Programmers

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

MySQL) 취소되지 않은 진료 예약 조회하기

SELECT a.APNT_NO, p.PT_NAME, p.PT_NO, a.MCDP_CD, d.DR_NAME, a.APNT_YMDFROM APPOINTMENT aJOIN PATIENT p ON p.PT_NO = a.PT_NOJOIN DOCTOR d ON d.DR_ID = a.MDDR_IDWHERE a.APNT_CNCL_YN = 'N'AND a.MCDP_CD = 'CS'AND DATE(a.APNT_YMD) = '2022-04-13'ORDER BY a.APNT_YMD ASC;

MySQL) 저자 별 카테고리 별 매출액 집계하기

SELECT a.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY, SUM(s.SALES * b.PRICE) AS TOTAL_SALESFROM BOOK_SALES sJOIN BOOK b ON s.BOOK_ID = b.BOOK_IDJOIN AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_IDWHERE s.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'GROUP BY a.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORYORDER BY a.AUTHOR_ID ASC, b.CATEGORY DESC;

MySQL) 입양 시각 구하기(2)

WITH RECURSIVE hours AS ( SELECT 0 AS hour UNION ALL SELECT hour + 1 FROM hours WHERE hour 0 THEN cnt.count ELSE 0 END as COUNTFROM hours h left join cnt on h.hour = cnt.hourORDER BY hour;

MySQL) 조건별로 분류하여 주문상태 출력하기

SELECT ORDER_ID, PRODUCT_ID ,DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE , CASE WHEN OUT_DATE = '2022-05-01' THEN '출고대기' ELSE '출고미정' END AS '출고여부'FROM FOOD_ORDERORDER BY ORDER_ID

MySQL) 부서별 평균 연봉 조회하기

SELECT H.DEPT_ID, H.DEPT_NAME_EN, ROUND(AVG(E.SAL), 0) AS AVG_SALFROM HR_DEPARTMENT HINNER JOIN HR_EMPLOYEES E ON H.DEPT_ID = E.DEPT_IDGROUP BY H.DEPT_ID, H.DEPT_NAME_ENORDER BY AVG_SAL DESC;