精华内容
下载资源
问答
  • Oracle子查询

    2018-12-09 19:50:16
    Oracle子查询 定义:有时候一条 sql 语句需要由另外一条 sql 语句产生的结果作为条件(或表) 1,where 子查询 --单行子查询 --- 查询工资 24000 的员工所在部门的信息 select * from departments where ...

    Oracle子查询

    定义:有时候一条 sql 语句需要由另外一条 sql 语句产生的结果作为条件(或表)

    1,where 子查询

    --单行子查询
    --- 查询工资 24000 的员工所在部门的信息
    select * from departments 
    where department_id =
    (select department_id from employees where salary=24000);
    
    --多行子查询
    --- 查询名字为 Steven 的员工所在部门的信息
    select * from departments 
    where department_id in
    (select department_id from employees where first_name='Steven');

    2,from 子查询

    -- sql 查询数据来源于 表 --> sql 的执行结果也是表 
    -- from 表名   /   from sql查询结果
    -- from 子查询 sql 语句的执行结果充当了一张表的存在
    
    select employee_id,first_name||last_name as name from employees;
    
    select employee_id,name 
    from (select employee_id,first_name||last_name as name from employees)
    where name like 'S%';

    3, 伪列

    1.oracle确确实实存在的列,不需要手动去创建,自动帮你创建的伪列,如果不主动发起查询则不会显示结果

    rowid

    --- 伪列 rowid 数据的唯一物理地址
    select rowid from departments;
    
    -- AAAC8/AAEAAAAA3AAA
    select * from departments where rowid ='AAAC8/AAEAAAAA3AAA';
    
    -- 表别名
    -- 展示伪列 以及所有的原始列内容   不能使用 as 直接空格别名即可
    select de.*,rowid from departments de;

    rownum

    --对于符合的数据进行一个编号,默认是1,执行顺序在 where 之前
    
    select * from (select d.*,rownum from departments d)
    where 条件判断;
    
    
    --- 内外层SQL嵌套  rownum 每一条SQL都存在  内层rownum n  外层rownum
    --- 使用分页判断 使用内层的rownum n
    
    -- from 嵌套
    select d.*,rownum from (select de.*,rownum n from departments de) d
    where n>10 and n<=20;
    
    
    -- 查询工资排名 3-5 位的员工的编号 姓名 以及工资 
    -- 排序 再 编号
    select * from
        (select t1.*,rownum n from 
              (select employee_id,first_name,salary  from employees order by salary desc) t1) 
    where n>=3 and n<=5;
    
    --- MySQL
    	select * fromwhere 条件 limit10,20

    4, 多表操作

    1.内连接 语法: inner join …表… on … 连接条件

    --将两张表中 可以连接的数据进行连接展示
    --106 行的数据  因为员工表中有一个员工没有部门编号
    
    select e.*,d.department_name from employees e
    inner join departments d
    on e.department_id = d.department_id
    
    注意: inner也可以省略 但是一般没有人省略inner
    
    
    --查询 员工 ID 为 100 的 员工信息以及其部门信息
    
    select e.*,d.department_name from employees e
    join departments d
    on e.department_id = d.department_id
    where employee_id = 100;

    2.外连接 (outer 关键字可以省略)

    2.1 左外连接 left outer join…on…

    2.2 右外连接 right outer join…on…

    2.3 全外连接 full outer join…on…

    --左外连接      107行  员工表中所有的数据展示
    select e.*,d.department_name from employees e
    left outer join departments d
    on e.department_id = d.department_id
    
    --右外连接      122行  部门表中所有的数据展示
    select e.*,d.* from employees e
    right outer join departments d
    on e.department_id = d.department_id
    
    --全外连接      123行 将员工表以及部门表中所有的数据进行展示 (了解)
    select e.*,d.* from employees e
    full outer join departments d
    on e.department_id = d.department_id
    
    
    注意:左右的概念只是相对于连接关键字而决定,当表的位置相反的时候会得到完全不一样的结果

    3.连接条件

    两张表中拥有共同的 department_id ---- 两张表之间的关联关系 : 外键

    主键 : 唯一标识了一行数据(唯一、非空)

    外键:表示两张或者多张表之间的关联关系,可以建立起数据之间的多方关系(连接)。---- 存在众多规范

    4. 多张表连接(>=3)

    现在存在三张表   表 A B C D
    
    select a.*,b.*,c.*,d.* from  A a
    inner join B b
    on a.条件 = b.条件
    inner join C c
    on b.条件 = c.条件
    inner join D d
    on ....

    5. 自连接

    --查询 员工自己的 ID 、First_name 以及 管理者 的ID 和 first_name 
    select e1.employee_id,e1.first_name,e1.manager_id,e2.first_name as manager_name
    from employees e1
    left join employees e2
    on e1.manager_id = e2.employee_id;

    练习作业

    分页查询练习
    --1.查询工资最高的3名员工信息
    select a.*,rownum from (select * from employees order by salary desc) a where rownum<=3;
    
    --2.查询工资排名第5到第10的员工信息
    select * from (select a.*,rownum as n from (select * from employees order by salary) a) b
    where b.n between 5 and 10;
    
    --3.查询first_name是以大写D开头的第3到第5个员工信息
    select * from (select employees.*,rownum as n from employees where first_name like 'D%') where n>=3 and n<=5;
    
    子查询练习
    --1.显示工资比’Allan’(first_name)高的所有员工的姓名和工资
    select * from employees where salary>(select salary from employees where first_name='Allan');
    
    --2.显示与’Allan’(first_name)从事相同工作的员工的详细信息
    select * from employees where job_id in (select job_id from employees where first_name='Allan');
    
    --3.显示与30号部门first_name为’Guy’员工工资相同的员工姓名和工资
    select * from employees where salary=(select salary from employees where first_name='Guy' and department_id=30);
    
    --4.查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SA_REP')(job_id)
    select * from employees where salary>=(select avg(salary) from employees) and job_id='SA_REP';
    
    --5.查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位 
    select count(*),job_id from employees where salary>=(select avg(salary) from employees) group by job_id;
    
    --表连接查询练习
    --1.显示所有职员的姓名及其所在部门的名称和工资
    select a.first_name,b.department_name,a.salary from employees a inner join departments b on a.department_id=b.department_id;
    
    --2.查询在研发部('IT')工作员工的编号,姓名,工作部门,工作所在地
    select a.employee_id,a.first_name,b.department_name,b.location_id 
    from employees a 
    inner join (select * from departments where department_name='IT') b
    on a.department_id=b.department_id;
    
    --3.查询各个部门的名称和员工人数
    select a.department_id,a.department_name,b.c 
    from departments a 
    inner join
    (select count(*) as c,department_id from employees group by department_id) b
    on a.department_id=b.department_id;
    
    --4.查询工资相同的员工的工资和姓名
    select * from (select count(*) as c,salary from employees group by salary having count(*)>1) b 
    inner join employees a 
    on b.salary=a.salary;
    
    --5.显示10号部门的经理和20号部门的所有职员的详细信息
    select * from employees where employee_id = (select manager_id from departments where department_id=10) or department_id=20;
    
    --6.查询员工的基本信息,附加其上级的姓名
    select a.employee_id,a.first_name,b.first_name from employees a inner join employees b
    on a.manager_id = b.employee_id;
    
    --7.求入职日期相同(年月日相同)的员工(考察知识点:自连接)
    select * from employees a inner join 
    (select hire_date,count(*) from employees group by hire_date having count(*)>1) b
    on a.hire_date=b.hire_date;
    
    --8.显示各个部门经理的基本工资
    select * from employees where employee_id in (select manager_id from departments);
    
    --9.** 查询平均工资最高的部门信息(考察知识点:子查询,组函数,连接查询)
    select * from departments c inner join 
       (select s,rownum,department_id from 
           (select avg(salary) as s,department_id from employees group by department_id order by avg(salary) desc)
       where rownum=1) b
    on b.department_id = c.department_id;
    
    select * from departments b where department_id=
         (select department_id from employees group by department_id having avg(salary)=
               (select max(avg(salary)) from employees group by department_id));
                         
    
    展开全文
  • oracle子查询

    2018-09-21 08:51:02
    -- SELECT emp.last_name, emp.salary, emp.email, ...子查询 顺便说一下,软件开发要具备一个想法或思想:大处着眼,小处着手 */ -- /* 单行子查询 1.只返回一行 2.使用单行比较操作符 = &gt; &gt;= ...
    --
    SELECT emp.last_name, emp.salary,  emp.email, first_name
    FROM employees emp
    WHERE emp.salary > 3000
    
    /*
    
    子查询
    
    顺便说一下,软件开发要具备一个想法或思想:大处着眼,小处着手
    
    */
    
    --
    
    /*
    
    单行子查询
    1.只返回一行
    2.使用单行比较操作符
    =
    >
    >=
    <
    <=
    <>或者用!=
    
    */
    
    --谁的工资比abel高
    SELECT EMP.SALARY, EMP.*
      FROM EMPLOYEES EMP
     WHERE EMP.SALARY > (SELECT EMP.SALARY
                           FROM EMPLOYEES EMP
                          WHERE LOWER(EMP.LAST_NAME) = 'abel')
    
    --查询员工Chen的manager信息
      SELECT EMP.EMPLOYEE_ID, EMP.LAST_NAME, EMP.SALARY
              FROM EMPLOYEES EMP
             WHERE EMP.EMPLOYEE_ID =
                   (SELECT EMP.MANAGER_ID
                      FROM EMPLOYEES EMP
                     WHERE LOWER(EMP.LAST_NAME) = 'chen')
            
            --返回job_id与141号员工相同,salary比143员工多的员工姓名,job_id和工资
            SELECT EMP.LAST_NAME, EMP.JOB_ID, EMP.SALARY
              FROM EMPLOYEES EMP
             WHERE EMP.JOB_ID = (SELECT EMP.JOB_ID
                                   FROM EMPLOYEES EMP
                                  WHERE EMP.EMPLOYEE_ID = 141)
               AND EMP.SALARY > (SELECT EMP.SALARY
                                   FROM EMPLOYEES EMP
                                  WHERE EMP.EMPLOYEE_ID = 143)
                    
                    --返回公司工资最少的员工的last_name, JOB_ID和SALARY
                    SELECT EMP.LAST_NAME, EMP.JOB_ID, EMP.SALARY
                      FROM EMPLOYEES EMP
                     WHERE EMP.SALARY =
                           (SELECT MIN(EMP.SALARY) FROM EMPLOYEES EMP)
                            --查询最低工资大于50号部门最低工资的部门id和其最低工资
                            SELECT EMP.DEPARTMENT_ID, MIN(EMP.SALARY)
                              FROM EMPLOYEES EMP
                            HAVING MIN(EMP.SALARY) > (SELECT MIN(EMP.SALARY)
                                                        FROM EMPLOYEES EMP
                                                       WHERE EMP.DEPARTMENT_ID = 50)
                             GROUP BY EMP.DEPARTMENT_ID
    --
    SELECT EMP.DEPARTMENT_ID, MIN(EMP.SALARY)
                              FROM EMPLOYEES EMP
                              GROUP BY emp.department_id
                            HAVING MIN(EMP.SALARY) > (SELECT MIN(EMP.SALARY)
                                                        FROM EMPLOYEES EMP
                                                       WHERE EMP.DEPARTMENT_ID = 50)
    --
    SELECT EMP.DEPARTMENT_ID, MIN(EMP.SALARY)
                              FROM EMPLOYEES EMP
                              GROUP BY emp.department_id
                            HAVING MIN(EMP.SALARY) > (SELECT MIN(EMP.SALARY)
                                                        FROM EMPLOYEES EMP
                                                       WHERE EMP.DEPARTMENT_ID = 50)
                                                       ORDER BY EMP.DEPARTMENT_ID ASC
    /*
    
    多行子查询
    1.返回多行
    2.使用多行比较操作符
    in 等于列表中的任意一个
    any 和子查询返回的某一个值比较
    all 和子查询返回的所有值比较
    
    */                                                   
    --
    /*
    
    返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary
    下面演示any和all
    
    */
    --使用ANY的方式
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG' 
    AND emp.salary < 
    ANY(
    SELECT emp.salary 
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    ) 
    ORDER BY emp.employee_id ASC
    --也可以改成下面这种方式,效果是一样的
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG' 
    AND emp.salary < (
    SELECT MAX(emp.salary)
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    )
    ORDER BY emp.employee_id ASC
    
    /*
    
    返回其他部门中比job_id为'IT_PROG'部门任意(所有)工资低的员工的员工号、姓名、job_id以及salary
    
    */
    
    --使用ALL的方式
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG' 
    AND emp.salary < 
    ALL(
    SELECT emp.salary 
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    ) 
    ORDER BY emp.employee_id ASC
    --也可以改成下面这种方式,效果是一样的
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG' 
    AND emp.salary < (
    SELECT MIN(emp.salary)
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    )
    ORDER BY emp.employee_id ASC
    
    /*
    
    子查询中的空值问题
    
    */
    --子查询中查到了结果
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id = (
    SELECT emp.manager_id
    FROM employees emp
    WHERE emp.employee_id = 117
    )
    /*
    
    子查询中没有查到符合条件的数据,所以返回空值给外层查询,所以外层查询自然就查不到任何的数据,
    子查询中的空值问题不会报错,只是没有查询结果而已
    
    */
    --子查询中没有查到结果
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id = (
    SELECT emp.manager_id
    FROM employees emp
    WHERE emp.employee_id = 666
    )
    --
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id NOT IN (
    SELECT emp.manager_id
    FROM employees emp
    )
    --
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id IN (
    SELECT emp.manager_id
    FROM employees emp
    )
    
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id IS NULL
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id NOT IN (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id != (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id != (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id != NULL
    --
    SELECT NULL, NULL AS "空空如也", 'abc123', 'hello', '江西赣州于都县' AS "家乡", '深圳' "工作地", emp.last_name, emp.salary
    FROM employees emp
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = ANY (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = ALL (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    
    /*
    子查询练习题
    */
    --查询工资最低的员工的信息,last_name, salary
    SELECT emp.last_name, emp.salary
    FROM employees emp
    WHERE emp.salary = (
    SELECT MIN(emp.salary)
    FROM employees emp
    )
    --查询平均工资最低的部门信息
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MIN(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    
    --查询平均工资最低的部门信息
    SELECT dep.department_id 部门编号, dep.department_name AS "部门名称", dep.*
    FROM departments dep
    WHERE dep.department_id = (
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MIN(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    --查询平均工资最低的部门信息和该部门的平均工资(此题目有点难度)
    SELECT dep.department_id 部门编号, dep.department_name AS "部门名称", dep.*, 
    --(SELECT AVG(emp.salary) AS "平均薪水"
    (SELECT AVG(emp.salary)
    FROM employees emp
    WHERE emp.department_id = dep.department_id) AS "平均工资"
    FROM departments dep
    WHERE dep.department_id = (
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MIN(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    
    --查询平均工资最高的job的信息(万一有多个job_id的平均工资等于最高平均工资,那就不能用=等号了,应该改成in)
    SELECT job.*
    FROM jobs job
    WHERE job.job_id = (
    SELECT  
    emp.job_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT  
    MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.job_id
    )
    GROUP BY emp.job_id
    )
    
    --把=等号改成in
    SELECT job.*
    FROM jobs job
    --这里改成in
    WHERE job.job_id in (
    SELECT  
    emp.job_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT  
    MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.job_id
    )
    GROUP BY emp.job_id
    )
    
    --查询平均工资高于公司平均工资的部门有哪些?
    SELECT dep.*, (SELECT AVG(emp.salary) FROM employees emp WHERE emp.department_id = dep.department_id )
    FROM departments dep
    WHERE dep.department_id IN (
    SELECT emp.department_id
    --, AVG(emp.salary) 
    FROM employees emp
    GROUP BY emp.department_id
    HAVING AVG(emp.salary) > (
    SELECT AVG(emp.salary)
    FROM employees emp
    )
    )
    --
    SELECT emp.department_id, AVG(emp.salary) AS avg_salary
    FROM employees emp
    GROUP BY emp.department_id
    HAVING AVG(emp.salary) > (
    SELECT AVG(emp.salary)
    FROM employees emp
    )
    ORDER BY avg_salary DESC
    --
    
    --查询公司中所有manager的详细信息
    SELECT emp.*
    FROM employees emp 
    WHERE emp.employee_id IN (
    SELECT emp.manager_id 
    FROM employees emp
    WHERE emp.manager_id IS NOT NULL
    GROUP BY emp.manager_id
    )
    --另一种写法
    SELECT emp.*
    FROM employees emp 
    WHERE emp.employee_id IN (
    SELECT emp.manager_id 
    FROM employees emp
    )
    
    
    
    --各个部门中 最高工资中最低的那个部门的 最低工资是多少?
    SELECT emp.department_id, MAX(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    ORDER BY MAX(emp.salary) ASC
    --
    SELECT MIN(MAX(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, MAX(emp.salary), MIN(emp.salary)
    FROM employees emp
    HAVING MAX(emp.salary) = (
    SELECT MIN(MAX(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    --
    SELECT MIN(emp.salary)
    FROM employees emp
    WHERE emp.department_id IN (
    --WHERE emp.department_id = (
    SELECT emp.department_id
    FROM employees emp
    HAVING MAX(emp.salary) = (
    SELECT MIN(MAX(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    --
    SELECT * FROM employees emp WHERE emp.department_id = 10
    --
    SELECT MIN(emp.salary)
    FROM employees emp
    WHERE emp.department_id IN (40, 60, 80)
    --
    SELECT emp.department_id, MAX(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    HAVING MAX(emp.salary) = 24000 OR MAX(emp.salary) = 14000
    
    
    
    
    --查询平均工资最高的部门的manager的详细信息:last_name, department_id, email,salary
    SELECT emp.department_id, AVG(emp.salary) AS avg_salary
    FROM employees emp
    GROUP BY emp.department_id
    ORDER BY avg_salary DESC
    --
    SELECT MAX(AVG(emp.salary)) AS MAX_avg_salary
    FROM employees emp
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    --
    SELECT emp.*
    FROM employees emp
    WHERE emp.department_id IN(
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    --
    SELECT last_name AS 名字, department_id AS "部门编号", email 邮箱, salary 薪水, emp.*
    FROM employees emp
    WHERE emp.employee_id IN (
    SELECT emp.manager_id
    FROM employees emp
    WHERE emp.department_id IN(
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    )
    
    
    --查询1999年来公司的员工中的最高工资的那个员工的信息
    SELECT emp.salary, emp.*
    FROM employees emp
    WHERE to_char(emp.hire_date, 'yyyy') = '1999'
    ORDER BY emp.salary DESC
    --
    SELECT MAX(emp.salary)
    FROM employees emp
    WHERE to_char(emp.hire_date, 'yyyy') = '1999'
    --
    SELECT emp.salary, emp.*
    FROM employees emp
    WHERE emp.salary IN (
    --WHERE emp.salary = (
    SELECT MAX(emp.salary)
    FROM employees emp
    WHERE to_char(emp.hire_date, 'yyyy') = '1999'
    )
    AND to_char(emp.hire_date, 'yyyy') = '1999'
    
    --1.查询和Zlotkey相同部门的员工姓名和雇用日期
    SELECT emp.last_name AS 员工姓名, emp.hire_date "雇用日期", emp.*
    FROM employees emp
    --可以把=等号改成in
    WHERE emp.department_id = (
    SELECT emp.department_id
    FROM employees emp 
    WHERE lower(emp.last_name) = 'zlotkey'
    )
    --也可以把!=符号改成<>
    AND lower(emp.last_name) != 'zlotkey'
    
    --2.查询工资比公司平均工资高的员工的员工号,姓名和工资
    SELECT emp.employee_id, emp.last_name, emp.salary
    FROM employees emp
    WHERE emp.salary > (
    SELECT AVG(emp.salary)
    FROM employees emp
    )
    
    --3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(这道题目有点难度)
    --各个部门的平均工资
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    WHERE emp.department_id = emp.department_id
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    WHERE 20 = 20
    GROUP BY emp.department_id
    --正确答案
    SELECT emp1.employee_id, emp1.last_name, emp1.salary
    FROM employees emp1
    WHERE emp1.salary > (
    SELECT AVG(emp2.salary)
    FROM employees emp2
    WHERE emp1.department_id = emp2.department_id
    GROUP BY emp2.department_id
    )
    --
    
    
    --4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
    SELECT emp.employee_id, emp.last_name, emp.*
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT emp.department_id
    FROM employees emp
    --WHERE emp.last_name LIKE '%' || lower('U') || '%'
    WHERE emp.last_name LIKE '%u%'
    )
    AND emp.last_name NOT LIKE '%u%'
    
    
    --
    SELECT *
    FROM employees emp
    WHERE last_name = 'Pataballa'
    --
    SELECT *
    FROM employees emp
    WHERE last_name = 'Pata' || 'balla'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%Pata' || 'bal%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%Patabal%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%' || 'Pata' || 'bal' || '%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%' || upper('p') || 'ata' || 'bal' || '%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%' || lower('P') || 'ata' || 'bal' || '%'
    
    --5.查询在部门的location_id为1700的部门工作的员工的员工号
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT dep.department_id
    FROM departments dep
    WHERE dep.location_id = 1700
    )
    
    --6.查询管理者是King的员工姓名和工资
    SELECT emp.last_name AS "员工姓名", emp.salary 薪水, emp.*
    FROM employees emp
    WHERE emp.manager_id IN(
    SELECT emp.employee_id
    FROM employees emp
    WHERE LOWER(emp.last_name) = 'king'
    )
    
    
    --
    SELECT emp.job_id, AVG(emp.salary) AS sal
    FROM employees emp 
    GROUP BY emp.job_id
    ORDER BY sal DESC
    --
    SELECT emp.department_id, AVG(emp.salary) AS sal
    FROM employees emp 
    GROUP BY emp.department_id
    ORDER BY sal DESC
    
    --
    SELECT emp.job_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) >= 17000 --HAVING关键字也可以放在FROM后面,也可以放在GROUP BY后面
    GROUP BY emp.job_id
    --HAVING AVG(emp.salary) >= 17000
    
    --
    --SELECT MAX(emp.salary)
    SELECT AVG(emp.salary), MAX(emp.salary)
    --SELECT AVG(emp.salary)
    --SELECT emp.salary
    FROM employees emp
    --HAVING AVG(emp.salary) >= 6400 AND MAX(emp.salary) > 23000
    --HAVING AVG(emp.salary) >=  6400
    --HAVING AVG(emp.salary) >=  6400
    HAVING MAX(emp.salary) > 23000
    
    --
    SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    HAVING AVG(emp.salary) > 9000
    
    --
    SELECT emp.salary
    FROM employees emp
    WHERE emp.salary > 6400
    
    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    --HAVING emp.salary > 6400
    HAVING AVG(emp.salary) > 6400 AND department_id > 80
    GROUP BY emp.department_id
    
    --以下这种写法是错误的
    SELECT emp.last_name, emp.salary, emp.employee_id
    FROM employees emp
    HAVING AVG(emp.salary) > 6400
    
    --
    SELECT MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300
    --
    SELECT AVG(emp.salary)
    FROM employees emp
    HAVING MAX(emp.salary) > 23500
    --
    SELECT AVG(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300
    --
    SELECT AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300
    --
    SELECT AVG(emp.salary), MAX(emp.salary), SUM(emp.salary), MIN(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300
    
    --
    --SELECT emp.department_id
    SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6400
    GROUP BY emp.department_id
    --
    SELECT emp.department_id
    --SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6400
    GROUP BY emp.department_id
    
    --
    SELECT emp.department_id, emp.job_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    --HAVING AVG(emp.salary) > 6400 
    /*
    还有一个要注意:HAVING关键字的作用也是过滤数据,HAVING后面可以跟组函数,如
    果HAVING要过滤某个字段的话,只能过滤SELECT后面出现了的字段,比
    如这里的SELECT emp.department_id, emp.job_id, AVG(emp.salary), MAX(emp.salary)这句话,SELECT后面出
    现了department_id和job_id这2个字段,所以HAVING后面只能跟组函数以及department_id和job_id这2个字段
    */
    --不等于可以使用<>符号,也可以使用!=符号,如下:
    HAVING emp.department_id IN (40, 60, 80, 100) AND (emp.job_id != 'IT' OR emp.job_id <> 'IT')
    GROUP BY emp.department_id, emp.job_id

    --
    SELECT emp.last_name, emp.salary,  emp.email, first_name
    FROM employees emp
    WHERE emp.salary > 3000

    /*

    子查询

    顺便说一下,软件开发要具备一个想法或思想:大处着眼,小处着手

    */

    --

    /*

    单行子查询
    1.只返回一行
    2.使用单行比较操作符
    =
    >
    >=
    <
    <=
    <>或者用!=

    */

    --谁的工资比abel高
    SELECT EMP.SALARY, EMP.*
      FROM EMPLOYEES EMP
     WHERE EMP.SALARY > (SELECT EMP.SALARY
                           FROM EMPLOYEES EMP
                          WHERE LOWER(EMP.LAST_NAME) = 'abel')

    --查询员工Chen的manager信息
      SELECT EMP.EMPLOYEE_ID, EMP.LAST_NAME, EMP.SALARY
              FROM EMPLOYEES EMP
             WHERE EMP.EMPLOYEE_ID =
                   (SELECT EMP.MANAGER_ID
                      FROM EMPLOYEES EMP
                     WHERE LOWER(EMP.LAST_NAME) = 'chen')
            
            --返回job_id与141号员工相同,salary比143员工多的员工姓名,job_id和工资
            SELECT EMP.LAST_NAME, EMP.JOB_ID, EMP.SALARY
              FROM EMPLOYEES EMP
             WHERE EMP.JOB_ID = (SELECT EMP.JOB_ID
                                   FROM EMPLOYEES EMP
                                  WHERE EMP.EMPLOYEE_ID = 141)
               AND EMP.SALARY > (SELECT EMP.SALARY
                                   FROM EMPLOYEES EMP
                                  WHERE EMP.EMPLOYEE_ID = 143)
                    
                    --返回公司工资最少的员工的last_name, JOB_ID和SALARY
                    SELECT EMP.LAST_NAME, EMP.JOB_ID, EMP.SALARY
                      FROM EMPLOYEES EMP
                     WHERE EMP.SALARY =
                           (SELECT MIN(EMP.SALARY) FROM EMPLOYEES EMP)
                            --查询最低工资大于50号部门最低工资的部门id和其最低工资
                            SELECT EMP.DEPARTMENT_ID, MIN(EMP.SALARY)
                              FROM EMPLOYEES EMP
                            HAVING MIN(EMP.SALARY) > (SELECT MIN(EMP.SALARY)
                                                        FROM EMPLOYEES EMP
                                                       WHERE EMP.DEPARTMENT_ID = 50)
                             GROUP BY EMP.DEPARTMENT_ID
    --
    SELECT EMP.DEPARTMENT_ID, MIN(EMP.SALARY)
                              FROM EMPLOYEES EMP
                              GROUP BY emp.department_id
                            HAVING MIN(EMP.SALARY) > (SELECT MIN(EMP.SALARY)
                                                        FROM EMPLOYEES EMP
                                                       WHERE EMP.DEPARTMENT_ID = 50)
    --
    SELECT EMP.DEPARTMENT_ID, MIN(EMP.SALARY)
                              FROM EMPLOYEES EMP
                              GROUP BY emp.department_id
                            HAVING MIN(EMP.SALARY) > (SELECT MIN(EMP.SALARY)
                                                        FROM EMPLOYEES EMP
                                                       WHERE EMP.DEPARTMENT_ID = 50)
                                                       ORDER BY EMP.DEPARTMENT_ID ASC
    /*

    多行子查询
    1.返回多行
    2.使用多行比较操作符
    in 等于列表中的任意一个
    any 和子查询返回的某一个值比较
    all 和子查询返回的所有值比较

    */                                                   
    --
    /*

    返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary
    下面演示any和all

    */
    --使用ANY的方式
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG'
    AND emp.salary <
    ANY(
    SELECT emp.salary
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    )
    ORDER BY emp.employee_id ASC
    --也可以改成下面这种方式,效果是一样的
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG'
    AND emp.salary < (
    SELECT MAX(emp.salary)
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    )
    ORDER BY emp.employee_id ASC

    /*

    返回其他部门中比job_id为'IT_PROG'部门任意(所有)工资低的员工的员工号、姓名、job_id以及salary

    */

    --使用ALL的方式
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG'
    AND emp.salary <
    ALL(
    SELECT emp.salary
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    )
    ORDER BY emp.employee_id ASC
    --也可以改成下面这种方式,效果是一样的
    SELECT emp.employee_id, emp.last_name, emp.job_id, emp.salary
    FROM employees emp
    WHERE emp.job_id <> 'IT_PROG'
    AND emp.salary < (
    SELECT MIN(emp.salary)
    FROM employees emp
    WHERE emp.job_id = 'IT_PROG'
    )
    ORDER BY emp.employee_id ASC

    /*

    子查询中的空值问题

    */
    --子查询中查到了结果
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id = (
    SELECT emp.manager_id
    FROM employees emp
    WHERE emp.employee_id = 117
    )
    /*

    子查询中没有查到符合条件的数据,所以返回空值给外层查询,所以外层查询自然就查不到任何的数据,
    子查询中的空值问题不会报错,只是没有查询结果而已

    */
    --子查询中没有查到结果
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id = (
    SELECT emp.manager_id
    FROM employees emp
    WHERE emp.employee_id = 666
    )
    --
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id NOT IN (
    SELECT emp.manager_id
    FROM employees emp
    )
    --
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.employee_id IN (
    SELECT emp.manager_id
    FROM employees emp
    )

    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id IS NULL
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id NOT IN (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id != (
    SELECT NULL
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id != (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id != NULL
    --
    SELECT NULL, NULL AS "空空如也", 'abc123', 'hello', '江西赣州于都县' AS "家乡", '深圳' "工作地", emp.last_name, emp.salary
    FROM employees emp
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = ANY (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )
    --
    SELECT emp.employee_id, emp.last_name, emp.department_id
    FROM employees emp
    WHERE emp.department_id = ALL (
    SELECT emp.department_id
    FROM employees emp
    WHERE emp.employee_id = 199
    )

    /*
    子查询练习题
    */
    --查询工资最低的员工的信息,last_name, salary
    SELECT emp.last_name, emp.salary
    FROM employees emp
    WHERE emp.salary = (
    SELECT MIN(emp.salary)
    FROM employees emp
    )
    --查询平均工资最低的部门信息
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MIN(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id

    --查询平均工资最低的部门信息
    SELECT dep.department_id 部门编号, dep.department_name AS "部门名称", dep.*
    FROM departments dep
    WHERE dep.department_id = (
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MIN(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    --查询平均工资最低的部门信息和该部门的平均工资(此题目有点难度)
    SELECT dep.department_id 部门编号, dep.department_name AS "部门名称", dep.*,
    --(SELECT AVG(emp.salary) AS "平均薪水"
    (SELECT AVG(emp.salary)
    FROM employees emp
    WHERE emp.department_id = dep.department_id) AS "平均工资"
    FROM departments dep
    WHERE dep.department_id = (
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MIN(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )

    --查询平均工资最高的job的信息(万一有多个job_id的平均工资等于最高平均工资,那就不能用=等号了,应该改成in)
    SELECT job.*
    FROM jobs job
    WHERE job.job_id = (
    SELECT  
    emp.job_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT  
    MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.job_id
    )
    GROUP BY emp.job_id
    )

    --把=等号改成in
    SELECT job.*
    FROM jobs job
    --这里改成in
    WHERE job.job_id in (
    SELECT  
    emp.job_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT  
    MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.job_id
    )
    GROUP BY emp.job_id
    )

    --查询平均工资高于公司平均工资的部门有哪些?
    SELECT dep.*, (SELECT AVG(emp.salary) FROM employees emp WHERE emp.department_id = dep.department_id )
    FROM departments dep
    WHERE dep.department_id IN (
    SELECT emp.department_id
    --, AVG(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    HAVING AVG(emp.salary) > (
    SELECT AVG(emp.salary)
    FROM employees emp
    )
    )
    --
    SELECT emp.department_id, AVG(emp.salary) AS avg_salary
    FROM employees emp
    GROUP BY emp.department_id
    HAVING AVG(emp.salary) > (
    SELECT AVG(emp.salary)
    FROM employees emp
    )
    ORDER BY avg_salary DESC
    --

    --查询公司中所有manager的详细信息
    SELECT emp.*
    FROM employees emp
    WHERE emp.employee_id IN (
    SELECT emp.manager_id
    FROM employees emp
    WHERE emp.manager_id IS NOT NULL
    GROUP BY emp.manager_id
    )
    --另一种写法
    SELECT emp.*
    FROM employees emp
    WHERE emp.employee_id IN (
    SELECT emp.manager_id
    FROM employees emp
    )

     

    --各个部门中 最高工资中最低的那个部门的 最低工资是多少?
    SELECT emp.department_id, MAX(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    ORDER BY MAX(emp.salary) ASC
    --
    SELECT MIN(MAX(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, MAX(emp.salary), MIN(emp.salary)
    FROM employees emp
    HAVING MAX(emp.salary) = (
    SELECT MIN(MAX(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    --
    SELECT MIN(emp.salary)
    FROM employees emp
    WHERE emp.department_id IN (
    --WHERE emp.department_id = (
    SELECT emp.department_id
    FROM employees emp
    HAVING MAX(emp.salary) = (
    SELECT MIN(MAX(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    --
    SELECT * FROM employees emp WHERE emp.department_id = 10
    --
    SELECT MIN(emp.salary)
    FROM employees emp
    WHERE emp.department_id IN (40, 60, 80)
    --
    SELECT emp.department_id, MAX(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    HAVING MAX(emp.salary) = 24000 OR MAX(emp.salary) = 14000

     


    --查询平均工资最高的部门的manager的详细信息:last_name, department_id, email,salary
    SELECT emp.department_id, AVG(emp.salary) AS avg_salary
    FROM employees emp
    GROUP BY emp.department_id
    ORDER BY avg_salary DESC
    --
    SELECT MAX(AVG(emp.salary)) AS MAX_avg_salary
    FROM employees emp
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    --
    SELECT emp.*
    FROM employees emp
    WHERE emp.department_id IN(
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    --
    SELECT last_name AS 名字, department_id AS "部门编号", email 邮箱, salary 薪水, emp.*
    FROM employees emp
    WHERE emp.employee_id IN (
    SELECT emp.manager_id
    FROM employees emp
    WHERE emp.department_id IN(
    SELECT emp.department_id
    FROM employees emp
    HAVING AVG(emp.salary) = (
    SELECT MAX(AVG(emp.salary))
    FROM employees emp
    GROUP BY emp.department_id
    )
    GROUP BY emp.department_id
    )
    )


    --查询1999年来公司的员工中的最高工资的那个员工的信息
    SELECT emp.salary, emp.*
    FROM employees emp
    WHERE to_char(emp.hire_date, 'yyyy') = '1999'
    ORDER BY emp.salary DESC
    --
    SELECT MAX(emp.salary)
    FROM employees emp
    WHERE to_char(emp.hire_date, 'yyyy') = '1999'
    --
    SELECT emp.salary, emp.*
    FROM employees emp
    WHERE emp.salary IN (
    --WHERE emp.salary = (
    SELECT MAX(emp.salary)
    FROM employees emp
    WHERE to_char(emp.hire_date, 'yyyy') = '1999'
    )
    AND to_char(emp.hire_date, 'yyyy') = '1999'

    --1.查询和Zlotkey相同部门的员工姓名和雇用日期
    SELECT emp.last_name AS 员工姓名, emp.hire_date "雇用日期", emp.*
    FROM employees emp
    --可以把=等号改成in
    WHERE emp.department_id = (
    SELECT emp.department_id
    FROM employees emp
    WHERE lower(emp.last_name) = 'zlotkey'
    )
    --也可以把!=符号改成<>
    AND lower(emp.last_name) != 'zlotkey'

    --2.查询工资比公司平均工资高的员工的员工号,姓名和工资
    SELECT emp.employee_id, emp.last_name, emp.salary
    FROM employees emp
    WHERE emp.salary > (
    SELECT AVG(emp.salary)
    FROM employees emp
    )

    --3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(这道题目有点难度)
    --各个部门的平均工资
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    WHERE emp.department_id = emp.department_id
    GROUP BY emp.department_id
    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    WHERE 20 = 20
    GROUP BY emp.department_id
    --正确答案
    SELECT emp1.employee_id, emp1.last_name, emp1.salary
    FROM employees emp1
    WHERE emp1.salary > (
    SELECT AVG(emp2.salary)
    FROM employees emp2
    WHERE emp1.department_id = emp2.department_id
    GROUP BY emp2.department_id
    )
    --


    --4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
    SELECT emp.employee_id, emp.last_name, emp.*
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT emp.department_id
    FROM employees emp
    --WHERE emp.last_name LIKE '%' || lower('U') || '%'
    WHERE emp.last_name LIKE '%u%'
    )
    AND emp.last_name NOT LIKE '%u%'


    --
    SELECT *
    FROM employees emp
    WHERE last_name = 'Pataballa'
    --
    SELECT *
    FROM employees emp
    WHERE last_name = 'Pata' || 'balla'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%Pata' || 'bal%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%Patabal%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%' || 'Pata' || 'bal' || '%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%' || upper('p') || 'ata' || 'bal' || '%'
    --
    SELECT *
    FROM employees emp
    WHERE last_name LIKE '%' || lower('P') || 'ata' || 'bal' || '%'

    --5.查询在部门的location_id为1700的部门工作的员工的员工号
    SELECT emp.employee_id, emp.last_name
    FROM employees emp
    WHERE emp.department_id IN (
    SELECT dep.department_id
    FROM departments dep
    WHERE dep.location_id = 1700
    )

    --6.查询管理者是King的员工姓名和工资
    SELECT emp.last_name AS "员工姓名", emp.salary 薪水, emp.*
    FROM employees emp
    WHERE emp.manager_id IN(
    SELECT emp.employee_id
    FROM employees emp
    WHERE LOWER(emp.last_name) = 'king'
    )


    --
    SELECT emp.job_id, AVG(emp.salary) AS sal
    FROM employees emp
    GROUP BY emp.job_id
    ORDER BY sal DESC
    --
    SELECT emp.department_id, AVG(emp.salary) AS sal
    FROM employees emp
    GROUP BY emp.department_id
    ORDER BY sal DESC

    --
    SELECT emp.job_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) >= 17000 --HAVING关键字也可以放在FROM后面,也可以放在GROUP BY后面
    GROUP BY emp.job_id
    --HAVING AVG(emp.salary) >= 17000

    --
    --SELECT MAX(emp.salary)
    SELECT AVG(emp.salary), MAX(emp.salary)
    --SELECT AVG(emp.salary)
    --SELECT emp.salary
    FROM employees emp
    --HAVING AVG(emp.salary) >= 6400 AND MAX(emp.salary) > 23000
    --HAVING AVG(emp.salary) >=  6400
    --HAVING AVG(emp.salary) >=  6400
    HAVING MAX(emp.salary) > 23000

    --
    SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    GROUP BY emp.department_id
    HAVING AVG(emp.salary) > 9000

    --
    SELECT emp.salary
    FROM employees emp
    WHERE emp.salary > 6400

    --
    SELECT emp.department_id, AVG(emp.salary)
    FROM employees emp
    --HAVING emp.salary > 6400
    HAVING AVG(emp.salary) > 6400 AND department_id > 80
    GROUP BY emp.department_id

    --以下这种写法是错误的
    SELECT emp.last_name, emp.salary, emp.employee_id
    FROM employees emp
    HAVING AVG(emp.salary) > 6400

    --
    SELECT MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300
    --
    SELECT AVG(emp.salary)
    FROM employees emp
    HAVING MAX(emp.salary) > 23500
    --
    SELECT AVG(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300
    --
    SELECT AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300
    --
    SELECT AVG(emp.salary), MAX(emp.salary), SUM(emp.salary), MIN(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6300

    --
    --SELECT emp.department_id
    SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6400
    GROUP BY emp.department_id
    --
    SELECT emp.department_id
    --SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    HAVING AVG(emp.salary) > 6400
    GROUP BY emp.department_id

    --
    SELECT emp.department_id, emp.job_id, AVG(emp.salary), MAX(emp.salary)
    FROM employees emp
    --HAVING AVG(emp.salary) > 6400
    /*
    还有一个要注意:HAVING关键字的作用也是过滤数据,HAVING后面可以跟组函数,如
    果HAVING要过滤某个字段的话,只能过滤SELECT后面出现了的字段,比
    如这里的SELECT emp.department_id, emp.job_id, AVG(emp.salary), MAX(emp.salary)这句话,SELECT后面出
    现了department_id和job_id这2个字段,所以HAVING后面只能跟组函数以及department_id和job_id这2个字段
    */
    --不等于可以使用<>符号,也可以使用!=符号,如下:
    HAVING emp.department_id IN (40, 60, 80, 100) AND (emp.job_id != 'IT' OR emp.job_id <> 'IT')
    GROUP BY emp.department_id, emp.job_id

    展开全文
  • oracle 子查询

    千次阅读 2013-04-12 22:46:47
    子查询就是嵌套在查询语句内的查询,后续查询可以使用先前查询的结果,用以代替暂存表或变量存储先前查询结果子查询外层为较高的层级,以下称之为“主查询”。派生表是子查询的一个特殊情况,他是应用于FROM...

    子查询就是嵌套在查询语句内的查询,后续查询可以使用先前查询的结果,用以代替暂存表或变量存储先前查询结果。

    子查询的外层为较高的层级,以下称之为“主查询”。派生表是子查询的一个特殊情况,他是应用于FROM子句中的

    SELECT完整命令,以数据表别名引用此子查询时,同使用一般数据表一样。

     

    子查询以内外层是否进行连接(JOIN)可分成嵌套子查询相关子查询两种类型

     

    一、嵌套子查询

    1、单行子查询

    单行子查询将传回单一字段(Column)的一行(Row)数据值,可应用于任何需要纯量值的场合,从而简化查询中需要使用

    变量的那些需求。以下范例将查询学校为广州大学所在的单位的所有人员,其中子查询用来查询学校为广州大学的部门ID

    主查询则利用子查询所传回的部门编号查询人员数据表

    select name,account,unit_id
    from sys_user
    where unit_id = 
    (
    select id from sys_unit 
    where name = '广州大学'
    )


    2、多行子查询

    多行子查询将传回单一字段(Column)的多行(Rows)数据值,当子查询传回多笔数据值时,必须改用IN。将上述范例增加查询

    另一个学校名称为广东工业大学的部门编号

    select name,account,unit_id
    from sys_user
    where unit_id in 
    (
    select id from sys_unit 
    where name in('广州大学','广东工业大学')
    )


     

    二、相关子查询

    下面介绍将EXISTS应用于相关子查询的方法。EXISTS是用来检查某个数据是否符合子查询的条件,相反为NOT  EXISTS.。

    当在子查询中找到符合条件的数据时,EXISTS将传回TRUE,而这笔数据则被外层查询传回。

    select name
    from sys_user u
    where exists
    (
    select 1 --可为任何常数,如数值1
    from sys_unit unit
    where u.unit_id = unit.id and name in('广州大学','广东工业大学')
    )


    嵌套子查询与相关子查询的差别在于:子查询是否可以单独单独执行,相关子查询必须与外层查询相互关联,外层查询必须将每一笔

    数据传入子查询进行比对,符合子查询的数据最后才会被外层查询传回。

     

    1、派生表

    派生表是应用于FORM子句中的子查询,将子查询中的数据暂时视为一个虚拟数据表以供主查询使用。子查询时一完整查询,

    可使用数据表连接、GROUP BY甚至是ORDER  BY子句,将子查询置于主查询的FORM子句后,需将子查询置于括号内并予以命名,像使用一般数据表那样。

    select u.name
    from sys_user u,
    (
    select id,name
    from sys_unit where name in('广州大学','广东工业大学')
    ) t
    where u.unit_id = t.id 


    2、通用表表达式

    对于较复杂的查询命令,有时将需要重复运用相同的子查询,采用派生表的作法将耗用数据库许多资源,造成执行效率低下等问题。为避免重复执行相同的子查询,建议采用通用表表达式,即WITH命令。

    with Emp
    as
    (
    select id,name
    from sys_unit where name in('广州大学','广东工业大学')
    )
    --后续操作与一般表的使用方式向他
    select u.name
    from sys_user u,Emp e
    where u.unit_id = e.id;


     

     

    展开全文
  • 本文主要向大家介绍了Oracle数据库之Oracle子查询相关内容(包含TOP-N查询和分页查询),通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。一、子查询1、概念:嵌入在一个查询中的另一个查询语句,也...

    本文主要向大家介绍了Oracle数据库之Oracle子查询相关内容(包含TOP-N查询和分页查询),通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。

    一、子查询

    1、概念:嵌入在一个查询中的另一个查询语句,也就是说一个查询作为另一个查询的条件,这个查询称为子查询。

    那么可以使用子查询的位置有select后面、from后面、where后面以及having后面。

    2、分类:(1)单行子查询:查询结果只返回一行数据

    (2)多行子查询:查询结果返回多行数据,多行子查询的操作符有IN,ALL,ANY,具体用法实例中说明。

    3、示例说明:

    Example1:查找每个部门的员工数量:

    select deptno,dname,(select count(*) from emp e where e.deptno=d.deptno) amount from dept d;

    此处子查询位于select后面,是每个部门的员工总人数。

    Example2:查找工资大于部门平均工资的员工

    select ename,sal,e.deptno from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) m

    where e.deptno=m.deptno and e.sal>m.avgsal;

    此处子查询位于from后面,是每个部门的平均工资,将这个结果看做一张新表m,再加上查询条件即可。

    Example3:查找和scott相同职位的员工信息

    select * from emp where job=(select job from emp where ename='SCOTT');

    此处子查询位于where条件中,是和scott员工一样的职位。

    Example4:查询部门平均工资大于30号部门最高工资的部门信息。

    select deptno,avg(sal) from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);

    此处子查询位于having子句中,是30号部门的最高工资。

    Example5:查询部门是开发部或销售部的员工信息

    select * from emp where deptno in(select deptno from dept where dname='RESEARCH' or dname='SALES');

    此处用到了多行子查询的IN操作符用来获取RESEARCH和SALES部门的部门号,用来限制一个范围。

    Example6:获取工资大于30号部门所有员工工资的信息。

    select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);

    此处用到了多行子查询中的ALL操作符,用于获取30号部门的所有工资信息,这里all起的主要作用是为了获得30号部门的最大工资,大于所有的意思就是大于最大的即可。

    Example7:获取工资大于30号部门任意员工工资的信息。

    select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);

    此处用到了多行子查询中的any操作符,用于获取30号部门的工资信息,这里any的作用和all不同,主要取最小工资,任意就是说大于这些工资里面任意一个也就是大于最小的工资即可。

    二、oracle中TOP-N查询:

    概念:用于获取一个查询中的前N条记录,需要借助rownum伪列来实现,rownum伪列,oracle为每个查询自动生成的伪列,物理上并不存在,查询中经常涉及多个表,但每个查询只有一列伪列。

    Example:查找部门号为20和30的工资最高的5个员工信息

    select * from (select * from emp where deptno in(20,30) order by sal desc) where rownum<=5;

    这里在from后加了一个子查询,那么有个问题出现了,为什么这里不直接写而是要引入一个子查询呢,先来看看不加的结果:

    select * from emp e where e.deptno in(20,30) and rownum<=5 order by e.sal desc;

    很明显3000才是最高的工资,那是什么原因导致了这样的结果呢?是因为oracle中对select查询语句的执行顺序是先where条件后order by排序,也就是说先取了5行在对这5行进行排序,而正确的顺序应该是所有20,30部门的员工工资先进行排序在取5行

    三、Oracle分页查询的应用:

    概念:分页查询,顾名思义,控制查询结果的范围,得到我们想要的部分数据。

    Example:获取员工表中20,30部门按工资降序以后的第4页也就是第7,8两条数据

    select * from (select rownum rowline,emp1.* from (select * from emp where deptno in (20,30)

    order by sal) emp1 where rownum<=10) emp2 where emp2.rowline>=7 and emp2.rowline<=8;

    这里或许稍微有点复杂,首先为什么不这样写

    select * from (select * from emp where deptno in(20,30) order by sal desc) where rownum>=7 and rownum<=8;

    这个查询永远也不会有数据生成,为什么呢,因为当内层查询产生第一条记录时,oracle为其伪列赋值rownum=1,

    外层查询判rownum>=7 and rownum<=8不符合条件去除记录,当第二条记录产生时,oracle仍然会为其伪列赋值rownum=1,

    外层判断仍然不会通过,这样无论内层查询产生多少数据都会因为外层查询的条件不符合记录而流失数据。

    而想要避免这样的情况发生,就需要将伪列当成一个查询中的字段,将它不在看做“伪列”,而是真正的一个字段,

    这样就需要在外面在嵌套一层查询将伪列做成一个物理上存在的字段,而最后我们只需要将外层查询的条件改为内层查询中“真实”存在的伪列即可。

    本文由职坐标整理并发布,希望对同学们学习Oracle有所帮助,更多内容请关注职坐标数据库Oracle数据库频道!

    展开全文
  • Oracle 子查询

    2017-06-12 18:27:46
    Oracle 子查询 子查询可以返回单行结果,可以返回多行结果,也可以不返回结果。   如果子查询未返回任何行,则主查询也不会返回任何结果 (空值)select * from emp where sal > (select sal from emp where ...
  • Oracle子查询的注意问题

    千次阅读 2016-12-27 22:57:47
    1. 子查询中小括号,子查询要用()包起来 ...2. Oracle子查询书写风格 缩紧、对齐、换行等等,便于观察 3. Oracle可以使用的位置: 可以在where、select、having、from之后使用子查询 如果查询的时候有
  • 四、oracle子查询

    2020-01-31 12:10:34
    4.1.单行子查询 4.2. TopN 查询 4.3. 分页查询 4.4. exists 4.5. 对于 in 和 exists 的性能区别: 4.1.单行子查询 select * from emp where sal > (selectsal from emp where empno = 7566); 子查询空值/多...
  • Oracle子查询的用法

    2016-02-24 16:02:47
     在执行数据库操作(包括查询、添加、修改、删除等操作)的过程中,如果某个操作需要依赖另一个SELECT语句的查询结果,那么就可以把SELECT语句嵌入到该操作语句中,这样就形成了一个子查询。实际上,在关系型数据库...
  • Oracle 子查询展开(subquery unnesting)

    千次阅读 2017-07-10 15:04:52
    Oracle 子查询展开(subquery unnesting)
  • Oracle子查询与伪列

    2017-08-25 15:34:45
    1. 子查询 子查询在SELECT、UPDATE、DELETE语句内部可以...1. 单行子查询:不向外部返回结果,或者只返回一行结果。 2. 多行子查询:向外部返回零行、一行或者多行结果。   案例1:查询出销售部(SALES)下面的员
  • SELECT 理解子查询的关键在于把子查询当作一张表来看待,外层的语句可以把内嵌的子查询返回的结果当成一张表使用,子查询结果可以作为一个虚表被使用。注意,子查询要用括号括起来 。子查询根据其返回结果可以分为...
  • Oracle 子查询-07

    2020-10-28 10:39:23
    1. 子查询 1.1 1.2 什么是子查询
  • oracle子查询详解

    千次阅读 2018-04-24 10:37:37
    当一个查询结果是另一个查询的条件的时候,那么就称为子查询子查询是在SQL语句内的另外一条SELECT语句。 在SELECT、INSERT、UPDATE或DELETE命令中只要是表达式的地方都可以包含子查询子查询甚至可以包含在...
  • Oracle - 一般子查询+相关子查询

    千次阅读 2019-04-08 21:45:41
    一般子查询(select查询结果作为另一个查询的条件 => 不含有Exists的SQL查询语句) 相关子查询(含有Exists的SQL查询语句) Example-1: Example-2: 一:执行顺序与原理 ◼ ◼ 普通子查询的执行顺序是...
  • Oracle 高级查询-子查询 将一个查询结果作为基表再次进行查询,或者是将查询结果作为基表再和其他查询结果或者表再次联合!...-- 外层查询的列和条件是在子查询的结果集上进行的(所以列名以子查询结果的列定
  • -----------------------------------------Oracle子查询和exists及并集union和union all的使用-----------------------------------------------子查询:在一个查询的内部还包括另一个查询,则此查询称为子查询。...
  • 转-oracle 子查询

    2010-01-18 10:20:26
    对于父查询中处理的记录来说,一个关联子查询是每行计算一次,然而一个非关联子查询只会执行一次,而且结果集被保存在内存中(如果结果集比较小),或者放在一张oracle临时数据段中(如果结果集比较大)。...
  • 子查询, 分页查询
  • 1.相关子查询 --类似not in的查询条件会受到子查询中null值的影响,通常会排除子查询中的空值 SELECT ename FROM emp WHERE empno NOT IN (SELECT mgr FROM emp WHERE mgr IS NOT NULL); --有一种查询,出现了...
  • 本节介绍Oracle子查询的相关内容: 实例用到的数据为oracle中scott用户下的emp员工表,dept部门表,数据如下: 一、子查询 1、概念:嵌入在一个查询中的另一个查询语句,也就是说一个查询作为另一个查询的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,449
精华内容 2,979
关键字:

oracle子查询使用外层查询结果