Alvin Naufal: Assignment SQL Query Part 2

query2.sql

/**
*  Alvin Naufal
*/

-- 6. Tampilkan kuantitas barang yang di order dengan nama produk = Geitost
SELECT O.Quantity FROM OrderDetails O
	JOIN Products P
    	ON O.ProductID = P.ProductID
WHERE P.ProductName = 'Geitost';

-- 7. Tampilkan jumlah produk berdasarkan kategory
SELECT C.CategoryName, COUNT(P.ProductID) AS TotalProduct FROM Products P
	JOIN Categories C
    	ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName;

-- 8. Tampilkan seluruh customer yang ada memesan maupun tidak memesan dan tampilkan juga tanggal pemesanan
SELECT C.CustomerName, O.OrderDate FROM Customers C
	LEFT JOIN Orders O
    	ON C.CustomerID = O.CustomerID;

-- 9. Tampilkan jumlah pemesanan berdasarkan shipperName
SELECT S.ShipperName, COUNT(O.OrderID) AS TotalOrder FROM Orders O
	JOIN Shippers S
    	ON O.ShipperID = S.ShipperID
GROUP BY S.ShipperName;

-- 10. Tampilkan nama supplier yang total harga produknya dibawah 100
SELECT S.SupplierName, SUM(P.Price) AS TotalPrice FROM Products P
	JOIN Suppliers S
    	ON P.SupplierID = S.SupplierID
GROUP BY S.SupplierName
HAVING TotalPrice < 100;