[프로그래머스] SQL 고득점 Kit - SELECT (Oracle)
1. 3월에 태어난 여성 회원 목록 출력하기
문제
MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.
▼ MEMBER_PROFILE 테이블 구조
Column name Type Nullable
MEMBER_ID VARCHAR(100) FALSE
MEMBER_NAME VARCHAR(50) FALSE
TLNO VARCHAR(50) TRUE
GENDER VARCHAR(1) TRUE
DATE_OF_BIRTH DATE TRUE
풀이
- 조건 : 생일이 3월인 대상, 여성 회원, 전화번호가 NULL인 경우 제외, 회원 ID 기준 오름차순(ASC)
SELECT MEMBER_ID
, MEMBER_NAME
, GENDER
, TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE 1=1
AND TO_CHAR(DATE_OF_BIRTH, 'MM') = '03' -- 생일이 3월
AND TLNO IS NOT NULL -- 연락처가 Null 이 아닌 대상
AND GENDER = 'W' -- 성별이 여성(W) 인 대상
ORDER BY MEMBER_ID ASC; -- MEMBER_ID 기준 오름차순
2. 과일로 만든 아이스크림 고르기
문제
상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.
▼ FIRST_HALF 테이블 구조
NAME TYPE NULLABLE
SHIPMENT_ID INT(N) FALSE
FLAVOR VARCHAR(N) FALSE
TOTAL_ORDER INT(N) FALSE
▼ ICECREAM_INFO 테이블 구조
NAME TYPE NULLABLE
FLAVOR VARCHAR(N) FALSE
INGREDIENT_TYPE VARCHAR(N) FALSE
풀이
- 조건 : 상반기 총주문량 3,000 보다 높은 경우, 아이스크림 주 성분이 과일, 총주문량이 큰 순서대로 정렬(DESC)
SELECT A.FLAVOR
FROM FIRST_HALF A
INNER JOIN ICECREAM_INFO B
ON B.FLAVOR = A.FLAVOR
WHERE 1=1
AND A.TOTAL_ORDER > 3000 -- 상반기 총주문량이 3,000 보다 높은 경우
AND B.INGREDIENT_TYPE = 'fruit_based' -- 주 성분이 과일
ORDER BY A.TOTAL_ORDER DESC; -- 총주문량이 큰 순서대로 정렬(DESC)
3. 12세 이하인 여자 환자 목록 출력하기
문제
PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요.
▼ PATIENT 테이블 구조
Column name Type Nullable
PT_NO VARCHAR(10) FALSE
PT_NAME VARCHAR(20) FALSE
GEND_CD VARCHAR(1) FALSE
AGE INTEGER FALSE
TLNO VARCHAR(50) TRUE
풀이
- 조건 : 12세 이하 여성환자, 전화번호 없는 경우 NONE 출력, 나이 기준 내림차순(DESC), 나이가 동일하면 환자이름 기준 오름차순 (ASC)
SELECT PT_NAME
, PT_NO
, GEND_CD
, AGE
, NVL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE 1=1
AND GEND_CD = 'W' -- 성별이 여성
AND AGE <= 12 -- 나이가 12세 이하
ORDER BY AGE DESC, PT_NAME ASC; -- 나이 기준 내림차순(DESC), 나이 동일하면 이름 기준 오름차순(ASC)
4. 흉부외과 또는 일반외과 의사 목록 출력하기
문제
DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성해주세요. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬해주세요.
▼ DOCTOR 테이블 구조
Column name Type Nullable
DR_NAME VARCHAR(20) FALSE
DR_ID VARCHAR(10) FALSE
LCNS_NO VARCHAR(30) FALSE
HIRE_YMD DATE FALSE
MCDP_CD VARCHAR(6) TRUE
TLNO VARCHAR(50) TRUE
풀이
- 조건 : 진료과가 CS or GS, 고용일자 기준 내림차순 (DESC), 고용일자 동일하면 이름 기준 오름차순 (ASC)
SELECT DR_NAME
, DR_ID
, MCDP_CD
, TO_CHAR(HIRE_YMD, 'YYYY-MM-DD') AS HIRE_YMD
FROM DOCTOR
WHERE 1=1
AND MCDP_CD IN ('CS', 'GS') -- 진료과가 CS, GS
ORDER BY HIRE_YMD DESC, DR_NAME ASC; -- 고용일자 기준 내림차순 (DESC), 고용일자 동일하면 이름 기준 오름차순 (ASC)
5. 평균 일일 대여 요금 구하기
문제
CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.
▼ CAR_RENTAL_COMPANY_CAR 테이블 구조
Column name Type Nullable
CAR_ID INTEGER FALSE
CAR_TYPE VARCHAR(255) FALSE
DAILY_FEE INTEGER FALSE
OPTIONS VARCHAR(255) FALSE
풀이
- 조건 : 자동차 종류가 SUV, 평균(AVG) 일일 대여 요금은 소수 첫 번째 자리에서 반올림(ROUND) 하고 컬럼명 지정
SELECT ROUND(AVG(DAILY_FEE)) AS AVERAGE_FEE -- AVG 로 평균 구하고, ROUND 로 반올림, AS 로 명명
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'; -- 자동차종류가 SUV
6. 인기있는 아이스크림
문제
상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성해주세요.
▼ FIRST_HALF 테이블 구조
NAME TYPE NULLABLE
SHIPMENT_ID INT(N) FALSE
FLAVOR VARCHAR(N) FALSE
TOTAL_ORDER INT(N) FALSE
풀이
- 조건 : 총주문량 기준 내림차순 (DESC) 정렬, 총주문량이 동일하다면 출하 번호 기준 오름차순 (ASC)
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC; -- 총주문량 기준 내림차순 (DESC) 정렬, 총주문량이 동일하다면 출하 번호 기준 오름차순 (ASC)
7. 서울에 위치한 식당 목록 출력하기
문제
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.
▼ REST_INFO 테이블 구조
Column name Type Nullable
REST_ID VARCHAR(5) FALSE
REST_NAME VARCHAR(50) FALSE
FOOD_TYPE VARCHAR(20) TRUE
VIEWS NUMBER TRUE
FAVORITES NUMBER TRUE
PARKING_LOT VARCHAR(1) TRUE
ADDRESS VARCHAR(100) TRUE
TEL VARCHAR(100) TRUE
▼ REST_REVIEW 테이블 구조
Column name Type Nullable
REVIEW_ID VARCHAR(10) FALSE
REST_ID VARCHAR(10) TRUE
MEMBER_ID VARCHAR(100) TRUE
REVIEW_SCORE NUMBER TRUE
REVIEW_TEXT VARCHAR(1000) TRUE
REVIEW_DATE DATE TRUE
풀이
- 조건 : 서울에 위치한 식당, 리뷰 평균점수는 소수점 세 번쨰 자리에서 반올림 (ROUND), 결과는 평균점수 기준으로 내림차순 (DESC), 평균점수가 동일하다면 즐겨찾기수 기준 내림차순(DESC)
SELECT A.REST_ID
, A.REST_NAME
, A.FOOD_TYPE
, A.FAVORITES
, A.ADDRESS
, ROUND(AVG(B.REVIEW_SCORE), 2) AS SCORE -- 리뷰점수 평균, 반올림, AS 명명
FROM REST_INFO A
INNER JOIN REST_REVIEW B
ON B.REST_ID = A.REST_ID
WHERE 1=1
AND A.ADDRESS LIKE '서울%' -- 주소가 서울로 시작 LIKE 검색
GROUP BY A.REST_ID -- 집계함수 사용위한 GROUP BY
, A.REST_NAME
, A.FOOD_TYPE
, A.FAVORITES
, A.ADDRESS
ORDER BY SCORE DESC, FAVORITES DESC
8. 조건에 부합하는 중고거래 댓글 조회하기
문제
USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.
▼ USED_GOODS_BOARD 테이블 구조
Column name Type Nullable
BOARD_ID VARCHAR(5) FALSE
WRITER_ID VARCHAR(50) FALSE
TITLE VARCHAR(100) FALSE
CONTENTS VARCHAR(1000) FALSE
PRICE NUMBER FALSE
CREATED_DATE DATE FALSE
STATUS VARCHAR(10) FALSE
VIEWS NUMBER FALSE
▼ USED_GOODS_REPLY 테이블 구조
Column name Type Nullable
REPLY_ID VARCHAR(10) FALSE
BOARD_ID VARCHAR(5) FALSE
WRITER_ID VARCHAR(50) FALSE
CONTENTS VARCHAR(1000) TRUE
CREATED_DATE DATE FALSE
풀이
- 조건 : 2022년 10월에 작성된 게시글, 댓글 작성일 기준 오름차순 (ASC), 댓글 작성일 동일하면 게시글 제목 기준 오름차순 (ASC)
SELECT A.TITLE /*게시글 제목*/
, A.BOARD_ID /*게시글 ID*/
, B.REPLY_ID /*댓글 ID*/
, B.WRITER_ID /*댓글 작성자 ID*/
, B.CONTENTS /*댓글 내용*/
, TO_CHAR(B.CREATED_DATE, 'YYYY-MM-DD') AS CREATED_DATE /*댓글 작성일*/
FROM USED_GOODS_BOARD A
INNER JOIN USED_GOODS_REPLY B
ON B.BOARD_ID = A.BOARD_ID
WHERE 1=1
AND TO_CHAR(A.CREATED_DATE, 'YYYY-MM') = '2022-10' -- 2022-10 작성
ORDER BY B.CREATED_DATE, A.TITLE -- 댓글 작성일 기준 오름차순 (ASC), 댓글 작성일 동일하면 게시글 제목 기준 오름차순 (ASC)
9. 조건에 맞는 도서 리스트 출력하기
문제
BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서 도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.
▼ BOOK 테이블 구조
Column name Type Nullable Description
BOOK_ID INTEGER FALSE 도서 ID
CATEGORY VARCHAR(N) FALSE 카테고리 (경제, 인문, 소설, 생활, 기술)
AUTHOR_ID INTEGER FALSE 저자 ID
PRICE INTEGER FALSE 판매가 (원)
PUBLISHED_DATE DATE FALSE 출판일
풀이
- 조건 : 2021년 출판, 인문 카테고리, 출판일 기준 오름차순 (ASC)
SELECT BOOK_ID
, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE 1=1
AND TO_CHAR(PUBLISHED_DATE, 'YYYY') = '2021' -- 출판일 2021
AND CATEGORY = '인문' -- 카테고리 인문
ORDER BY PUBLISHED_DATE ASC -- 출판일 기준 오름차순 (ASC)