[SQL] 조건분기(CASE, IF) 연습문제

SQL 연습문제 7-1

주문정보(orders) 테이블에서 order_id, gender, gender_label(gender의 값에 따른 성별을 한글로 표시)을 해주세요.

• 필드명 : gender_label
  • gender가 F 이면 '여성'
  • gender가 M 이면 '남성'
• 결과로 표시할 필드
  • order_id
  • gender
  • gender_label
• 정렬순서 : order_id 오름차순

select order_id,gender, 
      case 
      when gender = 'F' then '여성'
      when gender = 'M' then '남성' 
      end as gender_label
from `thelook_ecommerce.orders`
order by order_id

-> case when 은 , 를 붙이지 않는다. case 하나당 end는 하나

 

SQL 연습문제 7-2

회원(users) 테이블에서 다음 정보를 조회하세요.

1. 조회 항목

• 유저아이디 - id,
• 가입연도 - year
• 가입월 - month
• 가입일 - day
• 이용경로(traffic_source) 한글 텍스트 - traffic_source_label
  • Search → 검색엔진
  • Organic → 검색결과
  • Email → 이메일
  • Display → 디스플레이 광고
  • Facebook → 페이스북

1. 정렬순서

• id 오름차순

select id,
      extract(year from created_at) as year, 
      extract(month from created_at) as month, 
      extract(day from created_at) as day, 
      case
      when traffic_source = 'Search' then '검색엔진'
      when traffic_source = 'Organic' then '검색결과'
      when traffic_source = 'Email' then '이메일'
      when traffic_source = 'Display' then '디스플레이 광고'
      when traffic_source = 'Facebook' then '페이스북'
      end as traffic_source_label

from `thelook_ecommerce.users`
order by id

SQL 연습문제 7-3

회원(users) 테이블에서 가입연도별 이용경로(traffic_source)별 가입자수를 조회하세요.

조회 항목

• year
• Search
• Organic
• Email
• Display
• Facebook
• Total

select extract(year from created_at) as year,
        count(CASE WHEN traffic_source = 'Search' THEN traffic_source END) AS Search,
        count(CASE WHEN traffic_source = 'Organic' THEN traffic_source END) AS Organic,
        count(case when traffic_source = 'Email' then traffic_source end) as Email,
        count(case when traffic_source = 'Display' then traffic_source end) as Display,
        count(case when traffic_source = 'Facebook' then traffic_source end) as Facebook,
        count(traffic_source) as total
from `thelook_ecommerce.users`
group by year
order by year

-> as 뒤에 '' 감싸주는 것 아님 조건이 traffic_source니까 count도 traffic_source 해줘야 함 !

 

SQL 연습문제 7-4

주문정보(orders) 테이블에서 주문을 3번 이상 구매한 사람의 등급을 ‘Gold’, 4번 이상 구매한 사람의 등급은 ‘VIP’, 그 외에는 'Silver'라고 등급을 지정해 줍니다.

select user_id,
case 
when count(user_id)>= 4 then 'VIP'
when count(user_id) >= 3 then 'Gold'
when count(user_id) < 3 then 'Silver'

end as Grade

from `thelook_ecommerce.orders`
group by user_id
order by user_id

when count(user_id)>= 4 then 'VIP' 부터 써야 함 -> >=3 을 먼저 쓰면 count(user_id)가 4인 경우 >=3 조건에 만족해서 Gold로 나

SQL 연습문제 7-5

상품정보(products) 테이블에서 상품의 id, 상품명(name), 사이즈(size)라는 컬럼을 하나 만들도록 하겠습니다.

상품명 맨 끝에 ‘XS’, ‘S’, ‘M’, ‘L’, ‘XL’, ‘XXL’ 라고 적혀져 있습니다. 사이즈 컬럼에 맞는 값을 저장하고 조건에 부합하지 않는 경우에는 NULL 값을 넣어줍니다.

• 정렬순서 : size 내림차순

select name, 
case
when name like '%XS' then 'XS'
when name like '%XXL' then 'XXL'
when name like '%XL' then 'XL'
when name like '%S' then 'S'
when name like '%M' then 'M'
when name like '%L' then 'L'
else NULL
end as size

from `thelook_ecommerce.products`
order by size desc

SQL 연습문제 7-6

각 연도의 분기별 매출 합계

order_items 테이블에서 각 연도의 분기별 매출합계을 표시하세요.

order_items의 status가 Complete 인 항목만 포함합니다.

표시항목

• year
• quarter
• sum_sale_price - 소수점 2자리 반올림

정렬 순서

• year 오름차순
• quarter 오름차순

select 
extract(year from created_at) as year,
extract(quarter from created_at) as quarter,
round(sum(sale_price), 2) as sum_sale_price
from `thelook_ecommerce.order_items`
where status = 'Complete'
group by year, quarter
order by year, quarter

SQL 연습문제 7-7

order_items 테이블에서 각 연도의 분기별 매출을 가로로 펼쳐서 표시하세요.

order_items의 status가 Complete 인 항목만 포함합니다.

각 쿼터별 매출합계는 소수점 2자리까지 반올림하여 표시합니다.

표시 항목

• YEAR
• Q1
• Q2
• Q3
• Q4
• TOTAL

정렬순서

• YEAR

select 
extract(year from created_at) as year,
round(sum(case when extract(quarter from created_at) = 1 then sale_price end), 2) as Q1,
round(sum(case when extract(quarter from created_at) = 2 then sale_price end), 2) as Q2,
round(sum(case when extract(quarter from created_at) = 3 then sale_price end), 2) as Q3,
round(sum(case when extract(quarter from created_at) = 4 then sale_price end), 2) as Q4,
round(sum(sale_price), 2) as TOTAL

from `thelook_ecommerce.order_items`
where status = 'Complete'
group by year
order by year

 

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

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

SELECT ANIMAL_ID, NAME,
CASE WHEN SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O'
ELSE 'X'
END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID, 'NAME', '중성화'

 

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

FOOD_ORDER 테이블에서 5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요. 출고여부는 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력해주시고, 결과는 주문 ID를 기준으로 오름차순 정렬해주세요.

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE,
    CASE
    WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
    WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
    ELSE '출고미정'
    END AS '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC