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
복사
6. 년, 월, 성별 별 상품 구매 회원 수 구하기
SELECT
YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
U.GENDER,
COUNT(DISTINCT U.USER_ID) AS USERS
FROM USER_INFO U
JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE GENDER IN (1, 0)
GROUP BY YEAR, MONTH, U.GENDER
ORDER BY YEAR, MONTH, U.GENDER;
-- 참고
WITH monthly_users AS (
SELECT
YEAR(O.SALES_DATE) AS YEAR,
MONTH(O.SALES_DATE) AS MONTH,
O.USER_ID
FROM ONLINE_SALE O
/* WHERE O.SALES_DATE >= '2021-01-01' AND O.SALES_DATE < '2022-01-01' */
GROUP BY YEAR, MONTH, O.USER_ID
)
SELECT
MU.YEAR,
MU.MONTH,
U.GENDER,
COUNT(*) AS USERS
FROM monthly_users MU
JOIN USER_INFO U
ON U.USER_ID = MU.USER_ID
WHERE U.GENDER IN (0, 1) -- 또는 U.GENDER IS NOT NULL
GROUP BY MU.YEAR, MU.MONTH, U.GENDER
ORDER BY MU.YEAR, MU.MONTH, U.GENDER;
SQL
복사
7. 입양 시각 구하기 (2)
SELECT H.h AS HOUR,
COALESCE(A.cnt, 0) AS COUNT
FROM (
SELECT 0 AS h UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) AS H
LEFT JOIN (
SELECT HOUR(DATETIME) AS h, COUNT(*) AS cnt
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
) AS A
ON A.h = H.h
ORDER BY H.h;
SQL
복사
8. 언어별 개발자 분류하기
SELECT
CASE
WHEN (d.SKILL_CODE & py.py_code) > 0
AND (d.SKILL_CODE & fe.front_mask) > 0 THEN 'A'
WHEN (d.SKILL_CODE & cs.cs_code) > 0 THEN 'B'
WHEN (d.SKILL_CODE & fe.front_mask) > 0 THEN 'C'
END AS GRADE,
d.ID,
d.EMAIL
FROM DEVELOPERS d
-- 카테고리 'Front end'의 모든 CODE를 합쳐 1개의 비트마스크로 만든다.
JOIN (
SELECT SUM(CODE) AS front_mask
FROM SKILLCODES
WHERE CATEGORY = 'Front End'
) fe
-- Python, C#의 코드 값을 각각 가져온다.
JOIN (
SELECT CODE AS py_code
FROM SKILLCODES
WHERE NAME = 'Python'
) py
JOIN (
SELECT CODE AS cs_code
FROM SKILLCODES
WHERE NAME = 'C#'
) cs
-- 등급이 부여되는 경우만 남긴다(A/B/C)
WHERE
((d.SKILL_CODE & py.py_code) > 0 AND (d.SKILL_CODE & fe.front_mask) > 0)
OR ((d.SKILL_CODE & cs.cs_code) > 0)
OR ((d.SKILL_CODE & fe.front_mask) > 0)
ORDER BY GRADE, d.ID;
SQL
복사
9. 연간 평가점수에 해당하는 평가 등급 및 성과급 조회하기
SELECT
E.EMP_NO,
E.EMP_NAME,
CASE
WHEN G.AVG_SCORE >= 96 THEN 'S'
WHEN G.AVG_SCORE >= 90 THEN 'A'
WHEN G.AVG_SCORE >= 80 THEN 'B'
ELSE 'C'
END AS GRADE,
/* 정수로 만들고 싶으면 CAST(...) AS SIGNED 혹은 FLOOR(...) 사용 */
CASE
WHEN G.AVG_SCORE >= 96 THEN E.SAL * 0.20
WHEN G.AVG_SCORE >= 90 THEN E.SAL * 0.15
WHEN G.AVG_SCORE >= 80 THEN E.SAL * 0.10
ELSE 0
END AS BONUS
FROM HR_EMPLOYEES E
JOIN (
SELECT EMP_NO, AVG(SCORE) AS AVG_SCORE
FROM HR_GRADE
GROUP BY EMP_NO
) G
ON G.EMP_NO = E.EMP_NO
ORDER BY E.EMP_NO;
SQL
복사
10. 주문량이 많은 아이스크림들 조회하기
SELECT
f.FLAVOR
FROM FIRST_HALF f
LEFT JOIN (
SELECT FLAVOR, SUM(TOTAL_ORDER) AS july_total
FROM JULY
GROUP BY FLAVOR
) j ON j.FLAVOR = f.FLAVOR
GROUP BY f.FLAVOR, f.TOTAL_ORDER, j.july_total
ORDER BY (f.TOTAL_ORDER + COALESCE(j.july_total, 0)) DESC
LIMIT 3;
SQL
복사
11. 5월 식품들의 총매출 조회하기
SELECT
F.PRODUCT_ID,
F.PRODUCT_NAME,
SUM(F.PRICE * O.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT F
JOIN FOOD_ORDER O
ON F.PRODUCT_ID = O.PRODUCT_ID
WHERE O.PRODUCE_DATE
BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY F.PRODUCT_ID, F.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, F.PRODUCT_ID ASC;
-- 성능 고려
-- 포함(>=) ~ 배제(<) 형태를 권장
SELECT
p.PRODUCT_ID,
p.PRODUCT_NAME,
p.PRICE * s.total_amount AS TOTAL_SALES
FROM FOOD_PRODUCT p
JOIN (
SELECT PRODUCT_ID, SUM(AMOUNT) AS total_amount
FROM FOOD_ORDER
WHERE PRODUCE_DATE >= '2022-05-01'
AND PRODUCE_DATE < '2022-06-01'
GROUP BY PRODUCT_ID
) s ON p.PRODUCT_ID = s.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, p.PRODUCT_ID ASC;
SQL
복사