第七天20160803

--集合运算：并集，交集，补集
--并集：UNION/UNION ALL
--查询收入最高与收入最低的员工，显示员工的last_name,salary
SELECT LAST_NAME AS EMP_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES)
UNION
SELECT LAST_NAME AS NAME,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MIN(SALARY) FROM EMPLOYEES)
ORDER BY SALARY ASC;

/*
1.集合运算时，查询结果中列的个数与类型的顺序保持一致。
2.集合运算时，第一个集合可以决定结果中列的标题。
3.集合运算时，ORDER BY子句只能出现在最后一个集合中。
*/
--UNION:自动去重复值。
--UNION ALL:不去重复值，在不考虑重复值的情况下，建议使用UNION ALL，可以提高效率。

SELECT LAST_NAME,SALARY
FROM EMPLOYEES
UNION ALL
SELECT LAST_NAME,SALARY
FROM EMPLOYEES
UNION ALL
SELECT LAST_NAME,SALARY
FROM EMPLOYEES;

--交集:INTERSECT
--查询employees表中工资大于3000，并且在50号部门工资的员工，显示last_name,salary,department_id
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>3000
INTERSECT
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=50;

--补集：MINUS
A:1 2 3 4 5 6
B:3 4 7
A - B = 1 2 5 6
B - A = 7

--查询employees表中工资最高的第二页的员工信息(每页显示5行数据)
SELECT R,LAST_NAME,SALARY
FROM (SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS R,LAST_NAME,SALARY FROM EMPLOYEES)
WHERE R BETWEEN 6 AND 10;

SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC)
WHERE ROWNUM<=10
MINUS
SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC)
WHERE ROWNUM<=5
ORDER BY SALARY DESC;