摘自https://blog.csdn.net/u010565545/article/details/100785261
简要提取重要内容如下：
其实题目比较基础，还是看自己的掌握程度（熟练）

--问题：查询工资大于149号员工工资的员工的信息
select * 
from employees
where salary > (
      select salary
      from employees
      where employee_id = 149
)

--问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的
--employee_id, manager_id, department_id  
select employee_id, manager_id, department_id
from employees
where manager_id in (
      select manager_id
      from employees
      where employee_id in(141, 174)
) and department_id in (
      select department_id
      from employees
      where employee_id in(141, 174)
) and employee_id not in (141, 174);

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);

--1. from 子句中使用子查询
select max(avg(salary))
from employees
group by department_id;

select max(avg_sal)
from (
      select avg(salary) avg_sal
      from employees
      group by department_id
) e

--问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

select last_name, department_id, salary, (select avg(salary) from employees where department_id = e1.department_id)
from employees e1
where salary > (
      select avg(salary)
      from employees e2
      where e1.department_id = e2.department_id
)

select last_name, e1.department_id, salary, avg_sal
from employees e1, (
     select department_id, avg(salary) avg_sal
     from employees
     group by department_id
) e2
where e1.department_id = e2.department_id
and e1.salary > e2.avg_sal;

--case...when ... then... when ... then ... else ... end 
--查询：若部门为10 查看工资的 1.1 倍，部门号为 20 工资的1.2倍，其余 1.3 倍
select employee_id, last_name, salary, case department_id when 10 then salary * 1.1
                                                          when 20 then salary * 1.2
                                                          else salary * 1.3
                                                          end "new_salary"
from employees;

select employee_id, last_name, salary, decode(department_id, 10, salary * 1.1,
                                                             20, salary * 1.2,
                                                             salary * 1.3) "new_salary"
from employees;

--问题：显式员工的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;

--问题：查询员工的employee_id,last_name,要求按照员工的department_name排序
select employee_id, last_name
from employees e1
order by (
      select department_name
      from departments d1
      where e1.department_id = d1.department_id
)

-- SQL 优化：能使用 EXISTS 就不要使用 IN

--问题：查询公司管理者的employee_id,last_name,job_id,department_id信息
select employee_id, last_name, job_id, department_id
from employees
where employee_id in (
      select manager_id
      from employees
)

select employee_id, last_name, job_id, department_id
from employees e1
where exists (
      select 'x'
      from employees e2
      where e1.employee_id = e2.manager_id
) 

-- 问题：查询departments表中，不存在于employees表中的部门的department_id和department_name
select department_id, department_name
from departments d1
where not exists (
      select 'x'
      from employees e1
      where e1.department_id = d1.department_id
)

--55. 更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job
    
update employees e1
set salary = (
    select max(salary)
    from employees e2
    where e1.department_id = e2.department_id
), job_id = (
   select job_id
   from employees
   group by job_id
   having avg(salary) = (
         select min(avg(salary))
         from employees
         group by job_id
   )
)
where employee_id = 108;
    
--56. 删除 108 号员工所在部门中工资最低的那个员工.
delete from employees e1
where salary = (
      select min(salary)
      from employees
      where department_id = (
            select department_id
            from employees
            where employee_id = 108
      )
)

select * from employees where employee_id = 108;
select * from employees where department_id = 100
order by salary;

rollback;