DAY 13. 문제 11~30 (Lv.2)

20. 가격이 제일 비싼 식품의 정보 출력하기

문제 동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL문을 작성. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 친다

 

풀이1: order by - limit 

select product_id,product_name,product_cd, category,price
from food_product
order by price desc
limit 1

 

풀이2: where  subquery

select product_id,product_name, product_cd, category, price
from food_product
where price = (
select max(price) as max_price
from food_product )

 

풀이3: CTE

with CTE as (
select max(price) as max_price
from food_product)

select product_id, product_name, product_cd, category, max_price as price
from food_product f inner join cte on f.price = cte.max_price

 

 

 

1) max(price)를 쓰면 안 되는 이유

select max(price)는 "최고 가격"만 반환. 하지만 문제에서는 "최고 가격을 가진 여러 컬럼"을 요구함. 다시 말해 집계함수는, 해당 컬럼만 요약해서 반환하는 구조

 

2) having으로 풀면 안 되는 이유

having의 용도 group by로 그룹핑된 결과에 대해서 조건을 걸 때 = 집계함수의 결과에만 적용

하지만 이 문제는 그룹핑이 필요 없음 = 단일행 조회 문제 = 특정 행의 집계 결과가 아니라 전체 테이블에서 조건을 거는 문제

 

23. 중성화 여부 파악하기

 

문제  보호소의 동물이 중성화되었는지 아닌지 파악. 중성화된 동물은 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 'sex_upon_intake'
from animal_ins
order by animal_id

노트 then 'O' 로 같은 출력값 있는 조건의 경우, or로 한 줄로 쓰는 것이 보기 편함

 

24. 카테고리 별 상품 개수 구하기

문제 PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬

 

풀이1: left (컬럼, 몇 글자)

select left(product_code, 2) as category,
       count(*) as products
from product
group by 1
order by 1

 

풀이2: substr (컬럼, 어디부터, 몇 글자)

select substr(product_code, 1, 2) as category,
       count(*) as products
from product
group by 1
order by 1

공통 count(*) 집계함수 썼기 때문에 select의 남은 컬럼으로 group by 필수

 

25. 고양이와 개는 몇 마리 있을까

문제 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성. 이때 고양이를 개보다 먼저 조회

select animal_type, 
       count(*) as count
from animal_ins
where animal_type in ('cat', 'dog')
group by 1
order by 1 asc

노트 in(값1, 값2) 는 or 조건으로 일치하는 내용이 있는지 찾는 것

 

27. 진료과별 총 예약 횟수 출력하기

문제 APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬

 

풀이1: date_format( )

select mcdp_cd,
       count(*) as '5월예약건수'
from appointment
where date_format(apnt_ymd, '%Y-%m') = '2022-05'
group by 1 
order by 2 asc, 1 asc

 

풀이2: year ( ), month( )

select mcdp_cd,
       count(*) as '5월예약건수'
from appointment
where year(apnt_ymd) = 2022 and month(apnt_ymd) = 05
group by 1 
order by 2 asc, 1 asc

1) date_format, year, month의 차이

date_format (컬럼, '%Y-%m'): 문자열 (varchar) 타입 출력

year( ), month( ): 정수(integer) 타입 출력

2) where 절에 함수 사용 가능

where절에는 함수식(date_format, year, substr, upper 등 문자열, 날짜, 수치 함수 등) 자유롭게 사용 가능.

단, 집계함수(count, sum, avg, max, min)은 사용할 수 없음

3) group by

count(*) 집계함수 썼기 때문에 select에서 이를 제외한 나머지 컬럼으로 모두 group by 필수

 

30. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

문제 CAR_RENTAL_COMPANY_CAR테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성. 이때 자동차 수에 대한 컬럼명은CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬

 

풀이1: where 컬럼 like (' % %') or... 

select car_type, 
       count(*) as cars
from car_rental_company_car
where options like ('%통풍시트%') or options like ('%열선시트%') or options like ('%가죽시트%')
group by 1
order by 1

 

풀이2: where 컬럼 regexp ' | '

select car_type, 
       count(*) as cars
from car_rental_company_car
where options regexp '통풍시트|열선시트|가죽시트'
group by 1
order by 1

노트 

options like ('% %') 를 3번 길게 쓰는 것 보다는 regexp ' A | B | C ' 백 배 편함 + 괄호 쓸 필요 없음!!

count(*) 집계함수 썼기 때문에 group by 1 필수