mysql练习题 -- 第四天

• 表信息

dept 部门表

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

emp 员工表

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

salgrade 薪水等级表

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

文件下载: https://download.csdn.net/download/eebaicai/12626751

15.列出所有员工及领导的姓名

emp a <员工表>
emp b <领导表>

SELECT
	a.ename,
	b.ename AS leadername
FROM
	emp a
LEFT JOIN emp b ON a.mgr = b.empno;

+--------+------------+  
| ename  | leadername |  
+--------+------------+  
| 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      |  
+--------+------------+  

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

emp a <员工表>
emp b <领导表>

SELECT
	a.empno,
	a.ename,
	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;

+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7369 | SMITH | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7782 | CLARK | ACCOUNTING |
+-------+-------+------------+

17. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

SELECT
	e.*, d.dname
FROM
	emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;

+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  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      |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  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      |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+

18.列出至少有5个员工的所有部门(部门详细信息)

SELECT
	d.deptno,
	d.dname,
	d.loc,
	count(e.ename)
FROM
	emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
GROUP BY
	d.deptno,
	d.dname,
	d.loc
HAVING
	count(e.ename) >= 5;

+--------+----------+---------+----------------+
| deptno | dname    | loc     | count(e.ename) |
+--------+----------+---------+----------------+
|     20 | RESEARCH | DALLAS  |              5 |
|     30 | SALES    | CHICAGO |              6 |
+--------+----------+---------+----------------+

19.列出薪水比"SMITH"多的所以员工信息

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+