II. SQL 기본 활용
제 1장 SQL 기본 - 문제
문제 38.
배치작업을 통하여 고객에게 추천할 컨텐츠를 생성하고 고객에게 추천서비스를 제공한다.
추천서비스 엔터티에서 언제 추천을 해야 하는지 정의하는 추천대상일자가 있어 해당일자에만 컨텐츠를 추천해야 한다. 또한 고객이 컨텐츠를 추천 받았을 때 선호하는 컨텐츠가 아닌 경우에는 고객이 비선호 컨텐츠로 분류하여 더 이상 추천 받기를 원하지 않는다. 그러므로 우리는 비선호 컨텐츠 엔터티에 등록된 데이터에 대해서는 추천을 수행하지 않아야 한다.
②
SELECT C.컨텐츠ID. C.컨텐츠명
FORM 고객 A INNER JOIN 추천컨텐츠 B
ON (A.고객ID = #custId# AND A.고객ID = B.고객ID) INNER JOIN 컨텐츠 C
ON (B.컨텐츠ID = C.컨텐츠ID) RIGHT OUTER JOIN 비선호컨텐츠 D
ON (B.고객ID = D.고객ID AND B.컨텐츠ID = D.컨텐츠ID)
WHERE B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND B.컨텐츠ID IS NOT NULL;
③
SELECT C.컨텐츠ID. C.컨텐츠명
FORM 고객 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.고객ID AND B.컨텐츠ID = D.컨텐츠ID)
WHERE A.고객ID = #custId#
AND B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND D.컨텐츠ID IS NOT NULL;
④
SELECT C.컨텐츠ID. C.컨텐츠명
FORM 고객 A INNER JOIN 추천컨텐츠 B
ON (A.고객ID = #custId# AND A.고객ID = B.고객ID) INNER JOIN 컨텐츠 C
ON (B.컨텐츠ID = C.컨텐츠ID)
WHERE B.추천대상일자 = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND NOT EXISTS (SELECT X.컨텐츠ID
FROM 비선호컨텐츠 X
WHERE X.고객ID = B.고객ID
AND X.컨텐츠ID = B.컨텐츠ID);정답 : ④
해설:
②, 추천컨텐츠를 기준으로 비선호컨텐츠와의RIGHT OUTER JOIN이 아닌LEFT OUTER JOIN이 수행되어야 하고 비선호컨텐츠의 컨텐츠 ID에 대해서IS NULL;조건이 있다면 정확히 비선호컨텐츠만 필터링할 수 있다.(고객이 비선호로 등록하지 않은 컨텐츠는 추천컨텐츠에만 등록 되어있으므로).
③, 추천컨텐츠를 기준으로 비선호컨텐츠와의LEFT OUTER JOIN이 수행되고D.컨텐츠ID에 대해IS NOT NULL수행 시, 비선호컨텐츠가 추출된다. (추천컨텐츠를 추출해야함)
④, NOT EXISTS 절의 연관서브쿼리에AND X.컨텐츠ID = B.컨텐츠ID구문이 없다면X.고객ID = B.고객ID가 존재하지 않아 단 하나의 컨텐츠라도 비선호로 등록한 고객에 대해서는 모든 컨텐츠가 추천에서 배제된다.
문제 41.
시간대별사용량 테이블 기반으로 고객별 사용금액을 추출하는 SQL?
정답:
SELECT A.고객ID, A.고객명, SUM(B.사용량 * C.단가) AS 사용금액 FORM 고객 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.고객명;
문제 42.
아래의 구문의 실행 결과는 전부 동일
SELECT T.REGION_NAME, T.TEAM_NAME, STADIUM_ID, S.STADIUM_NAME
FORM TEAM T INNER JOIN STADIUM S
USING (STADIUM_ID);
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM_NAME
FORM TEAM INNER JOIN STADIUM
ON (TEAM.STADIUM_ID = STADIUM.STADIUM_NAME)
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME
FORM TEAM T, STADIUM S
WHERE T.STADIUM_ID = S.STADIUM_ID;문제 44. LEFT OUTER JOIN
CREATE TABLE OS
( OSID NUMBER PRIMARY KEY,
OS명 VARCHAR(10) NOT NULL);
CREATE TABLE 단말기
( 단말기ID NUMBER PRIMARY KEY,
단말기명 VARCHAR(10) NOT NULL,
OSID NUMBER FORIEGN KEY);
CREATE TABLE 고객
( 고객번호 NUMBER PRIMARY KEY,
고객명 VARCHAR(10) NOT NULL,
단말기ID NUMBER FORIEGN KEY);
INSERT INTO OS (OSID, OS명)
VALUES (100, 'Android');
INSERT INTO OS (OSID, OS명)
VALUES (200, 'iOS');
INSERT INTO OS (OSID, OS명)
VALUES (300, 'Bada');
INSERT INTO 단말기 (단말기ID, 단말기명, OSID)
VALUES (1000, 'A1000', 100);
INSERT INTO 단말기 (단말기ID, 단말기명, OSID)
VALUES (2000, 'B2000', 100);
INSERT INTO 단말기 (단말기ID, 단말기명, OSID)
VALUES (3000, 'C3000', 200);
INSERT INTO 단말기 (단말기ID, 단말기명, OSID)
VALUES (4000, '4D000', 300);
INSERT INTO 고객 (고객번호, 고객명, 단말기ID)
VALUES (11000, '홍길동', 1000);
INSERT INTO 고객 (고객번호, 고객명, 단말기ID)
VALUES (12000, '강감찬', NULL);
INSERT INTO 고객 (고객번호, 고객명, 단말기ID)
VALUES (13000, '이순시', NULL);
INSERT INTO 고객 (고객번호, 고객명, 단말기ID)
VALUES (14000, '안중근', 3000);
INSERT INTO 고객 (고객번호, 고객명, 단말기ID)
VALUES (15000, '고길동', 4000);
INSERT INTO 고객 (고객번호, 고객명, 단말기ID)
VALUES (16000, '이대로', 4000);
SELECT A.고객번호, A.고객명, B.단말기ID, B.단말기명, C.OSID, C.OS명
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.고객번호;결과:
해설:
FROM 고객 A LEFT OUTER JOIN 단말기 B
ON (A.고객번호 IN (11000, 12000) AND A.단말기ID = B.단말기ID)
ON (A.고객번호 IN (11000, 12000) AND A.단말기ID = B.단말기ID) LEFT OUTER JOIN OS C
문제 45. FULL 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
UNION
SELECT A.ID, B.ID
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 TAB1 A
WHERE NOT EXISTS (SELECT 1 FROM TBL2 B WHERE A.ID = B.ID)
UNION ALL
SELECT NULL, B.ID
FROM TAB2 B
WHERE NOT EXISTS (SELECT 1 FROM TBL1 A WHERE B.ID = A.ID)①, ②, ③ 모두 FULL OUTER JOIN과 동일한 결과를 반환한다.
문제 46. OUTER JOIN
EMP, DEPT 테이블을 LEFT, FULL, RIGHT 외부조인하면 생성되는 결과 건수는?
CREATE TABLE EMP
( A NUMBER NOT NULL,
B VARCHAR(10) NOT NULL,
C VARCHAR(10) NOT NULL);
CREATE TABLE DEPT
( C VARCHAR(10) NOT NULL,
D NUMBER NOT NULL,
E NUMBER NOT NULL);
INSERT INTO EMP (A, B, C)
VALUES (1, 'b', 'w');
INSERT INTO EMP (A, B, C)
VALUES (3, 'd', 'w');
INSERT INTO EMP (A, B, C)
VALUES (5, 'y', 'y');
INSERT INTO DEPT (C, D, E)
VALUES ('w', 1, 10);
INSERT INTO DEPT (C, D, E)
VALUES ('z', 4, 11);
INSERT INTO DEPT (C, D, E)
VALUES ('v', 2, 22);3건, 5건, 4건
LEFT OUTER JOIN
SELECT EMP.A, EMP.B, EMP.C, DEPT.C, DEPT.D, DEPT.E FROM EMP LEFT OUTER JOIN DEPT ON (EMP.C = DEPT.C)
FULL OUTER JOIN
SELECT EMP.A, EMP.B, EMP.C, DEPT.C, DEPT.D, DEPT.E FROM EMP FULL OUTER JOIN DEPT ON (EMP.C = DEPT.C)
RIGHT OUTER JOIN
SELECT EMP.A, EMP.B, EMP.C, DEPT.C, DEPT.D, DEPT.E FROM EMP RIGHT OUTER JOIN DEPT ON (EMP.C = DEPT.C)
문제 49. OUTER JOIN
ORACLE SQL을 동일한 결과를 출력하는 ANSI 표준 구문으로 변경해라.
-- ORACLE
SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A, 게시글 B
WHERE A.게시판ID = B.게시판ID(+)
AND B.삭제여부(+) = 'N' -- Inner절
AND A.사용여부 = 'Y' -- Outer절
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID;
-- ANSI 표준
SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A LEFT OUTER JOIN 게시글 B
ON (A.게시판ID = B.게시판ID AND B.삭제여부 = 'N')
WHERE A.사용여부 = 'Y'
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID;문제 50.
CREATE TABLE 테이블
( COL1 NUMBER NULL,
COL2 NUMBER NULL,
COL3 NUMBER NULL);
INSERT INTO 테이블 (COL1, COL2, COL3)
VALUES (10, 20, NULL);
INSERT INTO 테이블 (COL1, COL2, COL3)
VALUES (15, NULL, NULL);
INSERT INTO 테이블 (COL1, COL2, COL3)
VALUES (50, 70, 20);① SELECT SUM(COL2) FROM 테이블; -- (20+70) = 90
COL2 20 70 ② SELECT SUM(COL1+COL2+COL3) FROM 테이블; -- (50+70+20) = 140
COL1 COL2 COL3 50 70 20 ③ SELECT SUM(COL2+COL3) FROM 테이블; -- (70+20) = 90
COL2 COL3 70 20 ④ SELECT SUM(COL2) + SUM(COL3) FROM 테이블; -- ((20+70) + 20) = 110
COL2 COL3 20 70 20
참고,
SQL 자격검정 실전문제, Kdata 한국데이터산업진흥원
SQLOnline Compiler : https://www.programiz.com/sql/online-compiler
ʚȉɞ