Oracle高级查询练习题

Oracle⾼级查询练习题
--1. 列出⾄少有⼀个员⼯的部门名称select d.dname from dept d inner join (
select count(ename),deptno from emp group by deptno having count(ename)>0
)t on d.deptno=t.deptno;
--2. 列出⽉薪⽐“SMITH”多的所有员⼯。

select*from emp where sal>(
select sal from emp where ename='SMITH'
);
--3. 列出所有员⼯的姓名及其直接上级的姓名。

select e1.ename,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno;
--4. 列出受雇⽇期早于其直接上级的所有员⼯的编号、姓名、部门名称。

select e1.empno,e1.ename,d.dname from emp e1
inner join emp e2 on e1.mgr=e2.empno
inner join dept d on e1.deptno=d.deptno
where e1.hiredate<e2.hiredate;
--5. 列出部门名称和这些部门的员⼯信息，同时列出那些没有员⼯的部门。

select d.dname,e.*from dept d left outer join emp e on e.deptno=d.deptno;
--6. 列出所有“CLERK”（办事员）的姓名及其部门名称、部门的⼈数。

select m.ename,m.job, p.dname, p.renshu from emp m
inner join (
select d.deptno, d.dname, count(e.empno) renshu from emp e inner join dept d on e.deptno = d.deptno group by d.dname, d.deptno
) p on m.deptno = p.deptno where m.job ='CLERK';
--7. 列出最低⽉薪⼤于1500的各种⼯作及从事此⼯作的全部雇员⼈数。

select job,sal from emp;
select t1.job,t2.renshu from (
select job from emp group by job having min(sal)>1500
)t1 inner join (
select job,count(ename) from emp group by job
)t2 on t1.job=t2.job;
--8. 列出在部门“SALES”（销售部）⼯作的员⼯的姓名。

select e.ename from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES';
--9. 列出⽉薪⾼于公司平均⽉薪的所有员⼯，所在部门，上级领导，⼯资等级。

select e1.ename,d.dname,e2.ename,s.grade from emp e1 left outer join dept d on e1.deptno=d.deptno
left outer join emp e2 on e1.mgr=e2.empno
left outer join salgrade s on (e1.sal+nvl(m,0)) between s.losal and s.hisal
where (e1.sal+nvl(m,0))>(
select round(avg(e.sal + nvl(m, 0))) from emp e
);
--10. 列出与“SCOTT”从事相同⼯作的所有员⼯及部门名称。

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno where e.job=(
select job from emp where ename='SCOTT'
) and e.ename <>'SCOTT';
--11. 列出⽉薪等于部门30中员⼯的⽉薪的所有员⼯的姓名和⽉薪。

select ename,sal from emp e where sal in (
select distinct(sal) from emp where deptno=30
);
--12. 列出⽉薪⾼于在部门30⼯作的所有员⼯的⽉薪的员⼯的姓名、⽉薪、部门名称。

select e.ename,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno where sal >all (
select distinct(sal) from emp where deptno=30
);
--13. 列出每个部门⼯作的员⼯的数量、平均⼯资和平均服务期限。

select count(ename) 员⼯数量，floor(avg(sal+ nvl(comm, 0))) as平均⼯资,floor(avg(sysdate-hiredate)) as平均服务期限from emp group by deptno; --14. 列出所有员⼯的姓名、部门名称和部门平均⼯资。

select m.ename, t.dname, t.b_avg
from emp m
left outer join (select d.deptno,
d.dname,
round(avg(e.sal + nvl(m, 0))) b_avg
from emp e
inner join dept d
on e.deptno = d.deptno
group by d.deptno, d.dname) t
on m.deptno = t.deptno;
--15. 列出所有部门的详细信息和部门⼈数。

select d.*,t.renshu from dept d inner join (
select deptno,count(ename) as renshu from emp group by deptno
)t on d.deptno=t.deptno ;
--16. 列出各种⼯作的最低⼯资及从事此⼯作的员⼯姓名。

select e.ename,t.minsal from emp e inner join (
select job,min(sal+ nvl(comm, 0)) minsal from emp group by job
)t on e.job=t.job;
--17. 列出各个部门的MANAGER(经理)的最低⽉薪。

select deptno,min(sal) from emp where job='MANAGER'group by deptno;
--18. 列出所有员⼯的年总收⼊，按年总收⼊从低到⾼排序。

select ename,(sal+nvl(comm,0))*12from emp order by (sal+nvl(comm,0))*12;
--19. 列出员⼯的上级主管信息，并要求这些主管的⽉薪超过3000。

select*from emp where empno in(
select e1.mgr from emp e1 inner join emp e2 on e1.mgr=e2.empno group by e1.mgr ) and sal>3000;
--20. 列出部门名称中带“S”字符的部门员⼯的⽉薪合计、部门⼈数。

select d.dname,t.heji,t.renshu from dept d inner join (
select deptno,count(ename) renshu,sum(sal) heji from emp group by deptno
)t on d.deptno=t.deptno where instr(d.dname,'S',1)>0;
--21. 求出部门平均⽉薪最⾼的部门名和平均⽉薪。

select d.dname,t.pingjun from dept d inner join (
select deptno,floor(avg(sal+ nvl(comm, 0))) pingjun from emp group by deptno
)t on d.deptno=t.deptno where t.pingjun=(
select max(floor(avg(sal))) pingjun from emp group by deptno
);。