아찌방 하우스

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

풀이 - 나의 생각

먼저 8~10월 총 대여 횟수가 5회 이상인 CAR_ID를 찾아낸 후

JOIN한다.

그 후에 GROUP BY로 달 별, CAR_ID 별로 묶어서

그 숫자를 센다.

코드

SELECT MONTH(START_DATE) MONTH, C.CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C
JOIN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE MONTH(START_DATE) IN (8, 9, 10)
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5) SUB ON C.CAR_ID = SUB.CAR_ID
WHERE MONTH(START_DATE) IN (8, 9, 10)
GROUP BY MONTH, C.CAR_ID
HAVING COUNT(*) >= 1
ORDER BY MONTH, C.CAR_ID DESC;

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

풀이 - 나의 생각

서브쿼리를 조인하여 문제를 해결했습니다.

코드

SELECT P.*
FROM PLACES P
JOIN (
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(*) > 1
) Sub ON P.HOST_ID = Sub.HOST_ID
ORDER BY P.ID;

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

풀이 - 나의 생각

코드

SELECT CONCAT('/home/grep/src/',BOARD_ID,'/',FILE_ID,FILE_NAME,FILE_EXT) FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID LIKE (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
)
ORDER BY FILE_ID DESC

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

풀이 - 나의 생각

코드

SELECT 
    CAR_ID,
    CASE 
        WHEN '대여중' IN(
            SELECT 
                CASE
                    WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN '대여중'
                    ELSE '대여 가능'
                END RANTAL
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY SUB
            WHERE SUB.CAR_ID = ORIGINAL.CAR_ID
        ) THEN '대여중'
        ELSE '대여 가능'
    END AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY ORIGINAL
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

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

풀이 - 나의 생각

코드

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

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

풀이 - 나의 생각

코드

SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM REST_INFO R
JOIN (
    SELECT FOOD_TYPE, MAX(FAVORITES) MAX
    FROM REST_INFO
    GROUP BY FOOD_TYPE) M ON R.FOOD_TYPE = M.FOOD_TYPE
WHERE R.FAVORITES = MAX
ORDER BY R.FOOD_TYPE DESC

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

풀이 - 나의 생각

코드

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-10%'
AND CAR_ID IN(
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE LIKE '세단')
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

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

풀이 - 나의 생각

코드

SELECT WRITER_ID, (
    SELECT NICKNAME
    FROM USED_GOODS_USER
    WHERE USER_ID LIKE WRITER_ID
) NICKNAME,SUM(PRICE) TOTAL_SALES
FROM USED_GOODS_BOARD
WHERE STATUS LIKE 'DONE'
GROUP BY WRITER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES