1.显示编号为7839的员工名字(名字小写),薪水
SQL> select lower(ename) ,sal from emp where empno=7839;
2.显示编号为7839的员工名字(首字符大写其他小写),薪水
SQL> select initcap(ename) ,sal from emp where empno=7839;
3.显示有字符A的员工的名字,薪水
SQL> select sal ,ename from emp where regexp_instr(ename,'A',1)>0;
>>select ename,sal from emp where ename lile '%A%';
4.显示一个A字符的员工的名字,薪水
SQL> select sal ,ename from emp where regexp_count(ename,'A',1)=1 ;
5.显示二个A字符的员工的名字,薪水
SQL> select sal ,ename from emp where regexp_count(ename,'A',1)=2 ;
6.显示超过二个A字符的员工的名字,薪水
SQL> select sal ,ename from emp where regexp_count(ename,'A',1)>2 ;
7.显示有A字符员工名字,薪水[员工名字的A字符显示为小写a字符]
SQL> select sal ,regexp_replace(ename,'A','a') as "new" from emp where
regexp_count(ename,' A',1)>0;
>>>select ename,sal,replace(ename,'A','a') from emp;
8.显示员工名字,名字含有A字符出现的字符位置,薪水
SQL> select sal, ename ,instr(ename,'A') from emp;
9.显示员工名字,名字字符长度,薪水
SQL> select ename,length(ename),sal from emp;
10.显示员工名字,职位,薪水,职位列名居中显示,职位列值靠右显示
SQL> select ename,lpad(job,8),sal from emp;
>>>col job just center
select ename,job,sal,lpad(job,15) from emp;
11.显示员工名字,薪水[名字中T,TT,TTT这样相同T字符结尾的不显示,其他字符要显示]
SQL> select trim(trailing 'T' from ename),sal from emp;
>>>select ename,sal,rtrim(ename,'T') from emp;
12.不使用||操作符实现部门表每个列之间使用逗号分割显示
SQL> select concat(concat(concat(concat(deptno,','),dname),','),loc) from
dept;
13.显示员工名字,薪水[名字字符显示为第一个字符到第一次出现A的所有字符]
SQL> select
sal,substr(ename,1,decode(instr(ename,'A'),0,length(ename),instr(ename,'A')))
from emp;
14.显示员工名字,薪水[名字字符显示为第一次出现A的字符到最后一个字符]
SQL> select
sal,substr(ename,decode(instr(ename,'A'),0,1,instr(ename,'A')),length(ename))
from emp;
15.显示员工名字,薪水增加500,减少500,乘以500,除以500的结果
SQL> select ename,sal+500,sal-500,sal*500,sal/500 from emp;
16.显示日期,显示当前时间,显示当前时间戳
SQL> select sysdate,to_char(sysdate,'yyyy/mm/dd HH24:MI:SS'),systimestamp from
dual;
17.显示员工名字,入职时间,到今天经过了多少天
SQL> select ename hiredate,sysdate-hiredate from emp;
18.显示员工名字,入职时间,到今天经过了多少周
SQL> select hiredate,(sysdate-hiredate)/7 from emp;
19.显示员工名字,入职时间,到今天经过了多少年
SQL> select hiredate,(sysdate-hiredate)/365 from emp;
>>> select hiredate,ename ,(months_between(sysdate,hiredate))/12 from emp;
20.显示员工名字,入职时间,到今天经过了多少月
SQL> select hiredate,to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') from
emp;
21.显示员工名字,入职时间,3个月转正的时间
SQL> select hiredate,add_months(hiredate,3) from emp;
22.显示员工名字,入职时间,第一次上班时间[办理入职后下个星期一]
SQL> select hiredate,next_day(add_months(hiredate,3),'MON') from emp;
23.显示员工的总行数
SQL> select count(*) from emp;
24.显示员工的薪水总和,最大值,最小值,平均值
SQL> select sum(sal), max(sal),min(sal),avg(sal) from emp;
25.显示员工的名字最大值,最小值
SQL> select max(ename),min(ename) from emp;
26.显示员工的唯一部门总数
SQL> select count(distinct deptno) from emp;
27.通过奖金统计员工总数,统计有奖金的员工总数
SQL> select count(comm),count(nvl(comm,0)) from emp;
28.显示每个部门的总人数,薪水总和,最大值,最小值
SQL> select deptno,count(*),sum(sal),max(sal),min(sal) from emp group by
deptno;
29.显示每个职位的总人数,薪水总和,最大值,最小值
SQL> select job,count(*),sum(sal),max(sal),min(sal) from emp group by job;
30.显示职位薪水总和超过3000的职位,职位薪水总和
SQL> select job,sum(sal) from emp group by job having sum(sal)>3000 ;
31.显示当前部门的每个职位的总人数,薪水总和,最大值,最小值
SQL> select deptno, job,count(*),sum(sal),max(sal),min(sal) from emp group by
job,deptno ;
32.显示部门平均薪水最大值,部门平均薪水最小值
SQL> select max(avg(sal)),min(avg(sal)) from emp group by deptno;
33.统计员工入职时间在同一年的人数
SQL> select to_char(hiredate,'yyyy'),count(*) from emp group by
to_char(hiredate,'yyyy');
练习
1.找出比10部门最低工资要高的员工,显示姓名,部门编号,薪水
SQL> select ename,deptno,sal from emp where sal>(select min(sal) from emp
where deptno=10);
2.找出比自己部门平均薪水要低的员工,显示姓名,薪水
SQL> select ename,sal from emp e where sal<(select avg(sal) from emp where
e.deptno=deptno);
3.找出除KING外工资最高的员工,显示姓名,职位,薪水
SQL> select ename,job,sal from emp where sal=(select max(sal) from emp where
ename<>'KING');
4.找出职位是CLERK,并且薪水比SCOTT要高的员工有哪些
>>>select * from emp where job='CLERK' and sal >(select sal from emp where
ename='SCOTT');
SQL> select ename from emp where sal>(select sal from emp where
ename='SCOTT') and job='CLERK';
5.找出每个部门的薪水前2名,显示姓名,部门编号,薪水
SQL> select * from(select ename,deptno,sal,row_number() over(partition by
deptno order by sal desc) asal from emp ) where asal<=2;
6.找出薪水排名在6-8的员工,显示薪水和姓名
SQL> select * from(select ename,deptno,sal,row_number() over(partition by
deptno order by sal desc) asal from emp ) where asal between 6 and 8;
7.显示员工的部门名,部门总人数
SQL> select dname,(select count(*) from dept where d.deptno=deptno) from dept
d;
8.显示员工的部门编号,部门名,职位,薪水
select deptno,dname,job,sal from emp natural join dept;
select deptno,dname,job,sal from emp e,dept d where d.deptno=e.deptno;
select deptno,dname,job,sal from emp join dept using(deptno);
select deptno,dname,job,sal from emp e join dept d on(d.deptno=e.deptno);
SQL> select dname,job,sal from emp e join dept d on(d.deptno=e.deptno);
9.显示员工的部门名,员工名字,薪水等级
select dname,ename,grade from emp natural join dept join salgrade on(sal
between losal and hisal);
10.显示员工的工作地址,部门名,员工名字,薪水等级。
select loc, dname,ename,grade from emp natural join dept join salgrade
on(sal between losal and hisal);