[프로그래머스] 2주차 MySQL 스터디 1

1. SELECT - [흉부외과 또는 일반외과 의사 목록 출력하기] _ LEVEL 1

DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성해주세요. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬해주세요. 날짜 포맷은 예시와 동일하게 나와야합니다.

 

[ 정답 쿼리 ]

SELECT DR_NAME, DR_ID, MCDP_CD
    , DATE_FORMAT(HIRE_YMD,"%Y-%m-%d") HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ("CS","GS") -- 진료과 조건은 CS 또는 GS이므로 IN 키워드 사용해서 표현
ORDER BY HIRE_YMD DESC, DR_NAME;

 

[ 풀이 ]

• WHERE MCDP_CD = "CS" OR MCDP_CD = "GS" 를 MCDP_CD IN ("CS","GS") 로 표현 가능
• 날짜, 문자열의 포맷은 놓치기 쉬우므로 늘 마지막에 확인하기!

---

2. SUM, MAX, MIN - [가격이 제일 비싼 식품의 정보 출력하기] _ LEVEL 2

FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

 

[ 정답 쿼리 ]

-- WHERE절 중첩 서브쿼리 이용
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

 

[ 풀이 ]

• 문제에 가격이 가장 높은 식품은 하나만 존재합니다. 와 같은 조건이 없었기 때문에, ORDER BY PRICE DESC LIMIT 1 처럼 가격으로 내림차순 정렬 후 상단 1개의 ROW만 출력하는 조건은 위험함
• 따라서 조금 돌아가더라도 정석적인 방법으로 WHERE절의 중첩 서브쿼리를 이용하는 것이 안전
• 서브쿼리 안에서 MAX() 함수를 이용해 해당 테이블의 최고가를 뽑아내고, 해당 가격을 외부 쿼리에서 조건으로 받아서 원하는 결과를 출력.

---

3. GROUP BY - [저자 별 카테고리 별 매출액 집계하기] _ LEVEL 4

다음은 어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK), 저자 정보(AUTHOR), 판매량 정보(BOOK_SALES) 테이블입니다.

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요.

결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT MIN(B.AUTHOR_ID) AUTHOR_ID
	, AUTHOR_NAME
	, CATEGORY
	, SUM(SALES * PRICE) TOTAL_SALES
FROM BOOK B
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID
WHERE DATE_FORMAT(SALES_DATE, "%Y-%m")='2022-01' -- 2022년 1월 판매 데이터에 대해서 필터링
GROUP BY AUTHOR_NAME, CATEGORY -- 저자 별, 카테고리 별 매출액을 구하기 위해 GROUP BY
ORDER BY AUTHOR_ID, CATEGORY DESC;

 

[ 풀이 ]

포인트 1) 2022년 1월의 판매 데이터만을 필터링하는 방법

• WHERE 절에서 SALES_DATE에 DATE_FORMAT() 함수를 적용해서 2022년 01월의 데이터만 걸러낼 수 있다!

포인트 2) 저자 별, 카테고리 별 매출액을 계산하기

• 저자 별, 카테고리 별 매출액(TOTAL_SALES)를 집계하기 위해서 먼저 GROUP BY절에서 집계 기준들을 순서대로 나열
• SELECT 절에서 SUM(SALES * PRICE) 로 저자 별, 카테고리 별로 책들의 매출액을 집계

---

4. IS NULL - [이름이 없는 동물의 아이디] _ LEVEL 1

동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

 

[ 정답 쿼리 ]

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL -- 이름이 없는 채로 들어온 동물
ORDER BY ANIMAL_ID;

 

[ 풀이 ]

• WHERE 절에서 이름(NAME)이 없는 동물에 대한 조건을 IS NULL 로 걸어줌
• 해당 예시에서는 출력되는 ANIMAL_ID가 하나 뿐이라서 상관없지만, 문제에서 제시하는 대로 ORDER BY 절도 잊지 않고 명시하기

---

🌟🌟🌟5. JOIN - [특정 기간동안 대여 가능한 자동차들의 대여비용 구하기] _ LEVEL 4

다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 입니다.

세 개의 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

* 주의사항 : FEE 의 경우 예시처럼 정수부분만 출력되어야 합니다.

 

[ 정답 쿼리 ]

SELECT C.CAR_ID
    , C.CAR_TYPE
    , ROUND(DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30, 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE AND DURATION_TYPE = '30일 이상'
WHERE C.CAR_TYPE IN ('세단','SUV') 
		-- 조건a. 자동차 종류가 '세단' 또는 'SUV' 인 자동차'
    AND CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE NOT(DATE_FORMAT(START_DATE,"%Y-%m") > '2022-11' OR DATE_FORMAT(END_DATE,"%Y-%m") < '2022-11'))
    -- 조건b. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능
    -- 즉, 대여 시작일이 2022년 12월 이상이거나, 대여 종료일이 2022년 11월 미만인 경우
    AND (DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 >= 500000 AND DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 < 2000000)
    -- 조건c. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

-- 조건b를 조금 더 직관적 방법으로 구성한 풀이
SELECT C.CAR_ID
    , C.CAR_TYPE
    , ROUND(DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30, 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE AND DURATION_TYPE = '30일 이상'
WHERE C.CAR_TYPE IN ('세단','SUV')
    AND CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE ("2022-11" between DATE_FORMAT(START_DATE,"%Y-%m") and DATE_FORMAT(END_DATE,"%Y-%m")))
    AND (DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 >= 500000 AND DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 < 2000000)
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

 

[ 풀이 ]

• 이 문제의 핵심은 어떤 테이블을 JOIN할 지 & 말로 되어있는 조건을 어떻게 조건으로 변경할 수 있는 지에 대한 고민

---

1. JOIN에 사용되는 테이블

• 출력에 필요한 COLUMN들은 CAR_RENTAL_COMPANY_CAR , CAR_RENTAL_COMPANY_DISCOUNT_PLAN 에 존재하고 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 출력에 필요한 COLUMN을 포함하고 있지 않다는 것을 일단 먼저 캐치해야 함.
• 따라서 CAR_RENTAL_COMPANY_CAR , CAR_RENTAL_COMPANY_DISCOUNT_PLAN 두 테이블만 JOIN하고, CAR_RENTAL_COMPANY_RENTAL_HISTORY 은 WHERE절에서 서브쿼리를 이용하여 조건 구성
• 단, JOIN 시 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 에서 DURATION_TYPE = '30일 이상' 인 ROW들에 대해서만 정보가 필요하므로, ON 절에서 해당 조건을 추가!

FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
ON C.CAR_TYPE = P.CAR_TYPE AND DURATION_TYPE = '30일 이상'

---

2. 조건은 크게 3가지로 볼 수 있음(아래의 세 조건은 AND로 연결되는 조건들)

    a. 자동차 종류가 '세단' 또는 'SUV' 인 자동차

C.CAR_TYPE IN ('세단','SUV')

    b. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 (실수 확률↑)

-- 계속해서 틀렸었던 내 코드
CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
		WHERE DATE_FORMAT(START_DATE,"%Y-%m") > '2022-11' 
				OR DATE_FORMAT(END_DATE,"%Y-%m") < '2022-11')

-- 수정된 풀이 1. 
CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
		WHERE NOT(DATE_FORMAT(START_DATE,"%Y-%m") > '2022-11' 
				OR DATE_FORMAT(END_DATE,"%Y-%m") < '2022-11'))

-- 수정된 풀이 2. START_DATE와 END_DATE 사이에 
CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
		WHERE ("2022-11" BETWEEN DATE_FORMAT(START_DATE,"%Y-%m") AND DATE_FORMAT(END_DATE,"%Y-%m")))

• 위의 조건 b를 잘못 작성해서 이 문제를 자꾸 틀렸었는데, 뭐가 잘못됐는 지를 찾아내기가 쉽지 않았음 → 결론은 조건의 집계 레벨을 착각해서 생긴 문제였음
• 해당 조건의 경우, CAR_ID별로 단 하나의 ROW라도 11월 대여 기록이 존재한다면, 해당 CAR_ID는 제외하는 조건으로 구성되어야 함
• 내가 처음에 짰던 쿼리는 11월 대여 기록만 제외한 테이블에서, CAR_ID를 뽑아내려고 했었음(이 문제풀이 가장 아래에 예시 있음)
• 위의 두 조건의 차이는 제외 기준이 CAR_ID인지 ROW인지 (즉 집계 레벨의 차이)

• 풀이1, 2의 차이 : 풀이 1은 NOT IN ( WHERE NOT ~) 의 형태로 제외 조건을 만들기 위해 이중부정을 사용했고, 풀이 2는 BETWEEN AND 키워드 사용으로 조금 더 직관적인 조건으로 구성
• 결론적으로는 한번이라도 ~한다면 조건은 제외 조건으로 구술해야 오류를 방지할 수 있음!

    c. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차

(DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 >= 500000 
		AND DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 < 2000000)

• 해당 조건의 경우, 이미 FROM~ JOIN 절에서 DURATION_TYPE = '30일 이상' 인 ROW들만 추렸기 때문에, 30일이라는 대여기간에 맞는 할인율로 계산됨

---

3. 대여금액 계산(FEE)

• 마지막으로 문제에서 요구하는 대여 금액(컬럼명: FEE) 은 다음과 같이 구성.
• 단 주의사항에 정수 부분만 출력하라고 되어 있는데, 이 조건이 달린 이유는 DISCOUNT_RATE를 100으로 나누면서 자동으로 FLOAT로 형변환(type casting)이 되었기 때문!→ 해당 문제는 계산된 FEE가 모두 정수로 떨어지게 구성해 놓았기 때문에 ROUND(), 또는 TRUNCATE() 함수로 해결할 수 있음

ROUND(DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30, 0) AS FEE

---

* 참고 )  2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 조건의 단적인 예시

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 CAR_ID = 18번의 대여기록

• CAR_ID = 18번의 렌탈 기록을 보면, 해당 차량은 7개의 렌탈 기록이 남아 있는데, 하나의 기록이 2022년 11월을 포함하고 있기 때문에 사실상 18번 차량은 조건에서 제외되어야 함
• 하지만, 처음 내가 짰던 쿼리대로라면 HISTORY_ID = 712번의 표시된 ROW 하나만 제외하고 나서 CAR_ID를 추출하기 때문에, 남은 대여기록들에 의해서 CAR_ID = 18번 차량은 조건에 포함되어 버림!!

 

---

🌟🌟🌟6. String, Date - [특정 옵션이 포함된 자동차 리스트 구하기] _ LEVEL 1

CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성해주세요. 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

[ 정답 쿼리 ]

-- 풀이 1. LIKE
SELECT *
FROM car_rental_company_car
WHERE options LIKE '%네비게이션%'
ORDER BY car_id DESC;

-- 풀이 2. INSTR
SELECT *
FROM car_rental_company_car
WHERE INSTR(options, '네비게이션') > 0 -- INSTR() 특성상 >0 이 없어도 무방
ORDER BY car_id DESC;

-- 풀이 3. 정규식
SELECT *
FROM car_rental_company_car
WHERE options REGEXP '네비게이션'
ORDER BY car_id DESC;

 

[ 풀이 ]

1. LIKE : 특정 문자열이 포함된 데이터를 선택할 때 ( 컬럼명 LIKE 검색할 문자열 ) . 검색할 문자열 내에 와일드카드 사용가능
   • % : 0개 이상의 문자
   • _ : 1개의 문자
   • 사용예제
     • %e : e로 끝나는 문자열 (e, ee, eevee,pineapple)
     • e% : e로 시작하는 문자열 ( e, ee, eevee, eric)
     • %e% : e를 포함하는 문자열 ( e, ee, eevee, pineapple, eric, aespa)

 

  2. INSTR(str, substr) : 특정 문자열에서 찾고자 하는 문자열이 있는지 확인한다. 검색하고자 하는 문자열이 찾는 문자열이 없을 경우 0을 리턴, 1개 이상 있을경우 첫번째 문자열의 시작 자리 수를 리턴한다.

 

 

  3. REGEXP : LIKE 검색과는 달리 정규식을 이용한 검색 방식.

• LIKE 검색보다 디테일하고 다양한 검색을 할 수 있다는 장점 존재.
• 파이썬, Java 등의 언어에도 정규식 문법이 존재한다!(regex)
• 정규식은 필요할 때마다 찾아서 작성할 수 있을 정도면 충분!

. : 문자 하나를 나타낸다.
* : 앞에 나온 문자의 0개 이상 반복을 나타낸다.
^ : 문자열의 처음을 나타낸다.
$ : 문자열의 끝을 나타낸다.
[.] : 괄호 안의 문자열 일치를 확인한다.
{.} : 반복을 나타낸다.
| : or 를 나타낸다.

‘가’를 포함한 문자열을 찾는 정규식 /  첫 글자가 E(e)나 F(f)가 아닌 문자열을 찾는 정규식

글자 수가 2 이상이면서, 모음으로 시작하고 모음으로 끝나는 문자열  /  글자 수가 1 이상이면서, 모음으로 시작하고 모음으로 끝나는 문자열

이름이 모음으로 시작하거나 모음으로 끝나는 문자열을 찾는 정규식