oracle笔记高级子查询

/*

高级子查询(注意：这部分知识点有点难)

1.书写多列子查询
2.在 FROM 子句中使用子查询
3.在SQL中使用单列子查询
4.书写相关子查询
5.使用 EXISTS 和 NOT EXISTS 操作符
6.使用子查询更新和删除数据
7.使用 WITH 子句

*/

--书写多列子查询
--查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

--方式1(多列子查询)
SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID
  FROM EMPLOYEES
 WHERE (MANAGER_ID, DEPARTMENT_ID) IN
       (SELECT MANAGER_ID, DEPARTMENT_ID
          FROM EMPLOYEES
         WHERE EMPLOYEE_ID IN (141, 174))
   AND EMPLOYEE_ID NOT IN (141, 174)

--方式2(单列子查询)
SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID
  FROM EMPLOYEES
 WHERE MANAGER_ID IN
       (SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN (174, 141))
   AND DEPARTMENT_ID IN
       (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN (174, 141))
   AND EMPLOYEE_ID NOT IN (174, 141)
   
--在FROM 子句中使用子查询
--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
--方式1 (存在冗余)
SELECT LAST_NAME,
       DEPARTMENT_ID,
       SALARY,
       (SELECT AVG(SALARY)
          FROM EMPLOYEES E3
         WHERE E1.DEPARTMENT_ID = E3.DEPARTMENT_ID
         GROUP BY DEPARTMENT_ID) AVG_SALARY
  FROM EMPLOYEES E1
 WHERE SALARY > (SELECT AVG(SALARY)
                   FROM EMPLOYEES E2
                  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
                  GROUP BY DEPARTMENT_ID)
--方式2(在FROM子句中使用子查询)
SELECT last_name, emp1.department_id, salary, emp2.dep_avg_salary AS "部门平均工资"
FROM employees emp1, (SELECT department_id, AVG(salary) AS dep_avg_salary FROM employees GROUP BY department_id) emp2
WHERE emp1.department_id = emp2.department_id
AND emp1.salary > emp2.dep_avg_salary


--单列子查询表达式

/*
单列子查询表达式
子查询可以使用在很多地方，比如：
SELECT 语句 (FROM 和 WHERE 子句)
INSERT 语句中的VALUES列表中
DECODE  和 CASE
SELECT 中除 GROUP BY 子句以外的所有子句中
ORDER BY子句中也可以使用单列子查询 如：ORDER BY(子查询)
*/

/*

显式员工的employee_id,last_name和location。其中，若员
工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’

*/
--
SELECT EMPLOYEE_ID,
       LAST_NAME,
       (CASE DEPARTMENT_ID
         WHEN
          (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID = 1800) THEN
          'Canada'
         ELSE
          'USA'
       END) LOCATION
  FROM EMPLOYEES

--
SELECT EMPLOYEE_ID,
       LAST_NAME,
       (CASE
         WHEN DEPARTMENT_ID =
              (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID = 1800) THEN
          'Canada'
         ELSE
          'USA'
       END) LOCATION
  FROM EMPLOYEES

--在 ORDER BY 子句中使用单列子查询
--查询员工的employee_id,last_name,要求按照员工的department_name排序
--员工编号为178, 199的这2个员工的部门id为空，排序的时候没有把这2个部门id为空的员工排进去
SELECT employee_id, last_name, department_name
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
ORDER BY dep.department_name ASC
/*
SELECT * 
FROM employees
WHERE employee_id IN (178, 199)
*/
--在 ORDER BY 子句中使用单列子查询
--员工编号为178, 199的这2个员工的部门id为空，排序的时候会把这2个部门id为空的员工排进去
SELECT employee_id, last_name
FROM employees emp
ORDER BY (
SELECT dep.department_name
FROM departments dep
WHERE emp.department_id = dep.department_id
) ASC --这是一个相关子查询

--相关子查询
--查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT LAST_NAME, SALARY, DEPARTMENT_ID
  FROM EMPLOYEES
 emp1 WHERE SALARY > (SELECT AVG(SALARY)
                         FROM EMPLOYEES
                        WHERE DEPARTMENT_ID = emp1.DEPARTMENT_ID)

/*

若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输
出这些相同id的员工的employee_id,last_name和其job_id

*/

--相关子查询                        
SELECT EMPLOYEE_ID, LAST_NAME, EMP.JOB_ID
  FROM EMPLOYEES EMP
 WHERE 2 <= (SELECT COUNT(EMP.EMPLOYEE_ID)
               FROM JOB_HISTORY
              WHERE EMPLOYEE_ID = EMP.EMPLOYEE_ID)
--相关子查询                        
SELECT EMPLOYEE_ID, LAST_NAME, EMP.JOB_ID
  FROM EMPLOYEES EMP
 WHERE 2 <= (SELECT COUNT(*)
               FROM JOB_HISTORY
              WHERE EMPLOYEE_ID = EMP.EMPLOYEE_ID)
              
--

--EXISTS 操作符
--查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees 
WHERE employee_id IN (
SELECT Distinct emp.manager_id 
FROM employees emp
)
--
SELECT employee_id, last_name, job_id, department_id
FROM employees emp1
WHERE employee_id IN (
SELECT emp2.manager_id 
FROM employees emp2
WHERE emp1.employee_id = emp2.manager_id
)
--
SELECT distinct emp1.employee_id, emp1.last_name, emp1.job_id, emp1.department_id
FROM employees emp1, employees emp2
WHERE emp1.employee_id = emp2.manager_id
--使用EXISTS关键字
SELECT employee_id, last_name, job_id, department_id
FROM employees emp1
WHERE EXISTS (
SELECT 'aaa' 
FROM employees emp2
WHERE emp1.employee_id = emp2.manager_id
)

--题目：查询departments表中，不存在于employees表中的部门的department_id和department_name
--使用NOT EXISTS关键字
SELECT department_id, department_name
FROM departments dep
WHERE NOT EXISTS(
SELECT 'ccc'
FROM employees emp
WHERE emp.department_id = dep.department_id
)
--以下这种写法不可以达到题目的目的
SELECT department_id, department_name
FROM departments dep
MINUS
SELECT emp.department_id, to_char(NULL)
FROM employees emp

--以下这种写法可以达到题目的目的(遗憾的是无法显示department_name这一列)
SELECT department_id
FROM departments dep
MINUS
SELECT emp.department_id
FROM employees emp


--以下这种写法达不到题目的目的
SELECT department_id, department_name
FROM departments dep
WHERE EXISTS(
SELECT 'ccc'
FROM employees emp
WHERE emp.department_id != dep.department_id
)
--
SELECT * 
FROM departments dep
WHERE dep.department_id = 20
--
SELECT * 
FROM employees emp
WHERE emp.department_id = 20
--
--相关更新

--1.创建一个新表
CREATE TABLE empaaa
AS
SELECT * 
FROM employees
--增加一列
ALTER TABLE empaaa
ADD (department_name VARCHAR(30))
--
SELECT * FROM 
empaaa
--
UPDATE empaaa emp
SET department_name = (
SELECT dep.department_name
FROM departments dep
WHERE dep.department_id = emp.department_id
)

--
SELECT * FROM 
empaaa

--相关删除
--题目，删除表employees中，其与emp_history表皆有的数据
SELECT * 
FROM job_history

--以下这种写法会出错
--SELECT DISTINCT employee_id, * 
--FROM job_history

--
SELECT DISTINCT employee_id, job_history.* 
FROM job_history
--
SELECT DISTINCT(employee_id), job_history.* 
FROM job_history
--
SELECT DISTINCT(employee_id)
FROM job_history
--
SELECT DISTINCT employee_id, job_id
FROM job_history

--以下这种写法是错误的
--SELECT DISTINCT employee_id, DISTINCT job_id
--FROM job_history

--
CREATE TABLE empbbb
AS
SELECT *
FROM employees emp
WHERE emp.department_id IN (80, 90)
--
SELECT * 
FROM empbbb
ORDER BY employee_id ASC

--相关删除
DELETE 
FROM empbbb emp
WHERE emp.employee_id = (
SELECT DISTINCT his.employee_id 
FROM job_history his
WHERE his.employee_id = emp.employee_id
)
--相关删除
DELETE 
FROM empbbb emp
WHERE emp.employee_id IN (
SELECT DISTINCT his.employee_id 
FROM job_history his
WHERE his.employee_id = emp.employee_id
)
--相关删除
DELETE 
FROM empbbb emp
WHERE emp.employee_id IN (
SELECT his.employee_id 
FROM job_history his
WHERE his.employee_id = emp.employee_id
)

--
CREATE TABLE empccc
AS
SELECT * 
FROM employees emp
WHERE emp.department_id IN (80, 90)
--
CREATE TABLE empddd
AS
SELECT * 
FROM employees emp
WHERE emp.department_id IN (90)
--
SELECT * 
FROM empccc
--
SELECT * 
FROM empddd
--相关删除
DELETE FROM
empccc e01
WHERE e01.department_id = (
SELECT DISTINCT e02.department_id
FROM empddd e02
WHERE e02.department_id = e01.department_id
)
--相关删除
DELETE FROM
empccc e01
WHERE e01.department_id IN (
SELECT DISTINCT e02.department_id
FROM empddd e02
WHERE e02.department_id = e01.department_id
)
--相关删除
DELETE FROM
empccc e01
WHERE e01.department_id IN (
SELECT e02.department_id
FROM empddd e02
WHERE e02.department_id = e01.department_id
)
--


--with子句
--题目：查询公司中工资比Abel工资高的员工信息
--老知识解决这道题目
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE LOWER(last_name) = 'abel'
)
--使用with子句来解决这道题目(使用with关键字)
--
WITH abel_salary --WITH后面跟一个别名，别名随便取，可以把这个别名理解成表
AS (
SELECT salary --salary这一列自然而然的成为了abel_salary这张临时表中的列了，后面可以使用abel_salary这张临时表中的salary这一列
FROM employees
WHERE LOWER(last_name) = 'abel' --相当于把这条语句的结果存在了abel_salary这个别名中(即把这条语句的结果存在了abel_salary这个临时表中)
)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT salary --这里使用abel_salary这张临时表中的salary这一列
FROM abel_salary --这里使用别名(即这里使用abel_salary这张临时表)
)

--
WITH dep_info 
AS (
SELECT dep.department_id AS dep_id
FROM departments dep
WHERE department_id IN (80, 90)
)
SELECT employee_id, last_name, salary, department_id 
FROM employees
WHERE department_id IN (
SELECT dep_id
FROM dep_info
)


--查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH dep_sum_salary
AS (
SELECT dep.department_name, SUM(emp.salary) AS dep_sum_sal
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
GROUP BY dep.department_name
--ORDER BY SUM(emp.salary) DESC
),
dep_totalAvg_salary AS (
SELECT SUM(dep_sum_sal) / COUNT(*) AS dep_totalAvg_sal --公司中各部门的平均总工资
FROM dep_sum_salary
)
SELECT * 
FROM dep_sum_salary
WHERE dep_sum_sal > (
SELECT dep_totalAvg_sal
FROM dep_totalAvg_salary
)
ORDER BY dep_sum_sal DESC --排序

--
--这种写法不正确，会报错
/*
SELECT salary, salary / COUNT(*)  
FROM employees
*/

--这种写法正确
SELECT salary, salary / 100  
FROM employees

--这种写法不正确，会报错
/*
SELECT salary / COUNT(*)  
FROM employees
*/

--这种写法正确
SELECT SUM(salary) / COUNT(*), MAX(salary) / COUNT(*), MIN(salary) / COUNT(*), AVG(salary) / COUNT(*), COUNT(*) 
FROM employees

--这种写法正确
SELECT salary / (SELECT COUNT(*) FROM employees) AS sal
FROM employees

--这种写法正确
SELECT salary / 2, salary
FROM employees

--这种写法正确
SELECT 6000 / COUNT(*)  
FROM employees

--这种写法正确
SELECT SUM(salary) / COUNT(*), SUM(salary), COUNT(*)   
FROM employees

--这种写法正确，注意：小括号中的(SELECT COUNT(*) FROM employees)这句话相当于一个子查询，可以理解和看成是一个子查询
SELECT salary, salary / (SELECT COUNT(*) FROM employees) 
FROM employees

--这种写法正确
SELECT salary, (SELECT COUNT(*) FROM employees), salary / (SELECT COUNT(*) FROM employees) 
FROM employees

--这种写法正确
SELECT COUNT(*), 80000 / COUNT(*), 2 + COUNT(*), 200 - COUNT(*), 2 * COUNT(*)   
FROM employees

--这种写法正确
SELECT COUNT(*)   
FROM employees

--这种写法正确
SELECT 50000 / COUNT(*), COUNT(*)
FROM employees

--这种写法错误，需要在SELECT COUNT(*) FROM employees这句话的两边加上括号才正确
/*
SELECT salary, SELECT COUNT(*) FROM employees
FROM employees
*/

--这种写法正确
SELECT salary, (SELECT COUNT(*) FROM employees) --可以把()括号中的语句当做或者理解成一个子查询
FROM employees


/*

SELECT COUNT(*)
FROM employees emp, JOB_HISTORY his
WHERE emp.employee_id = his.employee_id 
        
*/

SELECT emp.last_name, emp.*, his.*
FROM employees emp, JOB_HISTORY his
WHERE emp.employee_id = his.employee_id

SELECT salary, emp.* 
FROM employees emp
WHERE emp.salary > (
SELECT employees.salary
FROM employees
WHERE LOWER(last_name) LIKE '%abel%'
)

--如下这样写会报错(SELECT后面列名,*这种写法会报错,SELECT后面如果是跟列名,表名.* 这样写不会报错)
--SELECT last_name, * FROM employees
--如下这样写不会报错
SELECT last_name, employees.* FROM employees
--如下这样写不会报错
SELECT * FROM employees
--如下这样写不会报错
SELECT * FROM employees emp
--如下这样写不会报错
SELECT emp.* FROM employees emp
--如下这样写不会报错
SELECT employees.* FROM employees

--DISTINCT关键字 表示去掉重复(回顾下之前的这个DISTINCT知识点)
--
SELECT DISTINCT (dep.department_id)
FROM departments dep
--
SELECT DISTINCT dep.department_id
FROM departments dep
--
SELECT DISTINCT (dep.department_id), dep.* 
FROM departments dep

--
SELECT DISTINCT dep.department_id, dep.* 
FROM departments dep

--
SELECT DISTINCT dep.department_id, dep.manager_id, dep.* 
FROM departments dep

--
SELECT DISTINCT dep.department_id, dep.manager_id
FROM departments dep

--
SELECT DISTINCT (dep.department_id), (dep.manager_id)
FROM departments dep

--如下这种写法是错误的
/*
SELECT DISTINCT (dep.department_id, dep.manager_id)
FROM departments dep
*/

--
SELECT DISTINCT (dep.department_id), (dep.manager_id)
FROM departments dep

--以下这种写法是错误的
/*
SELECT DISTINCT (dep.department_id), DISTINCT (dep.manager_id)
FROM departments dep
*/

--以下这种写法是错误的
/*
SELECT DISTINCT dep.department_id, DISTINCT dep.manager_id
FROM departments dep
*/

--以下这种写法是正确的
SELECT DISTINCT dep.department_id, dep.manager_id
FROM departments dep

--复习一下以前的知识点
/*
如下：||符号可以理解成是一个连接符，相当于把这3个字符串连接起来，结果为江西省赣州市于都县

类似于java字符串中的+加号，String message = "江西省" + "赣州市" + "于都县"; 所以message的结果为江西省赣州市于都县
*/
SELECT '江西省' || '赣州市' || '于都县' FROM dual

/*
类似于 String result = "10" + "5" + "2"; 所以result的结果为1052
java中，字符串类型数据 + 任何类型的数据 最后结果都是字符串类型
*/
SELECT '10' || '5' || '2' FROM dual -- 结果是1052

--
SELECT 10 + 5 + 2 FROM dual -- 结果是17

/*
(注意：数字类型和字符类型是可以相互转换的，日期类型和字符类型也是可以相互转换的)
系统会把这3个字符串自动/隐式转换成数字类型
*/
SELECT '10' + '5' + '2' FROM dual -- 结果是17


/*

高级子查询-练习题

*/

/*
查询员工的last_name, department_id, salary.其中员
工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可
*/

--多列子查询
SELECT last_name, department_id, salary
FROM employees
WHERE (salary, department_id) 
IN (
SELECT salary, department_id
FROM employees
WHERE commission_pct IS NOT NULL
)

/*
选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
*/
SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL (
SELECT salary 
FROM employees
WHERE job_id = 'SA_MAN'
)
--用以下这种写法也可以
SELECT last_name, job_id, salary
FROM employees
WHERE salary > (
SELECT MAX(salary)
FROM employees
WHERE job_id = 'SA_MAN'
)

/*
选择所有没有管理者的员工的last_name
*/

--如下是用老知识解决
SELECT * 
FROM employees
WHERE manager_id IS NULL

--如下是用新知识解决
SELECT * 
FROM employees emp1
WHERE NOT EXISTS (
SELECT 'hello' 
FROM employees emp2
WHERE emp1.manager_id = emp2.employee_id
)
--以下的逻辑是错误的，达不到题目的目的
SELECT * 
FROM employees emp1
WHERE NOT EXISTS (
SELECT 'hello' 
FROM employees emp2
WHERE emp1.employee_id = emp2.manager_id
)
--以下的逻辑是错误的，达不到题目的目的
SELECT * 
FROM employees emp1
WHERE EXISTS (
SELECT 'hello' 
FROM employees emp2
WHERE emp1.employee_id != emp2.manager_id
)
--以下的逻辑是错误的，达不到题目的目的
SELECT * 
FROM employees emp1
WHERE EXISTS (
SELECT 'hello' 
FROM employees emp2
WHERE emp1.manager_id != emp2.employee_id
)


--回顾一下DISTINCT知识点
--
SELECT manager_id, employees.*
FROM employees

--
SELECT DISTINCT manager_id, employees.*
FROM employees

--
SELECT DISTINCT manager_id
FROM employees

--
SELECT DISTINCT manager_id, job_id
FROM employees

--
SELECT DISTINCT manager_id, job_id, employees.*
FROM employees