💻 하나씩 차곡차곡/프로그래머스 (SQL)
-
[GROUP BY] 식품분류별 가장 비싼 식품의 정보 조회하기 (Oracle)💻 하나씩 차곡차곡/프로그래머스 (SQL) 2023. 5. 11. 07:10
SELECT A.AUTHOR_ID , B.AUTHOR_NAME , A.CATEGORY , SUM(PRICE * SALES) AS TOTAL_SALES FROM BOOK A, AUTHOR B, BOOK_SALES C WHERE A.AUTHOR_ID = B.AUTHOR_ID AND A.BOOK_ID = C.BOOK_ID AND TO_CHAR(SALES_DATE, 'YYYYMM') = '202201' GROUP BY A.AUTHOR_ID , B.AUTHOR_NAME , A.CATEGORY ORDER BY AUTHOR_ID, CATEGORY DESC ;
-
[GROUP BY] 년, 월, 성별 별 상품 구매 회원 수 구하기(Oracle)💻 하나씩 차곡차곡/프로그래머스 (SQL) 2023. 5. 10. 07:46
SELECT TO_CHAR(SALES_DATE, 'YYYY') AS YEAR , TO_NUMBER(TO_CHAR(SALES_DATE, 'MM')) AS MONTH , A.GENDER AS 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(SALES_DATE, 'YYYY') , TO_NUMBER(TO_CHAR(SALES_DATE, 'MM')) , A.GENDER HAVING GENDER IS NOT NULL ORDER BY YEAR, MONTH, GENDER ;
-
[GROUP BY] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 (Oracle)💻 하나씩 차곡차곡/프로그래머스 (SQL) 2023. 5. 9. 07:10
SELECT CAR_ID , MAX( CASE WHEN '20221016' BETWEEN TO_CHAR(START_DATE, 'YYYYMMDD') AND TO_CHAR(END_DATE, 'YYYYMMDD') THEN '대여중' ELSE '대여 가능' END ) AS AVAILABILITY FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC ;
-
[GROUP BY] 조건에 맞는 사용자와 총 거래금액 조회하기(Oracle/MySQL)💻 하나씩 차곡차곡/프로그래머스 (SQL) 2023. 5. 4. 06:57
SELECT A.USER_ID , A.NICKNAME , SUM(PRICE) AS TOTAL_SALES FROM USED_GOODS_USER A, USED_GOODS_BOARD B WHERE A.USER_ID = B.WRITER_ID AND STATUS = 'DONE' GROUP BY A.USER_ID , A.NICKNAME HAVING SUM(PRICE) >= 700000 ORDER BY TOTAL_SALES ;