Mysql8.0习题系列
软件下载地址
提取码：7v7u
数据下载地址
提取码：e6p9
@TOC
聚合函数
1.where子句可否使用组函数进行过滤?
不可以
2.查询公司员工工资的最大值，最小值，平均值，总和
mysql> SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
-> FROM employees;
+-------------+-------------+-------------+-------------+
| MAX(salary) | MIN(salary) | AVG(salary) | SUM(salary) |
+-------------+-------------+-------------+-------------+
| 24000.00 | 2100.00 | 6588.679245 | 698400.00 |
+-------------+-------------+-------------+-------------+
1 row in set (0.02 sec)3.查询各job_id的员工工资的最大值，最小值，平均值，总和
mysql> SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
-> FROM employees
-> GROUP BY job_id;
+------------+-------------+-------------+--------------+-------------+
| job_id | MAX(salary) | MIN(salary) | AVG(salary) | SUM(salary) |
+------------+-------------+-------------+--------------+-------------+
| AC_ACCOUNT | 8300.00 | 8300.00 | 8300.000000 | 8300.00 |
| AC_MGR | 12000.00 | 12000.00 | 12000.000000 | 12000.00 |
| AD_ASST | 4400.00 | 4400.00 | 4400.000000 | 4400.00 |
| AD_PRES | 24000.00 | 24000.00 | 24000.000000 | 24000.00 |
| AD_VP | 17000.00 | 17000.00 | 17000.000000 | 34000.00 |
| FI_ACCOUNT | 9000.00 | 6900.00 | 7920.000000 | 39600.00 |
| FI_MGR | 12000.00 | 12000.00 | 12000.000000 | 12000.00 |
| HR_REP | 6500.00 | 6500.00 | 6500.000000 | 6500.00 |
| IT_PROG | 9000.00 | 4200.00 | 5760.000000 | 28800.00 |
| MK_MAN | 13000.00 | 13000.00 | 13000.000000 | 13000.00 |
| MK_REP | 6000.00 | 6000.00 | 6000.000000 | 6000.00 |
| PR_REP | 10000.00 | 10000.00 | 10000.000000 | 10000.00 |
| PU_CLERK | 3100.00 | 2500.00 | 2780.000000 | 13900.00 |
| PU_MAN | 11000.00 | 11000.00 | 11000.000000 | 11000.00 |
| SA_MAN | 15000.00 | 11000.00 | 13000.000000 | 65000.00 |
| SA_REP | 12500.00 | 6100.00 | 8741.379310 | 253500.00 |
| SH_CLERK | 4200.00 | 2500.00 | 3215.000000 | 64300.00 |
| ST_CLERK | 3600.00 | 2100.00 | 2785.000000 | 55700.00 |
| ST_MAN | 8200.00 | 5800.00 | 7280.000000 | 36400.00 |
+------------+-------------+-------------+--------------+-------------+
19 rows in set (0.01 sec)4.选择具有各个job_id的员工人数
mysql> SELECT job_id,count(*) '人数'
-> FROM employees GROUP BY job_id;
+------------+------+
| job_id | 人数 |
+------------+------+
| AC_ACCOUNT | 1 |
| AC_MGR | 1 |
| AD_ASST | 1 |
| AD_PRES | 1 |
| AD_VP | 2 |
| FI_ACCOUNT | 5 |
| FI_MGR | 1 |
| HR_REP | 1 |
| IT_PROG | 5 |
| MK_MAN | 1 |
| MK_REP | 1 |
| PR_REP | 1 |
| PU_CLERK | 5 |
| PU_MAN | 1 |
| SA_MAN | 5 |
| SA_REP | 29 |
| SH_CLERK | 20 |
| ST_CLERK | 20 |
| ST_MAN | 5 |
+------------+------+
19 rows in set (0.01 sec)5.查询员工最高工资和最低工资的差距（DIFFERENCE）
mysql> SELECT MAX(salary) - MIN(salary) AS 'difference'
-> FROM employees;
+------------+
| difference |
+------------+
| 21900.00 |
+------------+
1 row in set (0.00 sec)6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
mysql> SELECT manager_id,MIN(salary)
-> FROM employees
-> WHERE manager_id IS NOT NULL
-> GROUP BY manager_id
-> HAVING MIN(salary) > 6000;
+------------+-------------+
| manager_id | MIN(salary) |
+------------+-------------+
| 102 | 9000.00 |
| 108 | 6900.00 |
| 145 | 7000.00 |
| 146 | 7500.00 |
| 147 | 6200.00 |
| 148 | 6100.00 |
| 149 | 6200.00 |
| 205 | 8300.00 |
+------------+-------------+
8 rows in set (0.01 sec)7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
mysql> SELECT department_name, location_id, COUNT(*), AVG(salary)
-> FROM employees e RIGHT JOIN departments d
-> ON e.`department_id` = d.`department_id`
-> GROUP BY department_name
-> ORDER BY AVG(salary) DESC;
+----------------------+-------------+----------+--------------+
| department_name | location_id | COUNT(*) | AVG(salary) |
+----------------------+-------------+----------+--------------+
| Executive | 1700 | 3 | 19333.333333 |
| Accounting | 1700 | 2 | 10150.000000 |
| Public Relations | 2700 | 1 | 10000.000000 |
| Marketing | 1800 | 2 | 9500.000000 |
| Sales | 2500 | 33 | 9439.393939 |
| Finance | 1700 | 6 | 8600.000000 |
| Human Resources | 2400 | 1 | 6500.000000 |
| IT | 1400 | 5 | 5760.000000 |
| Administration | 1700 | 1 | 4400.000000 |
| Purchasing | 1700 | 6 | 4150.000000 |
| Shipping | 1500 | 45 | 3475.555556 |
| Treasury | 1700 | 1 | NULL |
| Corporate Tax | 1700 | 1 | NULL |
| Control And Credit | 1700 | 1 | NULL |
| Shareholder Services | 1700 | 1 | NULL |
| Benefits | 1700 | 1 | NULL |
| Manufacturing | 1700 | 1 | NULL |
| Construction | 1700 | 1 | NULL |
| Contracting | 1700 | 1 | NULL |
| Operations | 1700 | 1 | NULL |
| IT Support | 1700 | 1 | NULL |
| NOC | 1700 | 1 | NULL |
| IT Helpdesk | 1700 | 1 | NULL |
| Government Sales | 1700 | 1 | NULL |
| Retail Sales | 1700 | 1 | NULL |
| Recruiting | 1700 | 1 | NULL |
| Payroll | 1700 | 1 | NULL |
+----------------------+-------------+----------+--------------+
27 rows in set (0.00 sec)8.查询每个工种、每个部门的部门名、工种名和最低工资
mysql> SELECT department_name,job_id,MIN(salary)
-> FROM departments d LEFT JOIN employees e
-> ON e.`department_id` = d.`department_id`
-> GROUP BY department_name,job_id;
+----------------------+------------+-------------+
| department_name | job_id | MIN(salary) |
+----------------------+------------+-------------+
| Administration | AD_ASST | 4400.00 |
| Marketing | MK_MAN | 13000.00 |
| Marketing | MK_REP | 6000.00 |
| Purchasing | PU_MAN | 11000.00 |
| Purchasing | PU_CLERK | 2500.00 |
| Human Resources | HR_REP | 6500.00 |
| Shipping | ST_MAN | 5800.00 |
| Shipping | ST_CLERK | 2100.00 |
| Shipping | SH_CLERK | 2500.00 |
| IT | IT_PROG | 4200.00 |
| Public Relations | PR_REP | 10000.00 |
| Sales | SA_MAN | 11000.00 |
| Sales | SA_REP | 6100.00 |
| Executive | AD_PRES | 24000.00 |
| Executive | AD_VP | 17000.00 |
| Finance | FI_MGR | 12000.00 |
| Finance | FI_ACCOUNT | 6900.00 |
| Accounting | AC_MGR | 12000.00 |
| Accounting | AC_ACCOUNT | 8300.00 |
| Treasury | NULL | NULL |
| Corporate Tax | NULL | NULL |
| Control And Credit | NULL | NULL |
| Shareholder Services | NULL | NULL |
| Benefits | NULL | NULL |
| Manufacturing | NULL | NULL |
| Construction | NULL | NULL |
| Contracting | NULL | NULL |
| Operations | NULL | NULL |
| IT Support | NULL | NULL |
| NOC | NULL | NULL |
| IT Helpdesk | NULL | NULL |
| Government Sales | NULL | NULL |
| Retail Sales | NULL | NULL |
| Recruiting | NULL | NULL |
| Payroll | NULL | NULL |
+----------------------+------------+-------------+
35 rows in set (0.00 sec)有的部门没有工资信息。
