sugenius

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<JOIN>

Q. 상품 별 오프라인 매출 구하기 | Level 2

select p.product_code, (sum(os.sales_amount) * p.price) as sales
from offline_sale os 
left join product p on os.product_id = p.product_id
group by os.product_id
order by sales desc, p.product_code asc

Q. 조건에 맞는 도서와 저자 리스트 출력하기 | Level 2

select b.book_id , a.author_name, DATE_FORMAT(b.published_date, "%Y-%m-%d") as published_date
from author a 
inner join book b on a.author_id = b.author_id and b.category = '경제'
order by b.published_date asc
-- DATE_FORMAT !!

Q. 없어진 기록 찾기 | Level 3

/* 오답!
select ao.animal_id, ao.name
from animal_outs ao 
inner join animal_ins ai on ao.animal_id != ai.animal_id
order by ao.animal_id;
*/

select ao.animal_id, ao.name
from animal_outs ao 
left join animal_ins ai on ao.animal_id = ai.animal_id
where ai.animal_id is null
order by ao.animal_id;
-- !! a left join b on a.key = b.key where b.key is null 조건 !

Q. 있었는데요 없었습니다 | Level 3

select ai.animal_id, ai.name
from animal_ins ai
inner join animal_outs ao on ai.animal_id = ao.animal_id
where ao.datetime < ai.datetime
order by ai.datetime

Q. 오랜 기간 보호한 동물(1) | Level 3

select ai.name, ai.datetime
from animal_ins ai 
left join animal_outs ao on ai.animal_id = ao.animal_id
where ao.animal_id is null
order by ai.datetime asc 
limit 3

Q. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | Level 4 >> 하는 즁 ! 

/* 오답
select 
    A.car_id
    , A.car_type
    , round(daily_fee*30*(1-discount_rate/100),0) as fee
from (
    select *
    from car_rental_company_car crcc 
    where crcc.car_type in ('세단', 'SUV')
    and crcc.car_id not in (
        select crcrh.car_id -- 대여 불가 (기간 내 대여중)
        from car_rental_company_rental_history crcrh
        where crcrh.start_date between '2022-11-01' and '2022-12-01'
        or crcrh.end_date between '2022-11-01' and '2022-12-01'
        )
    ) A
left join (
    select * 
    from car_rental_company_discount_plan crcdp
    where crcdp.duration_type like "30%"
    order by discount_rate desc 
    ) B
on A.car_type = B.car_type
-- fee 조건 ... 
order by fee desc, car_type asc, car_id desc

*/
-- 대여 start_date, end_date 조건 주의 !
-- CAST(REPLACE(DISCOUNT_RATE, '%', '') AS DECIMAL) 안해도 되네 ? 

-- (1-DISCOUNT_RATE/100) 계산식

Q. 5월 식품들의 총매출 조회하기 | Level 4

Q. 주문량이 많은 아이스크림들 조회하기 | Level 4

Q. 그룹별 조건에 맞는 식당 목록 출력하기 | Level 4

Q. 보호소에서 중성화한 동물 | Level 4

Q. FrontEnd 개발자 찾기 | Level 4

Q. 상품을 구매한 회원 비율 구하기 | Level 5

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<IS NULL>
Q.경기도에 위치한 식품창고 목록 출력하기

SELECT
    WAREHOUSE_ID
    , WAREHOUSE_NAME
    , ADDRESS
    , IFNULL(FREEZER_YN,'N') as FREEZER_YN
FROM FOOD_WAREHOUSE 
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID

Q.이름이 없는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS 
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

Q.이름이 있는 동물의 아이디

SELECT ANIMAL_ID 
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

Q.NULL 처리하기

SELECT 
    ANIMAL_TYPE
    , IFNULL(NAME,"No name") as NAME
    , SEX_UPON_INTAKE
FROM ANIMAL_INS

Q.나이 정보가 없는 회원 수 구하기

SELECT COUNT(*)
FROM USER_INFO 
WHERE AGE IS NULL

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<GROUP BY>
Q.즐겨찾기가 가장 많은 식당 정보 출력하기

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

Q.저자 별 카테고리 별 매출액 집계하기

SELECT 
    au.AUTHOR_ID
    , au.AUTHOR_NAME
    , bo.CATEGORY
    , SUM(SALES*PRICE) as TOTAL_SALES
FROM BOOK_SALES as bs
INNER JOIN BOOK as bo ON bs.BOOK_ID = bo.BOOK_ID 
INNER JOIN AUTHOR as au ON bo.AUTHOR_ID = au.AUTHOR_ID
WHERE YEAR(bs.SALES_DATE)=2022
AND MONTH(bs.SALES_DATE)=1
GROUP BY au.AUTHOR_NAME, bo.CATEGORY 
ORDER BY au.AUTHOR_ID , bo.CATEGORY DESC

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

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

Q.조건에 맞는 사용자와 총 거래금액 조회하기

/*
SELECT WRITER_ID
FROM USED_GOODS_BOARD
WHERE STATUS='DONE'
GROUP BY WRITER_ID
HAVING SUM(PRICE)>=700000
*/
SELECT ugb.WRITER_ID, ugu.NICKNAME, SUM(ugb.PRICE) as TOTAL_SALES
FROM USED_GOODS_BOARD as ugb
JOIN USED_GOODS_USER  as ugu ON ugb.WRITER_ID = ugu.USER_ID
WHERE STATUS='DONE'
GROUP BY ugb.WRITER_ID
HAVING SUM(ugb.PRICE)>=700000
ORDER BY TOTAL_SALES

Q.성분으로 구분한 아이스크림 총 주문량

SELECT 
     ii.INGREDIENT_TYPE as INGREDIENT_TYPE
    ,SUM(TOTAL_ORDER) as TOTAL_ORDER 
FROM FIRST_HALF as fh
LEFT JOIN ICECREAM_INFO as ii ON fh.FLAVOR=ii.FLAVOR
GROUP BY ii.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER

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

SELECT
    b.CATEGORY as CATEGORY
    ,SUM(SALES) as TOTAL_SALES
FROM BOOK_SALES as bs
LEFT JOIN BOOK as b ON bs.BOOK_ID = b.BOOK_ID
WHERE YEAR(bs.SALES_DATE)=2022 and MONTH(bs.SALES_DATE)=1
GROUP BY b.CATEGORY
ORDER BY CATEGORY

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

SELECT 
    MCDP_CD as 진료과코드
    ,COUNT(*) as 5월예약건수
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD,"%Y-%m-%d") LIKE "2022-05-%"
GROUP BY MCDP_CD
ORDER BY 5월예약건수,진료과코드

Q.자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT 
    CAR_ID
    ,MAX(
    #CASE WHEN ('2022-10-16' between DATE_FORMAT(START_DATE,"%Y-%m-%d") and DATE_FORMAT(END_DATE,"%Y-%m-%d") )
    CASE WHEN ('2022-10-16' between START_DATE and END_DATE )
    THEN '대여중'
    ELSE '대여 가능' END
    ) as AVAILABILITY
    #,MAX(if('2022-10-16' between START_DATE and END_DATE, '대여중','대여 가능'))
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID #CAR_ID별로 기록하기 위함
ORDER BY CAR_ID DESC
/*
MAX()함수는 GROUP BY와 함께 사용해야함
GROUP BY를 통해 CAR_ID 기준으로 묶어주고, 
위 조건을 MAX() 통해 반환하면
, 같은 CAR_ID 중에 가장 큰 값이 실행 결과로 나타난다.
*/

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

SELECT MONTH(START_DATE) as MONTH, CAR_ID, COUNT(HISTORY_ID) as RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY  
WHERE CAR_ID IN (
    SELECT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY   
    WHERE MONTH(START_DATE) between 8 and 10
    GROUP BY CAR_ID 
    HAVING COUNT(HISTORY_ID)>=5
    )
AND MONTH(START_DATE) between 8 and 10
GROUP BY MONTH, CAR_ID
HAVING RECORDS>0
ORDER BY MONTH,CAR_ID DESC

Q.식품분류별 가장 비싼 식품의 정보 조회하기

/* 틀린 답 
SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
GROUP BY CATEGORY 
HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY PRICE DESC
*/
SELECT CATEGORY, PRICE AS MAX_PRICE ,PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY,PRICE) IN (
    SELECT CATEGORY,MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY 
    HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
    )
ORDER BY MAX_PRICE DESC

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

SELECT ANIMAL_TYPE,count(*)
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE 
ORDER BY ANIMAL_TYPE

Q.동명 동물 수 찾기

SELECT NAME, COUNT(*)
FROM ANIMAL_INS 
GROUP BY NAME 
HAVING COUNT(NAME)>=2
ORDER BY NAME

Q.년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(os.SALES_DATE) as YEAR
    ,MONTH(os.SALES_DATE) as MONTH
    ,ui.GENDER as GENDER
    ,COUNT(distinct ui.USER_ID) as USERS 
FROM ONLINE_SALE as os 
INNER JOIN USER_INFO as ui 
ON os.USER_ID = ui.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR , MONTH, GENDER
ORDER BY YEAR , MONTH, GENDER

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

SELECT HOUR(DATETIME) AS HOUR , COUNT(*) AS COUNT
FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) between 9 and 19 
GROUP BY HOUR
ORDER BY HOUR

Q.입양 시각 구하기(2)#***

/*
틀린 답
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME) 
ORDER BY HOUR
*/

/*
SET @변수명 = 대입값; 
SELECT @변수명 := 대입값; # :=는 할당 연산자
*/

SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR,
    (SELECT COUNT(HOUR(DATETIME)) 
    FROM ANIMAL_OUTS 
    WHERE HOUR(DATETIME)=@HOUR) AS COUNT 
    FROM ANIMAL_OUTS
WHERE @HOUR < 23;

Q.가격대 별 상품 개수 구하기 #***

SELECT 
    TRUNCATE(PRICE,-4) as PRICE_GROUP #4자릿수를 버림. 
    , COUNT(*) as PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP
/*
TRUNCATE(PRICE,-4)  #4자릿수를 버림. 
TRUNC(수, [, 자릿수])
수를 지정된 소수점 자리까지 버림하여 반환해주는 함수이다. 
자릿수를 명시하지 않았을 경우 기본값은 0 . 즉, 정수로 반환
*/