test_lesson05分组函数

测试

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(salar

y)

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年之间，每年雇用的人数，结果类似下面

的格式

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')