oracle多表查询

oracle基础
oracle单行函数
oracle多表查询
oracle分组函数
oracle子查询

---------------- 多表查询 -----------------

-- 笛卡尔积错误 （原因就是没有加条件）
-- 查询员工表与部门表
-- 2889条
--会拿EMPLOYEES表（左表）中的每一条数据与DEPARTMENTS（右表）中的每一条数据匹配 即 107*27
SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.LAST_NAME,DEPARTMENTS.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_NAME
FROM EMPLOYEES ,DEPARTMENTS  
-- 2889
SELECT 107*27 FROM dual;
----------------------
-- 内连接 等值连接：
-- 内连接 ：合并具有同一列的两个以上的表得行，结果集中不包含一个表与另一个表不匹配的行

--正确写法
-- 106条，有一条列为NULL所以包含在结果集中
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e,DEPARTMENTS  d 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID


--连接n个表，至少需要n-1个连接条件
-- 查询公司员工的LAST_NAME，DEPARTMENT_ID，CITY
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME,l.CITY
FROM EMPLOYEES e,DEPARTMENTS  d, LOCATIONS l 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.LOCATION_ID = l.LOCATION_ID


--内连接 非等值连接

-- 查询公司 每个员工的工资属于第几档工资
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,GRADE_LEVEL
FROM EMPLOYEES e,JOB_GRADES j
WHERE SALARY BETWEEN LOWEST_SAL AND HIGHEST_SAL


-- 外连接
-- 两个表在连接过程汇总除了返回满足条件连接的行以外
-- * 还返回左（或右）表汇总不满足条件的行，这种连接称为（左或者右）外连接
-- 没有匹配的行时，结果表中的列为NULL，外连接的where子句条件类似于内部连接
-- * 但连接条件中没有匹配行的表得列后面要加外连接运算符，即用圆括号括起来的加号（+）
-- 例：
-- 107 条
-- 左外连接 （有员工，员工可能没部门）
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e,DEPARTMENTS  d 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)
-- 右外连接 （有部门，部门里面没有员工）
-- 122 条
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e,DEPARTMENTS  d 
WHERE e.DEPARTMENT_ID(+) = d.DEPARTMENT_ID

-- join on 方式 内连接
-- 两个表连接 106条记录
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e JOIN DEPARTMENTS d 
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- 三个表连接 106条记录
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,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

-- 左外连接 （有员工，员工可能没部门）107条记录
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e 
left outer join DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- 右外连接 （有部门，部门里面没有员工）122条记录
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e 
right outer join DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- 满外连接（左右连接的结果都想出来） 123条记录
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e 
full outer join DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID



---------------------------
--自连接 
-- 查询员工'chen' 的mannage信息
-- 需要通过两条语句查询出结果，自连接可以一条sql查询出来
select MANAGER_ID 
FROM EMPLOYEES
WHERE LOWER(LAST_NAME) = 'chen'

SELECT LAST_NAME,SALARY,EMPLOYEE_ID,EMAIL
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 108

-- 查询员工'chen' 的mannage信息
SELECT emp.LAST_NAME,manager.LAST_NAME,manager.SALARY,manager.EMAIL,manager.EMPLOYEE_ID
FROM EMPLOYEES emp, EMPLOYEES manager
WHERE emp.MANAGER_ID = manager.EMPLOYEE_ID AND LOWER(emp.LAST_NAME)  = 'chen'


-- 练习题

--查询出公司员工的 last_name, department_name, city

SELECT e.LAST_NAME,d.DEPARTMENT_NAME,l.CITY
FROM EMPLOYEES e 
left join DEPARTMENTS d on e.DEPARTMENT_ID = d.DEPARTMENT_ID
left join LOCATIONS l   on d.LOCATION_ID = l.LOCATION_ID


SELECT e.LAST_NAME,d.DEPARTMENT_NAME,l.CITY
FROM EMPLOYEES e , DEPARTMENTS d, LOCATIONS l 
WHERE e.DEPARTMENT_ID = d.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'

SELECT * 
FROM EMPLOYEES
WHERE EMPLOYEE_ID = '108'

-- 2). 通过一条 sql 查询(自连接):

SELECT em.LAST_NAME,ma.*
FROM EMPLOYEES em,EMPLOYEES ma
WHERE LOWER(em.LAST_NAME) = 'chen' and ma.EMPLOYEE_ID = em.MANAGER_ID

-- 3) 通过一条 sql 查询(子查询):
SELECT * 
FROM EMPLOYEES
WHERE EMPLOYEE_ID =  (SELECT MANAGER_ID
FROM EMPLOYEES
WHERE LOWER(LAST_NAME) = 'chen')

--29. 查询每个员工的 last_name 和 GRADE_LEVEL(在 JOB_GRADES 表中). ---- 非等值连接

SELECT e.LAST_NAME,j.GRADE_LEVEL
FROM EMPLOYEES e, JOB_GRADES j
WHERE e.SALARY BETWEEN j.LOWEST_SAL AND HIGHEST_SAL


SELECT e.LAST_NAME,j.GRADE_LEVEL
FROM EMPLOYEES e join JOB_GRADES j
on e.SALARY BETWEEN j.LOWEST_SAL AND 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 表

-- 缺点: 要求两个表中必须有一样的列名.
SELECT *
FROM EMPLOYEES join DEPARTMENTS
using(DEPARTMENT_ID)

SELECT * 
FROM EMPLOYEES e join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID

-- 三表连接
SELECT * 
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 的左外连接, 右外连接, 满外连接 (outer)可省略
-- 107条
SELECT * 
FROM EMPLOYEES e 
left join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- 122条
SELECT * 
FROM EMPLOYEES e 
right join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- 123条
SELECT * 
FROM EMPLOYEES e 
full join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID

-- 1.	显示所有员工的姓名，部门号和部门名称。
SELECT e.LAST_NAME , e.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e
left join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID


SELECT e.LAST_NAME , e.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)


--2.	查询90号部门员工的job_id和90号部门的location_id

SELECT e.LAST_NAME,d.DEPARTMENT_ID,d.LOCATION_ID 
FROM EMPLOYEES e
left join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.DEPARTMENT_ID = 90 


-- 3.	选择所有有奖金的员工的last_name , department_name , location_id , city

SELECT e.LAST_NAME,d.DEPARTMENT_NAME,l.LOCATION_ID,l.CITY
FROM EMPLOYEES e
left join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
left 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 e.LAST_NAME,e.JOB_ID,e.DEPARTMENT_ID,d.DEPARTMENT_NAME 
FROM EMPLOYEES e
left join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
left join LOCATIONS l
on d.LOCATION_ID = l.LOCATION_ID
WHERE l.CITY = 'Toronto'

-- 5.	选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
/*
employees	Emp#	manager	Mgr#
kochhar	101	king	100
*/
SELECT em.LAST_NAME as "employees",em.EMPLOYEE_ID as "Emp#",ma.LAST_NAME as "manager",ma.EMPLOYEE_ID as "Mgr#"
FROM EMPLOYEES em,EMPLOYEES ma
WHERE em.MANAGER_ID = ma.EMPLOYEE_ID(+)