11. 서브쿼리(Sub Query) and 시퀀스(Sequence)

서브쿼리란?

하나의 쿼리 안에 또 다른 하나의 쿼리가 담겨 있는 것

--> 쿼리문장을 줄일 수 있다.

SELECT select_list
FROM TABLE 또는 VIEW
WHERE 조건 연산자 ( SELECT select_list
                                 FROM TABLE
                                 WHERE 조건);

---

Q. Lex의 급여보다 많이 받는 사원들의 이름과 급여 조회

SELECT first_name as "First Name", SALARY as "Salary"
FROM EMPLOYEES WHERE first_name = 'Lex';

SELECT first_name as "Frst Name", SALARY as "Salary"
FROM EMPLOYEES WHERE SALARY > 17000;

--이렇게 두번의 쿼리를 써야 풀 수 있는 건데 한 번 쿼리문장으로 만족할 수는 없을까?

SELECT first_name as "First Name", SALARY as "Salary"
FROM EMPLOYEES WHERE SALARY > ( SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME = 'Lex');

--> Sub Query를 사용해서 조건절 수정!

Q. Lex와 같은 부서 사람들의 이름과 부서코드, 연락처를 조회

--a. SUB QUERY를 사용해서 만들어라

SELECT FIRST_NAME as "이름", DEPARTMENT_ID as "부서 코드", PHONE_NUMBER as "연락처"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Lex');

--b. JOIN을 사용해서 만들어라 

SELECT e.FIRST_NAME as "이름", e.DEPARTMENT_ID as "부서 코드", e.PHONE_NUMBER as "연락처"
FROM EMPLOYEES e JOIN EMPLOYEES ee
ON e.department_id = ee.department_id
WHERE ee.first_name = 'Lex';

Q. Lex의 매니저가 관리하는 직원들의 이름과 이메일을 조회

--a. SUB QUERY를 사용해서 만들어라

SELECT FIRST_NAME as "이름", EMAIL as "이메일"
FROM EMPLOYEES 
WHERE MANAGER_ID = (SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Lex');

--b. JOIN을 사용해서 만들어라 

SELECT e.FIRST_NAME as "이름", e.EMAIL as "이메일"
FROM EMPLOYEES e JOIN EMPLOYEES ee
ON e.manager_id = ee.manager_id
WHERE ee.first_name = 'Lex'; 

---

다중행 서브쿼리 연산자 : any, all

>any : 최소값
code > any (10, 20, 30, 40)

<any : 최대값
code < any (10, 20, 30, 40)

<all : 최소값
code <all(10, 20, 30, 40)

>all : 최대값
code >all(10, 20, 30, 40)

Q. 부서 코드가 50번인 직원들 중 최대 급여보다 더 많이 받는 직원의 이름과 급여를 조회하시오 (복수행 연상 사용해보자)

SELECT first_name, salary
FROM EMPLOYEES
WHERE SALARY>all(SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 50);

Q. 매니저 아이디가 100번인 직원들의 최소 급여보다 더 많이 받는 직원들의 이름과 급여 조회

SELECT first_name, salary
FROM EMPLOYEES
WHERE SALARY>any(SELECT SALARY FROM EMPLOYEES WHERE MANAGER_ID = 100);

Q. 수수료(commission_pct)가 있는 직원들의 최대급여보다 더 적게 받는 직원의 이름과 급여 조회 

SELECT first_name, salary
FROM EMPLOYEES
WHERE SALARY<any(SELECT SALARY FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL);

Q. 2006년에 입사한 직원들의 최소급여보다 더 적게 받는 직원들의 이름과 급여 조회     

SELECT first_name, salary
FROM EMPLOYEES
WHERE SALARY<all(SELECT SALARY FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'rrrr') = '2006');

---

시퀀스(Sequence)

시퀀스 생성과 삭제

CREATE SEQUENCE seq_member;

drop table emp;

시퀀스 사용해보기(테이블)

--1) employees 테이블의 first_name, email, salary 컬럼만을 갖는 테이블 emp 생성(단, 테이블 구조만)
CREATE TABLE emp as SELECT FIRST_NAME, EMAIL, SALARY
FROM EMPLOYEES WHERE 1=2;

--2) no number(5,0)의 형식을 갖는 컬럼 추가
alter TABLE emp add(no number(5,0));

desc emp;

--3) 컬럼에 SEQUENCE 값을 증가시켜 저장 
INSERT INTO EMP VALUES('park', 'bogum@daum.net', 1000, seq_member.nextval);

SELECT * FROM emp; --확인

--시퀀스 번호가 1~10까지 반복하도록 설정하고 emp2에
--임의의 테이터를 입력하여 테스트하시오(시퀀스 명: seq_emp2)
CREATE SEQUENCE seq_emp2
START WITH 100
MAXVALUE 110
MINVALUE 90
CYCLE
CACHE 2;

INSERT INTO EMP VALUES('Lee', 'bean@daum.net', 500, seq_emp2.nextval);