▶MySQL / Oracle SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID

▶ MySQL SELECT WAREHOUSE_ID ,WAREHOUSE_NAME ,ADDRESS ,IFNULL(FREEZER_YN,'N') FROM FOOD_WAREHOUSE WHERE ADDRESS LIKE '경기도%' ORDER BY WAREHOUSE_ID ▶ Oracle SELECT WAREHOUSE_ID ,WAREHOUSE_NAME ,ADDRESS ,NVL(FREEZER_YN,'N') FROM FOOD_WAREHOUSE WHERE ADDRESS LIKE '경기도%' ORDER BY WAREHOUSE_ID

▶MySQL / Oracle SELECT CATEGORY ,MAX(PRICE) ,PRODUCT_NAME FROM FOOD_PRODUCT A WHERE CATEGORY IN ('과자','국','김치','식용유') AND PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY = A.CATEGORY) GROUP BY CATEGORY,PRODUCT_NAME ORDER BY MAX(PRICE) DESC

▶ MySQL SELECT A.AUTHOR_ID ,B.AUTHOR_NAME ,A.CATEGORY ,SUM(A.PRICE * C.SALES) AS TOTAL_SALES FROM BOOK A INNER JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID INNER JOIN BOOK_SALES C ON A.BOOK_ID = C.BOOK_ID WHERE C.SALES_DATE BETWEEN DATE_FORMAT('20220101','%Y-%m-%d') AND DATE_FORMAT('20220131','%Y-%m-%d') GROUP BY A.AUTHOR_ID,B.AUTHOR_NAME,A.CATEGORY ORDER BY A.AUTHOR_ID,A.CATEGORY DESC; ▶Oracle SE..

▶ MySQL SELECT CAR_ID ,CASE WHEN MAX(DATE_FORMAT('20221016','%Y-%m-%d') BETWEEN START_DATE AND END_DATE) THEN '대여중' ELSE '대여 가능' END AS AVAILABILITY FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC ▶Oracle SELECT CAR_ID ,MAX(AVAILABILITY) AS AVAILABILITY FROM (SELECT CAR_ID ,CASE WHEN TO_DATE('20221016','YYYY-MM-DD') BETWEEN START_DATE AND END_DATE THEN '대여중' ELSE '대여 ..

▶ MySQL SELECT A.CATEGORY ,SUM(B.SALES) AS TOTAL_SALES FROM BOOK A INNER JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID WHERE DATE_FORMAT(B.SALES_DATE,'%Y-%m') = '2022-01' GROUP BY A.CATEGORY ORDER BY A.CATEGORY ▶Oracel SELECT A.CATEGORY ,SUM(B.SALES) AS TOTAL_SALES FROM BOOK A INNER JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID WHERE TO_CHAR(B.SALES_DATE,'YYYYMM') = '202201' GROUP BY A.CATEGORY ORDER ..

▶ MySQL SELECT (PRICE - PRICE % 10000) AS PRICE_GROUP ,COUNT(*) AS PRODUCTS FROM PRODUCT GROUP BY (PRICE - PRICE % 10000) ORDER BY PRICE_GROUP ▶Oracle SELECT TRUNC(PRICE,-4) AS PRICE_GROUP ,COUNT(*) AS PRODUCTS FROM PRODUCT GROUP BY TRUNC(PRICE,-4) ORDER BY PRICE_GROUP

▶ MySQL SELECT DATE_FORMAT(DATETIME,'%H') AS HOUR ,COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE DATE_FORMAT(DATETIME,'%H') >= 9 AND DATE_FORMAT(DATETIME,'%H') < 20 GROUP BY DATE_FORMAT(DATETIME,'%H') ORDER BY HOUR ▶ Oracle SELECT TO_NUMBER(TO_CHAR(DATETIME,'HH24')) AS HOUR ,COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE TO_NUMBER(TO_CHAR(DATETIME,'HH24')) BETWEEN 9 AND 19 GROUP BY TO_CHAR(DATETIME,'HH24')..

▶ MySQL / Oracle SELECT ANIMAL_TYPE ,COUNT(ANIMAL_TYPE) AS COUNT FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE ▶LEAST 함수 활용(Oracle) SELECT ANIMAL_TYPE ,COUNT(ANIMAL_TYPE) AS COUNT FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY LEAST(ANIMAL_TYPE)

▶Join 활용 SELECT U.USER_ID ,U.NICKNAME ,SUM(B.PRICE) AS TOTAL_SALES FROM USED_GOODS_BOARD B INNER JOIN USED_GOODS_USER U ON B.WRITER_ID = U.USER_ID WHERE B.STATUS = 'DONE' GROUP BY U.USER_ID,U.NICKNAME HAVING SUM(B.PRICE) >= 700000 ORDER BY TOTAL_SALES;

▶ Multiple-Row 활용 SELECT FOOD_TYPE ,REST_ID ,REST_NAME ,FAVORITES FROM REST_INFO WHERE (FAVORITES,FOOD_TYPE) IN (SELECT MAX(FAVORITES),FOOD_TYPE FROM REST_INFO GROUP BY FOOD_TYPE) ORDER BY FOOD_TYPE DESC; ▶Join 활용 SELECT A.FOOD_TYPE ,B.REST_ID ,B.REST_NAME ,A.FAVORITES FROM (SELECT FOOD_TYPE ,MAX(FAVORITES) AS FAVORITES FROM REST_INFO GROUP BY FOOD_TYPE) A INNER JOIN REST_INFO B ON A.FOOD_TYPE =..

▶MySQL SELECT CAR_TYPE ,COUNT(OPTIONS) AS CARS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%시트%' GROUP BY CAR_TYPE ORDER BY CAR_TYPE; ▶Oracle SELECT CAR_TYPE ,COUNT(OPTIONS) AS CARS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%시트%' GROUP BY CAR_TYPE ORDER BY CAR_TYPE;