기록

Employees 테이블

1. 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

select e.Name,
		e.Department,
		e.Salary,
		b.Top_Earner,
		b.Top_Salary
from employees e
	inner join 
	(
		select Department,
			Name Top_Earner,
			Salary Top_Salary
		from 
		(
		select Department,
			Name,
			Salary,
		rank() over(partition by Department order by Salary desc) rn
		from employees
		)a
		where rn = 1
	)b on e.Department = b.Department

• 먼저 각 부서별 월급 순위를 윈도우 함수를 통해서 만든 후에 이를 서브쿼리로 만들어서 where절을 이용하여 부서별 1순위만 추출함
• 그 후에 해당 결과를 서브쿼리로 만들어서 서브쿼리와 조인해서 결과를 도출함

2. 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.

select Department,
	avg(Salary) Avg_Salary
from employees e
group by Department
order by Avg_Salary desc limit 1

SQL 실행 순서

1. FROM: 데이터를 테이블에서 읽어옵니다.
2. WHERE: 개별 행(row)에 대해 조건을 필터링합니다. (집계 함수 사용 불가)
3. GROUP BY: 데이터를 그룹화합니다.
4. 집계 함수 (SUM, AVG, COUNT 등): 그룹화된 데이터에 대해 계산을 수행합니다.
5. HAVING: 계산된 결과(집계 함수 포함)를 필터링합니다.
6. SELECT: 최종 결과를 반환합니다.

1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

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

• 주문건수가 없는 고객의 주문 횟수를 어떤 식으로 생각할지 고민했음
• -> 결국 주문건수가 없다는 것은 orderId가 없다는 것임
• coalesce (sum(o.TotalAmount), 0)    널인 항목을 0으로 대신 넣겠다

2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

select Country,
	CustomerName Top_Customer,
	total_amount Top_Spent
from 
(
select c.Country 
	, c.CustomerName
	, sum(o.TotalAmount) total_amount
	, rank() over (partition by c.Country order by sum(o.TotalAmount) desc) ranking
from Customers_n c
left join Orders_n o
	on c.CustomerID = o.CustomerID
group by 1, 2
) a
where ranking = 1

• 윈도우 함수의 사용법 
  • <윈도우 함수> OVER ([PARTITION BY <열>] [ORDER BY <열>])
    • 윈도우 함수: 수행할 작업(예: SUM, AVG, RANK, ROW_NUMBER 등).
    • OVER: 윈도우 함수의 범위를 정의.
    • PARTITION BY (선택 사항): 데이터를 특정 그룹으로 나누는 역할. 생략하면 전체 데이터에 대해 계산.
    • ORDER BY (선택 사항): 윈도우 내에서 특정 기준으로 정렬 후 계산.

SELECT c.Country,
		c.CustomerName,
		sum(o.TotalAmount) TotalAmount
from Customers_n c
inner join Orders_n o 
	on c.CustomerID = o.CustomerID
group by 1, 2
	having TotalAmount = (
		select max(a.total) 
		from
		(
		SELECT c_n.Country,
			c_n.CustomerName,
			sum(o_n.TotalAmount) total
		from Customers_n c_n
		inner join Orders_n o_n
		on c_n.CustomerID = o_n.CustomerID
		group by 1, 2
		) a
		where a.Country = c.Country
	)

• group by 절에서 조건을 걸때는 having을 사용
• 그룹화할때의 조건을 주로 having에서 사용함
• where a.Country = c.Country 
  • 이 부분에서 나라별로 최대 주문 금액을 가져올 수 있다는 점을 배웠다 (쉽지 않았다 ㅠ)

Lv1. 데이터 속 김서방 찾기

select count(1)
from users
where substr(name, 1, 1) = '김'

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

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

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

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

select u.user_id,
		u.email,
		if(p.point is null, 0, p.point) point
from users u
left join point_users p on u.user_id = p.user_id
order by point desc

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 2 DESC limit 1

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

select p.id, 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

from orders o

inner join products p

on o.product_id = p.id

where o.order_date > '2023-03-03'

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

select p.name, sum(o.quantity)

from orders o

inner join products p

on o.product_id = p.id

group by p.name

order by 2 desc limit 1

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

select p.id, avg(o.quantity)

from orders o

inner join products p

on o.product_id = p.id

group by p.id

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

select a.id, a.name

from

(

select p.id, p.name, sum(o.quantity) as sum_quantity

from products p

left join orders o

on o.product_id = p.id

group by 1, 2

) a

where 0 = a.sum_quantity

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

select count(*)

from departments

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

select e.name name, d.name departments_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(*)

from employees e

left join departments d

on e.department_id = d.id

group by d.name

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

from departments d

inner join employees e

on d.id = e.department_id

where d.name = '마케팅팀'

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

select gender, count(*)

from patients

group by gender

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

select count(*)

from patients

where 40 <= timestampdiff(year, birth_date, curdate())

SELECT COUNT(*) FROM patients

WHERE birth_date <= DATE_SUB(CURDATE(), INTERVAL 40 YEAR);

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

select *

from patients

where last_visit_date <= date_sub(curdate(), interval 1 year)

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

select count(*)

from patients

where birth_date between '1980-01-01' and '1989-12-31'

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

select name

from doctors

where major = '성형외과'

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

select major, count(*)

from doctors

group by major

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

select count(*)

from doctors

where hire_date <= date_sub(curdate(), interval 5 year)

#timestampdiff는 두번째 매개변수에서 첫번째를 뺌, 나머지 diff는 반대

select count(*)

from doctors

where 5 <= timestampdiff(year, hire_date, curdate())

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

select name, datediff(curdate(), hire_date) as date_period

from doctors

배운 것

• TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
  
  • unit: 계산 단위 (YEAR, MONTH, DAY, 등)
  • datetime_expr1: 시작 날짜/시간
  • datetime_expr2: 종료 날짜/시간
  • 두번째에서 첫번째 매개변수를 뺌
• datediff(datetime_expr1, datetime_expr2)
  • 첫번째에서 두번째 매개변수를 뺌
• date_sub(curdate(), interval 5 year)
  
  • date: 기준 날짜
  • INTERVAL expr unit: 빼고자 하는 기간을 표현
    • expr: 숫자 (빼고자 하는 값)
    • unit: 기간 단위 (예: DAY, MONTH, YEAR, HOUR, 등)