-1.查询平均工资最高的部门的部门编号、部门名称和该部门的平均工资
select e.deptno,d.dname,avg(sal) from empe,dept d where e.deptno=d.deptno group by
e.deptno,dname havingavg(sal)>=all(select avg(sal) from emp group by deptno);
--2.查询所有员工的年薪、所在部门的名称,查询结果按年薪从低往高排序
select e.sal*12+nvl(comm,0) asannual_sal,d.dname from emp e, dept d order by annual_sal asc;
--3.查询每种工作的最低工资,以及领取该工资的员工姓名,查询结果显示工作名称、最低工资、领取该工资的员工姓名
select e1.ename,e2.minsal,e2.job from empe1,(select min(sal) as minsal,job from emp group by job) e2 where e1.job=e2.job;
--4.查询出管理员工人数最多的人的名字和他管理的人的名字
selecte1.ename,e2.ename from emp e1,
(select enamefrom emp where mgr =(select mgr from emp group by mgr having count(*)=(selectmax(count(*)) from emp group by mgr)))e2
where empno=(select mgr from emp group by mgr having count(*)=(selectmax(count(*)) from emp group by mgr));
--5.查询所有员工的编号、姓名,及其上级领导的编号、姓名。显示结果按领导的年薪降序排列
selecte.empno,e.ename,m.ename,m.empno,(m.sal+NvL(m.comm,0))*12 as annual_sal from empe,emp m where e.mgr=m.empno order by annual_sal desc;
--6.?查询所有领取奖金和不领取奖金的员工人数、平均工资;查询结果的列名分别为:人数、平均工资;第一行为有奖金的员工,第二行为没有奖金的员工
select avg(sal),count(*)num,nvl2(comm,1,0)comm from emp group by nvl2(comm,1,0)
order by comm desc;
--7.?查询工资不超过2500的人数最多的部门名称和该部门工资不超过2500的员工的员工人数
select max(deptno)from emp group by deptnohaving max(count(*)) ;
select dname,a.* from dept d,(selectdeptno,count(1)num from emp where sal<2500
group by deptno)a where a.deptno=d.deptnoand deptno=(select deptno from a having max(num));
--8查询受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
selecte.ename,e.deptno,e.hiredate,m.hiredate,d.dname from emp e,emp m,dept d wheree.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
--9查询至少有4个员工的部门的部门名称
select d.dname from dept d where deptnoin(select deptno from emp group by deptno having count(*)>=4);
--10.查询工资比“SMITH”高的员工的基本信息
select * from emp where sal>(select salfrom emp where ename='SMITH');
--.11?查询部门名称中带'S'字符的部门的员工的工资总和部门人数,显示结果为部门名称,部门员工的工资总和,部门人数
select d.dname,e.sum_sal,e.c from deptd,(select sum(sal) sum_sal,count(*) c from emp where d.deptno=(select deptnofrom dept where dname like's%')) e groupby deptno;
select sum(sal) sum_sal,count(*) c from empwhere deptno=(select deptno from dept where dname like's%')
select deptno from dept where dnamelike'%s%'
select deptno,sum(sal),count(empno) fromemp where deptno in(select deptno from dept
where dname like'%s%')
select * from dept;
group by deptno;
--12.?查询所有从事"CLERK"工作的雇员所在部门的部门名称、部门里的人数
select e.ename,d.* from emp e,(select e.deptno,d.dname, count(*) fromemp e,dept d group by e.deptno,d.dname)dwhere e.deptno=d.deptno and e.job='CLERK';
--13.?查询雇员领导的基本信息,要求领导的薪水要超过3000
select e1.ename from emp e1,emp e2 wheree1.empno=e2.mgr and e1.sal>=3000;
--14.?查询在"sales"部门(销售部)工作的员工的姓名
select e.ename from emp e where deptno=(selectdeptno from dept where dname='SALES');
--15.?查询工资高于30号部门的所有员工的工资的员工姓名、工资及部门名称
select e.ename,e.sal,d.dname from empe,dept d where e.sal>all(select sal from emp where deptno=30);
--16.?查询所有部门的详细信息(部门编号、部门名称)和部门人数
select * from dept a left join(selectdeptno,count(*) from emp group by deptno)b on a.deptno=b.deptno;
--17.?显示每个部门中每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资
select a.deptno,a.job,a.avgsal,b.avgsal2,c.avgsal3 from
(select job,deptno,avg(sal) as avgsal from emp group by deptno,job) a left join
(select deptno, avg(sal) as avgsal2 from emp group by deptno) b on a.deptno=b.deptno
left join (select job , avg(sal) as avgsal3 from emp group by job)c on a.job=c.job
--18显示与"BLAKE"同部门的所有员工的基本信息,但不显示"BLAKE"的基本信息
select * from emp where deptno=(selectdeptno from emp where ename='BLAKE')and ename<>'BLAKE';
--19.?查询出“KING”所在部门的部门编号、部门名称以及该部门里的员工人数
select emp.deptno,dname,count(*) fromemp,dept where emp.deptno=dept.deptno and emp.deptno=(select deptno from emp
where ename='KING')group by emp.deptno,dname;
--20.?查询出"WARD"所在部门的工作年限最大的员工的姓名
select ename from emp wherehiredate=(select min(hiredate) from emp where deptno=(select deptno from empwhere ename='WARD' ))
--21.?查询出没有下属的员工的姓名及他的职位
select ename,job from emp where empno notin(select mgr from emp where mgr is not null);
--22.查询出员工姓名以A开头的人数最多的部门的部门名称
select d.dname from dept d where deptnoin(select deptno from emp where ename like 'A%' group by deptno
having count(*)>=all(select count(*)from emp where ename like'A%' group by deptno));
--23.?查询出SMITH所在部门的部门名称、部门工资的平均值(注意平均值保留两位小数)
select d.dname,to_char(avg(sal),'9999.99') from empe,dept d where e.deptno=d.deptno and e.deptno=(select deptno from emp
where ename='SMITH')group bye.deptno,d.dname;