New 병아리 개발자

[프로그래머스] 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)

# context root cannot be empty 해결방법

1. 설정 변경

• 일반적으로 구글링 시 나오는 내용
• 프로젝트 우 클릭 > Properties > Web Project Settings 에서 Context root 를 변경해준다.

2. 설정 변경

• windows > show view > Navigator
• org.eclipse.wst.common.component 에 property 추가.

<property name="context-root" value="넣을값입력"/>

# 컬렉션 프레임웍

• 컬렉션 (Collection) : 데이터 군을 저장하는 클랙스들을 표준화한 설계. (데이터 그룹)
• 프레임웍 : 표준화된 프로그래밍 방식을 의미.
• JDK 1.2 부터 컬렉션 프레임웍이 등장, 다양한 종류의 컬렉션 클래스가 추가되고 체게화 됨.
• 장점 : 컬렉션 프레임웍은 다수의 데이터를 다루는데 필요한 많은 다양한 클래스들을 제공, 인터페이스와 다형성을 이용한 객체지향적 설계를 통해 사용법을 익히기 쉽고 재사용성이 높은 코드를 작성할 수 있다.

## 핵심 인터페이스

• 컬렉션 프레임웍에서 컬렉션 데이터 그룹을 크게 3가지로 타입이 존재한다고 인식, 3개의 인터페이스를 정의하였고 인터페이스 List 와 Set 의 공통 부분을 다시 뽑아 새로운 인터페이스인 Collection 을 추가로 정의.
• 컬렉션 프레임웍 핵심 인터페이스의 상속 계층도 상 List 와 Set 은 Collection 에 상속되지만, Map 인터페이스의 경우 다른 형태로 컬렉션을 다루기 때문에 같은 상속 계층도에 포함되지 못했다.

## 핵심 인터페이스의 특징 (List / Set / Map)

List

• 순서가 있는 데이터의 집합 (데이터 중복 허용)
• 구현클래스 : ArrayList / LinkedList / Stack  Vector 등 ..

Set

• 순서를 유지하지 않는 데이터의 집합 (데이터 중복 X)
• 구현클래스 : HashSet / TreeSet 등 ..

Map

• 키(Key) 와 값(Value) 의 쌍으로 이루어진 데이터 집합, 순서를 유지하지 않는다. (키 중복 X / 값 중복 허용)
• 구현클래스 : HashMap / TreeMap / Hashtable / Properties 등 ..

## Collection 인터페이스

## List 인터페이스

• 중복 허용
• 저장 순서 유지

## Set 인터페이스

• 중복 허용 X
• 저장 순서 유지 X
• 구현 클래스 : HashSet / TreeSet 등 ..

## Map 인터페이스

• 값만 중복 허용 (키는 중복 X)
• 기존에 저장된 데이터와 중복된 키와 값을 저장 시 기존의 값은 없어지고 마지막에 저장된 값이 남게 됨.
• 구현 클래스 : Hashtable / HashMap / LinkedHashMap / SortedMap / TreeMap 등 ..

## Map.Entry 인터페이스

• Map 인터페이스의 내부 인터페이스 (inner interface)
• Map 에 저장되는 key-value 쌍을 다루기 위해 내부적으로 Entry 인터페이스 정의

# 원인 분석 및 개선활동

## 원인분석 유형

• 기준성데이터(개별코드) 관련
• 타업무 테이블 데이터(오류포함) 단순 참조
• 외부 수신 데이터 오류 수용
• 테스트 데이터
• 이행 데이터 매핑 관련
• 이행 데이터 as-is 자체 오류
• 데이터 값 변경 시 잘못 변경함
• 프로그램 로직 관련
• 자료동기화(대내대민) 관련
• 표준데이터 관련
• DB 모델설계(데이터구조) 관련
• 업무규칙 관련
• 기타 원인 유형
  
  

## 개선계획 유형

• 데이터 값 오류아님
• 데이터 값 오류보완 불 필요
• 데이터 값 오류보완(open 이후 생성 데이터)
• 데이터 값 오류보완(as-is 이행데이터)
• 프로그램 보완
• 자료동기화(대내대민) 로직 보완
• 데이터 표준 개선(유효값/도메인 등)
• 데이터 구조 개선(ERD/물리테이블 등)
• 업무규칙보완(일반/IT)
• 업무규칙전환(IT-일반 전환)
• 업무규칙폐기(일반/IT)
• 기타개선유형
• 개선활동 불 필요

## 개선활동 유형

• 데이터 값 개선완료
• 프로그램 개선완료
• 데이터표준 개선완료
• 데이터구조 개선완료
• 없무규칙 개선완료
• 기타 개선완료
• 개선활동 불필요