[Oracle/수업 과제 practice] 도서 관리

-- SQL Final Workshop
-- 도서 관리 시스템
✅ 답안과 비교하여 스스로 SQL 구문 개선점 짚어보기 완료(2022.02.06)

 

FINAL 1. ❎✅

-- 1. 4개 테이블에 포함된 데이터 건 수를 구하는 SQL문 생성 구문 작성
SELECT
       'SELECT COUNT(*) FROM ' || TABLE_NAME || ';' AS " "
  FROM USER_TABLES
 ORDER BY TABLE_NAME;

 

FINAL 2. ❎✅

-- 2. 4개 테이블의 구조를 파악하려고 한다.
-- 제시된 결과처럼 TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_DEFAULT, NULLABLE, 
-- CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME 값을 조회하는 SQL 구문을 작성
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)
 ORDER BY 1;

 

FINAL 3. ✅

-- 3. 도서명이 25자 이상인 책 번호와 도서명을 출력하는 SQL문 작성
SELECT
       BOOK_NO
     , BOOK_NM
  FROM TB_BOOK
 WHERE LENGTH(BOOK_NM) >= 25;

SELECT *
FROM USER_TAB_COLS; -- *** TABLE_NAME, COLUMN_NAME, DATA_TYPE,*** DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH, DATA_PRECISION,
                    -- DATA_SCALE. ***NULLABLE,*** COLUMN_ID, DEFAULT_LENGTH, ***DATA_DEFAULT,*** NUM_DITINCT, LOW_VALUE, HIGH_VALUE,
                    -- DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, CHARACTER_SET_NAME, CHAR_COL_DEL_LENGTH,
                    -- GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED, V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN,
                    -- VARTUAL_COLUMN, SEGMENT_COLUMN_ID, HISTROGRAM, QUALIFIED_COL_NAME

SELECT * 
FROM USER_CONS_COLUMNS; -- OWNER, ***CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME,*** POSITION

SELECT *
FROM USER_CONSTRAINTS; -- OWNER, ***CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, *** SEARCH_CONDITION, R_OWNER, 
                       -- *** R_CONSTRAINT_NAME, *** DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED,
                       -- BAD, RELY, LAST_CHANCE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED

 

FINAL 4. ✅

ROWNUM 대신 RANK() OVER를 쓰는 것도 방법이다.

휴대폰 번호에 대한 조건을 작성할 때 LIKE 연산자를 써서 명시할 수도 있다: MOBILE_NO LIKE '019-%'

최초 작성한 답안에서 인라인뷰 컬럼과 SELECT한 컬럼이 모두 일치하므로 V.* 처럼 간단히 작성해도 결과는 같다.

-- 4. 휴대폰 번호가 '019'로 시작하는 김씨 성을 가진 작가를 이름순으로 정렬했을 때
-- 가장 먼저 표시되는 작가 이름과 사무실 전화번호, 집 전화번호, 휴대폰 전화번호를 표시하는 SQL문 작성
SELECT
       V.*
  FROM (SELECT W.WRITER_NM
             , W.OFFICE_TELNO
             , W.HOME_TELNO
             , W.MOBILE_NO
          FROM TB_WRITER W
         WHERE SUBSTR(W.MOBILE_NO, 1, 3) = '019'
           AND W.WRITER_NM LIKE '김%'
         ORDER BY 1 
        )V
 WHERE ROWNUM <= 1;

-- RANK() OVER 활용
SELECT
       V.WRITER_NM
     , V.OFFICE_TELNO
     , V.HOME_TELNO
     , V.MOBILE_NO
  FROM (SELECT W.WRITER_NM
             , W.OFFICE_TELNO
             , W.HOME_TELNO
             , W.MOBILE_NO
             , RANK() OVER(ORDER BY W.WRITER_NM) AS "RANK"
         FROM TB_WRITER W
        WHERE W.MOBILE_NO LIKE '019-%'
          AND W.WRITER_NM LIKE '김%'
        ) V
 WHERE V.RANK <= 1;

 

FINAL 5. ❎✅

작가 이름을 중복 제거하고 세면 된다.

-- 5. 저작 형태가 "옮김"에 해당하는 작가들이 총 몇 명인지 계산하는 SQL문 작성
-- 결과 헤더는 "작가(명)"으로 표시되도록 할 것
SELECT
       COUNT(DISTINCT WRITER_NM) "작가(명)"
  FROM TB_WRITER
  JOIN TB_BOOK_AUTHOR USING (WRITER_NO)
 WHERE COMPOSE_TYPE = '옮김';

 

FINAL 6. ❎✅

GROUP BY~HAVING절에서 조건을 달아 해결한다.

-- 6. 300권 이상 등록된 도서의 저작 형태 및 등록된 도서 수량을 표시하는 SQL문 작성
-- 저작 형태가 등록되지 않은 경우는 제외할 것
SELECT
       COMPOSE_TYPE
     , COUNT(*)
  FROM TB_BOOK_AUTHOR
 WHERE COMPOSE_TYPE IS NOT NULL
 GROUP BY COMPOSE_TYPE
HAVING COUNT(*) >= 300;

 

FINAL 7. ✅

인라인뷰와 ROWNUM을 활용하는 대신, WHERE절에 단일행 서브쿼리를 쓸 수도 있다.

-- 7. 가장 최근에 발간된 최신작 이름과 발행일자, 출판사 이름 표시하는 SQL문 작성
SELECT
       V.BOOK_NM
     , V.ISSUE_DATE
     , V.PUBLISHER_NM
  FROM (SELECT BOOK_NM
             , ISSUE_DATE
             , PUBLISHER_NM
          FROM TB_BOOK
         ORDER BY 2 DESC 
        ) V
 WHERE ROWNUM <= 1;

 

FINAL 8. ✅

SELECT절의 컬럼명을 V.*로 간단히 작성할 수 있다.

COUNT(*)처럼 써도 무방하다.

-- 8. 가장 많은 책을 쓴 작가 3명의 이름과 수량을 표시하되,
-- 많이 쓴 순서대로 표시하는 SQL문 작성
-- 단, 동명이인 작가는 없다고 가정한다.
-- 결과 헤더는 "작가 이름", "권 수"로 표시되도록 한다.
SELECT
       V."작가 이름"
     , V."권 수"
  FROM (SELECT WT.WRITER_NM "작가 이름"
             , COUNT(BA.WRITER_NO) "권 수"
          FROM TB_WRITER WT
          JOIN TB_BOOK_AUTHOR BA ON (WT.WRITER_NO = BA.WRITER_NO)
         GROUP BY WT.WRITER_NM
         ORDER BY 2 DESC
        ) V
 WHERE ROWNUM <= 3;

 

FINAL 9. ❎✅

그룹함수 MIN은 최소값만 다루지 않고, 사전순으로 가장 이르거나 날짜순으로 가장 빠른 데 대해서도 적용할 수 있다.

-- 1,052개 행 이(가) 업데이트되었습니다.

-- 9. 작가 정보 테이블의 모든 등록일자 항목이 누락돼 있는 것을 발견하였다.
-- 누락된 등록일자 값을 각 작가의 최초 출판도서의 발행일과 동일한 날짜로 변경시키는 SQL문 작성
-- COMMIT 처리할 것
UPDATE
       TB_WRITER WT
   SET WT.REGIST_DATE = (SELECT MIN(BK.ISSUE_DATE)
                           FROM TB_BOOK BK
                           JOIN TB_BOOK_AUTHOR BA ON (BK.BOOK_NO = BA.BOOK_NO)
                          WHERE WT.WRITER_NO = BA.WRITER_NO);
COMMIT;

 

FINAL 10. ✅

CREATE TABLE 및 ALTER TABLE~ADD CONSTRAINT 단계로 나누어 진행할 수도 있다.

Table TB_BOOK_TRANSLATOR이(가) 생성되었습니다.

-- 10. 현재 도서저자 정보 테이블은 저서와 번역서를 구분 없이 관리하고 있다.
-- 앞으로는 번역서를 따로 관리하려고 한다. 제시된 내용에 맞게 "TB_BOOK_TRANSLATOR" 테이블 생성하는 SQL 구문 작성
-- PRIMARY KEY 제약 조건 이름은 "PK_BOOK_TRANSLATOR"로 한다.
-- REFERENCE 제약 조건 이름은 "FK_BOOK_TRANSLATOR_01", "FK_BOOK_TRANSLATOR_02"로 한다.
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),
    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)
);
-- 컬럼 주석
COMMENT ON COLUMN TB_BOOK_TRANSLATOR.BOOK_NO IS '도서 번호';
COMMENT ON COLUMN TB_BOOK_TRANSLATOR.WRITER_NO IS '작가 번호';
COMMENT ON COLUMN TB_BOOK_TRANSLATOR.TRANS_LANG IS '번역 언어';

 

FINAL 11. ✅

SELECT절에서 채택한 BOOK_NO, WRITER_NO 컬럼과 서브쿼리 WHERE절에 쓰인 COMPOSE_TYPE 모두 TB_BOOK_AUTHOR 테이블 하나만으로 조회 가능한 것들이다. 따라서 조인은 굳이 필요하지 않다.

169개 행 이(가) 삽입되었습니다.
169개 행 이(가) 삭제되었습니다.

-- 11. 도서 저작 형태가 옮김, 역주, 편역, 공역에 해당하는 데이터는
-- 도서 저자 정보 테이블에서 도서 역자 정보 테이블(TB_BOOK_TRANSLATOR)로 옮기는 SQL 구문 작성
-- 단, TRANS_LANG 컬럼은 NULL 상태로 두도록 한다.
INSERT 
  INTO TB_BOOK_TRANSLATOR
(
  BOOK_NO
, WRITER_NO
)
(
SELECT BOOK_NO
     , WRITER_NO
  FROM TB_BOOK_AUTHOR
 WHERE COMPOSE_TYPE IN ('옮김', '역주', '편역', '공역')
);
-- 최초 작성한 답
INSERT
  INTO TB_BOOK_TRANSLATOR TBT
(
  TBT.BOOK_NO
, TBT.WRITER_NO
)
(
SELECT BK.BOOK_NO
     , WT.WRITER_NO
  FROM TB_BOOK BK
  JOIN TB_BOOK_AUTHOR BA ON (BK.BOOK_NO = BA.BOOK_NO)
  JOIN TB_WRITER WT ON (BA.WRITER_NO = WT.WRITER_NO)
 WHERE BA.COMPOSE_TYPE IN ('옮김', '역주', '편역', '공역')
);
-- 이동된 데이터는 더이상 TB_BOOK_AUTHOR 테이블에 남아있지 않도록 삭제한다.
DELETE
  FROM TB_BOOK_AUTHOR
 WHERE COMPOSE_TYPE IN ('옮김', '역주', '편역', '공역');
 COMMIT;

 

FINAL 12. ✅

답안에서는 ISSUE_DATE 컬럼도 조회했다.

WHERE절 조건으로 연도를 가져올 때 다음과 같이 더 간단히 쓸 수 있어야겠다: TO_CHAR(ISSUE_DATE, 'RRRR') = '2007';

-- 12. 2007년 출판된 번역서 이름과 번역자를 표시하는 SQL 구문 작성
SELECT
       BK.BOOK_NM
     , WT.WRITER_NM
  FROM TB_BOOK BK
  JOIN TB_BOOK_TRANSLATOR BT ON (BK.BOOK_NO = BT.BOOK_NO)
  JOIN TB_WRITER WT ON (BT.WRITER_NO = WT.WRITER_NO)
 WHERE TO_CHAR(TO_DATE(SUBSTR(BK.ISSUE_DATE, 1, 2), 'RRRR'), 'YYYY') = '2007';

 

FINAL 13. ❎✅

컬럼명이 같을 때는 가급적 USING()을 쓰고, TO_CHAR() 구문도 고쳐 쓰면서 쿼리문을 간단히 하는 연습이 필요하겠다.

문제 조건이 '출판일을 변경할 수 없도록' 하는 것이므로 WITH READ ONLY가 아니라 WITH CHECK OPTION으로 옵션을 걸어야 한다.

View VW_BOOK_TRANSLATOR이(가) 생성되었습니다.

-- 13. 12번 결과를 활용하여 대상 번역서들의 출판일을 변경할 수 없도록 하는 뷰를 생성하는 SQL 구문 작성
-- 뷰 이름은 "VW_BOOK_TRANSLATOR"로 하고 도서명, 번역자, 출판일이 표시되도록 한다.
GRANT CREATE VIEW TO C##FINALWORKSHOP;
CREATE OR REPLACE VIEW VW_BOOK_TRANSLATOR
AS
SELECT
       BOOK_NM
     , WRITER_NM
  FROM TB_BOOK
  JOIN TB_BOOK_TRANSLATOR USING (BOOK_NO)
  JOIN TB_WRITER USING (WIRTER_NO)
 WHERE TO_CHAR(ISSUE_DATE, 'RRRR') = '2007'
  WITH CHECK OPTION;

 

FINAL 14. ✅

세 번째 컬럼에 DEFAULT 값을 명시해 INTO () 소괄호 안에 컬럼명을 생략했다.

답안에서는 컬럼명 두 개를 명시하고, DEFAULT를 쓰지 않았다.

-- 14. 새로운 출판사(춘 출판사)와 거래 계약을 맺게 되었다.
-- 제시된 정보를 입력하는 SQL 구문 작성
-- COMMIT 처리할 것
INSERT
  INTO TB_PUBLISHER
VALUES
(
  '춘 출판사'
, '02-6710-3737'
, DEFAULT
);
COMMIT;

 

FINAL 15. ❎✅

HAVING 조건을 달면 간단한 문제였다.

-- 15. 동명이인 작가의 이름을 찾으려고 한다.
-- 이름과 동명이인 숫자를 표시하는 SQL 구문 작성
SELECT
       WRITER_NM
     , COUNT(*)
  FROM TB_WRITER
GROUP BY WRITER_NM
HAVING COUNT(*) > 1;

 

FINAL 16. ❎✅

WHERE절 조건으로 IS NULL을 명시하면 된다.

389개 행 이(가) 업데이트되었습니다.

-- 16. 도서의 저자 정보 중 저작 형태가 누락된 데이터들이 적지 않다.
-- 해당 컬럼이 NULL인 경우 '지음'으로 변경하는 SQL 구문 작성
-- COMMIT 처리할 것
UPDATE
       TB_BOOK_AUTHOR
   SET COMPOSE_TYPE = '지음'
 WHERE COMPOSE_TYPE IS NULL;
COMMIT;

 

FINAL 17. ✅

대시(-)가 들어가는 자리 또한 자릿수에 포함해 명시할 수 있고, 혹은 문제 조건을 보다 정확히 기재하기 위해 다음과 같이 고쳐쓸 수도 있다: OFFICE_TELNO LIKE '02-___-%'

-- 17. 서울 지역 작가 모임을 개최하려고 한다.
-- 사무실이 서울이고, 사무실 전화번호 국번이 3자리인 작가의 이름과 사무실 전화번호 표시하는 SQL 구문 작성
SELECT
       WRITER_NM
     , OFFICE_TELNO
  FROM TB_WRITER
 WHERE OFFICE_TELNO LIKE '02_________';

 

FINAL 18. ✅

답안에서는 TO_DATE()를 다음과 같이 썼다: TO_DATE('20060101','YYYY/MM/DD')

-- 18. 2006년 1월 기준으로 등록된 지 31년 이상된 작가 이름을 이름순으로 표시하는 SQL 구문 작성
SELECT
       WRITER_NM
     , REGIST_DATE
  FROM TB_WRITER
 WHERE MONTHS_BETWEEN(TO_DATE(20060101, 'RRRRMMDD'), REGIST_DATE) >= 372
 ORDER BY 1;

 

FINAL 19. ❎✅

CASE~WHEN~THEN~ELSE~END 구문을 사용할 수 있다.

-- 19. 황금가지 출판사를 위한 기획전을 열려고 한다.
-- 황금가지 출판사에서 발행한 도서 중 재고 수량이 10권 미만인 도서명과 가격, 재고상태를 표시하는 SQL 구문 작성
-- 재고 수량이 5권 미만인 도서는 '추가주문필요'로, 나머지는 '소량보유'로 표시하고,
-- 재고 수량이 많은 순, 도서명 순으로 표시되도록 한다.
SELECT
       BOOK_NM "도서명"
     , PRICE "가격"
     , CASE
        WHEN STOCK_QTY < 5 THEN '추가주문필요'
        ELSE '소량보유'
       END "재고수량"
  FROM TB_BOOK
 WHERE PUBLISHER_NM = '황금가지'
   AND STOCK_QTY < 10
 ORDER BY 3;

 

FINAL 20. ❎✅

테이블간 조인 관계가 중요하다. 하나라도 흐트러지면 정상적으로 조회되지 않는다.

저자=BOOK_AUTHOR, 역자=BOOK_TRANSLATOR로 연결지어야 한다.

-- 20. 아타트롤 도서 작가와 역자를 표시하는 SQL 구문 작성
-- 결과 헤더는 '도서명', '저자', '역자'로 표시한다.
SELECT
       BK.BOOK_NM "도서명"
     , WA.WRITER_NM "저자"
     , WB.WRITER_NM "역자"
  FROM TB_BOOK BK
  JOIN TB_BOOK_AUTHOR BA ON (BK.BOOK_NO = BA.BOOK_NO)
  JOIN TB_BOOK_TRANSLATOR BT ON (BK.BOOK_NO = BT.BOOK_NO)
  JOIN TB_WRITER WA ON (BA.WRITER_NO = WA.WRITER_NO)
  JOIN TB_WRITER WB ON (BT.WRITER_NO = WB.WRITER_NO)
 WHERE BK.BOOK_NM = '아타트롤';

 

FINAL 21. ✅

-- 21. 현재 기준으로 최초 발행일로부터 만 30년이 경과되고, 재고 수량이 90권 이상인 도서에 대해
-- 도서명, 재고 수량, 원래 가격, 20% 인하 가격을 표시하는 SQL 구문 작성
-- 결과 헤더는 "도서명", "재고 수량", "가격(Org)", "가격(New)"로 표시한다.
-- 재고 수량이 많은 순, 할인 가격이 높은 순, 도서명 순으로 표시되도록 한다.
SELECT
       BOOK_NM "도서명"
     , STOCK_QTY "재고 수량"
     , TO_CHAR(PRICE, '99,999') "가격(Org)"
     , TO_CHAR(PRICE - (PRICE * 0.2), '99,999') "가격(New)"
  FROM TB_BOOK
 WHERE MONTHS_BETWEEN(SYSDATE, ISSUE_DATE) >= 360
   AND STOCK_QTY >= 90
 ORDER BY 2 DESC, 4 DESC, 1;