数据库sql语言05-sql练习

-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;