oracle学习80-oracle之单行函数之多表查询值之课后练习

26. 多表连接查询时, 若两个表有同名的列, 必须使用表的别名对列名进行引用, 否则出错!
 
27. 查询出公司员工的 last_name, department_name, city
 
	
	select last_name, department_name, city
	from departments d, employees e, locations l
	where d.department_id = e.department_id and d.location_id = l.location_id
 
28. 查询出 last_name 为 'Chen' 的 manager 的信息. (员工的 manager_id 是某员工的 employee_id) 
	
	0). 例如: 老张的员工号为: "1001", 我的员工号为: "1002", 
 
            我的 manager_id 为 "1001" --- 我的 manager 是"老张" 
 
	1). 通过两条 sql 查询:
  
			select manager_id
			from employees
			where lower(last_name) = 'chen' --返回的结果为 108
			
			select *
			from employees
			where employee_id = 108
			
	2). 通过一条 sql 查询(自连接):
			
			select m.*
			from employees e, employees m
			where e.manager_id = m.employee_id and e.last_name = 'Chen'		
			
	3). 通过一条 sql 查询(子查询):	
			
			select *
			from employees
			where employee_id = (
			                      select manager_id 
			                      from employees
			                      where last_name = 'Chen'
			                    )	
 
29. 查询每个员工的 last_name 和 GRADE_LEVEL(在 JOB_GRADES 表中). ---- 非等值连接
 
			select last_name, salary, grade_level, lowest_sal, highest_sal
			from employees e, job_grades j
			where e.salary >= j.lowest_sal and e.salary <= j.highest_sal
			
30. 左外连接和右外连接
 
			select last_name, e.department_id, department_name
			from employees e, departments d
			where e.department_id = d.department_id(+)
			
			select last_name, d.department_id, department_name
			from employees e, departments d
			where e.department_id(+) = d.department_id
			
			理解 "(+)" 的位置: 以左外连接为例, 因为左表需要返回更多的记录,
			右表就需要 "加上" 更多的记录, 所以在右表的链接条件上加上 "(+)"
			
			注意: 1). 两边都加上 "(+)" 符号, 会发生语法错误!
			      2). 这种语法为 Oracle 所独有, 不能在其它数据库中使用.			
			      
31. SQL 99 连接 Employees 表和 Departments 表
			1).
			select *
			from employees join departments
			using(department_id)
			
			缺点: 要求两个表中必须有一样的列名.
			
			2).
			select *
			from employees e join departments d
			on e.department_id = d.department_id
			
			3).多表连接
			select e.last_name, d.department_name, l.city
			from employees e join departments d
			on e.department_id = d.department_id
			join locations l
			on d.location_id = l.location_id			     
			
32. SQL 99 的左外连接, 右外连接, 满外连接
			1).
			select last_name, department_name
			from employees e left outer join departments d
			on e.department_id = d.department_id
			
			2).
			select last_name, department_name
			from employees e right join departments d
			on e.department_id = d.department_id
			
			3).
			select last_name, department_name
			from employees e full join departments d
			on e.department_id = d.department_id	

1.	显示所有员工的姓名，部门号和部门名称。
a)	select last_name,e.department_id,department_name
b)	from employees e,departments d
c)	where e.department_id = d.department_id(+)
 
方法二：
select last_name,e.department_id,department_name
from employees e left outer join departments d
on e.department_id = d.department_id
2.	查询90号部门员工的job_id和90号部门的location_id
a)	select distinct job_id,location_id
b)	from employees e left outer join departments d
c)	on e.department_id = d.department_id
d)	where d.department_id = 90
3.	选择所有有奖金的员工的
last_name , department_name , location_id , city
select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where e.commission_pct is not null
4.	选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 
select last_name , job_id , e.department_id , department_name
from employees e ,departments d,locations l
where e.department_id = d.department_id and l.city = 'Toronto' and d.location_id = l.location_id
5.	选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar	101	king	100
select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name"Manger",e2.employee_id "Mgr#"
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+)