[ORACLE INLINE VIEW, subquery ] 오라클 인라인뷰, 서브쿼리

//*****************************************

-- INLINE VIEW, 서브쿼리(subquery)

   - inline view는 SELECT, INSERT, UPDATE, DELETE 문이나 다른 하위 쿼리 내부에 중첩된 SELECT 쿼리 이다.

   - inline view는 식이 허용되는 모든 위치에서 사용할 수 있으며 단독으로 실행 가능 하다.

   - 다음의 SQL 명령절에 사용이 가능하다.

     - WHERE 절

     - HAVING 절

     - INSERT 문장의 INTO 절

     - UPDATE 문장의 SET 절

     - SELECT 또는 DELETE 문장의 FROM 절

   - subquery에는 두 종류의 연산자가 사용.

     1) 결과로 하나의 행과 하나의 컬럼을 반환 받아 연산 할수 있는 연산자

       >, <, >=, <=, =, !=

      SELECT num, name, basicpay  FROM insa

            WHERE basicpay < (SELECT AVG(basicpay) FROM insa);

     2) 결과로 여러 행을 반환 받아 연산 할수 있는 연산자 

      IN, ANY, ALL

      SELECT num, name, basicpay  FROM insa

            WHERE num IN (SELECT num FROM insa WHERE MOD(SUBSTR(ssn,8,1),2)=0);

   - ANY(SOME) 연산자의 사용

     - ANY 연산자는 IN 연산자와 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.

     - ANY 연산자는 서브쿼리에서 리턴되는 어떠한 값이라도 만족을 하면 조건이 성립된다.

 

SELECT  num, name, basicpay, ssn 

FROM insa

WHERE num = ANY (SELECT num FROM insa WHERE MOD(SUBSTR(ssn,8,1),2)=0);

 

는 다음과 동일

 

SELECT  num, name, basicpay, ssn 

FROM insa

WHERE num IN (SELECT num FROM insa WHERE MOD(SUBSTR(ssn,8,1),2)=0);

 

-- 1003 보다 큰 모든 데이터(ANY : OR 개념)

SELECT  num, name, basicpay, ssn 

FROM insa

WHERE num > ANY (1003, 1005, 1007);

-- 여자의 num이 가장 작은 num 보다 큰 모두 데이터 출력

SELECT  num, name, basicpay, ssn 

FROM insa

WHERE num > ANY (SELECT num FROM insa WHERE MOD(SUBSTR(ssn,8,1),2)=0);

 

   - ALL 연산자의 사용

     - ALL 연산자는 IN 연산자와는 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.

     - ALL 연산자는 서브쿼리에서 리턴되는 모든 값을 만족하면 조건이 성립된다.

     - ANY는 OR의 개념, ALL은 AND의 개념과 유사하다.

 

-- 1007 보다 큰 모든 데이터(ALL : AND 개념)

SELECT  num, name, basicpay, ssn 

FROM insa

WHERE num > ALL (1003, 1005, 1007);

 

-- 서울 사람보다 급여를 많이 받는 사람은 : num, name, city, basicpay

SELECT num, name, city, basicpay

FROM insa

WHERE basicpay > (SELECT MAX(basicpay) FROM insa WHERE city='서울');

SELECT num, name, city, basicpay

FROM insa

WHERE basicpay > ALL (SELECT basicpay FROM insa WHERE city='서울');

 

-- insa : basicpay가 평균 미만인 사람의 수당을 10만원 더해준다.(수정)

SELECT num, name, basicpay, basicpay-(SELECT AVG(basicpay) FROM insa) FROM insa;

SELECT num, name, basicpay, basicpay-AVG(basicpay) OVER() FROM insa;

SELECT num, name, basicpay, sudang, AVG(basicpay) OVER()

FROM insa

SELECT num, name, basicpay, sudang

FROM insa

WHERE basicpay < (SELECT AVG(basicpay) FROM insa);

SELECT num, name, basicpay, sudang

FROM insa

WHERE basicpay < AVG(basicpay) OVER(); (X)

UPDATE insa SET sudang = sudang + 100000

WHERE basicpay < (SELECT AVG(basicpay) FROM insa);

SELECT num, name, basicpay, sudang FROM insa;

ROLLBACK;

 

 

//*****************************************

-- WITH

   -- 서브쿼리를 미리 블럭으로 정한후 사용(여러번 사용될 경우 간결)

-- b_id, title, price, g_id, g_name, p_date, p_su

book : b_id, title

danga : b_id, price

panmai : b_id, g_id, p_su, p_date

gogaek : g_id, g_name

WITH tb AS (

   SELECT b.b_id, title, price, p.g_id, g_name, p_date, p_su, price*p_su 금액

   FROM book b

   JOIN danga d ON b.b_id = d.b_id

   JOIN panmai p ON b.b_id = p.b_id

   JOIN gogaek g ON p.g_id = g.g_id

) 

SELECT * FROM tb;

WITH tb AS (

   SELECT b.b_id, title, price, p.g_id, g_name, p_date, p_su, price*p_su 금액

   FROM book b

   JOIN danga d ON b.b_id = d.b_id

   JOIN panmai p ON b.b_id = p.b_id

   JOIN gogaek g ON p.g_id = g.g_id

) 

SELECT TO_CHAR(p_date, 'YYYY') 년도, SUM(금액)

FROM tb

GROUP BY TO_CHAR(p_date, 'YYYY')

ORDER BY 년도;

-- 년도별 판매금액이 가장 많은 서점을 다음과 같이 출력

년도  서점코드  서점명  년도판매금액합

WITH tb AS (

   SELECT b.b_id, title, price, p.g_id, g_name, p_date, p_su, price*p_su 금액

   FROM book b

   JOIN danga d ON b.b_id = d.b_id

   JOIN panmai p ON b.b_id = p.b_id

   JOIN gogaek g ON p.g_id = g.g_id

) 

SELECT TO_CHAR(p_date, 'YYYY') 년도, g_id, g_name, SUM(금액) 년도금액,

       RANK() OVER(PARTITION BY TO_CHAR(p_date, 'YYYY') ORDER BY SUM(금액) DESC) 순위

FROM tb

GROUP BY TO_CHAR(p_date, 'YYYY'), g_id, g_name;

WITH tb AS (

   SELECT b.b_id, title, price, p.g_id, g_name, p_date, p_su, price*p_su 금액

   FROM book b

   JOIN danga d ON b.b_id = d.b_id

   JOIN panmai p ON b.b_id = p.b_id

   JOIN gogaek g ON p.g_id = g.g_id

)

SELECT 년도, g_id, g_name, 년도금액 FROM (

    SELECT TO_CHAR(p_date, 'YYYY') 년도, g_id, g_name, SUM(금액) 년도금액,

       RANK() OVER(PARTITION BY TO_CHAR(p_date, 'YYYY') ORDER BY SUM(금액) DESC) 순위

    FROM tb

    GROUP BY TO_CHAR(p_date, 'YYYY'), g_id, g_name

) WHERE 순위=1;

SELECT 년도, g_id, g_name, 년도금액 FROM (

    SELECT TO_CHAR(p_date, 'YYYY') 년도, p.g_id, g_name, SUM(p_su*price) 년도금액,

      RANK() OVER(PARTITION BY TO_CHAR(p_date, 'YYYY') ORDER BY SUM(p_su*price) DESC) 순위

    FROM panmai p

JOIN danga d ON p.b_id = d.b_id

JOIN gogaek g ON p.g_id = g.g_id

GROUP BY TO_CHAR(p_date, 'YYYY'), p.g_id, g_name

) WHERE 순위=1;

//*****************************************

-- EXISTS (subquery) : 하나라도 존재하면 참

    이그지스트 조건은 mainquery로 리턴되는 subquery의 결과 행의 존재 여부를 체크하여 존재하면 TRUE를 반환한다.

    EXISTS 조건 대신 IN 조건을 사용해서 표현할 수도 있다.

//*****************************************

-- 상관 하위 부질의

   상호 연관 서브 쿼리(correlated sub query)는 sub query가 main query의 값을 이용하고, 그렇게 구해진 sub query의 값을 다시 main query가 다시 이용하게 된다.