프로그래머스 SQL SELECT

문제 및 답변

Oracle

SELECT MEMBER_ID
     , MEMBER_NAME
     , GENDER
     , TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE EXTRACT(MONTH FROM DATE_OF_BIRTH) = 3 
AND GENDER = 'W' AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;

Mysql

SELECT MEMBER_ID
     , MEMBER_NAME
     , GENDER
     , DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d") as DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE month(DATE_OF_BIRTH) = 3 
and GENDER = 'W' 
and TLNO <> "NULL"
order by MEMBER_ID;

(Level 1) 인기있는 아이스크림

Oracle

SELECT flavor
  FROM FIRST_HALF
  ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

Mysql

SELECT flavor
  FROM FIRST_HALF
  ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

(Level 1) 조건에 부합하는 중고거래 댓글 조회하기

Oracle

SELECT board.TITLE
     , board.BOARD_ID
     , reply.REPLY_ID
     , reply.WRITER_ID
     , reply.CONTENTS
     , TO_CHAR(reply.CREATED_DATE, 'YYYY-MM-DD') AS CREATED_DATE
FROM USED_GOODS_BOARD board, USED_GOODS_REPLY reply
WHERE board.BOARD_ID = reply.BOARD_ID
AND TO_CHAR(board.CREATED_DATE, 'YYYY-MM') = '2022-10'
ORDER BY reply.CREATED_DATE, board.TITLE;

Mysql

SELECT board.TITLE
     , board.BOARD_ID
     , reply.REPLY_ID
     , reply.WRITER_ID
     , reply.CONTENTS
     , date_format(reply.CREATED_DATE,"%Y-%m-%d") CREATED_DATE
  from USED_GOODS_BOARD board
  JOIN USED_GOODS_REPLY reply 
    ON board.BOARD_ID = reply.BOARD_ID
 where date_format(board.CREATED_DATE,"%Y%m")=202210 
order by reply.CREATED_DATE ,board.TITLE

(Level 1) 강원도에 위치한 생산공장 목록 출력하기

Oracle & Mysql

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

(Level 4) 서울에 위치한 식당 목록 출력하기

Oracle

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

Mysql

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

(Level 1) 흉부외과 또는 일반외과 의사 목록 출력하기

Oracle

SELECT DR_NAME
     , DR_ID
     , MCDP_CD
     , TO_CHAR(HIRE_YMD, 'YYYY-MM-DD')
FROM DOCTOR
WHERE MCDP_CD= 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

Mysql

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 ASC;

(Level 1) 조건에 맞는 도서 리스트 출력하기

Oracle

SELECT BOOK_ID
     , TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문'
AND TO_CHAR(PUBLISHED_DATE, 'YYYY') = '2021'
ORDER BY PUBLISHED_DATE;

Mysql

SELECT BOOK_ID
     , DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문'
AND YEAR(PUBLISHED_DATE) = '2021'
ORDER BY PUBLISHED_DATE;

(Level 1) 평균 일일 대여 요금 구하기

Oracle & Mysql

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

(Level 1) 과일로 만든 아이스크림 고르기

Oracle & Mysql

SELECT first_half.FLAVOR
from FIRST_HALF first_half, ICECREAM_INFO icecream_info
where first_half.FLAVOR = icecream_info.FLAVOR
and first_half.TOTAL_ORDER > 3000
and icecream_info.INGREDIENT_TYPE = 'fruit_based'
order by first_half.TOTAL_ORDER desc;

(Level 1) 12세 이하인 여자 환자 목록 출력하기

Oracle

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

Mysql

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;

-- Case Version

SELECT PT_NAME
     , PT_NO
     , GEND_CD
     , AGE
     ,
  CASE
    WHEN TLNO IS NULL THEN 'NONE'
    ELSE TLNO
  END TLNO
FROM PATIENT
WHERE AGE <= 12 
AND GEND_CD = 'w' 
ORDER BY AGE DESC, PT_NAME ASC;

(Level 1) 모든 레코드 조회하기

Oracle & Mysql

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

(Level 2) 재구매가 일어난 상품과 회원 리스트 구하기

Oracle

SELECT USER_ID
     , PRODUCT_ID
  FROM 
  (
    SELECT USER_ID
         , PRODUCT_ID
         , COUNT(*) AS REPEAT_COUNT
    FROM ONLINE_SALE
    GROUP BY USER_ID, PRODUCT_ID
    HAVING COUNT(*) > 1
  ) REPEAT_SALES
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

Mysql

SELECT USER_ID
     , PRODUCT_ID
  FROM 
  (
    SELECT USER_ID
         , PRODUCT_ID
         , COUNT(*) AS REPEAT_COUNT
    FROM ONLINE_SALE
    GROUP BY USER_ID, PRODUCT_ID
    HAVING COUNT(*) > 1
  ) 
AS REPEAT_SALES
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

(Level 4) 오프라인/온라인 판매 데이터 통합하기

서브쿼리
- 2022년 3월인 데이터를 선택합니다.
- TO_CHAR(SALES_DATE, 'YYYY-MM-DD')를 사용하여 판매 날짜를 'YYYY-MM-DD' 형식으로 변환합니다.
- 선택된 컬럼은 SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT입니다.
- 두번째 서브쿼리에선 USER_ID 값을 NULL로 하여 오프라인 판매 데이터의 사용자 ID를 표시하지 않습니다.
메인 쿼리
- 서브쿼리에서 선택된 결과를 모두 합칩니다.
- 이 때, UNION을 사용하여 중복을 제거하지 않고 모든 결과를 포함합니다.
- ORDER BY 절을 사용하여 SALES_DATE, PRODUCT_ID, USER_ID를 기준으로 오름차순으로 정렬합니다.
- 정렬된 결과를 반환하는 컬럼은 SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT입니다.

Oracle

SELECT SALES_DATE
     , PRODUCT_ID
     , USER_ID
     , SALES_AMOUNT
  FROM 
  (   
    SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') 
           SALES_DATE
         , PRODUCT_ID
         , USER_ID
         , SALES_AMOUNT
    FROM ONLINE_SALE
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
    UNION 
    SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') 
          SALES_DATE
        , PRODUCT_ID
        , NULL
        , SALES_AMOUNT
    FROM OFFLINE_SALE
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
  )
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

Mysql

SELECT SALES_DATE
     , PRODUCT_ID
     , USER_ID
     , SALES_AMOUNT
FROM 
(   
  SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d')
        AS SALES_DATE
         , PRODUCT_ID
         , USER_ID
         , SALES_AMOUNT
  FROM ONLINE_SALE
  WHERE DATE_FORMAT(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 DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
) integration
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

(Level 1) 역순 정렬하기

Oracle & Mysql

SELECT NAME
     , DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

(Level 1) 아픈 동물 찾기

Oracle & Mysql

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

(Level 1) 어린 동물 찾기

Oracle & Mysql

SELECT ANIMAL_ID
     , NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION NOT LIKE 'Aged'
ORDER BY ANIMAL_ID

(Level 1) 동물의 아이디와 이름

Oracle & Mysql

SELECT ANIMAL_ID
     , NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

(Level 1) 여러 기준으로 정렬하기

Oracle & Mysql

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

(Level 1) 상위 n개 레코드

Oracle

SELECT NAME
FROM 
    (
        SELECT NAME
        FROM ANIMAL_INS 
        ORDER BY DATETIME
    ) 
WHERE ROWNUM = 1;

-- FETCH FIRST
SELECT NAME
FROM ANIMAL_INS 
ORDER BY DATETIME ASC
FETCH FIRST 1 ROWS ONLY;

Mysql

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

(Level 1) 조건에 맞는 회원수 구하기

Oracle

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE AGE BETWEEN 20 AND 29
AND EXTRACT(YEAR FROM JOINED) = '2021'

Mysql

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE 20 <= AGE
AND AGE <= 29
AND YEAR(JOINED) = '2021';

References By

프로그래머스

코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.

programmers.co.kr

https://chat.openai.com/

https://dotoritori-96.tistory.com/m/140

저작자표시

'DataBase > SQL 연습문제' 카테고리의 다른 글

프로그래머스 SQL JOIN (0)	2023.05.30

티스토리툴바

프로그래머스 SQL SELECT

문제 및 답변

References By

'DataBase > SQL 연습문제' 카테고리의 다른 글

티스토리툴바