없어진 기록 찾기
ANIMAL_INS테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블ANIMAL_OUTS테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블- ANIMAL_OUTS 테이블에는 있는데, ANIMAL_INS 테이블에는 없는 동물들의 ID와 이름을 조회하는 문제
- 입양간 데이터는 남아있기 때문에 ANIMAL_OUTS 테이블이 LEFT 테이블이 될 것이다.
- ANIMAL_ID가 같은 값이 ANIMAL_INS 테이블에 존재하지 않는다면, NULL 값으로 연결될 것입니다. 이를 통해, ANIMAL_OUTS 에는 있지만 ANIMAL_INS에는 없는 ANIMAL_ID 의 값을 조회할 수 있습니다.
SELECT o.ANIMAL_ID, o.NAME
from ANIMAL_OUTS o
left outer join ANIMAL_INS i
on o.ANIMAL_ID = i.ANIMAL_ID
where i.ANIMAL_ID is null
order by o.ANIMAL_ID;오랜 기간 보호한 동물(1)
- 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성
- 아직 입양을 못 간 동물 =
ANIMAL_INS테이블에는 있는데,ANIMAL_OUTS테이블에는 없는 동물
- 아직 입양을 못 간 동물 =
- 결과는 보호 시작일 순으로 조회
SELECT i.NAME, i.DATETIME
from ANIMAL_INS i
left outer join ANIMAL_OUTS o
on i.ANIMAL_ID = o.ANIMAL_ID
where o.ANIMAL_ID is null
order by i.DATETIME
limit 3아래 문제부터는 LEVEL 4~5 문제이다.
아무래도 하나은행 코테에 SQL이 2문제밖에 안 나오기 때문에 어렵게 출제가 되지 않을까하는 걱정이 있다.
그룹별 조건에 맞는 식당 목록 출력하기
- 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문
- 중첩 select 문이 필요하다.
- 중첩 select 문을 통해서 리뷰를 가장 많이 작성한 회원의 ID를 가져오고, where 절에서 해당 회원의 ID와 같은 회원의 리뷰를 출력한다.
- 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
from REST_REVIEW r
left outer join MEMBER_PROFILE m
on r.MEMBER_ID = m.MEMBER_ID
where r.MEMBER_ID = (select MEMBER_ID
from REST_REVIEW
group by MEMBER_ID
order by count(MEMBER_ID) desc
limit 1
)
order by r.REVIEW_DATE, REVIEW_TEXT- 리뷰를 가장 많이 작성한 회원의 ID
- MEMBER_ID 별로 그룹핑을 한 후에,
order by count(MEMBER_ID) desc를 통해 정렬시켜준다. - 그 중에서 가장 위에 있는 행이 리뷰를 가장 많이 작성한 회원이다.
- MEMBER_ID 별로 그룹핑을 한 후에,
주문량이 많은 아이스크림들 조회하기
- FIRST_HALF 테이블과 JULY 테이블에서 FLAVOR의 TOTAL_ORDER 개수를 더해서 가장 상위의 3개의 맛을 출력해야 한다.
- JULY의 FLAVOR가 외래키이기 때문에 INNER JOIN을 쓰면 된다.
- 이때 주의해야할 점은 7월 아이스크림(JULY)에 같은 맛이지만 다른 출하번호를 가져서 TOTAL_ORDER가 나누어져 있는 경우도 있다는 것!
그렇기 때문에 FIRST_HALF 테이블과 JOIN 하기 전에, JULY 테이블을 FLAVOR로 그룹핑하는 과정이 필요하다.
SELECT F.FLAVOR
from FIRST_HALF F
join (select FLAVOR, sum(TOTAL_ORDER) as sum
from JULY
group by FLAVOR) as J
on F.FLAVOR = J.FLAVOR
group by F.FLAVOR
order by sum(F.TOTAL_ORDER + J.sum) desc
limit 3- JULY 테이블을 FLAVOR로 그룹핑한 후에, FLAVOR 별 총 주문량을 다시 더해줬다. 해당 테이블을 J로 명칭
(이 과정을 겪지 않고 바로 JOIN 하게 된다면, 총 주문량을 더할 때 중복이 발생해서 숫자가 커지게 된다.) - J 테이블과 FIRST_HALF 테이블을 조인한다.
sum(F.TOTAL_ORDER + J.sum)으로 내림차순 정렬하고, 상위 3개를 출력한다.
5월 식품들의 총매출 조회하기
- 생산일자가 2022년 5월 식품을 출력
- 결과는 총매출을 기준으로 내림차순 정렬, 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬
SELECT O.PRODUCT_ID, PRODUCT_NAME, sum(O.AMOUNT * P.PRICE) as TOTAL_SALES
from FOOD_ORDER O
join FOOD_PRODUCT P
on O.PRODUCT_ID = P.PRODUCT_ID
where YEAR(PRODUCE_DATE) = 2022 and MONTH(PRODUCE_DATE) = 05
group by PRODUCT_ID
order by TOTAL_SALES desc, O.PRODUCT_ID보호소에서 중성화한 동물
- 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물을 조회하는 문제
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
from ANIMAL_INS I
join ANIMAL_OUTS O
on I.ANIMAL_ID = O.ANIMAL_ID
where (I.SEX_UPON_INTAKE like 'Intact%') and
(O.SEX_UPON_OUTCOME like 'Spayed%' or O.SEX_UPON_OUTCOME like 'Neutered%')
order by I.ANIMAL_ID⭐️ 상품을 구매한 회원 비율 구하기(LEVEL 5)
SELECT YEAR(SALES_DATE) as YEAR, MONTH(SALES_DATE) as MONTH,
count(distinct S.USER_ID) as PUCHASED_USERS,
ROUND(count(distinct S.USER_ID)/(select count(*) from USER_INFO where YEAR(JOINED) = 2021), 1) as PUCHASED_RATIO
from ONLINE_SALE S
join USER_INFO I
on S.USER_ID = I.USER_ID
where YEAR(JOINED) = 2021
group by YEAR(SALES_DATE), MONTH(SALES_DATE)
order by YEAR, MONTH-
두 테이블을 조인한다.
-
2021년에 가입한 전체 회원들 중
->where YEAR(JOINED) = 2021 -
상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력
group by YEAR(SALES_DATE), MONTH(SALES_DATE): 년, 월별로 출력해야하기 때문에 그룹핑을 한다.count(distinct S.USER_ID) as PUCHASED_USERS: 해당 년, 월에 한 회원이 여러 번 주문할 수 있기 때문에distinct키워드를 사용한다.ROUND(count(distinct S.USER_ID)/(select count(*) from USER_INFO where YEAR(JOINED) = 2021), 1) as PUCHASED_RATIO- 2021년에 가입한 전체 회원 수를 중첩 select 문으로 가져온다.
- 상품을 구매한 회원의 비율은 소수점 두번째 자리에서 반올림 -> ROUND 사용
- 전체 결과는 년을 기준으로 오름차순 정렬, 년이 같다면 월을 기준으로 오름차순 정렬
->order by YEAR, MONTH
[다른 사람의 풀이]
- 미리 중복을 제거한 테이블을 가져오는 풀이도 있다.
DISTINCT A, B, C: A만 중복 처리를 하는 게 아니라, A/B/C 모두를 중복 처리한다.
SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS,
ROUND((COUNT(*)/ (SELECT COUNT(*)
FROM USER_INFO WHERE YEAR(JOINED) = 2021)), 1) AS PUCHASED_RATIO
FROM (
SELECT DISTINCT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, U.USER_ID
FROM ONLINE_SALE S
JOIN USER_INFO U ON S.USER_ID = U.USER_ID AND YEAR(JOINED) = 2021
) A
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH❗️ 테이블 3개 조인
테이블 3개 조인은 2개 조인과 크게 다르지 않다.
한번 더 JOIN과 ON을 작성해주면 된다.
이때 주의할 점은 LEFT JOIN이다.
- LEFT OUTER JOIN 시에는 조인하는 순서가 중요하기 때문에 왼쪽 테이블일수록 행의 숫자가 많은 것을 둬야 한다.
- 또한, LEFT OUTER JOIN를 사용한 경우에는, INNER JOIN이나 다른 조인을 사용하지 않아야 한다.
(자세한 건 블로그를 참고)
⭐️ 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
- 조건이 까다로운 문제이다.
조건
- 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중
- 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하며
= HISTORY 테이블에 START_DATE과 END_DATE에 11/1 ~ 11/30 기간은 없어야 한다. - 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차
SELECT *
from CAR_RENTAL_COMPANY_CAR as C
join CAR_RENTAL_COMPANY_RENTAL_HISTORY as H on H.CAR_ID = C.CAR_ID
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as P on C.CAR_TYPE = P.CAR_TYPE
where (C.CAR_TYPE = '세단' or C.CAR_TYPE = 'SUV')
and (END_DATE < '2022-11-01' or START_DATE > '2022-12-01')
and DURATION_TYPE = '30일 이상'위와 같은 조건을 가지고, 작성한 쿼리문이다.
하지만 여기서 놓친 게, HISTORY에 존재하지 않는 자동차가 있다는 것이다.
HISTORY에 존재하지 않는 자동차는 END_DATE < '2022-11-01' or START_DATE > '2022-12-01' 조건을 만족할 수 없기 때문에
조건을 만족하지 않는 자동차들을 가져와서, 그 안에 존재하지 않는지를 파악했다.
SELECT C.CAR_ID, C.CAR_TYPE, ROUND(C.DAILY_FEE * 30 * (100- DISCOUNT_RATE) / 100) as FEE
from CAR_RENTAL_COMPANY_CAR as C
join CAR_RENTAL_COMPANY_RENTAL_HISTORY as H on H.CAR_ID = C.CAR_ID
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as P on C.CAR_TYPE = P.CAR_TYPE
where (C.CAR_TYPE = '세단' or C.CAR_TYPE = 'SUV')
and DURATION_TYPE = '30일 이상'
and C.CAR_ID NOT IN (select CAR_ID
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where END_DATE > '2022-11-01' AND START_DATE < '2022-12-01'
)
group by C.CAR_ID
having FEE >= 500000 and FEE <= 2000000
order by FEE desc, C.CAR_TYPE, C.CAR_ID desc
