[W3Schools] MySQL 예제

SQL을 배우면서 개인적으로 어려웠던 문제를 정리합니다.

예제는 W3Schools의 MySQL Tutorial 환경에서 제공되었습니다

 

실습 환경: https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all

---

 

 

[문제 1] 주문 수량이 가장 많은 주문 건의 상품 ID와 주문 수량 추출

SELECT ProductID, Quantity 
FROM OrderDetails
where Quantity = (select max(Quantity) FROM OrderDetails);

 

---

 

[문제 2] 가격이 상위 10위까지인 상품들의 평균 가격 추출

select avg(t.Price) as avg
from (select * from Products
	order by Price desc
	limit 10) t;

 

 

+ 틀리기 쉬운 코드

select avg(Price)
from Products
order by Price
limit 10;

 

 

SQL  실행 순서 때문에 order by와 limit 절은 맨 마지막에 수행되기 때문에 

정답 코드처럼 상위 10개의 데이터를 먼저 추출한 후, 인라인뷰를 통해 새로운 테이블을 생성해 줘야 합니다.

 

---

 

[문제 3]  공급사 전화번호에서 지역번호만 추출

 

공급사 전화번호 컬럼 형태

 

공급사의 지역번호는 전화번호 컬럼에 ( ) 안에 1~4 자리 숫자로 입력되어 있습니다.

 

 

지역번호만 추출하기 위해서는 ①. instr 함수를 사용해서 ' ( ' , ' ) '의 위치를 찾고 ②. substr 함수를 사용해 1단계에서 구한 괄호 위치를 함수 인자에 대입해 줘야 합니다.

 

SELECT substr(Phone, instr(Phone,'(') + 1 , instr(Phone, ')') - 2 ) as code
FROM Suppliers;

 

 

 

여기서 substr 함수는 설정한 시작 위치부터 지정한 수만큼 문자열을 반환하는 함수이므로 시작 위치가 1이 아닐 때 주의해야 합니다. 개인적으로 이런 미세 조정할 때 어려움을 느끼는 편이라 이 문제도 시간이 오래 걸렸던 거 같습니다. 

 

 

 

저와 마찬가지로 이 문제가 어렵다고 생각하시는 분이 계시다면 아래 정리된 내용이 도움이 됐으면 좋겠습니다. 

 

---

 

 

[문제 4] 주문 이력이 없는 고객의 고객 ID와 고객 이름 추출

select CustomerID, CustomerName
from Customers
where CustomerID not in (select CustomerID from Orders);

 

---

  

[문제 5] 1996-07-10일에 상품을 팔지 못한 사원 ID, FriatName 추출 (left join 사용)

 

1) left join 사용 1

select e.EmployeeID, e.FirstName
from Employees e
left join (select EmployeeID, OrderDate
	  from Orders
          where OrderDate = '1996-07-10') o
on e.EmployeeID = o.EmployeeID
where o.OrderDate is null;

 

left join을 통해 문제를 풀라는 전제가 주어졌었는데, left join에 사용할 테이블을 만들 때 조건절을 사용하지 않아 틀렸던 문제입니다. 

 

 

 

아래는 제가 처음 작성했던 쿼리입니다. 

틀린 이유를 적어놨으니 저와 동일하게 쿼리를 짜셨던 분들은 확인해 보세요

 

틀린 코드1

 

틀린 코드2

 

 

 

2) left join 사용 2

select e.EmployeeID, e.FirstName
from Employees e
left join Orders o on e.EmployeeID = o.EmployeeID and o.OrderDate = '1996-07-10'
where o.OrderDate is null;

이 쿼리가 강사님이 짜셨던 쿼리입니다.

left join을 진행할 때  and를 통해 조건을 연결할 수 있는 걸 처음 알게 되었는데, 확실히 1번 쿼리보다 가독성이 좋아 보입니다. 

그런데 조인 조건을 바로 연결하는 게 아직도 익숙하지 않아 위의 방식으로 풀게 되네요.. 하핳

 

 

 

다음은 left join을 사용하라는 전제가 없다면 사용할 수 있는 쿼리입니다.

 

3) 중첩 서브쿼리 사용

select EmployeeID, FirstName
from Employees
where EmployeeID not in (select EmployeeID
                          from Orders
                          where OrderDate = '1996-07-10')

저는 역시 중첩 서브쿼리 방식이 제일 쉬운 거 같은데 여러분들도 그러시나요?

 

---

 

[문제 6] 주문 수량이 가장 많은 상품의 상품 ID, 상품명, 총수량 추출 (스칼라 서브쿼리 사용)

select od.ProductID, 
	(select p.ProductName from Products p where p.ProductID = od.ProductID ) as ProductName,
	sum(od.Quantity) as total_cnt 
from OrderDetails od
group by od.ProductID 
order by total_cnt desc
limit 1;

 

총수량을 추출하는 것이기 때문에 상품 id별로 주문 수량에 대한 sum을 해줘야 합니다.

 

---

 

[문제 7] 주문 ID별 total 주문 금액 추출 (인라인뷰, 스칼라 서브쿼리 사용)

 

1) 인라인뷰, 스칼라 서브쿼리 사용 

select t.OrderID, 
	sum(t.Quantity * t.Price) as total_price
from (select od.OrderID,
	od.Quantity,
	(select p.Price from Products p where od.ProductID = p.ProductID) as Price
	from OrderDetails od) t
group by t.OrderID;

 

이 문제는 개인적으로 제일 어렵다고 느낀 문제입니다.

inner join을 사용하면 쉽게 풀리는 문제이나, 

인라인뷰와 스칼라 서브쿼리를 동시에 사용해야 돼서 그런지 풀 때마다 구조를 어떤 식으로 잡아야 할지 어렵네요.

 

 

inner join을 사용해서 푸는 쿼리는 다음과 같습니다.

 

 

2) inner join 사용

select od.OrderID, 
	sum(od.Quantity * p.Price)
from OrderDetails od
inner join Products p on od.ProductID = p.ProductID
group by od.OrderID

 

---

 

[문제 8] 전체 상품 데이터를 출력하되, 공급사가 일본에 위치한 상품 공급사 ID만 표시하고 나머진 null로 표시 (인라인뷰 사용)

select p.ProductName,
	s.SupplierID,
	p.CategoryID,
	p.Unit,
	p.Price
from Products p
left join 
(select SupplierID from Suppliers
where Country = 'Japan') s
on p.SupplierID = s.SupplierID

 

 

인라인뷰에 사용된 테이블 내용은 다음과 같습니다.

select SupplierID 
from Suppliers
where Country = 'Japan'

일본에 위치한 공급사는 4, 6으로 2개입니다.

그렇기 때문에 left join을 사용해서 공급사의 위치가 일본이 아닌 경우 null로 표시하기 위해선 메인 쿼리에 s.SupplierID를 호출해야 합니다. 

 

---

 

[문제 9] 전체 직원의 EmployeeID, LastName, FirstName, OrderYN을 추출 (1997년 2월 판매 이력이 있는 경우 OrderYN 'Y' 없는 경우 'N'으로 표시) (인라인뷰 사용)

select distinct e.EmployeeID, e.LastName, e.FirstName, 
case when o.OrderDate is null then 'N'
	else  'Y'
	end OrderYN
from Employees e
left join 
(select EmployeeID, OrderDate 
from Orders
where OrderDate like '1997-02-%') o
on e.EmployeeID = o.EmployeeID
order by e.EmployeeID

 

---

 

[문제 10] 카테고리명과 해당 카테고리 상품 중 가장 비싼 상품의 가격 추출 (인라인뷰 사용)

select c.CategoryName, p.Price
from Categories c
inner join 
(select CategoryID, max(Price) as Price
from Products
group by CategoryID) p
on c.CategoryID = p.CategoryID

 

---

 

[문제 11] 주문 이력이 없는 고객의 모든 정보 추출 (중첩 서브쿼리 사용)

select * from Customers
where CustomerID not in (select distinct CustomerID 
			 from Orders)

 

---

 

[문제 12] 동일 주문 건에서 가장 주문 수량이 많은 상품의 주문 데이터만 추출 (주문 ID, 상품  ID, 주문 수량) (중첩 서브쿼리 사용)

 

 

이 문제는 쿼리를 짜기 전 우선적으로 확인해야 할 부분이 존재하는데, 바로 고유 주문수입니다.

 

OrderDetails 테이블을 살펴보면 각 OrderID에 따른 제품 주문수량을 알 수 있습니다.

이때 OrderID = 10252인 경우를 보면, 서로 다른 상품을 동일하게 max개 (40개) 주문을 했습니다.

문제에서 구하고자 하는 내용은 동일 주문 건에서 max 주문수량을 가진 행을 추출하는 것이기 때문에 이를 추출하면 고유 주문수 보다 더 많은 행이 추출되어야 합니다. 

 

 

1) 고유 주문수 확인

select count(distinct OrderID) as cnt
from OrderDetails;

 

 

2) 주문 건에 따른 max 주문수량 추출

select OrderID, ProductID, Quantity
from OrderDetails
where (OrderID, Quantity) in (select OrderID, max(Quantity) 
			      from OrderDetails 
			      group by OrderID)

OrderID = 10252를 보면 max Quantity가 40이기 때문에 2건이 출력된 것을 알 수 있습니다.

 

 

 

3) 다른 풀이

select a.OrderID, a.ProductID, a.Quantity 
from OrderDetails a
where a.Quantity = (select max(b.Quantity)
		    from OrderDetails b
		    where a.OrderID = b.OrderID)

 

---

 

[문제 13] 배송사와 공급사 데이터 중 전화번호가 동일한 곳의 전화번호 추출

 

1) 중첩 서브쿼리 사용

select Phone
from Shippers
where Phone in (select Phone from Suppliers);

 

 

 

2) 합집합 & 인라인뷰 사용

select t.Phone
from (select Phone from Suppliers
	union all
	select Phone from Shippers) t
group by t.Phone
having count(*) > 1