MySQL基础练习题：习题7-11

AIGoland之星
185 阅读5分钟

这部分主要是为了帮助大家回忆回忆MySQL的基本语法，数据库来自于MySQL的官方简化版，题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。

求平均薪水的等级最高的部门的部门名称

Solution：

先求各部门平均薪水的等级，再求最高等级，最后显示平均薪水等级等于最高等级的部门名称

Operation：

1

select a.deptno,a.avgsal,b.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) a join salgrade b on avgsal between losal and hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+

2

+-------+
| grade |
+-------+
|     4 |
+-------+

mysql> select d.dname,c.avgsal,c.grade
from
	(select a.deptno,a.avgsal,b.grade
     	from
     		(select deptno,avg(sal) as avgsal from emp group by deptno) a
     	join
     		salgrade b
     	on
     		avgsal between losal and hisal) c
join
	dept d
on
	c.deptno=d.deptno and c.grade=
		(select b.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) a join salgrade b on avgsal between losal and hisal order by b.grade desc limit 1);
+-------------+-------------+-------+
| dname       | avgsal      | grade |
+-------------+-------------+-------+
| ACCOUNTING  | 2916.666667 |     4 |
| RESEARCHING | 2175.000000 |     4 |
+-------------+-------------+-------+
  • 内层查询 (子查询 c)：

内层查询使用了一个子查询 c，通过联结 emp 表和 salgrade 表计算每个部门的平均工资，并根据平均工资匹配相应的工资等级。 子查询 c 的主要目的是找到每个部门的平均工资以及相应的工资等级。

  • 外层查询 (主查询)：

外层查询从 dept 表中检索部门的名称，然后与子查询 c 进行联结，以获取子查询返回的结果集中的平均工资和工资等级。 外层查询中的联结条件是 c.deptno = d.deptno，确保每个部门的平均工资和工资等级都与正确的部门关联。 外层查询还包括了一个条件 c.grade = (select ... limit 1)，它使用了一个子查询来查找具有最高工资等级的部门的工资等级。

  • 内层子查询 (子查询 b)：

这个子查询用于计算每个部门的平均工资，并根据平均工资匹配相应的工资等级。 子查询 b 中首先计算每个部门的平均工资，然后与 salgrade 表进行联结，并按照工资等级降序排序。 最后，使用 LIMIT 1 来限制结果集返回最高工资等级的部门。

取得比普通员工（员工代码没在mgr字段出现的）最高薪水更高的领导人姓名

Solution：

先取出普通员工的最高薪水，注意mgr字段里有null，不能直接使用not in 语句。取得所有领导人的姓名和薪水。然后取得结果。

Operation：

找出所有不是其他员工的经理的员工的最高工资

mysql> select max(sal) as cmsal from emp where empno not in (select distinct mgr from emp where mgr is not null);
+---------+
| cmsal   |
+---------+
| 1600.00 |
+---------+

找出管理层员工，并且列出他们的姓名和薪水

mysql> select ename,sal from emp where empno in (select distinct mgr from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| BLAKE | 2850.00 |
| KING  | 5000.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| CLARK | 2450.00 |
+-------+---------+

  1. SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL：这部分子查询用于获取所有不是其他员工的经理的员工的经理编号（mgr字段）。使用DISTINCT确保返回的mgr字段值是唯一的，避免重复。

  2. SELECT MAX(sal) AS cmsal FROM emp WHERE empno NOT IN (...)：这部分子查询计算所有不是其他员工的经理的员工的最高工资。它通过在emp表中选择所有不是其他员工的经理的员工（即不在上述子查询中的员工）来实现。MAX函数用于找到这些员工中工资的最高值。

  3. WHERE empno IN (...) AND sal > (...)：这是主查询的条件部分，它同时使用了两个子查询的结果作为条件。前一个子查询筛选了作为其他员工的经理的员工，而后一个子查询计算了不是其他员工的经理的员工的最高工资。因此，这个条件确保了只选择薪水高于最高工资的经理。

  4. SELECT ename, sal FROM emp ...：这是主查询的主体部分，它选择了符合上述条件的员工的姓名（ename）和薪水（sal）。

mysql> select ename,sal from emp where empno in (select distinct mgr from emp) and sal>(select max(sal) as cmsal 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 |
+-------+---------+

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

select * from emp order by sal desc limit 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+

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

mysql> select * from emp order by sal desc limit 5,5;
+-------+--------+----------+------+------------+---------+--------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+--------+----------+------+------------+---------+--------+--------+
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |   NULL |     10 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |   NULL |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |   NULL |     10 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
+-------+--------+----------+------+------------+---------+--------+--------+

  1. SELECT * FROM emp：这是查询的主体部分，表示从emp表中选择所有列的数据。

  2. ORDER BY sal DESC：这部分表示按照薪水字段（sal）进行降序排序。

  3. LIMIT 5, 5：这部分表示从结果集中的第6条记录开始，返回接下来的5条记录。第一个数字5表示从第6条记录开始（索引从0开始），第二个数字5表示返回5条记录。

取得最后入职的5名员工

mysql> select * from emp order by hiredate desc limit 5;
+-------+--------+---------+------+------------+---------+------+--------+
| empno | ename  | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+---------+------+------------+---------+------+--------+
|  7876 | ADAMS  | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7788 | SCOTT  | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK   | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7900 | JAMES  | CLERK   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7902 | FORD   | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+--------+---------+------+------------+---------+------+--------+
avatar
文章
阅读
粉丝