第一题 :查询所有员工的年工资、所在部门的名称,按年薪从低往高排序;
SQL> select (sal*12+nvl(comm,0)) 年工资,d.dname 部门名称 from emp e,dept d where e.deptno=d.deptno order by 年工资 asc;
年工资 部门名称
---------- --------------
14400 RESEARCH
16200 SALES
18000 RESEARCH
20300 SALES
20400 ACCOUNTING
21200 OPERATIONS
22800 SALES
23400 RESEARCH
24300 SALES
40800 RESEARCH
40800 RESEARCH
48600 ACCOUNTING
53400 SALES
54900 RESEARCH
84000 ACCOUNTING
已选择15行。
第二题 :查询所有员工的编号、姓名,及其上级领导编号、姓名,显示结果按领导的年薪降序;
SQL> select e.empno 员工编号,e.ename 员工姓名,d.empno 领导编号,d.ename 领导姓名 from emp e,emp d where e.mgr=d.empno(+) order by d.sal*12+nvl(d.comm,
0) desc;
员工编号 员工姓名 领导编号 领导姓名
---------- ---------- ---------- ----------
7839 KING
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7521 WARD 7698 BLAKE
7654 MARTIN 7698 BLAKE
7844 TURNER 7698 BLAKE
7900 JAMES 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7369 SMITH 7902 FORD
7876 ADAMS 7788 SCOTT
5741 QIN_JIALI 7654 MARTIN
已选择15行。
第三题 :查询非销售人员:工作名称,以及从事同一工作员工的月工资之和,要求月工资之和大于5000,输出结果按月工资之和降序排列;
SQL> select job,sum(sal) from emp where job<> 'SALESMAN' group by job having sum(sal)>5000 order by sum(sal) desc;
JOB SUM(SAL)
--------- ----------
MANAGER 13075
CLERK 7650
PRESIDENT 7000
ANALYST 6800
第四题 :查询所有领取奖金和不领取奖金的员工人数、平均工资
SQL> select count(*),avg(sal) from emp where comm is not null and comm>0
2 union
3 select count(*),avg(sal) from emp where comm is null or comm=0;
COUNT(*) AVG(SAL)
---------- ----------
4 1800
11 3138.63636
本题涉及集合运算:
前提:各个集合的列数、类型必须一致。
union(并集):返回各个查询的所有记录,不包括重复记录。
unionall(并集):返回各个查询的所有记录,包括重复记录。
intersect(交集):返回两个查询的共有的记录。
minus(补集):返回包含在第一个查询中,但不包含在第二个查询中的记录。
–报表:查询总工资、各个部门的总工资、各个部门中各个工作的总工资;
各个部门中各个工作的总工资:
SQL> select deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 4600
30 SALESMAN 5550
20 MANAGER 4575
30 CLERK 1350
10 PRESIDENT 7000
40 SALESMAN 1650
30 MANAGER 4450
10 CLERK 1700
10 MANAGER 4050
20 ANALYST 6800
已选择10行。
各个部门的总工资
SQL> select deptno,NULL,sum(sal) from emp group by deptno;
DEPTNO N SUM(SAL)
---------- - ----------
30 11350
20 15975
40 1650
10 12750
查询总工资:
SQL> select NULL,NULL,sum(sal) from emp;
N N SUM(SAL)
- - ----------
41725
求并集:
SQL> select deptno,job,sum(sal) from emp group by deptno,job
2 union
3 select deptno,NULL,sum(sal) from emp group by deptno
4 union
5 select NULL,NULL,sum(sal) from emp;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1700
10 MANAGER 4050
10 PRESIDENT 7000
10 12750
20 ANALYST 6800
20 CLERK 4600
20 MANAGER 4575
20 15975
30 CLERK 1350
30 MANAGER 4450
30 SALESMAN 5550
30 11350
40 SALESMAN 1650
40 1650
41725
已选择15行。
增强group by:rollup()函数
用法:如果是group by rollup(A, B, C)
首先,对(A、B、C)进行group by
其次,对(A、B)进行group by
最后,对(A)进行GROUP BY
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1700
10 MANAGER 4050
10 PRESIDENT 7000
10 12750
20 CLERK 4600
20 ANALYST 6800
20 MANAGER 4575
20 15975
30 CLERK 1350
30 MANAGER 4450
30 SALESMAN 5550
30 11350
40 SALESMAN 1650
40 1650
41725
已选择15行。
第五题 :查询每种工作的最低工资,以及领取该工资的员工姓名。
SQL> select e.ename,d.minsal,e.job from emp e,(select min(sal) minsal,job from emp group by job) d where d.minsal=e.sal and d.job=e.job;
ENAME MINSAL JOB
---------- ---------- ---------
SMITH 1200 CLERK
WARD 1650 SALESMAN
MARTIN 1650 SALESMAN
CLARK 4050 MANAGER
SCOTT 3400 ANALYST
KING 7000 PRESIDENT
FORD 3400 ANALYST
已选择7行。
第六题 :查询出工资不超过2500的人数最多的部门名称;
SQL> select d.dname,d.deptno from dept d,emp e where d.deptno=e.deptno and e.sal<=2500 group by d.deptno,d.dname having count(*)=(select max(count(*))
from emp where sal<=2500 group by deptno);
DNAME DEPTNO
-------------- ----------
SALES 30
–难度系数比较高;
第七题 :查询出管理员工人数最多的人的名字和他管理的人的名字;
分析过程:
1 每个领导管理的人数:
SQL> select mgr,count(*) from emp group by mgr;
MGR COUNT(*)
---------- ----------
7839 3
1
7782 1
7698 5
7902 1
7654 1
7566 2
7788 1
已选择8行。
2 所有领导中,管理人数最多是多少?
SQL> select max(cn) from (select mgr,count(*) cn from emp group by mgr);
MAX(CN)
----------
5
3 查询出管理人数最多的领导编号
SQL> select mgr from emp group by mgr having count(*)=(select max(cn) from (select mgr,count(*) cn from emp group by mgr));
MGR
----------
7698
4 根据领导编号,查询领导名字;
SQL> select d.ename,e.ename from emp e,emp d where e.deptno=d.deptno and d.empno=(select mgr from emp group by mgr having count(*)=(select max(cn) fro
m (select mgr,count(*) cn from emp group by mgr))) ;
ENAME ENAME
---------- ----------
BLAKE ALLEN
BLAKE WARD
BLAKE BLAKE
BLAKE TURNER
BLAKE JAMES
第八题: 统计各个年份的入职人数,以及总入职人数;
本题涉及到了decode函数:
格式:decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值);
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
…
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
SQL> select count(*) 总人数,sum(decode(to_char(hiredate,'yyyy'),1980,1,0)) "1980",sum(decode(to_char(hiredate,'yyyy'),1987,1,0)) "1987",
2 sum(decode(to_char(hiredate,'yyyy'),1982,1,0)) "1982",sum(decode(to_char(hiredate,'yyyy'),1985,1,0)) "1985",
3 sum(decode(to_char(hiredate,'yyyy'),1981,1,0)) "1981" from emp;
总人数 1980 1987 1982 1985 1981
---------- ---------- ---------- ---------- ---------- ----------
15 1 2 1 1 10