[TIL]241129_코드카타

 

24. 카테고리 별 상품 개수 구하기

SELECT 
    SUBSTR(product_code,1,2) AS 'CATEGORY',
    COUNT(product_id)
FROM PRODUCT
GROUP BY SUBSTR(product_code,1,2)
ORDER BY 1;

 

25. 고양이와 개는 몇마리 있을까

SELECT
    ANIMAL_TYPE,
    COUNT(*) AS 'count'
FROM ANIMAL_INS
GROUP BY 1
ORDER BY 1;

 

 

26. 입양 시각 구하기(1)

--에러 : DATETIME 결과값이 '9'가 아니라 '09'로 표시되어서?
SELECT 
    DATE_FORMAT(DATETIME,'%H') AS 'HOUR',
    COUNT(DATETIME) AS 'COUNT'
FROM ANIMAL_OUTS
GROUP BY DATE_FORMAT(DATETIME,'%H')
ORDER BY DATE_FORMAT(DATETIME,'%H')
LIMIT 2,11;

--정답 (HOUR함수 사용)
SELECT 
    HOUR(DATETIME) AS 'HOUR',
    COUNT(DATETIME) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY 1
ORDER BY 1;

 

 

27. 진료과별 총 예약 횟수 출력하기

SELECT
    MCDP_CD '진료과 코드',
    COUNT(MCDP_CD)'5월예약건수'
FROM APPOINTMENT
WHERE MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY COUNT(MCDP_CD), MCDP_CD;

 

 

28. 12세 이하인 여자 환자 목록 출력하기

SELECT 
    PT_NAME,
    PT_NO,
    GEND_CD,
    AGE,
    IF(TLNO IS NULL,'NONE',TLNO)'TLNO'
FROM PATIENT
WHERE AGE <= 12
AND GEND_CD = 'w'
ORDER BY AGE DESC, PT_NAME;

--IFNULL도 사용 가능
SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC;

 

 

29. 인기있는 아이스크림

SELECT
    FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;

 

30.자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

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