MySQL基础练习题:习题26-30

17 阅读1分钟

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

列出所有员工的姓名、部门名称、工资

mysql> select e.ename,d.dname,e.sal from emp e join dept d on e.deptno = d.deptno;
+--------+-------------+---------+
| ename  | dname       | sal     |
+--------+-------------+---------+
| SIMITH | RESEARCHING |  800.00 |
| ALLEN  | SALES       | 1600.00 |
| WARD   | SALES       | 1250.00 |
| JONES  | RESEARCHING | 2975.00 |
| MARTIN | SALES       | 1250.00 |
| BLAKE  | SALES       | 2850.00 |
| CLARK  | accounting  | 2450.00 |
| SCOTT  | RESEARCHING | 3000.00 |
| KING   | accounting  | 5000.00 |
| TURNER | SALES       | 1500.00 |
| ADAMS  | RESEARCHING | 1100.00 |
| JAMES  | SALES       |  950.00 |
| FORD   | RESEARCHING | 3000.00 |
| MILLER | accounting  | 1300.00 |
+--------+-------------+---------+
14 rows in set (0.00 sec)

列出所有部门的详细信息和人数

mysql> select d.*,count(e.ename) as totalemp from dept d left join emp e on d.deptno=e.deptno group by d.deptno;
+--------+-------------+----------+----------+
| deptno | dname       | loc      | totalemp |
+--------+-------------+----------+----------+
|     20 | RESEARCHING | DALLAS   |        5 |
|     30 | SALES       | CHICAGO  |        6 |
|     10 | ACCOUNTING  | NEW YORK |        3 |
|     40 | OPERATIONS  | BOSTON   |        0 |
+--------+-------------+----------+----------+

列出各种工作的最低工资以及从事此工作的雇员姓名

mysql> select e.ename,t.job,t.minsal from emp e join (select job,min(sal) as minsal from emp group by job) t on e.sal=t.minsal and e.job=t.job;
+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SIMITH | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+

列出各个部门的manager的最低薪水

mysql> select e.ename,e.job,t.minsal,t.deptno from emp e join (select min(sal) minsal,deptno from emp where job='manager' group by deptno) t on e.sal=t.minsal and e.deptno=t.deptno ;
+-------+---------+---------+--------+
| ename | job     | minsal  | deptno |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| CLARK | MANAGER | 2450.00 |     10 |
+-------+---------+---------+--------+

列出员工的年工资,按年薪从低到高排序

mysql> select ename,sal*12 as yearsal from emp order by yearsal;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SIMITH |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| WARD   | 15000.00 |
| MARTIN | 15000.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN  | 19200.00 |
| CLARK  | 29400.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)