Jimmy Chen

A Programmer

使用HR数据库进行练习之聚合函数和Group by

使用HR数据库进行练习之聚合函数和Group by

Practice 1

  编写一条SQL查询语句,列出employees表中可用的job

SELECT job_id FROM employees GROUP BY job_id;

Practice 2

  编写一条SQL查询语句,列出employees表中所有employee的总salary

SELECT sum(salary) total_salary FROM employees;

Practice 3

  编写一条SQL查询语句,获取所有employee的最高、最低、总和和平均salary

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人数

SELECT job_id, COUNT(job_id) num_job FROM employees GROUP BY job_id;

Practice 5

  编写一条SQL查询语句,获取最高和最低salary之间的差异

SELECT MAX(salary) - MIN(salary) dif_salary FROM employees;

Practice 6

  编写一条SQL查询语句,获取某manager_id底下最低的salary

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

SELECT department_id, SUM(salary) department_salary FROM employees
    GROUP BY department_id;

Practice 8

  编写一条SQL查询语句,获取除了programmer外的每个工种的平均salary

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的总薪水,最高薪水,最低薪水和平均薪水

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

SELECT job_id, MAX(salary) max_salary FROM employees
    GROUP BY job_id
    HAVING MAX(salary) >= 4000;

Practice 11

  编写一条SQL查询语句,获取所有employee人数大于10人的的department的平均salary

SELECT department_id, AVG(salary) FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 10;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d 博主赞过: