목록self study/Database (48)
happy coding
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') FROM DOCTOR WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS' ORDER BY HIRE_YMD DESC, DR_NAME ASC;
SELECT PT_NAME, PT_NO, GEND_CD, AGE, CASE WHEN TLNO IS NULL THEN 'NONE' ELSE TLNO END AS TLNO FROM PATIENT WHERE AGE < 13 AND GEND_CD = 'W' ORDER BY AGE DESC, PT_NAME ASC;
SELECT FLAVOR FROM FIRST_HALF ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE FROM BOOK WHERE CATEGORY = '인문' AND YEAR(PUBLISHED_DATE) = '2021' ORDER BY PUBLISHED_DATE ASC;
SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE FROM CAR_RENTAL_COMPANY_CAR WHERE CAR_TYPE = 'SUV';
SELECT ICECREAM_INFO.FLAVOR FROM FIRST_HALF, ICECREAM_INFO INNER JOIN ICECREAM_INFO ON FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR WHERE FIRST_HALF.TOTAL_ORDER > 3000 AND ICECREAM_INFO.INGREDIENT_TYPE = 'fruit_based' ORDER BY FIRST_HALF.TOTAL_ORDER DESC; 틀렸다. SQL 실행 중 오류가 발생하였습니다. Not unique table/alias: 'ICECREAM_INFO' SELECT FIRST_HALF.FLAVOR FROM FIRST_HALF LEFT JOIN ICECREAM_INFO ON FIRST_HALF...
SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%네비게이션%' ORDER BY CAR_ID DESC;
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, CASE WHEN DATEDIFF(END_DATE,START_DATE) +1 >= 30 THEN '장기 대여' ELSE '단기 대여' END AS RENT_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE LIKE '2022-09-%' ORDER BY HISTORY_ID DESC; case문 유의점 : 하루 동안 대여하고 반납해도 기간은 1일 반납하기 때문에 0 반례 해결해야 한다.