PL/SQL-----分组函数练习01

--1，查询employees表中有多少个部门

select count(distinct department_id)
from employees;

--2,查询全公司奖金基数的平均值（没有奖金的人按0计算）

select avg(nvl(commission_id,0))
from employees;

--3，查询各个部门的平均工资

--错误：avg(salary)返回公司平均工资，只有一个值；而department_id有多个值，无法匹配返回
select department_id,avg(salary)
from employees;

--正确：按department_id进行分组
select department_id,avg(salary)
from employees
group by department_id;

--4,Toronto这个城市的员工的平均工资

select avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
and lower(l.city='toronto')

--5,(有员工的城市)各个城市的平均工资

select city,avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
and group by city

--6,查询平均工资高于8000的部门id和他的平均工资

select department_id,avg(salary)
from employees
having avg(salary)>8000
group by department_id

--7,查询最高工资高于8000的部门id和他的平均工资

select department_id,avg(salary)
from employees
having max(salary)>8000
group by department_id

--8,查询平均高于6000的job_title有哪些

select job_title,avg(salary)
from employees e join jobs j
on e.job_id=j.job_id
group by job_title
having avg(dalary)>6000