一、相关子查询
--1：查询本部门的最高工资的员工的信息
--查询10部门的最高工资的员工的信息
--10部门的最高工资
select max(sal) from emp where deptno=10
select * from emp where deptno=10 and sal=(select max(sal) from emp where deptno=10)
select * from emp where deptno=20 and sal=(select max(sal) from emp where deptno=20)
select * from emp where deptno=30 and sal=(select max(sal) from emp where deptno=30)
select * from emp where deptno=40 and sal=(select max(sal) from emp where deptno=40)
select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno)
--2：查询工资高于其所在部门的平均工资的员工的信息
--查询工资高于10部门的平均工资的10部门员工的信息
--10部门的平均工资
select avg(sal) from emp where deptno=10
select * from emp where deptno=10 and sal > (select avg(sal) from emp where deptno=10)--1
select * from emp where deptno=20 and sal > (select avg(sal) from emp where deptno=20)--3
select * from emp where deptno=30 and sal > (select avg(sal) from emp where deptno=30)--2
select * from emp where deptno=40 and sal > (select avg(sal) from emp where deptno=40)--0
select e.*,(select avg(sal) from emp where deptno = e.deptno)平均工资
from emp e
where sal>(select avg(sal) from emp where deptno = e.deptno)
--3：查询本部门的最高工资的员工的信息
-- 使用不相关的子查询实现
--把所有部门的最高的工资求出
select deptno,max(sal) from emp group by deptno
select deptno,sal from emp
select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)
二、练习
-- 1 、列出所有员工的年工资，按年薪从低到高排序。
select ename, sal*12+nvl(comm,0)*12 year_sal from emp order by year_sal
-- 2 、列出薪金比“ SMITH ”多的所有员工。
select * from emp where sal>(select sal from emp where ename='SMITH')
-- 3 、列出所有员工的姓名及其直接上级的姓名。 sql92 , sql99
--sql92
select e1.ename,e2.ename
from emp e1, emp e2
where e1.mgr= e2.empno
--sql99
select e1.ename,e2.ename
from emp e1 join emp e2
on e1.mgr= e2.empno
-- 4 、列出受雇日期早于其直接上级的所有员工。
--sql99
select e1.*
from emp e1 join emp e2
on e1.mgr=e2.empno
where e1.hiredate < e2.hiredate
-- 5 、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
select d.dname,e.*
from emp e right join dept d
on e.deptno=d.deptno
-- 6 、列出所有job 为“ CLERK ”（办事员）的姓名及其部门名称。
select e.ename,d.dname
from emp e join dept d
Using(deptno)
where e.job='CLERK'
-- 7 、列出最低薪金大于1500 的各种工作。
select job, min(sal)
from emp
group by job
having min(sal) > 1500
-- 8 、列出在部门“ SALES ”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select e.deptno,e.ename
from emp e join dept d
on e.deptno=d.deptno
where d.dname='SALES'
-- 9 、列出薪金高于公司平均薪金的所有员工。
select * from emp where sal > (select avg(sal) from emp)
-- 10 、列出与“ SCOTT ”从事相同工作的所有员工。
select * from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT'
-- 11 、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal>all(select sal from emp where deptno=30)
-- 12 、列出在每个部门工作的员工数量、平均工资和平均服务期限（年）。
select deptno, count(*), avg(sal) , avg(to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')) 平均服务年限
from emp
group by deptno
-- 13 、列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal
from emp e natural join dept d
-- 14 、列出从事同一种工作但属于不同部门的员工的一种组合。
select e1.ename,e1.job ,e1.deptno,e2.ename,e2.job,e2.deptno
from emp e1 join emp e2
on e1.job=e2.job and e1.deptno<>e2.deptno and e1.ename >e2.ename
-- 15 、列出所有部门的详细信息和部门人数。
select d.*, count(*)
from emp e join dept d
on e.deptno=d.deptno
group by d.deptno,d.dname,d.loc
select d.*,count(e.ename) from dept d left join emp e
on d.deptno = e.deptno
group by d.deptno,d.dname,d.loc
-- 16 、列出各种工作的最低工资。
select job,min(sal)
from emp
group by job
-- 17 、列出各个部门的 MANAGER （经理）的最低薪金。
select deptno,min(sal)
from emp
where job='MANAGER'
group by deptno
-- 18 、列出至少有一个员工的所有部门。
select d.*,count(*)
from emp e join dept d
on e.deptno=d.deptno
group by d.deptno,d.dname,d.loc
having count(*)>0
-- 19 、人数最多部门的信息
--1
select *
from dept d
where deptno = (select deptno
from (select deptno,count(deptno) count_dept from emp group by deptno)
where count_dept = (select max(count_dept) from (select deptno,count(deptno) count_dept from empgroup by deptno)))
--2
select *
from dept d
where deptno = (select deptno from emp group by deptno having (count(*) >=
all(select count(deptno) from emp group by deptno)))
