第六天20160802

SELECT EMP.LAST_NAME,DEPT.DEPARTMENT_NAME,LOC.CITY
FROM EMPLOYEES EMP LEFT JOIN DEPARTMENTS DEPT ON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID
LEFT JOIN LOCATIONS LOC ON LOC.LOCATION_ID=DEPT.LOCATION_ID;

SELECT DEPT.DEPARTMENT_NAME,COUNT(EMP.EMPLOYEE_ID)
FROM EMPLOYEES EMP RIGHT JOIN DEPARTMENTS DEPT
ON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID
GROUP BY DEPT.DEPARTMENT_NAME
ORDER BY DEPT.DEPARTMENT_NAME ASC;

--子查询(嵌套查询)
--查询哪些员工的工资大于176号员工的工资，显示这些员工的last_name,salary,department_id
SELECT SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID=176;

--普通子查询：先执行子查询，再执行主查询。
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>(SELECT SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID=176);

/*
1.从Oracle8i开始除了GROUP BY子句外，其它子句都可以使用子查询。
2.无论子查询出现在哪个子句中，都必须放在一对小括号内。
3.如果子查询作为条件，尽量将子查询放在运算符的右边，可以提高查询的效率。
4.如果子查询作为条件，子查询中查询列的个数与类型必须与主查询中条件列的个数与类型保持一致。
5.除非做TOP N的操作，否则不能在子查询中作用ORDER BY子句。
*/

--子查询与组函数
--查询employees表中收入最高的员工的last_name,salary
SELECT LAST_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES);

--查询employees表中收入大于公司平均工资的员工，显示last_name,salary(格式如:$50,000.00)
SELECT LAST_NAME,TO_CHAR(SALARY,'$999,999.00')
FROM EMPLOYEES
WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEES);

--查询与入职最早员工在同一部门工作的员工的last_name,hire_date,department_id
SELECT LAST_NAME,HIRE_DATE,DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE=(SELECT MIN(HIRE_DATE) FROM EMPLOYEES));

--HAVING与子查询
--查询平均工资大于公司平均工资的部门编号与平均工资。
SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY)>(SELECT AVG(SALARY) FROM EMPLOYEES)
ORDER BY 1 ASC;

--查询人数最多的部门，显示department_id与人数。
SELECT DEPARTMENT_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);

--多行子查询:ALL,ANY,IN
--查询工资大于60号部门所有员工工资的员工的last_name,salary,department_id
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>(SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID=60);

SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>ALL(SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=60);

--查询工资大于60号部门任意一名员工的员工的last_name,salary,department_id
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>(SELECT MIN(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID=60);

SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY=ANY(SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=60);

SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY IN (SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=60);

--在SELECT子句中使用子查询:子查询只能返回一行一列。
--查询每个部门的department_id,人数以及此部门人数占公司人数的百分比。
SELECT DEPARTMENT_ID,COUNT(*),(COUNT(*)/(SELECT COUNT(*) FROM EMPLOYEES))*100||'%' AS 百分比
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1 ASC;

--在FROM子句中使用子查询
--查询employees收入最低的前5名员工，显示last_name,salary
SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC)
WHERE ROWNUM<=5;

--SQL Server中TOP N的操作
SELECT TOP 5 LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC;

--MySQL中TOP N的操作
SELECT LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC
LIMIT 5;

--伪列：在创建表时ORACLE自动为表添加的列。
--ROWID:数据在硬盘或内存中的地址。
--ROWNUM:行号。只能从表的第一行开始查询数据(只能等于1,或小于N,或小于等于N)。

SELECT ROWID,LAST_NAME
FROM EMPLOYEES;

SELECT ROWNUM,LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC;

--排名
--查询employees表中所有员工的:名次(根据工资排名),last_name,salary
SELECT
ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS 名次1,
RANK() OVER(ORDER BY SALARY DESC) AS 名次2,
DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 名次3,
LAST_NAME,SALARY
FROM EMPLOYEES;

--翻页功能：
--查询employees表第二页的员工信息(每页显示5行数据，注意：本例中没有排序的列)
SELECT R,LAST_NAME,SALARY
FROM (SELECT ROWNUM AS R,LAST_NAME,SALARY
FROM EMPLOYEES)
WHERE R BETWEEN 6 AND 10;

--查询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;

--高级子查询
--成对子查询
--查询每个部门中工资最高的员工，显示last_name,department_id,salary
SELECT LAST_NAME,DEPARTMENT_ID,SALARY
FROM EMPLOYEES
WHERE (DEPARTMENT_ID,SALARY) IN (SELECT DEPARTMENT_ID,MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);

--相关子查询(关联子查询):子查询使用了主查询中相关的列。相关子查询先执行主查询，再执行子查询。
--查询每个部门中工资大于所在部门平均工资的员工，显示last_name,department_id,salary
SELECT LAST_NAME,DEPARTMENT_ID,SALARY
FROM EMPLOYEES EMP
WHERE SALARY>(SELECT AVG(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID=EMP.DEPARTMENT_ID);

--集合运算：并集，交集，补集
--并集：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子句只能出现在最后一个集合中。
*/