[SQL 공부-프로그래머스] Lv.3 모음 - 1

 

없어진 기록 찾기

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

프로그래머스

SELECT    OUTS.ANIMAL_ID,
          OUTS.NAME
FROM      ANIMAL_OUTS AS OUTS
LEFT JOIN ANIMAL_INS AS INS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE     INS.ANIMAL_ID IS NULL

 

있었는데요 없었습니다

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

프로그래머스

SELECT     INS.ANIMAL_ID, 
           INS.NAME
FROM       ANIMAL_INS AS INS
INNER JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE      INS.DATETIME > OUTS.DATETIME
ORDER BY   INS.DATETIME ASC

 

오랜 기간 보호한 동물(1)

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

프로그래머스

SELECT    INS.NAME, 
          INS.DATETIME
FROM      ANIMAL_INS AS INS
LEFT JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE     OUTS.ANIMAL_ID IS NULL
ORDER BY  INS.DATETIME ASC
LIMIT     3

 

오랜 기간 보호한 동물(2)

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

프로그래머스

SELECT     INS.ANIMAL_ID, 
           INS.NAME
FROM       ANIMAL_INS AS INS
INNER JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY   DATEDIFF(OUTS.DATETIME, INS.DATETIME) DESC
LIMIT      2

 

헤비 유저가 소유한 장소

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

프로그래머스

SELECT * FROM PLACES
WHERE HOST_ID IN(
                SELECT HOST_ID FROM PLACES
                GROUP BY HOST_ID
                HAVING COUNT(ID) >= 2
                )
ORDER BY ID ASC;

 

조건별로 분류하여 주문상태 출력하기

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

프로그래머스

SELECT   ORDER_ID,
         PRODUCT_ID,
         DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE,
         CASE WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
              WHEN OUT_DATE <= '2022-05-01' THEN '출고완료' 
              ELSE '출고미정' 
         END AS 출고여부
FROM     FOOD_ORDER
ORDER BY ORDER_ID ASC

 

즐겨찾기가 가장 많은 식당 정보 출력하기

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

프로그래머스

SELECT   FOOD_TYPE,
         REST_ID,
         REST_NAME,
         FAVORITES
FROM     REST_INFO
WHERE    (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
                                 FROM REST_INFO
                                 GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC

 

카테고리 별 도서 판매량 집계하기

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

프로그래머스

SELECT     b.CATEGORY, 
           SUM(bs.SALES) AS TOTAL_SALES
FROM       BOOK b
INNER JOIN BOOK_SALES bs on b.BOOK_ID = bs.BOOK_ID
WHERE      bs.SALES_DATE LIKE "2022-01%"
GROUP BY   CATEGORY
ORDER BY   CATEGORY

 

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

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

프로그래머스

SELECT   MONTH(START_DATE) AS MONTH,
         CAR_ID,
         COUNT(HISTORY_ID) AS RECORDS
FROM     CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE    START_DATE >= "2022-08-01" 
         AND START_DATE < "2022-11-01" 
         AND CAR_ID IN (SELECT CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE START_DATE >= "2022-08-01" and START_DATE < "2022-11-01"
            GROUP BY CAR_ID
            HAVING COUNT(CAR_ID) > 4)
GROUP BY MONTH, CAR_ID
HAVING   RECORDS > 0
ORDER BY MONTH ASC, CAR_ID DESC