分组函数

分组函数

/*
显示所有雇员的最高、最低、合计和平均薪水，
列标签分别为： Maximum、Minimum、Sum 和 Average。
四舍五入结果为最近的整数。
*/
select max(salary) Maximum, min(salary) Minimum, round(sum(salary)) Sum, round(avg(salary)) Average 
from employees;

/*
修改上题显示每中工作类型的最低、最高、合计和平均薪水。
*/
select max(salary) Maximum, min(salary) Minimum, round(sum(salary)) Sum, round(avg(salary)) Average 
from employees group by job_id;

/*
写一个查询显示每一工作岗位的人数
*/
select job_id, count(employee_id) from employees group by job_id;

/*
确定经理人数，不需要列出他们，列标签是 Number of Managers。
*/
select count(distinct MANAGER_id) "Number of Managers" from EMPLOYEES;

/*
写一个查询显示最高和最低薪水之间的差。列标签是 DIFFERENCE
*/
select max(salary) - min(salary) DIFFERENCE from employees;

/*
显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。
排除最低薪水小于等于 $6,000 的组。按薪水降序排序
输出。
*/
select MANAGER_ID, min(SALARY)
from (select * from EMPLOYEES where MANAGER_ID is not null)
group by MANAGER_ID having min(SALARY) > 6000
order by min(SALARY) desc;

/*
写一个查询显示每个部门的名字、地点、人数和部门中所有
雇员的平均薪水。四舍五入薪水到两位小数。
*/
select DEP.DEPARTMENT_NAME, DEP.LOCATION_ID, count(EMP.EMPLOYEE_id), round(avg(EMP.SALARY), 2)
from EMPLOYEES EMP, DEPARTMENTS DEP
where EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
group by DEP.DEPARTMENT_NAME, DEP.LOCATION_ID;

/*
创建一个查询显示雇员总数，和在 2001、2002、2003 和
受雇的雇员人数。创建适当的列标题。
*/
select count(*) total,
sum(decode(to_char(hire_date,'yyyy'),'2000',1,0))"2000" ,
sum(decode(to_char(hire_date,'yyyy'),'2001',1,0))"2001",
sum(decode(to_char(hire_date,'yyyy'),'2002',1,0))"2002",
sum(decode(to_char(hire_date,'yyyy'),'2003',1,0))"2003" 
from employees e;


/*
创建一个混合查询显示工作岗位和工作岗位的薪水合计，
并且合计部门 20、50、80 和 90 的工作岗位的薪水。给每
列一个恰当的列标题。
*/
select job_id ,sum(emp.salary) total,
sum(decode(emp.department_id,20,1,0))"20",
sum(decode(emp.department_id,50,1,0))"50",
sum(decode(emp.department_id,80,1,0))"80",
sum(decode(emp.department_id,90,1,0))"90" 
from employees emp
group by job_id;