万事俱备之ORACLE_SQL 练手 part5

更多参考和数据脚本下载：

1.万事俱备之ORACLE_SQL 练手 part1

select * from regions;
select * from countries;
select * from locations;
select * from departments;
select * from jobs;
-- 1. 哪些部门的人数比90 号部门的人数多。
---区别count(*)、count(1)、count(column)
----•count(1) 中的 1 并不是表示为第一个 column
----•count(*) 跟 count(1) 的结果一样，包括对NULL的统计
----•count(column) 是不包括对NULL的统计
---所以以下包括部分column为null的项
select department_id,count(department_id) nums
from employees
group by department_id
having count(department_id)>
(select count(*) nums from employees where department_id=90)
;
---count 结果不包括department_id为null的项
select department_id,count(department_id) nums
from employees
group by department_id
having count(*)>
(select count(department_id) nums from employees where department_id=90)
;

--2. Den (FIRST_NAME) 、Raphaely (LAST_NAME) 的领导是谁（非关联子查询in） 。
-- 区别 in 和exists
---in用的是父查询表的索引，此处父子查询表相同
select * from employees
where employee_id in (select manager_id from employees 
where FIRST_NAME='Den' and last_name='Raphaely' );
---exists 用到了子查询表的索引，如果子查询的表小，索引就小，速度就快
select * from employees empp
where  exists (select manager_id from employees 
where FIRST_NAME='Den' and last_name='Raphaely'and empp.employee_id=manager_id);

--3.Den (FIRST_NAME) 、Raphaely (LAST_NAME) 领导谁（树形查询）
---树形查询 start with+起点条件 connect by目标条件  
---- prior x_id = y_id (prior 指定目标的对比列 就是找与x_id相同的y_id)
----最大的好处就是不用关联表，当表大的时候优势可能比较明显
select * from employees start with
 FIRST_NAME='Den' and last_name='Raphaely' 
 connect by employee_id = prior manager_id;
----prior employee_id =  manager_id 找对应的子结点
select * from employees start with 
FIRST_NAME='Den' and last_name='Raphaely'  
connect by prior employee_id =  manager_id;

--4. Den (FIRST_NAME) 、Raphaely (LAST_NAME) 的领导是谁（关联子查询exists ） 。
select * from employees empp
where  exists
(select manager_id from employees where FIRST_NAME='Den' 
and last_name='Raphaely'and empp.employee_id=manager_id);

---找下属
select * from employees empp
where  exists
(select null from employees where FIRST_NAME='Den' 
and last_name='Raphaely'and empp.manager_id=employee_id);

--5. 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期

--not good
select FIRST_NAME || ' ' || LAST_NAME ename ,salary,hire_date from employees empp
,(select department_id,min(salary) minsal from employees
group by department_id) tt
where empp.department_id= tt.department_id
and empp.salary>tt.minsal
and empp.haire_date;

---答案是通过 分析函数把同部门工资比自己低，又比自己先入职的最小工资筛选出来。再做一次查询，筛选出有比自己工资小但先后入职。
--包含相同日期工资高的
SELECT *
from
(select department_id,first_name || ' ' || last_name AS ename,hire_date,salary,
MIN(salary) over(PARTITION BY department_id ORDER BY hire_date ) AS p_cmin
FROM employees)
where salary>p_cmin;

--work 不包含相同日期工资高的
SELECT *
FROM (SELECT department_id,first_name || ' ' || last_name AS ename,
hire_date AS hdate,
salary AS sal,
MIN(salary) over(PARTITION BY e.department_id ORDER BY e.hire_date RANGE BETWEEN unbounded preceding
AND 1 preceding)as min_salary
FROM    employees e
ORDER BY department_id,hire_date
)
WHERE sal> MIN_salary;
--隐形连接，慢
select distinct e1.first_name||' '||e1.last_name,e1.salary,e1.hire_date 
from employees e1 join employees e2 
on e1.department_id=e2.department_id 
where e1.hire_date> e2.hire_date and  e1.salary>e2.salary;

--6. Finance 部门有哪些职位（非关联子查询in） 。
select distinct job_id from employees  
where department_id in(select department_id from departments where departments.department_name ='Finance');

--7. Finance 部门有哪些职位（关联子查询exists） 。
select distinct job_id from employees  empp
where exists(select department_id from departments 
where departments.department_name ='Finance' and empp.department_id=department_id
);