[SQL] 프로그래머스 lv1~lv5

mysql 풀이

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

• PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요.

• 조건
  1) 12세 이하 & 여자 환자
  2) IFNULL로 전화번호가 없는 환자는 NONE로 표시

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;

LV.1 인기있는 아이스크림

• 상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성해주세요.

• 조건
  1) ORDER BY : 총주문량 기준 내림차순 정렬 + 출하기준 오름차순 정렬

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC

LV.4 식품분류별 가장 비싼 식품의 정보 조회하기

• FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요

• 첫번째 풀이

SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT 
WHERE CATEGORY IN ('과자', '국', '김치', '식용유') 
GROUP BY CATEGORY
ORDER BY MAX_PRICE DESC;

-> 정답에 일치하는 MAX_PRICE는 가져오지만, 이와 일치하는 상품명을 가져오지 못함.

• 정답

SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT 
WHERE CATEGORY IN ('과자', '국', '김치', '식용유') AND 
    PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY )
GROUP BY CATEGORY
ORDER BY MAX_PRICE DESC;

• where절 서브쿼리를 이용해서 max_price와 일치하는 가격의 상품명을 가져오도록 함.

LV.4 5월 식품들의 총매출 조회하기

• FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

• 조인을 사용해서 가격과 수량으로 총 매출 계산

• SUBSTR으로 5월 가져오기

SELECT A.PRODUCT_ID,A.PRODUCT_NAME,SUM(B.AMOUNT * A.PRICE) AS TOTAL_SALES
FROM FOOD_PRODUCT A LEFT JOIN FOOD_ORDER B ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE SUBSTR(B.PRODUCE_DATE,1,7) = '2022-05'
GROUP BY PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC 

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

• REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

• 조건
  0) 조인으로 테이블 결합 (inner)
  1) 서울에 위치한 식당 가져오기 (like)
  2) 리뷰 평균점수 반올림(ROUND)
  3) 결과는 평균점수 DESC, 즐겨찾기 ASC

SELECT A.REST_ID
     , REST_NAME
     , FOOD_TYPE
     , FAVORITES
     , ADDRESS
     , ROUND(AVG(B.REVIEW_SCORE),2) AS SCORE

FROM REST_INFO A INNER JOIN  REST_REVIEW B ON A.REST_ID = B.REST_ID
WHERE ADDRESS LIKE ('서울%')
GROUP BY A.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC

LV.4 년, 월, 성별 별 상품 구매 회원 수 구하기

• USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

• 조건
  1) GROUP BY로 년,월,성별로 묶기
  2) 성별 IS NOT NULL
  3) ★ DISTINCT COUNT 활용
  -> 1월 한 사용자가 두 번 이상 구매한 이력이 있기 때문에 DISTINCT 는 필수

SELECT 	YEAR(SALES_DATE) YEAR, 
	  	MONTH(SALES_DATE) MONTH,
        GENDER, 
        COUNT(DISTINCT B.USER_ID) USERS
FROM ONLINE_SALE A INNER JOIN USER_INFO B ON A.USER_ID = B.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR,MONTH,GENDER 

LV.4 취소되지 않은 진료 예약 조회하기

• PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.

• 조건
  0) 다중 조인
  - A JOIN B JOIN C ON A.~=B.~ AND A.~ = C.~
  1) 취소되지 않은 예약내역 가져오기(APPOINTMENT)
  2) 2022년 4월 13일 (APPOINTMENT)
  3) 흉부외과
  4) APPOINTMENT 의사ID : MDDR_ID
  DOCTOR 의사ID : DR_ID

SELECT  A.APNT_NO,
        P.PT_NAME,
        P.PT_NO,
        D.MCDP_CD, -- 진료코드
        D.DR_NAME,
        APNT_YMD
FROM PATIENT P JOIN APPOINTMENT A JOIN DOCTOR D
    ON P.PT_NO = A.PT_NO AND  A.MDDR_ID = D.DR_ID
WHERE APNT_CNCL_YN = 'N' AND 
	  SUBSTR(APNT_YMD,1,10) = '2022-04-13' AND A.MCDP_CD ='CS'
GROUP BY PT_NAME
ORDER BY APNT_YMD ASC 

LV4. 주문량이 많은 아이스크림들 조회하기

• 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

• 조건
  0) join : 외래키는 SHIPMENT_ID지만, 생산량 많을 시 같은 이름의 다른 출고번호를 가진다 했으므로 flavor을 key로 조인
  1) 상위 3개 (limit)
  2) sum(a)+sum(b)로 각각 총 주문량을 더함 -> 내림차순

SELECT A.FLAVOR
FROM FIRST_HALF A LEFT JOIN JULY B ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR 
ORDER BY SUM(A.TOTAL_ORDER) +SUM(B.TOTAL_ORDER) DESC 
LIMIT 3

LV.4 그룹별 조건에 맞는 식당 목록 출력하기

• MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

• 조건
  0) JOIN
  1) WHERE 서브쿼리를 사용
  2) LIMIT

SELECT  M.MEMBER_NAME,
        REVIEW_TEXT,
        DATE_FORMAT(REVIEW_DATE,'%Y-%m-%d') REVIEW_DATE
FROM REST_REVIEW R JOIN MEMBER_PROFILE M ON R.MEMBER_ID = M.MEMBER_ID 
WHERE M.MEMBER_NAME = (
      SELECT MEMBER_NAME
      FROM REST_REVIEW R JOIN MEMBER_PROFILE M ON R.MEMBER_ID = M.MEMBER_ID
      GROUP BY MEMBER_NAME 
      ORDER BY COUNT(MEMBER_NAME) DESC
      LIMIT 1)
ORDER BY REVIEW_DATE,REVIEW_TEXT

LV.5 상품을 구매한 회원 비율 구하기

• 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

• 조건
  0) 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수
  -> COUNT DISTINCT로 본 절에서 구함
  1) 2021년에 가입한 전체 회원 수
  -> SELECT 절 서브쿼리로 USER 테이블에서 ID COUNT로 구함
  

SELECT  YEAR(B.SALES_DATE) YEAR, 
        MONTH(B.SALES_DATE) MONTH,
        COUNT(DISTINCT B.USER_ID) PUCHASED_USERS,
        ROUND(COUNT(DISTINCT B.USER_ID) 
            / (SELECT COUNT(USER_ID)
                FROM USER_INFO
                WHERE YEAR(JOINED) = '2021'),1) PUCHASED_RATIO 
FROM USER_INFO A JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
WHERE YEAR(JOINED) = '2021'
GROUP BY YEAR,MONTH 
ORDER BY YEAR,MONTH 

---

프로그래머스에 있는 SQL 문제 끝