[Oracle] Solution to puzzles 🔥

🧩 도서관리 테이블을 기반으로 한 쿼리문 작성하기

Q1 SQL구문을 RESULT SET에 출력하기

💁‍ 4개 테이블에 포함된 데이터 건 수를 구하는 SQL 구문을 만드는 SQL 구문을 작성하세요.

🚩 Example Output 4 rows selected

SELECT COUNT(*) FROM TB_BOOK;
SELECT COUNT(*) FROM TB_BOOK_AUTHOR;
SELECT COUNT(*) FROM TB_PUBLISHER;
SELECT COUNT(*) FROM TB_WRITER;

SELECT
        'SELECT COUNT(*) FROM ' || A || ';' " "
    FROM (SELECT
                DISTINCT TABLE_NAME A
            FROM USER_TAB_COLUMNS
            ORDER BY 1
            );

💡 Another Solution

SELECT 'SELECT COUNT(*) FROM '||TABLE_NAME||';' AS " "
FROM   USER_TABLES U
WHERE  TABLE_NAME IN ('TB_BOOK', 'TB_BOOK_AUTHOR', 'TB_PUBLISHER', 'TB_WRITER');

💬 Overall Comment

* 처음에는 SELECT 구문 자체를 어떻게 RESULT SET에 나오게끔 해야하는지 감이 잡히질 않아 여러 방면으로 헤맸다. 굉장히 넌센스적인 문제였고, 서브쿼리와 데이터 딕셔너리를 활용하여 결국 풀어내니 뿌듯했다.

---

Q2 데이터 딕셔너리를 활용하여 테이블의 구조 파악하기

💁‍ 4개 테이블의 구조를 파악하려고 합니다. 제시된 결과처럼 TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_DEFAULT, NULLABLE, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME 값을
조회하는 SQL 구문을 작성하세요.

🚩 Example Output 29 rows selected

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_DEFAULT	NULLABLE	CONSTRAINT_NAME	CONSTRAINT_TYPE	R_CONSTRAINT_NAME
TB_BOOK	BOOK_NO	VARCHAR2	(null)	N	PK_BOOK_NO	P	(null)
TB_BOOK	BOOK_NM	VARCHAR2	(null)	Y	(null)	(null)	(null)
. . . . . .	. . . . . .	. . . . . .	. . . . . .	. . . . . .	. . . . . .	. . . . . .	. . . . . .
TB_WRITER	EMAIL_ADDR	VARCHAR2	(null)	Y	(null)	(null)	(null)
TB_WRITER	REGIST_DATE	DATE	(null)	Y	(null)	(null)	(null)

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_DEFAULT, NULLABLE, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME
FROM USER_TAB_COLS
LEFT JOIN USER_CONS_COLUMNS USING (TABLE_NAME, COLUMN_NAME) 
LEFT JOIN USER_CONSTRAINTS USING  (TABLE_NAME, CONSTRAINT_NAME)
WHERE  TABLE_NAME IN ('TB_BOOK', 'TB_BOOK_AUTHOR', 'TB_PUBLISHER', 'TB_WRITER');

---

Q3 LENGTH 활용하기

💁‍ 도서명이 25자 이상인 책 번호와 도서명을 화면에 출력하는 SQL 문을 작성하세요.

🚩 Example Output 5 rows selected

책 번호	도서명
1987071002	창비 1987(58호_1987 부정기 간행물)
1991120501	진실을 영원히 감옥에 가두어 둘 수는 없습니다
1999121501	SMILES OF THE BABY BUDDHA
2004020501	뉴턴과 아인슈타인, 우리가 몰랐던 천재들의 창조성
2007052102	문학집배원 도종환의 시배달-꽃잎의 말로 편지를 쓴다

SELECT
        BOOK_NO "책 번호"
,       BOOK_NM 도서명
    FROM TB_BOOK
    WHERE LENGTH(BOOK_NM) >= 25;

---

Q4 인라인 뷰를 활용한 TOP-N 구하기

💁‍ 휴대폰 번호가 ‘019’로 시작하는 김씨 성을 가진 작가를 이름순으로 정렬했을 때 가장 먼저 표시되는 작가 이름과 사무실 전화번호, 집 전화번호, 휴대폰 전화번호를 표시하는 SQL 구문을 작성하세요.

🚩 Example Output 1 row selected

작가 이름	사무실 전화번호	집 전화번호	휴대폰 전화번호
김경일	052-9272-8984	(null)	019-8623-8304

SELECT
        WRITER_NM "작가 이름"
    ,   OFFICE_TELNO "사무실 전화번호"
    ,   HOME_TELNO "집 전화번호"
    ,   MOBILE_NO "휴대폰 전화번호"
    FROM (SELECT 
                WRITER_NM 
            ,   OFFICE_TELNO 
            ,   HOME_TELNO 
            ,   MOBILE_NO 
            FROM TB_WRITER
            WHERE MOBILE_NO LIKE '019%'
            AND WRITER_NM LIKE '김%'
            ORDER BY 1
        )
    WHERE ROWNUM = 1;

💡 Another Solution

SELECT *
FROM   ( SELECT WRITER_NM, 
                 OFFICE_TELNO, 
                 HOME_TELNO, 
                 MOBILE_NO
         FROM   TB_WRITER
         WHERE  WRITER_NM LIKE '김%'
         AND MOBILE_NO LIKE '019-%'
         ORDER BY 1 )
WHERE  ROWNUM = 1;

---

Q5 COUNT 함수 활용하기

💁‍ 저작 형태가 “옮김”에 해당하는 작가들이 총 몇 명인지 계산하는 SQL 구문을 작성하세요. (결과 헤더는 “작가(명)”으로 표시)

🚩 Example Output 1 row selected

작가(명)
155

SELECT
        COUNT(*) "작가(명)"
    FROM TB_BOOK_AUTHOR
    WHERE COMPOSE_TYPE = '옮김';

---

Q6 그룹함수와 GROUP BY 활용하기

💁‍ 300권 이상 등록된 도서의 저작 형태 및 등록된 도서 수량을 표시하는 SQL 구문을 작성하세요. (단, 저작형태가 등록되지 않은 경우는 제외)

🚩 Example Output 2 rows selected

저작 형태	등록된 도서 수량
지음	1087
엮음	331

SELECT
        COMPOSE_TYPE "저작 형태"
    ,   COUNT(COMPOSE_TYPE) "등록된 도서 수량"
    FROM TB_BOOK_AUTHOR
    GROUP BY COMPOSE_TYPE
    HAVING COUNT(COMPOSE_TYPE) >= 300;

---

Q7 인라인 뷰를 활용한 TOP-N 구하기

💁‍ 가장 최근에 발간된 최신작 이름과 발행일자, 출판사 이름을 표시하는 SQL 구문을 작성하세요.

🚩 Example Output 1 rows selected

최신작 이름	발행일자	출판사 이름
호기심	08/01/02	청연

SELECT
        BOOK_NM "최신작 이름"
    ,   ISSUE_DATE 발행일자
    ,   PUBLISHER_NM "출판사 이름"
    FROM (SELECT
                BOOK_NM 
            ,   ISSUE_DATE 
            ,   PUBLISHER_NM 
            FROM TB_BOOK
            ORDER BY 2 DESC
        )
    WHERE ROWNUM = 1;

💡 Another Solution

SELECT 
		BOOK_NM
	,	ISSUE_DATE
	,	PUBLISHER_NM
FROM   TB_BOOK
WHERE  ISSUE_DATE = (SELECT 
							MAX(ISSUE_DATE)
                      FROM  TB_BOOK
                    ); 

---

Q8 그룹함수가 포함된 인라인 뷰를 활용한 TOP-N 구하기

💁‍ 가장 많은 책을 쓴 작가 3명의 이름과 수량을 표시하되, 많이 쓴 순서대로 표시하는 SQL 구문을 작성하세요. 단, 동명이인 작가는 없다고 가정합니다. (결과 헤더는 “작가 이름”, “권 수”로 표시)

🚩 Example Output 3 rows selected

작가 이름	권 수
고은(高銀)	46
황석영	36
창비	26

SELECT
       "작가 이름"
    ,  "권 수"
    FROM (SELECT 
                WRITER_NM "작가 이름"
            ,   COUNT(BOOK_NO) "권 수"
            FROM TB_WRITER
            JOIN TB_BOOK_AUTHOR USING (WRITER_NO)
            WHERE COMPOSE_TYPE = '지음'
            GROUP BY WRITER_NM
            ORDER BY 2 DESC
        )
    WHERE ROWNUM <= 3;

💬 Overall Comment

* 인라인 뷰를 활용한 TOP-N 분석 문제는 많이 풀어보았기 때문에 큰 어려움 없이 이 문항 또한 
  풀 수 있을거라 생각했다. 하지만 그룹함수 COUNT와 GROUP BY가 포함되는 등 약간 복잡한 
  쿼리를 가지고 인라인 뷰를 만들고자 하니 올바른 SQL 구문이 아니라는 오류가 계속 떠서 
  난감했다. 결론적으로는 메인쿼리에 서브쿼리의 별칭을 넣고 GROUP BY절을 삭제하니 해결이 
  되었다. 여러 가지 방법을 시도하다 해결방안을 발견하니 인상깊게 남은 문항이었다.  

---

Q9 UPDATE와 서브쿼리 활용하기

💁‍ 작가 정보 테이블의 모든 등록일자 항목이 누락되어 있는 걸 발견하였습니다. 누락된 등록일자 값을 각 작가의 ‘최초 출판도서의 발행일과 동일한 날짜’로 변경시키는 SQL 구문을 작성하세요. (COMMIT 처리 요망)

UPDATE
        TB_WRITER TW
    SET REGIST_DATE = (SELECT
                                MIN(ISSUE_DATE)
                            FROM TB_BOOK 
                            JOIN TB_BOOK_AUTHOR USING(BOOK_NO)
                            WHERE TW.WRITER_NO = WRITER_NO
                            -- TB_WRITER와 TB_BOOK_AUTHOR의 각 WRITER_NO가 동일한 조건
                        );

💬 Overall Comment

* 여기서도 작가 마다의 최초 출판도서 발행일은 서브쿼리로 완성했으나 가장 해결하기 어려웠던 
  부분은 '작가마다의 최초 발행일 삽입'이었다. UPDATE구문에서 서브쿼리를 사용해본 경험이 
  적다보니 어떤 방향으로 삽입을 해야할지 난감했다. 요리조리 쿼리를 바꿔보다가 메인쿼리와 
  서브쿼리를 상호연관지어 'B_WRITER와 TB_BOOK_AUTHOR의 각 WRITER_NO가 동일한 조건'을 
  서브쿼리에 입력해주면 해결이 되었다. 오래 고민하던 문제였는데 결국엔 풀어내어 굉장히 뿌듯했다.

---

Q10 기존 테이블의 데이터로 새로운 테이블 생성하기

💁‍ 현재 도서저자 정보 테이블은 저서와 번역서를 구분 없이 관리하고 있지만 앞으로는 번역서는 따로 관리하려고 합니다. 제시된 내용에 맞게 “TB_BOOK_TRANSLATOR” 테이블을 생성하는 SQL 구문을 작성하세요.
(Primary Key 제약 조건 이름은 “PK_BOOK_TRANSLATOR”로 하고, Reference 제약 조건 이름은 “FK_BOOK_TRANSLATOR_01”, “FK_BOOK_TRANSLATOR_02”로 할 것)

🚩 Example Output

CREATE TABLE TB_BOOK_TRANSLATOR(
    BOOK_NO VARCHAR2(10) NOT NULL
,   WRITER_NO VARCHAR2(10) NOT NULL
,   TRANS_LANG VARCHAR2(60) 
,   CONSTRAINT PK_BOOK_TRANSLATOR PRIMARY KEY(BOOK_NO, WRITER_NO)
,   CONSTRAINT FK_BOOK_TRANSLATOR_01 FOREIGN KEY(BOOK_NO) REFERENCES TB_BOOK(BOOK_NO)
,   CONSTRAINT FK_BOOK_TRANSLATOR_02 FOREIGN KEY(WRITER_NO) REFERENCES TB_WRITER(WRITER_NO)
);

---

Q11 새로운 테이블로 데이터 옮기기

💁‍ 도서 저작 형태(composetype)가 '옮김', '역주', '편역', '공역'에 해당하는 데이터는 도서 저자 정보 테이블에서 도서 역자 정보 테이블(TB_BOOK TRANSLATOR)로 옮기는 SQL 구문을 작성하세요. 단, “TRANS_LANG” 컬럼은 NULL 상태로 두도록 합니다. (이동된 데이터는 더 이상 TB_BOOK_AUTHOR 테이블에 남아 있지 않도록 삭제할 것)

/* 데이터 복사 */
INSERT
    INTO
    TB_BOOK_TRANSLATOR(BOOK_NO, WRITER_NO)
SELECT
        BOOK_NO
    ,   WRITER_NO
    FROM TB_BOOK_AUTHOR
    WHERE COMPOSE_TYPE IN ('옮김', '역주', '편역', '공역');
    
/* 남아있는 데이터 삭제 */
DELETE
    FROM TB_BOOK_AUTHOR
    WHERE COMPOSE_TYPE IN ('옮김', '역주', '편역', '공역');

---

Q12 JOIN 활용하기

💁‍ 2007년도에 출판된 번역서 이름과 번역자(역자)를 표시하는 SQL 구문을 작성하세요.

🚩 Example Output 5 rows selected

번역서 명	번역자
올리버 트위스트 2	윤혜준
올리버 트위스트 1	윤혜준
역주 시품	이철리
출동! 그린팀 고래를 구하자	유희석
안과밖: 영미문학연구 제23호	정혜용

SELECT
        BOOK_NM "번역서 명"
    ,   WRITER_NM 번역자
    FROM TB_BOOK 
    JOIN TB_BOOK_TRANSLATOR USING(BOOK_NO)
    JOIN TB_WRITER USING(WRITER_NO)
    WHERE BOOK_NO LIKE '2007%';

---

Q13 뷰 생성하기

💁‍ 12번 결과를 활용하여 대상 번역서들의 출판일을 변경할 수 없도록 하는 뷰를 생성하는 SQL구문을 작성하세요. (뷰 이름은 “VW_BOOK_TRANSLATOR”로 하고 도서명, 번역자, 출판일이 표시되도록 할 것)

🚩 Example Output 169 rows selected

도서명	번역자	출판일
민중문화와 제3세계	신경림	1983년 10월 25일
피의 꽃잎 下	김종철	1983년 09월 10일
. . . . . .	. . . . . .
시조 에쎄이	임희근	1995년 06월 30일
역주 목민심서 2	다산연구회	1995년 05월 25일

/* CREATE VIEW 권한 부여 */
GRANT CREATE VIEW TO C##BOOK;

/* 뷰 생성 */
CREATE OR REPLACE VIEW VW_BOOK_TRANSLATOR
AS
SELECT
        BOOK_NM 도서명
    ,   WRITER_NM 번역자
    ,   TO_CHAR(TO_DATE(SUBSTR(BOOK_NO, 1, 8), 'YYMMDD'), 'RRRR"년 "MM"월 "DD"일 "') 출판일
    FROM TB_BOOK 
    JOIN TB_BOOK_TRANSLATOR USING(BOOK_NO)
    JOIN TB_WRITER USING(WRITER_NO)
    WITH READ ONLY;

---

Q14 INSERT를 활용하여 데이터 삽입하기

💁‍ 새로운 출판사(춘 출판사)와 거래 계약을 맺게 되었습니다. 제시된 다음 정보를 입력하는 SQL 구문을 작성하세요. (COMMIT 처리 요망)

INSERT
    INTO TB_PUBLISHER
(
    PUBLISHER_NM, PUBLISHER_TELNO, DEAL_YN
)
VALUES
(
    '춘 출판사', '02-6710-3737', DEFAULT
);

COMMIT;

---

Q15 GROUP BY를 활용하여 동명이인 수 세기

💁‍ 동명이인 작가의 이름을 찾으려고 합니다. 이름과 동명이인 수를 표시하는 SQL 구문을 작성하세요.

🚩 Example Output 3 rows selected

이름	동명이인 수
이상권	2
이기영	2
이정호	2

SELECT
        WRITER_NM 이름
    ,   COUNT(WRITER_NM) "동명이인 수" 
    FROM TB_WRITER
    GROUP BY WRITER_NM
    HAVING COUNT(WRITER_NM) > 1;

---

Q16 UPDATE를 활용하여 NULL값 치환하기

💁‍ 도서의 저자 정보 중 저작 형태(compose_type)가 누락된 데이터들이 적지 않게 존재합니다. 해당 컬럼이 NULL인 경우 '지음'으로 변경하는 SQL 구문을 작성하세요. (COMMIT 처리 요망)

UPDATE
        TB_BOOK_AUTHOR
    SET COMPOSE_TYPE = DEFAULT -- 이 컬럼의 기본값이 '지음'
    WHERE COMPOSE_TYPE IS NULL;

COMMIT;

💬 Overall Comment

* 처음에 WHERE COMPOSE_TYPE = NULL;로 입력했다가 계속 0행이 삽입되었다는 문구만 
뜨길래 뭐가 문제인지 잠시 헷갈렸다. NULL은 반드시 '='이 아닌 'IS'를 함께 사용해야 한다는 
것을 잊지말자!

---

Q17 LIKE와 LENGTH함수 활용하기

💁‍ 서울지역 작가 모임을 개최하려고 합니다. 사무실이 서울이고, 사무실 전화 번호 국번이 3자리인 작가의 이름과 사무실 전화 번호를 표시하는 SQL 구문을 작성하세요.

🚩 Example Output 8 rows selected

이름	사무실 전화번호
김준태	02-777-3558
임형택	02-295-5329
. . . . . .	. . . . . .
곽학송	02-714-3181
송성수	02-504-4275

SELECT
        WRITER_NM 이름
    ,   OFFICE_TELNO "사무실 전화번호"
    FROM TB_WRITER
    WHERE OFFICE_TELNO LIKE '02%'
    AND LENGTH(OFFICE_TELNO) < 12;

💡 Another Solution

SELECT WRITER_NM, 
        OFFICE_TELNO
FROM   TB_WRITER
WHERE  OFFICE_TELNO LIKE '02-___-%'
ORDER BY 1; 

💬 Overall Comment

* 처음에 국번이 지역번호인 02를 의미하는줄로 알고 문제가 조금 이상하다고 생각되었으나 
  검색을 해보니, 국번은 지역번호(02)의 바로 뒷 번호를 의미하는 것이었다. 하하; 
  국번의 뒷자리는 모두 4자리일테니, 따라서 LENGTH(OFFICE_TELNO) < 12;를 사용하여 
  국번이 3자리인 번호만 조회하였다. 문제를 해석하고 풀기 위해서는 이런 기본적인 상식 또한 
  필수라는 것을 또 배웠다.

---

Q18 MONTHS_BETWEEN 활용하기

💁‍ 2006년 1월 기준으로 등록된지 31년 이상 된 작가 이름을 이름순으로 표시하는 SQL 구문을 작성하세요.

🚩 Example Output 10 rows selected

작가 이름
김정한(金廷漢)
리영희
. . . . . .
천승세
황석영

SELECT
        WRITER_NM "작가 이름"
    FROM TB_WRITER
    WHERE MONTHS_BETWEEN(TO_DATE(200601, 'YYMM'), REGIST_DATE) / 12 >= 31
    ORDER BY 1;

---

Q19 CASE 함수 사용하기

💁‍ 요즘 들어 다시금 인기를 얻고 있는 '황금가지' 출판사를 위한 기획전을 열려고 합니다. '황금가지' 출판사에서 발행한 도서 중 재고 수량이 10권 미만인 도서명과 가격, 재고상태를 표시하는 SQL 구문을 작성하세요. 재고 수량이 5권 미만인 도서는 ‘추가주문필요’로, 나머지는 ‘소량보유’로 표시하고, 재고수량이 많은 순, 도서명 순으로 표시되도록 합니다.

🚩 Example Output 12 rows selected

도서명	가격	재고상태
그리스 로마 신화 2	6000	소량보유
모여라 꾸러기 신들	8000	소량보유
. . . . . .	. . . . . .	. . . . . .
장길산 9	7500	추가주문필요
한국경제의 구조와 전개	6000	추가주문필요

SELECT
        BOOK_NM 도서명
    ,   PRICE 가격
    ,   CASE
            WHEN STOCK_QTY < 5 THEN '추가주문필요'
            ELSE '소량보유'
        END 재고상태
    FROM TB_BOOK
    WHERE PUBLISHER_NM = '황금가지'
    AND STOCK_QTY < 10
    ORDER BY STOCK_QTY DESC, 1;

💬 Overall Comment

* 처음에는 선택함수 DECODE를 사용하고자 했으나, 요리조리 사용하다보니 비교연산자 사용이 
불가능하다는 것을 깨달았다. 그래서 아직 사용해보지 못 했던 CASE함수를 사용하여 재고가 
5권 미만이면 '추가주문필요', 나머지(ELSE)는 '소량보유'로 쿼리문을 작성하였다. 
효율적이고 깔끔한 쿼리가 완성된 듯하여 뿌듯했다.

---

Q20 다중 및 SELF JOIN 활용하기

💁‍ '아타트롤' 도서 작가와 역자를 표시하는 SQL 구문을 작성하세요. (결과 헤더는 ‘도서명’,’저자’,’역자’로 표시)

🚩 Example Output 1 row selected

도서명	저자	역자
아타트롤	고트홀트 에프라임 레씽(GOTTHOLD EPHRAIM LESSING)	윤도중

SELECT
        BOOK_NM 도서명
    ,   TW1.WRITER_NM 저자
    ,   TW2.WRITER_NM 역자
    FROM TB_BOOK TB
    JOIN TB_BOOK_AUTHOR TBA ON(TB.BOOK_NO = TBA.BOOK_NO)
    JOIN TB_BOOK_TRANSLATOR TBT ON(TB.BOOK_NO = TBT.BOOK_NO)
    JOIN TB_WRITER TW1 ON(TBA.WRITER_NO = TW1.WRITER_NO)
    JOIN TB_WRITER TW2 ON(TBT.WRITER_NO = TW2.WRITER_NO)
    WHERE BOOK_NM = '아타트롤';

💬 Overall Comment

* 처음에 문제를 읽었을 때, 이 문항은 무조건 SELF-JOIN을 사용해야겠다고 생각했다. 
  하지만 실제로 적용시키기에 약간 헷갈리는 부분이 있어 헤맸지만 천천히 논리적으로 생각하여 
  JOIN의 순서를 신경쓰며 쿼리문을 작성한 결과 올바른 RESULT SET이 출력되었다 :)
  평소에 JOIN을 남용하는 습관이 있다보니 완성하고나서도 이것이 최선인지 의구심이 들었지만 
  검토한 결과 필요없는 JOIN절은 없었다. 이것 역시 뿌듯한 문항 중 하나이다.

---

Q21 MONTHS_BETWEEN 활용하기

💁‍ 현재 기준으로 최초 발행일로부터 만 30년이 경과되고, 재고 수량이 90권 이상인 도서에 대해 도서명, 재고수량, 원래 가격, 20% 인하 가격을 표시하는 SQL 구문을 작성하세요. (결과 헤더는 “도서명”, “재고수량”, “가격(Org)”, “가격(New)”로 표시. 재고 수량이 많은 순, 할인 가격이 높은 순, 도서명순으로 표시)

🚩 Example Output 58 rows selected

도서명	재고수량	가격(Org)	가격(New)
사과나무밭 달님_1978년판	98	8500	6800
문학과 예술의 사회사 4	98	8000	6400
. . . . . .	. . . . . .	. . . . . .	. . . . . .
똘스또이	90	2000	1600
창작과비평 49호(78년 가을호)	90	800	640

SELECT
        BOOK_NM 도서명
    ,   STOCK_QTY 재고수량
    ,   PRICE "가격(Org)"
    ,   (PRICE - PRICE * 0.2) "가격(New)"
    FROM TB_BOOK
    WHERE MONTHS_BETWEEN(SYSDATE, ISSUE_DATE) / 12 >= 30
    AND STOCK_QTY >= 90
    ORDER BY 2 DESC, 4 DESC, 1;

---