SQLD 2과목 2장 정리

** SQL 자격검정 실전문제 내용입니다:)
** 예시 SQL문의 결과는 따로 적어두지 않았습니다.

---

2. SQL 기본 및 활용

2) SQL 활용

(1) 표준 조인

순수 관계 연산자

• SELECT 연산은 WHERE 절로 구현
• PROJECT 연산은 SELECT 절로 구현
• (NATURAL) JOIN 연산은 JOIN 절로 구현

INNER JOIN (2-66)

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 (B.고객ID = D.고객D AND B.컨텐츠ID = D.컨텐츠ID)
WHERE A.고객ID = #custId#
AND D.컨텐츠ID IS NULL;

CROSS JOIN (2-71)

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

OUTER JOIN(2-73)

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

SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A, 게시글 B
WHERE A.게시판ID = B.게시판ID(+)
AND B.삭제여부(+) = 'N' -- (+): ORACLE의 OUTER JOIN 구문
AND A.사용여부 = 'Y'
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID

(2) 집합 연산자

집합 연산자의 종류(2-79)

UNION -- 합집합, 중복행X
UNION ALL -- 합집합. 중복행O
INTERSECT -- 교집합. 중복행X
EXCEPT(= MINUS) -- 차집합. 중복행X

EXCEPT 쿼리문 비교(2-79)

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

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

(3) 계층형 질의와 셀프 조인

PRIOR & START WITH & ORDER SIBLINGS (2-89)

SELECT 사원번호, 사원명, 입사일자, 매니저사원번호
FROM 사원
START WITH 매니저사원번호 IS NULL -- 계층구조 전개의 시작위치
CONNECT BY PRIOR 사원번호 = 매니저사원번호 -- 자식 = 부모
AND 입사일자 BETWEEN '2013-01-01' AND '2013-12-31'
ORDER SIBLINGS BY 사원번호; -- 형제 노드 사이에서 정렬

셀프 조인 (2-93)

SELECT A.일자, SUM(B.매출액) AS 누적매출액
FROM 일자별매출 A JOIN 일자별매출 B ON (A.일자 >= B.일자) -- 같은 테이블을 조인
GROUP BY A.일자
ORDER BY A.일자;

(4) 서브쿼리

메인쿼리와 서브쿼리

• Single Row 서브쿼리 (단일 행 서브쿼리)
  • 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리
• Multi Row 서브쿼리 (다중 행 서브쿼리)
  • 서브쿼리의 실행 결과가 여러 건인 서브쿼리
• Multi Column 서브쿼리 (다중 칼럼 서브쿼리)
  • 서브쿼리의 실행 결과가 여러 칼럼인 서브쿼리

다중 행 서브쿼리

IN (서브쿼리) -- 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건
비교연산자 ALL (서브쿼리) -- 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건
비교연산자 ANY (서브쿼리) -- 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건
EXISTS (서브쿼리) -- 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건

다중 행 연관 서브쿼리, 단일 행 연관 서브쿼리 (2-100)

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.부서번호);

뷰 (2-104)

CREATE VIEW V_TBL -- 뷰 생성
AS
SELECT *
FROM TBL
WHERE C1 = 'B' OR C1 IS NULL

SELECT SUM(C2) C2 -- 뷰 조회
FROM V_TBL
WHERE C2 >= 200 AND C1 'B'

(5) 그룹 함수

ROLLUP 함수 (2-107)

SELECT 구매고객, 구매월, COUNT(*) "총 구매건", SUM(구매금액) "총 구매액"
FROM 구매이력
GROUP BY ROLLUP(구매고객, 구매월)

CUBE 함수 (2-109)

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 SETS 함수 (2-109)

SELECT A.설비ID, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY GROUPING SETS((A.설비IB), (B.에너지코드), (A.설비ID, B.에너지코드), ())
-- ROLLUP과 달리 평등한 관계

(6) 윈도우 함수

그룹 내 순위 함수 (2-113) (2-115)

-- RANK: 동일한 값에 동일 순위(1, 1, 3)/ DENSE_RANK: 동일한 값에 동일 순위, 공백X(1, 2, 3)
SELECT 고객번호, 고객명, 매출액, RANK() OVER(ORDER BY 매출액 DESC) AS 순위
FROM ( ... )
ORDER BY RNK;

-- ROW_NUMBER: 동일한 값에 고유 순위 부여
SELECT 추천경로, 추천인, 피추천인, 추천점수,
	   ROW_NUMBER() OVER(PARTITION BY 추천경로 ORDER BY 추천점수 DESC) AS RNUM
FROM 추천내역 -- 추천경로별 추천점수 순위

일반 집계 함수 (2-116) (2-117)

SELECT 상품분류코드,
	  ,AVG(상품가격) AS 상품가격
	  ,COUNT(*) OVER(ORDER BY AVG(상품가격)
                     RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품분류코드

SELECT Y.사원ID, Y.부서ID, Y.사원명, Y.연봉
FROM (SELECT 사원ID, MAX(연봉) OVER(PARTITION BY 부서ID) AS 최고연봉
      FROM 사원) X, 사원 Y
WHERE X.사원ID = Y.사원ID
AND X.최고연봉 = Y.연봉

(7) DCL

GRANT & REVOKE (2-122)

GRANT SELECT, INSERT, DELETE ON R TO Kim WITH GRANT OPTION;
GRANT SELECT, INSERT, DELETE ON R TO Park; -- Kim
REVOKE DELETE ON R FROM Kim;
REVOKE INSERT ON R FROM Kim CASCABE; -- Kim과 Park의 INSERT 권한 회수

(8) 절차형 SQL

PL/SQL의 특징

• Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다
• Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• 응용 프로그램의 성능을 향상시킨다
• 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

프로시저 (2-124)

create or replace procedure insert_dept authid current_user as
begin
  execute immediate 'TRUNCATE TABLE DEPT';
  INSERT /*+ APPEND */ INTO DEPT (DEPTNO, DNAME, LOC)
  SELECT DEPTNO, DNAME, LOC
  FROM TMP_DEPT;
  commit;
end;