1. 주문량이 많은 아이스크림들 조회하기
-- 이렇게 하면 SUM값이 정확하게 안나옴
SELECT FLAVOR
FROM (
SELECT A.FLAVOR
, SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF A
JOIN JULY B
ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR
ORDER BY TOTAL_ORDER DESC
)
WHERE ROWNUM <= 3
-- 이렇게 해야 SUM값이 정확하게 나옴
SELECT FLAVOR
FROM (
SELECT A.FLAVOR
, SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER) AS TOTAL_ORDER
FROM (
-- strawberry SUM 3100
SELECT FLAVOR
, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF
GROUP BY FLAVOR
) A
JOIN (
-- strawberry SUM 740
SELECT FLAVOR
, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY
GROUP BY FLAVOR
) B
ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR
ORDER BY TOTAL_ORDER DESC
)
WHERE ROWNUM <= 3
2. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.
-- 자동차 종류가 세단 또는 SUV
-- 2022년 11월 1일 ~ 30일 대여 가능
-- 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차
SELECT CAR_ID
, CAR_TYPE
, FEE
FROM (
SELECT A.CAR_ID
, A.CAR_TYPE
, A.DAILY_FEE * (1 - B.DISCOUNT_RATE/100) * 30 AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON A.CAR_TYPE = B.CAR_TYPE
AND B.DURATION_TYPE = '30일 이상'
WHERE A.CAR_TYPE IN('세단', 'SUV')
AND A.CAR_ID NOT IN(
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_CHAR(END_DATE, 'YYYY-MM-DD') > '2022-11-01'
)
)
WHERE FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC
-----------------------------------------------------------------------------------
SELECT CAR_ID
, C1.CAR_TYPE
, DAILY_FEE * (1 - DISCOUNT_RATE/100) * 30 AS FEE
FROM CAR_RENTAL_COMPANY_CAR C1
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P1
ON C1.CAR_TYPE = P1.CAR_TYPE
AND P1.DURATION_TYPE = '30일 이상'
WHERE C1.CAR_TYPE IN('세단', 'SUV')
AND DAILY_FEE * (1 - DISCOUNT_RATE/100) * 30 >= 500000
AND DAILY_FEE * (1 - DISCOUNT_RATE/100) * 30 < 2000000
AND CAR_ID NOT IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_CHAR(END_DATE, 'YYYY-MM-DD') > '2022-11-01'
)
ORDER BY 3 DESC, 2, 1 DESC;
3. 5월 식품들의 총매출 조회하기
FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.
SELECT A.PRODUCT_ID
, A.PRODUCT_NAME
, SUM(A.PRICE*B.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT A
JOIN FOOD_ORDER B
ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE TO_CHAR(B.PRODUCE_DATE, 'YYYYMM') = '202205'
GROUP BY A.PRODUCT_ID, A.PRODUCT_NAME, A.PRICE
ORDER BY TOTAL_SALES DESC, PRODUCT_ID
4. 그룹별 조건에 맞는 식당 목록 출력하기
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
-- 내가 한거 -> 3명이 공동1위여서 RANK() 함수 사용함 3명의 데이터를 출력함
SELECT A.MEMBER_NAME
, B.REVIEW_TEXT
, TO_CHAR(B.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
FROM MEMBER_PROFILE A
JOIN REST_REVIEW B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID IN (
SELECT MEMBER_ID
FROM (
SELECT A.MEMBER_ID
, RANK() OVER (ORDER BY COUNT(B.MEMBER_ID) DESC) AS RANK
FROM MEMBER_PROFILE A
JOIN REST_REVIEW B
ON A.MEMBER_ID = B.MEMBER_ID
GROUP BY A.MEMBER_ID
)
WHERE RANK = 1
)
ORDER BY REVIEW_DATE, REVIEW_TEXT
-- 이 방법은 리뷰개수가 가장많은 첫번째 회원 한 사람의 데이터만 가져옴
SELECT A.MEMBER_NAME
, B.REVIEW_TEXT
, TO_CHAR(B.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
FROM MEMBER_PROFILE A
JOIN REST_REVIEW B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID IN (
SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(REVIEW_ID) DESC
FETCH FIRST 1 ROWS ONLY
)
ORDER BY 3,2
5. 상품 별 오프라인 매출 구하기
PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.
SELECT A.PRODUCT_CODE
, SUM(A.PRICE * B.SALES_AMOUNT) AS SALES
FROM PRODUCT A
LEFT JOIN OFFLINE_SALE B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY A.PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE
6. 상품을 구매한 회원 비율 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
SELECT EXTRACT(YEAR FROM A.SALES_DATE) AS YEAR
, EXTRACT(MONTH FROM A.SALES_DATE) AS MONTH
, COUNT(DISTINCT A.USER_ID) AS PUCHASED_USERS
, ROUND(COUNT(DISTINCT A.USER_ID) / (SELECT COUNT(USER_ID)
FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') = '2021')
, 1) AS PUCHASED_RATIO
FROM ONLINE_SALE A
JOIN USER_INFO B
ON A.USER_ID = B.USER_ID
WHERE TO_CHAR(B.JOINED, 'YYYY') = '2021'
GROUP BY EXTRACT(YEAR FROM A.SALES_DATE), EXTRACT(MONTH FROM A.SALES_DATE)
ORDER BY YEAR, MONTH
