Lv.1

특정 옵션이 포함된 자동차 리스트

SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC

FROM - CAR_RENTAL_COMPANY_CAR 테이블에서
WHERE - '네비게이션' 옵션이 포함된
SELECT - 자동차 리스트를 출력
ORDER BY - 결과는 자동차 ID를 기준으로 내림차순 정렬

자동차 대여 기록에서 장기/단기 대여 구분하기

SELECT 
    HISTORY_ID, 
    CAR_ID, 
    DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, 
    DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, 
    CASE
        WHEN (DATEDIFF(END_DATE, START_DATE) >= 29 ) THEN '장기 대여'
    ELSE '단기 대여'
    END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC

FROM - CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서
WHERE - 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서
SELECT - 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력
ORDER BY - 결과는 대여 기록 ID를 기준으로 내림차순 정렬

30일 이상이라고 해서 단순하게 >= 30 하면 될거라고 생각했는데 아니었다.
당일대여 당일반납 이면 0 이라고 표시되지만 1일 빌린 것이다.
즉, 30일 이상은 >= 29가 되어야 한다.

DATEDIFF(종료일, 시작일) 함수
https://ponyozzang.tistory.com/697
두 기간 사이의 일수를 계산하는 함수

CASE ~ WHEN 조건문

SELECT
	CASE
    	WHEN 조건문1 THEN 반환값1
        WHEN 조건문2 THEN 반환값2
    ELSE 반환값3
    END
FROM 테이블

IF ~ ELSE 조건문

SELECT
	IF(조건문, 참일 때 반환값, 거짓일 때 반환값)
FROM 테이블

Lv.2

자동차 평균 대여 기간

SELECT 
    CAR_ID,
    ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

FROM - CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서
HAVING - 평균 대여 기간이 7일 이상인 자동차들의
SELECT - 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고,
ORDER BY - 결과는 평균 대여 기간을 기준으로 내림차순 정렬, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬.

조건에 부합하는 중고거래 상태

FROM - USED_GOODS_BOARD 테이블에서
WHERE - 2022년 10월 5일에 등록된 중고거래 게시물의
SELECT - 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회
거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력,
ORDER BY - 결과는 게시글 ID를 기준으로 내림차순 정렬.

SELECT
    BOARD_ID,
    WRITER_ID,
    TITLE,
    PRICE,
    CASE 
        WHEN STATUS = 'SALE' THEN '판매중'
        WHEN STATUS = 'RESERVED' THEN '예약중'
        WHEN STATUS = 'DONE' THEN '거래완료'
    END AS STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID DESC

루시와 엘라 찾기

동물 보호소에 들어온 동물 중
WHERE - 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의
SELECT - 아이디와 이름, 성별 및 중성화 여부를 조회.
ORDER BY - 이때 결과는 아이디 순으로

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

이름에 el이 들어가는 동물 찾기

보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 이 사람이 말하길 할머니가 기르던 개는 이름에 'el'이 들어간다고 합니다. 동물 보호소에 들어온 동물 이름 중,
WHERE - 이름에 "EL"이 들어가는 개의
SELECT - 아이디와 이름을 조회
ORDER BY - 이때 결과는 이름 순으로.
단, 이름의 대소문자는 구분하지 않습니다.

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
WHERE UPPER(NAME) LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

영대소문자 구분없이 검색하는 UPPER(), LOWER() 함수

• UPPER() : 소문자를 대문자로
• LOWER() : 대문자를 소문자로

중성화 여부 파악하기

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다.
SELECT - 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를
ORDER BY - 아이디 순으로 조회
이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시.

SELECT ANIMAL_ID, NAME, 
    CASE
        WHEN (SEX_UPON_INTAKE LIKE 'Spayed%') THEN 'O'
        WHEN (SEX_UPON_INTAKE LIKE 'Neutered%') THEN 'O'
    ELSE 'X' 
    END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

카테고리 별 상품 개수 구하기

FROM - PRODUCT 테이블에서
SELECT - 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력
ORDER BY - 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬.

SELECT 
    LEFT(PRODUCT_CODE, 2) AS CATEGORY, 
    COUNT(*) AS PRODUCTS
FROM PRODUCT 
GROUP BY CATEGORY
ORDER BY CATEGORY

DATETIME에서 DATE로 형 변환

FROM - ANIMAL_INS 테이블에 등록된 모든 레코드에 대해,
SELECT - 각 동물의 아이디와 이름, 들어온 날짜를 조회
ORDER BY - 이때 결과는 아이디 순으로.

SELECT 
	ANIMAL_ID, 
    NAME, 
    DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

Lv.3

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

SELECT CONCAT('/home/grep/src/', 
              f.BOARD_ID,'/',
              f.FILE_ID,
              f.FILE_NAME,
              f.FILE_EXT  
       ) AS FILE_PATH
FROM USED_GOODS_FILE f
INNER JOIN USED_GOODS_BOARD b ON b.BOARD_ID = f.BOARD_ID
WHERE VIEWS = (
    SELECT MAX(VIEWS)
    FROM USED_GOODS_BOARD)
ORDER BY f.FILE_ID DESC

FROM - USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서
WHERE - 조회수가 가장 높은 중고거래 게시물에 대한
SELECT - 첨부파일 경로를 조회.
ORDER BY - 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬.
CONCAT() - 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

앞서 풀었던 가격이 가장 비싼 식품정보 문제에서 MAX() 함수를 활용하는 경우를 알아두어 도움이 됐다.

CONCAT(str1, str2, ...) 함수: 문자열 합치기, 연결하기

조건에 맞는 사용자 정보 조회하기

문제: https://school.programmers.co.kr/learn/courses/30/lessons/164670
FROM - USED_GOODS_USER 테이블과
INNER JOIN - USED_GOODS_BOARD 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의
SELECT - 사용자 ID, 닉네임, 전체주소, 전화번호를 조회
이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요.
OREDER BY - 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

SELECT 
    u.USER_ID, 
    u.NICKNAME, 
    CONCAT_WS(
    	' ', 
        u.CITY, 
        u.STREET_ADDRESS1, 
        u.STREET_ADDRESS2
    ) AS 전체주소,
    CONCAT_WS(
        '-', 
        SUBSTRING(u.TLNO, 1, 3), 
        SUBSTRING(u.TLNO, 4, 4), 
        SUBSTRING(u.TLNO, 8, 4)
    ) AS 전화번호
FROM USED_GOODS_USER u
INNER JOIN (
    SELECT b.WRITER_ID
    FROM USED_GOODS_BOARD b
    GROUP BY b.WRITER_ID
    HAVING COUNT(*) >= 3
) w ON w.WRITER_ID = u.USER_ID
ORDER BY u.USER_ID DESC

처음에 전체주소는
CONCAT(u.CITY, ' ', u.STREET_ADDRESS1, u.STREET_ADDRESS2) 으로 작성했더니 틀렸다고 떴다.
출력결과 상으로는 u.STREET_ADDRESS1, u.STREET_ADDRESS2 이 두 컬럼은 띄어쓰기가 되어 있어서 CONCAT_WS()를 안써도 괜찮은 줄 알았는데 아니었음!

GROUP BY / 동명 동물수 찾기 문제 풀이 때에도 사용했던 HAVING COUNT()로 조건에 맞는 유저ID를 테이블w로 구했고, 이어서 유저 테이블u와 inner join하여 조건에 맞는 유저 정보만 골라낼 수 있었다.

CONCAT_WS(구분자, str1, str2, ...) 함수
구분자를 넣어 문자열을 합치는, 연결하는 함수

SUBSTRING(문자열, 시작위치, 숫자) 함수
문자열을 시작 위치로부터 입력된 숫자만큼 잘라내는 함수
시작위치는 1부터 시작한다.

대여 기록이 존재하는 자동차 리스트

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

SELECT DISTINCT c.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR c
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY rh ON c.CAR_ID = rh.CAR_ID
WHERE c.CAR_TYPE = '세단' AND DATE_FORMAT(rh.START_DATE, '%m') = '10'
ORDER BY c.CAR_ID DESC

조건별로 분류하여 주문상태 출력하기

FROM - FOOD_ORDER 테이블에서
5월 1일을 기준으로
SELECT - 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요.
CASE WHEN - 출고여부는 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력해주시고,
ORDER BY - 결과는 주문 ID를 기준으로 오름차순 정렬해주세요.

SELECT 
	ORDER_ID, 
    PRODUCT_ID, 
    DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE, 
    CASE
        WHEN DATEDIFF(OUT_DATE, '2022-05-01') > 0 THEN '출고대기'
        WHEN NOT OUT_DATE IS NULL THEN '출고완료'
    ELSE '출고미정'
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID

CASE WHEN 과 DATEDIFF() 함수를 활용하여 5월 1일을 기준으로 출고날짜가 하루 이상 뒤면 출고대기, 출고대기가 아니면서 출고날짜가 존재하면 출고완료, 출고날짜가 없으면 출고미정으로 표기하도록 구현했다.

오랜 기간 보호한 동물(2)

입양을 간 동물 중,
ORDER BY, LIMIT - 보호 기간이 가장 길었던 동물 두 마리의
SELECT - 아이디와 이름을 조회
ORDER BY - 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

SELECT i.ANIMAL_ID, i.NAME
FROM ANIMAL_INS i
INNER JOIN ANIMAL_OUTS o ON i.ANIMAL_ID = o.ANIMAL_ID
ORDER BY DATEDIFF(o.DATETIME, i.DATETIME) DESC LIMIT 2

보호한 동물 중에서 입양기록이 있는 동물만 필요하므로 INNER JOIN으로 입양기록이 없는(NULL) 동물은 배제했고
DATEDIFF()를 사용하여 입양날짜와 보호날짜의 차이로 구한 보호기간에 대해서
ORDER BY DESC를 사용하여 보호기간을 내림차순으로 정렬하고
LIMIT 2를 사용하여 상위 2마리로 제한했다.