1. 코드카타
1. 76번 문제 - 2021년에 가입한 회원들의 구매 비율 구하기
문제
USER_INFO테이블과ONLINE_SALE테이블을 이용하여 2021년에 가입한 회원들 중에서 상품을 구매한 회원 수와 그 비율을 년, 월별로 구해야 한다.- 비율은
소수점 두 번째 자리에서 반올림해야 한다. - 결과는 년, 월 오름차순 정렬해야 한다.
정답 SQL
SELECT YEAR(a.SALES_DATE) YEAR,
MONTH(a.SALES_DATE) MONTH,
COUNT(DISTINCT a.USER_ID) PURCHASED_USERS,
ROUND(COUNT(DISTINCT a.USER_ID) / NULLIF((SELECT COUNT(*)
FROM USER_INFO
WHERE YEAR(JOINED) = 2021), 0), 1) PURCHASED_RATIO
FROM (
SELECT O.SALES_DATE, O.USER_ID
FROM ONLINE_SALE O
LEFT JOIN USER_INFO I ON I.USER_ID = O.USER_ID
WHERE YEAR(I.JOINED) = 2021
) a
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;쿼리 설명
- 2021년에 가입한 회원 중에서 상품을 구매한 회원들만 필터링한다.
- 년과 월을 기준으로 구매한 회원 수(PURCHASED_USERS)와 비율(PURCHASED_RATIO)을 계산한다.
COUNT(DISTINCT a.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021)NULLIF를 사용하여 0으로 나누는 오류 방지ROUND(..., 1)을 사용하여 소수점 둘째 자리에서 반올림
- 최종적으로 년과 월을 기준으로 정렬한다.
2. 77번 문제 - 저지방이면서 재활용 가능한 제품 찾기
문제
PRODUCTS테이블에서 저지방(low_fats = 'Y')이면서 재활용 가능(recyclable = 'Y')한 제품의product_id를 찾는다.- 결과는 어떤 순서로 출력해도 상관없다.
정답 SQL
SELECT product_id
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y';쿼리 설명
WHERE조건을 사용하여low_fats = 'Y'이면서recyclable = 'Y'인 제품을 필터링한다.product_id만 출력한다.
3. 78번 문제 - 추천인 ID가 2가 아닌 고객 찾기
문제
CUSTOMER테이블에서 추천인 ID(referee_id)가 2가 아닌 고객의 이름을 찾는다.- 결과는 아무 순서나 출력해도 된다.
정답 SQL
SELECT name
FROM Customer
WHERE referee_id IS NULL OR referee_id != 2;쿼리 설명
- 추천인이 없는 고객(
referee_id IS NULL)을 포함한다. - 추천인이 2가 아닌 고객(
referee_id != 2)을 찾는다. name컬럼만 출력한다.
4. 79번 문제 - 면적이 크거나 인구가 많은 나라 찾기
문제
WORLD테이블에서 면적(area)이 300만 km² 이상이거나, 인구(population)가 2500만 명 이상인 나라를 찾는다.- 결과는 아무 순서로 출력해도 된다.
정답 SQL
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;쿼리 설명
WHERE조건을 사용하여 면적이 300만 km² 이상이거나(area >= 3000000) 인구가 2500만 명 이상(population >= 25000000)인 나라를 찾는다.name,population,area만 출력한다.
5. 80번 문제 - 자신이 쓴 글을 조회한 저자 찾기
문제
VIEWS테이블에서 자신이 작성한 글(author_id)을 조회한(viewer_id) 저자를 찾는다.- 결과는 저자 ID를 오름차순으로 정렬해야 한다.
정답 SQL
SELECT DISTINCT author_id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id;쿼리 설명
WHERE조건을 사용해 저자가 직접 자신의 글을 조회한 경우(author_id = viewer_id)만 필터링한다.DISTINCT를 사용하여 중복된 저자 ID를 제거한다.ORDER BY author_id를 사용하여 오름차순 정렬한다.
2.qcc 시험
1. 이메일 프로모션 고객 수 조회하기
문제
person테이블에서 이메일 프로모션을 수락한 개인(person_type = 'IN') 고객의 수를 구해야 한다.email_promotion값이 1 또는 2인 경우 이메일 프로모션을 수락한 것으로 간주한다.- 결과는
customer_count라는 컬럼명으로 출력해야 한다.
정답 SQL
SELECT COUNT(1) customer_count
FROM person
WHERE email_promotion IN (1,2)
AND person_type = 'IN';쿼리 설명
WHERE email_promotion IN (1,2)- 이메일 프로모션을 수락한 고객(1 또는 2인 경우)만 필터링한다.
AND person_type = 'IN'- 개인 고객(
IN)만 필터링한다.
- 개인 고객(
COUNT(1) customer_count- 필터링된 고객 수를 계산하고, 결과 컬럼명을
customer_count로 설정한다.
- 필터링된 고객 수를 계산하고, 결과 컬럼명을
정리
email_promotion값이 1 또는 2이고,person_type이'IN'인 고객의 수를 구하는 SQL이다.COUNT(1)을 사용하여 필터링된 고객 수를 빠르게 계산할 수 있다.
2. VIP 고객 찾기
설명:
2011년 10월 한 달 동안 구매 수량이 70개 이상인 고객을 찾는 문제이다.
쿼리:
SELECT
h.customer_id,
p.first_name,
p.last_name,
SUM(d.order_qty) total_quantity
FROM sales_order_detail d
JOIN sales_order_header h ON d.sales_order_id = h.sales_order_id
JOIN sales_customer c ON h.customer_id = c.customer_id
JOIN person p ON c.person_id = p.business_entity_id
WHERE DATE_FORMAT(h.order_date,'%Y-%m') = '2011-10'
AND h.status <> 6
GROUP BY h.customer_id, p.first_name, p.last_name
HAVING total_quantity >= 70
ORDER BY h.customer_id;설명:
JOIN을 사용하여 고객 정보, 주문 내역을 결합SUM(d.order_qty): 각 고객별 총 주문량을 계산DATE_FORMAT(h.order_date,'%Y-%m') = '2011-10': 2011년 10월에 해당하는 주문 필터링HAVING total_quantity >= 70: 구매 수량이 70개 이상인 고객만 출력ORDER BY h.customer_id: 고객 ID 순으로 정렬
QCC 마지막 문제는 TABLE 4개를 JOIN해야 했기 때문에 처음에는 머리가 너무 아픈 문제였다...
그리고 다른 사람들이 DATE_FORMAT(h.order_date,'%Y-%m') = '2011-10' 이 부분을 BETWEEN으로 썼다가 틀린것을 보고 BETWEEN의 쓰임법에 대해 다시 재정립하게 됐다.
아자아자