카테고리 없음
TIL_20250507 (수) SQL 문법 연습 사전캠프 퀘스트
elya0919
2025. 5. 7. 17:41
걷기반
1번 돈을 벌기 위해~
아래와 같은 sparta_employees(직원) 테이블이 있습니다.
|
id
|
name
|
position
|
salary
|
hire_date
|
|
1
|
르탄이
|
개발자
|
30000
|
2022-05-01
|
|
2
|
배캠이
|
PM
|
40000
|
2021-09-25
|
|
3
|
구구이
|
파트장
|
35000
|
2023-06-01
|
|
4
|
이션이
|
팀장
|
50000
|
2021-07-09
|
1.
sparta_employees 테이블에서 모든 직원의 이름(name)과 직급(position)을 선택하는 쿼리를 작성해주세요.
select name, position
from sparta_employees
2.
sparta_employees 테이블에서 중복 없이 모든 직급(position)을 선택하는 쿼리를 작성해주세요.
select distinct position
from sparta_employees
3.
sparta_employees 테이블에서 연봉(salary)이 40000과 60000 사이인 직원들을 선택하는 쿼리를 작성해주세요.
select *
from sparta_employees
where salary between 40000 and 60000
4.
sparta_employees 테이블에서 입사일(hire_date)이 2023년 1월 1일 이전인 모든 직원들을 선택하는 쿼리를 작성해주세요.
select *
from sparta_employees
where hire_date < 2023-01-01
2) 이제 좀 벌었으니 flex 한 번 해볼까요?!
실제 데이터 베이스를 연결하기 전, SQL 문법을 탄탄하게 다져봅시다.
여러분이 구매하고 싶은 상품들의 정보가 있는 products(상품) 테이블이 아래에 있습니다.
|
id
|
product_name
|
price
|
category
|
|
1
|
맥북 프로
|
1200
|
컴퓨터
|
|
2
|
다이슨 청소기
|
300
|
생활가전
|
|
3
|
갤럭시탭
|
600
|
컴퓨터
|
|
4
|
드롱기 커피머신
|
200
|
주방가전
|
5.
products 테이블에서 제품 이름(product_name)과 가격(price)만을 선택하는 쿼리를 작성해주세요.
select product_name, price
from products
6.
products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.
select *
from products
where product_name like '%프로%'
7.
products 테이블에서 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리를 작성해주세요.
select *
from products
where product_name like '갤%'
8.
products 테이블에서 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리를 작성해주세요.
select sum(price) as total_price
from products
3) 상품 주문이 들어왔으니 주문을 처리해봅시다!
이제 상품 주문이 들어왔으니 어떤 고객에게 어떤 주문이 들어왔는지를 파악할 수 있는 orders(주문) 테이블이 아래에 있습니다.
|
id
|
customer_id
|
product_id
|
amount
|
shipping_fee
|
order_date
|
|
1
|
719
|
1
|
3
|
50000
|
2023-11-01
|
|
2
|
131
|
2
|
1
|
10000
|
2023-11-02
|
|
3
|
65
|
4
|
1
|
20000
|
2023-11-05
|
|
4
|
1008
|
3
|
2
|
25000
|
2023-11-05
|
|
5
|
356
|
1
|
1
|
15000
|
2023-11-09
|
9.
orders 테이블에서 주문 수량(amount)이 2개 이상인 주문을 진행한 소비자의 ID(customer_id)만 선택하는 쿼리를 작성해주세요!
select customer_id, amount
from orders
where amount >= 2
10.
orders 테이블에서 2023년 11월 2일 이후에 주문된 주문 수량(amount)이 2개 이상인 주문을 선택하는 쿼리를 작성해주세요!
select *
from orders
where order_date > 2023-11-02 and amount >= 2
11.
orders 테이블에서 주문 수량이 3개 미만이면서 배송비(shipping_fee)가 15000원보다 비싼 주문을 선택하는 쿼리를 작성해주세요!
select *
from orders
where amount < 3 and shipping_fee > 15000
12.
orders 테이블에서 배송비가 높은 금액 순으로 정렬하는 쿼리를 작성해주세요!
select *
from orders
order by shipping_fee desc
4) 이제 놀만큼 놀았으니 다시 공부해봅시다!
아래와 같은 sparta_students(학생) 테이블이 있습니다.
|
id
|
name
|
track
|
grade
|
enrollment_year
|
|
1
|
르탄이
|
Node.js
|
A
|
2023
|
|
2
|
배캠이
|
Spring
|
B
|
2022
|
|
3
|
구구이
|
Unity
|
C
|
2021
|
|
4
|
이션이
|
Node.js
|
B
|
2022
|
13.
sparta_students 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성해주세요!
select name, track
from sparta_students
14.
sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!
select *
from sparta_students
where not track = 'unity'
15.
sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!
select *
from sparta_students
where enrollment_year = '2021' or enrollment_year = '2023'
16.
sparta_students 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성해주세요!
select *
from sparta_students
where track = 'Node.js' and grade = 'A'
5) 공부하다보니 팀 프로젝트 시간이 왔어요!
공부를 한 결과를 점검하기 위해 팀 프로젝트를 수행해야 합니다! 이제, 아래와 같은 team_projects(프로젝트) 테이블이 있습니다.
|
id
|
name
|
start_date
|
end_date
|
aws_cost
|
|
1
|
일조
|
2023-01-01
|
2023-01-07
|
30000
|
|
2
|
꿈꾸는이조
|
2023-03-15
|
2023-03-22
|
50000
|
|
3
|
보람삼조
|
2023-11-20
|
2023-11-30
|
80000
|
|
4
|
사조참치
|
2022-07-01
|
2022-07-30
|
75000
|
17.
team_projects 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성해주세요!
select *
from team_projects
where aws_cost >= 40000
18.
team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!
select *
from team_projects
where start_date like '2022%'
19.
team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!
select *
from team_projects
where start_date < now() and end_date > now()
20.
team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!
SELECT
name,
DATEDIFF(end_date, start_date)
FROM team_projects
name,
DATEDIFF(end_date, start_date)
FROM team_projects
6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!
아래와 같은 lol_users(LOL 유저 테이블)이 있습니다.
|
id
|
name
|
region
|
rating
|
join_date
|
|
1
|
르탄이
|
한국
|
1300
|
2019-06-15
|
|
2
|
배캠이
|
미국
|
1500
|
2020-09-01
|
|
3
|
구구이
|
한국
|
1400
|
2021-01-07
|
|
4
|
이션이
|
미국
|
1350
|
2019-11-15
|
21.
lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)
SELECT name, rating,
RANK() OVER (ORDER BY rating DESC) AS lol_rank
FROM lol_users;
22.
lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요!
서브쿼리 사용
SELECT name
FROM lol_users
WHERE join_date = (
SELECT MAX(join_date)
FROM lol_users
) t;
FROM lol_users
WHERE join_date = (
SELECT MAX(join_date)
FROM lol_users
) t;
리미트 사용
select name
from lol_users
order by join_date
limit 1
23.
lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!
select *
from lol_users
order by region, rating desc
24.
lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!
select region, avg(rating)
from lol_users
group by region
7) 랭크게임 하다가 싸워서 피드백 남겼어요…
아래와 같은 lol_feedbacks (LOL 피드백 테이블)이 있습니다.
|
id
|
user_name
|
satisfaction_score
|
feedback_date
|
|
1
|
르탄이
|
5
|
2023-03-01
|
|
2
|
배캠이
|
4
|
2023-03-02
|
|
3
|
구구이
|
3
|
2023-03-01
|
|
4
|
이션이
|
5
|
2023-03-03
|
|
5
|
구구이
|
4
|
2023-03-04
|
25.
lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!
select *
from lol_feedbacks
order by satisfaction_score desc, feedback_date desc;
실무에서는 *을 잘 안씀
지금은 연습 문제라 필요한 컬럼만 나와있지만 나중에 데이터베이스에는 굉장히 많은 양의 정보가 있어
*을 쓰면 필요없는 컬럼도 너무 많이 들어오기 때문이다.
26.
lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!
select user_name, satisfaction_score, max(feedback_date)
from lol_feedbacks
group by user_name;
id를 기준으로 안 잡는 이유 = 접수가 들어온 id는 고유한 user_id가 아닐 수도 있긴 때문
27.
lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!
select count(satisfaction_score)
from lol_feedbacks
where satisfaction_score = 5;
28.
lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!
select user_name, count(feedback_date) as count_feedback
from lol_feedbacks
group by user_name
order by count_feedback
limit 3;
29.
lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!
select avg(satisfaction_score) as avg_score, feedback_date
from lol_feedbacks
group by feedback_date
order by avg_score desc
limit 1;
8) LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.
이제, 아래와 같은 doctors(의사) 테이블이 있습니다.
|
id
|
name
|
major
|
hire_date
|
|
1
|
르탄이
|
피부과
|
2018-05-10
|
|
2
|
배캠이
|
성형외과
|
2019-06-15
|
|
3
|
구구이
|
안과
|
2020-07-20
|
30.
doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요!
select name, major
from doctors
where major = '성형외과'
31.
doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!
select
major, count(*) as count_major
from doctors
group by major
32.
doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!
SELECT COUNT(*) AS senior_doctors
FROM doctors
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);
FROM doctors
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);
DATE_SUB = 날짜 빼기
INTERVAL 5 YEAR = 5년
ex)
hire_date = 2018-05-10
DATE_SUB(CURDATE(), INTERVAL 5 YEAR)
= DATE_SUB( (2025-05-07) - (5년) )
= DATE_SUB ( 2020-05-07)
즉, where절은
hire_date (2018-05-10)이 date_sub(2020-05-07) 이하면 출력
2020-05-07 이하 입사는 5년 이상 근무한게 된다.
33.
doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!
SELECT name, DATEDIFF(CURDATE(), hire_date)
FROM doctors;
9)아프면 안됩니다! 항상 건강 챙기세요!
의사가 있으면 당연히 의사에게 진료받는 환자가 있겠죠? 아래와 같은 patients(환자) 테이블이 있습니다.
|
id
|
name
|
birth_date
|
gender
|
last_visit_date
|
|
1
|
르탄이
|
1985-04-12
|
남자
|
2023-03-15
|
|
2
|
배캠이
|
1990-08-05
|
여자
|
2023-03-20
|
|
3
|
구구이
|
1982-12-02
|
여자
|
2023-02-18
|
|
4
|
이션이
|
1999-03-02
|
남자
|
2023-03-17
|
34.
patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요!
select gender, count(*)
from patients
group by gender
35.
patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!
SELECT COUNT(*) AS over_40_count
FROM patients
WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 40;
FROM patients
WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 40;
***
where 절에 함수가 들어가면 안되는 줄 알고 처음에 서브쿼리로 만들었는데
집계함수만 안됌
***
36.
patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요!
select id, name,gender
from patients
where DATEDIFF(CURDATE(), last_visit_date) >= 365
37.
patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!
처음에 이렇게 품
select count(*)
from patients
where birth_date like '198%'
수정
select count(*)
from patients
where birth_date between '1980-01-01' and '1980-12-31'
10) 이젠 테이블이 2개입니다
다음과 같은 직원(employees) 테이블과 부서(departments) 테이블이 있습니다.
•
employees 테이블
|
id
|
department_id
|
name
|
|
1
|
101
|
르탄이
|
|
2
|
102
|
배캠이
|
|
3
|
103
|
구구이
|
|
4
|
101
|
이션이
|
•
departments 테이블
|
id
|
name
|
|
101
|
인사팀
|
|
102
|
마케팅팀
|
|
103
|
기술팀
|
38.
현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!
select count(*)
from departments
39.
모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!
select e.*, d.*
from employees e
join departments d on e.department_id = d.id
40.
'기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
select d.name, e.name
from employees e
join departments d on e.department_id = d.id
where d.name = '기술팀'
41.
부서별로 직원 수를 계산하는 쿼리를 작성해주세요!
select d.name, count(*)
from employees e
join departments d on e.department_id = d.id
group by d.name
42.
직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!
SELECT d.name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;
43.
'마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
SELECT e.name
FROM employees e JOIN departments d ON e.department_id = d.id
WHERE d.name = '마케팅팀';
마지막 연습 문제 !
다음과 같은 상품(products) 테이블과 주문(orders) 테이블이 있습니다.
•
products 테이블
|
id
|
name
|
price
|
|
1
|
랩톱
|
1200
|
|
2
|
핸드폰
|
800
|
|
3
|
타블렛
|
400
|
•
orders 테이블
|
id
|
product_id
|
quantity
|
order_date
|
|
101
|
1
|
2
|
2023-03-01
|
|
102
|
2
|
1
|
2023-03-02
|
|
103
|
3
|
5
|
2023-03-04
|
44.
모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!
select o.id, p.name
from orders o
join products p on o.product_id=p.id
45.
총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!
select p.id, sum(p.price*o.quantity) as total_price
from orders o
join products p on o.product_id=p.id
group by p.id
order by desc;
46.
각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!
select p.id, sum(o.quantity) as total_quantity
from orders o
join products p on o.product_id=p.id
group by p.id
47.
2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!
select p.id,p.name
from orders o
join products p on o.product_id=p.id
where o.order_date > 2023-03-03
48.
가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!
select p.name,sum(o.quantity) as total_quantity
from orders o
join products p on o.product_id=p.id
group by p.name
order by total_quantity
49.
각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!
select p.id, avg(o.quantity) as avg_quantity
from orders o
join products p on o.product_id=p.id
group by p.id
50.
판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!
select p.id, p.name
from orders o
left join products p on o.product_id=p.id
where o.id is null