1 ##1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
2 SELECT
3 `job_id` AS 职位编号,
4 `first_name` AS 员工名,
5 MAX(`salary`) AS 最大值,
6 MIN(`salary`) AS 最小值,
7 AVG(`salary`) AS 平均工资,
8 SUM(`salary`) AS 总和
9 FROM
10 `employees`
11 GROUP BY `job_id` ##按照xxx分组
12 ORDER BY `job_id` ASC ; ##按照xxx升序 默认升序
13
14 ##2.查询员工最高工资和最低工资的差距（DIFFERENCE）
15 SELECT
16 MAX(`salary`)-MIN(`salary`)AS DIFFERENCE ##最大值-最小值
17 FROM `employees`
18 ##3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
19 SELECT `manager_id`AS 管理者,`first_name`AS 员工,MIN(`salary`)AS 最低工资
20 FROM `employees`
21 WHERE `manager_id` IS NOT NULL
22 GROUP BY `job_id`
23 HAVING MIN(`salary`)>=6000;
24 ##4.查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
25 SELECT `department_id` AS 部门编号,
26 COUNT(`department_id`) AS 员工数,
27 AVG(`salary`) AS 平均工资
28 FROM `employees`
29 GROUP BY `job_id`
30 ORDER BY AVG(`salary`) DESC;
31 ##5.选择具有各个job_id的员工人数
32 SELECT `job_id`AS 员工编号,
33 COUNT(`employee_id`) AS 员工数
34 FROM `employees`
35 GROUP BY `job_id`;
36 ##6.查询公司员工工资的最大值,最小值，平均值，总和
37 SELECT`first_name` AS 员工名,
38 MAX(`salary`) AS 最大值,
39 MIN(`salary`) AS 最小值,
40 AVG(`salary`) AS 平均值,
41 SUM(`salary`) AS 总和
42 FROM `employees`
43
44 ##7.查询员工表中得最大入职时间和最小入职时间得相差天数（DIFFERENCE）`first_name` AS 员工名, - TIME_TO_SEC(MIN(`hiredate`))
45 SELECT
46 ##TO_DAYS(MAX(`hiredate`)) - TO_DAYS(MIN(`hiredate`)) AS 相差天数 ##把时间跟0000年1月1日比较
47 ##datediff(MAX(`hiredate`), MIN(`hiredate`)) AS 相差天数 ##时间减去时间,最大值减去最小值
48 DATEDIFF(MAX(`hiredate`), MIN(`hiredate`))*24*3600 AS 相差秒数 ##把天数换算成秒数
49 FROM `employees`;
50
51
52 ##8.查询部门编号为90的员工个数
53 SELECT COUNT(*)AS 员工个数 FROM `employees` WHERE `department_id`=90;
