sub-query수업정리_2021-02-02

----------------------------------------------------------
--window function 
----------------------------------------------------------
--행과 행간의 관계를 쉽게 정의하기 위한 표준함수 
--1.순위함수
--2.집계함수
--3.분석함수 

/*
window_function(args) over([partition by절][order by절][windowing절])

1.args윈도우함수 인자 0~n개 지정
2.partition by절 : 그룹핑
3.order by절 : 정렬기준 컬럼
4.windowing절 : 처리할 행의 범위를 지정.

*/

--rank() over() : 순위를 지정
--dense_rank() over(): 빠진 숫자 없이 순위를 지정
select emp_name,
        salary,
        rank() over(order by salary desc) rank,
        dense_rank() over(order by salary desc) rank
from employee;

--그룹핑에 따른 순위지정

select E.*
from(
     select emp_name,   
            dept_code,
            salary,
            rank() over(partition by dept_code order by salary desc) rank
     from employee
     )E
where rank between 1 and 3;

--sum() over()
--일반컬럼과 같이 사용할 수 있다.
select emp_name,
        dept_code,
        sum(salary) over()"전체사원급여합계",
        sum(salary) over(partition by dept_code)"부서별 급여합계",
        sum(salary) over(partition by dept_code order by salary)"부서별 급여누계_급여"
from employee;

--avg() over()
select emp_name,
        dept_code,
        salary,
        trunc(avg(salary) over(partition by dept_code))"부서별 평균급여"
from employee
order by 3;

--count() over()
select emp_name,
        dept_code,
        count(*)over(partition by dept_code)cnt_by_dept
from employee;
​

--exists연산자 
--exists(sub-query) sub-query에 행이 존재하면 참, 존재하지 않으면 거짓

select * 
from employee
where 1 = 1; -- true 결과행이 존재한다.

select * 
from employee
where 1 = 0; -- false 결과행이 존재하지 않는다.

--행이 존재하는 subquery : exist true
select*
from employee
where exists(
            select*
            from employee
            where 1=1
            );

--행이 존재하지 않는 subquery: exist false
select*
from employee
where exists(
            select*
            from employee
            where 1=0
            );

--관리하는 직원이 한명이라도 존재하는 관리자사원 조회!
--내 emp_id 값이 누군가의 manager_id로 사용된다면, 나는 관리자
--내 emp_id 값이 누군가의 manager_id로 사용되지 않는다면, 나는 관리자x

select emp_id, emp_name
from employee E
where exists(
            select *
            from employee
            where manager_id = E.emp_id
            ) -- true // 값이 있으면 true // 없으면 false

--부서테이블에서 실제 사원이 존재하는 부서만 조회 (부서코드, 부서명)
select dept_id,
        dept_title
from department D
where exists(
            select*
            from employee
            where dept_code = D.dept_id
            );

--부서테이블에서 실제 사원이 존재하지 않는 부서만 조회 (부서코드, 부서명)
select dept_id,
        dept_title
from department D
where not exists(
            select*
            from employee
            where dept_code = D.dept_id
            );

--최대/최소값 구하기(not exisits)
--가장 많은 급여를 받는 사원을 조회 
--가장 많은 급여를 받는다 -> 본인보다 많이 급여를 받는 사람이 존재하지 않는다

select emp_name, salary
from employee E
where not exists (
                select 1
                from employee
                where salary > e.salary
                );

----------------------------------------------------------
--Scala(값이 하나) subquery
----------------------------------------------------------
--서브쿼리의 실행결과가 1(단일행 단일컬럼)인 select절에 사용된 상관서브쿼리
--관리자이름 조회 
select emp_name,
       (
        select emp_name 
        from employee
        where emp_id = E.manager_id
       )manager_name 
from employee E;

--사원명, 부서명, 직급명 조회

select E.emp_name 사원명,
        (
        select D.dept_title
        from department D
        where E.dept_code = D.dept_id
        ) 부서명,
        (
        select J.job_name
        from job J
        where E.job_code = J.job_code
        ) 직급명
from employee E;

----------------------------------------------------------
--inline view
----------------------------------------------------------
--from절에 사용된 subquery. 가상테이블 

--여사원의 사번, 사원명, 성별 조회

select emp_id,
       emp_name
       decode(substr(emp_no,8,1), '1','남','3','남','여') ='여';
from employee
where decode(substr(emp_no,8,1), '1','남','3','남','여') ='여';

select *
from (        
        select emp_id,
               emp_name,
               decode(substr(emp_no,8,1), '1','남','3','남','여') gender
        from employee
        )
where gender ='여'; 

--30 ~ 50살 사이의 여사원 조회(사번, 이름, 부서명, 나이, 성별)
--inline-view나이, 성별 

select emp_id 사번,
           emp_name 이름,
           nvl(dept_code,'인턴') 부서명,
           age 나이,
           gender 성별
from (
        select emp_id,
        emp_name,
        dept_code,
        decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender,
        extract(year from sysdate)-(decode(substr(emp_no, 8, 1), '1', 1900+substr(emp_no, 1, 2), 
        '2', 1900+substr(emp_no, 1, 2), 2000+substr(emp_no, 1, 2)))+1 age
        from employee
)
where gender ='여' and age between 30 and 50;

-- 직급이 대리인 사원 중에서 연봉 top-3명을 조회 (순위,이름, 연봉)
​
select rownum,
       emp_name,
        salary * 12
from (
        select rownum old, emp_name, salary
        from employee E
        where job_code in (select job_code from job where job_name = '대리')
        order by  salary * 12  desc
      ) E
where rownum < 4;
​
​
-- 부서별 평균 급여 TOP-3 조회(순위, 부서명, 평균급여)
select rownum 순위,
       (select dept_title from department where dept_id = E.dept_code) 부서명,
       avg 평균급여
from (
      select dept_code, trunc(avg(salary)) avg
      from employee
      group by dept_code
      order by avg desc
     ) E
where rownum <= 3;

/*
공식
select E.*
from (
        select rownum rnum, E.*
        from(  
            <<정렬된 ResultSet>>
            )E
        )E
where rnum between 1 and 10;
*/

with emp_hire_date_asc
as
(select emp_name,
        hire_date
from employee
order by hire_date asc
)
select E.*
from (
        select rownum rnum, E.*
        from emp_hire_date_asc E 
        )E
where rnum between 6 and 10;