01. 查询员工表所有数据, 并说明使用*的缺点
答:
select * from emp;
使用*的缺点有
a) 查询出了不必要的列
b) 效率上不如直接指定列名
02. 查询职位(JOB)为'PRESIDENT'的员工的工资
答:
select * from emp where job = 'PRESIDENT';
03. 查询佣金(COMM)为0 或为NULL 的员工信息
答:重点是理解0 与null 的区别
select * from emp where comm = 0 or comm is null;
04. 查询入职日期在1981-5-1 到1981-12-31 之间的所有员工信息
答:通过此题掌握常用日期函数
select * from emp where hiredate
between to_date('1981-5-1','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd');
05. 查询所有名字长度为4 的员工的员工编号,姓名
答:
select * from emp where length(ename) = 4;
06. 显示10 号部门的所有经理('MANAGER')和20 号部门的所有职员('CLERK')的详细信息
答:
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job ='CLERK';
07. 显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
答:考察知识点模糊查询
select * from emp where ename not like '%L%' or ename like '%SM%';
08. 显示各个部门经理('MANAGER')的工资
答:
select sal from emp where job = 'MANAGER';
09. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
答:
select * from emp where comm > sal;
10. 把hiredate 列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
答:
select * from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm');
11. 把hiredate 列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
答:
select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysdate,1) , 'mm');
12. 求1982 年入职的员工(考察知识点:单行函数)
答:
select * from emp where to_char(hiredate,'yyyy') = '1982';
13. 求1981 年下半年入职的员工(考察知识点:单行函数)
答:
select * from emp where hiredate
between to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd') - 1;
14. 求1981 年各个月入职的的员工个数(考察知识点:组函数)
答:
select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm')
from emp where to_char(hiredate,'yyyy')='1981'
group by trunc(hiredate,'month')
order by trunc(hiredate,'month');
Part II(第二天)
01. 查询各个部门的平均工资
答:考察知识点:分组
select deptno,avg(sal) from emp group by deptno;
02. 显示各种职位的最低工资
答:考察知识点:分组
select job,min(sal) from emp group by job;
03. 按照入职日期由新到旧排列员工信息
答:考察知识点:排序
select * from emp order by hiredate desc;
04. 查询员工的基本信息,附加其上级的姓名
答:考察知识点:自连接
select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;
05. 显示工资比'ALLEN'高的所有员工的姓名和工资
答:考察知识点:子查询
select * from emp where sal > (select sal from emp where ename='ALLEN');
分析:当查询结果是一行一列时,可以将此结果看做一个值,参与条件比较。
06. 显示与'SCOTT'从事相同工作的员工的详细信息
答:考察知识点:子查询
select * from emp where job = (select * from emp where ename='SCOTT');
分析:同第5 题
07. 显示销售部('SALES')员工的姓名
答:考察知识点:连接查询
select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES';
08. 显示与30 号部门'MARTIN'员工工资相同的员工的姓名和工资
答:考察知识点:子查询
select ename, sal from emp
where sal = (select sal from emp where deptno=30 and ename='MARTIN');
分析:同第5 题
09. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')
答:考察知识点:子查询
select * from emp where job='SALESMAN' and sal > (select avg(sal) from emp);
10. 显示所有职员的姓名及其所在部门的名称和工资
答:考察知识点:表连接
select ename, job, dname from emp e, dept d where e.deptno = d.deptno;
11. 查询在研发部('RESEARCH')工作员工的编号,姓名,工作部门,工作所在地
答:考察知识点:表连接
select empno,ename,dname,loc from emp e, dept d
where e.deptno = d.deptno and danme='RESEARCH';
12. 查询各个部门的名称和员工人数
答:考察知识点:子查询,表连接
select * from (select count(*) c, deptno from emp group by deptno) e
inner join dept d on e.deptno = d.deptno;
分析:主要思路是要将子查询结果看做一个临时表,此临时表又可以与其他表做表连接
13. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位
答:考察知识点:子查询
select job, count(*) from emp where sal > (select avg(sal) from emp) group by job;
分析:查询结果是一行一列,可以将查询结果看做一个值,进行条件比较
14. 查询工资相同的员工的工资和姓名
答:考察知识点:子查询
select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1;
分析:此题目类似于17 题,见17 题分析。
15. 查询工资最高的3 名员工信息
答:考察知识点:子查询,rownum
select * from (select * from emp order by sal desc) where rownum <= 3;
分析:见21 题要点一
16. 按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第
三名继续排)
答:考察知识点:子查询
select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank;
分析:
此题的要点在于理解select count(*) from emp where sal > e.sal+1 的含义,e.sal 代表当前员工,
该子查询的含义就是求比当前员工工资高的人数个数:比此员工工资高的人数个数如果为
0,表示此人排名第一,比此员工工资高的人数个数如果为1,表示此人排名第二… 所以该
子查询结果就表示排名。
17. 求入职日期相同的(年月日相同)的员工
答:考察知识点:子查询
select * from emp e where (select count(*) from emp where e.hiredate=hiredate)>1;
分析:常见的一个误解就是把此题当做自连接做:
select * from emp e1, emp e2 where e1.hiredate = e2.hiredate and e1.empno <> e2.empno;
这样做的结果中对于只有两个日期相等的没有错误,查询结果有2 条,但如果有三个日期相
等的查询结果就是6 条,其中3 条是重复的。
要点也是理解子查询的含义select count(*) from emp where e.hiredate=hiredate,代表取得与当
前员工入职日期相等的人数个数,如果个数大于1 表示此日期有相等的。
18. 查询每个部门的最高工资
答:考察知识点:分组
select deptno, max(sal) maxsal from emp group by deptno order by deptno;
19. 查询每个部门,每种职位的最高工资
答:考察知识点:分组
select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;
分析:要点是理解多列分组:部门与职位都相同的分为一组,求每组的最高工资,其实就是
表示每个部门,每种职位的最高工资
20. 查询每个员工的信息及工资级别(用到表Salgrade)
答:考察知识点:不等值连接
select * from salgrade;
select e.*, sg.grade from emp e, salgrade sg where sal between losal and hisal;
21. 查询工资最高的第6-10 名员工
答:考察知识点:子查询, rownum
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10)
where rn > 5;
分析:
要点一是rownum 不能直接和order by 连用,因为rownum 先产生,order by 后执行,因此
需要将
select * from emp order by sal desc
先排序之后的结果看做一个临时表,再对此临时表产生rownum 编号。
要点二是rownum 不能用作>或>=的比较条件,因此不能够直接这样写
select e.* from
(select * from emp order by sal desc) e
where rownum > 5 and rownum <=10;
因此需要将
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10
查询结果看做一个临时表,这个临时表除了有表e 中的所有列之外,多添加一个rownum 列
并取别名为rn,这时rn 已经作为临时表中一个真实的列存在了,因此可以使用>或>=比较
条件:
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10)
where rn > 5;
两次查询示例图如下:第一次取前10 条,第二次排除前5 条
22. 查询各部门工资最高的员工信息
答:考察知识点:子查询
select * from emp e where e.sal = (select max(sal) from emp where (deptno = e.deptno));
分析:要点同样是理解子查询select max(sal) from emp where (deptno = e.deptno)获取当前部
门(e.deptno)的最高工资,再将此最高值与当前工资(e.sal)进行比较。
思路2:
select e.* from (select max(sal) maxsal, deptno from emp group by deptno) b, emp e
where e.deptno = b.deptno and b.maxsal = e.sal;
将子查询看做一个临时表,临时表中有最高工资列maxsal,以及deptno 列,此临时表与真
实表emp 做表连接,连接条件为emp 表中的工资要等于临时表的最高工资并且两表的部门
编号要相等。
23. 查询每个部门工资最高的前2 名员工
答:考察知识点:子查询
select * from emp e where
(select count(*) from emp where sal > e.sal and e.deptno = deptno) < 2
order by deptno, sal desc;
分析:此题类似于第16 题,需要理解select count(*) from emp where sal > e.sal and e.deptno =
deptno 的含义:求工资大于当前员工工资(e.sal)并且部门编号等于当前员工部门编号
(e.deptno)的员工的个数,此个数+1 表示排名,< 2 表示取前两名。
思路2:
使用oracle 提供的分析函数rank:
select * from (
select rank() over (partition by deptno order by sal desc) rank, e.* from emp e
) where rank < 3;
rank 函数的作用是产生排名,与普通函数不同,高亮部分都是函数语法部分,其中over 是
关键字,总体意思是指按部门编号分组(partition by deptno),按工资降序(order by sal desc)
排名。
思路3:
步骤1:按照部门,工资降序排列,并产生编号
select e.*,rownum rn from (select * from emp order by deptno,sal desc) e;
步骤2:在此基础上再按照部门编号分组,求每组的编号的最小值
select min(rn) minrank,deptno from
(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e)
group by deptno;
步骤3:将两步产生的结果看做是临时表分别称为t1,t2,做连接,连接条件时t1 表中
部门编号等于t2 部门编号且t1.rn >= t2.minrank and t1.rn <= t2.minrank+1
select t1.* from
(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e) t1,
(select min(rn) minrank,deptno from
(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e)
group by deptno) t2
where t1.deptno = t2.deptno and t1.rn >= t2.minrank and t1.rn <= t2.minrank+1;
24. 查询出有3 个以上下属的员工信息
答:考察知识点:自连接,子查询
select * from emp e where
(select count(*) from emp where e.empno = mgr) > 2;
分析:关键是理解连接条件e.empno = mgr 是表示连接当前员工(e.empno)和他的下属(mgr)
25. 查询所有大于本部门平均工资的员工信息()
答:考察知识点:子查询
select * from emp e where sal >
(select avg(sal) from emp where (deptno = e.deptno))
order by deptno;
分析:思路与22 题相同。
26. 查询平均工资最高的部门信息
答:考察知识点:子查询,组函数,连接查询
select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se
where avgsal = (select max(avg(sal)) from emp group by deptno) and d.deptno = se.deptno;
分析:
步骤1:求每个部门的平均工资:
select avg(sal) avgsal, deptno from emp group by deptno;
步骤2:求最高的平均工资:
select max(avg(sal)) from emp group by deptno;
步骤3:求平均工资最高的部门信息,连接步骤1 产生的临时表与真实表dept:
select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se
where avgsal = (select max(avg(sal)) from emp group by deptno) and d.deptno = se.deptno;
27. 查询大于各部门总工资的平均值的部门信息
答:考察知识点:子查询,组函数,连接查询
select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se
where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = d.deptno;
分析:
步骤1:求每个部门总工资
select sum(sal) sumsal, deptno from emp group by deptno;
步骤2:求每总工资平均值
select avg(sum(sal)) from emp group by deptno;
步骤3:求大于总工资平均值的部门信息,连接步骤1 产生的临时表与真实表dept:
select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se
where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = d.deptno;
28. 查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接
查询)
答:考察知识点:子查询,组函数,连接查询
select e.*,sumsal from emp e, (select sum(sal) sumsal, deptno from emp group by deptno) se
where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = e.deptno;
分析:类似于26 题,27 题
29. 查询没有员工的部门信息
答:考察知识点:表连接
select d.* from dept d left join emp e on (e.deptno = d.deptno) where empno is null;
分析:利用了左外连接的特点,部门连接员工时,没有匹配记录的部门对应的员工编号列肯
定为null
30. 查询用户(users 表)huxz 所下所有订单编号,下单日期,总价格(orders 表),并包括订
单中的商品数量(orderitem 表),名称(product 表),价格(product 表)
答:考察知识点:多表连接
select u.username, o.orderid, o.orderdate, o.totalprice, p.productname, p.price, i.qty
from users u inner join orders o on (u.username = o.username)
inner join orderitem i on (o.orderid = i.orderid)
inner join product p on (p.productid = i.productid)
where u.username = 'huxz';
31. 查询100001 号商品被哪些顾客(users 表)购买过,下单日期(orders 表),每人购买的数
量(orderitem 表),购买时的价格(product 表)
答:考察知识点:多表连接
select u.username, o.orderdate, p.productname, p.price, i.qty
from product p inner join orderitem i on (p.productid = i.productid)
inner join orders o on (o.orderid = i.orderid)
inner join users u on (u.username = o.username)
where p.productid = 100001;
32. 查询出哪些商品从未被订购过
答:考察知识点:连接查询
select p.* from product p left join orderitem i on(i. productid = p. productid)
where i.orderitemid is null;
分析:同29 题
33. 查询出被订购过2 次以上的商品信息
答:考察知识点:连接查询,子查询
select p.* from product p
where (select count(*) from orderitem where productid = p. productid) >= 2;
分析:子查询select count(*) from orderitem where productid = p. productid 的含义是:当前商
品在orderitem 表中出现的次数,即被订购的次数
答:
select * from emp;
使用*的缺点有
a) 查询出了不必要的列
b) 效率上不如直接指定列名
02. 查询职位(JOB)为'PRESIDENT'的员工的工资
答:
select * from emp where job = 'PRESIDENT';
03. 查询佣金(COMM)为0 或为NULL 的员工信息
答:重点是理解0 与null 的区别
select * from emp where comm = 0 or comm is null;
04. 查询入职日期在1981-5-1 到1981-12-31 之间的所有员工信息
答:通过此题掌握常用日期函数
select * from emp where hiredate
between to_date('1981-5-1','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd');
05. 查询所有名字长度为4 的员工的员工编号,姓名
答:
select * from emp where length(ename) = 4;
06. 显示10 号部门的所有经理('MANAGER')和20 号部门的所有职员('CLERK')的详细信息
答:
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job ='CLERK';
07. 显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
答:考察知识点模糊查询
select * from emp where ename not like '%L%' or ename like '%SM%';
08. 显示各个部门经理('MANAGER')的工资
答:
select sal from emp where job = 'MANAGER';
09. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
答:
select * from emp where comm > sal;
10. 把hiredate 列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
答:
select * from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm');
11. 把hiredate 列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
答:
select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysdate,1) , 'mm');
12. 求1982 年入职的员工(考察知识点:单行函数)
答:
select * from emp where to_char(hiredate,'yyyy') = '1982';
13. 求1981 年下半年入职的员工(考察知识点:单行函数)
答:
select * from emp where hiredate
between to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd') - 1;
14. 求1981 年各个月入职的的员工个数(考察知识点:组函数)
答:
select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm')
from emp where to_char(hiredate,'yyyy')='1981'
group by trunc(hiredate,'month')
order by trunc(hiredate,'month');
Part II(第二天)
01. 查询各个部门的平均工资
答:考察知识点:分组
select deptno,avg(sal) from emp group by deptno;
02. 显示各种职位的最低工资
答:考察知识点:分组
select job,min(sal) from emp group by job;
03. 按照入职日期由新到旧排列员工信息
答:考察知识点:排序
select * from emp order by hiredate desc;
04. 查询员工的基本信息,附加其上级的姓名
答:考察知识点:自连接
select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;
05. 显示工资比'ALLEN'高的所有员工的姓名和工资
答:考察知识点:子查询
select * from emp where sal > (select sal from emp where ename='ALLEN');
分析:当查询结果是一行一列时,可以将此结果看做一个值,参与条件比较。
06. 显示与'SCOTT'从事相同工作的员工的详细信息
答:考察知识点:子查询
select * from emp where job = (select * from emp where ename='SCOTT');
分析:同第5 题
07. 显示销售部('SALES')员工的姓名
答:考察知识点:连接查询
select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES';
08. 显示与30 号部门'MARTIN'员工工资相同的员工的姓名和工资
答:考察知识点:子查询
select ename, sal from emp
where sal = (select sal from emp where deptno=30 and ename='MARTIN');
分析:同第5 题
09. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')
答:考察知识点:子查询
select * from emp where job='SALESMAN' and sal > (select avg(sal) from emp);
10. 显示所有职员的姓名及其所在部门的名称和工资
答:考察知识点:表连接
select ename, job, dname from emp e, dept d where e.deptno = d.deptno;
11. 查询在研发部('RESEARCH')工作员工的编号,姓名,工作部门,工作所在地
答:考察知识点:表连接
select empno,ename,dname,loc from emp e, dept d
where e.deptno = d.deptno and danme='RESEARCH';
12. 查询各个部门的名称和员工人数
答:考察知识点:子查询,表连接
select * from (select count(*) c, deptno from emp group by deptno) e
inner join dept d on e.deptno = d.deptno;
分析:主要思路是要将子查询结果看做一个临时表,此临时表又可以与其他表做表连接
13. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位
答:考察知识点:子查询
select job, count(*) from emp where sal > (select avg(sal) from emp) group by job;
分析:查询结果是一行一列,可以将查询结果看做一个值,进行条件比较
14. 查询工资相同的员工的工资和姓名
答:考察知识点:子查询
select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1;
分析:此题目类似于17 题,见17 题分析。
15. 查询工资最高的3 名员工信息
答:考察知识点:子查询,rownum
select * from (select * from emp order by sal desc) where rownum <= 3;
分析:见21 题要点一
16. 按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第
三名继续排)
答:考察知识点:子查询
select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank;
分析:
此题的要点在于理解select count(*) from emp where sal > e.sal+1 的含义,e.sal 代表当前员工,
该子查询的含义就是求比当前员工工资高的人数个数:比此员工工资高的人数个数如果为
0,表示此人排名第一,比此员工工资高的人数个数如果为1,表示此人排名第二… 所以该
子查询结果就表示排名。
17. 求入职日期相同的(年月日相同)的员工
答:考察知识点:子查询
select * from emp e where (select count(*) from emp where e.hiredate=hiredate)>1;
分析:常见的一个误解就是把此题当做自连接做:
select * from emp e1, emp e2 where e1.hiredate = e2.hiredate and e1.empno <> e2.empno;
这样做的结果中对于只有两个日期相等的没有错误,查询结果有2 条,但如果有三个日期相
等的查询结果就是6 条,其中3 条是重复的。
要点也是理解子查询的含义select count(*) from emp where e.hiredate=hiredate,代表取得与当
前员工入职日期相等的人数个数,如果个数大于1 表示此日期有相等的。
18. 查询每个部门的最高工资
答:考察知识点:分组
select deptno, max(sal) maxsal from emp group by deptno order by deptno;
19. 查询每个部门,每种职位的最高工资
答:考察知识点:分组
select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;
分析:要点是理解多列分组:部门与职位都相同的分为一组,求每组的最高工资,其实就是
表示每个部门,每种职位的最高工资
20. 查询每个员工的信息及工资级别(用到表Salgrade)
答:考察知识点:不等值连接
select * from salgrade;
select e.*, sg.grade from emp e, salgrade sg where sal between losal and hisal;
21. 查询工资最高的第6-10 名员工
答:考察知识点:子查询, rownum
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10)
where rn > 5;
分析:
要点一是rownum 不能直接和order by 连用,因为rownum 先产生,order by 后执行,因此
需要将
select * from emp order by sal desc
先排序之后的结果看做一个临时表,再对此临时表产生rownum 编号。
要点二是rownum 不能用作>或>=的比较条件,因此不能够直接这样写
select e.* from
(select * from emp order by sal desc) e
where rownum > 5 and rownum <=10;
因此需要将
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10
查询结果看做一个临时表,这个临时表除了有表e 中的所有列之外,多添加一个rownum 列
并取别名为rn,这时rn 已经作为临时表中一个真实的列存在了,因此可以使用>或>=比较
条件:
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10)
where rn > 5;
两次查询示例图如下:第一次取前10 条,第二次排除前5 条
22. 查询各部门工资最高的员工信息
答:考察知识点:子查询
select * from emp e where e.sal = (select max(sal) from emp where (deptno = e.deptno));
分析:要点同样是理解子查询select max(sal) from emp where (deptno = e.deptno)获取当前部
门(e.deptno)的最高工资,再将此最高值与当前工资(e.sal)进行比较。
思路2:
select e.* from (select max(sal) maxsal, deptno from emp group by deptno) b, emp e
where e.deptno = b.deptno and b.maxsal = e.sal;
将子查询看做一个临时表,临时表中有最高工资列maxsal,以及deptno 列,此临时表与真
实表emp 做表连接,连接条件为emp 表中的工资要等于临时表的最高工资并且两表的部门
编号要相等。
23. 查询每个部门工资最高的前2 名员工
答:考察知识点:子查询
select * from emp e where
(select count(*) from emp where sal > e.sal and e.deptno = deptno) < 2
order by deptno, sal desc;
分析:此题类似于第16 题,需要理解select count(*) from emp where sal > e.sal and e.deptno =
deptno 的含义:求工资大于当前员工工资(e.sal)并且部门编号等于当前员工部门编号
(e.deptno)的员工的个数,此个数+1 表示排名,< 2 表示取前两名。
思路2:
使用oracle 提供的分析函数rank:
select * from (
select rank() over (partition by deptno order by sal desc) rank, e.* from emp e
) where rank < 3;
rank 函数的作用是产生排名,与普通函数不同,高亮部分都是函数语法部分,其中over 是
关键字,总体意思是指按部门编号分组(partition by deptno),按工资降序(order by sal desc)
排名。
思路3:
步骤1:按照部门,工资降序排列,并产生编号
select e.*,rownum rn from (select * from emp order by deptno,sal desc) e;
步骤2:在此基础上再按照部门编号分组,求每组的编号的最小值
select min(rn) minrank,deptno from
(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e)
group by deptno;
步骤3:将两步产生的结果看做是临时表分别称为t1,t2,做连接,连接条件时t1 表中
部门编号等于t2 部门编号且t1.rn >= t2.minrank and t1.rn <= t2.minrank+1
select t1.* from
(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e) t1,
(select min(rn) minrank,deptno from
(select e.*,rownum rn from (select * from emp order by deptno,sal desc) e)
group by deptno) t2
where t1.deptno = t2.deptno and t1.rn >= t2.minrank and t1.rn <= t2.minrank+1;
24. 查询出有3 个以上下属的员工信息
答:考察知识点:自连接,子查询
select * from emp e where
(select count(*) from emp where e.empno = mgr) > 2;
分析:关键是理解连接条件e.empno = mgr 是表示连接当前员工(e.empno)和他的下属(mgr)
25. 查询所有大于本部门平均工资的员工信息()
答:考察知识点:子查询
select * from emp e where sal >
(select avg(sal) from emp where (deptno = e.deptno))
order by deptno;
分析:思路与22 题相同。
26. 查询平均工资最高的部门信息
答:考察知识点:子查询,组函数,连接查询
select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se
where avgsal = (select max(avg(sal)) from emp group by deptno) and d.deptno = se.deptno;
分析:
步骤1:求每个部门的平均工资:
select avg(sal) avgsal, deptno from emp group by deptno;
步骤2:求最高的平均工资:
select max(avg(sal)) from emp group by deptno;
步骤3:求平均工资最高的部门信息,连接步骤1 产生的临时表与真实表dept:
select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se
where avgsal = (select max(avg(sal)) from emp group by deptno) and d.deptno = se.deptno;
27. 查询大于各部门总工资的平均值的部门信息
答:考察知识点:子查询,组函数,连接查询
select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se
where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = d.deptno;
分析:
步骤1:求每个部门总工资
select sum(sal) sumsal, deptno from emp group by deptno;
步骤2:求每总工资平均值
select avg(sum(sal)) from emp group by deptno;
步骤3:求大于总工资平均值的部门信息,连接步骤1 产生的临时表与真实表dept:
select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se
where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = d.deptno;
28. 查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接
查询)
答:考察知识点:子查询,组函数,连接查询
select e.*,sumsal from emp e, (select sum(sal) sumsal, deptno from emp group by deptno) se
where sumsal >(select avg(sum(sal)) from emp group by deptno) and se.deptno = e.deptno;
分析:类似于26 题,27 题
29. 查询没有员工的部门信息
答:考察知识点:表连接
select d.* from dept d left join emp e on (e.deptno = d.deptno) where empno is null;
分析:利用了左外连接的特点,部门连接员工时,没有匹配记录的部门对应的员工编号列肯
定为null
30. 查询用户(users 表)huxz 所下所有订单编号,下单日期,总价格(orders 表),并包括订
单中的商品数量(orderitem 表),名称(product 表),价格(product 表)
答:考察知识点:多表连接
select u.username, o.orderid, o.orderdate, o.totalprice, p.productname, p.price, i.qty
from users u inner join orders o on (u.username = o.username)
inner join orderitem i on (o.orderid = i.orderid)
inner join product p on (p.productid = i.productid)
where u.username = 'huxz';
31. 查询100001 号商品被哪些顾客(users 表)购买过,下单日期(orders 表),每人购买的数
量(orderitem 表),购买时的价格(product 表)
答:考察知识点:多表连接
select u.username, o.orderdate, p.productname, p.price, i.qty
from product p inner join orderitem i on (p.productid = i.productid)
inner join orders o on (o.orderid = i.orderid)
inner join users u on (u.username = o.username)
where p.productid = 100001;
32. 查询出哪些商品从未被订购过
答:考察知识点:连接查询
select p.* from product p left join orderitem i on(i. productid = p. productid)
where i.orderitemid is null;
分析:同29 题
33. 查询出被订购过2 次以上的商品信息
答:考察知识点:连接查询,子查询
select p.* from product p
where (select count(*) from orderitem where productid = p. productid) >= 2;
分析:子查询select count(*) from orderitem where productid = p. productid 的含义是:当前商
品在orderitem 表中出现的次数,即被订购的次数