— 子查询
— 子查询语句可以在select， from where 中
— 查询工资高于平均工资的雇员名字和工资。
select ename, sal from emp where sal > (select avg(sal) as avg_sal from emp);
— 查询和scott同一部门且工资比他低的雇员名字和工资
select t1.ename, t2.sal from emp as t1 join (select deptno, sal from emp where ename = ‘scott’) as t2 on t1.deptno = t2.deptno and t1.sal < t2.sal;
— 查询工资低于任何一个clerk的工资的雇员信息
select * from emp where sal < all (select sal from emp where job=’clerk’);
— 查询工资比所有的salesman都高的雇员编号、名字和工资
select empno, ename, sal from emp where sal > all (select sal from emp where job = ‘salesman’);
— 查询部门20中职务同部门10的雇员一样的雇员信息
select * from emp where job in (select job from emp where deptno = 10) and deptno = 20;
— 查询职务和scott相同，比scott雇佣时间早的雇员信息
select t2.* from (select job, hiredate from emp where ename = ‘scott’) as t1 join emp as t2 on t1.job = t2.job and t1.hiredate > t2.hiredate;
— 查询每个部门的详细信息及该部门平均工资和等级
— 1、先查询每个部门的平均工资和详细信息
select t3.*, t4.grade from (select t2.*, avg(sal) as avg_sal from emp as t1 right join dept as t2 on t1.deptno = t2.deptno group by deptno) as t3 left join salgrade as t4 on avg_sal between t4.losal and t4.hisal;
— 求平均薪水的等级最低的部门名称
— 1、先求出每个部门的平均薪水和信息
select t1.* from (select dept.*, avg(emp.sal) as avg_sal from emp join dept on emp.deptno = dept.deptno group by dept.deptno) as t1 join salgrade as t2 on t1.avg_sal between t2.losal and t2.hisal order by t1.avg_sal limit 1;
— 找出部门编号为20的所有员工中收入最高的职员
select * from emp where deptno = 20 and job = ‘clerk’ order by sal desc limit 1;
— 查询在雇员中有哪些人是领导
select * from emp where job = ‘manager’ or job = ‘president’;
— 求平均薪水最高的部门的部门编号
— 1、先求出所有部门的平均薪水，再排序
select t1.deptno from (select dept.deptno,avg(sal) as avg_sal from emp join dept on emp.deptno = dept.deptno group by dept.deptno order by avg_sal desc limit 1) as t1
— 求比普通员工的最高薪水还要高的经理名字
— 1、先求普通员工的最高薪水
select ename from emp where sal > (select max(sal) as max_sal from emp where job = ‘clerk’) and job = ‘manager’;