Q1. 클래스 문제1: 거래 상태에 따른 출력 및 날짜 비교
SELECT board_id, writer_id, title, price,
CASE status
WHEN 'SALE' THEN '판매중'
WHEN 'RESERVED' THEN '예약중'
WHEN 'DONE' THEN '거래완료'
ELSE status
END AS STATUS
FROM used_goods_board
WHERE created_date = '2022-10-05'
ORDER BY board_id DESC;중고 거래 게시글 중에서 생성일자를 기준으로 한 번 거르고, 거래 상태에 따라 판매중/예약중/거래완료를 표기하는 문제였다.
날짜를 비교하는 부분은 날짜 그대로 비교하는게 성능적으로 더 좋다기에 created_date = '2022-10-05'와 같이 날짜 그대로 두고 비교하였다.
또 거래 상태가 SALE/RESERVED/DONE일 때에 따라 다른 식으로 표기해줘야하는 부분은 CASE WHEN을 사용했다. WHEN 뒤에 조건식을 써도 되지만, 단일값의 경우가 동일한지 (= 이 연산 비교) 에는 C언어나 다른 언어의 switch문처럼 CASE에 속성을 적고, WHEN 뒤에 값을 적어주면 되기에 그렇게 작성해주었다.
Q2. 클래스 문제2: 10월 생성된 글 및 댓글 조회
SELECT B.title, B.board_id,
R.reply_id, R.writer_id, R.contents,
DATE_FORMAT(R.created_date, '%Y-%m-%d')
FROM used_goods_board B JOIN
used_goods_reply R ON
B.board_id = R.board_id
WHERE MONTH(B.created_date) = '10'
ORDER BY R.created_date ASC, B.title;댓글 테이블과 게시글 테이블이 있었고, 댓글 테이블에서 게시글 id를 외래키로 가지면서 관계를 맺고 있는 식이었다.
10월에 만들어진 게시글과 그 댓글 등의 정보를 조회해서 출력하는 문제여서 일단은 MONTH()를 써서 비교했는데...? 이게 연도 조건이 있었던걸로 기억하는데 이렇게 풀면 연도는 사실 상관없이 10월에 만든 글이면 다 출력될텐데...? 어떻게 통과가 된 것 같다.
실제로는 WHERE MONTH(B.created_date) = '10' 이런식으로 쓰지 않고 그래서
B.created_date BEWTEEN '2022-10-01' AND '2022-10-31' 이렇게 쓰는게 더 좋을 것 같다. 또 찾아보니까, B.created_date LIKE '2022-10%' 이렇게 써도 10월인 날짜들만 걸러지는 것 같은데 이건 문자열로 비교하는거라 성능이 별로...다.
Q3. 클래스 문제3:
-- 코드를 입력하세요
SELECT B.book_id, A.author_name,
DATE_FORMAT(B.published_date, '%Y-%m-%d') AS PUBLISHED_DATE
FROM book B JOIN
author A ON
B.author_id = A.author_id
WHERE B.category = '경제'
ORDER BY B.published_date;크게 복잡한건 없었고, 테이블 두 개를 JOIN으로 잘 연결한 후에 카테고리가 경제인 것들만 남기고, 날짜 형식만 요구대로 잘 조정해주었다.
날짜 형식은 DATE_FORMAT() 사용해서 연월일만 출력하도록 조정해주었다.
Q4. 클래스 문제4: 상품의 코드 앞 자리 두 개를 기준으로 분류
SELECT LEFT(product_code,2) AS category,
COUNT(DISTINCT product_id) AS product
FROM product P
GROUP BY category
ORDER BY category;처음에는 순간 앞 자리 두 개로 어떻게 분류해야하지? 했고, 약간 테이블을 하나 만들거나 서브 쿼리를 만들어야하나 생각했는데, LEFT()를 써서 분리하고 이걸 기준으로 그루핑하고, 정렬하면 되는 것이었다. 정렬은 SELECT 후에 진행되니 SELECT에서 만든 별칭으로 정렬해도 되고.
LEFT(속성,2) 이렇게 해서 우선 상품 코드의 앞 두 개를 카테고리로 SELECT에서 정의해주었다.
그리고 위에서는 GROUP BY에 별칭 사용하긴 했는데 MySQL이나 MariaDB에서는 이렇게 GROUP BY에서 SELECT에서 사용한 별칭을 사용하는걸 허용하는데 Oracle에서는 허용하지 않는다고 하니 만약에 표준적으로 사용해야하는걸 생각하면
GROUP BY LEFT(product_code,2)이렇게 적어야할 것 같았는데, Oracle에서는 LEFT도 없다고 하니? 그것조차 SUBSTR으로 바꾸어 주어야할 것 같다...
Q5. 클래스 문제5: 상품 출고 날짜를 기준으로 출력
SELECT order_id, product_id, COALESCE(out_date,' ') AS out_date,
CASE
WHEN out_date <= '2022-05-01' THEN '출고완료'
WHEN out_date IS NULL THEN '출고미정'
ELSE '출고대기'
END AS '출고여부'
FROM food_order
ORDER BY order_id;날짜 기준으로 출고 여부를 잘 출력해줘야하는 문제였는데, 일단 처음에 위에처럼 쿼리를 작성해서 여러번 틀렸다.
SELECT order_id, product_id, DATE_FORMAT(out_date, '%Y-%m-%d') AS out_date,
CASE
WHEN out_date <= '2022-05-01' THEN '출고완료'
WHEN out_date IS NULL THEN '출고미정'
WHEN out_date > '2022-05-01' THEN '출고대기'
END AS '출고여부'
FROM food_order
ORDER BY order_id;코드를 바꿔보면서 위처럼 작성해야하는걸 깨달았는데. 일단 CASE WHEN으로 구분하는건 괜찮았고, NULL일 때 출고 미정인 부분들은 틀린게 없었는데 out_date 출력할 때 문제가 있었다.
- 널 값을 굳이 다른 값으로 채울 필요가 없었다.
- 날짜 형식 지정을 잘 해줘야했다.
처음에는 COALESCE를 사용해서 널인 경우에는 공백을 출력하도록 해줘야할 것 같아 그렇게 했는데 이렇게 하면 DATE_FORMAT을 하지 않아도 한 것처럼 연월일이 출력되어서 계속 그대로 돌렸는데, 일단 포맷 형식을 지정해줘야했었고, 공백을 출력하게 지정하지 않아도 됐다. 널은 왠지 널이라고 쓰여있어야할 것 같아서 그냥 공백이길래 ' ' 이걸 지정해줘서 문제였다.
다른게 문제인줄 알고 바꾸다가 CASE절도 바꾸어줬는데 마지막에 출고 대기는 그냥 전처럼 ELSE를 쓰는게 나을 것 같기도하다.
Q6. 음식 종류별 즐겨찾기가 가장 많은 식당 출력
SELECT food_type, rest_id, rest_name, favorites
FROM rest_info R1
WHERE favorites >= (SELECT MAX(favorites)
FROM rest_info R2
WHERE R1.food_type = R2.food_type)
ORDER BY food_type DESC;이 문제가 음식 종류별로 이제 즐겨찾기가 가장 많은 행(식당)을 찾고 -> 그 행(식당)의 정보를 출력하는 거였는데 이 즐겨찾기가 가장 많은 행은 MAX()로 찾는다해도, 그것과 비교를하려면 임시 테이블을 둬야할지 서브쿼리를 써야할 지 고민했다. 그리고 윈도우 함수 써서 rank 매기는 것도 방법일 것 같았다. (근데 서브쿼리가 훨씬 나을 것 같았다. 행 몇 개 두는 테이블을 만들 필요는 없을 것 같았다.)
WHERE절에 서브쿼리를 두었는데 (중첩질의) 처음에는 어떻게 조건에 부합하는 행(종류별 즐겨찾기가 가장 많은 그 행)만 뽑아낼 수 있을까 고민을 많이 했다.
상관질의 사용해서 상위 쿼리의 음식 종류와 하위 쿼리 음식 종류가 같은 것들로 1차로 조건 설정을 해주고, 그 안에서 이제 즐겨찾기 수를 토대로 걸러주면 됐다. 처음에는 ALL을 써서 했는데 없이 비교문만 사용해도 잘 돌아갔다.
하위 쿼리에서 해당 음식 종류의 최대 즐겨찾기 수를 찾고 그것과 상위 쿼리의 즐겨찾기 수를 행마다 비교하며 각 음식 종류의 즐겨찾기 수가 가장 많은 음식점 정보를 잘 출력하면 됐다.
Q7. 클래스 문제7: 렌트카 대여 가능 여부 출력
SELECT car_id,
MAX(CASE
WHEN '2022-10-16' BETWEEN start_date AND end_date
THEN '대여중'
ELSE '대여 가능'
END) AS availability
FROM car_rental_company_rental_history
GROUP BY car_id
ORDER BY car_id DESC;차량 대여 기록이 엄청 많고, 빌렸던걸 또 빌리고 막 하는 기록들이 많아서 막상 테이블을 출력해보면 동일한 아이디가 대여중/대여 가능이 번갈아서 막 여러 번 나왔었다.
이걸 동일한 ID의 여러 기록들/값들 중에 어떻게 걸러내야하지? 라는 생각이 들었다. 그루핑하자는 생각이 들었는데 이 경우에는 대여중/대여 가능을 어떻게 선택할 조건을 설정할 수 없으니 랜덤하게 나와버린다.
car_id를 기준으로 그룹화를 하고, SELECT절에서 CASE WHEN을 사용해 날짜 기준으로 조건의 날짜가 start_date나 end_date 사이에 있으면 대여중, 아니면 대여 가능을 반환하도록 했는데, 위의 문제는 해결하지 못했다.
이거 관련해서 AI한테 물어봤을 때는 '대여중' '대여 가능' 중에서 대여 중이 MAX를 썼을 때 더 크니까(뒤) 그렇게 하면 그루핑 후에 대여중+대여가능이 모두 뜰 경우 대여중으로 설정된다고 한다. 그래서 기존에 썼던 코드에서 SELECT의 CASE WHEN을 MAX로 감쌌는데 잘 돌아갔다.
약간 꼼수같은 방법이라 다른 사람들 풀이가 궁금해서 찾아봤는데 CASE WHEN 안에서 또 SELECT를 사용해서 IN으로 이제 하나라도 start_date와 end_date 사이에 해당 날짜가 끼여있는지 확인하면 해결하면 되는 것 같았다.
아래는 다시 풀었을 때...인데 의외로 또 간단하다.
SELECT car_id,
CASE
WHEN car_id IN (SELECT car_id
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE '2022-10-16'
BETWEEN start_date AND end_date )
THEN '대여중'
ELSE '대여 가능'
END AS availability
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C
GROUP BY car_id
ORDER BY car_id DESC;Q8. 클래스 문제8: 1월 판매된 작가별-카테고리별 총액 출력
WITH sales AS (
SELECT A.author_id, A.author_name,
B.category,
SUM(S.sales)*B.price AS total_sales
FROM book_sales S JOIN
book B ON S.book_id = B.book_id JOIN
author A ON A.author_id = B.author_id
WHERE MONTH(S.sales_date) = '1'
GROUP BY B.category, S.book_id
)
SELECT author_id, author_name,
category,
SUM(total_sales) AS total_sales
FROM sales
GROUP BY author_id, category
ORDER BY author_id, category DESC;위에서는 CTE 사용해서 풀어보긴했는데 사실 안써도 풀 수 있는 문제였다.
일단은 작가와 카테고리 별로 묶어서 총 판매액을 출력하면 되는 문제였는데 그룹화를 어떤 기준으로 할 지 고민했었다.
이 문제에서도 위에처럼 일단 월 설정하는거는 다시해야할 것 같다.
일단은 위에 문제를 풀 때는 카테고리별로 묶고, 그 안에서 책의 아이디 별로 묶어서 해당 책이 얼마나 팔렸는지를 먼저 기록하고, 그걸 토대로 다시 작가 아이디, 카테고리별로 묶었는데 이거를 사실 그냥 GROUP BY author_id, category, book_id로 하면 됐다.
근데 이걸 처음에 했다가 위처럼 테이블을 하나 더 만든 이유는 SUM()을 사용해서 계산할 때, book_id 별로 다른 가격을 반영해서 총액 계산하는게 어려워서 였다.
SUM(S.sales) 이렇게 해서 판매한 개수를 얻어오고 이 뒤에 곱셈을 해서 다른 가격 반영하는게 쉽지 않았는데 SUM(S.sales*B.price) 이런식으로? SUM() 안에 하나가 아니라 수식을 넣어도 되는걸 다 풀고 찾아보다 알았다....
다시 풀게 되면 아래처럼 풀 것 같다.
SELECT A.author_id, A.author_name, B.category,
SUM(S.sales * B.price) AS total_sales
FROM book B JOIN
book_sales S ON B.book_id = S.book_id JOIN
author A ON B.author_id = A.author_id
WHERE S.sales_date BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY A.author_id, B.category
ORDER BY author_id, category DESC;약간 SUM을 하게되면, 그룹한것들을 총 합계를 구하는거니까 SUM(S.sales*B.price)를 하게 되면 이제 행별로 각각 곱한 후에 -> 작가&카테고리별로 묶었으니 그 기준으로 다 합하고 행 줄이기. 이런식으로 진행되는 것 같다.
Q9. 클래스 문제9: 게시글의 수가 3개 이상인 고객 정보 출력
WITH users AS (
SELECT U.user_id AS user_id
FROM used_goods_board B JOIN
used_goods_user U ON
B.writer_id = U.user_id
GROUP BY U.user_id
HAVING COUNT(DISTINCT B.board_id) >= 3)
SELECT U.user_id, U.nickname,
CONCAT(U.city,' ',U.street_address1,' ', U.street_address2) AS '전체주소',
CONCAT(LEFT(TLNO,3),'-',SUBSTR(TLNO,4,4),'-',RIGHT(TLNO,4)) AS '전화번호'
FROM used_goods_user U LEFT JOIN
users ON
U.user_id = users.user_id
WHERE users.user_id IS NOT NULL
ORDER BY U.user_id DESC;위에도 CTE 사용하기는 하는데, 사실 그럴 필요는 없었을 것 같긴하다. 그냥 분리하면 아래를 더 간단하게 쓸 수 있을 것 같았다...?
일단 위에서 3번 이상 게시글을 올린 사용자의 아이디만 테이블에 남겼다. 그룹화하고, HAVING 사용해서 이 부분은 풀었다.
그 후에 그렇게 만든 테이블을 기존 테이블하고 조인한 뒤에 널값 아닌 사용자의 정보들만 출력했다. 사실 CTE 만드는건 불필요한데 이번주 내용에서 배운 거라 사용하고 조인도 사용해봤다.
실제로 다시 풀게되면 그냥 바로 GROUP BY -> HAVING 쓴 뒤에 SELECT에서 조건대로 처리할 것 같다. 아래처럼.
문제에서 신경써서 처리할 부분은 CONCAT() 써서 요구조건대로 문자열 만드는 부분이었다. 전화 번호 사이에 '-' 넣는 것이나 주소 연결하는 것만 잘 처리해주었다. (시키는대로 하면 되는데 그 조건이 은근 자잘해서 귀찮고 또 완전 안똑같으면 틀려서 몇 번 다시 풀었다.)
SELECT U.user_id, U.nickname,
CONCAT(U.city, ' ', U.street_address1, ' ', U.street_address2) AS '전체주소',
CONCAT(LEFT(U.tlno,3), '-', SUBSTR(U.tlno,4,4), '-', RIGHT(U.tlno,4)) AS '전화번호'
FROM used_goods_board B JOIN
used_goods_user U ON B.writer_id = U.user_id
GROUP BY B.writer_id
HAVING COUNT(DISTINCT B.board_id) >= 3
ORDER BY U.user_id DESC;CTE 만들지 않고 풀면 위와 같다. 이게 더 좋은 방법인 것 같긴하다, 위는 그냥 억지로 쓴 거...같고? 주소 연결할 때 공백을 빼먹어서 몇 번 다시 풀었었다....
