하나둘셋넷

SQL String, Date] 오랜 기간 보호한 동물(2)

https://school.programmers.co.kr/learn/courses/30/lessons/59411

-- 코드를 입력하세요
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

SQL String, Date] 취소되지 않은 진료 예약 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/157342

-- 코드를 입력하세요

# "1월 2일" - "1월 1일"인 경우 1일이 아닌 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 AVG(DATEDIFF(END_DATE, START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

SQL String, Date] 취소되지 않은 진료 예약 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/132204

-- 코드를 입력하세요
SELECT A.APNT_NO, P.PT_NAME,  A.PT_NO,  D.MCDP_CD, D.DR_NAME, A.APNT_YMD 
FROM PATIENT P
LEFT JOIN APPOINTMENT A ON P.PT_NO = A.PT_NO
LEFT JOIN DOCTOR D on D.DR_ID = A.MDDR_ID	
WHERE (A.APNT_CNCL_YN = 'N') 
    AND (DATE(A.APNT_YMD) = '2022-04-13')
    AND (D.MCDP_CD = 'CS')
ORDER BY A.APNT_YMD

SQL String, Date] 조건별로 분류하여 주문상태 출력하기

SQL String, Date] 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 LIMIT 1

-- 코드를 입력하세요
SELECT CONCAT('/home/grep/src/',B.BOARD_ID,'/',F.FILE_ID,F.FILE_NAME,F.FILE_EXT) as FILE_PATH
    FROM USED_GOODS_BOARD B
    INNER JOIN(
        SELECT *
        FROM USED_GOODS_FILE
    ) as F on B.BOARD_ID = F.BOARD_ID
    WHERE B.BOARD_ID = (
        SELECT BOARD_ID
        FROM USED_GOODS_BOARD
        ORDER BY VIEWS DESC
        LIMIT 1
    )
    
    ORDER BY F.FILE_ID DESC

SQL String, Date] 조건에 맞는 사용자 정보 조회하기

-- 코드를 입력하세요
SELECT u.USER_ID, 
    u.NICKNAME, 
    CONCAT(u.CITY, ' ',u.STREET_ADDRESS1,' ', u.STREET_ADDRESS2) as 전체주소, 
    CONCAT(SUBSTRING(u.TLNO, 1,3), 
           '-',SUBSTRING(u.TLNO, 4,4), '-', 
           SUBSTRING(u.TLNO, 8) ) as  전화번호
    FROM USED_GOODS_BOARD b
    INNER JOIN USED_GOODS_USER u on b.WRITER_ID = u.USER_ID

    GROUP BY b.WRITER_ID
    HAVING COUNT(b.WRITER_ID) >= 3
    ORDER BY u.USER_ID DESC;

SQL String, Date] 재구매가 일어난 상품과 회원 리스트 구하기

-- 코드를 입력하세요
SELECT c.CAR_ID
    FROM CAR_RENTAL_COMPANY_CAR c
    INNER JOIN(
        SELECT CAR_ID, START_DATE
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    ) as h on c.CAR_ID = h.CAR_ID
    WHERE MONTH(h.START_DATE) = 10 AND  c.CAR_TYPE = '세단'
    GROUP BY CAR_ID
    ORDER BY CAR_ID DESC;

1] CAR_RENTAL_COMPANY_CAR와 CAR_RENTAL_COMPANY_RENTAL_HISTORY 를 CAR_ID 기준으로 INNER JOIN

2] MONTH(h.START_DATE) 을 통해 10월에 대여를 시작한 차의 정보를 얻는다.

3] GROUP BY CAR_ID를 사용하면 문제 조건에 따라 중복없이 값을 출력할 수 있다.