Last active
April 18, 2022 13:42
-
-
Save bl4ckck/20b8379001859e53fb6e456c03afd1ba to your computer and use it in GitHub Desktop.
Alvin Naufal: Assignment SQL Query Part 3
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment