프로그래머스 SQL 고득점 kit - SELECT 1

프로그래머스의 SQL 고득점 kit를 해결한 나만의 풀이 쿼리!
SELECT 문항에서 어려웠던 점은 없었다 ㅎㅎ
오랜만에 쿼리를 작성해 까먹었던 함수들을 상기하면서 연습하기 딱 좋은 난이도!
MySQL Syntax로 진행했습니다.

📌 1. 12세 이하인 여자 환자 목록 출력하기

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC

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

SELECT MEMBER_ID, MEMBER_NAME, GENDER, 
       DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE DATE_OF_BIRTH LIKE '%-03-%'
AND GENDER = 'W'
AND TLNO IS NOT NULL
ORDER BY MEMBER_ID

📌 3. 인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF 
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID

📌 4. 흉부외과 또는 일반외과 의사 목록 출력하기

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

📌 5. 과일로 만든 아이스크림 고르기

SELECT ICECREAM.FLAVOR
FROM FIRST_HALF HALF
JOIN ICECREAM_INFO ICECREAM
ON HALF.FLAVOR = ICECREAM.FLAVOR
AND HALF.TOTAL_ORDER >= 3000
AND ICECREAM.INGREDIENT_TYPE = 'fruit_based'
ORDER BY HALF.TOTAL_ORDER DESC

📌 6. 강원도에 위치한 생산공장 목록 출력하기

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원%'
ORDER BY FACTORY_ID

📌 7. 조건에 맞는 도서 리스트 출력하기

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d')
FROM BOOK
WHERE CATEGORY = '인문' AND LEFT(PUBLISHED_DATE, 4) = '2021'

📌 8. 평균 일일 대여 요금 구하기

SELECT ROUND(AVG(DAILY_FEE),1) AS 'AVERAGE_FEE'
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

📌 9. 조건에 부합하는 중고거래 댓글 조회하기

SELECT BOARD.TITLE, BOARD.BOARD_ID, REPLY.REPLY_ID,
       REPLY.WRITER_ID, REPLY.CONTENTS,
       DATE_FORMAT(REPLY.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD BOARD
JOIN USED_GOODS_REPLY REPLY
ON BOARD.BOARD_ID = REPLY.BOARD_ID
AND BOARD.CREATED_DATE LIKE '2022-10%'
ORDER BY REPLY.CREATED_DATE, BOARD.TITLE;

📌 10. 서울에 위치한 식당 목록 출력하기

SELECT REST.REST_ID, REST.REST_NAME, REST.FOOD_TYPE,
       REST.FAVORITES, REST.ADDRESS, ROUND(AVG(REVIEW.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO REST
JOIN REST_REVIEW REVIEW
ON REST.REST_ID = REVIEW.REST_ID
AND REST.ADDRESS LIKE '서울%'
GROUP BY REVIEW.REST_ID
ORDER BY SCORE DESC, REST.FAVORITES DESC