[프로그래머스/SQL 고득점 Kit/MySQL] SELECT

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

프로그래머스

---

<SELECT>
Q.3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID,MEMBER_NAME,GENDER,DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d")
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3
AND GENDER='W'
AND TLNO is not null 
ORDER BY MEMBER_ID;
/*
DATE_FORMAT(DATE_OF_BIRTH, 원하는 형식) 
DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") AS BIRTH, #1992-03-16
DATE_FORMAT(DATE_OF_BIRTH, "%Y") AS YEAR, 
DATE_FORMAT(DATE_OF_BIRTH, "%m") AS MONTH,
DATE_FORMAT(DATE_OF_BIRTH, "%d") AS DAY
*/

Q.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
/*
IFNULL(Column명, "Null일 경우 대체 값")
*/

Q.평균 일일 대여 요금 구하기

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

Q.인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF 
ORDER BY TOTAL_ORDER DESC,SHIPMENT_ID ASC

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

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

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

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

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

SELECT
    ugb.TITLE
    , ugb.BOARD_ID
    , ugr.REPLY_ID
    , ugr.WRITER_ID
    , ugr.CONTENTS
    , DATE_FORMAT(ugr.CREATED_DATE,"%Y-%m-%d") as CREATED_DATE
FROM USED_GOODS_BOARD as ugb
INNER JOIN USED_GOODS_REPLY as ugr
ON ugb.BOARD_ID = ugr.BOARD_ID
WHERE YEAR(ugb.CREATED_DATE) = 2022 
AND MONTH(ugb.CREATED_DATE) = 10 
ORDER BY ugr.CREATED_DATE, ugb.TITLE

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

SELECT ii.FLAVOR 
FROM ICECREAM_INFO as ii
INNER JOIN FIRST_HALF as fh 
ON ii.FLAVOR = fh.FLAVOR
WHERE fh.TOTAL_ORDER>3000
and ii.INGREDIENT_TYPE = 'fruit_based'
ORDER BY fh.TOTAL_ORDER DESC

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

SELECT 
    ri.REST_ID
    ,ri.REST_NAME
    ,ri.FOOD_TYPE
    ,ri.FAVORITES
    ,ri.ADDRESS
    ,ROUND(AVG(rr.REVIEW_SCORE),2) as SCORE
FROM REST_INFO  as ri
INNER JOIN REST_REVIEW as rr
ON ri.REST_ID = rr.REST_ID
WHERE ri.ADDRESS LIKE '서울%'
GROUP BY ri.REST_ID #***
ORDER BY SCORE DESC, ri.FAVORITES DESC

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

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

Q.재구매가 일어난 상품과 회원 리스트 구하기 

SELECT USER_ID,PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID,PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID, PRODUCT_ID DESC

Q.모든 레코드 조회하기

SELECT ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS  
ORDER BY ANIMAL_ID

Q.오프라인/온라인 판매 데이터 통합하기 

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as SALES_DATE
    ,PRODUCT_ID
    ,USER_ID
    ,SALES_AMOUNT
FROM ONLINE_SALE 
WHERE MONTH(SALES_DATE)=3
#WHERE SALES_DATE LIKE '2022-03%'
#WHERE DATE_FORMAT(ons.SALES_DATE,"%Y-%m")='2022-03'
UNION
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as SALES_DATE
    ,PRODUCT_ID
    ,NULL as USER_ID
    ,SALES_AMOUNT
FROM OFFLINE_SALE 
WHERE MONTH(SALES_DATE)=3
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID
/*
UNION (DISTINCT) : 쿼리의 결과를 합친다. 중복된 ROW는 제거 
<-> UNION ALL
*/

Q.역순 정렬하기

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

Q.아픈 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS  
WHERE INTAKE_CONDITION='Sick'
ORDER BY ANIMAL_ID

Q.어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS  
WHERE INTAKE_CONDITION!='Aged'
ORDER BY ANIMAL_ID

Q.동물의 아이디와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

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

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS  
ORDER BY NAME , DATETIME DESC

Q.상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

Q.조건에 맞는 회원수 구하기 

SELECT count(*) #count(USER_ID) as USERS
FROM USER_INFO
WHERE AGE between 20 and 29 #AGE >= 20 and AGE<=29
and YEAR(JOINED)=2021