🚩오늘의 문제는?

SQL - level 1

1.최댓값 구하기
2.PYTHON 개발자 찾기
3.한 해에 잡은 물고기 수 구하기
4.자동차 대여 기록에서 장기/단기 대여 구분하기
5.잡은 물고기의 평균 구하기
6.조건에 부합하는 중고거래 댓글 조회하기

1.최댓값 구하기

문제 설명

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

문제 풀이

SELECT DATETIME AS 시간
FROM ANIMAL_INS
ORDER BY DATETIME DESC LIMIT 1;

2.PYTHON 개발자 찾기

문제 설명

문제 풀이

SELECT ID,EMAIL,FIRST_NAME,LAST_NAME
FROM DEVELOPER_INFOS
WHERE SKILL_1 = 'PYTHON' OR SKILL_2 = 'PYTHON' OR SKILL_3 = 'PYTHON'
ORDER BY ID ASC;

주의할 점

WHERE 절의 조합은 다양함
AND, OR, NOT, IN이 있으므로 확인 필요
https://inforyou.tistory.com/28

3.한 해에 잡은 물고기 수 구하기

문제 설명

DEVELOPER_INFOS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPER_INFOS 테이블 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_1, SKILL_2, SKILL_3는 각각 ID, 이름, 성, 이메일, 첫 번째 스킬, 두 번째 스킬, 세 번째 스킬을 의미합니다.

DEVELOPER_INFOS 테이블에서 Python 스킬을 가진 개발자의 정보를 조회하려 합니다. Python 스킬을 가진 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.

결과는 ID를 기준으로 오름차순 정렬해 주세요.

문제 풀이

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE YEAR(TIME)='2021';

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

문제 설명

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

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

문제 풀이

#실패

#컬럼추가

ALTER TABLE CAR_RENTAL_COMPANY_RENTAL_HISTORY
ADD COLUMN RENT_TYPE VARCHAR(10);

#업데이트

UPDATE CAR_RENTAL_COMPANY_RENTAL_HISTORY
SET RENT_TYPE = CASE
WHEN DATEDIFF(END_DATE , START_DATE) + 1>=30 THEN '장기 대여'
ELSE '단기 대여'
END;

#SELECT 구문

SELECT HISTORY_ID,CAR_ID,START_DATE,END_DATE,RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE)='2022' AND MONTH(START_DATE)='09'
ORDER BY HISTORY_ID DESC;

#성공(하나로 줄이기)

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

주의할 점

• 조건에 따른 컬럼 새로 추가

5.잡은 물고기의 평균 구하기

문제 설명

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. FISH_INFO 테이블의 구조는 다음과 같으며 ID, FISH_TYPE, LENGTH, TIME은 각각 잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜를 나타냅니다.단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.

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

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

문제 풀이

#오류

SELECT ROUND(AVG(LENGTH),3) AS AVERAGE_LENGTH,
CASE WHEN LENGTH IS NULL THEN 10 
ELSE LENGTH
FROM FISH_INFO

#오류

SELECT ROUND(AVG(
CASE WHEN LENGTH IS NULL THEN 10 
ELSE LENGTH),3) AS AVERAGE_LENGTH
FROM FISH_INFO

#성공 : IFNULL을 써보기

SELECT ROUND(AVG(IFNULL(LENGTH,10)),3) AS AVERAGE_LENGTH
FROM FISH_INFO;

주의할 점

• IFNULL을 써서 간단하게 값을 구해보기

6.조건에 부합하는 중고거래 댓글 조회하기

문제 설명

다음은 중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_REPLY 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

USED_GOODS_REPLY 테이블은 다음과 같으며 REPLY_ID, BOARD_ID, WRITER_ID, CONTENTS, CREATED_DATE는 각각 댓글 ID, 게시글 ID, 작성자 ID, 댓글 내용, 작성일을 의미합니다.

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

문제 풀이

#오류
#이상하게 FULL OUTER JOIN을 하면 구해지지 않는다.

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID,A.WRITER_ID,B.CONTENTS,DATE_FORMAT(A.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A LEFT OUTER JOIN USED_GOODS_REPLY B
ON A.BOARD_ID = B.BOARD_ID AND A.CREATED_DATE AND B.CREATED_DATE AND A.WRITER_ID=B.WRITER_ID
WHERE YEAR(A.CREATED_DATE)='2022' AND MONTH(A.CREATED_DATE)='10'
ORDER BY A.CREATED_DATE ASC, A.TITLE ASC;

#오류
#조건에 대한 날짜를 게시글 기준이 아닌 댓글기준으로 작성함

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID,B.WRITER_ID,B.CONTENTS,DATE_FORMAT(B.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE

FROM USED_GOODS_BOARD A JOIN USED_GOODS_REPLY B

ON A.BOARD_ID = B.BOARD_ID

WHERE YEAR(B.CREATED_DATE)='2022' AND MONTH(B.CREATED_DATE)='10'

ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;

#성공(최종정답)

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID,B.WRITER_ID,B.CONTENTS,DATE_FORMAT(B.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE

FROM USED_GOODS_BOARD A JOIN USED_GOODS_REPLY B

ON A.BOARD_ID = B.BOARD_ID

WHERE YEAR(A.CREATED_DATE)='2022' AND MONTH(A.CREATED_DATE)='10'

ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;

주의할 점

• FULL OUTER JOIN (X) => JOIN 써서 해결
• 2022년 10월에 작성된 게시글 제목
  -> 작성일이 게시글이 올라온 테이블로 진행해야함.
  -> 댓글 테이블이 적힌 작성일을 기준으로 하면 XXX