使用HR数据库进行练习之聚合函数和Group by
Practice 1
编写一条SQL查询语句，列出employees表中可用的job
|
1 |
SELECT job_id FROM employees GROUP BY job_id; |
Practice 2
编写一条SQL查询语句，列出employees表中所有employee的总salary
|
1 |
SELECT sum(salary) total_salary FROM employees; |
Practice 3
编写一条SQL查询语句，获取所有employee的最高、最低、总和和平均salary
|
1 2 3 4 5 |
SELECT ROUND(MAX(salary), 2) highest_salary, ROUND(MIN(salary), 2) lowest_salary, ROUND(SUM(salary), 2) sum_salary, ROUND(AVG(salary), 2) avg_salary FROM employees; |
Practice 4
编写一条SQL查询语句，获取同一种工作employee人数
|
1 |
SELECT job_id, COUNT(job_id) num_job FROM employees GROUP BY job_id; |
Practice 5
编写一条SQL查询语句，获取最高和最低salary之间的差异
|
1 |
SELECT MAX(salary) - MIN(salary) dif_salary FROM employees; |
Practice 6
编写一条SQL查询语句，获取某manager_id底下最低的salary
|
1 2 3 4 |
SELECT manager_id, MIN(salary) min_salary FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id ORDER BY MIN(salary) DESC; |
Practice 7
编写一条SQL查询语句，获取department id和该department需要支付的总的salary
|
1 2 |
SELECT department_id, SUM(salary) department_salary FROM employees GROUP BY department_id; |
Practice 8
编写一条SQL查询语句，获取除了programmer外的每个工种的平均salary
|
1 2 3 |
SELECT job_id, AVG(salary) avg_salary FROM employees WHERE job_id != 'IT_PROG' GROUP BY job_id; |
Practice 9
编写一条SQL查询语句，获取department ID为90（包含不同job_id）的所有employee的总薪水，最高薪水，最低薪水和平均薪水
|
1 2 3 4 |
SELECT job_id, SUM(salary) sum_salary, MAX(salary) max_salary, MIN(salary) min_salary, avg(salary) avg_salary FROM employees WHERE department_id = 90 GROUP BY job_id; |
Practice 10
编写一条SQL查询语句，获取最高salary大于等于4000的job_id以及该类job中最高salary
|
1 2 3 |
SELECT job_id, MAX(salary) max_salary FROM employees GROUP BY job_id HAVING MAX(salary) >= 4000; |
Practice 11
编写一条SQL查询语句，获取所有employee人数大于10人的的department的平均salary
|
1 2 3 |
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING COUNT(*) > 10; |
谢谢您的付出谢谢。
楼主辛苦谢谢谢谢
没有什么不可以真心谢谢博主。