SQL语句练习

已知数据库表

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

1.取出每个部门最高薪水的人员名称

select ename,d.* from emp e join (select max(sal) sal,deptno from emp group by deptno) d on e.sal=d.sal and e.deptno=d.deptno;

2.哪些人的薪水在部门的平均薪水之上

select ename,emp.sal from (select deptno,avg(sal) sal from emp group by deptno) t join emp on emp.deptno=t.deptno where emp.sal>t.sal; 

3.取得所有部门的平均薪水等级

select deptno,avg(grade)  from emp e join salgrade s on e.sal between s.losal and hisal group by deptno

4.不使用组函数max，获取最高薪水(给出两种)——①内连接②降序排列然后取出第一个(求最大最小值都可以用order by xx limit的思想)③找出存在比别人小的数据，然后not in，相当于取反的思想。

select e.sal from emp e join emp t on e.sal>=t.sal group by e.sal having count(e.sal)=(select count(*) from emp);
-----
select sal from emp order by sal desc limit 0,1;
-----
select sal from emp where empno not in (select e.empno from emp e join emp j on e.sal <j.sal);

5.取得平均薪水最高的部门的部门编号(两种)

select s.deptno from (select deptno,avg(sal) sal from emp group by deptno) s where sal=(select max(t.sal) from (select avg(sal) sal from emp group by deptno) t);
把条件写在having中
select deptno,avg(sal) from emp group by deptno having avg(sal)=(select  max(sal)  from (select avg(sal) sal from emp group by deptno )as t);
-----
select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1

6.取得平均薪水最高的部门的名称

select dname from dept where deptno=(select deptno from emp group by deptno order by avg(sal) desc limit 0,1)

7.求平均薪水的等级最低的部门的部门名称(不考虑多个相同)

select dname from dept where deptno=(select s.deptno from (select deptno,avg(sal) sal from emp  group by deptno) s where sal=(select min(t.sal) from (select avg(sal) sal from emp group by deptno) t));
-----考虑多个最低等级
select  d.dname  from
(select deptno,avg(sal) sal from emp group by deptno) as t
join salgrade s on t.sal between losal and hisal join dept d on t.deptno=d.deptno  where 
s.grade=(select min(s.grade) from (select deptno,avg(sal) sal from emp group by deptno) as t join salgrade s on t.sal between losal and hisal );

8.取得比普通员工的最高薪水还要高的领导人姓名

select ename,sal from emp where sal>(select max(sal) from (select sal from emp where empno not in(select distinct  mgr from emp where mgr is not null))as t);

9.取得薪水最高的前五名员工

select ename,sal from emp order by sal desc,ename desc limit 0,5;

10.取得薪水最高的第六到第十名员工

select ename,sal from emp order by sal desc,ename desc limit 5,5;

11.取得最后入职的5名员工

select ename,hiredate from emp order by hiredate desc,ename asc limit 0,5;

12.取得每个薪水等级有多少员工

select grade, count(grade)  from( select grade from emp e join salgrade s on e.sal between s.losal and hisal )as t group by grade;

13.面试题

查找没选黎明老师课的学生
select sname from S where sno not in(
select sno
from SC join C on C.cno=SC.cno
where cteacher='黎明'
);
列出两门及以上不及格同学的姓名和平均成绩
select sname,avg(scgrade) from sc join s on sc.sno=s.sno where s.sno in(
select sno from sc where scgrade <'60'
group by sno having count(sno)>=2
) group by sname;
学过1号和2号课程的所有学生姓名
select sname from s where sno in(
select sno  from sc where cno='1' and sno in(select sno from sc where cno='2')
);

14.列出所有员工及领导的姓名

select e.ename worker,ifnull(t.ename,'无上级领导') boss from emp e left join emp t on e.mgr=t.empno;

15.列出受雇日期早于其直接上级的所有员工编号，姓名，部门名称

select e.empno,e.ename,dname from emp e join emp t on e.mgr=t.empno join dept on e.deptno=dept.deptno where e.hiredate<t.hiredate;

16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select e.*,d.dname  from emp e right join dept d on e.deptno=d.deptno;

17.列出至少有5个员工的所有部门

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

18.列出薪金比“SMITH"多的所有员工信息

select * from emp where sal > (select sal from emp where ename='smith')

19.列出所有"clerk"的姓名以及部门名称、部门人数

select ename,d.dname,count from emp join dept d on emp.deptno=d.deptno join (select deptno,count(deptno) count from emp group by deptno)as t on d.deptno=t.deptno  where job='clerk';

20.列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

select job,min(sal),count(job) from emp group by job having min(sal)>1500;

21.列出部门在sales的员工姓名

select ename from emp join dept d on emp.deptno=d.deptno where dname='sales';

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

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

23.列出与scott从事相同工作的所有员工及部门名称

select e.ename,dname from emp e join dept d on e.deptno=d.deptno where job in (select job from emp where ename='scott') and ename <> 'scott';

24.列出薪金等于部门30里的员工薪金的其它员工的姓名和薪金

select ename,sal from emp where sal in(select sal from emp where deptno=30) and deptno <> 30

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

select ename,sal,dname from emp e join dept d on e.deptno=d.deptno where sal>(select max(sal) from emp where deptno=30)

26.列出在每个部门工作的员工数量、平均工资和平均服务期限。关键点是group by分组的对象一定要为emp表中的deptno，这样count(null)才为0

————使用timestampdate
select count(e.deptno),ifnull(avg(sal),0),avg(timestampdiff(day,hiredate,now())) from emp e right join dept d on e.deptno=d.deptno group by e.deptno;
————使用datediff
select count(e.deptno),ifnull(avg(sal),0),avg(datediff(now(),hiredate)) from emp e right join dept d on e.deptno=d.deptno group by e.deptno;

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

select ename,dname,sal from emp e join dept d on e.deptno=d.deptno;

28.列出所有部门的详细信息和人数

select d.*,count(e.deptno)as '数量' from emp e right join dept d on e.deptno=d.deptno group by e.deptno

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

select ename,t.sal from emp e join (select min(sal) sal,job from emp  group by job) t on e.job=t.job where e.sal=t.sal

30.列出各个部门的manager的最低薪金

select deptno,min(sal)  from emp where job='manager' group by deptno

31.列出所有员工的年工资，按年薪从低到高排序

select ename,12*(sal+ifnull(comm,0)) income from emp order by income;

32.求出员工领导的薪水超过3000的员工名称和领导名称

select  e.ename,t.ename from emp e join emp t on e.mgr=t.empno where t.sal>3000;

33.求出部门名称中，带有'S'字符的部门员工的工资合计、部门人数

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

34.给任职日期超过30年的员工加薪10%

update  emp  set sal=sal*1.1   where date_format(now(),'%Y') -date_format(hiredate,'%Y')>30 
update emp set sal=sal*1.1 where timestampdiff(year,hiredate,now())>30