[250807] 스파르타코딩 본캠프 4일차 - 사전캠프 퀘스트 SQL 걷기반

사전캠프 퀘스트 - SQL 걷기반

 

(1) 돈을 벌기 위해 일을 합시다!

아래와 같은 sparta_employees(직원) 테이블이 있습니다.

 

sparta_employees 테이블에서 모든 직원의 이름(name)과 직급(position)을 선택하는 쿼리를 작성해주세요.

select name, position
from sparta_employees

 

sparta_employees 테이블에서 중복 없이 모든 직급(position)을 선택하는 쿼리를 작성해주세요.

select distinct position
from sparta_employees

 

sparta_employees 테이블에서 연봉(salary)이 40000과 60000 사이인 직원들을 선택하는 쿼리를 작성해주세요

select name
from sparta_employees
where salary between 40000 and 60000

 

테이블에서 입사일(hire_date)이 2023년 1월 1일 이전인 모든 직원들을 선택하는 쿼리를 작성해주세요.

select name, hire_date
from sparta_employees
where hire_date < 2023-01-01

 

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

여러분이 구매하고 싶은 상품들의 정보가 있는 products(상품) 테이블이 아래에 있습니다.

 

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

select product_name, price
from products

 

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

select product_name
from products
where product_name like '%프로%'

 

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

select product_name
from products
where product_name like '갤%'

 

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

select sum(price) as sum_price
from products

 

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

이제 상품 주문이 들어왔으니 어떤 고객에게 어떤 주문이 들어왔는지를 파악할 수 있는 orders(주문) 테이블이 아래에 있습니다.

 

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

select customer_id
from orders
where amount >= 2

 

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

select customer_id, amount, order_date 
from orders
where order_date > '2023-11-02' and amount >= 2

 

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

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

 

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

select *
from orders
order by shipping_fee desc

 

(4) 이제 놀만큼 놀았으니 다시 공부해봅시다!

아래와 같은 sparta_students(학생) 테이블이 있습니다.

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

select name, track
from sparta_students

 

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

select name, track
from sparta_students
where track != 'Unity'

 

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

[내 코드]
# and를 써도 2021이랑 2023 같이 선택이 안돼서 대체 뭐지...싶었음
# 그렇다고 2021, 2023도 아니었음. 너무 파이썬 문법에 길들여져서 아직 SQL 문법이 잘 기억나질 않음.

select *
from sparta_students
where enrollment_year = 2021 and 2023

[답 코드]
select *
from sparta_students
where enrollment_year in (2021, 2023)

 

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

select enrollment_year
from sparta_students
where track = 'Node.js' and grade = 'A'

 

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

공부를 한 결과를 점검하기 위해 팀 프로젝트를 수행해야 합니다! 이제, 아래와 같은 team_projects(프로젝트) 테이블이 있습니다

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

select name
from team_projects
where aws_cost >= 40000

 

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

[내코드]
select *
from team_projects
where start_date like '2022%'

[답코드]
select *
from team_projects
where year(start_date) = 2022

 

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

### 문제 자체가 지금 진행중일 수가 없어서 그냥 답안지만 봄 ###

select *
from team_projects
where curdate() between start_date and end_date

 

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

### 강의에 없던 거라 구글링해서 datediff 사용함 ###
select datediff(day, start_date, end_date) as '지속 기간'
from team_projects

curdate()

YYYY-MM-DD 형식의 현재 날짜
데이터베이스 서버의 현재 시간대를 기준으로 날짜가 반환

 

datediff()

datediff('구분자', '시작일자', '종료일자')

구분자는 어떤 차이를 구할지 정하는 부분

ex) year, month, day, week, hour, minute, second ...

 

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

아래와 같은 lol_users(LOL 유저 테이블)이 있습니다.

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

select *
from lol_users
order by rating desc

 

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

### order by로 가장 늦게한 날짜를 제일 위로 올려서, 첫번째 열을 출력하고 싶었는데
### 강의에서는 배우지 않아서 구글링해서 사용함.
select name, join_date
from lol_users
order by join_date desc limit 1

 

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

select *
from lol_users
order by region, rating

 

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

select region, avg(rating) as avg_region_rating
from lol_users
group by region

limit

지정한 수의 갯수만큼만 행이 출력됨

쿼리의 맨 마지막에 사용

 

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

아래와 같은 lol_users(LOL 유저 테이블)이 있습니다.

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

select * 
from lol_feedbacks
order by satisfaction_score desc

 

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

select id, user_name, satisfaction_score, max(feeback_date)
from lol_feedbacks
group by user_name

 

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

select count(1) as cnt_5
from lol_feedbacks
where satisfaction_score = 5

 

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

select user_name, count(1) as cnt_name
from lol_feedbacks
group by user_name
order by cnt_name desc limit 3

 

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(의사) 테이블이 있습니다.

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

select name
from doctors
where major = '성형외과'

 

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

select major, count(1) as cnt_major
from doctors
group by major

 

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

[내코드]
### 365 x 5 = 1825 일 해서 datediff로 day가 1825일보다 높으면 선택하는 코드로 짬
select count(1) as 5years
from doctors
where datediff(curdate(), hire_date) > 1825

[답 코드]
### date_sub? 
select count(*) as num_of_doctors
from doctors
where hire_date <= date_sub(curdate(), interval 5 year)

 

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

select name, datediff(curdate(), hire_date) as cnt_day
from doctors
where (curdate() - here_date => 5)

 

date_sub()

date_sub(’기준 날짜’, interval ‘구분자’)

기준 날짜에서 입력된 구분자만큼 빼는 함수

date_add()

date_sub(’기준 날짜’, interval ‘구분자’)

기준 날짜에서 입력된 구분자만큼 더하는 함수

 

 

(9) 아프면 안됩니다! 항상 건강 챙기세요!

의사가 있으면 당연히 의사에게 진료받는 환자가 있겠죠? 아래와 같은 patients(환자) 테이블이 있습니다.

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

select gender, count(1) as cnt_g
from patients
group by gender

 

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

[내코드]
### 어렵네... 여태 있던 함수중에서 써먹어 보려고 했는데 답이 안나와서 구글링함
### 근데 답지 보니까 있던 함수로도 되더라 ㄱ-
### 여튼 이것도 잘 돌아감

select count(1) as cnt_c
from patients
where year(curdate()) - year(birth_date) >= 40

[답코드]
select count(*)
from patients
where birth_date <= date_sub(curdate(), interval 40 year)

 

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

[내코드]
### 위의 답코드 보고 응용해서 써먹음 ㅎㅎ;
select name
from patients
where last_visit_date <= date_sub(curdate(), interval 1 year)

 

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

[내코드]
select count(1) as cnt_c
from patients
where birth_date like '198%'

[답코드]
select count(*)
from patients
where birth_date between '1980-01-01' and '1989-12-31'

 

 

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

다음과 같은 직원(employees) 테이블과 부서(departments) 테이블이 있습니다.

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

select count(1) as cnt_d
from departments
group by name          # 답지 보니 group by 생략 가능한가봄

 

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

select e.name, d.name
from employees e join departmets d on e.id = d.id

 

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

#ㅇㅏ 디버깅 못해서 완전 돌아버리겠순~~ 
#이코드 저코드 해보면서 뭐가 답인지 찾아야되는데
#못하자너~~~
### 같은 이름의 컬럼을 = 하는건줄 알아서 e.id = d.id 라고 했는데, 
### 같은 값을 가지고 있는걸로 하는거였군아 ㅎ;;


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

 

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

[내코드]
# 틀렸음... 
### 없는 부서의 직원까지 0으로 계산해야 하므로 left join 써야함
### count(d.name) 이 되면 name별로 세는데 name이 없는건 아니니까 0명이어도 1로 나온대

select d.name, count(1) as cnt_d 
from employees ejoin departments d on e.department_id = d.id
group by d.name   # d.name 이라고 해도 됨

[답코드]
select d.name, count(e.id) as cnt_d 
from employees e left join departments d on e.department_id = d.id
group by d.id

 

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

# 또틀렸네
### departments 기준으로 left join 을 해야함

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

[답코드]

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

 

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

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

 

 

(11) 마지막 연습 문제

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

select o.id, p.name
from t_products p join t_orders o on p.id = o.product_id

 

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

# 애초에 문제가 잘못된것 같은게...총 매출이 price*quantity 인데 왜 앞에다가 sum까지 붙이는지 이해할 수가?
select p.name, o.id, p.price*o.quantity as total_price
from t_products p join t_orders o on p.id = o.product_id

# 쨌든 문제의 답코드
select p.id, sum(p.price*o.quantity) as total_price
from t_products p join t_orders o on p.id = o.product_id 
group by p.id
order by total_price desc limit 1

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

select p.id, sum(o.quantity) as total_quantity
from t_products p left join t_orders o on p.id = o.product_id 
group by p.id

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

select p.name, o.order_date 
from t_products p join t_orders o on p.id = o.product_id 
where order_date > '2023-03-03'

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

select p.name, o.quantity 
from t_products p join t_orders o on p.id = o.product_id 
order by o.quantity desc limit 1

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

select p.id, avg(o.quantity) as avg_quantity
from t_products p join t_orders o on p.id = o.product_id 
group by p.id

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

select p.id, p.name
from t_products p join t_orders o on p.id = o.product_id 
where o.quantity is null