Problem #1

The goal is to write a report of orders, along with the names of the customers who placed the orders.

• The challenge is the fact that the customer names are not stored in the Orders table. The names are in the Customers table.
  • By the way, that's a good design. Later this semester, we'll learn why
• So to accomplish our goal, we need to use a JOIN so that our SELECT statement has access to both the Orders table and the Customers table.

Start with this SQL, and edit it to complete the goal below it:

SELECT o.OrderID, o.CustomerID FROM Orders AS o 

• Modify that SQL so that the 3rd column returned is the customer name for that order. That will require a JOIN to the Customers table.
• Notice that I aliased the Orders table as o You will alias the Customers table as c
• So, that 3rd column in the SELECT will be c.CustomerName

When completed, your output should look like this:

Problem #2

The goal is to write a report of order detail records, along with the names of the product being ordered.

• The challenge is the fact that the product names are not stored in the OrderDetails table. The names are in the Products table.
• So to accomplish our goal, we need to use a JOIN so that our SELECT statement has access to both the OrderDetails table and the Products table.

Start with this SQL, and edit it to complete the goal below it:

SELECT od.OrderID FROM OrderDetails AS od 

• Modify that SQL so that the 2nd column returned is the product name for that order detail. That will require a JOIN to the Products table.
• Notice that I aliased the OrderDetails table as od You will alias the Products table as p
• So, that 3rd column in the SELECT will be p.ProductName

When completed, your output should look like this:

Problem #3

The goal is to write a report of order 10251. You will list information about each product on the order.

• Here is pertinent information about the tables involved
  • Each Orders record represents one order placed by a customer
    • It contains an OrderID, which identifies the order
  • Each OrderDetails record holds one item that has been sold
    • One of its columns is the ProductID of the sold item
    • Another one of its columns is the OrderID for the order it belongs to
    • That is important when you do your JOINs
  • Each Products record holds one item that is for sale

Start with this SQL, and edit it to complete the goal below it:

SELECT o.OrderID, FROM Orders AS o WHERE o.OrderID = 10251 

• Modify that SQL so that these columns are also outputted:
  • OrderID
  • ProductName of that item on the order
  • Quantity of that item on the order
  • The subtotal (Price * Quantity)
    • Name that column 'Subtotal'
• This will require two JOINs

• Notice that I aliased the Orders table as o
  • You will alias the Products table as p
  • You will alias the OrderDetails table as od

When completed, your output should look like this: