Oracle数据库学习（二）

--高级子查询
--查询与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

来自优快云用户 nothing 地址http://blog.youkuaiyun.com/u011687186