SQL

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

-- 코드를 입력하세요
SELECT B.TITLE TITLE, R.BOARD_ID BOARD_ID, R.REPLY_ID REPLY_ID, R.WRITER_ID WRITER_ID, R.CONTENTS CONTENTS, DATE_FORMAT(R.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_REPLY AS R LEFT JOIN  USED_GOODS_BOARD AS B ON R.BOARD_ID = B.BOARD_ID
WHERE B.CREATED_DATE BETWEEN '2022-10-01' AND '2022-10-31'
ORDER BY R.CREATED_DATE ASC, B.TITLE ASC;

 

- SELECT 시 날짜 형태 변경, 기간 필터링, 그리고 출력하는 항목 명확히 보기!!

 

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

-- 코드를 입력하세요
SELECT SUBSTRING(PRODUCT_CODE,1,2) CATEGORY, COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY ASC;

- SUBSTRING 사용해서 잘라내기 / 별칭을 GROUP이랑 ORDER에서 사용 가능(MYSQL기준)

 

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

-- 코드를 입력하세요
SELECT B.CATEGORY CATEGORY, SUM(SALES) TOTAL_SALES
FROM BOOK B INNER JOIN BOOK_SALES S ON B.BOOK_ID = S.BOOK_ID
WHERE S.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY B.CATEGORY
ORDER BY CATEGORY ASC;

 

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

-- 코드를 입력하세요
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE,'%Y-%m-%d') OUT_DATE, 
    CASE
        WHEN OUT_DATE IS NULL THEN '출고미정'
        WHEN OUT_DATE >'2022-05-01' THEN '출고대기'
        ELSE '출고완료'
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC;

- DATE_FORMAT을 사용해 시간 잘라내기

- CASE WHEN~THEN END 사용해서 여러 조건분기 적용

 

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

-- 코드를 입력하세요
SELECT FLAVOR
FROM 
    (SELECT F.FLAVOR FLAVOR, SUM(F.TOTAL_ORDER)+SUM(J.TOTAL_ORDER) ORDERS
     FROM FIRST_HALF F, JULY J
     WHERE F.FLAVOR=J.FLAVOR
     GROUP BY F.FLAVOR
     ORDER BY ORDERS DESC
    ) AS ICECREAM
LIMIT 3;

- 서브쿼리로 데이터 만들어두고 여기서 limit으로 자르기

- 그냥+로 값 더해버리기

- 어느 부분에서 두 테이블이 연결된건지 잘 보기!!

 

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

-- 코드를 입력하세요
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1),1) AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION>=7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

-  DATEDIFF를 사용한 날짜 차이

- HAVING으로 GROUP BY 수행한 결과 필터링