1、列出至少有一个员工的所有部门
分析： 所有部门的员工数量
count（），group by
select deptno,count(empno) from emp group by deptno;
count(empno)>1
having
select deptno,count(empno) from emp group by deptno having count(empno)>1;
select d.*,ed.cou
from dept d,(select deptno,count(empno) cou from emp group by deptno having count(empno)>1) ed
where d.deptno=ed.deptno;
2、列出薪金比“SMITH”多的所有员工
select sal from emp where ename='SMITH';
select * from emp where sal>(select sal from emp where ename='SMITH');
3、列出所有员工的姓名以及其直接上级的姓名
select e.ename 员工的姓名,m.ename 领导的姓名
from emp e,emp m
where e.mgr=m.empno(+);
4、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno and e.hiredate
左右连接
select d.dname,e.*
from emp e,dept d
where e.deptno(+)=d.deptno;
6、列出所有“CLERK”的姓名及其部门名称，部门的人数
select ename,deptno from emp where job='CLERK';
select e.ename,d.dname,
from emp e,dept d
where e.deptno=d.deptno and job='CLERK' ;
要求部门的人数，要使用分组统计来完成，group by
select e.ename,d.dname,d.deptno
from emp e,dept d
where e.deptno=d.deptno and job='CLERK' ;
select deptno,count(empno) from emp group by deptno;
select e.ename,d.dname,d.deptno,ed.cou
from emp e,dept d,(select deptno,count(empno) cou from emp group by deptno) ed
where e.deptno=d.deptno and job='CLERK' and e.deptno=ed.deptno ;
7、列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数
min(sal)>1500 group by job
select job,min(sal) from emp group by job having min(sal)>1500;
in
any
all
select job,count(empno)
from emp
where job in (select job from emp group by job having min(sal)>1500) group by job ;
8、列出在部门“sales”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
通过dept表查deptno
select deptno from dept where dname=upper('sales');
select ename from emp where deptno=(select deptno from dept where dname=upper('sales'));
9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导等级，员工的工资等级
求出公司的平均工资
select avg(sal) from emp;
求出薪金高于平均工资的所有的员工
select * from emp where sal>(select avg(sal) from emp);
与部门表关联
select e.*,d.dname,d.loc from emp e,dept d where sal>(select avg(sal) from emp) and e.deptno=d.deptno;
要查询上级领导emp
select e.*,d.dname,d.loc,m.empno,m.ename from emp e,dept d ,emp m where sal>(select avg(sal) from emp) and e.deptno=d.deptno and e.mgr=m.empno;
一要查询员工的工资等级 sal
grade
select e.*,d.dname,d.loc,m.empno,m.ename ,s.grade
from emp e,dept d ,emp m,salgrade s
where sal>(select avg(sal) from emp) and e.deptno=d.deptno and e.mgr=m.empno(+) and e.sal between s.losal and s.hisal;
要查询领导等级salgrade
select e.ename 雇员的姓名,d.dname,d.loc,m.empno 领导的编号,m.ename 领导姓名,s.grade 雇员的工资等级,ms.grade 领导的工资等级
from emp e,dept d ,emp m,salgrade s,salgrade ms
where e.sal>(select avg(sal) from emp) and e.deptno=d.deptno and e.mgr=m.empno(+) and e.sal between s.losal and s.hisal and m.sal between ms.losal and ms.hisal;
10、列出与“scott”从事相同工作的所有员工及部门名称
要查询出scott这个人的工作
select job from emp where ename='SCOTT';
列出工作是analysis的所有员工，排除scott
select * from emp where job='ANALYSIS' and ename!='SCOTT';
列出工作是analysis的所有员工和部门名称，排除scott
select e.*.d.dname from emp e,dept d where e.job=(select job from emp where ename='SCOTT') and e.ename!='SCOTT' and e.deptno=d.deptno;
11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
分析：
部门30员工的薪金
select sal from emp where deptno=30;
把上面的查询作为一个子查询的条件
select ename,sal from emp where sal in (select sal from emp where deptno=30);
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
分析：部门30员工的薪金
select sal from emp where deptno=30;
>all
select ename,sal from emp where sal >all( select sal from emp where deptno=30);
select e.ename,e.sal,d.dname
from emp e,dept d
where sal >all( select sal from emp where deptno=30) and e.deptno=d.deptno;
13、列出在每个部门工作的员工数量、平均工资和平均服务期限
分析：列出在每个部门工作的员工数量
count（）
select deptno,count(empno)
from emp
group by deptno;
平均工资
avg（sal）
select deptno,count(empno)，avg（sal）
from emp
group by deptno;
平均服务期限
hiredate
sysdate
select deptno,count(empno),round(avg(sal)) 平均工资,round(avg(months_between(sysdate,hiredate)/12)) 平均服务期限
from emp
group by deptno;
select d.dname,count(e.empno),round(avg(e.sal)) 平均工资,round(avg(months_between(sysdate,e.hiredate)/12)) 平均服务期限
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
round()
trunc()
mod()
14、列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal
from emp e,dept d
where e.deptno=d.deptno;
15、列出所有部门的详细信息和部门人数
dept表
分析：列出所有部门的人数
select deptno,count(empno)
from emp group by deptno;
select d.* ,ed.cou from dept d,(selec
t deptno,count(empno) cou
from emp group by deptno)ed
where d.deptno=ed.deptno(+);
16、列出各种工作的最低工资以及从事此工作的雇员姓名
分析：按工作分组，求出最低工资 min（）
select job,min(sal) m from emp group by job;
select e.ename,s.m from emp e,(select job,min(sal) m from emp group by job) s where e.sal=s.m;
select * from emp where sal in(select min(sal) m from emp group by job);
17、列出各个部门的经理的最低薪金
select deptno,min(sal) from emp where job='MANAGER' group by deptno;
18、列出所有员工的年工资，按年薪从低到高排序
select nvl2(comm,sal+comm,sal)*12 income from emp order by income;
select (nvl(comm,0)+sal)*12 income from emp order by income;
nvl(字段，值)
nvl2(字段，值1，值2)
order by asc|desc
group by
19、查出某个员工的上级主管，并要求出这些主管中的薪水超过3000
select e.ename,d.dname,de.nl from emp e,emp d,(select mgr,(nvl(comm,0)+sal) nl from emp where nl>3000) ed where e.mgr=d.empno and e.mgr=ed.mgr;
select distinct m.*
from emp e,emp m
where e.mgr=m.empno and m.sal>3000;
20、求出部门名称中，带'S'字符的部门员工的工资总和 、部门人数
select sum(e.sal),count(e.empno) from emp e,dept d where d.dname like '%S%' and d.deptno=e.deptno;
select sum(sal),count(empno) from emp where deptno in (select deptno from emp where dname like '%S%';