–1、列出至少有一个雇员的所有部门信息
select * from dept where deptno in (select deptno from emp group by deptno having count(*)>0)
select a.deptno,b.dname,b.loc,a.num from (select deptno,count() num from emp group by deptno having count()>=1) a,dept b where a.deptno=b.deptno
select a.deptno,a.dname,a.loc,b.c from dept a,(select deptno,count() c from emp group by deptno having count()>0) b
where a.deptno = b.deptno
select * from dept where deptno in (select distinct deptno from emp)
select a.deptno,
(select dname from dept b where a.deptno=b.deptno),
(select loc from dept b where a.deptno=b.deptno),
count() from emp a group by a.deptno having count()>0
–2、列出薪金(工资)比’SMITH’多的所有雇员信息
select * from emp where sal>(select sal from emp where ename=‘SMITH’)
–3、列出所有雇员的姓名及其上级的姓名
select empno,ename,mgr,
(select ename from emp b where a.mgr=b.empno)
from emp a
–4、列出入职日期(雇佣日期)早于其直接上级的所有雇员
select a.empno,a.ename,a.mgr,a.hiredate,
(select hiredate from emp b where a.mgr=b.empno)
from emp a where a.hiredate<(select hiredate from emp b where a.mgr=b.empno)
–5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select * from emp a right join dept b on a.deptno=b.deptno
select * from dept a left join emp b on a.deptno=b.deptno
select * from emp a,dept b where a.deptno(+)=b.deptno
–6、列出所有’CLERK’(办事员)的姓名及其部门名称
select a.ename,b.dname,a.job from emp a,dept b where a.deptno=b.deptno and a.job=‘CLERK’
–7、列出各种类别job的最低薪金,并使最低薪金大于1500
select job,min(sal) from emp group by job having min(sal)>1500
–8、列出从事sales(销售)工作的雇员的姓名,假定不知道销售部的部门编号
select * from emp where deptno in(select deptno from dept where dname like ‘%SALES%’)
–9、列出薪金高于公司平均水平的所有雇员信息
select * from emp where sal>(select avg(sal) from emp)
–10、列出与"SCOTT"从事相同工作的所有雇员
select * from emp where job=(select job from emp where ename=‘SCOTT’) and ename!=‘SCOTT’
–11、列出薪金等于在部门30工作的雇员的所有姓名和薪金
select * from emp where sal in (select sal from emp where deptno=30) and deptno!=30
select * from emp where sal=any(select sal from emp where deptno=30) and deptno!=30
insert into emp(empno,sal,deptno) values(2222,1600,10)
–12、列出薪金高于在部门30工作的所有雇员的姓名和薪金
select * from emp where sal>(select max(sal) from emp where deptno=30)
select * from emp where sal>all(select sal from emp where deptno=30) and deptno!=30
–13、列出在每个部门工作的雇员的数量以及其他信息
select a.deptno,a.dname,a.loc,count(empno) from dept a left join emp b on a.deptno=b.deptno group by a.deptno,a.dname,a.loc
–14、列出所有雇员的姓名、部门名称和薪金
select a.ename,b.dname,a.sal from emp a,dept b where a.deptno=b.deptno
–15、列出从事同一种工作但属于不同的部门的雇员的不同组合
select a.ename,a.job,a.deptno,b.ename,b.job,b.deptno from emp a,emp b where a.job=b.job and a.deptno!=b.deptno and a.empno>b.empno
select * from emp order by job
–16、列出分配有雇员数量的所有部门的详细信息,即使分配有零个雇员
select a.deptno,a.dname,a.loc,count(empno) from dept a left join emp b on a.deptno=b.deptno group by a.deptno,a.dname,a.loc having count(empno)>=0
select * from dept
–17、列出薪金水平处于第四位的雇员信息
select * from (select dense_rank() over (order by sal desc) rn,a.* from emp a) where rn=4
select * from emp where sal=(select sal from (select rownum rn,a.* from (select distinct sal from emp order by sal desc) a) where rn=4)