MySQL34道练习题【14-22】

前言：这34道MySQL练习题是我在学习动力节点杜老师的老杜带你学_mysql入门基础课程整理的。

不得不说，杜老师是一位资深的程序员老师，在我看完杜老师的javaweb零基础入门到精通IDEA版，我才深刻体会到什么是真正的记事本战神，什么是真正的用心良苦的老师。杜老师在javaweb中声明的HttpServletRequest类型的变量名称为request而不是req，这让我在学到后面的时候真真切切感受到了什么是用心良苦。

SQL脚本和表结构放在文末

---

14、列出所有员工及领导的名字

SELECT
	a.ENAME '员工',
	b.ENAME '领导' 
FROM
	EMP a
	LEFT JOIN EMP b ON a.MGR = b.EMPNO;

---

15、列出受雇日期早于其直接上级的所有员工编号，姓名，部门名称

SELECT
	a.DEPTNO,
	a.ENAME '员工',
	a.HIREDATE,
	b.ENAME '领导',
	b.HIREDATE,
	d.DNAME 
FROM
	EMP a
	JOIN EMP b ON a.MGR = b.EMPNO
	JOIN DEPT d ON a.DEPTNO = d.DEPTNO 
WHERE
	a.HIREDATE < b.HIREDATE 
ORDER BY
	a.DEPTNO;

---

16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT
	e.*,
	d.DNAME 
FROM
	EMP e
	RIGHT JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

---

17、 列出至少有5个员工的所有部门

SELECT
	#用了分组以后，select后面只能写分组的字段和聚合函数
	d.DNAME,
	COUNT( e.DEPTNO ) '部门人数' 
FROM
	EMP e
	JOIN DEPT d ON d.DEPTNO = e.DEPTNO 
GROUP BY
	d.DNAME 
HAVING
	COUNT( e.DEPTNO ) >= 5;

---

18、列出薪资比‘SMITH’多的所有员工信息

先查出SMITH的薪资：

SELECT SAL FROM EMP WHERE ENAME = 'SMITH';

再作比较就可以啦

SELECT
	* 
FROM
	EMP 
WHERE
	SAL > ( SELECT SAL FROM EMP WHERE ENAME = 'SMITH' );

---

19、列出所有"CLERK"（办事员）的姓名及其部门名称，部门人数

#第一步：查出员工的姓名，部门名称，部门编号
SELECT
	e.ENAME,
	e.JOB,
	d.DNAME,
	d.DEPTNO 
FROM
	EMP e
	JOIN DEPT d ON e.DEPTNO = d.DEPTNO 
WHERE
	JOB = 'CLERK';
#第二步：查出每个部门的人数
SELECT
	DEPTNO,
	COUNT( DEPTNO ) '人数 ' 
FROM
	EMP e
GROUP BY
	DEPTNO;
#第三步：两张临时表做表连接：
SELECT
	t1.*,
	t2.`人数 ` 
FROM
	(
	SELECT
		e.ENAME,
		e.JOB,
		d.DNAME,
		d.DEPTNO 
	FROM
		EMP e
		JOIN DEPT d ON e.DEPTNO = d.DEPTNO 
	WHERE
		e.JOB = 'CLERK' 
	) t1
	JOIN ( SELECT DEPTNO, COUNT( DEPTNO ) AS '人数 ' FROM EMP GROUP BY DEPTNO ) t2 
	ON t1.DEPTNO = t2.DEPTNO;

---

20、列出最低薪资大于1500的各种工作的全部雇员人数

SELECT
	JOB,
	COUNT(*) 
FROM
	EMP 
GROUP BY
	JOB 
HAVING
	min( SAL ) > 1500;

---

21、列出在部门"SALES"《销售部》工作的员工姓名，假定不知道销售部的编号

SELECT
	e.ENAME,
	d.DNAME 
FROM
	EMP e
	JOIN DEPT d ON e.DEPTNO = d.DEPTNO 
WHERE
	d.DNAME = 'SALES';

---

22、列出薪资高于公司平均薪资的所有员工，所在部门，上级领导，雇员的工资

SELECT
	e.ENAME '员工',
	d.DNAME,
	l.ENAME '领导',
	s.GRADE 
FROM
	EMP e
	JOIN DEPT d ON e.DEPTNO = d.DEPTNO
	LEFT JOIN EMP l ON e.MGR = l.EMPNO
	JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL 
	AND s.HISAL 
WHERE
	e.SAL > ( SELECT avg( sal ) FROM EMP );

---

SQL脚本和表结构

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;
 

---

表结构：
EMP表（员工表）

DEPT表（部门表）

SALGRADE表（薪水等级表）