A Programmer

# 使用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;
```