상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요. (링크)
SELECT i.ingredient_type
, sum(f.total_order) 'TOTAL_ORDER'
FROM first_half f INNER JOIN icecream_info i
ON f.flavor = i.flavor
GROUP BY 1
ORDER BY f.total_order
;동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요. (링크)
SELECT
animal_id
, name
, sex_upon_intake
FROM
animal_ins
WHERE
name in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY
animal_id
;BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서 도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요. (링크)
SELECT
book_id
, date_format(published_date, '%Y-%m-%d') 'PUBLISHED_DATE'
FROM
book
WHERE
year(published_date) = 2021
and category = '인문'
ORDER BY
published_date
;CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.(링크)
SELECT
round(avg(daily_fee),0) as 'AVERAGE_FEE'
FROM
CAR_RENTAL_COMPANY_CAR
WHERE
car_type = 'SUV'
GROUP BY
car_type
;소수점 첫째자리'에서' 반올림이니까 1이 아니라 0이 맞다
1은 소수점 첫째자리로 반올림하는 것...
USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요. (링크)
SELECT
b.writer_id
, u.nickname
, sum(b.price) as 'total_sales'
FROM
used_goods_board b
INNER JOIN used_goods_user u
ON b.writer_id = u.user_id
WHERE
status = 'done'
GROUP BY
b.writer_id
HAVING
total_sales >= 700000
ORDER BY 3
;where만 쓰다가 having을 오랜만에 써봤다
개념을 놓치지 않도록 주의해야지
아래는 2차로 풀면서 작성한 코드
코드 자체는 조금 더 복잡해지긴 했지만,
FROM 절에 서브쿼리를 활용해서 필요한 칼럼만 조회하도록 해보았다
SELECT
g.user_id
, g.nickname
, a.total_sales
FROM
(SELECT
writer_id
, sum(price) as 'total_sales'
FROM
USED_GOODS_BOARD
WHERE
status = 'done'
GROUP BY
writer_id
HAVING
sum(price) >= 700000) a
INNER JOIN USED_GOODS_USER g ON a.writer_id = g.user_id
ORDER BY
3PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요. (링크)
처음에는 그냥 if문 여러개 달면 되겠거니 했는데
SELECT
count(if(price < 10000, 1, null)) as '10000'
,count(if(price between 10000 and 19999, 1, null)) as '20000'
,count(if(price between 20000 and 39999, 1, null)) as '30000'
,count(if(price between 30000 and 49999, 1, null)) as '40000'
FROM
product
;
답안이 요구하는 형태(세로)와는 달랐다
생각해보면, 딱 피벗 테이블을 만들어달라고 하는듯 한데...
피벗은 솔직히 그냥 한번 보면서 넘어갔던터라, 기억이 나지 않더라
어쩌겠나 오픈북 해야지
일단 price_group 칼럼을 만들고
SELECT
CASE
WHEN price < 10000 then '0'
WHEN price between 10000 and 19999 then '10000'
WHEN price between 20000 and 29999 then '20000'
WHEN price between 30000 and 39999 then '30000'
END as 'PRICE_GROUP'
FROM
product
;
적당히 맞춰서 했는데... 이상하게 PRICE_GROUP 칼럼에 null값이 나왔다
SELECT
price_new as 'PRICE_GROUP'
, count(price_new) as 'PRODUCTS'
FROM
(
SELECT
CASE
when price < 10000 then '0'
when price between 10000 and 19999 then '10000'
when price between 20000 and 29999 then '20000'
when price between 30000 and 39999 then '30000'
when price between 40000 and 49999 then '40000'
END as 'price_new'
FROM
product
) as aa
GROUP BY 1
ORDER BY 1
;
WHERE PRICE_GROUP is not null 조건을 달아봤지만, 계속 답은 틀렸다고 나오더라
이유는 간단했는데...
40000 이상인 값들이 존재했다;
SELECT
price_new as 'PRICE_GROUP'
, count(price_new) as 'PRODUCTS'
FROM
(
SELECT
CASE
when price < 10000 then '0'
when price between 10000 and 19999 then '10000'
when price between 20000 and 29999 then '20000'
when price between 30000 and 39999 then '30000'
when price between 40000 and 49999 then '40000'
when price between 50000 and 59999 then '50000'
when price between 60000 and 69999 then '60000'
when price between 70000 and 79999 then '70000'
when price between 80000 and 89999 then '80000'
when price between 90000 and 99999 then '90000'
else null
END as 'price_new'
FROM
product
) as aa
GROUP BY 1
ORDER BY 1
;그냥 9만대까지 늘려주니까 해결...
설마 저렇게 초보적인 복붙으로 해결될 지 몰라서 저렇게 조건이 많아야 한다고 생각을 못했다...
그래서 보다 깔끔한 코드가 없나 다른 사람들의 코드를 좀 더 공부해보는 시간을 가졌고,
내가 생각했던 이상적인 코드를 하나 발견했다
SELECT FLOOR(PRICE / 10000) * 10000 as PRICE_GROUP, COUNT(*) as PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1"어차피 모든 숫자를 만 단위로 남기고 그 이하는 0을 붙일 것이다"라는 명제에서 출발하여
PRICE/10000의 소수점을 다 떼어내고(floor 함수) 다시 *10000을 하면 깔끔하게 만의 자리 위로만 숫자를 남길 수 있다
그럼 12000이든 15298이든 전부 10000이 되므로, group by를 했을 때 만의 자리 단위로 그룹이 형성되고, 깔끔하게 계산이 되는 것이다
이 식으로는 혹여 100,000 이상의 수가 입력되더라도 자동으로 계산이 되니 훨씬 이상적인 코드라고 생각된다
MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요. (링크)
SELECT
member_id
, member_name
, gender
, date_format(date_of_birth, '%Y-%m-%d') as 'date_of_birth'
FROM
member_profile
WHERE
(month(date_of_birth) = 3)
AND (gender = 'W')
AND (tlno is not null)
ORDER BY
member_id
;문제를 제대로 안 읽는 건 여전하다
생일 3월, 전화번호 not null까진 조건 잘 걸어놓고
정작 gender 조건을 안 걸어서 코드를 몇 번이나 다시 보고 말이야
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문을 작성해주세요. 자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요. (링크)
SELECT
distinct c.car_id
FROM
CAR_RENTAL_COMPANY_CAR C
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON c.car_id = h.car_id
WHERE
(c.car_type = '세단')
and (month(h.start_date) = 10)
ORDER BY 1 desc
;동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요. (링크)
SELECT
*
FROM
animal_ins
ORDER BY
animal_id
;...이건 뭔데 이렇게 뒤쪽에 배치된 문제지?
REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요. (링크)
SELECT
food_type
, rest_id
, rest_name
, favorites
FROM
(
SELECT
food_type
, rest_id
, rest_name
, favorites
, rank() over (partition by food_type order by favorites desc) as 'ranking'
FROM
rest_info
) aa
WHERE
ranking = 1
ORDER BY
food_type desc
;윈도우 함수를 쓸 때마다 드는 생각, "윈도우 함수를 쓰면 서브쿼리를 여러번 쓰는 것을 줄일 수 있다. 그런데 그럼 내가 윈도우 함수를 쓰지 않으면 서브쿼리를 중첩해서라도 이 문제를 풀 수 있나?"
사실 이미 윈도우 함수를 알아버린 뇌로서는 잘 생각이 되지 않는다...
FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요. (링크)
SELECT
category
, price as 'max_price'
, product_name
FROM
(
SELECT
*
, rank() over (partition by category order by price desc) as 'ranking'
FROM
food_product
) aa
WHERE
(ranking = 1)
and (category in ('과자', '국', '김치', '식용유'))
ORDER BY
price desc
;사실 이전 문제랑 다를게 없지 않나..?
FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요. (링크)
SELECT
p.product_id
, p.product_name
, p.price * sum(o.amount) as 'total_sales'
FROM
food_product p
INNER JOIN food_order o
ON p.product_id = o.product_id
WHERE
(year(o.produce_date) = 2022)
and (month(o.produce_date) = 5)
GROUP BY
o.product_id
ORDER BY
3 desc
, 1
;천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요. (링크)
SELECT
o.animal_id
, o.name
FROM
animal_ins i
RIGHT JOIN animal_outs o
ON i.animal_id = o.animal_id
WHERE
(i.datetime is null)
# and (o.datetime is not null)
ORDER BY
i.animal_id
;일단 알게된 점 하나 : MySQL에서는 FULL OUTER JOIN을 제공하지 않는다.
LEFT OUTER JOIN과 RIGHT OUTER JOIN을 UNION 해서 FULL을 구현한다.
이 문제에서 헤맸던 점이, 처음에 INNER JOIN이었다가, 그 다음에는 FULL OUTER JOIN이었다가, 그 다음으로 LEFT OUTER JOIN을 시도했다는 점이다.
결국 정답 빼고 모든 함정을 밟은 셈인데, 조급해하지 말고 문제를 찬찬히 고민했으면 개념상 RIGHT OUTER JOIN이라는 점을 알 수 있었을텐데 아쉬운 점이다.
문제가 '입양을 간 기록은 있는데' -> animal_outs에 값이 있어야 함
'보호소에 들어온 기록이 없는' -> animal_ins에는 없어야 함
이니까 종합하면 animal_outs에서는 not null이고 animal_ins에서는 null인 값을 찾는 것이니까.
그런데 어차피 RIGHT JOIN을 하면 animal_outs은 반드시 값이 존재하니까 자연스럽게 not null 조건은 충족된다.
코드를 최적화하기 위해서 제거해도 좋은 부분.
문제를 해석하고 이해하는 것이 풀이의 첫 걸음이라는 것을 다시금 깨닫는다.
상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요. (링크)
SELECT
f.flavor
FROM
first_half f
INNER JOIN icecream_info i
ON f.flavor = i.flavor
WHERE
(f.total_order > 3000)
and (i.ingredient_type = 'fruit_based')
ORDER BY
f.total_order desc
;