1. 组函数处理多行返回一行吗?
是
2. 组函数不计算空值吗?
是
3. where子句可否使用组函数进行过滤?
不可以,用having替代
4. 查询公司员工工资的最大值,最小值,平均值,总和
a) select max(salary),min(salary),avg(salary),sum(salary)
b) from employees
5. 查询各job_id的员工工资的最大值,最小值,平均值,总和
a) select job_id,max(salary),min(salary),avg(salary),sum(salary)
b) from employees
c) group by job_id
6. 选择具有各个job_id的员工人数
a) select job_id,count(employee_id)
b) from employees
c) group by job_id
7. 查询员工最高工资和最低工资的差距(DIFFERENCE)
a) select max(salary),min(salary),max(salary)-min(salary) "DIFFERENCE"
b) from employees
8. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
a) select manager_id,min(salary)
b) from employees
c) where manager_id is not null
d) group by manager_id
e) having min(salary) >= 6000
9. 查询所有部门的名字,location_id,员工数量和工资平均值
a) select department_name,location_id,count(employee_id),avg(salary)
b) from employees e right outer join departments d
c) on e.department_id = d.department_id
d) group by department_name,location_id
10. 查询公司在1995-1998年之间,每年雇用的人数,结果类似下面的格式
total 1995 1996 1997 1998
20 3 4 6 7
select count(*) "total",
count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')