[SQL 고득점 Kit] SQL 코테 준비를 해봅시다 - 3일차

나혼자

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

프로그래머스

-- 코드를 입력하세요
SELECT *
from FOOD_PRODUCT
where price = (select max(price) from FOOD_PRODUCT)
;

 

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

코딩테스트 연습 - 가장 비싼 상품 구하기

-- 코드를 입력하세요
SELECT max(price) as MAX_PRICE
from PRODUCT;

 

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

프로그래머스

-- 코드를 입력하세요
SELECT max(DATETIME)
from ANIMAL_INS;

 

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

프로그래머스

-- 코드를 입력하세요
SELECT  min(DATETIME)
from ANIMAL_INS;

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

프로그래머스

-- 코드를 입력하세요
SELECT count(*)
from animal_ins

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

프로그래머스

-- 코드를 입력하세요
SELECT count(NAME) as count
from ( select * from ANIMAL_INS
group by NAME) a

 

distinct 사용해서 풀이 가능

 

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

프로그래머스

-- 코드를 작성해주세요
select sum(price) as TOTAL_PRICE
from ITEM_INFO
where RARITY = 'LEGEND'

 

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

프로그래머스

-- 코드를 작성해주세요
select ID, FISH_NAME, LENGTH
from 
(
select a.ID, a.FISH_TYPE, a.LENGTH, a.TIME, b.FISH_NAME, rank() over(partition by a.FISH_TYPE order by a.LENGTH desc) as r
    from FISH_INFO a left join FISH_NAME_INFO b  on a.FISH_TYPE = b.FISH_TYPE
) ta
where r = 1
order by id;

 

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

프로그래머스

-- 코드를 작성해주세요
select concat(format(max(length),2),'cm') as MAX_LENGTH
from FISH_INFO

 

문자열 이어 붙일 때는 concat

 

소수점 자릿수 정할 때는 format (?, 2)

해당 숫자까지 소수점 자릿수 고정한다.

 

 

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

프로그래머스

select year, (max(SIZE_OF_COLONY) over(partition by year) - SIZE_OF_COLONY) as YEAR_DEV, id
from 
(
    select *, year(DIFFERENTIATION_DATE) as year
    from ECOLI_DATA
) as a
order by YEAR, YEAR_DEV

 

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

프로그래머스

select *
from(
SELECT a.CAR_ID, a.CAR_TYPE, round(a.daily_fee * (1 - (c.discount_rate * 0.01)), 0) * 30 as FEE
from CAR_RENTAL_COMPANY_CAR a join CAR_RENTAL_COMPANY_RENTAL_HISTORY b
on a.CAR_ID = b.CAR_ID
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN c
on a.CAR_TYPE = c.CAR_TYPE
where (a.CAR_TYPE = 'SUV' or a.CAR_TYPE = '세단')
and c.duration_type = '30일 이상' and
a.car_id not in(
    select a.car_id
    from CAR_RENTAL_COMPANY_CAR a join CAR_RENTAL_COMPANY_RENTAL_HISTORY b
    on a.CAR_ID = b.CAR_ID
    where (start_date <= '2022-11-30' and end_date >= '2022-11-01')
)
    ) k
where FEE >= 500000 and FEE < 2000000
group by car_id
order by FEE desc, CAR_TYPE, car_id desc
;

 

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

프로그래머스

-- 코드를 입력하세요
SELECT a.PRODUCT_ID, a.PRODUCT_NAME, sum(PRICE * AMOUNT) as TOTAL_SALES
from FOOD_PRODUCT a left join FOOD_ORDER b on a.PRODUCT_ID = b.PRODUCT_ID
where year(PRODUCE_DATE) = '2022' and month(PRODUCE_DATE) = '05'
group by a.PRODUCT_ID
order by TOTAL_SALES desc, PRODUCT_ID

 

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

프로그래머스

-- 코드를 입력하세요
SELECT a.book_id, b.author_name, date_format(published_date, '%Y-%m-%d') as PUBLISHED_DATE
from BOOK a join AUTHOR b on a.AUTHOR_ID = b.AUTHOR_ID
where category = '경제'
order by published_date

 

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

프로그래머스

select member_name, REVIEW_TEXT, date_format(REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
from
(SELECT *, max(cnt) over() as maxCnt
from 
(select a.member_name,b.*, (count(*) over(partition by a.MEMBER_NAME)) as cnt
 from
 MEMBER_PROFILE a join REST_REVIEW b on a.MEMBER_ID = b.MEMBER_ID
) as k) as l
where cnt = maxCnt
order by REVIEW_DATE, REVIEW_TEXT

 

집계함수 max를 * 옆에 그냥 써버리면 한 행으로 압축되는데

over() 이랑 같이 써서 범위 지정 안 해버리면 모든 행 옆에 붙게 됨

 

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

프로그래머스

-- 코드를 입력하세요
SELECT b.ANIMAL_ID, b.NAME
from ANIMAL_INS a right join ANIMAL_OUTS b on a.ANIMAL_ID = b.ANIMAL_ID
where a.ANIMAL_ID is null

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

프로그래머스

-- 코드를 입력하세요
SELECT a.ANIMAL_ID, a.NAME
from ANIMAL_INS a join ANIMAL_OUTS b on a.ANIMAL_ID = b.ANIMAL_ID
where a.DATETIME > b.DATETIME
order by a.DATETIME

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

프로그래머스

-- 코드를 입력하세요
SELECT a.name, a.datetime
from ANIMAL_INS a left join ANIMAL_OUTS b on a.ANIMAL_ID = b.ANIMAL_ID
where b.ANIMAL_ID is null
order by a.datetime
limit 3 offset 0

https://school.programmers.co.kr/learn/courses/30/lessons/59045#fn1

프로그래머스

-- 코드를 입력하세요
SELECT b.ANIMAL_ID, b.ANIMAL_TYPE, b.name
from ANIMAL_INS a join ANIMAL_OUTS b on a.ANIMAL_ID = b.ANIMAL_ID
where a.SEX_UPON_INTAKE like('Intact%') and (b.SEX_UPON_OUTCOME like('Neutered%') or b.SEX_UPON_OUTCOME like('Spayed%'))

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

프로그래머스

-- 코드를 입력하세요
SELECT PRODUCT_CODE, sum(total) as SALES
from 
(select a.PRODUCT_CODE, b.*, (a.price * b.sales_amount) as total
    from PRODUCT a join OFFLINE_SALE b on a.PRODUCT_ID = b.PRODUCT_ID
) as k
group by product_id
order by SALES desc, PRODUCT_CODE

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

-- 코드를 작성해주세요
select id, email, first_name, last_name
from DEVELOPERS
where id in (select id
from developers a join SKILLCODES b
where (a.SKILL_CODE & b.CODE) > 0
and CATEGORY = 'Front End')
order by id

 

 

distinct 써서도 풀이 가능