본문 바로가기

문제풀이/프로그래머스 SQL 문제

 (54)
자동차 대여 기록 별 대여 금액 구하기 SELECT RH.HISTORY_ID, ROUND(SUM(C.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1) * (1 - IFNULL(DP.DISCOUNT_RATE, 0) / 100)), 0) AS FEE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_ID = RH.CAR_ID LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP ON C.CAR_TYPE = DP.CAR_TYPE AND (CASE WHEN DATEDIFF(RH.END_DATE, RH.START_DATE) + 1 BETWEEN 7 AND 29 THEN '7..
조건에 맞는 사용자 정보 조회하기 SELECT USER_ID, NICKNAME, CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) 전체주소, CONCAT(SUBSTRING(TLNO, 1, 3), '-', SUBSTRING(TLNO, 4, 4), '-', SUBSTRING(TLNO, 8, 4)) 전화번호 FROM USED_GOODS_USER WHERE USER_ID IN (SELECT WRITER_ID FROM USED_GOODS_BOARD GROUP BY WRITER_ID HAVING COUNT(*)>=3) ORDER BY USER_ID DESC https://school.programmers.co.kr/learn/courses/30/lessons/164670
특정 옵션이 포함된 자동차 리스트 구하기 SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%네비게이션%' ORDER BY CAR_ID DESC https://school.programmers.co.kr/learn/courses/30/lessons/157343
자동차 대여 기록에서 장기/단기 대여 구분하기 SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') 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 https://school.programmers.co.kr/learn/courses/30/lessons/151138
DATETIME에서 DATE로 형 변환 SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') 날짜 FROM ANIMAL_INS https://school.programmers.co.kr/learn/courses/30/lessons/59414
카테고리 별 상품 개수 구하기 SELECT SUBSTRING(PRODUCT_CODE, 1, 2) CATEGORY, COUNT(*) PRODUCTS FROM PRODUCT GROUP BY CATEGORY ORDER BY CATEGORY https://school.programmers.co.kr/learn/courses/30/lessons/131529
오랜 기간 보호한 동물(2) SELECT I.ANIMAL_ID, I.NAME FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON O.ANIMAL_ID=I.ANIMAL_ID ORDER BY DATEDIFF(O.DATETIME, I.DATETIME) DESC LIMIT 2 https://school.programmers.co.kr/learn/courses/30/lessons/59411
중성화 여부 파악하기 SELECT ANIMAL_ID, NAME, CASE WHEN SEX_UPON_INTAKE LIKE 'Neutered%' THEN 'O' WHEN SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O' ELSE 'X' END AS 중성화 FROM ANIMAL_INS https://school.programmers.co.kr/learn/courses/30/lessons/59409
이름에 el이 들어가는 동물 찾기 SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE ANIMAL_TYPE='Dog' AND NAME LIKE '%el%' ORDER BY NAME https://school.programmers.co.kr/learn/courses/30/lessons/59047
루시와 엘라 찾기 SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty') https://school.programmers.co.kr/learn/courses/30/lessons/59046

티스토리툴바