Programmers
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;
MySQL) 없어진 기록 찾기
SELECT O.ANIMAL_ID, O.NAMEFROM ANIMAL_OUTS O LEFT JOIN ANIMAL_INS I ON I.ANIMAL_ID = O.ANIMAL_IDWHERE I.ANIMAL_ID IS NULLORDER BY ANIMAL_ID
MySQL) 대여 기록이 존재하는 자동차 리스트
SELECT C.CAR_IDFROM CAR_RENTAL_COMPANY_CAR CJOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON H.CAR_ID = C.CAR_ID AND C.CAR_TYPE='세단'WHERE DATE_FORMAT(H.START_DATE, '%Y-%m') ='2022-10'GROUP BY C.CAR_IDORDER BY C.CAR_ID DESC
MySQL) 있었는데요 없었습니다
SELECT I.ANIMAL_ID, I.NAMEFROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_IDWHERE I.DATETIME > O.DATETIMEORDER BY I.DATETIME
MySQL) 헤비 유저가 소유한 장소
SELECT P.ID, P.NAME, P.HOST_IDFROM PLACES PJOIN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(*) >=2) S ON S.HOST_ID = P.HOST_IDORDER BY P.ID조건에 맞는 사용자 정보와 비슷한문제