|
|
用的是自帶的emp,salgrade,dept表,Oracle11G下測(cè)試通過(guò)。
--1.列出所有員工及直接上級(jí)的姓名
select e.ename,nvl(m.ename,'無(wú)')
from emp e,emp m
where e.mgr = m.empno(+);
--2.列出受雇日期早于其直接上級(jí)的所有員工的編號(hào),姓名,部門(mén)名稱(chēng)
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.列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息,同時(shí)列出那些沒(méi)有員工的部門(mén).
select d.dname,nvl(e.ename,'無(wú)')
from emp e,dept d
where e.deptno(+) = d.deptno;
--4.列出至少有一個(gè)員工的所有部門(mén)
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"(辦事員)的姓名及其部門(mén)名稱(chēng),部門(mén)的人數(shù).
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的各種工作及從事此工作的全部雇員人數(shù).
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.列出在部門(mén)"SALES"<銷(xiāo)售部>工作的員工的姓名,假定不知道銷(xiāo)售部的部門(mén)編號(hào).
select ename from emp where deptno =
(select deptno from dept where dname = 'SALES');
--9.列出薪金高于公司平均薪金的所有員工,所在部門(mén),上級(jí)領(lǐng)導(dǎo),雇員的工資等級(jí).
select ename 姓名,d.dname 部門(mén)名稱(chēng),nvl((select m.ename from emp m where e.mgr = m.empno),'無(wú)') 上級(jí)領(lǐng)導(dǎo),
(select grade from salgrade where e.sal between losal and hisal) 工資等級(jí)
from emp e,dept d
where e.deptno = d.deptno and e.sal >
(select avg(sal) from emp);
--或
select e.ename 姓名,d.dname 部門(mén)名稱(chēng),nvl(m.ename,'無(wú)') 上級(jí)領(lǐng)導(dǎo),s.grade 工資等級(jí)
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"從事相同工作的所有員工及部門(mén)名稱(chēng).
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.列出薪金等于部門(mén)30中員工的薪金的所有員工的姓名和薪金.
select ename,sal from emp where sal in
(select sal from emp where deptno = 30) and deptno <> 30;
--12.列出薪金高于在部門(mén)30工作的所有員工的薪金的員工姓名和薪金.部門(mén)名稱(chēng).
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.列出在每個(gè)部門(mén)工作的員工數(shù)量,平均工資和平均服務(wù)期限.
select d.dname 部門(mén)名稱(chēng),ed.c 員工數(shù)量,round(ed.a,2) 平均工資,round(ed.h,0) 服務(wù)期限
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 部門(mén)名稱(chēng),count(e.empno) 員工數(shù)量,round(avg(e.sal),2) 平均工資,round(avg(months_between(sysdate,hiredate)/12),0) 服務(wù)期限
from emp e,dept d
where e.deptno = d.deptno group by d.dname;
--14.列出所有員工的姓名、部門(mén)名稱(chēng)和工資。
select e.ename,d.dname,e.sal
from emp e,dept d
where e.deptno = d.deptno;
--15.列出所有部門(mén)的詳細(xì)信息和人數(shù)
select d.*,(select count(e.empno) from emp e where e.deptno = d.deptno) 人數(shù)
from dept d;
--或
select d.*,nvl(ed.c,0) 人數(shù)
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.列出各個(gè)部門(mén)的MANAGER(經(jīng)理)的最低薪金
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.查出某個(gè)員工的上級(jí)主管，并要求出這些主管中的薪水超過(guò)3000.
select distinct m.ename 主管, m.sal 主管工資
from emp e,emp m
where e.mgr = m.empno and m.sal > 3000;
--20.求出部門(mén)名稱(chēng)中,帶'S'字符的部門(mén)員工的工資合計(jì)、部門(mén)人數(shù).
select d.dname 部門(mén),count(e.sal) 工資合計(jì),count(e.empno) 部門(mén)人數(shù)
from emp e,dept d
where e.deptno(+) = d.deptno and d.dname like '%S%' group by d.dname;
--21.給任職日期超過(guò)10年的員工加薪10%.
update emp set sal = sal + sal * 0.1 where (months_between(sysdate,hiredate)/12) > 10;
|