1. 서울에 위치한 식당 목록 출력하기
SELECT
RI.REST_ID,
RI.REST_NAME,
RI.FOOD_TYPE,
RI.FAVORITES,
RI.ADDRESS,
ROUND(AVG(RR.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO RI
JOIN REST_REVIEW RR
ON RI.REST_ID = RR.REST_ID
WHERE RI.ADDRESS LIKE '서울%'
GROUP BY
RI.REST_ID,
RI.REST_NAME,
RI.FOOD_TYPE,
RI.FAVORITES,
RI.ADDRESS
ORDER BY SCORE DESC, RI.FAVORITES DESC;
SQL
복사
2. 오프라인/온라인 판매 데이터 통합하기
-- 코드를 입력하세요
SELECT
DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID,
USER_ID,
SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE < '2022-04-01'
UNION ALL
SELECT
DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID,
NULL AS USER_ID,
SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE < '2022-04-01'
ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC;
SQL
복사
3. 특정 세대의 대장균 찾기
SELECT C3.ID
FROM ECOLI_DATA AS C3
JOIN ECOLI_DATA AS C2 ON C3.PARENT_ID = C2.ID
JOIN ECOLI_DATA AS C1 ON C2.PARENT_ID = C1.ID
WHERE C1.PARENT_ID IS NULL
ORDER BY C3.ID;
SQL
복사
4. 저자 별 카테고리 별 매출액 집계하기
SELECT
a.AUTHOR_ID,
a.AUTHOR_NAME,
b.CATEGORY,
SUM(bs.SALES * b.PRICE) AS TOTAL_SALES
FROM BOOK_SALES bs
JOIN BOOK b
ON bs.BOOK_ID = b.BOOK_ID
JOIN AUTHOR a
ON b.AUTHOR_ID = a.AUTHOR_ID
WHERE bs.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY
a.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY
ORDER BY
a.AUTHOR_ID ASC,
b.CATEGORY DESC;
SQL
복사
5. 식품분류별 가장 비싼 식품의 정보 조회하기
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
SELECT CATEGORY, MAX(PRICE)
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
GROUP BY CATEGORY
)
ORDER BY MAX_PRICE DESC;
-- 윈도우 함수 사용
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM (
SELECT *,
RANK() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS rnk
FROM FOOD_PRODUCT
WHERE CATEGORY INT ('과자', '국', '김치', '식용유')
) AS ranked
WHERE rnk = 1
ORDER BY MAX_PRICE DESC;
SQL
복사