SQL 활용 실전문제 - 표준조인, 집합연산자, 계층형질의와 셀프조인, 서브쿼리, 그룹함수, 윈도우함수, DCL, 절차형 SQL

표준조인

순수관계연산자에 대해 서술하시오.

순수관계연산자는 관계형 데이터베이스에 적용할 수 있도록 개발된 관계연산자를 말합니다. 종류에는 SELECT, PROJECT, JOIN, DIVIDE 가 있습니다.

 

SELECT : 릴레이션에서 조건을 만족하는 튜플을 반환합니다. 실제 쿼리의 WHERE 절로 구현합니다.
PROJECT : 릴레이션에서 특정 칼럼으로 구성된 튜플을 반환합니다. 실제 쿼리의 SELECT 절로 구현합니다.
JOIN : 공통속성을 이용해 각 릴레이션의 특정 조건을 만족하는 튜플을 반들어 새로운 튜플을 반환합니다. 다양한 JOIN을 통해 구현합니다.
DIVIDE : 하나의 릴레이션과 관련있는 다른 릴레이션의 튜플을 반환합니다. 현제 실제 쿼리에선 사용되지 않습니다.

 

ANSI / ISO JOIN에서 표시하는 FROM절 정의형태에 대해 서술하시오

 

ANSI/ISO JOIN에서 사용하는 조인방식에는 INNER JOIN, NATURAL JOIN, CROSS JOIN, OUTER JOIN(LEFT,RIGHT,FULL) 이 있습니다. 조인조건은 ON 조건절에 표현하고 일반조건은 WHERE 절에 표현합니다.

 

-- 일반조인1

SELECT C.컨텐츠ID, C.컨텐츠명
FROM  고객 A, 추천컨텐츠 B, 컨텐츠 C
WHERE A.고객ID = :고객ID
AND A.고객ID  = B.고객ID
AND B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
AND B.컨텐츠ID = C.컨텐츠ID
AND B.컨텐츠ID NOT EXISTS (
    SELECT 컨텐츠ID
    FROM 비선호컨텐츠
    WHERE 고객ID = B.고객ID
    AND  컨텐츠ID = B.컨텐츠ID
)

-- ANSI/ISO 1
SELECT C.컨텐츠ID, C.컨텐츠명
FROM  고객 A 
INNER JOIN 추천컨텐츠 B ON (A.고객ID = :고객ID AND A.고객ID = B.고객ID) 
INNER JOIN 컨텐츠 C ON (B.컨텐츠ID = C.컨텐츠ID)
WHERE B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
AND B.컨텐츠ID NOT EXISTS (
    SELECT 컨텐츠ID
    FROM 비선호컨텐츠 X
    WHERE X.고객ID = B.고객ID
    AND X.컨텐츠ID = B.컨텐츠ID
)

-- 일반조인2

SELECT C.컨텐츠ID, C.컨텐츠명
FROM  고객 A, 추천컨텐츠 B, 컨텐츠 C, 비선호컨텐츠 D
WHERE A.고객ID = :고객ID
AND A.고객ID  = B.고객ID
AND B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
AND B.컨텐츠ID = C.컨텐츠ID
AND B.컨텐츠ID = D.컨텐츠ID(+)
AND D.컨텐츠ID IS NULL

-- ANSI/ISO 2
SELECT C.컨텐츠ID, C.컨텐츠명
FROM  고객 A 
INNER JOIN 추천컨텐츠 B 
ON (A.고객ID = B.고객ID) 
INNER JOIN 컨텐츠 C 
ON (B.컨텐츠ID = C.컨텐츠ID)
LEFT OUTER JOIN 비선호컨텐츠 D 
ON (D.고객ID = B.고객ID AND D.컨텐츠ID = B.컨텐츠ID)
WHERE A.고객ID = :고객ID 
AND B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
AND D.컨텐츠츠ID IS NULL

 

ANSI/ISO INNER JOIN에 대해 서술하시오.

 

INNER JOIN은 OUTER JOIN과 대비하여 내비 JOIN이라고 하며 조인조건 칼럼에 동일한 값이 있는 행만 반환합니다.

 

SELECT A.이름, A.고객등급
FROM 고객 A
INNER JOIN 구매정보 B
ON(A.고객번호 = B.고객번호)
GROUP BY A.이름, A.고객등급
HAVING COUNT(B.구매번호) >= 3

SELECT A.고객ID, A.고객명, SUM(C.단가 * B.사용량) AS 사용금액 
FROM 고객 A INNER JOIN 시간대별사용량 B
ON (A.고객ID = B.고객ID) INNER JOIN 시간대구간 C
ON (B.사용시간대  BETWEEN C.시작시간데 AND C.종료시간대)
GROUP BY A.고객ID, A.고객명
ORDER BY A.고객ID, A.고객명;

 

NATURAL 조인과 USING절을 이용한 JOIN 에대해 서술하시오.

 

NATURAL JOIN을 사용하면 공통된 이름을 같는 칼럼에 대한 EQUI 조인을 수행합니다. USING 조건절을 사용한 조인은 같은 이름을 가진 칼럼 중 특정 칼럼에 대해서만 선택적으로 EQUE 조인합니다. USING으로 지정한 칼럼은 ALIAS나 테이블 이름 같은 접두사를 붙일 수 없습니다.

 

CROSS JOIN에 대해 서술하시오

 

테이블간 JOIN 조건이 없는 경우, 모든 데이터를 조합합니다. 조인의 결과로 양쪽집합의 M*N 건 만큼의 레코드가 발생합니다.

 

SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;

 

LEFT OUTER JOIN 에 대해 서술하시오.

 

조인 수행시 먼저 표기된 좌측 테이블에 해당하는 레코드를 먼저 읽은 후, 위측 테이블에서 JOIN 대상을 찾습니다. 선행 테이블의 조인기준 칼럼과 값이 같은 후행 테이블의 조인기준 칼럼이 있을때 그 레코드를 가져옵니다. 값이 같은 후행테이블 칼럼이 없으면 후행 테이블에서 가져와야 하는 칼럼의 값을 null로 채웁니다.

 

SELECT
FROM 고객 A LEFT OUTER JOIN 단말 B
ON (A.고객번호 IN (11000,12000) AND A.단말기ID = B.단말기ID)
LEFT OUTER JOIN OS C
ON (B.OSID = C.OSID)
ORDER BY A.고객번호;

 

FULL OUTER JOIN 이 대해 서술하시오.

 

조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성합니다. RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일합니다.

 

SELECT A.ID, B.ID 
FROM TBL1 A FULL OUTER JOIN TBL2 B
ON  A.ID = B.ID

SELECT A.ID, B.ID
FROM TBL1 A LEFT OUTER JOIN TBL2 B
ON  A.ID = B.ID
UINON
SELECT 
FROM TBL1 A RIGHT OUTER JOIN TBL2 B
ON  A.ID = B.ID

SELECT A.ID, B.ID
FROM TBL1 A, TBL2 B
WHERE. A.ID = B.ID
UNION ALL
SELECT A.ID, NULL
FROM TBL1 A
WHERE NOT EXISTS(
  SELECT 1
  FROM TBL2 B
  WHERE A.ID = B.ID
)
UINON ALL
SELECT NULL, B.ID
FROM TBL2 B
WHERE NOT EXISTS(
  SELECT 1
  FROM TBL1 A
  WHERE A.ID = B.ID
)

 

집합연산자

 

집합연산자에 대해 서술하시오.

 

집합연산자의 종류는 크게 4가지가 있습니다.

 

- UNION : 여러개의 SQL문의 결과에 대한 합집합을 구하고 중복 행은 하나의 행으로 처리합니다.
- UNION ALL : 여러개의 SQL문의 결과에 대한 합집합을 구하고 중복 행은 그대로 표현합니다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과는 동일하지만 정렬 순서에는 차이가 있습니다.
- INTERSECT : 여러 개의 SQL 문의 결과에 대한 교집합입니다. 중복된 행은 하나의 행으로 만듭니다.
- EXCEPT : 앞의 SQL문의 결과에서 뒤 SQL 문의 결과에 대한 차집합입니다. 중복된 행은 하나의 행으로 만듭니다.

 

SELECT A,B
FROM TAB1
EXCEPT
SELECT A,B
FROM TAB2;

SELECT TAP1.A, TAB1.B
FROM TAB1
WHERE NOT EXISTS (
    SELECT 'X'
    FROM TAB2
    WHERE TAB1.A = TAB2.A
    AND TAB1.B = TAB2.B
)

-- 사용된 적이 있는 서비스를 도출하는 쿼리
-- INTERSECT : 교집합
SELECT A.서비스ID, B.서비스명, B.서비스uRL
FROM (
    SELECT 서비스ID
    FROM 서비스
    INTERSECT
        SELECT 서비스ID
        FROM 서비스이용
)A, 서비스 B
WHERE A.서비스ID = B.서비스ID

-- EXISTS
SELECT A.서비스ID, A.서비스명, A.서비스URL
FROM 서비스 A
WHERE EXISTS (
    SELECT 회원ID
       FROM 서비스이용 X
    WHERE X.서비스ID = A.서비스ID
)

-- UNION ALL
SELECT A.서비스ID, A.서비스명, A.서비스URL
FROM 서비스 A LEFT OUTER JOIN 서비스이용 B
ON(A.서비스ID = B.서비스ID)
WHERE B.서비스ID IS NOT NULL

SELECT A.서비스ID, A.서비스명, A.서비스URL
FROM 서비스 A
WHERE NOT EXISTS (
    SELECT 1
    FROM (
      SELECT 서비스ID
      FROM 서비스
          MINUS
              SELECT 서비스ID
              FROM 서비스이용
    ) Y
    WHERE X.서비스ID = Y.서비스ID

)

올바른 집합연산자 쿼리의 ORDER BY 위치에 대해 서술하시오

SELECT ...
FROM TABLE1
[WHERE]
[[GROUP BY ]]
[HAVING 그룹조건식]
집합연산자
SELECT ...
FROM TABLE2
[WHERE]
[[GROUP BY ]]
[HAVING 그룹조건식]
ORDER BY 1,2
[ASC OR DESC]

 

UNION ALL 의 ALIAS 정책에 대해 서술하시오.

 

UNION ALL을 사용하는 경우, 칼럼 ALIAS는 첫번째 SQL 모듈 기준으로 표시되며, 정렬 기준은 마지막 SQL 모듈에 표시하면 됩니다.

 

계층형질의에 대해 서술하시오.

 

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용합니다. 게층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말합니다.

 

계층형질의의 PRIOR에 대해 서술하시오.

 

계층형 칼럼을 연결하는 CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정합니다. PRIOR로 지정된 칼럼을 맞은편 칼럼이 찾아가는 구조입니다. 즉, PRIOR 자식칼럼 = 부모칼럼 형태가 되면 계층구조상의 부모칼럼이 자식칼럼을 찾아갑니다(순반향 전개). 계층형 탐색의 시작(START WITH) 은 계층구조 상 최상위 칼럼이어야 합니다. 반대로 자식칼럼 = PRIOR 부모칼럼이 되면 자식 칼럼이 부모칼럼을 찾아갑니다(역방향전개).계층형 탐색의 시작(START WITH) 은 계층구조 상 최하위 칼럼이어야 합니다. PRIOR 키워드는 SELECT, WHERE, CONNECT BY 절 모두에서 사용할 수 있습니다.

 

계층형질의의 START WITH에 대해 서술하시오

 

START WITH 절은 계층 구조 전개의 시작 위치를 지정하는 구문입니다. 즉, 루트 데이터를 지정합니다.

 

계층형 질의의 ORDER SIBLINGS BY 절에 대해 서술하시오.

 

형제 노드(동일한 LEVEL) 사이에서 정렬할 기준ㅇ르 정합니다. 루트노드의 LEVEL은 1입니다.

 

셀프조인에 대해 서술하시오

 

동일 테이블 사이의 조인을 말합니다. 따라서 FROM 절에 동일 테이블이 두 번이 상 나타납니다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 동일하기 때문에 식별을 위해 반드시 별칭을 사용합니다.

 

SELECT
FROM 일자별매출 A JOIN 일자별매출 B ON(A.일자 >= B.일자)
GROUP BY A.일자
ORDER BY A.일자;

 

서브쿼리의 종류에 대해 서술하시오

 

서브쿼리는 반환되는 데이터의 형태에 따라 분류할 수 있습니다.

 

- 단일 행 서브쿼리 : 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미합니다. 단일 행 비교연산자( =, <, <=, >, >=, <>) 와 함께 사용합니다.

- 다중 행 서브쿼리 : 서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미합니다. 다중 행 비교연산자 (IN, ALL, ANY, SOME, EXISTS) 와 함께 사용합니다.

- 다중 칼럼 서브쿼리 : 서브쿼리의 실행 결과러 여러 칼럼을 반환합니다. 메인쿼리의 조건절에 ㅇ러 칼럼을 동시에 비요할 수 있습니다. 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼의 수와 위치가 동일해야 합니다.

 

메인쿼리의 칼럼을 쿼리에 포함하고 있는지 여부에 따라 연관 서브쿼리와 비연관 서브쿼리로 구분할 수 있습니다. SQL_SERFVER에서는 여러 컬럼을 반환하는 다중 컬럼 서브쿼리를 제공하지 않습니다.

 

SELECT 이름
FROM 사원
WHERE NOT EXISTS (
    SELECT * FROM 가족 WHERE 사번 = 부양사번
)

SELECT 이름
FROM 사원
WHERE EXISTS (
    SELECT * FROM 가족 WHERE 사번 <> 부양사번
)

SELECT 이름
FROM 사원
WHERE NOT IN (
    SELECT 부양사번 FROM 가족
)

SELECT 이름
FROM 사원 LEFT OUTER JOIN
ON (사번 = 부양사번)
WHERE 부양사번 IS NULL

 

서브쿼리 사용시 주의사항에대해 서술하시오.

 

- 서브쿼리를 괄호로 감싸서 사용합니다.
- 서브쿼리는 단일행 또는 복수행 비교 연산자와 함께 사용 가능합니다. 단일 행 비교연산자는 서브쿼리의 결과가 반드시 1건 이하여야하고 복수행 비교 연산자는 건수와 상관없습니다.
- 서브쿼리안에서는 ORDER BY를 사용하지 못합니다. ORDER BY 절은 SELECT절에서 오직 한개만 올 수 있기 때문에 메인쿼리의 마지막 문장에 위치해야합니다.
- 다중 행 서비쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용할 수 있습니다. 반대의 경우 불가합니다.
- 비 연관 서브쿼리는 주로 메인 쿼리에 값을 제공하기 위한 목적으로 사용합니다.
- 메인 쿼리의 결과가 서브쿼리로 제공될 수도 있고, 서브쿼리의 결과가 메인쿼리로 제공될 수도 있습니다.

 

SELECT A.회원번호, A.회원명
FROM 회원 A, 동의항목 B
WHERE A.회원번호 = B.회원번호
GROUP BY A.회원번호, A.회원명
HAVING COUNT(CASE WHEN B.동의여부 = 'N' THEN 0 ELSE NULL END) > 1
ORDER BY A.회원번호


SELECT A.회원번호, A.회원명
FROM 회원 A
WHERE EXISTS (
    SELECT 1 
    FROM 동의항목 X
    WHERE X.동의여부 = 'N'
    AND X.회원번호 = A.회원번호
)

SELECT A.회원번호, A.회원명
FROM 회원 A, 동의항목 B
WHERE A.회원번호 = B.회원번호
AND B.동의여부 = 'N'
GROUP BY A.회원번호, A.회원명
ORDER BY A.회원번호

-- 2014년 10월 1일 이후 시작된 이벤트에 참여한 회원 중 메일발송이 하나라도 빠진 회원 
SELECT A.회원ID, A.회원명, A.이메일
FROM 회원 A
WHERE EXISTS (
    SELECT 'X'
    FROM 이벤트 B, 메일발송 C
    WHERE B.시작일자 >= '2014.10.01'
    AND B.이벤트ID = C.이벤트ID
    AND A.회원ID = C.회원ID
    HAVING COUNT(*) < (
        SELECT COUNT(*)
        FROM 이벤트
        WHERE 시작일자 >= '2014.10.01'
    )
)

-- 입사년도가 2014년이고 부서에 속해있는 직원의 사원번호, 사원명, 부서번호, 부서명, 부양가족수
SELECT B.사원번호, B.사원명, A.부서번호, A.부서명
, (SELECT COUNT(*) FROM 부양가족 Y WHERE Y.사원번호 = B.사원번호) AS 부양가족수
FROM 부서 A,(
  SELECT * 
  FROM 사원 
  WHERE 입사년도 = '2014'
) B
WHERE A.부서번호 = B.부서번호
AND EXISTS (
    SELECT 1
    FROM 사원 X
    WHERE  X.부서번호 = A.부서번호
);

 

서브쿼리 인라인 뷰에 대해 서술하시오.

 

FROM 절에서 사용되는 서브쿼리를 인라인뷰라고 합니다. 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것 처럼 사용할 수 있습니다. 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않습니다.

 

--평가대상상품에 대한 품질평가항목별 최종 평가결과 추출
SELECT A.상품ID, B,상품명, A.평가항목ID, C.평가항목ID, A.평가회차, A.평가등급, A.평가일자
FROM 평가결과 A, 품질평가항목 B, 평가대상상품 C
WHERE A.평가회차 = (
    SELECT MAX(평가회차)
    FROM 평가결과 X
    WHERE X.상품ID = A.상품ID
    AND X.평가항목ID = A.평가항목ID
)
AND A.상품ID = B.상품ID
AND A.상품ID = C.상품ID

-- 부서임시테이블에 있는 변경내역중 부서코드별 변경일자가 최근인 레코드만 업데이트하는 쿼리
UPDATE 부서 A SET 담당자 = (
  SELECT B.담당자
  FROM 부서임시 B
  WHERE B.부서코드 = A.부서코드
  AND B.변경일자 = (
    SELECT MAX(X.변경일자)
    FROM 부서임시 X
    WHERE X.부서코드 = A.부서코드
  )
)
WHERE 부서코드 IN (SELECT 부서코드 FROM 부서임시);

UPDATE 부서 A SET 담당자 = 
(
  SELECT 부서코드, MAX(변경일자)
  FROM 부서임시
  GROUP BY 부서코드
)B, 부서임시 C
WHERE B.부서코드 = C.부서코드
AND B.변경일자 = C.변경일자
AND A.부서코드 = C. 부서코드)
WHERE A.부서코드 IN (SELECT 부서코드 FROM 부서임시);

 

VIEW에 대해 서술하시오.

 

뷰는 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행합니다. 뷰의 장점으로는 테이블 구조가 변경되어도 뷰를 사용하는 프로그램은 변경하지 않아도 됩니다. 복잡한 질의를 뷰로 생성하여 질의를 단순하게 할 수 있습니다. 또한 자주 사용하는 형태의 SQL을 뷰로 만들어 편리하게 사용할 수 있습니다. 숨기고 싶은 정보가 있다면 해당 칼럼을 빼고 뷰를 생성할 수 있습니다.

 

CREATE VIEW V_TBL
AS
SELECT *
FROM TBL
WHERE C= 'B' OR C1 IS NULL`

 

ROLL UP GROUPING 함수에 대해 서술하시오.

 

계층 구조를 가진 SUB TOTAL을 생성하는 함수로 나열된 칼럼의 순서가 변경되면 수행결과도 바뀝니다. GROUP BY 구문과 함께 사용하여 그룹핑된 집합에 대해 좀 더 상세한 소계를 제공합니다.

 

SELECT
CASE WHEN GROUPING(A.서비스ID) = 0 THEN A.서비스ID ELSE '합계' AS 서비스ID
,CASE WHEN GROUPING(B.가입일자) = 0 THEN NVL(B.가입일자, '-') ELSE '소계' END AS 가입일자
,COUNT(B.회원번호) AS 가입건수 
FROM 서비스 A LEFT OUTER JOIN 서비스가입 B
ON ( A.서비스ID = B.서비스ID 
    AND B.가입일자 BETWEEN '2013-01-01' AND '2013-01-31'
)
GROUP BY ROLL UP (A.서비스ID, B.가입일자)


SELECT
CASE WHEN GROUPING(B.지역ID) = 0 THEN B.지역명 ELSE '지역전체' AS 지역명,
CASE WHEN GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM')) = 0 THEN TO_CHAR(A.이용일시, 'YYYY.MM') ELSE '월별합계' AS 이용월
FROM 이용내역 A INNER JOIN 지역 B
ON (A.지역ID = B.지역ID)
GROUP BY ROLL UP (B.지역ID, TO_CHAR(A.이용일시, 'YYYY.MM')

 

CUBE/GROUPING SETS에 대해 서술하시오.

 

CUBE 함수는 다차원적인 소계를 제공합니다. GROUP BY에 사용한 모든 칼럼에 대한 소그룹합계를 제공합니다. GROUPING SETS는 사용자가 소그룹집계할 칼럼을 직접 지정합니다. ROLLUP 함수는 인자로 주어진 칼럼의 순서에 따라 다른 결과를 추출합니다.

 

CUBE/ROLLUP/GROUPING SETS 모두 일반 그룹함수로 구현할 수 있습니다. 집계된 레코드에서 집계 대상 칼럼 이외의 GROUPING대상 칼럼의 값은 NULL을 반환합니다.

 

-- CUBE 함수 활용
SELECT A.설비ID, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY CUBE (A.설비ID, B.에너지코드)
ORDER BY A.설비ID, B.에너지코드

--GROUPING SET 활용
SELECT A.설비ID, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY GROUPING SETS ((A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드))
ORDER BY A.설비ID, B.에너지코드

 

GROUPING SETS에 대해 서술하시오.

 

다양한 소계 집합을 만들때 사용합니다. GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 때 사용하며 표시된 인수들 간에는 개층구조인 ROLLUP과 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같습니다. 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시되야합니다.

 

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS (월, 상품ID);

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS (월, 상품ID, ())

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS ((월, 상품ID))

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS ((월, 상품ID), 월)

 

윈도우 함수에 대해 서술하시오.

 

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수입니다.

 

그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER

그룹 내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (sql server는 OVER 절의 ORDER BY 지원 X)

그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클에서만 지원)

그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

선형 분석을 포함한 통계 분석 함수

 

윈도우 함수의 형태는 다음과 같습니다.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ; 

 

- WINDOW_FUNCTION : 윈도우 함수명
- ARGUMENTS(인수) : 함수에 따라 0 ~ N개의 인수가 지정됩니다.
- PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눕니다.
- ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 기술합니다.
- WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있습니다. SQL-SERVER에서는 지원하지 않습니다.

 

RANK WINDOW FUNCTION에 대해 서술하시오

 

ORDER BY를 포함한 QUERY 문에서 특정 항목에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순위를 부여합니다. 동일한 수만큼 비워두고 다음 값에 대한 순위를 매깁니다.

 

SELECT 고객번호, 고객명, 매출액, RANK() OVER(ORER BY 매출액 DESC) as 순위
FROM (
    SELECT A.고객번호, MAX(A.고객명) , SUM(B.매출액)
    FROM 고객 A INNER JOIN 월별매출 B
    ON (A.고객번호 = B.고객번호)
    GROUP BY  A.고객번호
)
ORDER BY RNK

 

DENSE RANK WINDOW FUNCTION에 대해 서술하시오

 

RANK WINDOW FUNCTION과 동일하지만 동일한 순위를 하나로 묶어 처리합니다. 동일한 값의 수에 상관없이 다음 값에는 다음 순위가 채번됩니다.

 

SELECT 고객ID, 게임상품ID, 활동점수, 순위
(
  SELECT DENSE_RANK() OVER(PARTITON BY 개임상품ID ORDER BY 활동점수 DESC) AS 순위, 고객ID, 게임상품ID, 활동점수
  FROM 고객활동
)
WHERE 순위 <= 10

 

ROW_NUMBER 함수에 대해 서술하시오.

 

동일한 값이라도 고유의 순위를 부여합니다.

 

SELECT
FROM (
    SELECT ... , ROW_NUMBER() OVER(PARTITION BY 추천경로 ORDER BY 추천점수 DESC) AS RNUM
    FROM 추천내역
)
WHER RNUM = 1

 

GROUP BY 절과 윈도우 함수의 관계에 대해 서술하시오.

 

GROUP BY 절과 WINDOW FUNCTION을 함께 사용하면 GROUPING 된 데이터를 원본데이터로 하여 WINDOW FUNCTION으로 집계합니다.
추가적으로 RANGE BETWEEN N PRECEDING AND N FOLLOWING 구문을 통해 집계범위를 새부적으로 지정할 수 있습니다.

 

LGA 함수에 대해 서술하시오

 

파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있습니다. 이후 몇 번째 행의 값을 가져오는 것은 LEAD 함수입니다.

 

DCL에 대해 서술하시오.

 

데이터 제어어는 DBMS에 생성된 User와 다양한 권한을 부여할때 쓰는 언어입니다. DBMS에서 발생하는 행위에 대한 권한을 ROLE이라고 하는데 이러한 ROLE을 DBMS USER에게 부여하기 위해서는 GRANT 명령어, 회수하기 위해서는 REVOKE 명령어를 사용합니다.

 

GRANT SELECT, UPDATE ON A.USER.TB_A TO B_USER;

-- TABLE R에 대한 SELECT, INSERT, DELETE 권한을 KIM에게 줍니다. KIM은 이 권한을 다른 사용자에게 GRANT 할 수 있습니다.
GRANT SELECT, INSERT, DELETE ON R TO KIM WITH GRANT OPTION

-- PARK에게 테이블 R에 대한 SELECT, INSERT, DELETE 권한을 줍니다.
GRANT SELECT, INSERT, DELETE ON R TO PARK;

-- KIM에게서 R에 대한 DELETE 권한을 회수합니다.
REVOKE DELETE ON R FROM KIM;

-- KIM에게서 R에 대한 INSERT 권한을 회수하고 KIM에게 이 권한을 받은 다른 사용자의 권한도 회수합니다.
REVOKE INSERT ON R FROM KIM CASCADE;

 

DBMS ROLE에 대해 서술하시오.

 

DBMS 사용자를 생성하면 기본적으로 많은 권한을 보여해야 합니다. 많은 DBMS에서 DBMS 관리자가 사용자별로 관한을 관리해야하는 부담과 복잡함을 줄이기 위해 다양한 권한ㅇ르 그룹으로 묶어 관리할 수 있도록 사용자과 관환 사이에 중개 역할을 수행합니다.

 

PL/SQL에 대해 서술하시오.

 

PROCEDURE, USER DEFINED FUNCTION, TRIGGER 객체를 PL/SQL로 생성할 수 있습니다. PROCEDURE 내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고 일반적인 SQL은 SQL 실행기가 처리합니다.

 

BLOCK 구조로 되어있어 각 기능별로 모듈화가 가능합니다. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환합니다. IF, LOOP등 절차형 언어를 사용해 절차적틴 프로그래밍이 가능합니다. DBMS 정의 에러나 사용자 정의 에러를 정의해 사용할 수 있습니다. ORACLE과 PL/SQL을 지원하는 어떤 서버로도 이식이가능합니다. 응용프로그램의 성능을 향상시킵니다. BLOCK 단위로 전송하므로 통신량을 줄일 수 있습니다.

 

PL/SQL로 작성된 PROCEDURE, USER DEFINED FUNCTION은 작성자 기준으로 트랜잭션을 분할할 수 있으며, 프로시저 내에서 다른 프로시저를 호출할 경우 호출 프로시저의 트랜잭션과는 별도로 PRAGMA AUTONOMOUS_TRANSACTION을 선언하여 자율 트랜잭션 처리 할수 있습니다.

 

변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 where절의 조건에 대입할 수 있습니다.

 

PL/SQL 내부에서 동적 SQL 또는 DDL 문장을 실행할때 사용하는 명령어에 대해 서술하시오

 

Execute immediate

절차형 SQL 모듈에 대해 서술하시오.

 

SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 컴포넌트 프로그램으로 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램입니다. PROCEDURE, USER DEFINED FUNCTION, TRIGGER가 있습니다.

 

저장형 프로시저는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합입니다. 저장형 함수는 단독적으로 실행되기보다는 다른 SQL 문을 통해 호출되고 결과를 리턴합니다.

 

트리거는 특정한 테이블에 INSERT UPDATE DELET 와 같은 dml이 수행되면 자동으로 동작합니다. 데이터의 무결성과 일관성을 위해 주로 사용합니다.

trigger에 대해 서술하시오.

trigger는 특정 dML이 수행되면 데이터베이스에 의해 자동으로 호출되고 수행됩니다. TCL을 이용해 commit 하거나 Rollback 하는 작업을 수행할 수 없습니다. 로그인하는 작업에도 정의할 수 있습니다.

 

트리거와 프로시저의 차이점에 대해 서술하시오

 

프로시저는 create procedure 트리거는 create trigger 명령어로 생성합니다. 프로시저는 execute 명령어리 실행하고 트리거는 특정 dml이 실행되면 자동으로 실행됩니다. 프로시저는 commit, rollback 이 가능하고 트리거는 불가하빈다.