MySQL练习题2

SQL练习题二

1、列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资
2、列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名
3、列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列
4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
5、列出部门名称和这些部门的员工信息(数量、平均工资)，同时列出那些没有员工的部门
6、列出所有CLERK的姓名及其部门名称，部门的人数，工资等级
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资
8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道该部门的部门编号
9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
10、列出与SCOTT从事相同工作的所有员工及部门名称，部门人数
11、列出公司各个工资等级雇员的数量、平均工资
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
13、列出在每个部门工作的员工数量、平均工资和平均服务期限
14、列出所有员工的姓名、部门名称和工资
15、列出所有部门的详细信息和部门人数
16、列出各种工作的最低工资及从事此工作的雇员姓名
17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数
18、列出所有员工的年工资，所在部门名称，按年薪从低到高排序
19、列出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
21、给任职日期超过30年或者1987年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，以此类推

1、列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资

mysql> select e.deptno,d.dname,avg(e.sal),max(e.sal),min(sal),count(*) from dept d right join emp e one.deptno = d.deptno group by e.deptno;

2、列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名

mysql> select a.empno,a.ename,d.dname,e.ename from (select * from emp where sal>(select min(sal) from emp where ename in ('smith','allen'))) a left join emp e on a.mgr =e.empno left join dept d on d.deptno=a.deptno;

3、列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列

mysql> select a.empno,a.ename,a.sal,b.empno,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno order by b.sal*12 desc;

4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数

mysql> select a.empno,a.ename,d.dname,d.loc from emp a join emp b on a.mgr=b.empno join dept d on a.deptno=d.deptno where a.hiredate<b.hiredate;

5、列出部门名称和这些部门的员工信息(数量、平均工资)，同时列出那些没有员工的部门

mysql> select d.dname, e.ename,e.job from emp e right join dept d on e.deptno=d.deptno;

6、列出所有CLERK的姓名及其部门名称，部门的人数，工资等级

mysql> select e.ename,d.dname,count(empno),s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e..job = 'CLERK'and (e.sal between s.losal and s.hisal) group by e.ename,d.dname,s.grade;

7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资

mysql> select e.job,count(e.empno),d.dname,d.loc,avg(e.sal) from emp e,dept d where e.deptno=d.deptno and e.saal>1500 group by e.job,d.dname,d.loc;

8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道该部门的部门编号

mysql> select e.ename,e.sal,e.hiredate,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='sales';

9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

mysql> select e.*, d.dname, m.ename, s.grade from emp e natural left join dept d left join emp m on m.empno=e..mgr left join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp);

10、列出与SCOTT从事相同工作的所有员工及部门名称，部门人数

mysql> select e.ename,d.dname 部门 from emp e,dept d where e.deptno=d.deptno and e.job=(select job from emp where ename='scott');

11、列出公司各个工资等级雇员的数量、平均工资

mysql> select s.grade,count(e.empno),avg(e.sal) from emp e,salgrade s where e.sal between losal and hisal group by s.grade order by s.grade;

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

mysql> select e.ename,e.sal,d.dname from emp e, dept d where e.sal>(select max(sal) from emp where deptnno=30) and e.deptno=d.deptno;

13、列出在每个部门工作的员工数量、平均工资和平均服务期限

mysql> select count(empno),avg(sal),avg((to_days(now())-to_days(hiredate))/365) from emp group by deptno;

14、列出所有员工的姓名、部门名称和工资

mysql> select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno;

15、列出所有部门的详细信息和部门人数

mysql> select d.deptno,d.dname,d.loc,count(e.empno) from emp e right join dept d on e.deptno=d.deptno group by d.deptno;

16、列出各种工作的最低工资及从事此工作的雇员姓名

mysql> select e.ename,e.sal from emp e inner join ( select job ,min(sal) minSal from emp group by job) a on e.job=a.job and e.sal=a.minSal;

17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数

mysql> select e.ename,e.sal,d.dname,a.count from emp e,(select deptno dno,min(sal) sal from emp where job='MANAGER' group by deptno) temp,dept d,(select deptno dno,count(empno) count from emp group by deptno) a where e.deptno=temp.dno and e.sal=temp.sal and e.job='MANAGER' and e.deptno=d.deptno and a.dno=d.deptno;

18、列出所有员工的年工资，所在部门名称，按年薪从低到高排序

mysql> select d.dname,sal*12 年薪 from emp e,dept d where e.deptno=d.deptno order by e.sal12;

19、列出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000

mysql> select e1.ename,e2.ename 领导 from emp e1 inner join emp e2 on e1.mgr=e2.empno where e2.sal>3000;

20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数

mysql> select d.deptno,d.dname,ifnull(sum(sal),0) ,count(empno) from emp e right join dept d on e.deptno=d.deptno where d.dname like '%S%' group by d.deptno;

21、给任职日期超过30年或者1987年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，以此类推

mysql> select sal, sal*(1+ deptno/100) 涨薪 from emp where adddate(curdate(), interval -30 year) > hiredate or year(hiredate) = 1987;