SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE FROM
CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = "SUV";
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD in ("CS", "GS")
ORDER BY HIRE_YMD DESC, DR_NAME ASC;
SELECT a.FLAVOR
FROM FIRST_HALF AS a
LEFT JOIN ICECREAM_INFO AS b
ON a.FLAVOR = b.FLAVOR
WHERE a.TOTAL_ORDER > 3000 AND b.INGREDIENT_TYPE LIKE 'fruit_based'
ORDER BY a.TOTAL_ORDER DESC;
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE DATE_FORMAT(DATE_OF_BIRTH, '%m') = 03 AND GENDER = 'W' AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;
깔끔한 코드
SELECT MEMBER_ID, MEMBER_NAME, GENDER,
DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d") AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE ISNULL(TLNO) = FALSE AND MONTH(DATE_OF_BIRTH) = "3" AND GENDER ="W"
ORDER BY MEMBER_ID ASC;
GROUP BY ⇒ AVG를 해줄 때 식당별로 AVG를 해줘야 하기 때문에 GROUP BY가 필요하다
//오답 내 코드
SELECT a.REST_ID, a.REST_NAME, a.FOOD_TYPE, a.FAVORITES, a.ADDRESS, ROUND(AVG(b.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO AS a JOIN REST_REVIEW AS b
ON a.REST_ID = b.REST_ID
WHERE a.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, FAVORITES DESC;
//정답코드
SELECT a.REST_ID, a.REST_NAME, a.FOOD_TYPE, a.FAVORITES, a.ADDRESS, ROUND(AVG(b.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO AS a JOIN REST_REVIEW AS b
ON a.REST_ID = b.REST_ID
WHERE a.ADDRESS LIKE '서울%'
GROUP BY REST_ID
ORDER BY SCORE DESC, FAVORITES DESC;
WHERE: 그룹화가 일어나기 전의 조건절
HAVING: GROUP BY 가 적용된 이후의 조건절
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;