SQL 문제

코딩/SQL

SQL 문제

americanoallday 2025. 1. 15. 16:06

2) 이제 좀 벌었으니 flex 한 번 해볼까요?!

products 테이블 정보

1. products 테이블에서 제품 이름(product_name)과 가격(price)만을 선택하는 쿼리를 작성해주세요.

select product_name, price from products;

2. products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.

select id, product_name from products where product_name like '%프로%';

3. products 테이블에서 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리를 작성해주세요.

select id, product_name from products where product_name like '갤%';

4. products 테이블에서 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리를 작성해주세요.

select sum(price) total_price from products;

3) 상품 주문이 들어왔으니 주문을 처리해봅시다!
orders 테이블

1. orders 테이블에서 주문 수량(amount)이 2개 이상인 주문을 진행한 소비자의 ID(customer_id)만 선택하는 쿼리를 작성해주세요!

select customer_id
from orders
where amount>=2;

2. orders 테이블에서 2023년 11월 2일 이후에 주문된 주문 수량(amount)이 2개 이상인 주문을 선택하는 쿼리를 작성해주세요!

select *
from orders
where amount>=2 and order_date>’2023-11-02’;

3. orders 테이블에서 주문 수량이 3개 미만이면서 배송비(shipping_fee)가 15000원보다 비싼 주문을 선택하는 쿼리를 작성해주세요!

select *
from orders
where amount<3 and shipping_fee>15000;

4. orders 테이블에서 배송비가 높은 금액 순으로 정렬하는 쿼리를 작성해주세요!

select *
from orders
order by shipping_fee desc;

4) 이제 놀만큼 놀았으니 다시 공부해봅시다!
sparta_students(학생) 테이블

1. sparta_students 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성해주세요!

select name, track from sparta_students;

2. sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!

select * from sparta_students where track <> 'Unity';

3. sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!

select * from sparta_students 
where enrollment_year in (2021, 2023)

4. sparta_students 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성해주세요!

select * from sparta_student 
where track='Node.js' and grade='A';

5) 공부하다보니 팀 프로젝트 시간이 왔어요!

team_projects(프로젝트) 테이블

1. team_projects 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성해주세요!

select name from team_projects where aws_cost>=40000;

2. team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!

select * from team_projects where substr(date(start_date),1,4)=2022;

3. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!

select * from team_projects where date(end_date) >= curdate();

4. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!

select name, datediff(date(end_date), date(start_date)) working_days 
from team_projects;

6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!

lol_users(LOL 유저 테이블)

1. lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)

select name, region, rating, rank() over(order by rating desc) ranking from lol_users;

2. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요

select name from lol_users order by join_date desc limit 1;

3. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!

select id, name, region, rating from lol_users 
order by region, rating desc;

4. lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!

select region, avg(rating) avg_rating from lol_users group by region;

7) 랭크게임 하다가 싸워서 피드백 남겼어요…

lol_feedbacks (LOL 피드백 테이블)

1. lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!

select * from lol_feedbacks order by satisfaction_score desc;

2. lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!

select id, user_name, satisfaction_score, max(feedback_date) from lol_feedbacks

3. lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!

select count(1) score_5_cnt from lol_feedbacks where satisfaction_score=5;

4. lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!

select user_name, count(user_name) user_feedback_cnt 
from lol_feedbacks 
group by user_name 
order by user_feedback_cnt desc 
limit 3;

5. lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!

select avg(satisfaction_score), feedback_date 
from lol_feedbacks 
group by feedback_date 
order by avg(satisfaction_score) desc 
limit 1;

8) LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.
doctors(의사) 테이블

1. doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요!

select name from doctors where major="성형외과";

2. doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!

select major, count(name) from doctors group by major;

3. doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!

select count(1) cnt_doctor_workingdays_over_5years from doctors where timestampdiff(year, hire_date, curdate())>=5;

4. doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!

select name, datediff(curdate(),hire_date) working_days from doctors

9)아프면 안됩니다! 항상 건강 챙기세요!
patients(환자) 테이블

1. patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요!

select gender, count(1) cnt_patient_bygender from patients group by gender

2. patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!

select count(1) patients from patients 
where timestampdiff(year, birth_date, curdate())>=40;

3. patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요!

select name from patients where datediff(curdate(), last_visit_date)>=365;

4. patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!

select count(1) cnt_1980s_patients from patients 
where birth_date between '1980-01-01' and '1989-12-31';

10) 이젠 테이블이 2개입니다

1. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!

select count(1) from departments;

2. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!

select e.name, d.name from employees e left join departments d on e.department_id=d.id;

3. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

select e.name 
from employees e left join departments d on e.department_id=d.id 
where d.name='기술팀';

4. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!

select d.name, count(e.name) 
from employees e left join departments d on e.department_id=d.id 
group by e.department_id;

5. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!

select d.name 
from departments d left join employees e on d.id=e.department_id 
where e.id is NULL;

6. '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

select e.name, d.name 
from employees e left join departments d on e.department_id=d.id 
where d.name='마케팅팀';

마지막 연습 문제 !

1. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!

select o.id, p.name from orders o left join products p on o.product_id=p.id;

2. 총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!

select p.id, sum(p.price*o.quantity) total_sales 
from orders o left join products p on o.product_id=p.id 
group by p.id 
order by total_sales desc 
limit 1;

3. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!

select p.id, p.name, sum(o.quantity) 
from orders o left join products p on o.product_id=p.id 
group by p.id;

4. 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!

select p.name, o.order_date 
from orders o left join products p on o.product_id=p.id 
where o.order_date>'2023-03-03';

5. 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!

select p.name, sum(o.quantity) total_quantity 
from orders o left join products p on o.product_id=p.id 
group by p.name 
order by total_quantity desc 
limit 1;

6. 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!

select p.id, p.name, avg(o.quantity) avg_quantity 
from orders o left join products p on o.product_id=p.id 
group by p.id;

7. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!

select p.id, p.name 
from products p left join orders o on o.product_id=p.id 
where o.id is NULL

Lv1. 데이터 속 김서방 찾기

users 테이블 :

user_id
created_at
updated_at
name
email

select count(name) name_cnt from users
where name like "김%" ;

Lv2. 날짜별 획득포인트 조회하기

point_users 테이블 :

point_user_id
created_at
updated_at
user_id
point

select date(created_at) created_at, avg(round(point)) average_points from point_users
group by date(created_at);

Lv3. 이용자의 포인트 조회하기

Lv1, Lv2 users, point_users테이블

select u.user_id, u.email, coalesce(p.point,0) point
from users u join point_users p on u.user_id = p.user_id
group by u.user_id
order by point desc;

Lv4. 단골 고객님 찾기

select c.CustomerName, count(o.OrderID) OrderCount,coalesce(sum(o.TotalAmount),0) TotalSpent
from Orders o join customers c on o.CustomerID=c.CustomerID
group by c.CustomerName, o.CustomerID;

select 
Country,
Top_Customer,
TotalSpent Top_Spent
from (
select 
c.Country Country, 
c.CustomerName Top_Customer,
coalesce(sum(o.TotalAmount),0) TotalSpent,
rank() over(partition by c.Country order by SUM(o.TotalAmount) desc) rnk
from Orders o join customers c on o.CustomerID=c.CustomerID
group by c.Country, c.CustomerID, c.CustomerName
) a
where rnk = 1;

Lv5. 가장 많이 팔린 품목은?

select
	CustomerName,
	TotalAmount,
	OrderCount
from
	Customers c
join
(
	select
		o.CustomerID,
		sum(o.Quantity * p.Price) TotalAmount,
		count(o.OrderID) OrderCount
	from
		Orders o
	join Products p on
		o.ProductID = p.ProductID
	group by
		o.CustomerID
)a on
	c.CustomerID = a.CustomerID

-- 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.
select
	Category,
	Top_Product,
	TotalSold
from
	(
	select
		p.Category Category,
		p.ProductName Top_Product,
		sum(o.Quantity) TotalSold,
		rank() over(partition by p.Category
	order by
		sum(o.Quantity) desc) rnk
	from
		Orders o
	join Products p on
		o.ProductID = p.ProductID
	group by
		p.Category, p.ProductID, p.ProductName
) a
where rnk = 1;

Lv5. 예산이 가장 큰 프로젝트는?

-- 각 직원이 속한 부서에서 가장 높은 월급을 받는 직원들만 포함된 결과를 조회하는 SQL 쿼리를 작성해주세요.
-- 출력 결과에는 직원의 이름, 부서, 그리고 월급이 포함되어야 합니다.
SELECT
	Name,
	Department,
	Salary
FROM(
SELECT
	Name,
	Department,
	Salary,
	rank() over(partition by Department
order by
	Salary desc) rnk
FROM
	Employees
) a 
where
	rnk = 1;

SELECT
	a.Name,
	p.ProjectName,
	p.Budget
FROM
	Projects p
JOIN (
	SELECT
	e.Name Name,
	ep.ProjectID ProjectID
	FROM EmployeeProjects ep
	inner join Employees e on
		ep.EmployeeID = e.EmployeeID 
)a on
	p.ProjectID = a.ProjectID
WHERE
	Budget >= 10000