자격증/SQLD

[SQLD] 2과목 SQL 활용 정리 + 오답노트

브디크리 2023. 9. 7. 15:55

서론

SQLD 2과목 SQL 활용 정리 및 오답노트 (헷갈리는 부분) 정리

70번 : INNER JOIN 

#USING 사용 올바른 방법
SELECT *
FROM TBL1 A INNER JOIN TBL2 B
USING (COL1); #TBL1, TBL2에 공통적으로 존재하는 칼럼명 그 자체만 사용가능

#ERROR
SELECT *
FROM TBL1 A INNER JOIN TBL2 B
USING (A.COL1 = B.COL1); #ALIAS사용 시 에러발생

#ON 사용
SELECT *
FROM TBL1 A INNER JOIN TBL2 B
ON A.COL1 = B.COL1; #ALIAS사용 가능

73번 : FULL OUTER JOIN

  • FULL OUTER JOIN 결과가 같은 것
#FULL OUTER JOIN
SELECT A.ID, B.ID
FROM TBL1 A FULL OUTER JOIN TBL2 B
ON A.ID = B.ID;

#(LEFT OUTER JOIN) UNION (RIGHT OUTER JOIN)
SELECT A.ID, B.ID
FROM TBL1 A LEFT OUTER JOIN TBL2 B
ON A.ID = B.ID
UNION #LEFT UNION RIGHT = FULL OUTER JOIN
SELECT A.ID, B.ID
FROM TBL1 A RIGHT OUTER JOIN TBL2 B
ON A.ID = B.ID;

#ETC)
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)
UNION ALL
SELECT NULL, B.ID
FROM TBL2 B
WHERE NOT EXISTS 
(SELECT 1 FROM TBL1 A WHERE B.ID = A.ID);

77번 : 오라클 OUTER JOIN (+) 문법

#오라클에서 (+)를 사용한 OUTER JOIN문법
SELECT A.게시판 ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A, 게시글 B
WHERE A.게시판ID = B.게시판ID(+)
AND B.삭제여부 = 'N'
AND A.사용여부 = 'Y'
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID;

#ANSI표준으로 작성한 문법
SELECT A.게시판 ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A LEFT JOIN 게시글 B
ON (A.게시판ID = B.게시판ID AND B.삭제여부 = 'N')
WHERE A.사용여부 = 'Y'
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID;

82번 : ORDER BY 1, 2

#SELECT절에 작성한 칼럼으로 정렬하고 싶을 때
SELECT COL1 C1, COL2 C2
FROM TBL1
UNION ALL
SELECT COL1 C3, COL2 C4
FROM TBL2
ORDER BY 1, 2;

#ERROR
#SELECT절에 칼럼을 2개 적고, 3이상을 사용하면 오류
#테이블에 칼럼이 3개 이상 있더라도 SELECT절에 작성한 개수가 최대임.
SELECT COL1 C1, COL2 C2
FROM TBL1
ORDER BY 1, 2, 3;

※ 같은 칼럼명에 다른 ALIAS를 주었을 때에는 첫번째 SQL모듈의 ALIAS를 출력해준다.

위의 SQL예시)

C1(SELECT절에 작성한 첫번째 칼럼)을 기준으로 오름차순 정렬,

C1이 같은 경우에는 C2 (SELECT절에 작성한 두번째 칼럼)을 기준으로 오름차순 정렬

C1 C2
A A
A Z
B Z

86번 : 1:1 양쪽 필수 관계

  • 1:1 양쪽 필수 관계를 그림으로 나타내보면, 오른쪽과 같다. (왼쪽은 우리가 흔히 보던 모습)
  • 따라서, 아래와 같은 결과를 얻을 수 있다.
  • C EXCEPT (= MINUS) D = 공집합
  • C UNION ALL B 의 개수 = C 혹은 D 레코드의 개수의 2배
  • C UNION D == C INNER JOIN D

 

89번 : START WITH ~ CONNECT BY (조건문 / 계층형 질의) 

#예시
SELECT *
FROM TBL
START WITH COL1 IS NULL
CONNECT BY PRIOR COL2 = COL1
AND COL3 BETWEEN '2023-01-01' AND '2023-01-31'
ORDER SIBLING BY COL2;
  • START WITH의 COL1 IS NULL 조건을 만족하는 레코드는
    CONNECT BY 이후 조건절에 해당되지 않더라도 출력된다.
  • START WITH 절에서 필터링된 시작 데이터는 결과목록에 포함되기 때문이다.

95번 : 서브쿼리 개념

  • 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용할 수 있다.
  • 서브쿼리는 SELECT, FROM , HAVING, ORDER BY절 등에서 사용 가능하다.
  • 연관 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브 쿼리이다.
  • 다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인 쿼리의 조건과 동시에 비교되는 것을 의미
    (SQL Server는 더 이상 지원하지 않는다.)

99번 : 연관, 비연관 서브쿼리

  • 연관 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브 쿼리이다.
    (메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 맞는지 확인하고자할때 주로 이용)

  • 비연관 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태의 서브 쿼리
    (메인쿼리에 값을 제공하기 위한 목적으로 주로 사용)

110번 : GROUPING ()

  • GROUPING(칼럼) = 칼럼이 집계된 값이면 1, 아니면 0을 반환
SELECT 
CASE WHEN GROUPING(자재번호) = 1 THEN '자재전체' ELSE 자재번호 END AS 자재번호,
CASE WHEN GROUPING(발주처ID) = 1 THEN '발주처전체' ELSE 발주처ID END AS 발주처ID,
CASE WHEN GROUPING(자재번호) = 1 THEN '발주일자전체' ELSE 발주일자 END AS 발주일자,
SUM(발주수량) AS 발주수량합계
FROM 자재발주
GROUP BY GROUPING SETS(자재번호, (발주처ID, 발주일자));
  • GROUPING SETS에 의해 소계된 칼럼이면 '자재전체'를 출력,
    소계되지 않은 칼럼이라면 해당 칼럼의 자재번호를 출력
자재번호 발주처ID  발주일자 발주수량 =>  자재번호 발주처ID 발주일자 발주수량합계
1 001 20150102 100 1 발주처전체 발주일자 전체 300
1 001 20150103 200 2 발주처전체 발주일자 전체 300
2 001 20150102 200 3 발주처전체 발주일자 전체 300
2 002 20150102 100 자재전체 001 20150102 300
3 001 20150103 100 자재전체 001 20150103 300
3 002 20150103 200 자재전체 002 20150102 100
  자재전체 002 20150103 200

123번 : PL/SQL

  • PL/SQL로 작성된 프로시저, 사용자 정의함수는 작성자의 기준으로 트랜잭션을 분할할 수 있다.
  • 프로시저 내에서 다른 프로시저를 호출한 경우, 호출 프로시저의 트랜잭션 과는 별도로 
    PRAGMA AUTONAMOUS_TRANSACTION을 선언하여 자율 트랜잭션을 처리할 수 있다.
    (즉, 무조건 전체가 하나의 트랜잭션으로 처리되는 것이 아니다.)
  • 변수와 상수등을 사용하여 일반 SQL문장을 실행할 때 WHERE절의 조건 등으로 대입 가능
  • 프로시저 내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고 일반적인 SQL문장은 SQL 실행기가 처리한다.

124번 : PL/SQL 내부에서 DDL 수행

  • PL/SQL 내부에서 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용하여야 한다.
#PL/SQL
CREATE OR REPLACE PROCEDURE INSERT_DEPT AUTHID CURRENT_USER AS
BEGIN
    #DDL 수행
    EXECUTE IMMEDIATE 'TRUNCATE TABLE DEPT';
    INSERT /*+ APPEND */ INTO DEPT (DEPTNO, DNAME, LOC)
    SELECT DEPTNO, DNAME, LOC
    FROM TMP_DEPT;
    COMMIT;
END;
/

125번 : 절차형 SQL 모듈

저장 모듈 (Stored Module)

  • SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든
    일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한
    실행 프로그램이다.
    (오라클에는 Procedure, User Defined Funcion, Trigger가 있다.)
  • 저장형 프로시저
    : SQL을 로직과 함께 데이터베이스 내에 저장해놓은 명령문의 집합

  • 저장형 함수 (사용자 정의 함수)는 단독적으로 실행되기 보단 다른 SQL문장을 통하여 호출되고 그 결과를 리턴하는
    SQL의 보조적인 역할을 한다.

  • 트리거는 데이터의 무결성과 일관성을 위해 사용한다.

126번 : 프로시저 VS 트리거

프로시저 트리거
CREATE Procedure 문법 사용 CREATE Trigger 문법 사용
EXECUTE 명령어로 실행 생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능 COMMIT, ROLLBACK 실행 불가능
저작자표시 비영리 변경금지 (새창열림)