Oracle数据库学习（二）

快来打我* 2022-09-25 06:29 223阅读 0赞

--高级子查询
    --查询与141号或174号员工的manager_id和department_id
    --相同的其他员工的employee_id,manager_id,department_id
    select employee_id,manager_id,department_id
    from employees e1
    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)
    --返回比本部门平均工资高的员工的last_name,department_id,salary及平均工资        
    select last_name,departmnet_id,salary,avgsalary
    from employees e1,(
    select department_id,avg(salary) avgsalary from employees group by department_id
    )e2
    where e1.department = e2.department_id
    --显示员工的employee_id,last_name和location
    --其中，若员工department_id与location_id为1800的department 相同时，则location为‘Canada’,其余为‘USA’
    select employee_id,last_name,
    (
           case department_id when(select department_id from department where location_id=1800)
             then 'Canada'
           else 
              'USA'
            end
    ) location
    from employees
    --查询员工employee_id,last_name,要求按照员工的department_name排序
    select employee_id,last_name
    from employees e1
    order by (select department_name
             from departments d
             where e1.department_id = d.department_id
    )
    --相关子查询
    --若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
    --输出这些相同id的员工的employee_id,last_name和其job_id
    select employee_id,last_name,job_id
    from employees e1
    where 2 <= (
          select count(*)
          from job_history
          where employee_id = e1.employee_id
    )
    --查询公司管理者的employee_id,last_name,job_id,department_id的信息
    select employee_id,last_name,job_id,department_id
    from employees e1
    where exists ( --不关心管理者是谁，当能找到管理者时就为true （not exists）
          select 'A' --查询的结果随意
          from employees e2
          where e1.employee_id = e2.employee_id
    )
    --与上面相反的是 not exists
    
    --查询部门名称 给新添加的字段 department_name
    --添加列
    alter table emp 
    add (department_name varchar(20))
    
    update 
        emp
    set department_name=(
        select department_name
        from departments
        where department_id=emp.department_id
        )
    --删除employee中，其与emp_history表中相同的数据
    delete from emp
    where department_id in (
          select department_id 
          from emp1
          where department = emp.department_id
    )
    --WITH子句
    --1.使用WITH子句，可以避免在select语句中重复书写相同的语句块
    --2.WITH子句将该子句中的语句块执行一次并存储到用户的临时表空间中
    --3.使用with子句可以提高查询效率
    --查询公司中工资比Abel高的员工的信息
    with Abel_sal as (
         select salary
         from employees 
         where last_name = 'Abel'
    )
    
    select  employee_id,last_name
    from employees
    where
         salary >(
                select salary
                from Abel_sal
         )
    --查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
    
    with dept_sumsal as(
    select sum(salary) sumsalary,department_name
    from departments d,employees e
    where d.department_id = e,department_id
    group by department_name
    )
    dept_avgsal as(
         select sum(sumsalary)/count(*) sum_al1
         from dept_sumsal
    )
    
    select * from dept_sumsal
    where sumsalary > (
          select sum_al1
          from dept_avgsal
    )
    order by department_name

来自CSDN用户 nothing 地址http://blog.csdn.net/u011687186