24/06/28 SQL 문제

오늘은 프로그래머스 SQL 문제를 풀었습니다.

 

 

SQL

1. DATETIME에서 DATE로 형 변환(https://school.programmers.co.kr/learn/courses/30/lessons/59414)

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d')
FROM ANIMAL_INS

 

 

2. 흉부외과 또는 일반외과 의사 목록 출력하기(https://school.programmers.co.kr/learn/courses/30/lessons/132203)

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d')
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME

 

 

3. 가격이 제일 비싼 식품의 정보 출력하기(https://school.programmers.co.kr/learn/courses/30/lessons/131115)

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

 

 

4. 이름이 없는 동물의 아이디(https://school.programmers.co.kr/learn/courses/30/lessons/59039)

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME is NULL
ORDER BY ANIMAL_ID

 

 

5. 조건에 맞는 회원수 구하기(https://school.programmers.co.kr/learn/courses/30/lessons/131535)

SELECT COUNT(*)
FROM USER_INFO
WHERE AGE BETWEEN 20 AND 29 AND JOINED BETWEEN '2021-01-01' AND '2021-12-31'

 

 

6. 중성화 여부 파악하기(https://school.programmers.co.kr/learn/courses/30/lessons/59409)

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

 

 

7. 카테고리 별 상품 개수 구하기(https://school.programmers.co.kr/learn/courses/30/lessons/131529)

SELECT SUBSTRING(PRODUCT_CODE, 1, 2) as CATEGORY, COUNT(SUBSTRING(PRODUCT_CODE, 1, 2)) as PRODUCTS
FROM PRODUCT
GROUP BY SUBSTRING(PRODUCT_CODE, 1, 2)

 

 

8. 고양이와 개는 몇 마리 있을까(https://school.programmers.co.kr/learn/courses/30/lessons/59040)

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

 

 

9. 입양 시각 구하기(1)(https://school.programmers.co.kr/learn/courses/30/lessons/59412)

SELECT HOUR(DATETIME) HOUR, COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 09 AND 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

 

 

10. 진료과별 총 예약 횟수 출력하기(https://school.programmers.co.kr/learn/courses/30/lessons/132202)

SELECT MCDP_CD 진료과코드, COUNT(MONTH(APNT_YMD)) 5월예약건수
FROM APPOINTMENT
WHERE MONTH(APNT_YMD) = 5 AND YEAR(APNT_YMD) = 2022
GROUP BY MCDP_CD
ORDER BY COUNT(*), MCDP_CD

 

 

11. 12세 이하인 여자 환자 목록 출력하기(https://school.programmers.co.kr/learn/courses/30/lessons/132201)

SELECT PT_NAME, PT_NO, GEND_CD, AGE, CASE WHEN TLNO is NULL THEN 'NONE'
                                          ELSE TLNO
                                          end as TLNO
FROM PATIENT
WHERE GEND_CD = 'W' AND AGE <= 12
ORDER BY AGE DESC, PT_NAME

 

 

12. 인기있는 아이스크림(https://school.programmers.co.kr/learn/courses/30/lessons/133024)

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID

 

 

13. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기(https://school.programmers.co.kr/learn/courses/30/lessons/151137)

SELECT CAR_TYPE, COUNT(*) as CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE