본문 바로가기
728x90
320x100

💻 하나씩 차곡차곡/프로그래머스 (SQL)105

[JOIN/LEVEL2] 조건에 맞는 도서와 저자 리스트 출력하기 (MySQL/Oracle) MySQLSELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATEFROM BOOK A, AUTHOR BWHERE A.AUTHOR_ID = B.AUTHOR_IDAND A.CATEGORY = '경제'ORDER BY A.PUBLISHED_DATE OracleSELECT A.BOOK_ID , B.AUTHOR_NAME , TO_CHAR(A.PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATEFROM BOOK A, AUTHOR BWHERE A.AUTHOR_ID = B.AUTHOR_IDAND A.CATEGORY = '경제'ORDER BY A.PUBLISHED.. 2023. 5. 15.
[GROUP BY] 식품분류별 가장 비싼 식품의 정보 조회하기 (Oracle) 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 ; 2023. 5. 11.
[GROUP BY/LEVEL4] 년, 월, 성별 별 상품 구매 회원 수 구하기(MYSQL/Oracle) MYSQLSELECT YEAR(B.SALES_DATE) AS YEAR , MONTH(B.SALES_DATE) AS MONTH , A.GENDER , COUNT(DISTINCT A.USER_ID) AS USERSFROM USER_INFO A, ONLINE_SALE BWHERE A.USER_ID = B.USER_IDAND A.GENDER IS NOT NULLGROUP BY YEAR(B.SALES_DATE) , MONTH(B.SALES_DATE) , A.GENDERORDER BY YEAR, MONTH, GENDER OracleSELECT TO_CHAR(SALES_DATE, 'YYYY') AS YEAR, TO_NUMBER(TO_CHAR(SAL.. 2023. 5. 10.
[GROUP BY/LEVEL3] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 (MySQL/Oracle) MySQLSELECT CAR_ID, MAX(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN '대여중' ELSE '대여 가능' END) AS AVAILABILITYFROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_IDORDER BY CAR_ID DESC  OracleSELECT CAR_ID, MAX( CASE WHEN '20221016' BETWEEN TO_CHAR(START_DATE, 'YYYYMMDD') AND TO_CHAR(END_DATE, 'YYYYMMDD') THEN '대여중' ELSE '대여 가능' END ) AS AVAILABILITYFROM CAR_R.. 2023. 5. 9.
[GROUP BY] 카테고리 별 도서 판매량 집계하기 (Oracle) SELECT CATEGORY, SUM(B.SALES) AS TOTAL_SALES FROM BOOK A, BOOK_SALES B WHERE A.BOOK_ID = B.BOOK_ID AND TO_CHAR(SALES_DATE, 'YYYYMM') = '202201' GROUP BY CATEGORY ORDER BY CATEGORY ; 2023. 5. 8.
[GROUP BY/LEVEL3] 조건에 맞는 사용자와 총 거래금액 조회하기(MySQL/Oracle) MySQL/OracleSELECT A.USER_ID , A.NICKNAME , SUM(PRICE) AS TOTAL_SALESFROM USED_GOODS_USER A, USED_GOODS_BOARD BWHERE A.USER_ID = B.WRITER_IDAND STATUS = 'DONE'GROUP BY A.USER_ID , A.NICKNAMEHAVING SUM(PRICE) >= 700000ORDER BY TOTAL_SALES; 2023. 5. 4.
728x90
320x100

티스토리툴바