<Sample DB를 활용하여 SQL 문제 직접 만들기>
w3schools
위 사이트는 SQL을 공부할 수 있는 tutorial 사이트입니다.
사이트에 존재하는 Sample DB를 활용하여 SQL 문제를 직접 만들었습니다. Sample Table의 구조 및 테이블 간의 관계를 익힌 후 문제를 풀어보세요!
스키마 설명
| 스키마 | 의미 |
|---|---|
| Customers | 고객 |
| Categories | 제품의 카테고리 정보(음료수, 양념 등) |
| Employees | 종업원 |
| OrderDetails | 주문상세 정보(어떤 주문에 대한 주문 내역) |
| Orders | 주문 정보 |
| Products | 제품 정보 |
| Shippers | 배달자 정보 |
| Suppliers | 공급자 정보 |
고객:Customers(CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
제품의 카테고리정보:Categories(CategoryID, CategoryName, Description)
종업원:Employees(EmployeeID, LastName, FirstName, BirthDate, Photo)
주문상세정보:OrderDetails(OrderDetailID, OrderID, ProductID, Quantity)
주문정보:Orders(OrderID, CustomerID, EmployeeID, OrderDate, ShipperID)
제품정보:Products(ProductID, ProductName, SupplierID, CategoryID, Unit, Price)
배달자:Shippers(ShipperID, ShipperName, Phone)
공급자:Suppliers(SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone)
#### 문제1 제품의 가격이 100에서 200 사이인 제품의 정보를 출력하라.
select *
from products p
where p.price between 100 and 200;
문제2
3 개 이상의 제품 카테고리에서 제품을 구매한 고객의 이름을 출력하라.
select cu.customername
from customers cu
inner join orders o on cu.customerid=o.customerid
inner join orderdetails od on o.orderid=od.orderid
inner join products p on od.productid=p.productid
group by cu.customerid, cu.customername
having count(distinct p.categoryid)>=3;
문제3
제품 Ipoh Coffee보다 가격이 비싼 제품의 이름과 ,가격, 카테고리 이름을 가격 의 오름차순으로 출력하라.
select p.productname, p.price, ca.categoryname
from products p, categories ca
where p.categoryid=ca.categoryid and p.price >
(select p.price from products p where p.productname="Ipoh Coffee")
order by p.price;
문제4
캐나다 국적인 공급자를 통해 공급된 제품들의 총 금액(price)합계가 50이 넘는 경우, 공급자 아이디와 공급자 이름, 총금액을 출력하라.
select su.supplierid,su.suppliername, sum(p.price)"총금액"
from products p, suppliers su
where su.country="Canada" and p.supplierid=su.supplierid
group by su.supplierid,su.suppliername
having sum(p.price)>50;
문제5
제품을 가장 많이(quantity) 구매한 1,2,3 등 고객에게 상품권을 증정하려고 한다. 고객의 아이디, 고객의 이름, 주문아이디, 주문 횟수의 총합을 출력하라. (단, 주문횟수의 총합 내림차순으로 1위부터 3위까지 출력 할 것)
select cu.customerid, cu.customername, o.orderid, sum(od.quantity)
from customers cu,orderdetails od, orders o
where cu.customerid=o.customerid and o.orderid=od.orderid
group by o.orderid
order by sum(od.quantity) desc
limit 0,3;
문제6
판매왕 종업원을 알아보고자 한다. 각 종업원의 종업원 아이디와 판매 고객의 수를 고객의 수의 내림차순으로 출력하라. (단, 고객의 수가 20명이 넘는 종업원만 출력할 것)
select e.employeeid,count(cu.customerid) "고객의 수"
from employees e, customers cu, orders o
where cu.customerid=o.customerid and o.employeeid=e.employeeid
group by e.employeeid
having count(cu.customerid) >=20
order by count(cu.customerid) desc;
문제7
1996년 10월 동안에 배달한 배달자의 아이디, 배달자의이름과 배달횟수를 출력하라.
(단 배달자의 아이디 오름차순으로 출력 할 것)
select sh.shipperid,sh.shippername, count(o.orderid) "배달횟수"
from shippers sh, orders o
where o.shipperid=sh.shipperid and o.orderdate like '1996-10%'
group by sh.shippername
order by sh.shipperid;
문제8
각 카테고리별로 가장 비싼 제품을 찾으려고 한다.
카테고리이름과 제품아이디, 제품 가격을 출력하라, (단 가격의 내림차순으로 출력 할 것)
select ca.categoryname, p.productid, p.price
from products p, categories ca
where p.categoryid=ca.categoryid and
(ca.categoryname, p.price) in
(select ca.categoryname, max(p.price)
from products p, categories ca
where p.categoryid=ca.categoryid
group by ca.categoryname)
order by p.price desc;
문제9
제품 Tofu와 같은 카테고리에 속한 제품들의 평균 제품가격보다 비싼 가격을 가진 제품의 제품아이디,제품명과 가격을 출력하라. (단, 가격의 내림차순으로 출력 할 것)
select p.productid,p.productname ,p.price
from products p, categories ca
where ca.categoryid=p.categoryid and p.price >(
select avg(p.price) from products p where ca.categoryid=(select p.categoryid
from products p where p.productname="Tofu"))
order by p.price desc;
문제10
제품 이름이 10글자인 제품 중 가격이 가장 비싼 제품의 카테고리이름, 제품이름, 가격을 출력하라.
(단, 제품의 이름의 길이는 띄어쓰기도 포함한다.)
select ca.categoryname, p.productname, p.price
from categories ca, products p
where ca.categoryid=p.categoryid
and p.price=(select max(p.price) from products p where length(p.productname)=10)
and length(p.productname)=10;
문제11
각 카테고리별 제품가격의 평균, 총제품가격, 최고가격,최저가격을 구하여 제품가격의 평균의 내림차순으로 카테고리id,카테고리이름,평균가격, 제품총가격, 최고가격, 최저가격을 출력하라.
select p.categoryid, ca.categoryname, avg(p.price)"제품가격의 평균", sum(p.price)"제품총가격",max(p.price)"최고가격",min(p.price)"최저가격"
from products p, categories ca
where p.categoryid=ca.categoryid
group by p.categoryid
order by avg(p.price) desc;
문제12
제품 중 두번째로 비싼 제품의 정보를 출력하라.
select *
from products
order by price desc
limit 1,1;
문제13
제품 Tofu의 가격에서 제품 Konbu의 가격을 뺀 결과 값보다 저렴한 가격의 제품의 카테고리이름과 제품아이디와 제품이름,가격을 출력하라.
SELECT ca.categoryname, p.productid , p.productname , p.price
FROM products p , categories ca
WHERE p.categoryid = ca.categoryid
AND p.price < (SELECT price FROM products WHERE productname = 'Tofu' )
- (SELECT price FROM products WHERE productname = 'Konbu');
문제14
London 도시에 사는 고객의 주문 정보를 알아 보고자 한다. 고객이름과 주문금액의 평균을 출력하라. (단, 주문금액의 평균의 내림차순으로 출력 할 것, 주문 이력이 없는 고객의 정보는 출력 하지 말 것)
select cu.customername, avg(p.price*od.quantity)
from products p, orderdetails od, customers cu, orders o
where cu.city='London' and p.productid=od.productid and
o.customerid=cu.customerid and od.orderid=o.orderid
group by cu.customername
order by avg(p.price*od.quantity) desc,cu.customername;
문제15
전체 고객의 10% 이상이 구매한 제품의 제품아이디, 제품이름, 카테고리아이디,카테고리이름, 고객아이디, 고객이름을 출력하라.
SELECT DISTINCT p.productid , p.productname , ca.categoryid, ca.categoryname, cu.customerid , cu.customername
FROM (SELECT od.productid
FROM orderdetails od
INNER JOIN orders b
ON od.orderid = b.orderid
GROUP BY od.productid
HAVING COUNT(DISTINCT b.customerid) >= (SELECT COUNT(*) * 0.1 FROM customers)
) od
INNER JOIN products p
ON od.productid = p.productid
INNER JOIN categories ca
ON p.categoryid = ca.categoryid
INNER JOIN orderdetails d
ON od.productid = d.productid
INNER JOIN orders e
ON d.orderid = e.orderid
INNER JOIN customers cu
ON e.customerid = cu.customerid
;
