EXISITS操作符,相关子查询，with子句

--exisits操作符
--查询公司管理者的employee_id,job_id ,last_name,department_id信息
--方法1
select employee_id,job_id,last_name,department_id from employees 
where employee_id in(select distinct manager_id from employees where manager_id is not null);
--方法2
select employee_id,job_id,last_name,department_id from employees e1
where employee_id in(select distinct manager_id from employees e2 e1.employee_id=e2.manager_id);
--方法3：自连接
select  distinct e1.employee_id,e1.job_id,e1.last_name,e1.department_id from employees e1,employees e2
where e1.employee_id= e2.manager_id;
--方法4 exists操作符 只要()中的where条件成立，不需要返回值，就会输出结果
select employee_id,job_id,last_name,department_id from employees e1
where EXISTS (select 'a' from employees e2  where e1.employee_id=e2.manager_id);


--not exisits 查询departments表当中，不存在于employees表中的部门的department_id和department_name
方法1：
select department_id,department_name from departments d
where not EXISTS (select 'a' from employees e where d.department_id= e.department_id);
方法2：
select department_id,department_name from departments where department_id in(
select department_id  from departments
minus
select department_id from employees);

--相关更新

--给表employees1添加了deparment_name项
alter table employees1
add department_name varchar2(30);
--给department_name项从departments表中获得相应得数据 并添加 
update employees1 e
set department_name=(select department_name from departments d where d.department_id= e.department_id);
select department_name,employee_id from employees1;

--删除表emp7中，其与emp8表皆有的数据 

delete FROM emp7 where department_id in(select department_id from emp8 where emp7.department_id=emp8.department_id);