欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

关于SQL子查询的实战演练

程序员文章站 2022-03-09 21:25:20
-- 子查询 -- 子查询语句可以在select, from where 中 -- 查询工资高于平均工资的雇员名字和工资。 select ename, sal from emp where sal...

-- 子查询

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