[sql-practice] SQL 문제 풀이 (6)

• 개요
  • SQL 쿼리 작성 능력 함양을 위해 문제 풀이를 진행함
  • 해당 쿼리들은 MySQL/SQL Server + (a little bit of) PostgreSQL 으로 작성함
  • 이전 포스팅들에서의 hospital.db와 달리 한번에 풀지 못한 문제 혹은 조금이라도 주저한 문제들에 한해 작성함
• 출처
  • https://www.sql-practice.com/

• 스키마

Schema of northwind.db from https://www.sql-practice.com/

• 목차
  • 지문
  • 풀이
  • 정답
  • 노트

---

• 지문
  • Show the first_name, last_name. hire_date of the most recently hired employee.
• 풀이

SELECT
	first_name,
    last_name,
    hire_date
FROM employees
WHERE hire_date = (select MAX(hire_date) FROM employees)

• 정답

select 
    first_name,
    last_name,
    max(hire_date) as hire_date
from employees

• 노트
  • 최근 데이터리안 문제들을 풀면서 PostgreSQL 기반 쿼리를 작성하기 시작했는데 sql-practice 의 정답 쿼리는 PostgreSQL 에서 제대로 작동하지 않을 것임을 내재하게 되었음
  • 이에 정확한 사유를 gpt에게 물어봄
    • PostgreSQL 에서는 MAX(hire_date) 같은 집계 함수를 쓰면서, 동시에 first_name, last_name처럼 집계되지 않은 컬럼을 함께 조회하고 있기 때문에 오류가 날 것이라고 함

---

• 지문
  • Show the ProductName, CompanyName, CategoryName from the products, suppliers, and categories table
• 풀이

SELECT
	product_name,
    company_name,
    category_name
FROM (select supplier_id, category_id, product_name FROM products) p
JOIN (select category_id, category_name FROM categories) c ON c.category_id = p.category_id
JOIN (select supplier_id, company_name FROM suppliers) s ON s.supplier_id = p.supplier_id

• 정답

SELECT p.product_name, s.company_name, c.category_name
FROM products p
JOIN suppliers s ON s.supplier_id = p.Supplier_id
JOIN categories c On c.category_id = p.Category_id;

• 노트
  • 필요한 것만 가져와서 쓰면 효율적이지 않을까 싶었는데 아니었음 !!!
  • gpt는 정답 쿼리가 효율 측면에서 더 낫다고 함
    • 옵티마이저가 처리할 일이 단순
    • 가독성 좋음
    • 서브쿼리로 얻는 성능상 이점 없음
  • 집계, 필터링, 정렬, 윈도우 함수 같은 추가 연산이 없다면 원본 테이블을 바로 조인하는 것과 거의 같다고 함
  • FROM 서브쿼리 (인라인 뷰) 가 유용한 경우는 보통 다음의 경우들이라고 함
    • 집계 후 조인
    • 윈도우 함수 계산 후 조인
    • 중복 제거 후 조인
    • 특정 조건으로 먼저 축소한 결과를 조인
    • 복잡한 계산 결과를 중간 테이블처럼 만들어 조인
    • 긴 SQL 을 단계별로 분리해 가독성 높일 때

---

• 지문
  • Show the city, company_name, contact_name from the customers and suppliers table merged together.
    Create a column which contains 'customers' or 'suppliers' depending on the table it came from.
• 풀이

SELECT
    city,
    company_name,
    contact_name,
    'customers' AS relationship
FROM customers
union
select
 	city,
 	company_name,
 	contact_name,
 	'suppliers' AS relationship
FROM suppliers

• 정답

select City, company_name, contact_name, 'customers' as relationship 
from customers
union
select city, company_name, contact_name, 'suppliers'
from suppliers

• 노트
  • 풀 때 답이 안나왔었는데 union 양 옆 쿼리문들에 괄호를 싸면 안되는 것이었음
  • 정답을 보니 union 아래 select 문에 relationship 으로 컬럼명을 굳이 지정하지 않았는데 sqld 공부했던 기억으로는 union 하면 앞의 selection 문의 컬럼명을 따라가기 때문이었음

---

• 지문
  • Show the total amount of orders for each year/month.
• 풀이

# PostgreSQL
SELECT
    EXTRACT (YEAR FROM order_date) AS order_year,
    EXTRACT (MONTH FROM order_date) AS order_month,
    COUNT(order_date) AS no_of_orders
FROM orders
group by EXTRACT (YEAR FROM order_date), EXTRACT (MONTH FROM order_date)

# SQLite
SELECT
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    COUNT(order_date) AS no_of_orders
FROM orders
group by YEAR(order_date), MONTH(order_date)

• 정답

select
	year(order_date) as order_year,
	month(order_date) as order_month,
   	count(*) as no_of_orders
from orders
group by order_year, order_month

• 노트
  • PostgreSQL 문법을 기준으로 작성하려고 연습 중이긴 하나 해당 사이트에서는 YEAR(), MONTH() 함수를 사용해야 함
  • PostgreSQL 로 문제 풀이 시 EXTRACT (시간 FROM 컬럼명) 임을 습득했음
  • DBMS 마다 문법이 조금씩 다르고 회사마다 요구하는 DBMS 가 다르다는 점 ...

---

• 지문
  • Show the employee's first_name and last_name, a "num_orders" column with a count of the orders taken, and a column called "Shipped" that displays "On Time" if the order shipped_date is less or equal to the required_date, "Late" if the order shipped late, "Not Shipped" if shipped_date is null.
  • Order by employee last_name, then by first_name, and then descending by number of orders.
• 풀이

SELECT
    first_name,
    last_name,
    COUNT(order_id) AS num_orders,
    CASE WHEN shipped_date <= required_date THEN "On Time"
    WHEN shipped_date > required_date THEN "Late"
    WHEN shipped_date IS NULL THEN "Not Shipped" END AS Shipped
FROM orders o
LEFT OUTER JOIN employees e ON o.employee_id = e.employee_id
group by first_name, last_name, Shipped
order by last_name, first_name, num_orders desc

• 정답

SELECT
  e.first_name,
  e.last_name,
  COUNT(o.order_id) As num_orders,
  (
    CASE
      WHEN o.shipped_date <= o.required_date THEN 'On Time'
      WHEN o.shipped_date > o.required_date THEN 'Late'
      WHEN o.shipped_date is null THEN 'Not Shipped'
    END
  ) AS shipped
FROM orders o
  JOIN employees e ON e.employee_id = o.employee_id
GROUP BY
  e.first_name,
  e.last_name,
  shipped
ORDER BY
  e.last_name,
  e.first_name,
  num_orders DESC

• 노트
  • 헤매다가 Expected Output 을 살펴보고 풀어낼 수 있었음
  • 지문에서는 Shipped 를 기준으로 주문 수를 카운트한다는 말이 없는 것 같은데 내가 영어를 못하는건가 ..?
  • 싶었으나 생각해보니 PostgreSQL 은 GROUP BY에 집계 함수 제외 열이 모두 포함되어야 해서 COUNT() 를 제외한 Shipped 컬럼까지 GROUP BY 에 포함되어야 오류가 나지 않을 듯함
  • 그런 관점에서 shipped까지 포함시켜야 하나 싶음
  • gpt에 물어봤을 때도 이것이 핵심 이유에 가깝다..고 함
    • SQL 의 기본 원리가 SELECT에 있는 컬럼이 집계 함수가 아니면 그 값은 GROUP BY 기준으로 하나로 결정될 수 있어야 하며
    • 집계 쿼리 문법을 성립시키려면 대부분의 엄격한 DBMS에서 필요하기 때문이라고 함

---

• 지문
• 풀이

SELECT
	YEAR(o.order_date) AS order_year,
    ROUND(SUM(od.discount*p.unit_price*od.quantity), 2) AS discount_amount
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY YEAR(o.order_date)
order by order_date DESC

• 정답

Select 
YEAR(o.order_date) AS 'order_year' , 
ROUND(SUM(p.unit_price * od.quantity * od.discount),2) AS 'discount_amount' 

from orders o 
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id

group by YEAR(o.order_date)
order by order_year desc;

• 노트
  • 처음에 db 구성을 확인하지 않고 바로 뛰어들었다가 틀리고 db 구성 호다닥 확인함 ..
  • discount가 할인율로 이루어져 있어서 products.unit_price가 필요했음
  • 괜히 products 테이블이 주어진게 아님 ...
  • 문제를 똑바로 읽자

---