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

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

2. sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!
   select * from sparta_students where track is not '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 enrollment_year 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

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 year(start_date) = 2022
3. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!
   select * from team_projects where end_date>getdate()
4. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!
   select datediff(day, end_date, start_date) as '지속 기간' 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

1. lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)
   select name, rank() over (order by rating desc) from lol_users
2. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요
   select name from lol_users where join_date = (select max(join_date) from lol_users)
3. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!
   select name, region from lol_users order by region, rating desc
4. 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

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

2. lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!
   select * from lol_feedbacks as a group by user_name having feedback_date = (select max(feedback_date) from lol_feedbacks as b where a.user_name=b.user_name group by user_name)

3. lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!
   select count(*) from lol_feedbacks where satisfaction_score = 5

4. lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!
   select top 3 user_name, count() as cnt from lol_feedbacks group by user_name order by cnt desc

5. lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!
   select top 1 feedback_date, avg(satisfation_score) as avg_score from lol_feedbacks group by feedback_date order by avg_score desc

---

# **8) LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.**

이제, 아래와 같은 doctors(의사) 테이블이 있습니다.

id	name	major	hire_date
1	르탄이	피부과	2018-05-10
2	배캠이	성형외과	2019-06-15
3	구구이	안과	2020-07-20

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

2. doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!
   select count(*) from doctors group by major

3. doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!
   select count(*) from doctors where datediff(year, getdate(), hire_date)>=5

4. doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!
   select name, datediff(day, getdate(), hire_date) as 근무 기간(일) 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

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

2. patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!
   select count(*) from patients where datediff(year, getdate(), birth_date)>=40

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

4. patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!
   select count(*) from patients where year(birth_date) between 1980 and 1989

   ---

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

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

• employees 테이블

id	department_id	name
1	101	르탄이
2	102	배캠이
3	103	구구이
4	101	이션이

• departments 테이블

id	name
101	인사팀
102	마케팅팀
103	기술팀

1. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!
   select count(*) from departments

2. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!
   select a.name, b.name from employees a join departments b on a.department_id = b.id

3. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
   select name from employees a join departments b on a.department_id = b.id where b.name = '기술팀'

4. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!
   select b.name, count(*) as '직원 수' from employees a join departments b on a.department_id = b.id group by b.name

5. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!
   select b.name from employees a right join departments b on a.department_id = b.id where a.id is null

6. '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
   select b.name from emxployees a, departments b where a.department_id=b.id and b.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

1. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!
   select o.id, p.name from orders o join products p on o.product_id=p.id

2. 총 매출(price quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!
   select top 1 id, pricequantity as total from orders o join products p on o.product_id=p.id order by total

3. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!
   select p.id, sum(o.quantity) from orders o join products p on o.product_id=p.id group by p.id

4. 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!
   select distinct p.name from orders o join products p on o.product_id=p.id where order_date>'2023-03-03'

5. 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!
   select name from products where id = (select top 1 product_id, count(*) as cnt from orders order by cnt desc)

6. 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!
   select p.id, avg(o.quantity) from orders o join products p on o.product_id=p.id group by p.id

7. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!select p.id, p.name from products p left outer join orders o on o.product_id=p.id where o.id is null

   ---

   이게 걷기반 문제라니... 말도안된다