[SQL] JOIN 연습문제

SQL 연습문제 8-1

회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 모든 주문내역에 회원정보를 표시하세요.

• 조회 항목 : 주문ID(order_id), 주문한 상품 수량(num_of_item), 회원 이름(first_name, last_name), 주소(street_address), 우편번호(postal_code), 도시(city), 국가(country)

SELECT 
  t2.order_id,
  t2.num_of_item,
  t1.first_name,
  t1.last_name,
  t1.street_address,
  t1.postal_code,
  t1.city,
  t1.country
  
FROM `thelook_ecommerce.users` as t1
left join `thelook_ecommerce.orders` as t2 on t1.id = t2.user_id

t1.first_name 하는 이유? 뭐징? 근데 그냥 first_name 해도 답 나옴.. -> 명시용 

SQL 연습문제 8-2

회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 상품을 주문한 회원의 국가가 ‘United States’이면서 주문 상태가 처리중(Processing)인 정보를 조회하시오.

• 조회 항목 : 회원 이름(first_name, last_name), 주소(street_address), 우편번호(postal_code), 도시(city), 국가(country), 주문한 상품 수량(num_of_item)
• 조건 : 국가가 ‘United States’이면서 주문 상태가 처리중(Processing)

<내가>

SELECT 
  t2.order_id,
  t1.first_name,
  t1.last_name,
  t1.street_address,
  t1.postal_code,
  t1.city,
  t1.country,
  t2.num_of_item
FROM `thelook_ecommerce.users` as t1
left join `thelook_ecommerce.orders` as t2 on t1.id = t2.user_id

where t1.country = 'United States' and t2.status = 'Processing'

<강사님>

select 
  t1.order_id,
  t2.first_name || ' ' ||t2.last_name as user_name,
  t2.street_address,
  t2.postal_code,
  t2.city,
  t2.country,
  t1.num_of_item
from `thelook_ecommerce.orders` t1
join `thelook_ecommerce.users` t2 on t1.user_id = t2.id
where t2.country = 'United States'
and t1.status = 'Processing'

왜 join이지? left join 안 쓰고?  -> 잘 관리되면 join 써도되는 건가?

 

 마스터테이블인 user나 product에 해당 id의 레코드가 없는경우
inner join의 경우에는 order 테이블 레코드도 출력이 안될 수 있어요.
예) 1번 주문에 user_id가 10인데 user 테이블에 10번 유저 데이터가 아예 없는 경우

order테이블에서 2022년 전체 매출기록을 조회하는데 
탈퇴한 10번유저의 주문기록만 빠져서는 안되겠죠.

그러다보니 저의 경우에는 의도적으로 left join만 사용하는 습관이 생겨버렸네요.

관리가 잘 되어있다는 것은
order테이블에 기록되어있는 user_id에 해당하는 모든 id의 유저가
user테이블에도 다 레코드가 존재가는것을 이야기 한 것 이예요.

 

=> 잘 관리된 테이블은 null 값이 없는 것 -> 그래서 매치가 잘 되는 

 

 

회원이 탈퇴 할 경우 -> 삭제하지 않고 탈퇴 한 회원이라고 표시를 함(회원 id, 가입, 탈퇴 일시 정도) -> 소프트 딜리트 

 

SQL 연습문제 8-3

회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 국가별 총 상품 주문주(total_order_count)을 조회하시오.

• 조회 항목 : 국가명(country), 국가별 총 상품 주문주(total_order_count)
• 정렬 : 국가별 총 상품 주문주(total_order_count)이 많은 순으로 정렬

select t1.country,
      count(t2.order_id) as total_order_count

from `thelook_ecommerce.users` as t1
left join `thelook_ecommerce.orders` as t2 on t1.id = t2.user_id

group by country
order by total_order_count desc

 

 

SQL 연습문제 8-4

주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 필요한 정보를 조회하시오.

• 조회 항목 : 가격(cost), 카테고리(category), 상품명(name), 브랜드(brand), 판매가격(retail_price), 주문상태(status), 주문일시(created_at), 배송일시(shipped_at), 배송완료일시(delivered_at)
• 조건 : 카테고리(category)가 'Outerwear & Coats'
• 정렬 : 주문일시(created_at) 기준으로 최신순으로 정렬

select 
      t1.id as order_item_id,
      t1.order_id,
      t2.cost,
      t2.category,
      t2.name,
      t2.brand,
      t2.retail_price,
      t1.status,
      t1.created_at,
      t1.shipped_at,
      t1.delivered_at

from `thelook_ecommerce.order_items` as t1
left join `thelook_ecommerce.products` as t2 on t1.id = t2.id
where t2.category = 'Outerwear & Coats'
order by t1.created_at desc

 

 

SQL 연습문제 8-5

주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 2022년 한해 주문한 상품들의 정보를 조회하시오.

• 조회 항목 : 주문일(order_date), 상품명(name), 주문상태(status), 원가(cost), 판매가격(retail_price), 카테고리(category), 브랜드(brand)
• 주문일은 2022-01-01 와 같은 %Y-%m-%d 포맷으로 표시해주세요. FORMAT_DATE 함수 이용합니다. order_items의 created_at을 데이터를 사용하여 조회하면 됩니다.
• 조회 조건
  • 주문일시 : 2022-01-01 ~ 2022-12-31
• 정렬 조건 : 주문일시(created_at) 오름차순

select 
      format_date('%Y-%m-%d', t1.created_at) as order_date,
      t2.name,
      t1.status,
      t2.cost,
      t2.retail_price,
      t2.category,
      t2.brand,

from `thelook_ecommerce.order_items` as t1
left join `thelook_ecommerce.products` as t2 on t1.product_id = t2.id
where t1.created_at between '2022-01-01' and  '2022-12-31'
order by created_at

날짜 between 써라 

<=, > 잘 못 쓸 것 같으면 그냥 between 써라 

 

SQL 연습문제 8-6

주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 주문한 상품의 브랜드 별 평균할인가격(brand_avg_sale_price)을 조회하시오.

• 조회 항목 : 브랜드(brand), 브랜드 별 평균할인가격(brand_avg_sale_price)
• 정렬 : 브랜드명으로 정렬

select 
      t2.brand,
      avg(t1.sale_price) as brand_avg_sale_price
from `thelook_ecommerce.order_items` as t1
left join `thelook_ecommerce.products` as t2 on t1.product_id = t2.id
group by t2.brand
order by t2.brand

t1.product_id = t2.id 가 답 근데 처음에 t1.id = t2.id로 했었음 잘 확인하고!! -> 근데 내가 그걸 어떻게 알지? 

SQL 연습문제 8-7

주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여

여성파트 주문완료상품의 날짜별 상품별 매출합계과 평균매출을 구하세요.

단 주문건수가 2개이상인 데이터만 표시하세요.

조회항목

• 주문일 (order_date)
  • 표시 형식 : 2022-01-01
• 상품명 (product_name)
• 주문건수 (order_item_count)
• 매출합계 (sum_sale_price) - 소수점 2자리까지 표시, 반올림
• 평균매출(avg_sale_price) - 소수점 2자리까지 표시, 반올림

필터 조건

• 상품의 department가 Women 입니다.
• 주문의 상태가 Complete 인 주문만 포함합니다.
• 주문건수가 2개 이상 데이터만 표시

정렬조건

• 주문일 오름차순
• 상품명 오름차순

 

SQL 연습문제 8-8

주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 주문id 당 매출 합계 정보를 구하세요.

group by order_id를 이용하세요.

• 조회 항목 :
  • 주문 id(order_id)
  • 비용 합계(sum_cost)
  • 판매가격 합계(sum_retail_price)
  • 총 이익(sum_profit)

select
  t1.order_id,
  sum(t2.cost) as sum_cost,
  sum(t2.retail_price) as sum_retail_price,
  sum(t2.retail_price - t2.cost) as sum_profit

from `thelook_ecommerce.order_items` as t1
left join `thelook_ecommerce.products` as t2 on t1.product_id = t2.id
group by order_id
order by order_id

 

SQL 연습문제 8-9

사용자 이벤트(events) 테이블에서 일별 이벤트타입별 이벤트 발생 횟수를 조회해보세요.

조회항목

• 이벤트 발생일(date)
• home 이벤트 횟수 (home)
• department 이벤트 횟수 (department)
• product 이벤트 횟수 (product)
• cart 이벤트 횟수 (cart)
• purchase 이벤트 횟수 (purchase)
• cancel 이벤트 횟수 (cancel)

정렬순서

• 이벤트 발생인 오름차순

select
  format_date('%Y-%m-%d', created_at) as date, 
  count(case when event_type = 'home' then 1 end) as home,
  count(case when event_type = 'department' then 1 end) as department,
  count(case when event_type = 'product' then 1 end) as product,
  count(case when event_type = 'cart' then 1 end) as cart,
  count(case when event_type = 'purchase' then 1 end) as purchase,
  count(case when event_type = 'cancel' then 1 end) as cancel

from `thelook_ecommerce.events`
group by date
order by date

 

---

프로그래머스 문제 8-1 (level 1)

상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

SELECT T2.FLAVOR
FROM ICECREAM_INFO AS T1
LEFT JOIN FIRST_HALF AS T2 ON T1.FLAVOR = T2.FLAVOR 
WHERE TOTAL_ORDER > 3000 AND INGREDIENT_TYPE = 'fruit_based'
GROUP BY FLAVOR
ORDER BY TOTAL_ORDER DESC

프로그래머스 문제 8-2 (level 2)

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

SELECT PRODUCT_CODE,
    (PRICE * SUM(SALES_AMOUNT)) AS SALES
FROM PRODUCT AS T1
LEFT JOIN OFFLINE_SALE AS T2 ON T2.PRODUCT_ID = T1.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC

프로그래머스 문제 8-3 (level 2)

상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

SELECT T2.FLAVOR
FROM ICECREAM_INFO AS T1
LEFT JOIN FIRST_HALF AS T2 ON T1.FLAVOR = T2.FLAVOR 
WHERE TOTAL_ORDER > 3000 AND INGREDIENT_TYPE = 'fruit_based'
GROUP BY FLAVOR
ORDER BY TOTAL_ORDER DESC

프로그래머스 문제 8-4 (level 2)

 

프로그래머스 문제 8-5 (level 3)

2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 카테고리명을 기준으로 오름차순 정렬해주세요.

SELECT CATEGORY,
SUM(SALES) AS TOTAL_SALES
FROM BOOK AS T1 
LEFT JOIN BOOK_SALES AS T2 ON T2. BOOK_ID = T1.BOOK_ID
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-01'
GROUP BY CATEGORY
ORDER BY CATEGORY ASC

 

프로그래머스 문제 8-6 (level 3)

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

SELECT T1.ANIMAL_ID, T1.NAME
FROM ANIMAL_INS AS T1
LEFT JOIN ANIMAL_OUTS AS T2 ON T1. ANIMAL_ID = T2.ANIMAL_ID
ORDER BY T2.DATETIME - T1.DATETIME DESC
LIMIT 2

프로그래머스 문제 8-7 (level 3)

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

SELECT T1.NAME, T1.DATETIME

FROM ANIMAL_INS AS T1 
LEFT JOIN ANIMAL_OUTS AS T2 ON T1.ANIMAL_ID = T2.ANIMAL_ID
WHERE ISNULL(T2.ANIMAL_ID)
ORDER BY T1.DATETIME
LIMIT 3

아직 입양 못 간 동물 -> WHERE ISNULL 로 찾아 줌

INNER JOIN인가 처음에 생각했는데 그럼 교집합이라 떠난 애들만 알 수 있게 

 

프로그래머스 문제 8-8 (level 3)

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

SELECT
T1.ANIMAL_ID, T1.NAME
FROM ANIMAL_INS AS T1 
LEFT JOIN ANIMAL_OUTS AS T2 ON T2.ANIMAL_ID = T1.ANIMAL_ID 

WHERE T1.DATETIME > T2.DATETIME
ORDER BY T1.DATETIME

 

프로그래머스 문제 8-9 (level 3)

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

SELECT T2.ANIMAL_ID, T2.NAME
FROM ANIMAL_INS AS T1 
RIGHT JOIN ANIMAL_OUTS AS T2 ON T2.ANIMAL_ID = T1.ANIMAL_ID
WHERE ISNULL(T1.ANIMAL_ID)
ORDER BY ANIMAL_ID

처음에 left join 해서 틀림 why?

left join으로 하면 animal_ins를 중심으로 join 되는 건데 유실된 기록은 t1에는 없지만 t2에는 있는 유실되지 않은 기록만 남게 됨 

right join하면 모두 다 남게 되고!

만약에 left join을 쓰고 싶으면 animal_out를 먼저 두면 됨 !