3주차 학습 회고 시작!
👩💻SQL 코드카타
<이번주에 푼 SQL>
SELECT문
상위 n개 레코드
3월에 태어난 여성 회원 목록 출력하기
특정 물고기를 잡은 총 수 구하기
잔챙이 잡은 수 구하기
서울에 위치한 식당 목록 출력하기
String, Date문
자동차 대여 기록에서 장기/단기 대여 구분하기
이름에 el이 들어가는 동물 찾기
카테고리 별 상품 개수 구하기
특정 옵션이 포함된 자동차 리스트 구하기
루시와 엘라 찾기
한 해에 잡은 물고기 수 구하기
조건에 맞는 사용자 정보 조회하기
년, 월, 성별 별 상품 구매 회원 수 구하기
Group by
자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
Sum, Max, Min
조건에 맞는 아이템들의 가격의 총합 구하기
물고기 종류 별 대어 찾기
잡은 물고기 중 가장 큰 물고기의 길이 구하기
Join
상품 별 오프라인 매출 구하기
없어진 기록 찾기
🤔인사이트💡
이번주는 MYSQL도 공부하면서 파이썬도 배우고
QCC도 진행하고 아티클 스터디도 진행했던 꽤 바쁜 주였다.
QCC(QuERY Challenge Cycle)는 이번 기수부터 새로 포함된 커리큘럼이라고 들었는데
이번주 금요일에 처음 참여해보니
주어진 시간 내에 문제를 푸는 실제 기업에서 진행하게 될 SQL 사전 테스트와 유사한 난이도로 내주신다고 해서 지금 내가 뭐가 부족한지 알 수 있고, 실전 감각을 기르기 좋겠다고 느꼈다.
이번 주에 나온 문제는 2문제(시간 제한 : 1시간)
문제들을 복습해보자면,
1. 이메일 프로모션 고객
- 테이블 설명
- 문제
당신은 마케팅 팀의 일원으로서, 최근에 진행된 이메일 프로모션 캠페인에 관심이 있습니다. 이메일 프로모션을 수락한 개인(소매) 고객의 수를 계산하여 출력하세요.
💻 풀이
✔️ 개인(소매) 고객 : person_type 컬럼 값이 'IN'
👉 where person_type = 'IN'
✔️ 이메일 프로모션 수락한 : email_promotion 컬럼 값이 1 또는 2
👉 where email_promotion in (1, 2)
최종 쿼리문
select distinct count(*)
from person
where person_type = 'IN'
AND
email_promotion in (1,2)
2. VIP 고객 산출 문제
- 테이블 설명
- 문제
당신의 회사는 2011년 10월 동안 자사 제품을 많이 주문한 고객들에게 특별 할인 쿠폰을 제공할 예정입니다. 이를 위해 2011년 10월 한 달 동안 구매한 회사 제품 수량이 총 70개 이상인 고객을 찾아주세요.
- 취소 된 주문은 제외해주세요
- 고객 정보를 포함하여 고객 ID, 이름, 성, 총 주문 수량을 출력하세요
- 결과는 고객 ID 기준으로 오름차순 정렬해야 합니다.
💻 풀이
✔️ 2011년 10월 동안 구매한 고객 : order_date 컬럼 필터링
👉 where date_format(order_date,'%Y-%m') = '2011-10'
✔️ 구매 수량이 총 70개 이상인 고객 : group by ~, having ~
👉 group by customer_id ~, ~ having tpotal_qt >= 70
✔️ 취소 된 주문은 제외 : status 6이 아닌
👉 where status <> 6 또는 where status != 6
✔️ 고객 정보 포함하여 고객 ID, 이름, 성, 총 주문 수량 출력 : sales_order_detail 테이블과 다른 테이블들 조인하여 컬럼 가져오기
👉 customer_id, first_name, last_name, sum(order_qty)
✔️ 결과는 고객 ID 기준 오름차순 정렬 : order by ~
👉 order by customer_id
💡4개 이상의 테이블 조인 시,
1️⃣ 각 테이블 간 관계 확인 > 연결할 키(PK-FK)를 찾기
2️⃣ 기본 되는 테이블을 FROM에 두고, JOIN 사용하여 하나씩 연결!
3️⃣ 각 JOIN이 연결되는 기준 이해하기
4️⃣ WHERE, GROUP BY, HAVING 적절히 사용하여 문제 조건 충족하는 데이터만 가져오기
💥기본 데이블을 선택하는 기준이 뭘까?
- 문제에서 요구하는 가장 중요한 정보를 담고 있는 테이블이 무엇인지 확인해보자!
- 물론 JOIN을 잘 활용하면 어떤 테이블을 FROM에 두더라도 원하는 데이터를 가져올 수 있음!
최종 쿼리문
select soh.customer_id, p.first_name, p.last_name, sum(sod.order_qty) order_total_qty
from sales_order_header soh
join sales_order_detail sod on soh.sales_order_id=sod.sales_order_id
join sales_customer sc on soh.customer_id=sc.customer_id
join person p on sc.person_id=p.business_entity_id
where date_format(soh.order_date, '%Y-%m') = '2011-10'
and soh.status != 6
group by soh.customer_id
having sum(sod.order_qty) >= 70
order by soh.customer_id
*having절에 sum(sod.order_qty)가 너무 길어서 별칭인 order_total_qty로 사용하고 싶어도 select에서 별칭이 적용되기 전에 having절이 실행되기 때문에 별칭 인식 못함!
별칭 말고 컬럼명 그대로 써주자!
다음에 여러 테이블을 조인하는 문제가 나오면, 더 잘 풀어보기!
