1. 대장균들의 자식의 수 구하기
SELECT
P.ID,
COUNT(C.ID) AS CHILD_COUNT
FROM ECOLI_DATA AS P
LEFT JOIN ECOLI_DATA AS C
ON P.ID = C.PARENT_ID
GROUP BY P.ID
ORDER BY P.ID;
SQL
복사
2. 대장균의 크기에 따라 분류하기 1
SELECT
ID,
CASE
WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
ELSE 'HIGH'
END AS SIZE
FROM ECOLI_DATA
ORDER BY ID ASC;
SQL
복사
3. 대장균의 크기에 따라 분류하기 2
-- 코드를 작성해주세요
WITH ranked AS (
SELECT
ID,
PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS rnk
FROM ECOLI_DATA
)
SELECT
ID,
CASE
WHEN rnk <= 0.25 THEN 'CRITICAL'
WHEN rnk <= 0.50 THEN 'HIGH'
WHEN rnk <= 0.75 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME
FROM ranked
ORDER BY ID;
SQL
복사
4. 업그레이드 할 수 없는 아이템 구하기
SELECT
I.ITEM_ID,
I.ITEM_NAME,
I.RARITY
FROM ITEM_INFO I
LEFT JOIN ITEM_TREE T
ON I.ITEM_ID = T.PARENT_ITEM_ID
WHERE T.PARENT_ITEM_ID IS NULL
ORDER BY I.ITEM_ID DESC;
-- 서브쿼리 활용
SELECT
I.ITEM_ID, I.ITEM_NAME, I.RARITY
FROM ITEM_INFO I
WHERE I.ITEM_ID NOT IN (
SELECT DISTINCT PARENT_ITEM_ID
FROM ITEM_TREE
WHERE PARENT_ITEM_ID IS NOT NULL
)
ORDER BY I.ITEM_ID DESC;
SQL
복사
5. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
패턴 1: SUM(CASE WHEN 조건 THEN 1 ELSE 0 END) > 0
패턴 2: MAX(CASE WHEN 조건 THEN 1 ELSE 0 END) = 1
패턴 3(서브쿼리 없이): CASE WHEN MAX('2022-10-16'
BETWEEN START_DATE AND END_DATE) THEN ... END
-- 그룹별 조건 충족 여부를 집계로 판별
SELECT
CAR_ID,
CASE
WHEN SUM(
CASE WHEN '2022-10-16'
BETWEEN START_DATE AND END_DATE
THEN 1 ELSE 0 END) > 0
THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID ASC;
-- MAX 활용
SELECT
CAR_ID,
CASE WHEN MAX('2022-10-16' BETWEEN START_DATE AND END_DATE) = 1
THEN '대여중' ELSE '대여 가능' END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;
SQL
복사
5. 물고기 종류 별 대어 찾기
SELECT F.ID, I.FISH_NAME, F.LENGTH
FROM FISH_INFO F
JOIN FISH_NAME_INFO I ON F.FISH_TYPE = I.FISH_TYPE
WHERE (F.FISH_TYPE, F.LENGTH) IN (
SELECT FISH_TYPE, MAX(LENGTH)
FROM FISH_INFO
GROUP BY FISH_TYPE
)
ORDER BY F.ID;
SQL
복사
6. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
SELECT
MONTH(h.START_DATE) AS MONTH,
h.CAR_ID,
COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
JOIN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE >= '2022-08-01'
AND START_DATE < '2022-11-01'
GROUP BY CAR_ID
HAVING COUNT(*) >= 5
) e ON e.CAR_ID = h.CAR_ID
WHERE h.START_DATE >= '2022-08-01'
AND h.START_DATE < '2022-11-01'
GROUP BY MONTH(h.START_DATE), h.CAR_ID
ORDER BY 1 ASC, 2 DESC;
SQL
복사
7. 즐겨찾기가 가장 많은 식당 정보 출력하기
SELECT
R.FOOD_TYPE,
R.REST_ID,
R.REST_NAME,
R.FAVORITES
FROM REST_INFO R
JOIN (
SELECT
FOOD_TYPE,
MAX(FAVORITES) AS MAX_FAV
FROM REST_INFO
GROUP BY FOOD_TYPE
) M
ON R.FOOD_TYPE = M.FOOD_TYPE
AND R.FAVORITES = M.MAX_FAV
ORDER BY R.FOOD_TYPE DESC;
SQL
복사
8. 있었는데요 없었습니다
SELECT
I.ANIMAL_ID,
I.NAME
FROM ANIMAL_INS I
JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME ASC;
SQL
복사