mysql练习题 -- 第六天

最新推荐文章于 2022-10-05 04:00:00 发布

白菜喵

最新推荐文章于 2022-10-05 04:00:00 发布

阅读量677

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/eebaicai/article/details/107510632

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

表信息

dept 部门表

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

emp 员工表

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

salgrade 薪水等级表

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

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

25.列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水

第一步: 部门30的员工的薪水

SELECT DISTINCT
	sal
FROM
	emp
WHERE
	deptno = 30;

第二步:

SELECT
	ename,
	sal
FROM
	emp
WHERE
	sal IN (
		SELECT DISTINCT
			sal
		FROM
			emp
		WHERE
			deptno = 30
	)
AND deptno != 30;

26.列出薪水高于部门30的所有员工的薪水的员工的姓名薪水部门名称

第一步: 找部门30的员工薪水最大值

SELECT
	max(sal)
FROM
	emp
WHERE
	deptno = 30;

第二步:

SELECT
	e.ename,
	e.sal,
	d.dname
FROM
	emp e
JOIN dept d ON e.deptno = d.deptno
WHERE
	e.sal > (
		SELECT
			max(sal)
		FROM
			emp
		WHERE
			deptno = 30
	);

+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

27.列出在每个部门工作的员工数量,平均工资和平均服务期限

第一步: 将员工表 emp 和部门表 dept d 进行表连接,将部门表数据全部显示

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

+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+ 
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      | LOC      | 
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+ 
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   | 
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  | 
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  | 
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   | 
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  | 
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  | 
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK | 
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   | 
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK | 
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  | 
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   | 
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  | 
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   | 
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK | 
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     40 | OPERATIONS | BOSTON   | 
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+

第二步: 以上结果按照d.deptno分组,按照e.ename计数

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

+--------+----------------+
| deptno | count(e.ename) |
+--------+----------------+
|     10 |              3 |
|     20 |              5 |
|     30 |              6 |
|     40 |              0 |
+--------+----------------+

第三步: 以上结果上继续计算平均工资

SELECT
	d.deptno,
	count(e.ename) AS total_emp,
	IFNULL(avg(sal), 0) AS avgsal
FROM
	emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
GROUP BY
	d.deptno;



+--------+-----------+-------------+
| deptno | total_emp | avgsal      |
+--------+-----------+-------------+
|     10 |         3 | 2916.666667 |
|     20 |         5 | 2175.000000 |
|     30 |         6 | 1566.666667 |
|     40 |         0 |    0.000000 |
+--------+-----------+-------------+

第四步:

SELECT
	d.deptno,
	count(e.ename) AS total_emp,
	IFNULL(avg(sal), 0) AS avgsal,
	IFNULL(
		avg(
			(
				TO_DAYS(NOW()) - TO_DAYS(e.hiredate)
			) / 365
		),
		0
	) as avgtime
FROM
	emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
GROUP BY
	d.deptno;

+--------+-----------+-------------+-------------+
| deptno | total_emp | avgsal      | avgtime     |
+--------+-----------+-------------+-------------+
|     10 |         3 | 2916.666667 | 38.78993333 |
|     20 |         5 | 2175.000000 | 36.81698000 |
|     30 |         6 | 1566.666667 | 39.08860000 |
|     40 |         0 |    0.000000 |  0.00000000 |
+--------+-----------+-------------+-------------+

28.列出所有员工的姓名部门名称和薪水

SELECT
	e.ename,
	d.dname,
	e.sal
FROM
	emp e
JOIN dept d ON e.deptno = d.deptno;

+--------+------------+---------+    
| ename  | dname      | sal     |    
+--------+------------+---------+    
| SMITH  | RESEARCH   |  800.00 |    
| ALLEN  | SALES      | 1600.00 |    
| WARD   | SALES      | 1250.00 |    
| JONES  | RESEARCH   | 2975.00 |    
| MARTIN | SALES      | 1250.00 |    
| BLAKE  | SALES      | 2850.00 |    
| CLARK  | ACCOUNTING | 2450.00 |    
| SCOTT  | RESEARCH   | 3000.00 |    
| KING   | ACCOUNTING | 5000.00 |    
| TURNER | SALES      | 1500.00 |    
| ADAMS  | RESEARCH   | 1100.00 |    
| JAMES  | SALES      |  950.00 |    
| FORD   | RESEARCH   | 3000.00 |    
| MILLER | ACCOUNTING | 1300.00 |    
+--------+------------+---------+

29. 列出所有部门的详细信息和人数

第一种方法:

SELECT
	d.*, IFNULL(t.total_emp, 0) AS total_emp
FROM
	dept d
LEFT JOIN (
	SELECT
		deptno,
		count(*) AS total_emp
	FROM
		emp
	GROUP BY
		deptno
) t ON d.deptno = t.deptno;

+--------+------------+----------+-----------+
| DEPTNO | DNAME      | LOC      | total_emp |
+--------+------------+----------+-----------+
|     10 | ACCOUNTING | NEW YORK |         3 |
|     20 | RESEARCH   | DALLAS   |         5 |
|     30 | SALES      | CHICAGO  |         6 |
|     40 | OPERATIONS | BOSTON   |         0 |
+--------+------------+----------+-----------+

第二种方法

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

+--------+------------+----------+----------------+
| DEPTNO | DNAME      | LOC      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+

注意:
count(null)等于0
count的效果这样理解:

+--------+
|    a   |
+--------+
|     10 |
|     20 | 
|     30 | 
|    NULL| 
+--------+
count(a) = 3
就是:
count(10) = 1
count(20) = 1
count(30) = 1
count(NULL) = 0
加起来就是3了