Critical concepts in a database
SELECT OL.OutletLocation AS "Outlet Name", DATE_FORMAT(D.DeliveryDateAndTime, '%d/%m/%Y %H:%i') AS "Delivery Date and Time",
ORD.OrderNumberID AS "Order Number",
MI.FoodItemDescription AS "Item Not Available", U.UserName AS "Customer Name", U.ContactNumber AS "Contact Number"
FROM ((((((User U
INNER JOIN `Order` ORD ON ORD.UsernameEmail = U.UsernameEmail)
INNER JOIN Delivery D ON D.DeliveryID = ORD.DeliveryID)
INNER JOIN Order_has_MenuItems OM ON OM.OrderNumberID = ORD.OrderNumberID)
INNER JOIN MenuItems MI ON MI.MenuItemID = OM.MenuItemID)
INNER JOIN Outlet_has_MenuItems OLM ON OLM.MenuItemID = MI.MenuItemID)
INNER JOIN Outlet OL ON OL.OutletID = OLM.OutletID)
WHERE ORD.OrderStatusID = 000001
AND OLM.ItemStockQuantity = 0
AND ORD.OutletID = OLM.OutletID
ORDER BY OL.OutletLocation ASC, D.DeliveryDateAndTime, ORD.OrderNumberID, ORD.TimeOfCollection;
SELECT E.EventTitle AS "Event Name", DATE_FORMAT(E.EventStartDateTime, '%d/%m/%Y') AS "Event Date",
COUNT(ESU.EventID) AS "Number of Participants"
FROM (Events E
Left JOIN Events_has_User ESU ON ESU.EventID = E.EventID)
GROUP BY E.EventTitle, E.EventStartDateTime
ORDER BY COUNT(ESU.EventID) DESC;
SELECT OL.OutletLocation AS "Outlet", OL.OutletManager AS "Outlet Manager", SUM(MIHS.UnitPrice * OM.OrderQuantity) AS "Total Sales ($)"
FROM (((((Outlet OL
INNER JOIN Outlet_has_MenuItems OHM ON OHM.OutletID = OL.OutletID)
INNER JOIN MenuItems MI ON MI.MenuItemID = OHM.MenuItemID)
INNER JOIN MenuItems_has_Size MIHS ON MIHS.MenuItemID = MI.MenuItemID)
INNER JOIN Order_has_MenuItems OM ON OM.MenuItemID = MI.MenuItemID)
INNER JOIN `Order` ORD ON ORD.OrderNumberID = OM.OrderNumberID)
WHERE ORD.OrderStatusID = 000004
AND OHM.ItemStockQuantity > OM.OrderQuantity
AND MONTH(ORD.DateOfOrder) = 12
GROUP BY 1, 2
ORDER BY 3 DESC;
SELECT MI.FoodItemDescription AS "Item", SUM(OM.OrderQuantity) AS "Quantity Sold"
FROM ((MenuItems MI
INNER JOIN Order_has_MenuItems OM ON OM.MenuItemID = MI.MenuItemID)
INNER JOIN `Order` ORD ON ORD.OrderNumberID = OM.OrderNumberID)
WHERE OM.OrderQuantity < 10 AND MONTH(ORD.DateOfOrder) = MONTH(CURDATE()) - 1
GROUP BY 1
ORDER BY 2;
SELECT DATE_FORMAT(DateOfOrder, '%M %Y') AS "Month", SUM(MIHS.UnitPrice * OM.OrderQuantity) AS "Total Amount Spent ($)",
CAST(AVG(MIHS.UnitPrice * OM.OrderQuantity) AS DECIMAL (10,2)) AS "Average Amount per Order ($)",
MIN(MIHS.UnitPrice * OM.OrderQuantity) AS "Minimum Amount of an Order ($)",
MAX(MIHS.UnitPrice * OM.OrderQuantity) AS "Maximum Amount of an Order ($)"
FROM (((`Order` ORD
INNER JOIN Order_has_MenuItems OM ON OM.OrderNumberID = ORD.OrderNumberID)
INNER JOIN MenuItems MI ON MI.MenuItemID = OM.MenuItemID)
INNER JOIN MenuItems_has_Size MIHS ON MIHS.MenuItemID = MI.MenuItemID)
WHERE MONTH(ORD.DateOfOrder) BETWEEN 10 AND 12
GROUP BY MONTH(ORD.DateOfOrder);