https://school.programmers.co.kr/learn/courses/30/lessons/151136
SELECT ROUND(AVG(DAILY_FEE)) as AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = "SUV"평균 : AVG
반올림 : ROUND
https://school.programmers.co.kr/learn/courses/30/lessons/164673
INNER JOIN 사용 -> 공통되는 것만 가져온다.
SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, DATE_FORMAT(R.CREATED_DATE,'%Y-%m-%d')
FROM USED_GOODS_BOARD B
INNER JOIN USED_GOODS_REPLY R ON B.BOARD_ID = R.BOARD_ID
WHERE YEAR(B.CREATED_DATE) = 2022 AND MONTH(B.CREATED_DATE) = 10
ORDER BY B.CREATED_DATE, B.WRITER_ID;https://school.programmers.co.kr/learn/courses/30/lessons/133024
총주문량을 기준으로 내림차순 정렬, 총주문량이 같은 경우 출하 번호를 기준으로 오름차순 정렬
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;https://school.programmers.co.kr/learn/courses/30/lessons/132203
DATE_FORMAT(HIRED_YMD,"%Y-%m-%d")
ORDERBY 기준 1, 같으면 기준 2
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,"%Y-%m-%d") AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME;https://school.programmers.co.kr/learn/courses/30/lessons/131120#qna
DATE_FORMAT을 잘 지켜야 한다.
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 03 AND GENDER = "W" AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;https://school.programmers.co.kr/learn/courses/30/lessons/132201#qna
출력 순서를 잘 지켜야 한다.
SELECT PT_NAME, PT_NO, GEND_CD, AGE, if(TLNO IS NULL, 'NONE', TLNO) AS TLNO
FROM PATIENT
WHERE AGE <= '12' AND GEND_CD = "W"
ORDER BY AGE DESC, PT_NAME ASC;https://school.programmers.co.kr/learn/courses/30/lessons/131536#qna
물건을 재구매한다..? GROUP BY
GROUP BY로 그룹화 해주고
HAVING COUNT PRODUCT_ID를 기준으로 갯수를 세준다.
SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID,PRODUCT_ID
HAVING COUNT(PRODUCT_ID) > 1
ORDER BY USER_ID, PRODUCT_ID DESC;https://school.programmers.co.kr/learn/courses/30/lessons/131535
COUNT로 갯수 세는 것이 핵심이다.
SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE >= 20 AND AGE <= 29 AND YEAR(JOINED) = '2021';