Oracle高级查询练习题2

Oracle⾼级查询练习题2
--1.查询平均⼯资最⾼的部门的部门编号，部门名称，和该部门的平均⼯资
select d.deptno,d.dname,t.pingjun from dept d inner join(
select deptno,round(avg(sal+nvl(comm,0))) pingjun from emp group by deptno
)t on d.deptno=t.deptno where t.pingjun=(
select max(round(avg(sal+nvl(comm,0))))from emp group by deptno
);
--2 查询所有员⼯的年薪，所在部门的名称，结果按年薪低到⾼排列
select e.ename,d.dname,round((sal+nvl(comm,0))*12) as nianxin from emp e inner join dept d on e.deptno=d.deptno order by nianxin desc;
--3查询每种⼯作的⼯作名称，最低⼯资，领取该最低⼯资员⼯的姓名
select t.job,t.minsal,e.ename,e.sal from (
select job,min(sal) as minsal from emp group by job
)t inner join emp e on t.job=e.job where e.sal=t.minsal;
--4查询出管理员⼯⼈数最多的⼈和他管理的⼈的名字
select e2.ename, e1.ename, t.⼈数
from (select count(e.ename) as⼈数, e.mgr from emp e group by e.mgr) t
inner join emp e1
on t.mgr = e1.empno
inner join emp e2
on e2.mgr = t.mgr
where t.⼈数=
(select max(count(e1.ename)) as max_num from emp e1 group by e1.mgr)
--5查询所有员⼯的编号、姓名，及其上级领导的编号、姓名。

显⽰结果按领导的年薪降序排列
select e1.empno,e1.ename,e2.empno,e2.ename,(e2.sal+nvl(m,0))*12 nianxin from emp e1 inner join emp e2 on e1.mgr=e2.empno order by nianxin desc;
--6查询所有领取奖⾦和不领取奖⾦的员⼯⼈数、平均⼯资；查询结果的列名分别为：⼈数、平均⼯资；第⼀⾏为有奖⾦的员⼯，第⼆⾏为没有奖⾦的员⼯
select count(ename) ⼈数,avg(sal+nvl(comm,0)) from emp where nvl(comm,0)!=0
union
select count(ename) ⼈数,avg(sal+nvl(comm,0)) from emp where nvl(comm,0)=0
--7查询⼯资不超过2500的⼈数最多的部门名称和该部门⼯资不超过2500的员⼯的员⼯⼈数
select d1.dname,t.renshu from dept d1 inner join (
select deptno,count(ename) renshu from emp where (sal+nvl(comm,0))<2500group by deptno
)t on d1.deptno=t.deptno where t.renshu=(
select max(count(ename)) renshu from emp where (sal+nvl(comm,0))<2500group by deptno
);
--8查询受雇⽇期早于其直接上级的所有员⼯的编号，姓名，部门名称
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;
--10查询⼯资⽐“SMITH”⾼的员⼯的基本信息
select*from emp where sal>(
select sal from emp where ename='SMITH'
);
--9查询⾄少有4个员⼯的部门的部门名称
select d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname having count(ename)>3;
--11查询部门名称中带'S'字符的部门的员⼯的⼯资总和部门⼈数，显⽰结果为部门名称，部门员⼯的⼯资总和，部门⼈数
--不推荐使⽤d.dname分组，因为可能dname不唯⼀
select d.dname 部门名称,sum(e.sal+nvl(m,0)) ⼯资总和，count(e.ename) 部门⼈数from dept d left outer join emp e on d.deptno=e.deptno where d.dname like'%S%'group by d.dname; --更优化
select d.dname, b.r, b.s
from dept d
left join (select count(rowid) r, sum(sal+nvl(comm,0)) s, deptno
from emp
group by deptno) b
on d.deptno = b.deptno
where d.dname like'%S%';
--12查询所有从事"CLERK"⼯作的雇员所在部门的部门名称、部门⾥的⼈数
select d.dname,count(ename) from dept d inner join emp e on d.deptno=e.deptno where d.deptno in(
select distinct(deptno) from emp where job='CLERK'
) group by d.dname;
--13查询雇员领导的基本信息，要求领导的薪⽔要超过3000
select*from emp where empno=(
select e1.mgr from emp e1 inner join emp e2 on e1.mgr=e2.empno where e2.sal>3000group by e1.mgr
);
--14查询在"sales"部门（销售部）⼯作的员⼯的姓名
select e.*from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES';
--15查询⼯资⾼于30号部门的所有员⼯的⼯资的员⼯姓名、⼯资及部门名称
select e.ename,(e.sal+nvl(comm,0)),d.dname from emp e inner join dept d on e.deptno=d.deptno where (e.sal+nvl(comm,0))>all(
select sal from emp where deptno=30
);
--16查询所有部门的详细信息（部门编号、部门名称）和部门⼈数
select d.deptno,d.dname,count(e.ename) from dept d inner join emp e on d.deptno=e.deptno group by d.deptno,d.dname;
--17显⽰每个部门中每个岗位的平均⼯资、每个部门的平均⼯资、每个岗位的平均⼯资（没看懂题⽬要求，好像有点问题）
--18显⽰与"BLAKE"同部门的所有员⼯的基本信息，但不显⽰"BLAKE"的基本信息
select*from emp where deptno =(
select deptno from emp where ename='BLAKE'
) and ename<>'BLAKE';
--19查询出“KING”所在部门的部门编号、部门名称以及该部门⾥的员⼯⼈数
select t.deptno,d.dname,t.renshu from(
select e.deptno,count(e.ename) renshu from emp e group by e.deptno
)t left join dept d on t.deptno=d.deptno where t.deptno=(
select deptno from emp where ename='KING'
)
--20查询出"WARD"所在部门的⼯作年限最⼤的员⼯的姓名
select t.ename from(
select ename,round(sysdate-hiredate) worktime from emp where deptno=(
select deptno from emp where ename='WARD'
)
)t where t.worktime=(
select max(round(sysdate-hiredate)) worktime from emp where deptno=(
select deptno from emp where ename='WARD'
)
)
--21查询出没有下属的员⼯的姓名及他的职位
select e1.ename,e1.job from emp e1 left outer join emp e2 on e1.empno=e2.mgr where e2.empno is null; --22查询出员⼯姓名以A开头的⼈数最多的部门的部门名称
select*from dept d inner join (
select deptno,count(ename) renshu from emp where ename like'A%'group by deptno
)t on d.deptno=t.deptno where t.renshu=(
select max(count(ename)) from emp where ename like'A%'group by deptno
)
--23查询出SMITH所在部门的部门名称、部门⼯资的平均值
select e.deptno,round(avg(e.sal+nvl(m,0))) pingjun from emp e where d.deptno=( select deptno from emp where ename='SMITH'
) group by d.deptno;。