MySQL-03 习题练习

最新推荐文章于 2023-10-17 22:50:17 发布
xiaoai_tx
最新推荐文章于 2023-10-17 22:50:17 发布
阅读量188
点赞数
文章标签： mysql 数据库
本文链接：https://blog.csdn.net/xiaoai_tx/article/details/106175660
版权

1. 找出各个部门最小薪资的人 


# 第一步：找出员工所在部门的平均工资
mysql>   (select ename, deptno, min(sal) as minsal from emp group by deptno) 
	+-------+--------+---------+
	| ename | deptno | minsal  |
	+-------+--------+---------+
	| CLARK |     10 | 1300.00 |
	| SMITH |     20 |  800.00 |
	| ALLEN |     30 |  950.00 |
	+-------+--------+---------+ 



# 完整代码
mysql> select
    ->          e.ename, d.dname, e.minsal
    -> from
    ->          (select ename, deptno, min(sal) as minsal from emp group by deptno) e
    -> right join
    ->          dept d
    -> on
    ->          d.deptno = e.deptno;
	+-------+------------+---------+
	| ename | dname      | minsal  |
	+-------+------------+---------+
	| CLARK | ACCOUNTING | 1300.00 |
	| SMITH | RESEARCH   |  800.00 |
	| ALLEN | SALES      |  950.00 |
	| NULL  | OPERATIONS |    NULL |
	+-------+------------+---------+
	4 rows in set (0.01 sec)```


3. 找出薪水大于部门平均薪水的人员
# 第一步：找出每个部门的平均工资	
mysql> select deptno ,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)


# 完整代码
mysql> select
    ->          e.ename,e.sal,e.deptno
    -> from
    ->          emp e
    -> where
    ->          (select deptno ,avg(sal) as avgsal from emp group by deptno) t
    -> \c
mysql> select
    ->          e.ename, e.sal, e.deptno
    -> from
    ->          emp e
    -> right join
    ->          (select deptno ,avg(sal) as avgsal from emp group by deptno) t
    -> on
    ->          (e.deptno = t.deptno) and (e.sal > t.avgsal);
	+-------+---------+--------+
	| ename | sal     | deptno |
	+-------+---------+--------+
	| ALLEN | 1600.00 |     30 |
	| JONES | 2975.00 |     20 |
	| BLAKE | 2850.00 |     30 |
	| SCOTT | 3000.00 |     20 |
	| KING  | 5000.00 |     10 |
	| FORD  | 3000.00 |     20 |
	+-------+---------+--------+
	6 rows in set (0.01 sec)
	

3. 找出部门中所有员工的平均的  薪水等级

# 第一步：找出所有员工的薪水等级 >>>>>取平均
mysql> select
    ->          e.ename, e.sal,e.deptno, s.grade
    -> from
    ->          emp e
    -> right join
    ->          salgrade s
    -> on
    ->           e.sal between s.losal and s.hisal;
	+--------+---------+--------+-------+
	| ename  | sal     | deptno | grade |
	+--------+---------+--------+-------+
	| SMITH  |  800.00 |     20 |     1 |
	| ALLEN  | 1600.00 |     30 |     3 |
	| WARD   | 1250.00 |     30 |     2 |
	| JONES  | 2975.00 |     20 |     4 |
	| MARTIN | 1250.00 |     30 |     2 |
	| BLAKE  | 2850.00 |     30 |     4 |
	| CLARK  | 2450.00 |     10 |     4 |
	| SCOTT  | 3000.00 |     20 |     4 |
	| KING   | 5000.00 |     10 |     5 |
	| TURNER | 1500.00 |     30 |     3 |
	| ADAMS  | 1100.00 |     20 |     1 |
	| JAMES  |  950.00 |     30 |     1 |
	| FORD   | 3000.00 |     20 |     4 |
	| MILLER | 1300.00 |     10 |     2 |
	+--------+---------+--------+-------+
	14 rows in set (0.00 sec)
# 完整代码
mysql> select
    ->          e.deptno, avg(grade) as avg_grade
    -> from
    ->          emp e
    -> right join
    ->          salgrade s
    -> on
    ->          e.sal between s.losal and s.hisal
    -> group by
    ->          deptno;
	+--------+-----------+
	| deptno | avg_grade |
	+--------+-----------+
	|     10 |    3.6667 |
	|     20 |    2.8000 |
	|     30 |    2.5000 |
	+--------+-----------+
	3 rows in set (0.01 sec)


4. 找出员工中最高的薪水。

方法一：
mysql> select ename, max(sal) from emp;
	+-------+----------+
	| ename | max(sal) |
	+-------+----------+
	| SMITH |  5000.00 |
	+-------+----------+
	1 row in set (0.01 sec) 
 	
 	
方法二：
mysql> select ename, sal from emp order by sal desc limit 1;
	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| KING  | 5000.00 |
	+-------+---------+
	1 row in set (0.01 s
	
5. 找出平均薪水最高的部门的编号

方法一：
mysql>  select deptno, avg(sal) as avg_sal from emp group by deptno order by avg_sal desc limit 1;
	+--------+-------------+
	| deptno | avg_sal     |
	+--------+-------------+
	|     10 | 2916.666667 |
	+--------+-------------+
	1 row in set (0.00 sec)
方法二：

mysql> select
    ->           deptno, avg(sal) as avg_sal
    ->  from
    ->           emp
    ->  group by
    ->           deptno
    -> having
    ->           avg_sal = (select max(t.avg_sal)  from (select  avg(sal) as avg_sal from emp group by deptno) as t);
	+--------+-------------+
	| deptno | avg_sal     |
	+--------+-------------+
	|     10 | 2916.666667 |
	+--------+-------------+
	1 row in set (0.01 sec)

	


6. 找出平均薪水最高的部门的部门名称
# 第一步：找出各个部门的平均薪水
	mysql> select deptno, avg(sal) from emp group by deptno;
	+--------+-------------+
	| deptno | avg(sal)    |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
	3 rows in set (0.01 sec)
# 第二步：将部门名称与部门编号用表连接一一对应
mysql> select
    ->          d.dname, t.avg_sal
    -> from
    ->          (select deptno, avg(sal) as avg_sal from emp group by deptno) t
    -> right join
    ->          dept d
    -> on
    ->          t.deptno = d.deptno;
	+------------+-------------+
	| dname      | avg_sal     |
	+------------+-------------+
	| ACCOUNTING | 2916.666667 |
	| RESEARCH   | 2175.000000 |
	| SALES      | 1566.666667 |
	| OPERATIONS |        NULL |
	+------------+-------------+
	4 rows in set (0.01 sec)
# 完整代码
mysql> select
    ->          d.dname, max(t.avg_sal)
    -> from
    ->          (select deptno, avg(sal) as avg_sal from emp group by deptno) t
    -> right join
    ->          dept d
    -> on
    ->           t.deptno = d.deptno;
	+------------+----------------+
	| dname      | max(t.avg_sal) |
	+------------+----------------+
	| ACCOUNTING |    2916.666667 |
	+------------+----------------+
	
7. 找出平均薪水的等级最低的部门的部门名称
# 第一步：先找出每个部门的平均薪水
mysql> select deptno, avg(Sal) as avg_sal from emp group by deptno;
	+--------+-------------+
	| deptno | avg_sal     |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
	3 rows in set (0.00 sec)
	
# 第二步： 找出每个部门平均薪水的等级
mysql> select
    ->          t.deptno, t.avg_sal, s.grade
    -> from
    ->           ( select deptno, avg(Sal) as avg_sal from emp group by deptno ) t
    -> join
    ->           salgrade s
    -> on
    ->          t.avg_sal between s.losal and s.hisal;
	+--------+-------------+-------+
	| deptno | avg_sal     | grade |
	+--------+-------------+-------+
	|     10 | 2916.666667 |     4 |
	|     20 | 2175.000000 |     4 |
	|     30 | 1566.666667 |     3 |
	+--------+-------------+-------+
	3 rows in set (0.00 sec)
	
# 完整代码>>>>>>最后找出薪水最低的部门名称
mysql> select
    ->          min(t1.grade), d.dname
    -> from
    ->          (select t.deptno, t.avg_sal, s.grade from (( select deptno, avg(Sal) as avg_sal from emp group by deptno ) t) join salgrade s on (t.avg_sal between s.losal and s.hisal)) t1
    -> join
    ->          dept d
    -> on
    ->          d.deptno = t1.deptno;
	+---------------+-------+
	| min(t1.grade) | dname |
	+---------------+-------+
	|             3 | SALES |
	+---------------+-------+
	1 row in set (0.01 sec)


8. 找出薪水比（普通员工的最高薪水) 的领导的名字
# 第一步：找出普通员工
mysql> select distinct mgr from emp where mgr is not null;
	+------+
	| mgr  |
	+------+
	| 7902 |
	| 7698 |
	| 7839 |
	| 7566 |
	| 7788 |
	| 7782 |
	+------+
	6 rows 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.01 sec)
	
# 完整代码 
mysql> select ename,sal from emp where sal >(select max(Sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	| CLARK | 2450.00 |
	| SCOTT | 3000.00 |
	| KING  | 5000.00 |
	| FORD  | 3000.00 |
	+-------+---------+
	6 rows in set (0.00 sec)

9. 找出薪水在第六到第十的员工
mysql>  select ename,sal from emp order by sal desc limit 5,5;
	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| CLARK  | 2450.00 |
	| ALLEN  | 1600.00 |
	| TURNER | 1500.00 |
	| MILLER | 1300.00 |
	| WARD   | 1250.00 |
	+--------+---------+
	5 rows in set (0.02 sec)




10.