mysql练习题 -- 第五天

• 表信息

dept 部门表

字段
DEPTNO 部门编号
DNAME 部门名称
LOC 位置

emp 员工表

字段
EMPNO 员工编号
ENAME 员工名称
JOB 职位
MGR 上级
HIREDATE 入职时间
SAL 薪水
COMM 奖金
DEPTNO 部门编号

salgrade 薪水等级表

字段
GRADE 等级编号
LOSAL 最低薪水
HISAL 最高薪水

文件下载: https://download.youkuaiyun.com/download/eebaicai/12626751

20.列出所有"CLERK"(办事员)的姓名及部门名称,部门的人数

第一步:

SELECT
	e.ename,
	d.dname
FROM
	dept d
JOIN emp e ON d.deptno = e.deptno
WHERE
	e.job = 'CLERK';

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| MILLER | ACCOUNTING |
+--------+------------+

第二步:

SELECT
	deptno,
	count(*) as total_emp
FROM
	emp
GROUP BY
	deptno;

+--------+-----------+
| deptno | total_emp |
+--------+-----------+
|     10 |         3 |
|     20 |         5 |
|     30 |         6 |
+--------+-----------+

第一步和第二步联合:

SELECT
	e.ename,
	d.dname,
	t.total_emp
FROM
	dept d
JOIN emp e ON d.deptno = e.deptno
JOIN (
	SELECT
		deptno,
		count(*) AS total_emp
	FROM
		emp
	GROUP BY
		deptno
) t ON d.deptno = t.deptno
WHERE
	e.job = 'CLERK';

+--------+------------+-----------+
| ename  | dname      | total_emp |
+--------+------------+-----------+
| SMITH  | RESEARCH   |         5 |
| ADAMS  | RESEARCH   |         5 |
| JAMES  | SALES      |         6 |
| MILLER | ACCOUNTING |         3 |
+--------+------------+-----------+

21.列出最低薪水大于1500的各种工作及从事此工作全部员工人数

SELECT
	min(sal),
	count(*)
FROM
	emp
GROUP BY
	job
HAVING
	min(sal) > 1500;

+----------+----------+
| min(sal) | count(*) |
+----------+----------+
|  3000.00 |        2 |
|  2450.00 |        3 |
|  5000.00 |        1 |
+----------+----------+

22. 列出在部门"SALES"<销售部>工作的员工的姓名(假设不知道销售部的部门编号)

SELECT
	ename
FROM
	emp
WHERE
	deptno = (
		SELECT
			deptno
		FROM
			dept
		WHERE
			dname = 'SALES'
	);

+--------+ 
| ename  | 
+--------+ 
| ALLEN  | 
| WARD   | 
| MARTIN | 
| BLAKE  | 
| TURNER | 
| JAMES  | 
+--------+ 

23.列出薪水高于公司平均薪水的所有员工,所在部门,上级领导,员工薪水等级

SELECT
	a.ename AS empname,
	d.dname,
	b.ename AS leadername,
	s.grade
FROM
	emp a
JOIN dept d ON a.deptno = d.DEPTNO
LEFT JOIN emp b ON a.mgr = b.empno
JOIN salgrade s ON a.sal BETWEEN s.losal
AND s.hisal
WHERE
	a.sal > (SELECT avg(sal) FROM emp);

+---------+------------+------------+-------+
| empname | dname      | leadername | grade |
+---------+------------+------------+-------+
| JONES   | RESEARCH   | KING       |     4 |
| BLAKE   | SALES      | KING       |     4 |
| CLARK   | ACCOUNTING | KING       |     4 |
| SCOTT   | RESEARCH   | JONES      |     4 |
| KING    | ACCOUNTING | NULL       |     5 |
| FORD    | RESEARCH   | JONES      |     4 |
+---------+------------+------------+-------+

24.列出与"SCOTT"从事相同工作的所有员工及部门名称

SELECT
	e.ename,
	e.job,
	d.dname
FROM
	emp e
JOIN dept d ON e.deptno = d.deptno
WHERE
	e.job = (
		SELECT
			job
		FROM
			emp
		WHERE
			ename = 'SCOTT'
	)
AND e.ename != 'SCOTT';

+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+