oracle之单行函数之分组函数之课后练习

33. 查询 employees 表中有多少个部门

		select count(distinct department_id)
		from employees		
		
34. 查询全公司奖金基数的平均值(没有奖金的人按 0 计算)

		select avg(nvl(commission_pct, 0))
		from employees		
		
35. 查询各个部门的平均工资

		--错误: avg(salary) 返回公司平均工资, 只有一个值; 而 department_id 有多个值, 无法匹配返回
		select department_id, avg(salary)
		from employees				 
		
		**在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
		
		--正确: 按 department_id 进行分组
		select department_id, avg(salary)
		from employees
		group by department_id
		
36. Toronto 这个城市的员工的平均工资
		
SELECT avg(salary)
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE city = 'Toronto'		

37. (有员工的城市)各个城市的平均工资
	
SELECT city, avg(salary)
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
GROUP BY city		

38. 查询平均工资高于 8000 的部门 id 和它的平均工资.		

SELECT department_id, avg(salary)
FROM employees e 
GR