Home 실전문제) 2과목 2장 - SQL 기본 Part. 1
Post
Cancel

실전문제) 2과목 2장 - SQL 기본 Part. 1

Posted Aug 27, 2023
17 min read

69번 - INNER JOIN& ON

문제에서 제시된 데이터 모델에 따라 작성한 SQL은 다음과 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 시간대구간
alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS';
/*
    PK) 시작시간대, 종료시간대
    NO) 단가(분/min 기준)
*/
CREATE TABLE TIME_INTERVAL (
INIT_TIME   DATE,
FINAL_TIME  DATE,
UNIT_PRICE  NUMBER(10),
CONSTRAINT INIT_TO_FINAL_PK PRIMARY KEY(INIT_TIME, FINAL_TIME)
);

-- 시간대별 사용량
/*
    PK) [FK]고객ID, 사용시간대
    NO) 사용량(분/min 기준)
 */
CREATE TABLE USAGE_BY_TIME (
CUSTOMER_ID             VARCHAR2(10),
USAGE_TIME_INTERVAL     DATE,
AMOUNT_TIME             NUMBER(10),
CONSTRAINT CSTM_ID_FK FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID),
CONSTRAINT ID_AND_USAGE_TIME_PK PRIMARY KEY(CUSTOMER_ID, USAGE_TIME_INTERVAL)
);

-- 고객
/*
    PK) 고객ID
    NO) 고객명, 생년월인
 */
CREATE TABLE CUSTOMER (
CUSTOMER_ID     VARCHAR2(10) PRIMARY KEY,
NAME            VARCHAR2(10),
BIRTH           DATE
);

그리고 다음과 같이 칼럼 값들을 입력하였다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

INSERT INTO TIME_INTERVAL VALUES (TO_DATE('2023/03/10 10:00:00', 'YYYY/MM/DD HH24/MI/SS'), TO_DATE('2023/03/10 11:00:00', 'YYYY/MM/DD HH24/MI/SS'), 100);
INSERT INTO TIME_INTERVAL VALUES (TO_DATE('2023/03/10 11:00:00', 'YYYY/MM/DD HH24/MI/SS'), TO_DATE('2023/03/10 12:00:00', 'YYYY/MM/DD HH24/MI/SS'), 200);
INSERT INTO TIME_INTERVAL VALUES (TO_DATE('2023/03/10 12:00:00', 'YYYY/MM/DD HH24/MI/SS'), TO_DATE('2023/03/10 13:00:00', 'YYYY/MM/DD HH24/MI/SS'), 300);
INSERT INTO TIME_INTERVAL VALUES (TO_DATE('2023/03/10 13:00:00', 'YYYY/MM/DD HH24/MI/SS'), TO_DATE('2023/03/10 14:00:00', 'YYYY/MM/DD HH24/MI/SS'), 350);
INSERT INTO TIME_INTERVAL VALUES (TO_DATE('2023/03/10 14:00:00', 'YYYY/MM/DD HH24/MI/SS'), TO_DATE('2023/03/10 15:00:00', 'YYYY/MM/DD HH24/MI/SS'), 350);




INSERT INTO USAGE_BY_TIME VALUES ('1', TO_DATE('2023/03/10 10:30:00', 'YYYY/MM/DD HH24/MI/SS'), 20);
INSERT INTO USAGE_BY_TIME VALUES ('1', TO_DATE('2023/03/10 11:30:00', 'YYYY/MM/DD HH24/MI/SS'), 25);
INSERT INTO USAGE_BY_TIME VALUES ('1', TO_DATE('2023/03/10 12:30:00', 'YYYY/MM/DD HH24/MI/SS'), 25);
INSERT INTO USAGE_BY_TIME VALUES ('2', TO_DATE('2023/03/10 11:25:00', 'YYYY/MM/DD HH24/MI/SS'), 10);
INSERT INTO USAGE_BY_TIME VALUES ('2', TO_DATE('2023/03/10 12:30:00', 'YYYY/MM/DD HH24/MI/SS'), 25);
INSERT INTO USAGE_BY_TIME VALUES ('2', TO_DATE('2023/03/10 13:10:00', 'YYYY/MM/DD HH24/MI/SS'), 25);
INSERT INTO USAGE_BY_TIME VALUES ('2', TO_DATE('2023/03/10 14:30:00', 'YYYY/MM/DD HH24/MI/SS'), 25);
INSERT INTO USAGE_BY_TIME VALUES ('3', TO_DATE('2023/03/10 10:15:00', 'YYYY/MM/DD HH24/MI/SS'), 25);
INSERT INTO USAGE_BY_TIME VALUES ('3', TO_DATE('2023/03/10 11:20:00', 'YYYY/MM/DD HH24/MI/SS'), 35);
INSERT INTO USAGE_BY_TIME VALUES ('3', TO_DATE('2023/03/10 12:10:00', 'YYYY/MM/DD HH24/MI/SS'), 25);



INSERT INTO CUSTOMER VALUES ('1', 'KIM', TO_DATE('1999/04/28', 'YYYY/MM/DD'));
INSERT INTO CUSTOMER VALUES ('2', 'LEE', TO_DATE('1999/03/28', 'YYYY/MM/DD'));
INSERT INTO CUSTOMER VALUES ('3', 'PARK', TO_DATE('2000/02/28', 'YYYY/MM/DD'));

적절한 선지인 ③번의 SQL을 작성하여 실행하면 다음과 같다.

①번이 틀린 이유

1
2

ON  (B.사용시간대 <= C.시작시간대 
    AND B.사용시간대 >= C.종료시간대)

맥락상, 사용시간대는
시작시간대와 종료시간대 사이에 있어야 한다.
따라서 해당 부등호는 반대로 작성되었다.

②번이 틀린 이유

1
2
3
4

FROM 고객 A INNER JOIN 시간대별사용량 B
INNER JOIN 시간대구간 C
ON  (A.고객ID = B.고객ID AND B.사용시간대
    BETWEEN C.시작시간대 AND C.종료시간대)

INNER JOIN 이후 ON에 작성된 조건에서
AND의 우선순위로 인하여 오류가 발생한다.

④번이 틀린 이유

BETWEEN JOIN


70번 - JOIN에서 USING 사용

JOIN 이후에 조건을 명시할 때는
ON을 쓸 수도 있지만 USING을 쓸 수도 있다.
그러나, 이때 USING을 쓸 때는 다음과 같이 소괄호가 강제된다.

1
2

FROM    TEAM INNER JOIN STADIUM
USING   (STADIUM_ID)

또한, ON은 JOIN 대상인 두 테이블의 공통 칼럼을 각각 써야 하지만
USING은 공통 칼럼만 소괄호 안에 작성하면 된다.


72번 - 선지별 SQL 문장 결과

OUTER JOIN에 대하여

- LEFT OUTER JOIN

1
2
3
4

~~~~~
FROM [테이블명1] LEFT OUTER JOIN [테이블명2]
ON  (두 테이블 사이의 특정 조건)
~~~~~

좌측 테이블의 데이터는 모두 가져옴.
But, 우측 테이블 데이터는 좌측에 대응 안 되는 건   => NULL 값으로 대응시켜놓음.

- RIGHT OUTER JOIN
우측은 LEFT~의 반대 개념으로 이해.

- FULL OUTER JOIN
양쪽 다
==> RIGHT OUTER와 LEFT OUTER의 각 결과를 합집합한 것!!

1
2
3
4
5
6
7
8
9
10
11

SELECT  A.CUSTOMER_ID,
        A.CUSTOMER_NM,
        B.DEVICE_ID,
        B.DEVICE_NM,
        C.OSID,
        C.OS_NM
FROM CUSTOMER A LEFT OUTER JOIN DEVICE B
ON   (A.CUSTOMER_ID IN (11000, 12000)
    AND A.DEVICE_ID = B.DEVICE_ID) LEFT OUTER JOIN OS_INFO C
ON   (B.OSID = C.OSID)
ORDER BY A.CUSTOMER_ID;

상기와 같이 제시된 SQL문을 위하여
아래의 코드로 테이블 생성 및 삽입을 하였다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE TABLE OS_INFO (
OSID    NUMBER(6) PRIMARY KEY,
OS_NM   VARCHAR2(15)
);

CREATE TABLE DEVICE (
DEVICE_ID   NUMBER(6) PRIMARY KEY,
DEVICE_NM   VARCHAR2(15),
OSID        NUMBER(6),
CONSTRAINT OSID_FK FOREIGN KEY(OSID) REFERENCES OS_INFO(OSID)
);

CREATE TABLE CUSTOMER (
CUSTOMER_ID NUMBER(7) PRIMARY KEY,
CUSTOMER_NM VARCHAR2(10),
DEVICE_ID   NUMBER(6),
CONSTRAINT DEVICE_ID_FK FOREIGN KEY(DEVICE_ID) REFERENCES DEVICE(DEVICE_ID)
);
DROP TABLE CUSTOMER;

INSERT INTO OS_INFO VALUES(100, 'Android');
INSERT INTO OS_INFO VALUES(200, 'iOS');
INSERT INTO OS_INFO VALUES(300, 'Bada');

INSERT INTO DEVICE VALUES(1000, 'A1000', 100);
INSERT INTO DEVICE VALUES(2000, 'B2000', 100);
INSERT INTO DEVICE VALUES(3000, 'C3000', 200);
INSERT INTO DEVICE VALUES(4000, 'D3000', 300);

INSERT INTO CUSTOMER VALUES(11000, '홍길동', 1000);
INSERT INTO CUSTOMER VALUES(12000, '강감찬', NULL);
INSERT INTO CUSTOMER VALUES(13000, '이순신', NULL);
INSERT INTO CUSTOMER VALUES(14000, '안중근', 3000);
INSERT INTO CUSTOMER VALUES(15000, '고길동', 4000);
INSERT INTO CUSTOMER VALUES(16000, '이대로', 4000);

이 결과는 앞서 알아본 세 개의 JOIN들 중
LEFT~의 부분의 개념과 일치함을 알 수 있다.


73번 - UNION과 UNION ALL, 그리고 EXCEPT

- UNION

: 여러 개의 SQL문의 결과에 대한 합집합으로
  결과에서 모든 중복된 행은 하나의 행으로 만들어짐.

- UNION ALL

: 여러 개의 SQL문의 결과에 대한 합집합으로
  중복된 행도 그대로 결과로 표시

 일반적으로,
여러 질의 결과가 상호배타적Exclusive일 때 사용.

- EXCEPT

: 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합.
  중복된 행은 하나의 행으로 만듦


79번 - EXCEPT와 NOT IN

1
2
3
4
5

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

에서 EXCEPT~TAB2; 사이의 코드와
다음의 ④번 선지 코드의 일부를 주목하기.

1
2
3
4
5
6

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

당연히 완전 동일하다고 할 순 없지만,
(왜냐면, 반환하는 데이터 형태부터 다름.)
최소한 이 문제에서는 서로 동일한 결과가 나오게 한다.


80번 - 집합 함수 문제

1
2
3
4
5
6
7
8

SELECT A.서비스ID, B.서비스명, B.서비스URL
FROM   (SELECT 서비스ID
        FROM   서비스
            INTERSECT
        SELECT 서비스ID
        FROM   서비스이용) A,
        서비스 B
WHERE   A.서비스ID = B.서비스ID;

문제에서 제시된 SQL문은 즉,
서비스와 서비스이용 사이에서
교집합이 성립하는 서비스ID 칼럼을 중복없이 출력하는 것.

그리고

②번 선지에서

1
2
3
4
5
6
7
8

~~~~~
NOT EXISTS  SELECT 1
            FROM (SELECT 서비스ID
                  FROM   서비스
                  MINUS         
                  SELECT 서비스ID
                  FROM   서비스이용) Y
~~~~~

일단 FROM 절 안의 것을 주목해보면,

1
2
3
4
5

FROM (SELECT 서비스ID
        FROM   서비스
        MINUS         
        SELECT 서비스ID
        FROM   서비스이용) Y

소괄호 안의 것이 도출하는 것은
서비스의 진부분집합이 되는 서비스ID이다.
즉, 서비스이용의 서비스ID와 겹치지 않는다.

그리고, 위의 FROM 절은
NOT EXISTS (SELECT~로 둘러싸여 있는데
NOT~Y.서비스ID); 사이의 문장이 도출하는 것은 결국
서비스의 진부분집합 서비스ID가 아닌 집합의 데이터이다.
간단히 말해, 서비스이용.서비스ID 집합 전체이다.

또한, 맨 마지막 WHERE 절에서의 조건과
맨 첫 번째의 SELECT를 고려하면
서비스의 서비스ID를 출력하게 되므로
서비스와 서비스ID의 중복없는 교집합 데이터를 도출한다.


82번 - UNION 사용 시 주의사항, 그리고 ORDER BY

UNION 사용 시 주의사항

칼럼명 표출 기준

1
2
3
4
5

SELECT  1 AS A, 2 AS B
FROM    DUAL
UNION
SELECT  3 AS C, 4 AS D
FROM    DUAL;

1
2
3
4
5

SELECT 3 AS C, 4 AS D
FROM DUAL
UNION
SELECT 1 AS A, 2 AS B
FROM DUAL;

위 두 코드의 결과는 각각 다음과 같다.


ORDER BY

ORDER BY [숫자], [숫자], ...로 정렬 가능함!!
예를 들어

1
2
3

SELECT  4 AS A, 8 AS B
FROM    DUAL
ORDER BY 1, 2;

이런 식으로!


87번 - 계층형 질의Hierarchical Query

계층형 데이터
: 동일 테이블에 계층적으로 상위와 하위 데이터가 데이터.

계층형 질의 구문

1
2
3
4
5
6

SELECT  [~~~]
FROM    [테이블]
WHERE   [조건1] AND [조건2] ...
START WITH [조건]
CONNECT BY [NOCYCLE]/[PRIOR] [조건3] AND [조건4] ...
[ORDER SIBLINGS BY [칼럼1], [칼럼2], ...]

PRIOR
: CONNECT BY 절에 사용됨.
  현재 읽은 칼럼을 지정함.

1

CONNECT BY PRIOR [자식] = [부모]

==> 부모 데이터 to 자식 데이터 방향

1

CONNECT BY PRIOR [부모] = [자식]

==> 자식 데이터 to 부모 데이터 방향

문제 풀이

1
2

START WITH C2 IS NULL
CONNECT BY PRIOR C1 = C2

이므로, C2 칼럼의 NULL 값부터 시작하고,
C2(부모) -> C1(자식)의 방향으로 진행한다.

진행 순서를 정리하면 아래와 같다.
구분을 위해 대응되는 C3 값과 단계Level를 병기한다.
C2(부모) -> C1(자식) : A, 1Lv
C1(부모) -> C2(자식) : C, 2Lv
C1(부모) -> C3(자식) : B, 2Lv
C2(부모) -> C1(자식) : D, 3Lv

따라서, A, C, B, D 순서이다.


89번 - 계층형 질의 문제 2

CONNECT BY의 조건 적용은 WHERE과는 다르다!!!

1
2
3
4
5
6
7

SELECT  사원번호, 사원명,
        입사일자, 매니저사원번호
FROM    사원
START WITH 매니저사원번호 IS NULL
CONNECT BY PRIOR 사원번호 = 매니저사원번호
AND 입사일자 BETWEEN '2013-01-01' AND '2013-12-31'
ORDER SIBLINGS BY 사원번호;

위의 SQL문에서,
언뜻 보면 CONNECT BY의 입사일자 조건에 대해
사원명이 홍길동인 행은 해당이 안된다고 생각할 수 있다.
그러나, 해당 행이 START WITH의 조건에 따라 시작되므로 무관하다.


93번 - 윈도우 함수Window Function란?

  행과 행 사이의 관계를 쉽계 정의하기 만든 함수.

윈도우 함수 종류

  1. 순위Rank 관련

: RANK, DENSE_RANK, ROW_NUMBER 등

  2. 집계Aggregate 관련

: SUM, MAX, MIN, AVG, COUNT 등

  3. 행 순서 관련

: FIRST_VALUE, LAST_VALUE, LAG, LEAD 등

  4. 그룹 내 비율 관련

: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 등

  5. 통계 분석 관련

: CORR, COVAR_POP, STDDEV 등

구문

  OVER 문구가 키워드로 필수 포함!!

1
2
3

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


95번 - 서브쿼리 설명

  • 서브쿼리의 결과가 복수 행 결과 반환하는 경우엔
    IN, ALL, ANY 등의 복수 행 비교 연산자와 같이 사용!!

  • 다중 칼럼 서브쿼리의 결과로 여러 개의 칼럼 반환되어
    메인 쿼리의 조건과 비교가 됨.
    이건 SQL Server는 지원 X.


99번 - 서브쿼리 설명 2

  • 서브쿼리 결과가 2건 이상 반환 가능성 있다면
    ==> 다중 행 비교 연산자

  • 연관 서브쿼리Correlated Subquery
    : 서브쿼리 내에 메인쿼리 칼럼 사용된 서브쿼리.
    e.g. - 선수 자신이 속한 팀의 평균 키보다 작은 선수들 정보 출력

  • EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용됨.

 ★ EXISTS 서브쿼리는
조건 만족하는 1건만 찾으면 추가 검색 진행 X


100번 - 서브쿼리 설명 3

  • 스칼라 서브쿼리Scalar Subquery
    : 한 행, 한 칼럼만을 반환하는 서브쿼리 e.g. - 선수 정보와 해당 선수가 속한 팀의 평균 키 함께 출력
        ==> 평균 키를 구하는 부분에 이용

  • 인라인 뷰Inline View
    : FROM 절에서 사용되는 서브쿼리
    ==> 인라인 뷰는 동적 뷰Dynamic View


101번 - 서브쿼리; 인라인 뷰

①

MAX(평가회차)를 구하는 부분에서
FROM 및 그 아래에 WHERE 조건으로 특정하는지에 주목!!

②

WHERE 절의 조건 중 하나로 인라인 뷰 사용함
==> 적절

③

MAX 값을 평가회차 이외의 칼럼에도 적용함

④

FROM의 인라인 뷰에서
WHERE 등을 통한 별다른 조건 설정이 없음

SQLD
DATABASE SQLD SQLD_Probs
Contents