madjaewon 님의 블로그

문제 : 

'경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.

해결 방식 : 

-- LEFT JOIN 
-- 경제 카테고리에 속하는 도서 >> CATEGORY = '경제'
-- 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE)
-- 결과는 출판일 기준으로 오름차순 정렬 >> PUBLISHED_DATE ASC 

결과 : 정답

답안 : 

SELECT B.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d')
FROM BOOK B LEFT JOIN AUTHOR A ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE ASC

다른 풀이법 : 

DATE 함수 사용법

문제 :

해결 방식 : 

-- 통풍시트, 열선시트, 가죽시트 중 하나 이상 옵션 > IN >> LIKE 변
-- 자통차 종류 별로 몇 대 >> GROUP BY, COUNT 
-- 자동차 수에 대한 컬럼 CARS 
-- 종류를 기준으로 오름차순 ASC 

결과 :  틀림

답안 : 

SELECT CRCC.CAR_TYPE, COUNT(CRCC.CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR AS CRCC
WHERE CRCC.OPTIONS LIKE ('%통풍시트%') OR ('%열선시트%') OR ('%가죽시트%')
GROUP BY 1
ORDER BY 1 ASC

결과 : 

해설 :

(like [option]) or ([option]) or ([option])
(like [option]) or (like [option]) or (like [option])

like 함수 적용 범위

다른 풀이법 : 

LIKE 활용

SELECT CRCC.CAR_TYPE, COUNT(CRCC.CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR AS CRCC
WHERE CRCC.OPTIONS LIKE ('%통풍시트%') OR 
CRCC.OPTIONS LIKE ('%열선시트%') OR 
CRCC.OPTIONS LIKE ('%가죽시트%')
GROUP BY 1
ORDER BY 1 ASC

INSTR 활용 

SELECT CAR_TYPE, COUNT(*) CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE 
INSTR(OPTIONS, '통풍시트') > 0 OR
INSTR(OPTIONS, '열선시트') > 0 OR
INSTR(OPTIONS, '가죽시트') > 0 
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC

REGEXP 활용 

SELECT CAR_TYPE, COUNT(*) CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE 
OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC

문제 : 

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다

해결 방식 : 

결과 : 정답

답안 : 

SELECT hour(datetime), count(animal_id)
from ANIMAL_OUTS
where hour(datetime) between 9 and 19
group by 1
order by 1

다른 풀이법 : 

문제 : 

SUBWAY_DISTANCE 테이블에서 노선별로 노선, 총 누계 거리, 평균 역 사이 거리를 노선별로 조회하는 SQL문을 작성해주세요.

총 누계거리는 테이블 내 존재하는 역들의 역 사이 거리의 총 합을 뜻합니다. 총 누계 거리와 평균 역 사이 거리의 컬럼명은 각각 TOTAL_DISTANCE, AVERAGE_DISTANCE로 해주시고, 총 누계거리는 소수 둘째자리에서, 평균 역 사이 거리는 소수 셋째 자리에서 반올림 한 뒤 단위(km)를 함께 출력해주세요.
결과는 총 누계 거리를 기준으로 내림차순 정렬해주세요.

해결 방식 : 

-- 코드를 작성해주세요
-- select route, sum(d_cumulative), avg(d.between_dist)
-- 누계거리 소수 둘째 / 평균 역사 소수 셋째 >> round 함수
-- km 함께 출력 >> concat
-- 누계거리 desc 

결과 : 틀림 

         concat 함수를 사용해서 텍스트(Km) 넣어야 함 

답안 : 

select route, concat(round(sum(d_between_dist),3),"km") as TOTAL_DISTANCE, concat(round(avg(d_between_dist), 4),"km") as AVERAGE_DISTANCE
from subway_distance
group by 1
order by 2 desc

주의할 점 : 

1. 정렬 기준 
   • 'TOTAL_DISTANCE'를 기준으로 하면 안된다. 해당 칼럼은 concat() 함수로 인해 더이상 숫자가 아니다. 문자열이다. 따라서 이 컬럼을 기준으로 정렬 시 문자열(사전식) 정렬기준에 따라 정렬되는 버그가 발생한다.
2. 반올림 함수 인자 설정
   • round(숫자, 반올림할 자릿수)
   • 반올림할 자릿수하는 말이 좀 헷갈림의 여지가 있다고 느껴지는데, 예를 들어 '소수 둘째 자리에서 반올림한다'고 가정하면 반올림할 자릿수라는 매개변수에는 1이 들어가야 한다.
   • 즉, 반올림 연산 수행 후에 유효한 자릿수가 매개변수로 사용되는 것이다.

SELECT p.product_code, sum(p.price*os.sales_amount) as sales
from product p
    inner join offline_sale os on p.product_id = os.product_id
group by 1
order by 2 desc, 1 asc

문제 : 

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

해결 방식 : 

-- join product / offline_sale 테이블
-- 상품코드 별 매출액(판매가 * 판매액)
-- 매출액 desc, 상품코드 asc

결과 :  정답

답안 : 

SELECT p.product_code, sum(p.price*os.sales_amount) as sales
from product p
    inner join offline_sale os on p.product_id = os.product_id
group by 1
order by 2 desc, 1 asc

다른 풀이법 : 

1. 서비스, 비즈니스 파악 - 서비스의 목표, 기획서

2. 문제 정의 - 핵심 문제, 목표 정의 - 핵심 이벤트 

3. 사용 주기 파악 - 고객은 문제를 어떤 빈도로 겪을까? 실제와 예상의 차이 파악 - Daily/Weekly/Monthly 결정

4. 리텐션 커브 분석 - PMF 도달 여부 확인 - 평평해지는 시점 확인 ( * PMF = Product Market Fit ) 

5. 추가 분석 - 코호트, 유저 행동 등  

- 핵심 이벤트 정의 필요

문제 : 

DUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

해결 방식 : 

-- PRODUCT 앞자리 2개 >> STRING 함수
-- 상품 개수 >> COUNT(*)

-- GROUP BY 
-- 카테고리 코드 오름차순

결과 : 틀림  

         STRING 함수 사용법 확인 필요

답안 :  

SELECT SUBSTR(PRODUCT_CODE,1,2), COUNT(*)
FROM PRODUCT  
GROUP BY 1
ORDER BY 1 ASC

참고사항 :

문자 함수 

LOWER( arg ) 소문자 반환

UPPER ( arg ) 대문자 반환

INITCAP 첫 글자 대문자, 이 후 글자 소문자 변환

CHR (arg )

TRIM 문자열의 양 끝단에서 공백 또는 지정된 문자열을 제거하고 반환

LTRIM 문자열의 왼쪽 끝에서 공백 또는 지정된 문자열을 제거하고 반환 

RTRIM 문자열의 오른쪽 끝에서 공백 또는 지정된 문자열을 제거하고 반환

SUBSTR 입력된 문자열의 부분 문자열을 추출하여 반환

LENGTH 입력된 문자열의 길이를 반환

REPLALE 입력된 문자열에서 특정 문자열을 찾아 다른 문자열로 대체

문제 : 

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문을 작성해주세요. 자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요.

해결 방식 : 

-- 자동차 id 리스트(NO중복) >> GROUP BY 
-- 자동차 종류가 '세단'인 자동차 >> WHERE = '세단'
-- 10월에 대여 시작한 기록 >> WHERE MONTH = 10
-- CAR_ID 내림차순 

결과 : 정답

답안 : 

SELECT CH.CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH
   LEFT JOIN CAR_RENTAL_COMPANY_CAR AS CC ON CH.CAR_ID = CC.CAR_ID 
WHERE MONTH(CH.START_DATE) = 10
     AND CC.CAR_TYPE = '세단'
GROUP BY 1     
ORDER BY 1 DESC

다른 풀이법 : 

1. 세단 찾기

  -  CAR_TYPE IN ('세단')

  -  SELECT DISTINCT(CC.CAR_ID)

2. 10월 

  - DATE_FORMAT(START_DATE, '%Y-%m-%d') like '2022-10%'

  - START_DATE LIKE '2022-10%'

인사이트 : 

날짜 찾기 문제는 자주 나오는데 상황별로 효율적인 방법을 찾아야 겠다. 

문제 : 

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

해결 방식 : 

select 식품id, 이름, 코드, 분류, 가격

max 

limit

결과 : 정답

답안 : LIMIT, ORDER BY 함수 사용 

SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1

다른 풀이법 :  MAX 사용 

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

질문:  * WHERE PRICE = MAX(PRICE) 는 왜 안되는 걸까?

        >>  MAX를 사용하려면 집계된 데이터가 필요한데,  
              WHERE절은 데이터를 집계하기 전에 실행되고요.
              sql의 쿼리 실행순서에 대해서 찾아보세요