[TIL] Programmers SQL 문제 풀이

https://school.programmers.co.kr/learn/courses/30/lessons/144853

SELECT BOOK_ID, SUBSTRING_INDEX(PUBLISHED_DATE, 1, 10) FROM BOOK
WHERE CATEGORY = '인문' AND SUBSTRING_INDEX(PUBLISHED_DATE, 1, 4) = 2021
ORDER BY PUBLISHED_DATE

 

https://school.programmers.co.kr/learn/courses/30/lessons/151136

SELECT ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

 

https://school.programmers.co.kr/learn/courses/30/lessons/59415

SELECT DATETIME AS '시간' FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1

 

https://school.programmers.co.kr/learn/courses/30/lessons/133025

SELECT II.FLAVOR FROM ICECREAM_INFO II
INNER JOIN FIRST_HALF FH ON II.FLAVOR = FH.FLAVOR
WHERE FH.TOTAL_ORDER > 3000 AND II.INGREDIENT_TYPE = 'fruit_based'

 

https://school.programmers.co.kr/learn/courses/30/lessons/157343

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/151138

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) < 29 THEN '단기 대여'
		    ELSE '장기 대여'
            END AS RENT_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-09-01' and '2022-09-30'
ORDER BY HISTORY_ID DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/164673

SELECT UB.TITLE,
       UB.BOARD_ID,
       UR.REPLY_ID,
       UR.WRITER_ID,
       UR.CONTENTS,
       DATE_FORMAT(UR.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
    FROM USED_GOODS_BOARD UB
INNER JOIN USED_GOODS_REPLY UR ON UB.BOARD_ID = UR.BOARD_ID
WHERE UB.CREATED_DATE LIKE '2022-10%'
ORDER BY UR.CREATED_DATE, UB.TITLE

 

https://school.programmers.co.kr/learn/courses/30/lessons/59408

SELECT (COUNT(DISTINCT(NAME))) AS count FROM ANIMAL_INS

 

https://school.programmers.co.kr/learn/courses/30/lessons/59406

SELECT COUNT(*) FROM ANIMAL_INS

 

https://school.programmers.co.kr/learn/courses/30/lessons/59038

SELECT DATETIME AS '시간' FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

 

https://school.programmers.co.kr/learn/courses/30/lessons/59041

SELECT NAME, COUNT(NAME) FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME

 

https://school.programmers.co.kr/learn/courses/30/lessons/59047

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
WHERE NAME LIKE '%el%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

 

https://school.programmers.co.kr/learn/courses/30/lessons/59410

SELECT ANIMAL_TYPE, IF(ISNULL(NAME), 'No name', NAME), SEX_UPON_INTAKE FROM ANIMAL_INS

 

https://school.programmers.co.kr/learn/courses/30/lessons/59414

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜' FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

https://school.programmers.co.kr/learn/courses/30/lessons/131115

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
    FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1

 

https://school.programmers.co.kr/learn/courses/30/lessons/59040

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Cat' OR ANIMAL_TYPE = 'DOG'
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

 

https://school.programmers.co.kr/learn/courses/30/lessons/59409

SELECT ANIMAL_ID, NAME,
    IF(SEX_UPON_INTAKE LIKE 'Intact%', 'X', 'O') AS 중성화
    FROM ANIMAL_INS

 

https://school.programmers.co.kr/learn/courses/30/lessons/59412

SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT
    FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

 

https://school.programmers.co.kr/learn/courses/30/lessons/131529

SELECT SUBSTRING_INDEX(PRODUCT_CODE, 0, 1) AS CATEGORY,
       COUNT(SUBSTRING_INDEX(PRODUCT_CODE, 0, 1)) AS PRODUCTS
    FROM PRODUCT
GROUP BY SUBSTRING_INDEX(PRODUCT_CODE, 0, 1)
ORDER BY PRODUCT_CODE

 

https://school.programmers.co.kr/learn/courses/30/lessons/132202

SELECT MCDP_CD AS 진료과코드, COUNT(MCDP_CD) AS 5월예약건수
    FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD
ORDER BY COUNT(MCDP_CD), MCDP_CD

 

https://school.programmers.co.kr/learn/courses/30/lessons/59046

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS
WHERE NAME = 'Lucy' OR NAME = 'Ella' OR NAME = 'Pickle' OR NAME = 'Rogan'
            OR NAME = 'Sabrina' OR NAME = 'Mitty'
ORDER BY ANIMAL_ID

 

https://school.programmers.co.kr/learn/courses/30/lessons/131533

SELECT P.PRODUCT_CODE,
       SUM(P.PRICE * O.SALES_AMOUNT) AS SALES FROM PRODUCT P
INNER JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, P.PRODUCT_CODE

 

https://school.programmers.co.kr/learn/courses/30/lessons/131120

SELECT MEMBER_ID, MEMBER_NAME, GENDER,
       DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
    FROM MEMBER_PROFILE
WHERE DATE_FORMAT(DATE_OF_BIRTH, '%m') = 3 AND
      GENDER = 'W' AND
      TLNO IS NOT NULL
ORDER BY MEMBER_ID

 

https://school.programmers.co.kr/learn/courses/30/lessons/151137

SELECT CAR_TYPE, COUNT(*) AS CARS FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR 
      OPTIONS LIKE '%열선시트%' OR
      OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE

 

https://school.programmers.co.kr/learn/courses/30/lessons/144854

SELECT B.BOOK_ID, A.AUTHOR_NAME,
       DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE FROM BOOK B
INNER JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY PUBLISHED_DATE

 

https://school.programmers.co.kr/learn/courses/30/lessons/133026

SELECT II.INGREDIENT_TYPE, SUM(FH.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF FH
INNER JOIN ICECREAM_INFO II ON FH.FLAVOR = II.FLAVOR
GROUP BY II.INGREDIENT_TYPE
ORDER BY SUM(FH.TOTAL_ORDER)

 

https://school.programmers.co.kr/learn/courses/30/lessons/131530

SELECT (CASE
            WHEN PRICE < 10000 THEN 0
            ELSE TRUNCATE(PRICE, -4)
        END) AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

 

https://school.programmers.co.kr/learn/courses/30/lessons/131536

SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID, PRODUCT_ID DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/164672

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
    (CASE WHEN STATUS = 'SALE' THEN '판매중'
          WHEN STATUS = 'RESERVED' THEN '예약중'
          ELSE '거래완료'
     END) AS STATUS
     FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-10-05%'
ORDER BY BOARD_ID DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/157342

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
    HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC