✅ 코드 # 서울에 위치한 레스토랑 리스트 WITH REST_IN_SEOUL AS ( SELECT * FROM REST_INFO WHERE ADDRESS LIKE '서울%' ) SELECT I.REST_ID AS REST_ID , I.REST_NAME AS REST_NAME , I.FOOD_TYPE AS FOOD_TYPE , I.FAVORITES AS FAVORITES , I.ADDRESS AS ADDRESS , ROUND(AVG(R.REVIEW_SCORE), 2) AS REVIEW_SCORE FROM REST_IN_SEOUL AS I INNER JOIN REST_REVIEW AS R ON I.REST_ID = R.REST_ID GROUP BY REST_ID ORDER BY REVIEW_SCORE DES..
✅ 코드 WITH T2 AS ( SELECT CATEGORY , MAX(PRICE) AS PRICE FROM FOOD_PRODUCT GROUP BY CATEGORY ) SELECT T1.CATEGORY , T1.PRICE , T1.PRODUCT_NAME FROM FOOD_PRODUCT AS T1 INNER JOIN T2 ON T1.CATEGORY = T2.CATEGORY AND T1.PRICE = T2.PRICE WHERE T1.CATEGORY IN ('과자', '국', '김치', '식용유') ORDER BY T1.PRICE DESC WITH문(또는 FROM절 서브쿼리)를 사용하면 쉽게 풀이가 가능합니다. 식품분류별 가장 높은 가격의 값을 가져오면 됩니다. 참고로 Lv.3 문제와 유사합니다. [SQL]프..
✅ 코드 SELECT O.ANIMAL_ID , O.NAME FROM ANIMAL_OUTS AS O LEFT JOIN ANIMAL_INS AS I ON O.ANIMAL_ID = I.ANIMAL_ID WHERE I.ANIMAL_ID IS NULL; LEFT JOIN을 수행한 풀이입니다.
✅ 코드 2개의 컬럼을 사용하여 그룹화 했습니다. SELECT USER_ID , PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID , PRODUCT_ID HAVING COUNT(ONLINE_SALE_ID) >= 2 ORDER BY USER_ID , PRODUCT_ID DESC;
✅ 코드 SELECT T2.FOOD_TYPE AS FOOD_TYPE , T2.REST_ID AS REST_ID , T2.REST_NAME AS REST_NAME , T2.FAVORITES AS FAVORITES FROM (SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES FROM REST_INFO GROUP BY FOOD_TYPE) AS T1 INNER JOIN REST_INFO AS T2 ON T1.FOOD_TYPE = T2.FOOD_TYPE AND T1.FAVORITES = T2.FAVORITES ORDER BY FOOD_TYPE DESC; 서브쿼리를 사용한 풀이입니다. 🔎 참고 SELECT FOOD_TYPE , REST_ID , REST_NAME , MAX(FAVOR..
✅ 코드 SELECT ORDER_ID , PRODUCT_ID , DATE_FORMAT(OUT_DATE, '%Y-%m-%d') , CASE WHEN OUT_DATE IS NULL THEN '출고미정' WHEN OUT_DATE
✅ 코드 SELECT (PRICE - (PRICE % 10000)) AS PRICE_GROUP , COUNT(PRODUCT_ID) AS PRODUCTS FROM PRODUCT GROUP BY PRICE_GROUP ORDER BY PRICE_GROUP; 그룹화 기준을 SELECT절에 선언할 수 있습니다. 기존 컬럼의 값을 활용하여 새로운 기준을 만들 수 있습니다.
✅ 코드 SELECT U.USER_ID AS USER_ID , U.NICKNAME AS NICKNAME , SUM(PRICE) AS TOTAL_SALES FROM USED_GOODS_BOARD AS B INNER JOIN USED_GOODS_USER AS U ON B.WRITER_ID = U.USER_ID WHERE B.STATUS = 'DONE' GROUP BY USER_ID HAVING SUM(PRICE) >= 700000 ORDER BY TOTAL_SALES; 두 테이블에 대해 INNER JOIN을 수행한 후에 제약 조건들을 지정했습니다.
