w3resource_MySQL练习题：Aggregate_functions 1. Write a query to list the number of jobs available in the employees tableSample table: employees
-- 要点：count() + distinctselect 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 tableSample 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 employeeswhere job_id = 'IT_PROG'
5. Write a query to get the average salary and number of employees working the department 90Sample table: employees
-- 要点：avg() + count()select avg(salary), count(*)from employeeswhere 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 jobSample table: employees
-- 要点：group by进行分组select job_id, count(*)from employeesgroup 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 managerSample table: employees
-- 要点：根据manager_id进行分组，得到每个分组内最低的salary(min)select manager_id, min(salary)from employeesgroup by manager_id
10. Write a query to get the department ID and the total salary payable in each departmentSample table: employees
-- 要点：根据department_id进行分组，得到每个分组内salary总和(sum)select department_id, sum(salary)from employeesgroup by department_id
11. Write a query to get the average salary for each job ID excluding programmerSample table: employees
-- 要点：通过job_id进行分组，得到每个分组内平均的salary(avg)，select job_id, avg(salary)from employeeswhere 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 onlySample table: employees
-- 要点：同上，通过job_id进行分组，得到每个组内相应数值，并且使用where进行department_id筛选select job_id, sum(salary), max(salary), min(salary), avg(salary)from employeeswhere department_id=90group 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
-- 要点：分组后的条件限制，使用havingselect job_id, max(salary)from employeesgroup by job_idhaving max(salary)>=4000
14. Write a query to get the average salary for all departments employing more than 10 employeesSample table: employees
-- 要点：同上，分组后使用havingselect department_id, avg(salary)from employeesgroup by department_idhaving count(salary)>10
