35道DQL练习题

发表于 2024-05-22 更新于 2024-09-14 分类于刷题阅读次数： Changyan：本文字数： 148 阅读时长 ≈ 1 分钟

该篇主要讲：MySQL刷题——35道DQL练习题

35道DQL练习题

表结构

mysql> use powernode;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_powernode |
+---------------------+
| DEPT                |
| EMP                 |
| SALGRADE            |
+---------------------+
3 rows in set (0.01 sec)

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int         | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int  | YES  |     | NULL    |       |
| LOSAL | int  | YES  |     | NULL    |       |
| HISAL | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

题目

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

第一步：取得每个部门最高薪水

mysql> select deptno, max(sal) maxsal from emp group by deptno; 
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     20 | 3000.00 |
|     30 | 2850.00 |
|     10 | 5000.00 |
+--------+---------+

第二步：将第一步查询结果作为临时表t，t和emp e连接，条件：t.deptno=e.deptno and t.maxsal=e.sal

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

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

0%