Oracle(6일차)

오늘 가장 중요하게 다룬 것은 바로 서브 쿼리이다. 

 

서브 쿼리에서 기억해야할 것은 바로 쿼리 안에 쿼리를 넣어서 사용할 수 있다는 것이다.

만약 다른 프로그래밍 언어를 배워봤으면 조금 이해하기 쉬울 것이다. 

 

대표적으로 if문 혹은 for문 등을 사용한다고 하면 안에 또 다른 if문이나 for문들을 넣고 넣을 수 있었다. 

 

이처럼 주 쿼리 안에 서브 쿼리를 넣을 수 있다는 개념이다.

-- 기본형
select ~ from ~ 

-- 서브 쿼리 예시
select ~ from 
	(
	select ~ from ~
    );

select문 안에 select문을 넣는 형태로 예시를 들어보았다. 

거의 비슷한 형태로 사용할 것이다.

 

어떤 상황에서 쓰는지 생각하면서 이해해보자.

우리는 조회한 테이블 안에서 또다른 테이블을 조회할 수도 있다. 

 

서브쿼리가 빛을 발하는 상황은 바로 정렬에서 일부만 조회하고 싶은 경우이다. 

쉽게 생각해서

select 
	rownumm as rank,
	~~ as ~~, 
	~~ as ~~
 from ~~
 order by ~~ desc
 where rank < 5;

이렇게 할 수 있을 것만 같다. 

 

그러나 실제로는 불가능하다. 

이유는 order by는 절대로 where 앞에 올 수 없기 때문이다. 

-- 실제로 order by는 우리 조회하는 데이터들을 마지막에 정렬해주는 함수이다. 정렬해서 출력이 된다. 

    그렇기에 데이터의 조건문이 where은 반드시 출력 전(order by)전에 와야한다.

-- 추가적으로 기억하면 좋은 사실은 where문은 col 별칭이 사용 불가능하고
    order by는 사용가능하다는 것이다. order by문은 조회된 데이터를 기준으로 움직이는 함수임을 기억해보자. 

 

 위를 실제로 해보기 위해서는 서브쿼리를 사용해야한다. 

사용한다면

select rank, ~~ from
	(select 
		rownumm as rank,
		~~ as ~~, 
		~~ as ~~
 	from ~~
 	order by ~~ desc)
 where rank < 5;

 

이런 형태로 서브쿼리를 이용한다면 우리가 원하는 형태로 출력할 수 있다.

 

 

 

문제를 통해서 이를 한 번 더 복습해보자.

step-1:

create table emp
as
select first_name||' '||last_name as fname,
            hire_date as join_day, salary as sal
from employees;

step-2:

col full_n format a20
col last_name format a10
col first_name format a10

select fname as full_n,
	instr(fname, ' ') as space,
	length(fname) as length,
	substr(fname, instr(fname, ' ')) as last_name,
	substr(fname, 0, instr(fname, ' ')) as first_name,
	to_char(join_day, 'dd/mm/yy') as day,
	replace(to_char(sal*12, '$999,999'), ',', '.') as year_sal
from emp;

 

col full_name format a20
col job format a20
col num_yy_hired format a20

select rownum as rank, id, full_name, job, hr, num_yy_hired
from (select EMPLOYEE_ID as id,
        last_name||', '||first_name as full_name,
         job_id as job,
        to_char(salary/160, '$999,999') as hr,
        round(months_between(sysdate, hire_date)/12)||' years' as num_yy_hired
    from employees
    order by hr desc)
where rownum < 6;

 

select * from
(select rownum as rank, id, full_name, job, year_hr, hr, hr_won, num_yy_hired, hire_date
from 
	(select EMPLOYEE_ID as id,
            last_name||', '||first_name as full_name,
            job_id as job,
            replace(to_char(salary*12, '$999,999'), ',', '.') as year_hr,
            replace(to_char(salary/160, '$999,999'), ',', '.') as hr,
            replace(to_char((salary/160)*1374, 'L999,999'), ',', '.') as hr_won,
            round(months_between(sysdate, hire_date)/12)||' years' as num_yy_hired,
            to_char(hire_date, 'dd/mm/yy') as hire_date
	from employees
    order by hr desc)
)
where rank between 15 and 25;

 

select * from
	(select rownum as rank, id, full_name, job, year_hr, hr, hr_won, num_yy_hired, hire_date 
    from (select EMPLOYEE_ID as id,
				 last_name||', '||first_name as full_name,
 				 job_id as job,
 				 to_char(salary*12, '999999') as year_hr,
				 replace(to_char(salary/160, '$999,999'), ',', '.') as hr,
				 replace(to_char((salary/160)*1374, 'L999,999'), ',', '.') as hr_won,
				 round(months_between(sysdate, hire_date)/12)||' years' as num_yy_hired,
                 to_char(hire_date, 'yyyy-mm-dd') as hire_date
         from employees
		 order by hr desc)
	 )
where rank between 5 and 8
or rank in (3,13,23);