목록프로그래머스 SQL 문제풀이(Oracle)/SQL LV4(Oracle) (6)
Creative Code
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 A, REST_REVIEW B WHERE (A.REST_ID = B.REST_ID) AND A.ADDRESS LIKE '서울%' GROUP BY A.REST_ID, A.REST_NAME, A.FOOD_TYPE,A.FAVORITES,A.ADDRESS ORDER BY SCORE DESC, A.FAVORITES DESC;
SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, D.MCDP_CD, D.DR_NAME, A.APNT_YMD FROM PATIENT P, DOCTOR D, APPOINTMENT A WHERE A.PT_NO = P.PT_NO AND D.DR_ID = A.MDDR_ID AND to_char(A.APNT_YMD,'YYYY-MM-DD') LIKE '2022-04-13' AND A.APNT_CNCL_YN = 'N' ORDER BY A.APNT_YMD;
SELECT to_char(B.SALES_DATE,'YYYY') AS YEAR, to_number(to_char(B.SALES_DATE,'MM')) AS MONTH ,GENDER, COUNT(DISTINCT B.USER_ID) AS USERS FROM USER_INFO A, ONLINE_SALE B WHERE A.USER_ID = B.USER_ID GROUP BY to_char(B.SALES_DATE,'YYYY'), to_number(to_char(B.SALES_DATE,'MM')),GENDER HAVING GENDER IS NOT NULL ORDER BY YEAR, MONTH, GENDER;
SELECT PRODUCT_ID, A.PRODUCT_NAME, SUM(B.AMOUNT * A.PRICE) AS TOTAL_SALES FROM FOOD_PRODUCT A JOIN FOOD_ORDER B USING (PRODUCT_ID) WHERE to_char(B.PRODUCE_DATE,'YYYY-MM') LIKE '2022-05%' GROUP BY PRODUCT_ID, A.PRODUCT_NAME ORDER BY TOTAL_SALES DESC, PRODUCT_ID;
SELECT B.CATEGORY, B.PRICE AS MAX_PRICE, B.PRODUCT_NAME FROM (SELECT CATEGORY, MAX(PRICE) AS PRICE FROM FOOD_PRODUCT GROUP BY CATEGORY) A, FOOD_PRODUCT B WHERE A.CATEGORY = B.CATEGORY AND A.PRICE = B.PRICE AND (B.CATEGORY = '과자' OR B.CATEGORY = '국' OR B.CATEGORY = '김치' OR B.CATEGORY = '식용유') ORDER BY B.PRICE DESC;
SELECT ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS USING (ANIMAL_ID) WHERE INS.SEX_UPON_INTAKE LIKE 'Intact%' AND (OUTS.SEX_UPON_OUTCOME LIKE 'Spayed%' OR OUTS.SEX_UPON_OUTCOME LIKE 'Neutered%') ORDER BY ANIMAL_ID;