Oracle Chp3 复杂查询 key point：数据分页；子查询；表连接；集合运算符

分页查询练习
1.查询工资最高的3名员工信息
按照工资排序
利用rownum排序
select t1.* from 
	(select * from employees order by salary desc) t1 
where rownum<=3
2.查询工资排名第5到第10的员工信息
按照工资排序
利用rownum 排序 取前10
取别名，取5-10
select t2.* from
	(select t1.*,rownum rn from
		(select * from employees order by salary desc)t1 
	where rownum<=10)t2
where rn >= 5
3.查询first_name是以大写D开头的第3到第5个员工信息
找出符合条件的员工
利用rownum 获得前5个员工的信息
取别名，获得3-5
select t2.* from
	(select t1.*,rownum rn from
		(select * from employees where first_name like 'D%')t1
	where rownum<=5)t2
where rn >= 3
子查询练习
1.显示工资比’Allan’(first_name)高的所有员工的姓名和工资
找出Allan 的工资
把子查询的结果当值使用
select first_name,salary from employees where salary>
	(select salary from employees where first_name='Allan')
2.显示与’Allan’(first_name)从事相同工作的员工的详细信息
找出Allan的job_id
把子查询结果当单个值使用
select * from employees where job_id=
	(select job_id from employees where first_name='Allan')
3.显示与30号部门first_name为’Guy’员工工资相同的员工姓名和工资
找出30号部门first_name 为Guy的员工的工资
把子查询当单个值 进行比较
select first_name||last_name 姓名,salary from employees where salary=
	(select salary from employees where department_id=30 and first_name='Guy')
4.查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SA_REP')(job_id)
求出平均工资
把子查询当单个值比较
必须是销售人员
select * from employees where job_id='SA_REP' and salary>(select avg(salary) from employees)
5.查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位 
获得平均工资
求出工资大于平均工资的员工
按部门分组统计大于平均工资的员工
select job_id,count(*) from employees where salary>(select avg(salary) from employees) group by job_id
6.查询各个职位员工工资大于平均工资(平均工资包括所有员工)的员工信息
子查询出平均工资
工资大于平均工资并且职位不为空
select * from employees where salary>(select avg(salary) from employees) group by job_id

表连接查询练习(employees、departments、locations)
1.显示所有职员的姓名及其所在部门的名称和工资
员工表和部门表表连接
员工的信息为左表
select first_name||last_name 姓名,department_name,salary from employees,departments where employees.department_id=departments.department_id
2.查询在研发部('IT')工作员工的编号，姓名，工作部门，工作所在地
三表联合 员工表、部门表、地址表
并且 职位有要求
select e.employee_id,first_name||last_name 姓名,department_name,city 
from employees e,departments d,locations l 
where e.department_id=d.department_id 
and d.location_id=l.location_id and e.department_id = 60
3.查询各个部门的名称和员工人数
子查询获得部门编号，以及员工人数
表连接查出名称
select departments.department_name, count(*) 
from employees left join departments on employees.department_id = departments.department_id 
group by departments.department_name;
4.查询工资相同的员工的工资和姓名
获得工资钱数员工人数大于2的钱数
表连接查询出信息
select last_name || first_name 姓名, e.salary 工资 
from employees e,(select salary from employees group by salary having count(*) >= 2) e2
where e.salary = e2.salary
5.显示10号部门的经理和20号部门的所有职员的详细信息
子查询获得 经理的Id
员工表和子查询左外连接
Where 条件
select manager_id from departments where department_id = 10--获得10号部门经理的员工ID
select * from employees where employee_id = (select manager_id from departments where department_id = 10)--获得10号部门的经理的详细信息
select * from employees where department_id = 20--获得20号部门所有员工详细信息
select * from employees where employee_id = (select manager_id from departments where department_id = 10) or department_id = 20--显示10号部门的经理和20号部门的所有职员的详细信息
6.查询员工的基本信息，附加其上级的姓名
子查询获得所有上级的姓名、编号
联合员工表获得员工的信息
select e1.employee_id, e1.first_name, e1.salary, e2.first_name 
from employees e1 left join employees e2 on e1.manager_id = e2.employee_id
7.求入职日期相同（年月日相同）的员工(考察知识点:自连接)
查询入职日期大于1的
表连接
select hire_date from employees group by hire_date having count(*) >= 2--(子查询)当日入职人数大于2的入职日期
select last_name || first_name 姓名, e.hire_date 入职日期
from employees e,(select hire_date from employees group by hire_date having count(*) >= 2) e2
where e.hire_date = e2.hire_date order by e.hire_date
8.显示各个部门经理的基本工资
查询出部门经理 编号
表连接员工表
select employees.employee_id, salary 
from departments left outer join employees on departments.manager_id = employees.employee_id
** 查询平均工资最高的部门信息(考察知识点:子查询,组函数,连接查询)
查询获得所有部门的平均工资
在所以的平均工资 为临时表 查询出 最高的平均 工资
最高平均工资 和 查询平均工资 获得最高平均工资的部门编号
和部门表表连接
select d.* 
from departments d join
	(select t.department_id from 
		(select department_id,avg(salary) avgSalary from 
			employees group by department_id order by avgSalary desc) t 
		where rownum=1) t1 
on d.department_id=t1.department_id