하나둘셋넷

SQL GROUP BY] 년, 월, 성별 별 상품 구매 회원 수 구하기

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

-- 코드를 입력하세요
SELECT DATE_FORMAT(O.SALES_DATE, '%Y') as YEAR, DATE_FORMAT(O.SALES_DATE, '%m') as MONTH,
        GENDER, COUNT(DISTINCT(U.USER_ID)) as USERS
FROM USER_INFO U
INNER JOIN ONLINE_SALE O on U.USER_ID = O.USER_ID
WHERE GENDER IS NOT NULL 
GROUP BY YEAR, MONTH, U.GENDER
ORDER BY YEAR, MONTH, U.GENDER

SQL String, Date] 오랜 기간 보호한 동물(2)

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

-- 코드를 입력하세요
SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I
INNER JOIN ANIMAL_OUTS O on I.ANIMAL_ID = O.ANIMAL_ID
ORDER BY DATEDIFF(O.DATETIME, I.DATETIME) DESC
LIMIT 2

SQL String, Date] 취소되지 않은 진료 예약 조회하기

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

-- 코드를 입력하세요

# "1월 2일" - "1월 1일"인 경우 1일이 아닌 2일로 계산해야 한다.

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 AVG(DATEDIFF(END_DATE, START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

SQL GROUP BY] 성분으로 구분한 아이스크림 총 주문량

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

-- 코드를 입력하세요
SELECT I.INGREDIENT_TYPE, SUM(F.TOTAL_ORDER) as TOTAL_ORDER
FROM FIRST_HALF F
INNER JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE

SQL GROUP BY] 고양이와 개는 몇 마리 있을까

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

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) as count
    FROM ANIMAL_INS
    GROUP BY ANIMAL_TYPE
    ORDER BY ANIMAL_TYPE

SQL String, Date] 취소되지 않은 진료 예약 조회하기

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

-- 코드를 입력하세요
SELECT A.APNT_NO, P.PT_NAME,  A.PT_NO,  D.MCDP_CD, D.DR_NAME, A.APNT_YMD 
FROM PATIENT P
LEFT JOIN APPOINTMENT A ON P.PT_NO = A.PT_NO
LEFT JOIN DOCTOR D on D.DR_ID = A.MDDR_ID	
WHERE (A.APNT_CNCL_YN = 'N') 
    AND (DATE(A.APNT_YMD) = '2022-04-13')
    AND (D.MCDP_CD = 'CS')
ORDER BY A.APNT_YMD

SQL GROUP BY] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

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

SELECT C.CAR_ID, C.CAR_TYPE, ROUND((C.DAILY_FEE * 30 * (1 - D.DISCOUNT_RATE / 100.0))) AS FEE

FROM CAR_RENTAL_COMPANY_CAR C

INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE AND D.DURATION_TYPE = '30일 이상'

### '세단','SUV' 조건에 속하면서 11월에 대여중인 CAR_ID가 11월에 대여 중인 그룹에는 속하지 않아야 한다. ###
WHERE C.CAR_TYPE IN ('세단', 'SUV')
        AND C.CAR_ID NOT IN (
            SELECT H.CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
            WHERE (H.START_DATE <= '2022-11-30' AND H.END_DATE >= '2022-11-01')
        )
        
GROUP BY C.CAR_ID
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

SQL JOIN] 상품 별 오프라인 매출 구하기

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

-- 코드를 입력하세요
SELECT P.PRODUCT_CODE, SUM(O.SALES_AMOUNT) * P.PRICE as SALES
    FROM PRODUCT P
    INNER JOIN OFFLINE_SALE O on P.PRODUCT_ID = O.PRODUCT_ID
    GROUP BY P.PRODUCT_ID
    ORDER BY SALES DESC,  P.PRODUCT_CODE

SQL GROUP BY] 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

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

-- 코드를 입력하세요
SELECT CAR_TYPE,COUNT(CAR_ID) as CARS
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE OPTIONS LIKE '%통풍시트%'
                OR OPTIONS LIKE '%열선시트%'
                OR OPTIONS LIKE '%가죽시트%'
    GROUP BY CAR_TYPE
    ORDER BY CAR_TYPE

SQL JOIN] 주문량이 많은 아이스크림들 조회하기

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

-- 코드를 입력하세요
SELECT F.FLAVOR
FROM ( 
    SELECT FLAVOR, TOTAL_ORDER
    FROM FIRST_HALF
    
) F
INNER JOIN JULY J ON F.FLAVOR = J.FLAVOR
GROUP BY F.FLAVOR
ORDER BY (SUM(F.TOTAL_ORDER) + SUM(J.TOTAL_ORDER)) DESC
LIMIT 3

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

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

SELECT G.MONTH, G.CAR_ID, COUNT(G.CAR_ID) AS RECORDS
FROM (
    SELECT CAR_ID, MONTH(START_DATE) AS MONTH
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
) G
INNER JOIN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(CAR_ID) >= 5
) T ON G.CAR_ID = T.CAR_ID
GROUP BY G.CAR_ID, G.MONTH
ORDER BY G.MONTH, G.CAR_ID DESC;

SQL GROUP BY] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

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

SELECT CAR_ID,
       CASE
            # START와 DATE 사이에 최소 2022-10-16이 끼어있다면, 1보다 클 것이고 이 경우에 '대여중'으로 표시한다.
            # 한 번이라도 해당 날짜에 대여중인 상태가 있는지 확인한다.
            WHEN SUM(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN 1 ELSE 0 END) > 0
            THEN '대여중'
            ELSE '대여 가능' 
        END AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

SQL SUM, MAX, MIN] 동물 수 구하기 COUNT

SQL String, Date] 조건별로 분류하여 주문상태 출력하기

SQL SELECT] 조건에 맞는 도서 리스트 출력하기

SQL JOIN] 오랜 기간 보호한 동물

SQL IS NULL] 이름이 있는 동물의 아이디

SQL GROUP BY] 저자 별 카테고리 별 매출액 집계하기

SQL SUM, MAX, MIN] 동물 수 구하기

-- 코드를 입력하세요
SELECT COUNT(ANIMAL_ID) as count
    FROM ANIMAL_INS I

SQL String, Date] 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 LIMIT 1

-- 코드를 입력하세요
SELECT CONCAT('/home/grep/src/',B.BOARD_ID,'/',F.FILE_ID,F.FILE_NAME,F.FILE_EXT) as FILE_PATH
    FROM USED_GOODS_BOARD B
    INNER JOIN(
        SELECT *
        FROM USED_GOODS_FILE
    ) as F on B.BOARD_ID = F.BOARD_ID
    WHERE B.BOARD_ID = (
        SELECT BOARD_ID
        FROM USED_GOODS_BOARD
        ORDER BY VIEWS DESC
        LIMIT 1
    )
    
    ORDER BY F.FILE_ID DESC