Alvin Naufal: Assignment SQL Query Part 3

query3.sql

/**
*  Alvin Naufal
*/

-- 11. Tampilkan nama produk dan nama category yang termasuk kategori Beverages, Dairy Products, dan Condiments
SELECT P.ProductName, C.CategoryName FROM Products P
	INNER JOIN Categories C
    	ON P.CategoryID = C.CategoryID
WHERE C.CategoryName = 'Beverages' 
	OR C.CategoryName = 'Dairy Products' 
  OR C.CategoryName = 'Condiments';
  
-- 12. Tampilkan nama customer, nama shipper, dan nomor telpon shipper untuk pemesanan pada tanggal 1996-07-08
SELECT C.CustomerName, S.ShipperName, O.OrderDate 
FROM Orders O
	INNER JOIN Customers C
    	ON O.CustomerID = C.CustomerID
    INNER JOIN Shippers S
    	ON O.ShipperID = S.ShipperID
WHERE OrderDate = '1996-07-08';

-- 13. Tampilkan seluruh employee yang tidak memesan
SELECT E.FirstName, E.LastName, O.OrderID FROM Employees E
	LEFT JOIN Orders O
    	ON E.EmployeeID = O.OrderID;
      
-- 14. Tampilkan namaProduk, unit, harga, kuantitas, namaCustomer, namaShipper, negaraShipper untuk nama produk 'Queso Cabrales' dan tanggal pemesanan setelah tahun 1996
SELECT P.ProductName, P.Unit, P.Price, OD.Quantity, O.OrderDate, C.CustomerName, S.ShipperName, C.Country 
FROM Products P
    INNER JOIN OrderDetails OD
    	ON P.ProductID = OD.ProductID
    INNER JOIN Orders O
    	ON O.OrderID = OD.OrderID
    INNER JOIN Customers C
    	ON O.CustomerID = C.CustomerID
    INNER JOIN Shippers S
    	ON O.ShipperID = S.ShipperID
WHERE P.ProductName = 'Queso Cabrales' AND O.Orderdate > '1996-12-31';

-- 15. Tampilkan setiap employee dan maksimum kuantitas yang employee melakukan pemesanan terbanyak
SELECT E.FirstName, MAX(OD.Quantity) AS MaxQty 
FROM Orders O
	INNER JOIN OrderDetails OD
    		ON O.OrderID = OD.OrderID
	INNER JOIN Employees E
    		ON O.EmployeeID = E.EmployeeID
GROUP BY E.Firstname;