这部分主要是为了帮助大家回忆回忆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)