关于SQL子查询的实战演练

-- 子查询

-- 子查询语句可以在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';