#73. STRING, DATE : 자동차 대여 기록 별 대여 금액 구하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. SELECT RENT.HISTORY_ID, ROUND(CAR.DAILY_FEE * (DATEDIFF(RENT.END_DATE, RENT.START_DATE)+1) * (CASE WHEN DATEDIFF(END_DATE,START_DATE)+1 < 7 THEN 1 WHEN DATEDIFF(END_DATE,START_DATE)+1 < 30 THEN 0.95 WHEN DATEDIFF(END_DATE,START_DATE)+1 < 90 THEN 0.92 ELSE 0.85 END) ) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS CAR JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT ON CAR.CAR_ID = RENT.CAR_ID J..

• format_list_bulleted SQL
• · 2023. 8. 30.
• textsms

#72. STRING, DATE : 취소되지 않은 진료 예약 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD FROM APPOINTMENT AS A JOIN PATIENT AS P ON A.PT_NO = P.PT_NO JOIN DOCTOR AS D ON A.MDDR_ID = D.DR_ID WHERE DATE_FORMAT(A.APNT_YMD, '%Y-%m-%d') = '2022-04-13' AND A.APNT_CNCL_YN = 'N' AND A.MCDP_CD = 'CS' ORDER BY A.APNT_YMD ASC; 코드 설명 #SELECT #JOIN #WHERE #DATE_FORMAT #AND #ORDER BY 문제 출처 https://school.programmers.c..

• format_list_bulleted SQL
• · 2023. 8. 30.
• textsms

#71. JOIN : 보호소에서 중성화한 동물 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME FROM ANIMAL_INS AS I JOIN ANIMAL_OUTS AS O ON I.ANIMAL_ID = O.ANIMAL_ID WHERE I.SEX_UPON_INTAKE LIKE 'Intact%' AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%') ORDER BY ANIMAL_ID; 2. SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME FROM ANIMAL_INS AS I JOIN ANIMAL_OUTS AS O WHERE (I.ANIMAL_ID = O.ANIMAL_ID) AND (I.SEX_U..

• format_list_bulleted SQL
• · 2023. 8. 29.
• textsms

#70. JOIN : 5월 식품들의 총매출 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(O.AMOUNT * P.PRICE) AS TOTAL_SALES FROM FOOD_PRODUCT AS P JOIN FOOD_ORDER AS O ON P.PRODUCT_ID = O.PRODUCT_ID WHERE O.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31' GROUP BY O.PRODUCT_ID ORDER BY TOTAL_SALES DESC, O.PRODUCT_ID ASC; 조건1. 생산일자가 2022년 5월인 식품 # 1. BETWEEN WHERE O.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31' # 2. LIKE WHERE ..

• format_list_bulleted SQL
• · 2023. 8. 29.
• textsms

#69. JOIN : 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 SELECT CAR.CAR_ID, CAR.CAR_TYPE, ROUND((CAR.DAILY_FEE * ((100 - DISCOUNT.DISCOUNT_RATE)/100) * 30)) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS CAR JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT ON CAR.CAR_ID = RENT.CAR_ID JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS DISCOUNT ON CAR.CAR_TYPE = DISCOUNT.CAR_TYPE WHERE CAR.CAR_ID NOT IN( SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE EN..

• format_list_bulleted SQL
• · 2023. 8. 28.
• textsms

#68. JOIN : 주문량이 많은 아이스크림들 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. JOIN - ON SELECT FH.FLAVOR FROM FIRST_HALF AS FH JOIN JULY AS JUL ON FH.FLAVOR = JUL.FLAVOR GROUP BY FH.FLAVOR ORDER BY SUM(FH.TOTAL_ORDER) + SUM(JUL.TOTAL_ORDER) DESC LIMIT 3; 2. JOIN - USING SELECT FLAVOR FROM FIRST_HALF AS A JOIN JULY AS B USING (FLAVOR) GROUP BY FLAVOR ORDER BY SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) DESC LIMIT 3; → 조인할 두 컬럼의 이름이 같은 경우에는 ON 대신에 USING을 사용할 수 있음 3. SUBQ..

• format_list_bulleted SQL
• · 2023. 8. 28.
• textsms