madjaewon 님의 블로그

문제 : 

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

문제 해결 방식 :

-- 이름에 el 들어감 LIKE '%el%'
-- 이름순 조회 , 대소문자 구문하지 않음

-- '개'를 찾음

사용 함수 : LIKE

결과 : 틀림

틀린 이유 : '개'를 찾는 다는 조건 누락 

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%'
ORDER BY 2 ;

답안 : 

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%'
      AND ANIMAL_TYPE = 'DOG'
ORDER BY 2 ;

문제 : 

USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

문제 해결 방식 : 

-- 코드를 입력하세요
-- 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일
-- ORDER BY 댓글 작성일 ASC / 같다면 게시글 제목 ASC 

사용 함수 : DATE_FORMAT, INNER JOIN, LIKE

결과 : 정답

SELECT UGB.TITLE, UGB.BOARD_ID, UGR.REPLY_ID, UGR.WRITER_ID, UGR.CONTENTS, DATE_FORMAT(UGR.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
FROM USED_GOODS_BOARD AS UGB 
     INNER JOIN USED_GOODS_REPLY UGR ON UGB.BOARD_ID = UGR.BOARD_ID 
WHERE UGB.CREATED_DATE LIKE '2022-10%'
ORDER BY 6 ASC, 1 ASC

다른 풀이 방법 :

SUBSTRING 사용

BETWEEN 사용

SELECT 
     B.TITLE, 
     B.BOARD_ID, 
     R.REPLY_ID, 
     R.WRITER_ID, 
     R.CONTENTS, 
     SUBSTRING(R.CREATED_DATE, 1, 10) AS CREATED_DATE
FROM 
USED_GOODS_BOARD AS B JOIN USED_GOODS_REPLY AS R 
on B.BOARD_ID = R.BOARD_ID
WHERE B.CREATED_DATE BETWEEN '2022-10-01' AND '2022-10-31'
ORDER BY R.CREATED_DATE, B.TITLE

문제 : 

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

문제 해결 방식 :

-- WHERE 에서 대여 시작일 2022년 9월
-- CASE WHEN 으로 대여기간 30일 이상 >= 30 '장기대여' / 아니면 '단기 대여' >> 컬럼 RENT_TYPE 
-- ORDER BY 대여 기록 ID DESC 

사용 함수 : DATE_FORMAT, CASE WHEN, MONTH

결과 : 틀림

SELECT TB.*
FROM(
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) >= 30 THEN '장기대여'
               ELSE '단기대여' END AS RENT_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY) TB
WHERE MONTH(TB.START_DATE) = 09
ORDER BY 1 DESC

틀린 이유 : 빌린 당일날 반납한 것은 1일 대여한 것으로 쳐야 함.. 30을 29로 바꾸니 정답 확인

SELECT TB.*
FROM(
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) TB
WHERE MONTH(TB.START_DATE) = 09
ORDER BY 1 DESC

다른 풀이법 : 

1. WHERE 을  다르게 사용 

WHERE START_DATE LIKE '2022-09%'
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 9

* DATEDIFF 

   두 날짜 간의 차이를 단순히 일(day) 단위로 계산

* TIMESTAMPDIFF 사용

   - YEAR 

   - MONTH 

   - DAY

   - HOUR

   - MINUTE 

   - SECOND

 예제 

SELECT DATEDIFF(MONTH, '2023-01-01', '2024-09-19') AS month_diff ;

   1) 경력 계산 : 직원의 입사일과 현재 날짜 사이의 차이를 계산할 때

SELECT 
 employee_name,
 DATEDIFF(NOW(), hire_date) AS day_worked
 FROM employees;

  2) 이벤트 간격 계산 : 두 이벤트 사이의 시간을 측정할 때

SELECT
  event_name,
  TIMESTAMPDIFF(HOUR, start_time, end_time) AS event_duration_hours
  FROM events ;

  3) 유효 기간 계산 : 유효기간이나 만료일을 계산할 때

SELECT
   product_name, 
   DATEDIFF(expire_date, NOW()) AS days_until_expiry
FROM products

문제 : 

잡은 물고기의 평균 길이를 출력하는 SQL문을 작성해주세요.

평균 길이를 나타내는 컬럼 명은 AVERAGE_LENGTH로 해주세요.
평균 길이는 소수점 3째자리에서 반올림하며, 10cm 이하의 물고기들은 10cm 로 취급하여 평균 길이를 구해주세요.

SELECT ROUND(AVG(IF(LENGTH IS NULL,10,LENGTH)),2)AS AVERAGE_LENGTH
FROM FISH_INFO

결과 : 

다른 풀이 :

-- 일반 풀이
SELECT ROUND(AVG(
                CASE WHEN LENGTH IS NULL THEN 10
                WHEN LENGTH <=10 THEN 10 
                ELSE LENGTH
                END),2) AS AVERAGE_LENGTH 
FROM FISH_INFO

-- 서브쿼리를 활용한 풀이
SELECT ROUND(AVG(A.LENGTH),2) AS AVERAGE_LENGTH
FROM(
SELECT ID, FISH_TYPE,
    CASE WHEN LENGTH IS NULL THEN 10
         WHEN LENGTH <= 10 THEN 10 
         ELSE LENGTH 
         END AS LENGTH
FROM FISH_INFO ) A