🚩오늘의 문제는?

SQL - level 3

1.자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
2.💥💥💥조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기💥💥💥
3.헤비 유저가 소유한 장소
4.💥대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
5.특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
6.대장균들의 자식의 수 구하기

1.자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

문제 정의

문제 풀이

오답(여쭤본 상황)

SELECT DISTINCT A.CAR_ID,
CASE WHEN A.START_DATE <= '2022-10-16' AND A.END_DATE >= '2022-10-16' THEN '대여중'
ELSE '대여 가능' 
END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
WHERE A.END_DATE IN (SELECT MAX(END_DATE)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID)
ORDER BY A.CAR_ID DESC;

2.💥💥💥조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기💥💥💥

문제 정의

문제 풀이

SELECT CONCAT('/home/grep/src/',BOARD_ID,'/',FILE_ID,FILE_NAME,FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE A
WHERE A.BOARD_ID = (
SELECT BOARD_ID
FROM USED_GOODS_BOARD
GROUP BY BOARD_ID
ORDER BY VIEWS DESC LIMIT 1)
ORDER BY FILE_ID DESC;

3.헤비 유저가 소유한 장소

문제 정의

문제 풀이

SELECT ID,NAME,HOST_ID
FROM PLACES A
WHERE A.HOST_ID
IN 
(SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID)>=2);

4.💥대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

문제 정의

문제 풀이

오답

SELECT MONTH(START_DATE) AS MONTH,CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A

WHERE A.CAR_ID 
IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY CAR_ID
HAVING COUNT(HISTORY_ID)>=5)

GROUP BY MONTH(START_DATE),CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;

정답

SELECT month(START_DATE) as 'MONTH', CAR_ID, count(*) as 'RECORDS'
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where CAR_ID in (select CAR_ID
                  from CAR_RENTAL_COMPANY_RENTAL_HISTORY
                  where START_DATE >= '2022-08-01' and START_DATE <= '2022-10-31'
                  group by CAR_ID
                  having count(*) >= 5)
    and START_DATE >= '2022-08-01' and START_DATE <= '2022-10-31'
group by MONTH, CAR_ID
order by MONTH asc, CAR_ID desc;

주의할 점

• 틀린 이유 : 출력을 할 때도 대여 시작일 이 8월에서 10월 사이인 경우만을 출력해야되기 때문에
  START_DATE가 8월-10월이라는 조건을 서브쿼리 밖에서도 썼다.

: 리스트 출력 조건을 자세히 살펴보고 거기에 맞춰서 정답을 구하자..

5.특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

문제 정의

문제 풀이

-- STEP3-2.잡은 수, 최대길이, 물고기 종류 출력 
SELECT COUNT(*) AS FISH_COUNT , MAX(LENGTH) AS MAX_LENGTH , FISH_TYPE
FROM FISH_INFO 
-- STEP2. FISH_TYPE의 평균 길이가 33CM 이상인 물고기를 종류별로 분류
WHERE FISH_TYPE IN (SELECT NEW_T.FISH_TYPE
-- STEP1. NULL값을 10으로 변경
FROM (SELECT A1.ID,A1.FISH_TYPE, A1.TIME, 
     CASE WHEN LENGTH IS NULL THEN 10
     ELSE LENGTH
     END AS LENGTH
     FROM FISH_INFO A1) AS NEW_T
GROUP BY NEW_T.FISH_TYPE
HAVING AVG(NEW_T.LENGTH)>=33
)
-- STEP3-1.물고기 종류를 기준으로 값을 출력하기 (이때 물고기 종류에 대해 오름차순으로 정렬)
GROUP BY FISH_TYPE
ORDER BY FISH_TYPE;

6.대장균들의 자식의 수 구하기

문제 정의

문제 풀이

#해당 문제의 HINT : ID가 1 & 개체의 자식 ID 3= PARENT_ID : 1 & ID : 3
#-> 여기서 말하는 ID은 PARRENT_ID & 개체의 자식은 ID 라는 점을 확인하기!!!

SELECT A.ID, 
    -- STEP3. NULL값인 경우 그대로 출력되지 않고 0으로 출력될 수 있도록 CASE 절 활용
    CASE WHEN B.CHILD_COUNT IS NULL THEN 0 ELSE CHILD_COUNT END AS CHILD_COUNT
    -- STEP2.  JOIN 진행 : 이때 자식 기준이 아니라 본 데이터의 ID 을 기준으로 COUNT을 해야하므로 LEFT JOIN
FROM ECOLI_DATA AS A LEFT JOIN (
    -- STEP1. ID : PARENT_ID & 개체의 자식 ID : ID 
    -- 이때 PARENT_ID가 NULL인 경우는 출력할 필요 없으니 WHERE 절 활용
    SELECT PARENT_ID AS ID ,COUNT(ID) AS CHILD_COUNT
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NOT NULL
    GROUP BY PARENT_ID 
    ) AS B

ON A.ID=B.ID

또 다른 풀이 (간단!)

select e2.id, count(e1.parent_id) as child_count
from ecoli_data as e1
right join ecoli_data as e2
on e1.parent_id = e2.id
group by e2.id
order by e2.id asc;