SQLD 01

https://school.programmers.co.kr/learn/challenges?order=recent&languages=mysql%2Coracle

 

1. https://school.programmers.co.kr/learn/courses/30/lessons/164673

where에서 댓글작성일이 아니라, 게시글 작성일임

oracle과 mysql의 to_char 하는 방식이 다름

 

MySQL

SELECT a.TITLE, b.BOARD_ID, b.REPLY_ID, b.WRITER_ID, b.CONTENTS, DATE_FORMAT(b.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
FROM used_goods_board a join used_goods_reply b on a.board_id = b.board_id
WHERE YEAR(a.created_date) = '2022' AND MONTH(a.created_date) = '10'
ORDER BY b.created_date, a.title;

Oracle

SELECT a.TITLE, b.BOARD_ID, b.REPLY_ID, b.WRITER_ID, b.CONTENTS, TO_CHAR(b.created_date, 'YYYY-MM-DD') as CREATED_DATE
FROM used_goods_board a inner join used_goods_reply b on a.board_id = b.board_id
WHERE TO_CHAR(a.created_date, 'YYYY-MM') = '2022-10'
ORDER BY b.created_date, a.title

 

2. https://school.programmers.co.kr/learn/courses/30/lessons/164672

Oracle

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, CASE 
                                            WHEN STATUS = 'SALE' THEN '판매중'
                                            WHEN STATUS = 'RESERVED' THEN '예약중'
                                            WHEN STATUS = 'DONE' THEN '거래완료' 
                                            END as STATUS
FROM USED_GOODS_BOARD
WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
ORDER BY BOARD_ID desc;

MySQL

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, CASE 
                                            WHEN STATUS = 'SALE' THEN '판매중'
                                            WHEN STATUS = 'RESERVED' THEN '예약중'
                                            WHEN STATUS = 'DONE' THEN '거래완료' 
                                            END as STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID desc;

 

3. https://school.programmers.co.kr/learn/courses/30/lessons/164671

MySQL

SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) as FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    WHERE VIEWS = (
        SELECT MAX(VIEWS)
        FROM USED_GOODS_BOARD
    )
)
ORDER BY FILE_ID desc;

 

Oracle

SELECT '/home/grep/src/' || BOARD_ID || '/' || FILE_ID || FILE_NAME || FILE_EXT as FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    WHERE VIEWS = (
        SELECT MAX(VIEWS)
        FROM USED_GOODS_BOARD
    )
)
ORDER BY FILE_ID desc;

 

4. https://school.programmers.co.kr/learn/courses/30/lessons/164670

Oracle

SELECT b.USER_ID, b.NICKNAME, 
    (b.CITY || ' ' || STREET_ADDRESS1 || ' ' || STREET_ADDRESS2) as 전체주소, 
    (SUBSTR(b.TLNO, 1, 3) || '-' || SUBSTR(b.TLNO, 4, 4) || '-' || SUBSTR(b.TLNO, 8, 4)) as 전화번호
FROM USED_GOODS_USER b
WHERE b.USER_ID IN (
    SELECT WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(WRITER_ID) >= 3
)
ORDER BY b.USER_ID DESC;

MySQL

SELECT b.USER_ID, b.NICKNAME, 
    CONCAT(b.CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) as 전체주소, 
    CONCAT(SUBSTR(b.TLNO, 1, 3), '-', SUBSTR(b.TLNO, 4, 4), '-', SUBSTR(b.TLNO, 8, 4)) as 전화번호
FROM USED_GOODS_USER b
WHERE b.USER_ID IN (
    SELECT WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(WRITER_ID) >= 3
)
ORDER BY b.USER_ID DESC;

 

5. https://school.programmers.co.kr/learn/courses/30/lessons/164668

MySQL, Oracle 동일

SELECT b.USER_ID, b.NICKNAME, c.TOTAL_SALES as TOTAL_SALES
FROM USED_GOODS_USER b INNER JOIN (
    SELECT WRITER_ID, SUM(PRICE) as TOTAL_SALES
    FROM USED_GOODS_BOARD
    WHERE STATUS = 'DONE'
    GROUP BY WRITER_ID        
    HAVING SUM(PRICE) >= 700000    
) c ON b.USER_ID = c.WRITER_ID
ORDER BY TOTAL_SALES;

 6. https://school.programmers.co.kr/learn/courses/30/lessons/157343

MySQL, Oracle 동일

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR 
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC

 

7. https://school.programmers.co.kr/learn/courses/30/lessons/157342

MySQL : 날짜를 단순히 빼는걸로 끝내버리면, 대여일이 하루가 줄어들게 됨.(첫날, 마지막날도 빌린 것.) ★

그리고, 날짜와 날짜를 빼도 계산되지 않음. DATEDIFF 사용 필요

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) as AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

Oracle : 마찬가지 이유로 하루 더함. 하지만, 날짜 그냥 빼도 됨

SELECT CAR_ID, ROUND(AVG((END_DATE - START_DATE)+1), 1) as AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG((END_DATE - START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

 

8. https://school.programmers.co.kr/learn/courses/30/lessons/157341

Oracle

SELECT DISTINCT a.CAR_ID as CAR_ID
FROM CAR_RENTAL_COMPANY_CAR a JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b
    ON a.CAR_ID = b.CAR_ID
WHERE a.CAR_TYPE = '세단' AND TO_CHAR(b.START_DATE, 'MM') = '10'
ORDER BY a.CAR_ID DESC

MySQL

SELECT DISTINCT a.CAR_ID as CAR_ID
FROM CAR_RENTAL_COMPANY_CAR a JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b
    ON a.CAR_ID = b.CAR_ID
WHERE a.CAR_TYPE = '세단' AND DATE_FORMAT(b.START_DATE, '%m') = '10'
ORDER BY a.CAR_ID DESC

 

9. https://school.programmers.co.kr/learn/courses/30/lessons/157340

MySQL

SELECT a.CAR_ID, (
    CASE WHEN b.CAR_ID IS NOT NULL THEN '대여중'
        ELSE '대여 가능'
    END
) as AVAILABILITY
FROM (
    SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) a LEFT OUTER JOIN (
    SELECT CAR_ID
    FROM (
        SELECT CAR_ID,
            (CASE
                WHEN START_DATE <= '2022-10-16' AND '2022-10-15' < END_DATE THEN '대여중'
                ELSE '대여 가능'
            END) AS AVAILABILITY
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    ) as IS_대여중
    WHERE AVAILABILITY = '대여중'
) b ON a.CAR_ID = b.CAR_ID
ORDER BY a.CAR_ID DESC;

주먹구구식으로 풀었으나, 딱 봐도 코드가 매우 더러움.

더 깔끔한 방법을 구상해보자.

 

GPT에게 설명에 설명을 거듭해서 코드들을 받아왔다.

SELECT CAR_ID, 
    CASE 
        WHEN EXISTS (
            SELECT * 
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
            WHERE CAR_ID = a.CAR_ID 
            AND START_DATE <= '2022-10-16' 
            AND END_DATE > '2022-10-15'
        ) THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM (
    SELECT DISTINCT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) a
ORDER BY CAR_ID DESC;

--=====================================

SELECT CAR_ID, 
    CASE 
        WHEN SUM(CASE WHEN START_DATE <= '2022-10-16' AND END_DATE > '2022-10-15' THEN 1 ELSE 0 END) > 0 THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

# SELECT CAR_ID, (CASE 
#     # WHEN START_DATE <= TO_DATE('2022/10/16', '%Y/%m/%d') AND TO_DATE('2022/10/16', '%Y/%m/%d') < END_DATE THEN '대여 가능'
#     WHEN START_DATE <= STR_TO_DATE('2022/10/16', '%Y/%m/%d') AND STR_TO_DATE('2022/10/17', '%Y/%m/%d') < END_DATE THEN '대여 가능'
#     ELSE '대여중'
#     END
# ) as AVAILABILITY
# FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
# ORDER BY CAR_ID DESC;

# SELECT * FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY;

#일단 대여 기록마다 검사하기 때문에 대여중, 대여 가능이 여러개가 쭈루룩 나올 수밖에 없는것으로 보인다.
#GROUP BY를 해야 할지.. 아니면 어떻게 해야할지 좀 생각을 해보자면...
#대여중이 여러개가 나오는 이유를 잘 모르겠다. 부등호를 잘못 체크했나? start_date보다 이후이고, end_date보다 이전 맞지 않나?
#꼭 대여 가능은 하나씩만 출력되는데? 왜 그런거지?
# WHEN START_DATE <= STR_TO_DATE('2022/10/16', '%Y/%m/%d') AND STR_TO_DATE('2022/10/16', '%Y/%m/%d') < END_DATE THEN '대여 가능'
# 경우의 수를 따져보자.
# 이전에 빌려서 이전에 반납 (대여 가능)
# 이전에 빌려서 이후에 반납 (대여 불가능)
# 이후에 빌려서 이후에 반납 (대여 가능)
# 
# GPT의 답
# SELECT CAR_ID,
#     (CASE
#         WHEN START_DATE <= '2022-10-16' AND '2022-10-16' < END_DATE THEN '대여중'
#         ELSE '대여 가능'
#     END) AS AVAILABILITY
# FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
# ORDER BY CAR_ID DESC;

#내가 바보같이 대여 가능과 대여중을 거꾸로 썼었다 ㅋㅋ
#이제 이걸 subquery를 써서 대여중인 값이 있으면 대여중, 없으면 대여가능을 출력하도록 하면 되지 않을까?

# SELECT CAR_ID
# FROM (
#     SELECT CAR_ID,
#         (CASE
#             WHEN START_DATE <= '2022-10-16' AND '2022-10-16' < END_DATE THEN '대여중'
#             ELSE '대여 가능'
#         END) AS AVAILABILITY
#     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
# ) as IS_대여중
# WHERE AVAILABILITY = '대여중'

# SELECT a.CAR_ID, (
#     CASE WHEN b.CAR_ID IS NOT NULL THEN '대여중'
#         ELSE '대여 가능'
#     END
# ) as AVAILABILITY
# FROM (
#     SELECT DISTINCT CAR_ID
#     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
# ) a LEFT OUTER JOIN (
#     SELECT CAR_ID
#     FROM (
#         SELECT CAR_ID,
#             (CASE
#                 WHEN START_DATE <= '2022-10-16' AND '2022-10-15' < END_DATE THEN '대여중'
#                 ELSE '대여 가능'
#             END) AS AVAILABILITY
#         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
#     ) as IS_대여중
#     WHERE AVAILABILITY = '대여중'
# ) b ON a.CAR_ID = b.CAR_ID
# ORDER BY a.CAR_ID DESC;

SELECT CAR_ID, 
    CASE 
        WHEN SUM(CASE WHEN START_DATE <= '2022-10-16' AND END_DATE > '2022-10-15' THEN 1 ELSE 0 END) > 0 THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

 

10.https://school.programmers.co.kr/learn/courses/30/lessons/157339

Oracle

/*
테이블 : CAR_RENTAL_COMPANY_CAR, CAR_RENTAL_COMPANY_RENTAL_HISTORY, CAR_RENTAL_COMPANY_DISCOUNT_PLAN  
1.대여사에서 대여중인 자동차 정보, 2.차 대여 기록 정보, 3.차종별 대여기간, 종류별 할인정책

문제
차종은 세단orSUV
2022/11/1~2022/11/30 대여 가능
30일간 대여 금액 50이상~200만원미만

해당하는 자동차들의 CAR_ID	CAR_TYPE	FEE

결과는 대여 금액을 기준으로 내림차순 정렬
대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬
자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬

편의상 테이블 3개는 a, b, c라고 부르겠다.

a, b의 공통 컬럼 : car_id
a, c의 공통 컬럼 : car_type

모두 JOIN을 한 후에 조건대로 걸러내도 되겠지만, 그냥 조건에 해당하는 애들만 남기고, 계속 INNER JOIN 해도 되지 않을까?
*/

/*
--우선 차종이 세단, SUV
SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE IN ('세단', 'SUV')


--11월1일~11월30일 대여 가능한 차량
--모든 대여일자가 11월을 침범하지 않는 차량들만 가능.
--불가능한 경우 : 대여일 반납일 사이에 11월이 들어가는 경우, 11월 중에 대여, 반납이 있는 경우
SELECT *
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID NOT IN(
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')
    AND END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD')
)


--할인도 차종은 세단, SUV에다가 기간은 30일 이상
SELECT *
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE IN ('세단', 'SUV')
AND duration_type IN ('30일 이상');
*/

-- SELECT DISTINCT a.CAR_ID, a.CAR_TYPE, (a.DAILY_FEE*30) * ((100 - c.DISCOUNT_RATE)/100) AS FEE
-- FROM (    
--     SELECT *
--     FROM CAR_RENTAL_COMPANY_CAR
--     WHERE CAR_TYPE IN ('세단', 'SUV')
-- ) a INNER JOIN (
--     SELECT *
--     FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
--     WHERE CAR_TYPE IN ('세단', 'SUV')
--     AND duration_type IN ('30일 이상')
-- ) c ON a.CAR_TYPE = c.CAR_TYPE
--     INNER JOIN (
--     SELECT *
--     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
--     WHERE CAR_ID NOT IN(
--         SELECT CAR_ID
--         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
--         WHERE START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')
--         AND END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD')
--     )
-- ) b ON a.CAR_ID = b.CAR_ID
-- WHERE (a.DAILY_FEE*30) * ((100 - c.DISCOUNT_RATE)/100) >= 500000 AND (a.DAILY_FEE*30) * ((100 - c.DISCOUNT_RATE)/100) < 2000000
-- ORDER BY FEE DESC, CAR_ID

--왜 안되나 했더니, 금액 기준으로 내림차순, 자동차 종류로 오름차순 정렬 필요
--금액도 50~200만 사이

--이번엔 JOIN부터 전부 다 하고 나서
SELECT DISTINCT a.CAR_ID, a.CAR_TYPE, (a.DAILY_FEE*30) * ((100 - c.DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR a 
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b
    ON a.CAR_ID = b.CAR_ID
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN c
    ON a.CAR_TYPE = c.CAR_TYPE
WHERE a.CAR_TYPE IN ('세단', 'SUV')
    AND c.duration_type IN ('30일 이상')
    AND b.CAR_ID NOT IN(
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD')
        AND END_DATE >= TO_DATE('2022-11-01', 'YYYY-MM-DD')
    )
    AND (a.DAILY_FEE*30) * ((100 - c.DISCOUNT_RATE)/100) >= 500000 AND (a.DAILY_FEE*30) * ((100 - c.DISCOUNT_RATE)/100) < 2000000
ORDER BY FEE DESC, a.CAR_ID