Programmers

MySQL) 월별 잡은 물고기 수 구하기

select count(*) AS FISH_COUNT, month(time) as MONTHfrom FISH_INFOgroup by month(time)having count(*)order by month asc;

MySQL) 우유와 요거트가 담긴 장바구니

WITH CTE AS ( SELECT CART_ID, NAME FROM CART_PRODUCTS WHERE NAME IN ('Milk', 'Yogurt'))SELECT CART_IDFROM CTEGROUP BY CART_IDHAVING COUNT(DISTINCT NAME) = 2ORDER BY CART_ID ASC;

MySQL) 조건에 맞는 사용자와 총 거래금액 조회하기

SELECT USER_ID, NICKNAME , SUM(B.PRICE) AS TOTAL_SALESFROM USED_GOODS_BOARD BJOIN USED_GOODS_USER U ON U.USER_ID = B.WRITER_IDWHERE B.STATUS = 'DONE'GROUP BY U.USER_IDHAVING SUM(B.PRICE) >= 700000ORDER BY TOTAL_SALES ASC;

MySQL) 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

WITH CTE AS ( SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31' GROUP BY CAR_ID HAVING COUNT(*) >= 5)SELECT MONTH(START_DATE) AS MONTH, H.CAR_ID, COUNT(*) AS RECORDSFROM CAR_RENTAL_COMPANY_RENTAL_HISTORY HJOIN CTE ON H.CAR_ID = CTE.CAR_IDWHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'GROUP BY MONTH(START_DAT..

MySQL) 카테고리 별 도서 판매량 집계하기

SELECT CATEGORY, SUM(S.SALES) AS TOTAL_SALESFROM BOOK BJOIN BOOK_SALES S ON S.BOOK_ID = B.BOOK_IDWHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'GROUP BY CATEGORYORDER BY B.CATEGORY ASC

MySQL) 조건에 맞는 사원 정보 조회하기

-- 상반기, 하반기 더해서 가장 높은 점수구하기.WITH CTE AS(SELECT E.EMP_NO, SUM(SCORE) AS SCOREFROM HR_GRADE G JOIN HR_EMPLOYEES E ON G.EMP_NO = E.EMP_NOWHERE G.YEAR = '2022'GROUP BY E.EMP_NOORDER BY SCORE DESCLIMIT 1)SELECT C.SCORE AS SCORE, E.EMP_NO ,E.EMP_NAME ,E.POSITION ,E.EMAILFROM HR_EMPLOYEES EJOIN CTE C ON C.EMP_NO = E.EMP_NO

MySQL) 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

SELECT CAR_TYPE, COUNT(*)FROM CAR_RENTAL_COMPANY_CARWHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%'GROUP BY CAR_TYPEORDER BY CAR_TYPE ASC

MySQL) 경기도에 위치한 식품창고 목록 출력하기

SELECT WAREHOUSE_ID,WAREHOUSE_NAME, ADDRESS,IFNULL(FREEZER_YN, 'N') AS FREEZER_YNFROM FOOD_WAREHOUSEWHERE ADDRESS LIKE '경기도%'ORDER BY WAREHOUSE_ID ASC

MySQL) 잡은 물고기의 평균 길이 구하기

SELECT ROUND(AVG(CASE WHEN LENGTH IS NULL THEN 10 ELSE LENGTH END), 2) AS AVERAGE_LENGTHFROM FISH_INFOSELECT ROUND(AVG(IFNULL(LENGTH,10)),2) AS AVERAGE_LENGTHFROM FISH_INFO

MySQL) 오프라인/온라인 판매 데이터 통합하기

WITH CTE AS(SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNTFROM ONLINE_SALEUNION ALLSELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNTFROM OFFLINE_SALE)SELECT * FROM CTEWHERE YEAR(SALES_DATE)='2022' AND MONTH(SALES_DATE)='03'ORDER BY SALES_DATE, PRODUCT_ID, USER_ID