Oracle(三) – 增强子查询例子

–查询各个部门的平均工资
select department_id, avg(salary)
from employees
group by department_id;

–查询所有薪水高于其所在部门平均薪水的员工
select a.last_name, a.salary, a.department_id, b.salarg
from employees a,
(select department_id, avg(salary) salarg
from employees
group by department_id) b
where a.department_id = b.department_id
and a.salary > b.salarg;

select last_name, salary, department_id
from employees outer
where salary > (select avg(salary) from employees where department_id = outer.department_id);

–查询更换部门达到两次以上的员工
select e.employee_id, e.last_name, e.job_id
from employees e
where 2 <= (select count(*) from job_history where employee_id = e.employee_id)

–查询所有主管
select employee_id, last_name, job_id, department_id
from employees outer
where EXISTS(select ‘X’ from employees where manager_id = outer.employee_id);
–如何理解：选择员工，哪些员工呢？符合manager_id的，即 查询到的员工的employee_id必须要 与employees表中某个或某些员工记录的manager_id存在相同的值
–另一种方法
select employee_id, last_name, job_id, department_id
from employees
where employee_id IN (select manager_id from employees where manager_id IS NOT NULL);

–查找没有员工的部门
select department_id, department_name
from departments d
where NOT EXISTS(select ‘X’ from employees where department_id = d.department_id);

–看看下面这个是否可以？
select department_id, department_name
from departments
where department_id NOT IN (select department_id from employees);
–查询结果为空，肯定不可以了，为什么呢？原因在于 子句 select department_id from employees查询到的结果中有NULL，那么(NOT IN NULL)又代表什么呢？没办法，什么数据都查不到呗。

不过，这样修改一下就可以了

select department_id, department_name
from departments
where department_id NOT IN (select department_id from employees where 1=1 and department_id is not null);