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

은지랑

 

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

프로그래머스

-- 코드를 입력하세요
SELECT a.FLAVOR
from FIRST_HALF a join ICECREAM_INFO b on a.FLAVOR = b.FLAVOR
where a.TOTAL_ORDER > 3000 and b.INGREDIENT_TYPE = 'fruit_based'
order by TOTAL_ORDER desc;

 

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

프로그래머스

 

-- 코드를 입력하세요
SELECT DR_NAME, DR_ID, MCDP_CD, date_format(HIRE_YMD, '%Y-%m-%d') from DOCTOR
where MCDP_CD = 'CS' or MCDP_CD = 'GS'
order by HIRE_YMD desc, DR_NAME ASC

 

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

프로그래머스

-- 코드를 입력하세요
SELECT FACTORY_ID, FACTORY_NAME, ADDRESS from FOOD_FACTORY
where ADDRESS like('강원도%');

 

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

프로그래머스

-- 코드를 입력하세요
SELECT a.TITLE, a.BOARD_ID, b.REPLY_ID, b.WRITER_ID, b.CONTENTS, date_format(b.CREATED_DATE, '%Y-%m-%d')
from USED_GOODS_BOARD a join USED_GOODS_REPLY b on a.BOARD_ID = b.BOARD_ID
where year(a.CREATED_DATE) = 2022 and month(a.CREATED_DATE) = 10
order by b.CREATED_DATE, a.TITLE;

 

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

프로그래머스

-- 코드를 입력하세요
SELECT a.FLAVOR as FLAVOR
from FIRST_HALF a join (select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER  from july group by FLAVOR) b on a.FLAVOR = b.FLAVOR
order by (a.TOTAL_ORDER+b.TOTAL_ORDER) desc
limit 3 offset 0;

 

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

프로그래머스

-- 코드를 입력하세요
SELECT a.REST_ID, a.REST_NAME, a.FOOD_TYPE, a.FAVORITES, a.ADDRESS, b.REVIEW_SCORE as SCORE
from REST_INFO a join (select REST_ID, round(avg(REVIEW_SCORE), 2) as REVIEW_SCORE from REST_REVIEW
group by REST_ID) as b on a.REST_ID = b.REST_ID
where a.ADDRESS like('서울%')
order by SCORE desc, FAVORITES desc;

 

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

프로그래머스

-- 코드를 입력하세요
SELECT date_format(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
from ONLINE_SALE
where year(SALES_DATE) = '2022' and month(SALES_DATE) = '03'
union all
SELECT date_format(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT
from OFFLINE_SALE
where year(SALES_DATE) = '2022' and month(SALES_DATE) = '03'
order by SALES_DATE, PRODUCT_ID, USER_ID;

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

프로그래머스

-- 코드를 작성해주세요
select ID, EMAIL,FIRST_NAME, LAST_NAME
from DEVELOPER_INFOS
where SKILL_1 = 'Python' or SKILL_2 = 'Python' or SKILL_3 = 'Python'
order by ID

 

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

프로그래머스

-- 코드를 작성해주세요
select ID, EMAIL, FIRST_NAME, LAST_NAME
from DEVELOPERS
where SKILL_CODE & (select CODE from SKILLCODES where NAME = 'Python') > 0
or SKILL_CODE & (select CODE from SKILLCODES where NAME = 'C#') > 0
order by id

 

✅ 1️⃣ AND 연산 (&) — 제일 중요
A & B


👉 둘 다 1인 비트만 1

예시
SELECT 400 & 256;

2진수로 보면
400 = 110010000
256 = 100000000
----------------
100000000 = 256


👉 결과 256 (0보다 크면 해당 스킬 있음)

🎯 스킬 포함 여부 확인 공식
SKILL_CODE & 특정코드 > 0


예:

WHERE SKILL_CODE & 256 > 0

✅ 2️⃣ OR 연산 (|)
A | B


👉 하나라도 1이면 1

SELECT 256 | 1024;


→ Python + C# 코드 만들기 가능

✅ 3️⃣ XOR (^)
A ^ B


👉 다르면 1, 같으면 0

코테에선 거의 안 씀.

✅ 4️⃣ NOT (~)
~A


👉 비트 반전

 

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

프로그래머스

-- 코드를 작성해주세요
select count(*) as FISH_COUNT
from FISH_INFO
where LENGTH is null

 

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

프로그래머스

-- 코드를 작성해주세요

select ID, LENGTH
from FISH_INFO
order by LENGTH desc, id
limit 10 offset 0;

 

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

프로그래머스

-- 코드를 작성해주세요
select count(*) as FISH_COUNT
from FISH_INFO a join FISH_NAME_INFO b on a.FISH_TYPE = b.FISH_TYPE
where b.FISH_NAME = 'BASS' or b.FISH_NAME = 'SNAPPER'

 

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

프로그래머스

-- 코드를 작성해주세요

select a.id as id, count(b.id) as CHILD_COUNT
from ECOLI_DATA a left join ECOLI_DATA b on a.ID = b.PARENT_ID
group by a.id
order by a.id

 

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

프로그래머스

-- 코드를 작성해주세요
select ID, 
(case
    when SIZE_OF_COLONY <= 100 then 'LOW'
    when SIZE_OF_COLONY > 100 and SIZE_OF_COLONY <= 1000 then 'MEDIUM'
    else 'HIGH'
 end 
) as SIZE
from ECOLI_DATA

 

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

프로그래머스

-- 코드를 작성해주세요
select count(*) as COUNT
from ECOLI_DATA
where (GENOTYPE & 2 = 0) and (GENOTYPE & 1 > 0 or GENOTYPE & 4 > 0)

 

3번 형질은 4

 

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

프로그래머스

-- 코드를 작성해주세요
select b.ID, b.GENOTYPE, a.GENOTYPE as PARENT_GENOTYPE
from ECOLI_DATA a left join ECOLI_DATA b on a.ID = b.PARENT_ID
where b.GENOTYPE & a.GENOTYPE = a.GENOTYPE
order by b.id

 

https://school.programmers.co.kr/learn/courses/30/lessons/301649?language=mysql

프로그래머스

 

-- 코드를 작성해주세요

select ID,
(case
    when ECOLI_DATA_COPY.a/total <= 0.25 then 'CRITICAL'
    when ECOLI_DATA_COPY.a/total <= 0.5 then 'HIGH'
    when ECOLI_DATA_COPY.a/total <= 0.75 then 'MEDIUM'
    else 'LOW'
end) as COLONY_NAME
from (select ECOLI_DATA.ID, rank() over(order by SIZE_OF_COLONY desc) as a, count(*) over() as total
from ECOLI_DATA) as ECOLI_DATA_COPY
order by id

-- 코드를 작성해주세요

select ID, 
(case
    when ntile(4) over(order by SIZE_OF_COLONY) = 1 then 'LOW'
    when ntile(4) over(order by SIZE_OF_COLONY) = 2 then 'MEDIUM'
    when ntile(4) over(order by SIZE_OF_COLONY) = 3 then 'HIGH'
    when ntile(4) over(order by SIZE_OF_COLONY) = 4 then 'CRITICAL'
end) as COLONY_NAME
from ECOLI_DATA
order by id

 

두 가지로 풀어봤다

 

윈도우 함수

over () 안에 정렬 기준이나 파티션 기준을 넣어서 그룹 바이 없이 집계함수를 쓸 수 있게 해줌

rank(), dense_rank(), row_number() 등등,,, 

 

ntile은 넣은 숫자만큼 그룹을 나눈다 

 

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

프로그래머스

-- 코드를 작성해주세요
select c.ID
from ECOLI_DATA a join ECOLI_DATA b on a.ID = b.PARENT_ID
join ECOLI_DATA c on b.ID = c.PARENT_ID
where a.PARENT_ID is null
order by c.id

 

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

프로그래머스

with recursive generation as(
    select ID, PARENT_ID, 1 as gen
    from ECOLI_DATA
    where PARENT_ID is null
    union all
    select e.id, e.parent_id, g.gen+1
    from ECOLI_DATA e join generation g
    on g.id = e.parent_id
)

select count(*) as COUNT, gen as GENERATION
from generation g left join ECOLI_DATA a on g.id = a.PARENT_ID
where a.id is null
group by gen
order by gen;

 

WITH RECURSIVE 이름 AS (
    
    -- ① 시작점 (anchor)
    SELECT ...

    UNION ALL

    -- ② 반복부분 (recursive part)
    SELECT ...
    FROM 테이블
    JOIN 이름 ON ...
)

-- ③ 최종 조회
SELECT * FROM 이름;

 

시작점은 루트 노드

 

반복 부분에서 이미 구해진 결과를 다시 참조함

union all로 합치기 때문에 컬럼 수가 일치해야함

 

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    
    UNION ALL
    
    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT * FROM numbers;

 

이렇게 사용해서 1~10까지 만들기도 가능

 

핵심은 만든 테이블을 다시 참고하는 것