전산 교육 정리_SQL(2)

각 주문별로 판매된 상품명이 궁금합니다. 같이 출력해 보세요

SELECT OrderDetailID, OrderID, ProductID, ProductName
FROM orderdetails
	INNER JOIN products 
		USING(productid)
ORDER BY orderdetailid;

상품의 상품별로 판매수량과 금액을 출력하세요.

SELECT orderdetailid, orderid, productid, productname, price, quantity, price*quantity as 금액
FROM orderdetails
	INNER JOIN products
    	USING(productid)
ORDER BY orderdetailid;

주문에 대해 상품별로 판매수량과 금액을 출력하되 제품 ID가 4보다 작은 경우만 선별하려면

SELECT orderdetailid, orderid, productid, productname, price, quantity, 
price*quantity as 금액
FROM orderdetails 
INNER JOIN products 
USING(productid)
WHERE productID < 4
ORDER BY orderdetailid;

제품명이 Filo Mix 또는 Chang인 제품을 공급자 이름과 같이 출력하세요

[ using(supplierID) 활용 ]

SELECT p.ProductName, s.SupplierName
FROM products p, suppliers s 
WHERE (p.ProductName = "Filo Mix" OR p.ProductName="Chang" )
and p.supplierID=s.supplierID;

SELECT p.productName, s.supplierName
FROM products p 
INNER JOIN suppliers s using(supplierID)
WHERE p.productname="Filo Mix" or p.productname="Chang" ;

주문별 제품의 판매금액을 기준으로 20%의 영업 수당을 산출해 보세요.

SELECT orderdate, employeeid, 
	orderid, (quantity* price) as 금액, Round(quantity* price*0.2, 2) as 수당
FROM orders INNER JOIN orderdetails USING(orderid)
		INNER JOIN products USING(productid) 
ORDER BY orderid;

+ 영업사원 이름도 같이 출력되게 하려면 ? -> concat()함수 활용(lastname, firstname 합성)

SELECT orderdate, employeeid, concat(LastName, FirstName),
	orderid, (quantity* price) as 금액, Round(quantity* price*0.2, 2) as 수당
FROM orders INNER JOIN orderdetails USING(orderid)
		INNER JOIN products USING(productid) INNER JOIN employees USING(EmployeeID)
ORDER BY orderid;

각 주문의 상품별 금액이 4000$이상인 제품을 출력해 보세요

SELECT OrderDetailID, OrderID, ProductID, ProductName, Quantity * Price as "금액"
FROM OrderDetails
INNER JOIN Products USING(ProductID) 
WHERE (Quantity * Price) >= 4000 
ORDER BY OrderDetailID;

각 주문의 상품별 금액이 4000$이상 제품들이 어떤 류인지 카테고리가 궁금합니다. 출력해 보십시요. !! 수정 !! 

SELECT OrderDetailID, OrderID, ProductID, ProductName, Quantity * Price as "금액", CategoryID
FROM OrderDetails
INNER JOIN Products USING(ProductID) INNER JOIN Categories USING(categoryID)
WHERE (Quantity * Price) >= 4000 
ORDER BY OrderDetailID;

아무런 시험도 보지 않은 사람 출력 (UNION)

힌트 : 어떤 시험이든 치른사람을 a라 할 때 - 전체명단과 a를 left조인하면??

진학반명단 원부 left join 어떤 시험이든 치른 사람 -> 아무런 시험도 보지 않은 사람 출력

select * from 진학반명단
	left join (select * from 입시생 union
    select * from 자격증응시생) as 응시생 using(id)
where 응시.id is null;

 

[ Group by ]

- 앞에서 주문된 상품별로 금액과 20%의 수당을 산출한 바 있습니다. 주문별로 합계를 산출해 보세요

SELECT OrderDate, EmployeeID, OrderID, 
sum(quantity* price) as 금액, round(sum(quantity*price*0.2), 2) as 수당
FROM Orders INNER JOIN OrderDetails using(OrderID)
INNER JOIN Products using(ProductID)
GROUP BY OrderID ORDER BY OrderID;

영업사원별 주문 성사금액이 40,000$ 이상인 사람에 대해 25%의 특별수당을 산출해 보세요

SELECT EmployeeID, 
	round(sum(quantity* price), 2) as 금액, 
	round(sum(quantity* price*0.25), 2) as 특별수당
FROM Orders INNER JOIN OrderDetails using(OrderID)
INNER JOIN Products using(ProductID)
GROUP BY EmployeeID
HAVING sum(quantity * price) >= 40000;

제품번호에 맞춰 제품명과 판매 수량을 출력해 보세요

SELECT ProductID, ProductName,sum(Quantity) as 판매수량
FROM OrderDetails
INNER JOIN Products using(ProductID) 
group by ProductID 
order by ProductID;

상품별로 주문에 등장한 횟수가 큰것부터 나열해 보세요

SELECT ProductID, ProductName, COUNT(*) AS 주문횟수
FROM OrderDetails
INNER JOIN Products using(ProductID) 
group by ProductID 
order by COUNT(*) DESC, productid asc;

판매금액 기준으로 봤을 때 기여도가 높은 제품 10개만 출력해 보세요.

SELECT p.productid, p.productname
, sum(p.price * o.quantity) as 판매금액
FROM orderdetails o 
INNER JOIN products p using(productid) 
group by p.productid, p.ProductName
order by 판매금액 desc limit 10;

조건에 따라 처리하기(Case When)

SELECT 가격분류, count(Price) 
FROM
( SELECT Price, 
CASE WHEN Price <=20 THEN "1.초저가류"
WHEN Price>20 AND Price <=35 THEN "2.저가류" 
WHEN Price >35 AND Price <=50 THEN "3.중가류" 
WHEN Price >50 AND Price <=80 THEN "4.고가류" 
ELSE "5.초고가류" 
END AS 가격분류
FROM Products) as p 
GROUP BY 가격분류;

< 연산도우미 –함수(Function)사용 >

1) Insert("BEAUTIFUL", 2, 4, "KK") : BKKIFUL (문자열의 부분문자열 교체, 2번째부터 4개)

2) Instr("BEAUTIFUL", "AUT") : 3 (부분문자열 위치, 없으면 0)

3) dateDiff('2022-08-15', '2022-09-03') : -19 ( 날짜 차이를 반환)

Products 테이블에서 가격(Price)이 가장 큰 제품의 거래현황을 출력

SELECT
	@mp:=max(price)
FROM products;

SELECT ProductID, ProductName, Price INTO @id, @n, @p
FROM Products 
ORDER BY Price DESC limit 1

SELECT *
FROM products
WHERE price = @mp;

SELECT *, @n as 품명, @p as 단가
FROM OrderDetails 
WHERE ProductID =@ID;