[SQL] SQL 기본 문법 정리 - VIEW, 서브쿼리

멋쟁이펭귄맨·2021년 7월 31일
sql문법정리자격증
0

이 게시글은 패스트캠퍼스의 '데이터베이스와 SQLD 합격패스 Online' 강의를 요약한 내용입니다.

1. VIEW

뷰(view)는 관계 데이터베이스의 데이터베이스 언어 SQL에서 하나 이상의 테이블 (또는 다른 뷰)에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것이다. 관계 데이터베이스의 관계 모델의 관계의 일종인 도출 관계에 해당한다. 여러 테이블(기본 관계) 또는 뷰의 데이터를 연결하여 조합할 수 있다. 보기에 표시되는 데이터의 선택 기준을 지정할 수도 있다.
(위키백과 정의)

뷰는 기본 테이블(table)과 같이 행(column)과 열(row)로 구성되지만, 다른 테이블에 있는 데이터를 보여줄 뿐이며, 실제 테이블과 달리 데이터 자체를 포함하고 있는 것은 아니다. 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블인 것처럼 볼 수 있다.

뷰 사용의 장점

장점설명
독립성- 테이블 구조가 변경되어도 뷰를 사용하는 응용프로글매은 변경하지 않아도 된다.
편리성- 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
보안성- 직원의 급여 정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.

뷰 사용 예시

SELECT * FROM INSERT_SUBQUERY_TEST;

DROP TABLE INSERT_SUBQUERY_TEST; 


CREATE VIEW V_TB_SAL_HIS_MAX_BY_EMP_NO
AS 
SELECT A.EMP_NO, A.EMP_NM, B.DEPT_CD, B.DEPT_NM
     , MAX(C.PAY_AMT) AS MAX_PAY_AMT 
  FROM TB_EMP A , TB_DEPT B, TB_SAL_HIS C 
 WHERE A.DEPT_CD = B.DEPT_CD 
   AND A.EMP_NO = C.EMP_NO 
GROUP BY A.EMP_NO, A.EMP_NM, B.DEPT_CD, B.DEPT_NM
;
SELECT * FROM V_TB_SAL_HIS_MAX_BY_EMP_NO;

DROP VIEW V_TB_SAL_HIS_MAX_BY_EMP_NO;

1.1 INLINE VIEW

SELECT A.*
  FROM
(
 SELECT
         NAME
       , CREDIT_LIMIT
    FROM CUSTOMERS
) A
;

SELECT문의 FROM절 내부에 있는 또다른 SELECT문을 INLINE VIEW라고 한다. 

SELECT A.*
  FROM
     (
       SELECT A.*
         FROM
            (
				SELECT
				       NAME
				     , CREDIT_LIMIT
				  FROM CUSTOMERS
     		) A
     ) A
;

인라인 뷰 내에 있는 SELECT문의 FROM절에 또 다른 인라인 뷰를 넣을 수 도 있다. (인라인 뷰 안에 인라인 뷰 가능)

1.2 VIEW

SELECT
       C.NAME AS CUSTOMER
     , TO_CHAR(A.ORDER_DATE, 'YYYY') AS YEAR
     , SUM( B.QUANTITY * B.UNIT_PRICE ) SALES_AMOUNT
  FROM ORDERS A
     , ORDER_ITEMS B
     , CUSTOMERS C
 WHERE 1=1
   AND A.STATUS = 'Shipped'
   AND A.ORDER_ID = B.ORDER_ID
   AND A.CUSTOMER_ID = C.CUSTOMER_ID
 GROUP BY C.NAME, TO_CHAR(A.ORDER_DATE, 'YYYY')
 ORDER BY C.NAME
;

이 SELECT 문은 발송된 주문에 대한 연도별 각 고객의 매출 총 금액을 구하는 SQL문

1.3 CREATE OR REPLACE VIEW

CREATE OR REPLACE VIEW CUSTOMER_SALES AS
SELECT
       C.NAME AS CUSTOMER
     , TO_CHAR(A.ORDER_DATE, 'YYYY') AS YEAR
     , SUM( B.QUANTITY * B.UNIT_PRICE ) SALES_AMOUNT
  FROM ORDERS A
     , ORDER_ITEMS B
     , CUSTOMERS C
 WHERE 1=1
   AND A.STATUS = 'Shipped'
   AND A.ORDER_ID = B.ORDER_ID
   AND A.CUSTOMER_ID = C.CUSTOMER_ID
 GROUP BY C.NAME, TO_CHAR(A.ORDER_DATE, 'YYYY')
 ORDER BY C.NAME;
SELECT * FROM CUSTOMER_SALES;

매번 2.1과 같이 SQL문을 작성해야한다면 시간이 많이 들고 번거로울 것이다.
CREATE OR REPLACE VIEW를 통해서 VIEW를 정의해두면, SELECT 문을 통하여 이를 조회할 수 있다. SELECT 문의로 CUSTOMER_SALES를 조회하면 아래와 같이 동일한 데이터를 확인할 수 있다. 다만 VIEW를 과도하게 사용할 경우, 성능의 저하가 있을 수 있으니 유의해야한다.

1.4 VIEW 활용

SELECT
       CUSTOMER
     , SALES_AMOUNT
  FROM
  CUSTOMER_SALES
WHERE YEAR = 2017
ORDER BY SALES_AMOUNT DESC;

고객별 2017년도 매출 구하기

SELECT
        CUSTOMER
      , SUM(SALES_AMOUNT) AS SUM_SALES_AMOUNT
  FROM CUSTOMER_SALES
GROUP BY CUSTOMER
ORDER BY SUM_SALES_AMOUNT DESC;

고객별 전체 매출 합계 구하기

2. 서브 쿼리

  • 하나의 SQL문에 포함되어 있는 또 다른 SQL문을 뜻한다.
  • 서브 쿼리는 메인 쿼리의 컬럼을 모두 사용할 수 있지만, 메인 쿼리는 서브 쿼리의 컬럼을 사용할 수 없다.

서브 쿼리 사용시 주의점

  1. 서브쿼리는 괄호로 감싸서 사용한다.

  2. 서브쿼리는 단일 행 또는 복수행 비교 연산자와 함께 사용 가능하다.

  3. 단일행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하이어야 하고, 복수행 비교 연산자는 서브 쿼리의 결과 건수와 상관없다.

  4. 서브쿼리에서 ORDER BY 를 사용하지 못한다. ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인 쿼리의 마지막 문장에 위치해야한다.


서브 쿼리가 사용 가능한 위치

  1. SELECT 절 - FROM 절 - WHERE 절 - HAVING 절 - ORDER BY 절

  2. INSERT문의 VALUES 절 - UPDATE문의 SET 절


동작 방식에 따른 서브 쿼리 분류

동작 방식설명
비연관 서브쿼리- 서브쿼리가 메인 쿼리의 컬럼을 가지고 있지 않는 형태
- 메인 쿼리에 값을 제공하기 위한 목적으로 주로 사용
연관 서브쿼리- 서브쿼리가 메인 쿼리의 컬럼을 가지고 있는 형태
- 일반적으로 메인 쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용한다.

반환 형태에 따른 서브 쿼리 분류

반환 형태설명
단일행 서브쿼리- 서브 쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미한다.
- 항상 비교 연산자와 함께 사용된다.
- ( +,<,<=,>,>=,<>)
다중행 서브쿼리- 서브쿼리의 실행 결과가 여러건인 서브쿼리를 의미한다.
- 다중행 서브 쿼리는 다중행 비교 연산자와 함께 사용된다.
- (IN, ALL, ANY, SOME, EXISTS)
다중 컬럼 서브 쿼리- 서브쿼리의 실행 결과로 여러 컬럼을 반환한다.
- 메인 쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있다.
- 서브쿼리와 메인 쿼리의 컬럼수와 컬럼순서가 동일해야한다.

2.1 서브쿼리 작성법

SELECT
       PRODUCT_ID
     , PRODUCT_NAME
     , LIST_PRICE
  FROM PRODUCTS
 WHERE LIST_PRICE = (
                   SELECT
                          MAX(LIST_PRICE)
                     FROM PRODUCTS 
                    );

LIST_PRICE의 조건이 서브 쿼리로 정의되어 있다.
서브 쿼리를 이용하여 단 한 개의 SQL문으로 출력을 완료

2.2 스칼라 서브 쿼리

SELECT
       A.PRODUCT_NAME
     , A.LIST_PRICE
     , ROUND( (SELECT AVG(K.LIST_PRICE) 
                 FROM PRODUCTS K 
               WHERE K.CATEGORY_ID = A.CATEGORY_ID 
            ), 2
       ) AVG_LIST_PRICE
  FROM PRODUCTS A
ORDER BY A.PRODUCT_NAME;

각 제품의 가격을 구하면서 해당 제품이 위치하고 있는 제품 카테고리의 평균 가격도 같이 구함
이러한 방식으로 SELECT절 내에 사용하는 서브 쿼리를 스칼라 서브쿼리라 함.

2.3 인라인 뷰 서브쿼리

SELECT ORDER_ID
     , ORDER_VALUE
  FROM
(
    SELECT ORDER_ID
         , SUM( QUANTITY * UNIT_PRICE ) ORDER_VALUE
      FROM ORDER_ITEMS
     GROUP BY ORDER_ID
     ORDER BY ORDER_VALUE DESC 
)
WHERE ROWNUM <= 10;

ORDER_VALUE는 제품의 단위 가격과 수량을 곱한 값
FROM 절에 사용되는 SELECT문을 인라인 뷰라고 한다.
인라인 뷰는 서브쿼리의 종류 중 하나이며, 인라인 뷰 서브쿼리라고도 부른다.

ORDER_ID 별로 GROUP BY 되어 있고, ORDER_VALUE 기준으로 내림차순 되어있다.

2.4 다양한 서브쿼리 예시 실습

단일행 서브쿼리

  • EMP_NO가 '1000000005'인 속한 팀의 팀원을 조회하는 SQL문 
SELECT A.EMP_NO, A.EMP_NM, A.DEPT_CD
FROM TB_EMP A 
WHERE A.DEPT_CD =
(
    SELECT DEPT_CD 
    FROM TB_EMP
    WHERE EMP_NO = '1000000005'
);

  • 2020년 5월 25일 기준으로 전체 평균 급여 이상의 급여를 받고 있는 직원들의 리스트 및 급여 내역을 조회하는 SQL문 
SELECT A.EMP_NO, B.EMP_NM, A.PAY_DE, A.PAY_AMT 
FROM SQLD.TB_SAL_HIS A, TB_EMP B 
WHERE A.PAY_DE = '20200525'
AND A.PAY_AMT >= 
(
SELECT AVG(K.PAY_AMT)
  FROM SQLD.TB_SAL_HIS K 
 WHERE K.PAY_DE = '20200525'
 )
 AND A.EMP_NO = B.EMP_NO;

다중행 서브쿼리

  • 한국데이터베이스 진흥원에서 발급한 자격증을 가지고 있는 직원의 사원번호 리스트와 그 수를 조회하는 SQL문
 SELECT A.EMP_NO, COUNT(*) CNT 
FROM TB_EMP_CERTI A 
WHERE A.CERTI_CD 
IN 
(
SELECT K.CERTI_CD 
FROM SQLD.TB_CERTI K 
WHERE K.ISSUE_INSTI_NM = '한국데이터베이스진흥원'
) 
GROUP BY A.EMP_NO
ORDER BY A.EMP_NO
;

  • 다중행 조건인데 아래와 같이 비교연산자 '='를 사용할경우 에러가 발생한다. 
SELECT A.EMP_NO, COUNT(*) CNT 
FROM TB_EMP_CERTI A 
WHERE A.CERTI_CD 
=
(
SELECT K.CERTI_CD 
FROM SQLD.TB_CERTI K 
WHERE K.ISSUE_INSTI_NM = '한국데이터베이스진흥원'
) 
GROUP BY A.EMP_NO
ORDER BY A.EMP_NO
;

다중컬럼 서브쿼리

  • 한 부서에 2명 이상 있는 부서중에서 각 부서의 생일 기준 최고참 사원을 출력하는 SQL문 
SELECT A.EMP_NO
     , A.EMP_NM
     , A.DEPT_CD
     , B.DEPT_NM
     , A.BIRTH_DE
  FROM TB_EMP A
     , TB_DEPT B 
WHERE (A.DEPT_CD, A.BIRTH_DE) IN 
(
    SELECT K.DEPT_CD, MIN(K.BIRTH_DE) AS MIN_BIRTH_DE 
    FROM TB_EMP K 
    GROUP BY K.DEPT_CD
    HAVING COUNT(*) > 1
) 
AND A.DEPT_CD = B.DEPT_CD
ORDER BY A.EMP_NO
;

EXIST문 서브 쿼리

  • 직원 주소지에 '강남' 있는 부서명과 부서이름 을 조회하는 SQL문 
SELECT A.DEPT_CD, A.DEPT_NM 
FROM TB_DEPT A
WHERE EXISTS( SELECT 1 FROM TB_EMP K WHERE K.DEPT_CD = A.DEPT_CD 
AND K.ADDR LIKE '%강남%')
;

스칼라 서브 쿼리

  • SELECT절 내에 사용하는 서브 쿼리를 스칼라 서브쿼리라 함.
  • 한국 데이터 베이스 진흥원에서 발급한 자격증을 가지고 있는 사원의 사원번호, 사원명, 자격증 번호, 자격증명을 조회 
SELECT A.EMP_NO
     , (SELECT L.EMP_NM FROM TB_EMP L WHERE L.EMP_NO = A.EMP_NO) AS EMP_NM
     , A.CERTI_CD
     , (SELECT L.CERTI_NM FROM TB_CERTI L WHERE L.CERTI_CD = A.CERTI_CD) AS CERTI_NM
  FROM TB_EMP_CERTI A 
 WHERE A.CERTI_CD 
IN 
(
SELECT K.CERTI_CD 
FROM SQLD.TB_CERTI K 
WHERE K.ISSUE_INSTI_NM = '한국데이터베이스진흥원'
) 
ORDER BY CERTI_NM
;

인라인뷰 서브쿼리

  • FROM 절에 사용되는 SELECT문을 인라인 뷰라고 한다.
    인라인 뷰는 서브쿼리의 종류 중 하나이며, 인라인 뷰 서브쿼리라고도 부른다.

  • 한국 데이터 베이스 진흥원에서 발급한 자격증을 가지고 있는 사원의 사원번호, 사원명, 자격증 번호, 자격증명을 조회 

SELECT B.EMP_NO
, (SELECT L.EMP_NM FROM TB_EMP L WHERE L.EMP_NO = B.EMP_NO) AS EMP_NM
, B.CERTI_CD
, (SELECT L.CERTI_NM FROM TB_CERTI L WHERE L.CERTI_CD = B.CERTI_CD) AS CERTI_NM
FROM 
(
SELECT K.CERTI_CD 
FROM SQLD.TB_CERTI K 
WHERE K.ISSUE_INSTI_NM = '한국데이터베이스진흥원'
) A 
, TB_EMP_CERTI B 
WHERE A.CERTI_CD = B.CERTI_CD
ORDER BY CERTI_NM
;

HAVING절 서브 쿼리

  • 부서번호 '10004'인 부서의 2020년 5월 25일 기준 평균 급여보다 평균 급여가 높은 부서 코드와 부서명을 조회하는 SQL 문 
SELECT B.DEPT_CD
     , (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = B.DEPT_CD) "부서명"
     , AVG(A.PAY_AMT) "평균급여"
 FROM SQLD.TB_SAL_HIS A, TB_EMP B 
WHERE A.PAY_DE = '20200525'
  AND A.EMP_NO = B.EMP_NO
GROUP BY B.DEPT_CD
HAVING AVG(A.PAY_AMT) >
(
    SELECT AVG(K.PAY_AMT)
      FROM SQLD.TB_SAL_HIS K, TB_EMP J
     WHERE K.PAY_DE = '20200525'
     AND K.EMP_NO = J.EMP_NO 
     AND J.DEPT_CD = '100004'
 )
ORDER BY "평균급여" DESC 
; 

-- 또다른 HAVING 절 서브 쿼리 
SELECT B.DEPT_CD
     , (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = B.DEPT_CD) "부서명"
     , AVG(A.PAY_AMT) "평균급여"
 FROM SQLD.TB_SAL_HIS A, TB_EMP B 
WHERE A.PAY_DE = '20200525'
  AND A.EMP_NO = B.EMP_NO
GROUP BY B.DEPT_CD
HAVING AVG(A.PAY_AMT) >
(
    SELECT AVG(K.PAY_AMT)
      FROM SQLD.TB_SAL_HIS K, TB_EMP J
     WHERE K.PAY_DE = '20200525'
     AND K.EMP_NO = J.EMP_NO 
     AND J.DEPT_CD = '100004'
 )
ORDER BY "평균급여" DESC 
;

UPDATE문 서브 쿼리

  • TB_EMP 테이블에 DEPT_NM 컬럼을 추가하고 UPDATE 문을 이용하여 DEPT_NM 컬럼의 값을 일괄적으로 UPDATE
  • TB_DEPT 테이블에서 DEPT_CD 코드가 같은 DEPT_NM을 TE_EMP 테이블의 DEPT_NM 컬럼값으로 업데이트 시킴 
ALTER TABLE TB_EMP ADD(DEPT_NM VARCHAR2(150)); 

UPDATE TB_EMP A
SET A.DEPT_NM = ( SELECT K.DEPT_NM FROM TB_DEPT K WHERE K.DEPT_CD = A.DEPT_CD)
; 

COMMIT;

SELECT EMP_NO, EMP_NM, DEPT_CD, DEPT_NM 
  FROM TB_EMP
  WHERE ROWNUM <= 10; 
  
  ALTER TABLE TB_EMP DROP COLUMN DEPT_NM;

INSERT문 서브 쿼리

  • INSERT_SUBQUERY_TEST 테이블을 만들고 ISERT 문을 통하여 값을 업데이트
  • TB_SAL_HIS 테이블로부터 EMP_NO가 100000001인 직원의 급여 최대값을 SELECT하여 INSERT_SUBQUERY_TEST 테이블의 MAX_PAY_AMT 값으로 업데이트 
  
  CREATE TABLE INSERT_SUBQUERY_TEST
(
     EMP_NO CHAR(10) 
  ,  MAX_PAY_AMT NUMBER(15) 
);

INSERT INTO INSERT_SUBQUERY_TEST
VALUES ('1000000001', (SELECT MAX(PAY_AMT) FROM TB_SAL_HIS WHERE EMP_NO = '1000000001'))
; 

COMMIT;

profile
멋쟁이펭귄맨
안녕하세요
이전 포스트

[SQL] SQL 기본 문법 정리 - INSERT, UPDATE, DELETE

다음 포스트

[SQL] 연습문제

0개의 댓글

관련 채용 정보