数据库——子查询(嵌套查询)练习

查询工资高于1号部门平均工资的员工信息

– 1号部门的平均工资
select avg(sal) from emp where deptno=1;
select * from emp where sal>(select avg(sal) from emp where deptno=1);

查询工资最高的员工信息

– 最高工资 select max(sal) from emp;
select * from emp where sal=(select max(sal) from emp);

查询工资高于2号部门最低工资的员工信息

– 2号部门最低工资 select min(sal) from emp where deptno=2;
select *from emp where sal >(select min(sal) from emp where deptno=2);

查询和孙悟空相同工作的其它员工信息

– 得到孙悟空的工作 select job from emp where ename=‘孙悟空’;
select * from emp where job=(select job from emp where ename=‘孙悟空’) and ename!=‘孙悟空’;

查询拿最低工资员工的同事们的信息(同事指同一部门的员工)

– 最低工资 select min(sal) from emp;

– 通过最低工资得到部门编号

select deptno from emp where sal=(select min(sal) from emp);

– 通过部门编号得到该部门的员工 排除掉最低工资

select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

查询白骨精的部门信息

– 先查询白骨精的部门编号 select deptno from emp where ename=‘白骨精’;

select * from dept where deptno=(select deptno from emp where ename=‘白骨精’);

查询所有员工的部门信息(部门只有1,2,3但是部门表里面有1,2,3,4 只根据员工表中出现的部门编号去查询部门信息)

– 查询员工表中出现的部门编号 select distinct deptno from emp;

select * from dept where deptno in(select distinct deptno from emp);