sql练习

1. 列出至少有一个员工的所有部门。
select distinct dept.dname from dept join emp on dept.deptno=emp.deptno;
2. 列出薪金比“SMITH”多的所有员工。
select ename from emp where sal>(select sal from emp where ename='SMITH');
select e.ename from emp e join emp m on e.sal>m.sal and m.ename='SMITH';
3. 列出所有员工的姓名及其直接上级的姓名。
select e.ename ,m.ename from emp e left join emp m on e.mgr=m.empno;
4. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称。
select e.empno , e.ename ,d.dname from emp e
join emp m on e.mgr=m.empno and e.hiredate<m.hiredate
join dept d on e.deptno = d.deptno
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.dname ,e.* from dept d
left join emp e on e.deptno=d.deptno;
6. 列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数。
select e.ename,d.dname,c.total from emp e
join dept d on e.deptno=d.deptno
join (select deptno,count(rowid) total from emp
 group by deptno) c on e.deptno=c.deptno
where e.job='CLERK' ;
7. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数。
select j.job ,c.num from (select distinct job from emp where sal >1500 ) j
join (select job ,count(rowid) num from emp group by job) c on j.job=c.job


8. 列出在部门“SALES”（销售部）工作的员工的姓名。
select e.ename from emp e
join dept d on e.deptno=d.deptno
and d.dname='SALES'
9. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
select e.ename,d.dname,m.ename,s.grade from emp e
join dept d on e.deptno=d.deptno
join emp m on e.mgr=m.empno
join salgrade s on e.sal between losal and hisal
where e.sal>(select avg(sal) from emp );
10. 列出与“SCOTT”从事相同工作的所有员工及部门名称。
select e.ename ,d.dname from emp e
join dept d on e.deptno=d.deptno
where e.job=(select job from emp where ename='SCOTT');
11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select e.ename ,e.sal from emp e
where e.sal in(select sal from emp where deptno=30);
12. 列出薪金高于在部门30工作的所有员工的薪金的员工和薪金、部门名称。
select  e.ename ,e.sal,d.dname from emp e
join dept d on e.deptno=d.deptno
where e.sal >(select max(sal) from emp where deptno=30);
13. 列出每个部门工作的员工的数量、平均工资和平均服务期限。
select count(rowid) num ,avg(sal) avgsal,round(avg(months_between(sysdate,hiredate)/12)) avgyear
from emp group by deptno


14. 列出所有员工的姓名、部门名称和工资 。
select e.ename,d.dname,e.sal from emp e
join dept d on e.deptno=d.deptno;
15. 列出所有部门的详细信息和部门人数。
select d.* , x.num from dept d
left join (select count(rowid) num ,deptno from emp group by deptno) x on d.deptno=x.deptno ;


16. 列出各种工作的最低工资及从事此工作的雇员姓名。
select e.ename,x.job,e.sal from emp e
join (select job ,min(sal) minsal from emp group by job) x on e.job=x.job and e.sal=x.minsal;


17. 列出各个部门的MANAGER(经理)的最低薪金。
 select deptno,min(sal) from emp where job='MANAGER' group by deptno
18. 列出所有员工的年工资，按年薪从低到高排序。
 select ename ,sal*12 yearsal from emp order by yearsal;
19. 查出某个员工的上级主管，并要求出这些主管中的薪水超过3000
select e.ename , m.ename , m.sal from emp e
join emp m on e.mgr=m.empno and m.sal>3000;
20. 求出部门名称中带“S”字符的部门员工的工资合计、部门人数。
select deptno,sum(sal) sumsal,count(rowid) num from emp 
where deptno in(select deptno from dept where instr(dname,'S')>0)
group by deptno;


select d.dname,c.csal,c.cnum from dept d
left join (select deptno ,count(rowid) cnum,sum(sal) csal from emp group by deptno) c 
on d.deptno=c.deptno 
where d.deptno in(select deptno from dept where instr(dname,'S')>0)




21. 求出部门平均月薪最高的部门名和平均月薪。
select d.dname,x.avgsal from dept d
join (select deptno , avg(sal) avgsal from emp group by deptno) x on d.deptno=x.deptno
where x.avgsal=(select max(avg(sal)) from emp group by deptno);




select d.dname,avg(e.sal) from emp e
join dept d on e.deptno=d.deptno
group by d.dname
having avg(e.sal)=(select  max(avg(sal)) from emp group by deptno)