240702(+27) | SQL 코드카타 | ~50

청솔·2024년 7월 2일

SQL

목록 보기

9/23

Q6. 동명 동물 수 찾기

SELECT NAME,
       COUNT(*) COUNT
FROM ANIMAL_INS
GROUP BY 1
HAVING COUNT(NAME) >= 2
ORDER BY NAME

Q8. 상위 n개 레코드

SELECT NAME
       #DATETIME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1;

Q9. 최솟값 구하기

SELECT #NAME
       DATETIME
FROM ANIMAL_INS
ORDER BY DATETIME
limit 1;

Q10. 어린동물 찾기

SELECT  animal_id, name
from animal_ins
where intake_condition <> 'Aged'
order by 1

Q11. 여러 기준으로 정렬하기

동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회
단, 이름이 같은 동물 중에서는 보호를 나중에 시작한 동물을 먼저
SELECT animal_id, name, datetime
from animal_ins
order by name, datetime desc

Q12. 멍멍이 찾기

이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회
SELECT animal_id, name #animal_type
from animal_ins
where name like '%el%' 
and animal_type ='Dog'
order by name

Q13. 나이정보가 없는 회원

SELECT count(user_id) users
from user_info
where age is null

Q14. 가장 비싼 상품

SELECT max(price) MAX_PRICE
from product

Q15. NULL 처리

SELECT animal_type, 
       if(name is not null, name, 'No name'),
       sex_upon_intake
from animal_ins

Q16. 식품창고 목록 출력 ✏️

냉동시설 여부가 NULL인 경우, 'N'으로 출력, 창고 ID를 기준으로 오름차순 정렬

SELECT warehouse_id, warehouse_name, address,
       if(freezer_yn is not null , freezer_yn, 'N') freezer_yn
from food_warehouse
having substr(address,1,2) = '경기' 
order by warehouse_id

Q17. 생산공장 목록 출력

SELECT factory_id, factory_name, address
from food_factory
having substr(address,1,3) = '강원도'
order by factory_id

Q18. DATETIME -> DATE

자료형변환
시각(시-분-초)을 제외한 날짜(년-월-일)만 출력
SELECT animal_id, name, 
       date_format(date(datetime),'%Y-%m-%d') date
from animal_ins
order by animal_id

Q19. 의사 목록 출력

복수 범위 지정 in() 한번 더 떠올리기.

SELECT dr_name, dr_id, mcdp_cd,
       date_format(date(hire_ymd),'%Y-%m-%d') date
from doctor
where mcdp_cd in('CS','GS')
order by 4 desc, dr_name

Q20. 가장 비싼 식품 정보

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

Q21. 이름 없는 동물ID

SELECT animal_id
FROM animal_ins
where name is null

Q22. 회원수 ✏️

2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원의 수
SELECT count(*) from user_info
where substr(date_format(joined, '%Y-%m-%d'),1,4) = '2021'
and age between 20 and 29
또는 YEAR()함수를 사용해 연도를 출력할 수 있다
SELECT count(*) from user_info
where year(joined) = '2021'
and age between 20 and 29

Q23. 중성화 여부 파악

동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회, 중성화가 되어있다면 'O', 아니라면 'X'. (중성화된 동물은 'Neutered' 또는 'Spayed'라는 단어가 들어있다)
SELECT animal_id,
       name,
       case
        when sex_upon_intake like 'Neutered%' then 'O'
        when sex_upon_intake like 'Spayed%' then 'O'
        else 'X'
       end '중성화'
from animal_ins
order by 1

Q24. 카데고리 별 상품 수

상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력, 카테고리 코드 오름차순
select cut_code CATEGORY,
       count(*) PRODUCTS
from
(
SELECT product_id,
       substr(product_code,1,2) cut_code
from product
) sub1
group by 1
order by 1

Q25. 고양이와 개

SELECT animal_type,
       count(*)
from animal_ins
where animal_type in ('Cat','Dog')
group by 1
order by 1

Q26. 시간 별 입양 수

SELECT hour(datetime) hour_time,
       count(*) count
from animal_outs
where hour(datetime) between 9 and 19
group by 1
order by 1

Q27. 진료과 별 예약 횟수

진료과별 예약한 환자 수를 기준으로 오름차순 정렬, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순
SELECT mcdp_cd '진료과 코드',
       count(*)
       #substr(apnt_ymd,1,7) '5월 예약 건수'
from appointment
where substr(apnt_ymd,1,7) = '2022-05'
group by 1
order by 2,1

Q28. 12세 이하 여자 환자 목록

SELECT pt_name, pt_no, gend_cd,age,
       if(tlno is null,'NONE',tlno) tlno
from patient
where age <= 12 and gend_cd = 'W'
order by age desc, pt_name

Q29. 인기있는 아이스크림

select flavor 
from first_half
order by total_order desc, shipment_id

Q30. 옵션이 포함된 자동차

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

Q31. 입양 못 간 동물 목록 😭

i table=보호소 입소 동물 목록, o table=입양 간 동물 목록
i를 기준으로 모든 테이터를 가져오지만 o데이터가 비어있는 행이
아직 입양을 못 가고 보호소에 남아있는 동물이다.
join문 중에서 left join을 사용하면 된다.
SELECT i.name, i.datetime #o.animal_id
from animal_ins i
left join animal_outs o
on i.animal_id = o.animal_id
where o.animal_id is null
order by datetime
limit 3

Q32. 도서 판매량 합산

카테고리별

SELECT CATEGORY,
       SUM(sales) TOTAL_SALES
from
(SELECT bs.book_id BOOK_ID,
       bl.category CATEGORY,
       bs.sales sales
from book_sales bs
inner join book bl
on bs.book_id = bl.book_id
where substr(sales_date,1,7) = '2022-01'
) sub1
group by 1
order by 1

Q33. 상품별 오프라인 매출

SELECT pd.product_code,
       sum((pd.price*sa.sales_amount)) SALES
from offline_sale sa
inner join product pd
on sa.product_id = pd.product_id
group by 1
order by 2 desc,1

Q34. 있다 없다

보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회

SELECT ai.animal_id,
       ai.name
       #ai.datetime '보호소 입소',
       #ao.datetime '입양일'
from animal_ins ai
inner join animal_outs ao
on ai.animal_id = ao.animal_id
where ai.datetime > ao.datetime
order by ai.datetime

Q35. 입양 동물 보호 기간 ✏️

datediff() 함수를 사용해 기간을 계산 할 수 있다. 결과 값은 일 단위 이다.

날짜형을 바로 연산 하면 밀리초 단위로 결과 값이 나온다. 다양한 단위 변경에 사용된다.

select ao_id ANIMAL_ID, ao_name NAME
from 
(
    SELECT ao.name ao_name, ao.animal_id ao_id,
       ai.datetime '보호소 입소',
       ao.datetime '입양일',
       datediff(ao.datetime,ai.datetime) '보호기간'
from animal_ins ai
inner join animal_outs ao
on ai.animal_id = ao.animal_id
where datediff(ao.datetime,ai.datetime) > 0
order by 5 desc
    )sub1
limit 2

Q36. 중성화시킨 동물 목록

보호소에 들어올 당시에는 중성화 되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물

select ao_id, ao_type, name
from
(
    SELECT ai.sex_upon_intake ai_intake,
       ao.sex_upon_outcome ao_intake,
       ao.animal_id ao_id, 
       ao.animal_type ao_type, 
       ao.name name
from animal_ins ai
inner join animal_outs ao
on ai.animal_id = ao.animal_id
where ai.sex_upon_intake 
not in ('Neutered Male','Neutered Female','Spayed Female', 'Spayed Male')
) sub1
where ao_intake like 'Neutered%'
  or ao_intake like 'Spayed%'

Q37. 경제도서-저자 목록

SELECT b.book_id,a.author_name,
       date_format(date(b.published_date),'%Y-%m-%d') '출판일'
from author a
inner join book b
on a.author_id = b.author_id
where b.category = '경제'
order by 3

Q38. 출고상태 조회 ✏️

5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회

5월 1일 까지: 출고완료
5월 1일 이후: 출고 대기
미정: 출고미정

SELECT order_id,
       product_id,
       date_format(out_date,'%Y-%m-%d') '출고일자',
       case
        when substr(out_date,6,5) <= '05-01' then '출고완료'
        when substr(out_date,6,5) is null then '출고미정'
        else '출고대기'
       end '출고여부'
from food_order
#where substr(out_date,6,5) <= '05-01'
order by 1

Q39. 아이스크림 성분별 조회

SELECT t.ingredient_type '아이스크림 성분',
       sum(f.total_order) '판매량'
from first_half f
left join icecream_info t
on f.flavor = t.flavor
group by 1

Q40. 루시와 엘라 찾기

SELECT animal_id, name, sex_upon_intake 
from animal_ins
where name in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
order by 1

Q41. 도서 목록 조회

SELECT BOOK_ID,
       date_format(PUBLISHED_DATE, '%Y-%m-%d') PUBLISHED_DATE
from book
where category = '인문'
having substr(PUBLISHED_DATE,1,4) = '2021'
order by 2

Q42. 평균 렌트비

SELECT floor(avg(daily_fee)) AVERAGE_FEE
from CAR_RENTAL_COMPANY_CAR
where car_type = 'SUV'

Q43. 중고 거래 순위

SELECT u.USER_ID, u.NICKNAME, sum(price) TOTAL_SALES 
from USED_GOODS_BOARD b
inner join USED_GOODS_USER u
on b.writer_id = u.user_id
where b.status = 'DONE'
group by 1
having sum(price) >= '700000'
order by 3

Q44. 가격대별 상품 수

SELECT case
        when price between 0 and 9999 then '0'
        when price between 10000 and 19999 then '10000'
        when price between 20000 and 29999 then '20000'
        when price between 30000 and 39999 then '30000'
        when price between 40000 and 49999 then '40000'
        when price between 50000 and 59999 then '50000'
        when price between 60000 and 69999 then '60000'
        when price between 70000 and 79999 then '70000'
        when price between 80000 and 89999 then '80000'
        when price between 90000 and 99999 then '90000'
        else '100000~'
       end PRICE_GROUP,
       count(*) PRODUCTS
FROM PRODUCT
group by 1
order by 1

이게 최선인가 ㅜㅜ

Q45. 3월에 태어남

SELECT MEMBER_ID, MEMBER_NAME, GENDER,
      date_format(date_of_birth, '%Y-%m-%d') DATE_OF_BIRTH
from member_profile
where tlno is not null and gender = 'W'
having substr(DATE_OF_BIRTH,6,2) = '03'
order by 1

Q46. 차량 대여 기록

'세단'인 자동차들 중 10월에 대여를 시작한 차량 id를 내림차순으로 조회(중복x)
month() 함수 사용.
SELECT distinct(r.car_id)
# ,month(h.start_date),CAR_TYPE 
from CAR_RENTAL_COMPANY_CAR r
inner join CAR_RENTAL_COMPANY_RENTAL_HISTORY h
on r.car_id = h.car_id
where month(h.start_date) = 10 and car_type ='세단'
order by 1 desc

Q47. 모든 정보 조회

SELECT * from ANIMAL_INS
order by ANIMAL_ID

Q48. 즐겨찾기 순위 ✏️

서브쿼리를 사용해야 하는 이유
순위를 매긴 결과를 저장하여, 그 결과를 바탕으로 ranking = 1인 레코드를 필터링하기 위해.

select FOOD_TYPE, REST_ID, REST_NAME, favorites
from
(
    SELECT FOOD_TYPE, REST_ID, REST_NAME, favorites,
      rank() over ( 
                    partition by food_type
                    order by favorites desc
      ) ranking
from REST_INFO
) sub1
where ranking = 1
order by 1 desc

WITH( ) 함수로 가독성 높히기

with rank_table as(
    select FOOD_TYPE, REST_ID, REST_NAME, favorites,
      rank() over ( 
                    partition by food_type
                    order by favorites desc
      ) ranking
from REST_INFO
)
--
select FOOD_TYPE, REST_ID, REST_NAME, favorites
from rank_table
where ranking = 1
order by 1 desc

Q49. 가장 비싼 식품들

with rank_table as(
    SELECT CATEGORY,PRICE,PRODUCT_NAME,
         rank () over(
                      partition by CATEGORY
                      order by price desc
         ) ranking
    from FOOD_PRODUCT
    where CATEGORY in ('과자','국','김치','식용유')
)
--
select CATEGORY,PRICE as MAX_PRICE ,PRODUCT_NAME
from rank_table
where ranking = 1
order by 2 desc

Q50. 총 매출

SELECT 
    p.PRODUCT_ID, p.PRODUCT_NAME, 
    sum(p.price*amount) TOTAL_SALES
from FOOD_PRODUCT p
inner join FOOD_ORDER o
on o.product_id = p.product_id
where YEAR(PRODUCE_DATE) = 2022 
      AND MONTH(PRODUCE_DATE) = 5
group by 1, 2
order by 3 desc, 1

청솔

모든 사람이 쉽게 이해할 수 있는 데이터 분석을 지향하는 분석가가 되고 싶습니다. "데이터 분석은 사람을 설득 시킬 수단이다. "