[MySQL] Programmers - MySQL Lv.2

Programmers - MySQL Lv.2

https://school.programmers.co.kr/learn/challenges?order=recent&levels=2&languages=mysql&page=1

코딩테스트 연습 | 프로그래머스 스쿨

 

1. 최솟값 구하기

SELECT MIN(DATETIME)  AS 시간
FROM ANIMAL_INS

 

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

SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

 

3. 동명 동물 수 찾기

SELECT NAME, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(*)>1 and NAME IS NOT NULL
ORDER BY NAME

 

4. 루시와 엘라 찾기 (*)

# 풀이1
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME='Lucy' 
    OR NAME='Ella'
    OR NAME='Pickle'
    OR NAME='Rogan'
    OR NAME='Sabrina'
    OR NAME='Mitty'

# 풀이2
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

 

5. 이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%'
    AND ANIMAL_TYPE='Dog'
ORDER BY NAME

 

6. 동물 수 구하기

SELECT COUNT(*) AS count
FROM ANIMAL_INS

 

7. 중복 제거하기 (*)

# 풀이1
SELECT COUNT(a.NAME) AS count
FROM (SELECT NAME
        FROM ANIMAL_INS
        WHERE NAME IS NOT NULL
        GROUP BY NAME) a

# 풀이2
SELECT COUNT(DISTINCT NAME) AS count
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

 

8. 중성화 여부 파악하기 (*)

# 풀이1
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;

# 풀이2
SELECT ANIMAL_ID, NAME,
IF(SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%', 'O', 'X') as '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

 

9. NULL 처리하기

SELECT ANIMAL_TYPE, IF(NAME IS NULL, 'No name', NAME), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

10. 입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS count
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)>=9 AND HOUR(DATETIME)<20
GROUP BY HOUR
ORDER BY HOUR

 

11. DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

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

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE=(SELECT MAX(PRICE) FROM FOOD_PRODUCT)

 

13. 3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE gender='w'
    AND TLNO IS NOT NULL 
    AND MONTH(DATE_OF_BIRTH)=3
ORDER BY MEMBER_ID ASC;

 

14. 카테고리 별 상품 개수 구하기 (*)

SELECT SUBSTRING(PRODUCT_CODE, 1, 2) AS category, count(*) AS products
FROM PRODUCT
GROUP BY category

 

15. 가격대 별 상품 개수 구하기 (*)

SELECT
    floor(price/10000)*10000 as price_group,
    count(product_id) as products
FROM product
GROUP BY floor(price/10000)
ORDER BY floor(price/10000) ASC;

 

16. 상품 별 오프라인 매출 구하기 (*)

SELECT p.product_code, (p.price * SUM(o.sales_amount)) as sales
FROM product p INNER JOIN offline_sale o
ON p.product_id=o.product_id
GROUP BY p.product_id
ORDER BY sales DESC, product_code ASC

 

17. 재구매가 일어난 상품과 회원 리스트 구하기 (*)

SELECT user_id, product_id
FROM online_sale
GROUP BY user_id, product_id
HAVING count(*)>=2
ORDER BY user_id ASC, product_id desc

 

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

SELECT mcdp_cd AS "진료과 코드", COUNT(*) AS "5월예약건수"
FROM appointment
WHERE DATE_FORMAT(apnt_ymd, "%Y-%m")='2022-05'
GROUP BY mcdp_cd
ORDER BY 5월예약건수 ASC, mcdp_cd ASC

 

19. 성분으로 구분한 아이스크림 총 주문량

SELECT i.ingredient_type, sum(f.total_order) as total_order
FROM first_half f INNER JOIN icecream_info i
on f.flavor=i.flavor
GROUP BY i.ingredient_type
ORDER BY total_order ASC