1.查询部门名称为ACCOUNTING和RESEARCH的员工信息
select *
from emp e
where e.deptno in
(select d.deptno
from dept d
where d.dname in('ACCOUNTING','RESEARCH'));
2.查询不是经理的员工的信息
select *
from emp e
where e.empno not in
(select distinct e1.mgr
from emp e1
where e1.mgr is not null);
3.查询出所有经理的姓名及所在部门编号
select e.ename,e.deptno
from emp e
where e.empno in
(select distinct e1.mgr
from emp e1
where e1.mgr is not null);
4.查询工资比10号部门都要低的员工信息
select *
from emp e
where e.sal<
(select min(e1.sal)
from emp e1
where e1.deptno=10);
5.如何显示小于10号部门的最低工资的员工
select e.ename,e.sal,s1.ms
from emp e,
(select min(e1.sal) ms
from emp e1