|
|
用的是自帶的emp,salgrade,dept表,Oracle11G下測試通過。
--1.列出所有員工及直接上級的姓名
select e.ename,nvl(m.ename,'無')
from emp e,emp m
where e.mgr = m.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;
--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';
--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;
|