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 . 즉, 정수로 반환
*/

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

<SUM, MAX, MIN>
Q.가격이 제일 비싼 식품의 정보 출력하기

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

Q.가장 비싼 상품 구하기

SELECT MAX(PRICE) as MAX_PRICE
FROM PRODUCT

Q.최댓값 구하기

SELECT MAX(DATETIME) as 시간
FROM ANIMAL_INS 
/*
SELECT DATETIME as 시간
FROM ANIMAL_INS 
ORDER BY DATETIME DESC
LIMIT 1
*/

Q.최솟값 구하기

SELECT MIN(DATETIME) AS 시간
FROM ANIMAL_INS

Q.동물 수 구하기

SELECT count(*) as count
FROM ANIMAL_INS

Q.중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS 
WHERE NAME is not null

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

<SELECT>
Q.3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID,MEMBER_NAME,GENDER,DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d")
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3
AND GENDER='W'
AND TLNO is not null 
ORDER BY MEMBER_ID;
/*
DATE_FORMAT(DATE_OF_BIRTH, 원하는 형식) 
DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") AS BIRTH, #1992-03-16
DATE_FORMAT(DATE_OF_BIRTH, "%Y") AS YEAR, 
DATE_FORMAT(DATE_OF_BIRTH, "%m") AS MONTH,
DATE_FORMAT(DATE_OF_BIRTH, "%d") AS DAY
*/

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

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
/*
IFNULL(Column명, "Null일 경우 대체 값")
*/

Q.평균 일일 대여 요금 구하기

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

Q.인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF 
ORDER BY TOTAL_ORDER DESC,SHIPMENT_ID ASC

Q.흉부외과 또는 일반외과 의사 목록 출력하기

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,"%Y-%m-%d") as HIRE_YMD
FROM DOCTOR 
WHERE MCDP_CD IN ('CS','GS')
ORDER BY HIRE_YMD DESC, DR_NAME

Q.조건에 맞는 도서 리스트 출력하기

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE,"%Y-%m-%d") as PUBLISHED_DATE 
FROM BOOK
WHERE YEAR(PUBLISHED_DATE)=2021
AND CATEGORY='인문'

Q.조건에 부합하는 중고거래 댓글 조회하기

SELECT
    ugb.TITLE
    , ugb.BOARD_ID
    , ugr.REPLY_ID
    , ugr.WRITER_ID
    , ugr.CONTENTS
    , DATE_FORMAT(ugr.CREATED_DATE,"%Y-%m-%d") as CREATED_DATE
FROM USED_GOODS_BOARD as ugb
INNER JOIN USED_GOODS_REPLY as ugr
ON ugb.BOARD_ID = ugr.BOARD_ID
WHERE YEAR(ugb.CREATED_DATE) = 2022 
AND MONTH(ugb.CREATED_DATE) = 10 
ORDER BY ugr.CREATED_DATE, ugb.TITLE

Q.과일로 만든 아이스크림 고르기

SELECT ii.FLAVOR 
FROM ICECREAM_INFO as ii
INNER JOIN FIRST_HALF as fh 
ON ii.FLAVOR = fh.FLAVOR
WHERE fh.TOTAL_ORDER>3000
and ii.INGREDIENT_TYPE = 'fruit_based'
ORDER BY fh.TOTAL_ORDER DESC

Q.서울에 위치한 식당 목록 출력하기

SELECT 
    ri.REST_ID
    ,ri.REST_NAME
    ,ri.FOOD_TYPE
    ,ri.FAVORITES
    ,ri.ADDRESS
    ,ROUND(AVG(rr.REVIEW_SCORE),2) as SCORE
FROM REST_INFO  as ri
INNER JOIN REST_REVIEW as rr
ON ri.REST_ID = rr.REST_ID
WHERE ri.ADDRESS LIKE '서울%'
GROUP BY ri.REST_ID #***
ORDER BY SCORE DESC, ri.FAVORITES DESC

Q.강원도에 위치한 생산공장 목록 출력하기

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY  
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID

Q.재구매가 일어난 상품과 회원 리스트 구하기 

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

Q.모든 레코드 조회하기

SELECT ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS  
ORDER BY ANIMAL_ID

Q.오프라인/온라인 판매 데이터 통합하기 

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as SALES_DATE
    ,PRODUCT_ID
    ,USER_ID
    ,SALES_AMOUNT
FROM ONLINE_SALE 
WHERE MONTH(SALES_DATE)=3
#WHERE SALES_DATE LIKE '2022-03%'
#WHERE DATE_FORMAT(ons.SALES_DATE,"%Y-%m")='2022-03'
UNION
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as SALES_DATE
    ,PRODUCT_ID
    ,NULL as USER_ID
    ,SALES_AMOUNT
FROM OFFLINE_SALE 
WHERE MONTH(SALES_DATE)=3
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID
/*
UNION (DISTINCT) : 쿼리의 결과를 합친다. 중복된 ROW는 제거 
<-> UNION ALL
*/

Q.역순 정렬하기

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

Q.아픈 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS  
WHERE INTAKE_CONDITION='Sick'
ORDER BY ANIMAL_ID

Q.어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS  
WHERE INTAKE_CONDITION!='Aged'
ORDER BY ANIMAL_ID

Q.동물의 아이디와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

Q.여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS  
ORDER BY NAME , DATETIME DESC

Q.상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

Q.조건에 맞는 회원수 구하기 

SELECT count(*) #count(USER_ID) as USERS
FROM USER_INFO
WHERE AGE between 20 and 29 #AGE >= 20 and AGE<=29
and YEAR(JOINED)=2021