Bakery DML Queries


Q1: List the bakery orders for a given time period?

By entering a range of dates, all of the orders that are due within that time range, will be displayed. In this example, the time period is for the month of December, and we are able to see all the orders that are due for this month as well as the collection type method.

Query:

 SELECT *
     FROM Order_T
     WHERE OrderDate < '2014-01-00'
     AND OrderDate > '2013-12-00';

Resultset:

Q2: What is a given customer's order history?

It is necessary to view a list of all the orders that a specific customer has made in order to keep track of their history. In this example, we check the customer whose ID is '2' in order to view all of this customer's online orders.

Query:

 SELECT C.name, O.*
     FROM Order_T O JOIN Customer C ON (O.CustomerID = C.CustomerID)
     WHERE C.CustomerID = 2;

Resultset:

Q3: What is the most popular bakery item?

In order to determine how much of a specific item needs to be made, it is important to analyze the most popular bakery items.

Query:

 SELECT I.*, SUM(Quantity) AS Total
     FROM OrderLine O JOIN Item I ON (O.ItemID = I.ItemID)
     GROUP BY ItemId ORDER BY Total DESC LIMIT 1;

Resultset:

Q4: What is the name of the most loyal customer?

Customer loyalty is key to any business. As a result, by determining what customer has made the most orders, our business can increase it's returning customers rate. In this query, Saul Goodman has made the most orders with a total of 5 orders and is thus our most loyal online orders customer.

Query:

 SELECT C.CustomerID, Name, COUNT(OrderID) as NumOrders
     FROM Order_T O JOIN Customer C ON (O.CustomerID = C.CustomerID)
     GROUP BY CustomerID
     ORDER BY NumOrders DESC LIMIT 1;

Resultset:

Q5: What is the most common type of payment?

Since a customer can pay with credit, cash, or check, we need to keep track of the most popular methods of payment. In this query, we are able to determine that cash is the most popular method of payment, with a total of 14 orders being paid in cash.

Query:

 SELECT Method, COUNT(PaymentID) AS Count
     FROM Payment
     GROUP BY Method
     ORDER BY Count DESC
     LIMIT 1;

Resultset:

Q6: What is the total projected revenue?

As a business, it is imperative to keep track of finances, it important to track how much projected revenue that the online orders will bring into the bakery business.

Query:

 SELECT SUM(Total) AS Revenue FROM
  (SELECT OrderID, SUM(Subtotal) AS Total FROM
    (SELECT OrderID, O.ItemID, ItemName, (Quantity*UnitPrice) AS Subtotal
        FROM OrderLine O JOIN Item I ON (O.ItemID = I.ItemID)
        GROUP BY OrderID, O.ItemID) AS X
        GROUP BY OrderID) AS Y;

Resultset:

Q7: How many of a given item were ordered?

By entering a specific Item ID, it is important to analyze how many times that item has been ordered. For this example, we look at ItemID 9 to determine the total number of times that Blueberry Pie has been ordered.

Query:

 SELECT ItemID, ItemName, Total FROM
   (SELECT I.*, SUM(Quantity) AS Total
       FROM OrderLine O JOIN Item I ON (O.ItemID = I.ItemID)
       GROUP BY ItemId) AS X
       WHERE ItemID = 9;

Resultset:

Q8: What is the total price for each order?

It is key to know the total price of each order to determine information that may be important for furture orders.

Query:

 SELECT OrderID, SUM(Subtotal) AS TotalPrice FROM
   (SELECT OrderID, O.ItemID, ItemName, (Quantity*UnitPrice) AS Subtotal
      FROM OrderLine O JOIN Item I ON (O.ItemID = I.ItemID)
      GROUP BY OrderID, O.ItemID) AS X
      GROUP BY OrderID;

Resultset:

Changes from previous Deliverables: