mysql练习题

0、比较同一行的值

表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

select (case when a>b then a else b end), 
	(case when b>c then b else c end)  from t_table

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

select
	e.ename,t.*
from
(select max(sal) as maxsal,deptno from emp group by deptno) t
join
	emp e
on
	t.maxsal = e.sal and t.deptno = e.deptno;
+-------+---------+--------+
| ename | maxsal  | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

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

//第一步：求出平均薪水
select
	deptno,avg(sal)
from
	emp
group by
	deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
//第二步：
select
	e.ename,t.*,e.sal
from
(select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno) t
join
	emp e
on
	e.deptno = t.deptno and e.sal >= t.avgsal;
+-------+--------+-------------+---------+
| ename | deptno | avgsal      | sal     |
+-------+--------+-------------+---------+
| ALLEN |     30 | 1566.666667 | 1600.00 |
| JONES |     20 | 2175.000000 | 2975.00 |
| BLAKE |     30 | 1566.666667 | 2850.00 |
| SCOTT |     20 | 2175.000000 | 3000.00 |
| KING  |     10 | 2916.666667 | 5000.00 |
| FORD  |     20 | 2175.000000 | 3000.00 |
+-------+--------+-------------+---------+

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

//第一步：求出部门中的薪资等级
select
	e.deptno,e.ename,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
+--------+--------+-------+
| deptno | ename  | grade |
+--------+--------+-------+
|     20 | SMITH  |     1 |
|     30 | ALLEN  |     3 |
|     30 | WARD   |     2 |
|     20 | JONES  |     4 |
|     30 | MARTIN |     2 |
|     30 | BLAKE  |     4 |
|     10 | CLARK  |     4 |
|     20 | SCOTT  |     4 |
|     10 | KING   |     5 |
|     30 | TURNER |     3 |
|     20 | ADAMS  |     1 |
|     30 | JAMES  |     1 |
|     20 | FORD   |     4 |
|     10 | MILLER |     2 |
+--------+--------+-------+
//第二步：
select
	e.deptno,e.ename,avg(s.grade)
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
group by
	e.deptno;
+--------+-------+--------------+
| deptno | ename | avg(s.grade) |
+--------+-------+--------------+
|     10 | CLARK |       3.6667 |
|     20 | SMITH |       2.8000 |
|     30 | ALLEN |       2.5000 |
+--------+-------+--------------+

4、不准用组函数（Max ），取得最高薪水

//1.降序，取第一行数据
select
	*
from
	empe
order by
	sal desc
limit 1;
//2.表的自连接
select
	sal
from
	emp
where
	sal not in(
        select
            distinct a.sal
        from
            emp a
        join
            emp b
        on
            a.sal < b.sal
) ;

5、取得平均薪水最高的部门的部门编号

//第一步：取得各部门的平均薪水
select
	deptno,avg(sal)
from
	emp
group by
	deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
//第二步：
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
order by
    avgsal desc
limit 1;
+--------+-------------+
| deptno | maxsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

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

//第一步：取得各部门平均薪水
select
	deptno,avg(sal)
from
	emp
group by
	deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
//第二步：取得平均薪水最高部门的部门编号
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
order by
	avgsal desc
limit 1;
+--------+-------------+
| deptno | maxsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
//第三步：
select
    d.dname,avg(e.sal) as avgsal
from
    emp e
join
	dept d
on
	e.deptno = d.deptno
group by
    d.dname
order by
    avgsal desc
limit 1;
+------------+-------------+
| dname      | maxsal      |
+