oracle强化练习之子查询

来源：互联网发布：贝恩财团知乎编辑：程序博客网时间：2024/04/28 01:41

1. 列出至少有二个雇员的所有部门

Select deptno From emp group by deptno having count(*)>2;

2. 列出薪金比"SMITH"多的所有雇员

Select ename From emp where sal>(select sal from emp where ename=’SMITH’);

3. 列出入职日期早于其直接上级的所有雇员

Select * From emp e,(select empno,hiredate from emp) a where e.mgr=a.empno and e.hiredate <a.hiredate;

4. 找员工姓名和直接上级的名字

Select e.ename,s.ename From emp e,(select ename,empno from emp where empno in(select distinct mgr from emp )) s

Where e.mgr = s.empno;

5. 显示部门名称和人数

Select d.dname,count(*) From emp e,dept d Where e.deptno = d.deptno Group by dname

6. 显示每个部门的最高工资的员工

Select * From emp e,(select deptno,max(sal) m from emp group by deptno) a Where e.sal = a.m

7. 显示每个部门的工资前2名的员工

SELECT *

FROM (SELECTRANK() OVER(PARTITIONBY DEPTNO ORDER BY SAL DESC) R,

E.ENAME,

E.SAL,

E.DEPTNO

FROM EMP E)

WHERE R <= 2;

Select * From (Select rank() over(partition by deptno order by sal desc) r,e.ename,e.sal,e.deptno From emp e) where r <=2;

8. 显示出和员工号7369部门相同的员工姓名，工资

Select e.ename,e.sal from emp e,(select empno,deptno from emp where empno=7369) a where e.deptno=a.deptno;

9. 显示出和姓名中包含"W"的员工相同部门的员工姓名

Select e.ename From emp e,(select deptno from emp where ename like ‘%W%’) a where e.deptno=a.deptno;

10. 显示出工资大于平均工资的员工姓名，工资

Select e.ename,e.sal From emp e,(select avg(sal) a from emp) s where e.sal>s.a;

11. 显示出工资大于本部门平均工资的员工姓名，工资

Select e.ename,e.sal from emp e,(select deptno,avg(sal) av from emp group by deptno) a where e.deptno=a.deptno and sal>av;

12. 显示员工"KING"所管理的员工姓名

Select e.ename from emp e,(select empno from emp where ename=’KING’) a where e.mgr=a.empno;

13. 显示每位经理管理员工的最低工资，及最低工资者的姓名

Select e.ename,e.sal From emp e,(select min(sal) mins from emp group by mgr having mgr is not null) a where e.sal=a.mins;

14. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间

Select ename,hiredate From emp where hiredate>(select hiredate from emp where sal=(select max(sal) from emp));

15. 显示出平均工资最高的的部门平均工资及部门名称

Select d.dname,a.s From dept d,(select * from (select deptno,avg(sal) s from emp group by deptno order by avg(sal) desc) where rownum=1) a where d.deptno=a.deptno;

0 0