728x90
반응형
sql을 공부한 지 시간이 좀 지나 기억을 되살리고자 프로그래머스에서 sql 코딩테스트 문제를 쭉 풀어보았습니다.
필요하신 문제는 ctrl + F로 찾아보시고 제가 어려웠던 문제는 볼드 처리해 두었습니다.
1. 모든 레코드 조회하기
select * from ANIMAL_INS
2. 역순 정렬하기
select NAME, DATETIME from ANIMAL_INS order by ANIMAL_ID Desc
3. 아픈 동물 찾기
select ANIMAL_ID, NAME from ANIMAL_INS where INTAKE_CONDITION = 'Sick' order by ANIMAL_ID
4. 어린 동물 찾기
select ANIMAL_ID, NAME from ANIMAL_INS where INTAKE_CONDITION != 'Aged' order by ANIMAL_ID
5. 동물의 아이디와 이름
select ANIMAL_ID, NAME from ANIMAL_INS order by ANIMAL_ID
6. 여러 기준으로 정렬하기
select ANIMAL_ID, NAME, DATETIME from ANIMAL_INS order by NAME asc, DATETIME desc
7. 상위 n개 레코드
select NAME from ANIMAL_INS order by DATETIME limit 1
8. 조건에 맞는 회원수 구하기
select count(USER_ID) from USER_INFO where left(JOINED, 4) = 2021 and AGE >= 20 and AGE <=29
9. 가장 비싼 상품 구하기
select max(PRICE) as MAX_PRICE from PRODUCT
10. 최댓값 구하기
select max(DATETIME) from ANIMAL_INS
11. 최솟값 구하기
select min(DATETIME) from ANIMAL_INS
12. 동물 수 구하기
select count(ANIMAL_ID) from ANIMAL_INS
13. 중복 제거하기
select count(distinct NAME) from ANIMAL_INS
14. 성분으로 구분한 아이스크림 총 주문량
select INGREDIENT_TYPE, sum(TOTAL_ORDER) as TOTAL_ORDER
from FIRST_HALF sale, ICECREAM_INFO info
where sale.FLAVOR = info.FLAVOR
group by info.INGREDIENT_TYPE
order by sum(TOTAL_ORDER)
15. 고양이와 개는 몇 마리 있을까
select ANIMAL_TYPE, count(ANIMAL_ID) from ANIMAL_INS group by ANIMAL_TYPE order by ANIMAL_TYPE asc
16. 동명 동물 수 찾기
select NAME, count(NAME) as COUNT
from ANIMAL_INS
group by NAME
having count(NAME) > 1
order by NAME
17. 년, 월, 성별 별 상품 구매 회원 수 구하기
select year(SALES_DATE) as YEAR, month(SALES_DATE) as MONTH, GENDER, count(distinct sale.USER_ID) as USER
from USER_INFO info, ONLINE_SALE sale
where info.USER_ID = sale.USER_ID and GENDER is not NULL
group by YEAR, MONTH, GENDER
18. 입양 시각 구하기(2)
set @HOUR = -1;
select (@HOUR := @HOUR + 1) as HOUR,
(select count(hour(DATETIME))
from ANIMAL_OUTS
where @HOUR = hour(DATETIME)) as COUNT
from ANIMAL_OUTS
where @HOUR < 23
19. 가격대 별 상품 개수 구하기
select floor(round(PRICE, -3)/10000)*10000 as PRICE_GROUP, count(PRODUCT_ID) as PRODUCT
from PRODUCT
group by PRICE_GROUP
order by PRICE_GROUP
20. 경기도에 위치한 식품창고 목록 출력하기
select WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS,
(case
when FREEZER_YN is null
then 'N'
else FREEZER_YN
end) as FREEZER_YN
from FOOD_WAREHOUSE
where left(ADDRESS, 3) = '경기도'
order by WAREHOUSE_ID
21. 이름이 없는 동물의 아이디
select ANIMAL_ID from ANIMAL_INS where NAME is null
22. 이름이 있는 동물의 아이디
select ANIMAL_ID from ANIMAL_INS where NAME is not null
23. NULL 처리하기
select ANIMAL_TYPE,
(case
when NAME is null
then 'No name'
else NAME
end)as NAME,
SEX_UPON_INTAKE
from ANIMAL_INS
order by ANIMAL_ID
24. 나이 정보가 없는 회원 수 구하기
select count(USER_ID) as USERS from USER_INFO where AGE is null
25. 그룹별 조건에 맞는 식당 목록 출력하기
select MEMBER_NAME, REVIEW_TEXT, date_format(REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
from
REST_REVIEW as review
join
(select MEMBER_ID, rank() over(order by count(MEMBER_ID) desc) as ranking
from REST_REVIEW
group by MEMBER_ID) rank_table
on review.MEMBER_ID = rank_table.MEMBER_ID
join MEMBER_PROFILE info
on review.MEMBER_ID = info.MEMBER_ID
where rank_table.ranking = 1
order by REVIEW_DATE, REVIEW_TEXT
26. 없어진 기록 찾기
select outs.ANIMAL_ID, outs.NAME
from ANIMAL_OUTS outs
left join ANIMAL_INS ins
on outs.ANIMAL_ID = ins.ANIMAL_ID
where ins.DATETIME is null
order by outs.ANIMAL_ID
27. 있었는데요 없었습니다
select ins.ANIMAL_ID, ins.NAME
from ANIMAL_INS ins join ANIMAL_OUTS outs on ins.ANIMAL_ID = outs.ANIMAL_ID
where ins.DATETIME > outs.DATETIME
order by ins.DATETIME
28. 오랜 기간 보호한 동물(1)
select ins.NAME, ins.DATETIME
from ANIMAL_INS ins left join ANIMAL_OUTS outs on ins.ANIMAL_ID = outs.ANIMAL_ID
where outs.DATETIME is null
order by ins.DATETIME
limit 3
29. 보호소에서 중성화한 동물
select outs.ANIMAL_ID, outs.ANIMAL_TYPE, outs.NAME
from ANIMAL_INS ins join ANIMAL_OUTS outs on ins.ANIMAL_ID = outs.ANIMAL_ID
where left(ins.SEX_UPON_INTAKE, 1) = 'I' and left(outs.SEX_UPON_OUTCOME, 1) != 'I'
30. 상품 별 오프라인 매출 구하기
select PRODUCT_CODE, sum(PRICE * SALES_AMOUNT) as SALES
from PRODUCT join OFFLINE_SALE on PRODUCT.PRODUCT_ID = OFFLINE_SALE.PRODUCT_ID
group by PRODUCT_CODE
order by SALES desc, PRODUCT_CODE
31. 상품을 구매한 회원 비율 구하기
select year(SALES_DATE) as YEAR, month(SALES_DATE) as MONTH, count(distinct info.USER_ID) as PUCHASED_USERS,
round(count(distinct info.USER_ID)/
(select count(distinct USER_ID) from USER_INFO where year(JOINED) = 2021),1) as PUCHASED_RATIO
from USER_INFO info join ONLINE_SALE sale on info.USER_ID = sale.USER_ID
where year(JOINED) = 2021
group by YEAR, MONTH
order by YEAR, MONTH
32. 취소되지 않은 진료 예약 조회하기
select APNT_NO, PT_NAME, appoint.PT_NO, appoint.MCDP_CD, DR_NAME, APNT_YMD
from PATIENT, DOCTOR, APPOINTMENT appoint
where appoint.MCDP_CD = 'CS' and left(APNT_YMD, 10) = '2022-04-13' and APNT_CNCL_YN = 'N'
and PATIENT.PT_NO = appoint.PT_NO and DOCTOR.DR_ID = appoint.MDDR_ID
order by APNT_YMD
33. 루시와 엘라 찾기
select ANIMAL_ID, NAME, SEX_UPON_INTAKE from ANIMAL_INS
where NAME in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
34. 이름에 el이 들어가는 동물 찾기
select ANIMAL_ID, NAME from ANIMAL_INS where NAME like '%el%' and ANIMAL_TYPE = 'Dog' order by NAME
35. 중성화 여부 파악하기
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
36. 오랜 기간 보호한 동물(2)
select ins.ANIMAL_ID, ins.NAME
from ANIMAL_INS ins join ANIMAL_OUTS outs on ins.ANIMAL_ID = outs.ANIMAL_ID
where outs.DATETIME is not null
order by outs.DATETIME-ins.DATETIME desc
limit 2
37. 카테고리 별 상품 개수 구하기
select left(PRODUCT_CODE, 2) as CATEGORY, count(PRODUCT_ID) as PRODUCTS from PRODUCT
group by left(PRODUCT_CODE, 2)
order by left(PRODUCT_CODE, 2)
38. 입양 시각 구하기(1)
select hour(DATETIME) as HOUR, count(ANIMAL_ID) as COUNT
from ANIMAL_OUTS
where hour(DATETIME) >= 9 and hour(DATETIME) <= 19
group by hour(DATETIME)
order by hour(DATETIME)
39. DATETIME에서 DATE로 형 변환
select ANIMAL_ID, NAME, date_format(DATETIME, '%Y-%m-%d') as 날짜 from ANIMAL_INS
order by ANIMAL_ID
40. 우유와 요거트가 담긴 장바구니
select milk.CART_ID
from
(select CART_ID from CART_PRODUCTS
where NAME = 'Milk'
group by CART_ID) milk
inner join
(select CART_ID from CART_PRODUCTS
where NAME = 'Yogurt'
group by CART_ID
) yogurt
on milk.CART_ID = yogurt.CART_ID
41. 헤비 유저가 소유한 장소
select ID, NAME, PLACES.HOST_ID
from
(select HOST_ID, count(ID) from PLACES group by HOST_ID having count(ID) > 1) heavy
left join PLACES
on heavy.HOST_ID = PLACES.HOST_ID
order by ID
42. 강원도에 위치한 생산공장 목록 출력하기
select FACTORY_ID, FACTORY_NAME, ADDRESS from FOOD_FACTORY where left(ADDRESS, 3) = '강원도'
order by FACTORY_ID
43. 재구매가 일어난 상품과 회원 리스트 구하기
select USER_ID, PRODUCT_ID from ONLINE_SALE
group by USER_ID, PRODUCT_ID
having count(ONLINE_SALE_ID) > 1
order by USER_ID, PRODUCT_ID desc
44. 오프라인/온라인 판매 데이터 통합하기
select date_format(SALES_DATE,'%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
from ONLINE_SALE
where left(SALES_DATE, 7) = '2022-03'
union all
select date_format(SALES_DATE,'%Y-%m-%d') as SALES_DATE, PRODUCT_ID, null as USER_ID, SALES_AMOUNT
from OFFLINE_SALE
where left(SALES_DATE, 7) = '2022-03'
order by SALES_DATE, PRODUCT_ID, USER_ID
45. 조건별로 분류하여 주문상태 출력하기
select ORDER_ID, PRODUCT_ID, date_format(OUT_DATE, '%Y-%m-%d') as OUT_DATE,
(case
when OUT_DATE < '2022-05-02'
then '출고완료'
when OUT_DATE is null
then '출고미정'
else '출고대기'
end) as 출고여부
from FOOD_ORDER
order by ORDER_ID
46. 가격이 제일 비싼 식품의 정보 출력하기
select PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE from FOOD_PRODUCT
order by PRICE desc limit 1
47. 식품분류별 가장 비싼 상품의 정보 조회하기
select CATEGORY, PRICE as MAX_PRICE, PRODUCT_NAME from FOOD_PRODUCT
where PRICE in (
select MAX(PRICE) as MAX_PRICE
from FOOD_PRODUCT
group by CATEGORY
)
and CATEGORY in ('과자', '국', '김치', '식용유')
order by PRICE desc
48. 5월 식품들의 총매출 조회하기
select odr.PRODUCT_ID, prod.PRODUCT_NAME, (PRICE * sum(AMOUNT)) as TOTAL_SALES
from FOOD_ORDER odr join FOOD_PRODUCT prod on odr.PRODUCT_ID = prod.PRODUCT_ID
where year(PRODUCE_DATE) = 2022 and month(PRODUCE_DATE) = 5
group by PRODUCT_ID
order by TOTAL_SALES desc, PRODUCT_ID
49. 서울에 위치한 식당 목록 출력하기
select info.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, round(avg(REVIEW_SCORE),2) as SCORE
from REST_INFO info join REST_REVIEW review on info.REST_ID = review.REST_ID
where left(ADDRESS, 2) = '서울'
group by REST_ID
order by SCORE desc, FAVORITES desc
50. 3월에 태어난 여성 회원 목록 출력하기
select MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d') as DATE_OF_BIRTH
from MEMBER_PROFILE
where month(DATE_OF_BIRTH) = 3 and GENDER = 'W' and TLNO is not null
order by MEMBER_ID
51. 즐겨찾기가 가장 많은 식당 정보 출력하기
select FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
from REST_INFO
where FAVORITES in (
select MAX(FAVORITES)
from REST_INFO
group by FOOD_TYPE
)
group by FOOD_TYPE
order by FOOD_TYPE desc
52. 12세 이하인 여아 환자 목록 출력하기
select PT_NAME, PT_NO, GEND_CD, AGE,
(case
when TLNO is null
then 'NONE'
else TLNO
end)as TLNO
from PATIENT
where AGE <= 12 and GEND_CD = 'W'
order by AGE desc, PT_NAME
53. 진료과별 총 예약 횟수 출력하기
select MCDP_CD as 진료과코드, count(APNT_NO) as 5월예약건수 from APPOINTMENT
where APNT_YMD like '2022-05%'
group by MCDP_CD
order by count(APNT_NO), MCDP_CD
54. 흉부외과 또는 일반외과 의사 목록 출력하기
select DR_NAME, DR_ID, MCDP_CD, date_format(HIRE_YMD, '%Y-%m-%d') as HIRE_YMD from DOCTOR
where MCDP_CD in ('CS', 'GS')
order by HIRE_YMD desc, DR_NAME
55. 인기있는 아이스크림
select FLAVOR from FIRST_HALF order by TOTAL_ORDER desc, SHIPMENT_ID
56. 과일로 만든 아이스크림 고르기
select ICECREAM_INFO.FLAVOR from FIRST_HALF, ICECREAM_INFO
where FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR and INGREDIENT_TYPE = 'fruit_based'
and TOTAL_ORDER > 3000
order by TOTAL_ORDER desc
57. 주문량이 많은 아이스크림들 조회하기
select FIRST_HALF.FLAVOR from FIRST_HALF, JULY
where FIRST_HALF.FLAVOR = JULY.FLAVOR
group by FIRST_HALF.FLAVOR
order by sum(FIRST_HALF.TOTAL_ORDER+JULY.TOTAL_ORDER) desc
limit 3
58. 조건에 맞는 도서 리스트 출력하기
select BOOK_ID, date_format(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE from BOOK
where year(PUBLISHED_DATE) = 2021 and CATEGORY = '인문'
order by PUBLISHED_DATE
59. 조건에 맞는 도서와 저자 리스트 출력하기
select BOOK_ID, AUTHOR_NAME, date_format(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
from BOOK join AUTHOR on BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
where CATEGORY = '경제'
order by PUBLISHED_DATE
60. 카테고리 별 도서 판매량 집계하기
select CATEGORY, sum(SALES) as TOTAL_SALES
from BOOK a join BOOK_SALES b on a.BOOK_ID = b.BOOK_ID
where SALES_DATE like '2022-01%'
group by CATEGORY
order by CATEGORY
61. 저자 별 카테고리 별 매출액 집계하기
select b.AUTHOR_ID, AUTHOR_NAME, CATEGORY, sum(SALES * PRICE) as TOTAL_SALES
from BOOK_SALES a join BOOK b on a.BOOK_ID = b.BOOK_ID
join AUTHOR c on b.AUTHOR_ID = c.AUTHOR_ID
where SALES_DATE like '2022-01%'
group by b.AUTHOR_ID, CATEGORY
order by b.AUTHOR_ID, CATEGORY desc
728x90
반응형
'데이터 분석 > SQL' 카테고리의 다른 글
| Codility SQL 테스트 문제 답변 기록 (0) | 2023.01.25 |
|---|