13、面试题
14、列出所有员工及领导的姓名
自关联，外连接
select e.ename '员工', e2.ename '领导' from emp e left join emp e2 on e.mgr = e2.empno;
+--------+-------+ | 员工 | 领导 | +--------+-------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | KING | NULL | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+-------+ 14 rows in set (0.00 sec)
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
步骤一：找到每个员工的编号，姓名，部门名称
mysql> select e.empno, e.ename, d.dname -> from emp e -> join dept d -> on e.deptno = d.deptno; +-------+--------+------------+ | empno | ename | dname | +-------+--------+------------+ | 7782 | CLARK | ACCOUNTING | | 7839 | KING | ACCOUNTING | | 7934 | MILLER | ACCOUNTING | | 7369 | SMITH | RESEARCH | | 7566 | JONES | RESEARCH | | 7788 | SCOTT | RESEARCH | | 7876 | ADAMS | RESEARCH | | 7902 | FORD | RESEARCH | | 7499 | ALLEN | SALES | | 7521 | WARD | SALES | | 7654 | MARTIN | SALES | | 7698 | BLAKE | SALES | | 7844 | TURNER | SALES | | 7900 | JAMES | SALES | +-------+--------+------------+ 14 rows in set (0.00 sec)
步骤二：筛选
select e.empno, e.ename, d.dname from emp e join dept d on e.deptno = d.deptno join emp e2 on e.mgr = e2.empno where e.hiredate<e2.hiredate; //where得放后面
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select d.dname, e.* from emp e right join dept d on e.deptno=d.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+ | dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------------+-------+--------+-----------+------+------------+---------+---------+--------+ | ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-------+--------+-----------+------+------------+---------+---------+--------+ 15 rows in set (0.00 sec)
17、列出至少有 5 个员工的所有部门
步骤一：统计每个部门的人数
mysql> select d.dname, count(*) -> from emp e -> join dept d -> on e.deptno = d.deptno -> group by e.deptno; +------------+----------+ | dname | count(*) | +------------+----------+ | ACCOUNTING | 3 | | RESEARCH | 5 | | SALES | 6 | +------------+----------+ 3 rows in set (0.00 sec)
步骤二：筛选
select d.dname, count(*) from emp e join dept d on e.deptno = d.deptno group by e.deptno having count(*)>=5; //多这句
+----------+----------+ | dname | count(*) | +----------+----------+ | RESEARCH | 5 | | SALES | 6 | +----------+----------+ 2 rows in set (0.00 sec)
18、列出薪金比"SMITH"多的所有员工信息
步骤一：找到SMITH的薪资
mysql> select sal from emp where ename='SMITH'; +--------+ | sal | +--------+ | 800.00 | +--------+ 1 row in set (0.00 sec)
步骤二：筛选
select e.* from emp e join (select sal from emp where ename='SMITH') t where e.sal>t.sal;
或者
select * from emp where sal>(select sal from emp where ename='SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 13 rows in set (0.00 sec)
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
步骤一：找到所有办事员的姓名
mysql> select deptno, ename from emp where job='CLERK'; +--------+--------+ | deptno | ename | +--------+--------+ | 20 | SMITH | | 20 | ADAMS | | 30 | JAMES | | 10 | MILLER | +--------+--------+ 4 rows in set (0.00 sec)
步骤二：找到每个部门的人数
mysql> select d.deptno, d.dname, count(*) as cc -> from emp e -> join dept d -> where e.deptno = d.deptno -> group by e.deptno; +--------+------------+----+ | deptno | dname | cc | +--------+------------+----+ | 10 | ACCOUNTING | 3 | | 20 | RESEARCH | 5 | | 30 | SALES | 6 | +--------+------------+----+ 3 rows in set (0.00 sec)
步骤三：连接两张表
select t1.ename, t2.dname, t2.cc from (select deptno, ename from emp where job='CLERK') t1 join (select d.deptno, d.dname, count(*) as cc from emp e join dept d where e.deptno = d.deptno group by e.deptno) t2 on t1.deptno=t2.deptno;
+--------+------------+----+ | ename | dname | cc | +--------+------------+----+ | SMITH | RESEARCH | 5 | | ADAMS | RESEARCH | 5 | | JAMES | SALES | 6 | | MILLER | ACCOUNTING | 3 | +--------+------------+----+ 4 rows in set (0.00 sec)
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.
步骤一：查询每种工作的最低薪资
mysql> select job, min(sal) min_sal from emp group by job; +-----------+---------+ | job | min_sal | +-----------+---------+ | ANALYST | 3000.00 | | CLERK | 800.00 | | MANAGER | 2450.00 | | PRESIDENT | 5000.00 | | SALESMAN | 1250.00 | +-----------+---------+ 5 rows in set (0.00 sec)
步骤二：查询最低薪资大于1500的工作
mysql> select job, min(sal) min_sal from emp group by job having min(sal)>1500; +-----------+---------+ | job | min_sal | +-----------+---------+ | ANALYST | 3000.00 | | MANAGER | 2450.00 | | PRESIDENT | 5000.00 | +-----------+---------+ 3 rows in set (0.00 sec)
步骤三：统计各种工作人数
select job,count(*) from emp group by job;
+-----------+----------+ | job | count(*) | +-----------+----------+ | ANALYST | 2 | | CLERK | 4 | | MANAGER | 3 | | PRESIDENT | 1 | | SALESMAN | 4 | +-----------+----------+ 5 rows in set (0.00 sec)
步骤四：合起来
select t1.job,t2.num from (select job, min(sal) min_sal from emp group by job having min(sal)>1500) t1 join (select job,count(*) num from emp group by job) t2 on t1.job=t2.job;
+-----------+-----+ | job | num | +-----------+-----+ | ANALYST | 2 | | MANAGER | 3 | | PRESIDENT | 1 | +-----------+-----+ 3 rows in set (0.00 sec)
步骤三和步骤四可以合起来用以下代替：
mysql> select job, count(*) from emp group by job having min(sal)>1500; +-----------+----------+ | job | count(*) | +-----------+----------+ | ANALYST | 2 | | MANAGER | 3 | | PRESIDENT | 1 | +-----------+----------+ 3 rows in set (0.00 sec)
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部 门编号
步骤一：先查找到编号
mysql> select deptno from dept where dname='SALES'; +--------+ | deptno | +--------+ | 30 | +--------+ 1 row in set (0.00 sec)
步骤二：
mysql> select ename from emp where deptno=(select deptno from dept where dname='SALES'); +--------+ | ename | +--------+ | ALLEN | | WARD | | MARTIN | | BLAKE | | TURNER | | JAMES | +--------+ 6 rows in set (0.00 sec)
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
步骤一：求出平均薪资
select avg(sal) avg_sal from emp;
+-------------+ | avg_sal | +-------------+ | 2073.214286 | +-------------+ 1 row in set (0.00 sec)
步骤二：找出薪资高于平均薪资的人
select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp);
+-------+--------+------+---------+ | ename | deptno | mgr | sal | +-------+--------+------+---------+ | JONES | 20 | 7839 | 2975.00 | | BLAKE | 30 | 7839 | 2850.00 | | CLARK | 10 | 7839 | 2450.00 | | SCOTT | 20 | 7566 | 3000.00 | | KING | 10 | NULL | 5000.00 | | FORD | 20 | 7566 | 3000.00 | +-------+--------+------+---------+ 6 rows in set (0.00 sec)
步骤三：加入部门名称
select t1.ename '姓名', d.dname '部门' from (select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp)) t1 join dept d on t1.deptno = d.deptno;
+-------+------------+ | 姓名 | 部门 | +-------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | JONES | RESEARCH | | SCOTT | RESEARCH | | FORD | RESEARCH | | BLAKE | SALES | +-------+------------+ 6 rows in set (0.00 sec)
步骤四：加入上级领导
select t1.ename '姓名', d.dname '部门', e.ename '上级领导' from (select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp)) t1 join dept d on t1.deptno = d.deptno left join emp e on t1.mgr = e.empno;
步骤五：加入薪资等级
select t1.ename '姓名', d.dname '部门', e.ename '上级领导', s.grade '工资等级' from (select ename, deptno, mgr, sal from emp where sal>(select avg(sal) avg_sal from emp)) t1 join dept d on t1.deptno = d.deptno left join emp e on t1.mgr = e.empno join salgrade s on t1.sal between s.losal and s.hisal;
+-------+------------+----------+----------+ | 姓名 | 部门 | 上级领导 | 工资等级 | +-------+------------+----------+----------+ | JONES | RESEARCH | KING | 4 | | BLAKE | SALES | KING | 4 | | CLARK | ACCOUNTING | KING | 4 | | SCOTT | RESEARCH | JONES | 4 | | KING | ACCOUNTING | NULL | 5 | | FORD | RESEARCH | JONES | 4 | +-------+------------+----------+----------+ 6 rows in set (0.00 sec)
23、列出与"SCOTT"从事相同工作的所有员工及部门名称
步骤一：找到此人从事的工作
mysql> select job from emp where ename='SCOTT'; +---------+ | job | +---------+ | ANALYST | +---------+ 1 row in set (0.00 sec)
步骤二：找到相同工作的人
mysql> select * from emp where job=(select job from emp where ename='SCOTT') and ename!='SCOTT'; +-------+-------+---------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+---------+------+------------+---------+------+--------+ | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+---------+------+------------+---------+------+--------+ 1 row in set (0.00 sec)
步骤三：找到部门
select t.ename, d.dname from (select * from emp where job=(select job from emp where ename='SCOTT') and ename!='SCOTT') t join dept d on d.deptno = t.deptno;
+-------+----------+ | ename | dname | +-------+----------+ | FORD | RESEARCH | +-------+----------+ 1 row in set (0.00 sec)
