SQL练习（四）

http://www.cnblogs.com/huyong/archive/2011/06/22/2086772.html

倒数第二篇了！继续！加油！

/*
第四篇
*/
1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
select AVG(e.salery) as salery_avg,MAX(e.salery) as salery_max,MIN(e.salery) as salery_min, COUNT(e.employee_id) 
from employee e group by department_id order by department_id 

2. 各个部门中工资大于5000的员工人数。
select COUNT(e.employee_id) from employee e  where e.salery > 5000 group by department_id

3. 各个部门平均工资和人数，按照部门名字升序排列。
select AVG(e.salery) as salery_avg,COUNT(e.employee_id) 
from employee e, department d where e.department_id = d.department_id group by department_id order by department_name;

4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。
select e1.department_id as department_id, e1.salery as salery, count(*)
from employee e1,employee e2 where e1.salery = e2.salery and e1.employee_id != e2.employee_id and e1.department_id = d2.department_id
group by e1.department_id,e1.salery;

5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。
select d.department_name,r.city
from employee e, department d,regions r where e.salery > 1000 and e.department_id = d.department_id and  d.loction_id = regions.loction_id
group by e.department_id,r.city having count(*)>2

6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。
select e.employee_name,e.salery from employee e where  e.salery > (select AVG(salery) from employee) order by e.salery DESC;

7. 哪些员工的工资，介于50号 和80号部门平均工资之间。
select * from employee where salery between (select AVG(salery) from employee where employee_id='50') and (select AVG(salery) from employee where employee_id='80');

8. 所在部门平均工资高于5000 的员工名字。
select e.employee_name from employee e where e.department_id in 
(select department_id from department group by department_id having AVG(salery) > 5000)

9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
select e.ename,e.department_id,e.salery from employee where (department_id,salery) in
(select department_id, MAX(salery) from employee group by department_id);

10. 最高的部门平均工资是多少。
select MAX(avg_salary) from (select department_id, AVG(salery) as avg_salary from employee group by department_id);