8. 聚合函数练习题

【题目】

1.where子句可否使用组函数进行过滤?

select max(salary), min(salary), avg(salary), sum(salary)  
from employees e ;

2.查询公司员工工资的最大值，最小值，平均值，总和

select max(salary), min(salary), avg(salary), sum(salary)  
from employees e 
group by job_id 

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

select job_id , max(salary), min(salary), avg(salary), sum(salary)  
from employees e 
group by job_id 

4.选择具有各个job_id的员工人数

select job_id, count(*)
from employees e 
group by job_id

5.查询员工最高工资和最低工资的差距(DIFFERENCE)

select max(salary )- min(salary ) DIFFERENCE
from employees e 

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select manager_id, min(salary)
from employees e 
where manager_id is not null  # 我自己做的时候没加这句，错了
group by manager_id 
having min(salary) >= 6000

7.查询所有部门的名字，location_id，员工数最和平均工资，并按平均工资降序

select department_name , location_id , count(e.department_id), avg(salary)
from departments d left join employees e 
on d.department_id = e.department_id 
group by d.department_id 
order by avg(salary) desc  

我自己做的时候用的是count(*)，所以搜出来的结果不对，count是1，但是avg是null，明明没有员工但是count会显示1

8.查询每个工种、每个部门的部门名、工种名和最低工资

select e.job_id , d.department_name , min(salary)
from employees e right join departments d
on e.department_id = d.department_id 
group by e.job_id , d.department_id 

查询每个工种、每个部门，所以group要有两个，我只写了一个还以为是题目有问题