oracle常用数据库语句（3）

–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)