프로그래머스 | SQL 스터디 3

SELECT

평균 일일 대여 요금 구하기(Lv1)

문제 설명

​

문제

CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.

​

풀이

SELECT로 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정

AVG(DAILY_FEE)를 ROUND( ,0)의 빈칸에 넣고 칼럼명 AVERAGE_FEE로 지정

​

WHERE문에서 CAR_TYPE ='SUV'로 차의 종류 설정

SELECT ROUND( AVG(DAILY_FEE) ,0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = 'SUV';

​

---

SUM, MAX, MIN

최댓값 구하기(Lv1)

문제 설명

​

문제

가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

​

풀이

SELECT에서 MAX(DATETIME)로 가장 최근에 들어온 동물의 시간을 조회할 수 있음

SELECT MAX(DATETIME)
FROM ANIMAL_INS;

 

---

GROUP BY

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기(Lv2)

문제 설명

​

문제

CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

​

​

풀이

SELECT에서 CAR_TYPE, COUNT(*)으로 차의 종류와 해당하는 차의 개수를 조회

​

WHERE에서 통풍시트, 열선시트, 가죽시트가 포함되어 있는지 설정

​

GROUP BY에서 CAR_TYPE별로 그룹화

​

ORDER BY에서 CAR_TYPE별로 오름차순 정렬

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%'
   OR OPTIONS LIKE '%열선시트%'
   OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE;

---

IS NULL

이름이 있는 동물의 아이디(Lv1)

문제 설명

​

문제

동물 보호소에 들어온 동물 중, 이름이 있는 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

​

풀이

SELECT에서 ANIMAL_ID를 조회​

​

WHERE에서 이름이 있는 동물을 원하기에 NAME IS NOT NULL로 설정

​

ORDER BY에서 동물 ID 기준으로 오름차순 정렬

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID ASC;

---

JOIN

5월 식품들의 총매출 조회하기(Lv4)

문제 설명

​

문제

FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요

​

풀이

SELECT에서 식품ID, 식품 이름, 총매출을 조회

여기서 총매출은 가격 * 주문량이며, 주문량의 경우 아래와 같이 5월 안에서도 다른 날 주문한 것들이 존재하기에 SUM을 씌워준다

​

두 테이블을 모두 사용해야하므로, 공통 컬럼인 PRODUCT_ID로 JOIN을 한다

​

WHERE에서 22년 5월의 생산일자의 제품들만 조회하기 위해 쿼리문 설정

​

GROUP BY에서 PRODUCT_ID와 PRODUCT_NAME을 기준으로 그룹화

※처음에는 group by를 쓰지 않아 틀렸었는데, 집계함수를 사용하기 때문에 집계의 기준이 되는 열을 지정해야 한다!!

​

ORDER BY에서 총매출 기준 내림차순 정렬, 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, PRICE*SUM(O.AMOUNT) TOTAL_SALES
FROM FOOD_PRODUCT P
JOIN FOOD_ORDER O ON O.PRODUCT_ID = P.PRODUCT_ID
WHERE O.PRODUCE_DATE LIKE '2022-05%'
GROUP BY P.PRODUCT_ID, P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, PRODUCT_ID;

---

String, Date

★자동차 대여 기록 별 대여 금액 구하기(Lv4)

문제 설명

​

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

​

​

풀이

SELECT에서 HISTROY_ID와 FEE를 조회

여기서 FEE는 DAILY_FEE * 대여기간 * 할인율(할인대상일때만) 의 식으로 설정

​

JOIN으로 CAR_RENTAL_COMPANY_RENTAL_HISTORY, CAR_RENTAL_COMPANY_DISCOUNT_PLAN를 연결

※ 여기서 기간의 차이를 계산할 때 +1이 되어야 하기 때문에 DURATION_TYPE 또한 새로 정의해줌

​

WHERE에서 CAR_TYPE이 트럭으로 설정

​

ORDER BY에서 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

SELECT HISTORY_ID,
    ROUND(DAILY_FEE * (DATEDIFF(END_DATE, START_DATE) + 1) * ( 100 - IFNULL(DISCOUNT_RATE, 0) ) / 100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
    AND D.DURATION_TYPE = (CASE
                                WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= '90' THEN '90일 이상'
                                WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= '30' THEN '30일 이상'
                                WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= '7' THEN '7일 이상'
                                ELSE NULL
                            END)
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;