IS NULL: 잡은 물고기의 평균 길이 구하기
⭐
문제
- 잡은 물고기의 평균 길이를 출력하는 SQL문을 작성해주세요.
- 평균 길이를 나타내는 컬럼 명은 AVERAGE_LENGTH로 해주세요.
- 평균 길이는 소수점 3째자리에서 반올림하며, 10cm 이하의 물고기들은 10cm 로 취급하여 평균 길이를 구해주세요.
코드
WITH LENGTH AS
(SELECT IF(ISNULL(LENGTH), 10, LENGTH) AS LENGTH
FROM FISH_INFO)
SELECT ROUND(AVG(LENGTH), 2) AS AVERAGE_LENGTH
FROM LENGTH
설명
10CM 이하의 물고기는 LENGTH가 NULL이므로, WITH절에서 이를 10으로 대체하는 테이블을 만들어주었습니다.
그 후, 소수점 3째자리에서 반올림한 평균 치를 구하는 쿼리문을 작성했습니다.
JOIN: 상품 별 오프라인 매출 구하기
⭐⭐
문제
PRODUCT 테이블과 OFFLINE_SALE 테이블에서
- 상품코드 별
- 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요.
- 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.
코드
SELECT P.PRODUCT_CODE,
SUM(P.PRICE * O.SALES_AMOUNT) AS SALES
FROM PRODUCT P JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, P.PRODUCT_CODE;
설명
상품코드로 GROUP BY한 뒤, 판매가 * 판매량의 SUM을 구하여 매출액을 계산합니다.
JOIN: 상품을 구매한 회원 비율 구하기
⭐⭐⭐⭐⭐
문제
USER_INFO 테이블과 ONLINE_SALE 테이블에서
- 2021년에 가입한 전체 회원들 중
- 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을
- 년, 월 별로 출력하는 SQL문을 작성해주세요.
- 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고,
- 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
코드
WITH YEAR_21 AS
(SELECT COUNT(*) AS USER_COUNT
FROM USER_INFO
WHERE YEAR(JOINED) = '2021')
SELECT YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
COUNT(DISTINCT(USER_ID)) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT(USER_ID)) / USER_COUNT, 1) AS PUCHASED_RATIO
FROM ONLINE_SALE, YEAR_21
WHERE USER_ID IN (SELECT USER_ID FROM USER_INFO WHERE YEAR(JOINED) = '2021')
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH
설명
이 문제의 핵심은 2021년에 가입한 전체 회원 수를 어떻게 ONLINE_SALE 테이블과 함께 표현할 수 있는가였습니다.
WITH절을 통해 2021년에 가입한 전체 회원 수를 구합니다.
그 후, 조인 대신에 두 테이블을 이용해줍니다.
먼저, 2021년에 가입했으면서 구매 이력이 있는 회원 수를 WHERE절에서 필터링한 후 COUNT(DISTINCT(USER_ID))하여 구합니다.
그리고, 비율 역시 위의 식을 분자에 분모에는 WITH절의 테이블에서 구한 총 회원 수를 넣어 구합니다.
STRING, DATE: 연도 별 평균 미세먼지 농도 조회하기
⭐⭐
문제
AIR_POLLUTION 테이블에서
- 수원 지역의
- 연도 별
- 평균 미세먼지 오염도와 평균 초미세먼지 오염도를 조회하는 SQL문을 작성해주세요.
- 이때, 평균 미세먼지 오염도와 평균 초미세먼지 오염도의 컬럼명은 각각 PM10, PM2.5로 해 주시고, 값은 소수 셋째 자리에서 반올림해주세요.
- 결과는 연도를 기준으로 오름차순 정렬해주세요.
코드
SELECT YEAR(YM) AS YEAR,
ROUND(AVG(PM_VAL1), 2) AS 'PM10',
ROUND(AVG(PM_VAL2), 2) AS 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION2 = '수원'
GROUP BY YEAR
ORDER BY YEAR;
설명
수원 지역을 WHERE절을 통해 필터링합니다.
연도별 평균치이므로, 연도로 GROUP BY하여 AVG를 통해 평균치를 구합니다.
STRING, DATE: 한 해에 잡은 물고기 수 구하기
⭐
문제
FISH_INFO 테이블에서
- 2021년도에
- 잡은 물고기 수를 출력하는 SQL 문을 작성해주세요.
- 이 때 컬럼명은 'FISH_COUNT' 로 지정해주세요.
코드
SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE YEAR(TIME) = '2021'
설명
WHERE절에서 2021년에 잡은 물고기를 필터링하여 COUNT합니다.
데이터에서 의사결정으로