w3resource_MySQL练习题:
Aggregate_functions
1. Write a query to list the number of jobs available in the employees table
Sample table: employees
-- 要点:count() + distinct
select count(distinct job_id)
from employees
2. Write a query to get the total salaries payable to employees
Sample table: employees
-- 要点:sum()
select sum(salary)
from employees
3. Write a query to get the minimum salary from employees table
Sample table: employees
-- 要点:min()
select min(salary)
from employees
4. Write a query to get the maximum salary of an employee working as a Programmer
Sample table: employees
-- 要点:max()
select max(salary)
from employees
where job_id = 'IT_PROG'
5. Write a query to get the average salary and number of employees working the department 90
Sample table: employees
-- 要点:avg() + count()
select avg(salary), count(*)
from employees
where department_id=90
6. Write a query to get the highest, lowest, sum, and average salary of all employees
Sample table: employees
-- 要点:max() + min() + sum() + avg()
select max(salary), min(salary), sum(salary), avg(salary)
7. Write a query to get the number of employees with the same job
Sample table: employees
-- 要点:group by进行分组
select job_id, count(*)
from employees
group by job_id
8. Write a query to get the difference between the highest and lowest salaries
Sample table: employees
-- 要点:max() + min()
select max(salary)-min(salary)
from employees
9. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager
Sample table: employees
-- 要点:根据manager_id进行分组,得到每个分组内最低的salary(min)
select manager_id, min(salary)
from employees
group by manager_id
10. Write a query to get the department ID and the total salary payable in each department
Sample table: employees
-- 要点:根据department_id进行分组,得到每个分组内salary总和(sum)
select department_id, sum(salary)
from employees
group by department_id
11. Write a query to get the average salary for each job ID excluding programmer
Sample table: employees
-- 要点:通过job_id进行分组,得到每个分组内平均的salary(avg),
select job_id, avg(salary)
from employees
where job_id<>'IT_PROG'
group by job_id
12. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only
Sample table: employees
-- 要点:同上,通过job_id进行分组,得到每个组内相应数值,并且使用where进行department_id筛选
select job_id, sum(salary), max(salary), min(salary), avg(salary)
from employees
where department_id=90
group by job_id
13. Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000
Sample table: employees
-- 要点:分组后的条件限制,使用having
select job_id, max(salary)
from employees
group by job_id
having max(salary)>=4000
14. Write a query to get the average salary for all departments employing more than 10 employees
Sample table: employees
-- 要点:同上,分组后使用having
select department_id, avg(salary)
from employees
group by department_id
having count(salary)>10