SQL综合训练

1.   查询所有从事"CLERK"工作的雇员姓名及其部门名称、部门人数。

2.       查询在部门"sales"（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

3.       查询出没有员工的那个部门的部门编号和部门名称

4.      查询职员领导信息,要求领导工资大于4000 

5.       显示职员工资的最大差额。

6.       显示所有雇员名及其全年收入(工资+补助)，并指定列别名“年收入”。

7.       显示所有雇员的平均工资、总计工资、最高工资、最低工资

8.       查询KING所在单位的单位编号\单位名称\单位人数。

9.       找出人力成本最高的部门

10.   显示部门20的员工姓名、部门编号以及在同一部门工作的所有员工

--1.  查询所有从事"CLERK"工作的雇员姓名及其部门名称、部门人数。
select e.ename 姓名,d.*
from emp e,
(select d.deptno ,d.dname 部门名称,count(*) 部门总人数
from emp e,dept d
group by d.deptno,d.dname) d
where e.deptno=d.deptno and e.job='CLERK';


--2.  查询在部门"sales"（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select e.ename
from emp e
where e.deptno=(select d.deptno from dept d where d.dname='SALES');


--3.  查询出没有员工的那个部门的部门编号和部门名称
select d.deptno,d.dname
from dept d
where d.deptno not in (select distinct e.deptno from emp e);


--4.  查询职员领导信息,要求领导工资大于4000 
select distinct e1.*
from emp e1,emp e2
where e1.empno=e2.mgr and e1.sal>4000;


--5.  显示职员工资的最大差额。
select max(sal)-min(sal) 最大差额 from emp;


--6.  显示所有雇员名及其全年收入(工资+补助)，并指定列别名“年收入”。
select e.ename,(e.sal+(nvl(comm,0)))*12 年收入
from emp e
order by 年收入 desc;


--7.  显示所有雇员的平均工资、总计工资、最高工资、最低工资
select round(avg(e.sal),2) 平均工资,sum(e.sal+nvl(comm,0)) 总计工资,max(e.sal) 最高工资,min(e.sal) 最低工资
from emp e;


--8.  查询KING所在单位的单位编号\单位名称\单位人数。
select d.deptno 单位编号,d.dname 单位名称,d1.c 单位人数
from dept d,
(select e.deptno d,count(1) c from emp e where e.ename='KING' group by e.deptno) d1
where d.deptno=d1.d;


--9.  找出人力成本最高的部门
select s.d 部门编号,s.s 部门总工资
from (select e.deptno d, sum(sal)+sum(nvl(comm,0)) s from emp e group by e.deptno) s
where s.s=(select max(s.s) from (select e.deptno d, sum(sal)+sum(nvl(comm,0)) s from emp e group by e.deptno) s);


--10.  显示部门20的员工姓名、部门编号以及在同一工作的所有员工
--思路：先统计出部门20的有两个人及以上做同一工作的工作（job）
select e.ename 员工姓名,e.deptno 部门编号,e.job 员工工作
from emp e
where 
job in (select e.job from emp e where e.deptno=20 group by e.job having count(*)>=2) 
and e.deptno=20;