标量子查询：

1. 查询基本工资高于公司平均工资的员工信息(where字句不能用聚合函数）

select *

from emp

where sal>(select avg(sal)

from emp);

2.练习：查询和allen同一个领导的员工：empno,ename,job,mgr

select empno,ename,job,mgr

from emp

where mgr=( select mgr from emp where ename='allen') and ename<>'allen';

行子查询

3. 查询和smith同部门同职位的员工：empno,ename,job,deptno

①select *

from emp

where deptno=(select deptno from emp where ename='smith') and job=(select job from emp where ename='smith') and ename<>'smith';

②select empno,ename,job,deptno

from emp

where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith';

列子查询：(any、(not)in、all)

4. 查询普通员工的工资等级：empno,ename,sal,grade

select empno,ename,sal,grade

from emp

left join salgrade on sal between losal and hisal

where empno not in (select distinct mgr from emp where mgr is not null );

5.练习：查询员工数不少于5人的部门的所有员工：empno,ename,deptno

select deptno,count(empno)

from emp

group by deptno

having count(empno)>=5; -- 员工数不少于5人的部门

select empno,ename,deptno

from emp

where deptno in (select deptno from emp group by deptno having count(empno)>=5); -- 查询员工数不少于5人的部门的所有员工

6. 查询基本工资高于30号部门任意(any)员工的员工信息

select sal from emp where deptno=30; -- 30号部门员工的工资

①select * from emp where sal> any(select sal from emp where deptno=30) and deptno<>30;

②select * from emp where sal> (select min(sal) from emp where deptno=30) and deptno<>30;

7.查询基本工资高于30号部门所有(all)员工的员工信息

select sal from emp where deptno=30; -- 30号部门员工的工资

①select * from emp where sal> all (select sal from emp where deptno=30) and deptno<>30;

②select * from emp where sal> (select max(sal) from emp where deptno=30) and deptno<>30;

8. 练习：查询员工数比CHICAGO少的部门的员工人数

select count(empno)

from emp right join dept on emp.deptno=dept.deptno

where loc='CHICAGO'; -- CHICAGO员工的人数

①select deptno,count(empno)

from emp

group by deptno

having count(empno)<(select count(empno)

from emp right join dept on emp.deptno=dept.deptno

where loc='CHICAGO'); -- 查询员工数比CHICAGO少的部门的员工人数

from子查询

9. 查询各部门最高工资的员工：empno,ename,sal,deptno

select deptno,max(sal) from emp group by deptno;

①select empno,ename,sal,emp.deptno

from emp

left join (select deptno,max(sal) 最高工资 from emp group by deptno) e -- 一定要设置表别名'e'和字段别名'最高工资'

on emp.deptno=e.deptno

where sal=最高工资;

select 子查询

10. 查询各部门的员工人数占比：各部门人数/总人数

select deptno,count(empno)/(select count(empno) from emp) 人数占比

from emp

group by deptno;