mysql 35道题

1：取得每个部门最高薪水的人员名称

 select deptno,max(sal)as maxsal from emp group by deptno


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

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

1：部门平均薪水

2；表连接

select deptno, avg(sal)as avgsal from emp group by deptno
找出每个部门的平均薪水
select d.deptno, e.ename,e.sal from emp e join (select deptno, avg(sal)as avgsal from emp group by deptno) d on d.deptno=e.deptno and e.sal>d.avgsal
t表和e表连接，条件式部门编号相同，emp的薪水高于平均薪水

3：取得部门中所有人的平均薪水等级

   每个部门，所有人的薪水等级

再平均

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

4:不准用组函数Max,取得最高薪水（给出2中解决方案）

select * from emp order by sal desc limit 1;

 select a.sal from emp a join emp b on a.sal<b.sal;
+---------+
| sal     |
+---------+
|  800.00 |
| 1250.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
|  800.00 |
| 1100.00 |
|  950.00 |
|  800.00 |
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
|  800.00 |
| 1100.00 |
|  950.00 |
|  800.00 |
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2450.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
|  800.00 |
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
|  800.00 |
| 1250.00 |
| 1250.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
|  800.00 |
|  950.00 |
|  800.00 |
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
|  800.00 |
| 1250.00 |
| 1250.00 |
| 1100.00 |
|  950.00 |
+---------+
89 rows in set (0.00 sec)

mysql> select a.sal from emp a where a.sal not in (select a.sal from emp a join emp b on a.sal<b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

5：取得平均薪水最高的部门的部门编号（2种解决方案）

各个部门的平均薪水

select deptno,avg(sal) as avg_sal from emp group by deptno order by avg_sal desc limit 1;

第二种方案max

select max(t.avg_sal) from (select deptno,avg(sal) as avg_sal from emp group by deptno) t;
+----------------+
| max(t.avg_sal) |
+----------------+
|    2916.666667 |
+----------------+
1 row in set (0.00 sec)

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select deptno,avg(sal)from emp group by deptno having avg(sal)=(select max(t.avg_sal) from (select deptno,avg(sal) as avg_sal from emp group by deptno);
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select deptno,avg(sal)from emp group by deptno having avg(sal)=(select max(t.avg_sal) from (select deptno,avg(sal) as avg_sal from emp group by deptno) t);
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

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

1：各部门的平均薪水

select e.deptno, avg(e.sal) as avg_sal

from emp e

join 

dept d

on e.deptno=d.deptno

group by e.deptno

order by e.avg_sal

desc limit 1

select e.deptno, avg(e.sal) as avg_sal from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avg_sal desc limit 1

7:平均薪水的等级最低的部门的部门名称

1：先按部门分类求平均薪水

2：再看是第几个等级

select d.dname,avg(e.sal)as avg_sal

from emp e join dept d

on d.deptno=e.deptno

group by d.dname

order by avg_sal asc limit 1;

8:取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名。

1：普通员工的薪水

mysql> select distinct mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
7 rows in set (0.00 sec)

mysql> select max（sal) from emp where select distinct mgr from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') from emp where select distinct mgr from emp' at line 1
mysql> select max（sal) from emp where empno not in (select distinct mgr from emp);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') from emp where empno not in (select distinct mgr from emp)' at line 1
mysql> select max（sal) from emp where empno not in (select distinct mgr from emp);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') from emp where empno not in (select distinct mgr from emp)' at line 1
mysql> select max(sal) from emp where empno not in (select distinct mgr from emp);
+----------+
| max(sal) |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql> select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
1 row in set (0.00 sec)

mysql> select ename sal from emp where sal>1600;
+-------+
| sal   |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+
6 rows in set (0.00 sec)

9：取得薪水最高的前5名

select * from emp order by sal desc limit 5;

10:取得薪水最高的第6到第10名员工

select * from emp order by sal desc limit 5,5