sql :emp ,dept表经典练习

--1.列出所有员工及直接上级的姓名

select e.ename,nvl(m.ename,'无')
from emp e,emp m
where e.mgr = m.empno(+);

****
 select e1.ename,nvl(e2.ename,'无上级') from emp e1, emp e2
 where e1.mgr=e2.empno;
//需要左连接
--2.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr = m.empno(+) and e.deptno = d.deptno and e.HIREDATE < m.hiredate;
****
select e.empno,nvl(m.ename,'无'),d.dname from emp e,emp m,dept d
where e.mgr=m.empno(+)
and e.deptno=d.deptno
and e.hiredate < m.hiredate
/

--3.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select d.dname,nvl(e.ename,'无')
from emp e,dept d
where e.deptno(+) = d.deptno;

--4.列出至少有一个员工的所有部门
select d.dname,count(e.empno)
from emp e,dept d
where e.deptno = d.deptno group by d.dname having count(e.empno) > 0;

--5.列出薪金比"SMITH"多的所有员工信息.
select * from emp where sal >
(select sal from emp where ename = 'SMITH');

--6.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
select e.ename,d.dname,ed.cc
from emp e,dept d,(select deptno,count(empno) cc from emp group by deptno) ed
where e.deptno = d.deptno and d.deptno = ed.deptno and e.job = 'CLERK';

select * from emp where  JOB = 'CLERK' ;
--7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
select e.job,count(e.empno)
from emp e where e.job in
(select job
from emp group by job having min(sal)>1500)
group by e.job;

--8.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
select ename from emp where deptno =
(select deptno from dept where dname = 'SALES');

--9.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
select ename 姓名,d.dname 部门名称,nvl((select m.ename from emp m where e.mgr = m.empno),'无') 上级领导,
(select grade from salgrade where e.sal between losal and hisal) 工资等级
from emp e,dept d
where e.deptno = d.deptno and e.sal >
(select avg(sal) from emp);

--或
select e.ename 姓名,d.dname 部门名称,nvl(m.ename,'无') 上级领导,s.grade 工资等级
from emp e,dept d,emp m,salgrade s
where e.deptno = d.deptno and e.sal >
(select avg(sal) from emp) and e.deptno = d.deptno and e.mgr = m.empno(+)
and e.sal between losal and hisal;

--10.列出与"SCOTT"从事相同工作的所有员工及部门名称.
select e.ename,d.dname from emp e,dept d
where e.deptno = d.deptno and e.job =
(select job from emp where ename = 'SCOTT') and e.ename <> 'SCOTT';

***
select e.ename,d.dname from emp e,dept d
where e.deptno=d.deptno and e.job =(
select job from emp where ename='SCOTT')
and e.ename<>'SCOTT';
--11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金.
select ename,sal from emp where sal in
(select sal from emp where deptno = 30) and deptno <> 30;

--12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
select e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno = d.deptno and e.sal > all (select sal from emp where deptno = 30) and e.deptno <> 30;

--13.列出在每个部门工作的员工数量,平均工资和平均服务期限.
select d.dname 部门名称,ed.c 员工数量,round(ed.a,2) 平均工资,round(ed.h,0) 服务期限
from dept d
,(select deptno,count(empno) c,avg(sal) a,avg(months_between(sysdate,hiredate)/12) h from emp group by deptno) ed
where d.deptno = ed.deptno;

--或
select d.dname 部门名称,count(e.empno) 员工数量,round(avg(e.sal),2) 平均工资,round(avg(months_between(sysdate,hiredate)/12),0) 服务期限
from emp e,dept d
where e.deptno = d.deptno group by d.dname;

--14.列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal
from emp e,dept d
where e.deptno = d.deptno;

--15.列出所有部门的详细信息和人数
select d.*,(select count(e.empno) from emp e where e.deptno = d.deptno) 人数
from dept d;
--或
select d.*,nvl(ed.c,0) 人数
from dept d,(select deptno,count(empno) c from emp group by deptno) ed
where d.deptno = ed.deptno(+);

--16.列出各种工作的最低工资及从事此工作的雇员姓名
select * from emp where sal in
(select min(sal) sal from emp group by job)

--17.列出各个部门的MANAGER(经理)的最低薪金
select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;

--18.列出所有员工的年工资,按年薪从低到高排序
select ename, (sal + nvl(comm,0)) * 12 income from emp order by income;

--19.查出某个员工的上级主管，并要求出这些主管中的薪水超过3000.
select distinct m.ename 主管, m.sal 主管工资
from emp e,emp m
where e.mgr = m.empno and m.sal > 3000;

--20.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
select d.dname 部门,count(e.sal) 工资合计,count(e.empno) 部门人数
from emp e,dept d
where e.deptno(+) = d.deptno and d.dname like '%S%' group by d.dname;

--21.给任职日期超过10年的员工加薪10%.
update emp set sal = sal + sal * 0.1 where (months_between(sysdate,hiredate)/12) > 10;