SELECT distinct r.CAR_ID
from CAR_RENTAL_COMPANY_CAR r
inner join CAR_RENTAL_COMPANY_RENTAL_HISTORY h
on r.CAR_ID = h.CAR_ID
where r.CAR_TYPE = '세단'
and month(h.START_DATE) = 10
order by 1 desc
식품분류별 가장 비싼 식품의 정보 조회하기
서브쿼리에 별칭을 무조건 지어줘야 함! -> Every derived table must have its own alias
select a.CATEGORY,
a.PRICE MAX_PRICE,
a.PRODUCT_NAME
from
(
SELECT CATEGORY,
PRODUCT_NAME,
rank() over(partition by CATEGORY order by PRICE desc) as ranking,
PRICE
from FOOD_PRODUCT
) a
where a.ranking = 1
and a.CATEGORY in ('과자', '국', '김치', '식용유')
order by 2 desc
5월 식품들의 총매출 조회하기
SELECT p.PRODUCT_ID,
p.PRODUCT_NAME,
sum(p.PRICE * o.AMOUNT) TOTAL_SALES
from FOOD_PRODUCT p
inner join FOOD_ORDER o on p.PRODUCT_ID = o.PRODUCT_ID
where o.PRODUCE_DATE >= '2022-05-01' and o.PRODUCE_DATE < '2022-06-01'
group by 1, 2
order by 3 desc, 1
없어진 기록 찾기
left join을 할 때 on을 걸어주는 오른쪽에 있는 테이블에 존재하지 않아도 되는 값임
exists : 메인 쿼리에 먼저 접근하여 row를 하나 가져오고 exists의 서브쿼리를 실행시켜 결과가 존재하는지를 판단
서브쿼리의 결과값에서 필터링해줌(in 과 같은 역할)
SELECT o.ANIMAL_ID, o.NAME
FROM ANIMAL_OUTS o
LEFT JOIN ANIMAL_INS i
ON o.ANIMAL_ID = i.ANIMAL_ID
where i.ANIMAL_ID is null
order by 1
SELECT ao.ANIMAL_ID, ao.NAME
FROM ANIMAL_OUTS ao
WHERE NOT EXISTS (
SELECT i.ANIMAL_ID
FROM ANIMAL_INS i
WHERE i.ANIMAL_ID = ao.ANIMAL_ID
)
order by ao.ANIMAL_ID
SELECT hour(DATETIME) as HOUR, count(*) COUNT
from ANIMAL_OUTS
where 9 <= hour(DATETIME) and hour(DATETIME) < 20
group by 1
order by HOUR
진료과별 총 예약 횟수 출력하기
SELECT MCDP_CD '진료과코드', count(*) '5월예약건수'
from APPOINTMENT
where APNT_YMD like('2022-05%')
group by MCDP_CD
order by count(*), MCDP_CD
자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
SELECT CAR_TYPE,
count(*) CARS
from CAR_RENTAL_COMPANY_CAR
where OPTIONS like ('%통풍시트%') or
OPTIONS like('%열선시트%') or
OPTIONS like('%가죽시트%')
group by CAR_TYPE
order by 1
오랜 기간 보호한 동물(2)
select i.ANIMAL_ID, i.NAME
from ANIMAL_INS i
inner join ANIMAL_OUTS o
on i.ANIMAL_ID = o.ANIMAL_ID
order by o.DATETIME - i.DATETIME desc limit 2
루시와 엘라 찾기
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
from ANIMAL_INS
where NAME in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
order by 1
select a.ID,
case WHEN a.percent <= 0.25 THEN 'CRITICAL'
WHEN a.percent <= 0.5 THEN 'HIGH'
WHEN a.percent <= 0.75 THEN 'MEDIUM'
else 'LOW' end COLONY_NAME
from
(select ID,
percent_rank() over(order by SIZE_OF_COLONY desc) percent
from ECOLI_DATA)
a
order by 1
특정 세대의 대장균 찾기 (4LV 💁😂)
자식테이블이랑 부모테이블 조인을 할 때 inner조인을 해서 풀었어야 했음
1세대는 무조건 부모가 null이기 때문에 이 조건을 추가해서 했어야 했음
첫번째 풀이
select c.aId as ID
from
(select a.ID aId,
a.PARENT_ID aPid,
b.ID bId,
b.PARENT_ID bPid
from ECOLI_DATA a
left join ECOLI_DATA b on a.PARENT_ID = b.ID
)
c left join ECOLI_DATA d on c.bPid = d.ID
where aPid is null
order by 1
두번째 풀이
select c.aId as ID
from
(select a.ID aId,
a.PARENT_ID aPid,
b.ID bId,
b.PARENT_ID bPid
from ECOLI_DATA a
inner join ECOLI_DATA b on a.PARENT_ID = b.ID
)
c inner join ECOLI_DATA d on c.bPid = d.ID
where d.PARENT_ID is null
order by ID asc
예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미합니다.
select ID, EMAIL, FIRST_NAME, LAST_NAME
from DEVELOPERS
where (0 < SKILL_CODE & (select CODE from SKILLCODES where name = 'Python'))
or (0 < SKILL_CODE & (select CODE from SKILLCODES where name = 'C#'))
order by ID;
특정 물고기를 잡은 총 수 구하기
select count(*) FISH_COUNT
from
(select f.FISH_TYPE, n.FISH_NAME
from FISH_INFO f
left join FISH_NAME_INFO n on n.FISH_TYPE = f.FISH_TYPE) a
where FISH_NAME = 'BASS' or FISH_NAME = 'SNAPPER'
대장균들의 자식의 수 구하기
부모컬럼에 자식의 정보까지 합쳐서 결과를 내서 join을 수행하면 이렇게 부모가 자식의 갯수만큼 생성이 된다
select e.ID, coalesce(count(d.ID), 0) CHILD_COUNT
from ECOLI_DATA e
left join ECOLI_DATA d on e.ID = d.PARENT_ID #부모컬럼에 자식의 정보를 붙여서 테이블 생성
group by e.ID
order by 1
대장균의 크기에 따라 분류하기 1
select ID,
case when SIZE_OF_COLONY <= 100 then 'LOW'
when SIZE_OF_COLONY > 100 and SIZE_OF_COLONY <= 1000 then 'MEDIUM'
when SIZE_OF_COLONY > 1000 then 'HIGH' end SIZE
from ECOLI_DATA
부모의 형질을 모두 가지는 대장균 찾기
부모의 형질을 모두 보유한 대장균
-> 이므로 조건은 p.GENOTYPE = (c.GENOTYPE & p.GENOTYPE) 이렇게 가야 함
-> 자식의 형질이 부모의 형질보다 크거나 같아야 하기 때문에 저렇게 식을 줌
select c.ID, c.GENOTYPE, p.GENOTYPE PARENT_GENOTYPE
from ECOLI_DATA c #자식
left join ECOLI_DATA p on c.PARENT_ID = p.ID
where p.GENOTYPE = (c.GENOTYPE & p.GENOTYPE)
order by 1
UNION ALL은 두 개 이상의 SELECT 결과를 합치면서(행끼리 합침) 중복된 데이터도 포함
중복 데이터를 제거하지 않기 때문에 UNION보다 성능이 더 빠름
명시적 NULL 표기
데이터 표현을 일관되게 유지하기 위해
의도적으로 데이터가 없음을 표현
오프라인/온라인 판매 데이터 통합하기 (LV 4 💁😲)
처음에 결합은 당연히 join을 사용하는 것이라고 생각해서 풀었다가 생각해보니, join은 컬럼끼리 결합하는 것이지, 로우끼리 결합하는 것이 아니라는 생각이 들었다.. 그래서 한번 풀어보고 당연히 오답이 나와서 찾아본 결과 로우끼리의 결합은 uinon을 사용하여 한다는 것을 알게 되었다. 또 컬럼에 null을 아예 박아서 표기할 수 있다는 사실도 알게 되었다.... 역시 문제를 풀어보면서 여러 기능(?)들을 알게 되는 것 같다...(문풀의 중요성)
1차 풀이(당연히 오답인)
select date_format(o.SALES_DATE, '%Y-%m-%d') as SALES_DATE, o.PRODUCT_ID, o.USER_ID, o.SALES_AMOUNT
from ONLINE_SALE o
left join
(SELECT SALES_DATE, PRODUCT_ID, SALES_AMOUNT
from OFFLINE_SALE
where SALES_DATE between '2022-03-01' and '2022-03-31'
) a on o.PRODUCT_ID = a.PRODUCT_ID
where o.SALES_DATE between '2022-03-01' and '2022-03-31'
order by 1 asc, 2 asc, 3 asc
2차 풀이
SELECT
SALES_DATE,
PRODUCT_ID,
USER_ID,
SALES_AMOUNT
FROM (
SELECT
DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID,
USER_ID,
SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
UNION ALL
SELECT
DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID,
NULL AS USER_ID,
SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
) AS SALES_DATA
ORDER BY
SALES_DATE ASC,
PRODUCT_ID ASC,
USER_ID ASC;
업그레이드 된 아이템 구하기
처음에는 조건을 잘 못보고 희귀도가 'RARE'인걸 빼먹고 쿼리를 짰다 😅
하지만 생각을 해보니 저 조건을 어떻게 적용해야 할까 고민하다가 결국 join을 2번이나 하게해서 결과를 어떻게든 만들어 냈다..
근데 조인 2번이면 아무리 생각해도 너무 부담이 크도 비효율적으로 쿼리를 짠 것 같아서 냅다 gpt에게 짜보라고 시켰더니 in 조건 안에 서브쿼리를 짜서 넣어 풀었다 서브쿼리 결과를 그런 식으로 쓸 수 있는지는 처음 알았다!! 완전 유용하게 쓸 듯
join 2번 사용한 답
select c.ITEM_ID, c.ITEM_NAME, c.RARITY
from ITEM_INFO c
inner join
(
select t.ITEM_ID
from
(
select ITEM_ID, ITEM_NAME, RARITY
from ITEM_INFO
where RARITY = 'RARE'
)a inner join ITEM_TREE t
on a.ITEM_ID = t.PARENT_ITEM_ID
)b on c.ITEM_ID = b.ITEM_ID
order by 1 desc
in 조건에 서브쿼리 넣어서 쓴 답
SELECT
i.ITEM_ID,
i.ITEM_NAME,
i.RARITY
FROM ITEM_INFO i
JOIN ITEM_TREE t
ON i.ITEM_ID = t.ITEM_ID
WHERE t.PARENT_ITEM_ID IN (
SELECT ITEM_ID
FROM ITEM_INFO
WHERE RARITY = 'RARE'
)
ORDER BY i.ITEM_ID DESC;
평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림 -> ROUND(123.567, 0) //소수점 1번째 자리를 반올림 해서 0번째까지 만든다
SELECT round(avg(DAILY_FEE), 0) as AVERAGE_FEE
from CAR_RENTAL_COMPANY_CAR
where CAR_TYPE = 'SUV'
재구매가 일어난 상품과 회원 리스트 구하기
select USER_ID, PRODUCT_ID
from
(SELECT user_id, product_id, count(*) c
from ONLINE_SALE
group by 1, 2) a
where a.c > 1
order by USER_ID asc, PRODUCT_ID desc
SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d') DATE_OF_BIRTH
from MEMBER_PROFILE
where GENDER = 'W'
and month(DATE_OF_BIRTH) = 3
and TLNO is not null
order by 1 asc
서울에 위치한 식당 목록 출력하기
(레벨 4짜리다 💁😆)
select r.REST_ID, r.REST_NAME, r.FOOD_TYPE, r.FAVORITES, r.ADDRESS, a.food_avg as SCORE
from REST_INFO r
inner join
(
select i.REST_ID, round(avg(r.REVIEW_SCORE), 2) food_avg
from REST_INFO i
left join REST_REVIEW r on i.REST_ID = r.REST_ID
group by i.REST_ID
) a on r.REST_ID = a.REST_ID
where substring(r.ADDRESS, 1, 2) = '서울'
and a.food_avg is not null
order by SCORE desc, r.FAVORITES desc
1. 각 직원이 속한 부서에서 가장 높은 월급을 받는 직원들만 포함된 결과를 조회하는 SQL 쿼리를 작성해주세요.
select Name, Department, Salary
from
(
SELECT Department,
Name,
Salary,
rank() over(partition by Department order by Salary desc) rn
from Employees e
) a
where rn = 1
2. 직원이 참여한 프로젝트 중 예산이 10,000 이상인 프로젝트만을 조회하는 SQL 쿼리를 작성해주세요.
select e.Name, a.ProjectName, a.Budget
from Employees e
left join
(
select ep.EmployeeID, p.ProjectName, p.Budget
from EmployeeProjects ep
left join Projects p on ep.ProjectID = p.ProjectID
) a on e.EmployeeID = a.EmployeeID
where a.Budget >= 10000
1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.
SELECT c.CustomerName,
sum(total) TotalAmount,
count(OrderId) OrderCount
from Customers c
left join
(
select o.OrderId, o.CustomerID ,p.ProductName, o.Quantity * p.Price total
from Orders o
left join Products p on o.ProductID = p.ProductID
) a
on c.CustomerID = a.CustomerID
group by 1
orders와 products테이블을 조인하여 주문에 따른 금액을 먼저 계산한 후, customers테이블과 조인한 테이블을 한번 더 조인하여 결과를 만듦
2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.
select Category, ProductName Top_Product, TotalSold
from
(
select p.Category,
p.ProductName,
sum(o.Quantity) TotalSold,
rank() over(partition by p.Category order by sum(o.Quantity) desc) rn
from Orders o
left join Products p on o.ProductID = p.ProductID
group by 1, 2
) a
where a.rn = 1
rank()함수 사용에 있어서 약간 모호하게 알고 있는 것 같아서 다시 한 번 정리를 해보았다.
RANK() 사용 위치 문제
RANK()는 윈도우 함수로, 테이블 전체 또는 특정 파티션 내에서 순위를 매기는 역할
이를 올바르게 사용하려면 집계 함수를 사용하는 쿼리를 서브쿼리로 작성하거나, GROUP BY와 함께 사용해야 함
select p.Category,
p.ProductName,
rank() over(partition by p.Category order by sum(o.Quantity) desc) rn
from Orders o
left join Products p on o.ProductID = p.ProductID
이렇게 사용했다가 에러가 나서 수정을 해보았다.
#group by 함수로 그룹화할 범위를 명확히 지정해주는 경우
select p.Category,
p.ProductName,
rank() over(partition by p.Category order by sum(o.Quantity) desc) rn
from Orders o
left join Products p on o.ProductID = p.ProductID
group by 1, 2
#서브쿼리로 각 상품별로 판매량을 먼저 집계 후 group by 없이 rank()를 사용하는 경우
SELECT Category,
ProductName,
RANK() OVER (PARTITION BY Category ORDER BY total_quantity DESC) AS rank
FROM (
SELECT p.Category,
p.ProductName,
SUM(o.Quantity) AS total_quantity
FROM Orders o
LEFT JOIN Products p ON o.ProductID = p.ProductID
GROUP BY p.Category, p.ProductName
) sub;
반드시 알고가자 !! -> 윈도우 함수는 그룹별로 집계되어있는 결과가 명확해야 한다는 것!!
* 241231 수정
인줄 알았으나.. 집계함수를 쓰지 않아도 윈도우 함수는 잘 ~ 돌아감
SELECT CATEGORY,
PRODUCT_NAME,
rank() over(partition by CATEGORY order by PRICE desc) as ranking,
PRICE
from FOOD_PRODUCT
아주 멀쩡히 돌아감... 저건 그냥 order by 에 sum이 들어가서 집계함수가 필요해서 넣어야 했던 것 뿐임 없어도 잘 돌아간다!
