Day 6 – Using INNER JOIN to Combine Data from Multiple Tables

For test exercise we’ll use the PizzaDB. You can find link to the database script on GIT.

You don’t need to have a database installed to do these exercise, just use the prompts below.

Exercise #1

Lou, from Lou’s Pizza shop would like to see a report of which customer are ordering which products.  To get started with this we’ll use the Product and CustomerOrderItem tables.

The Product table has the following columns:

• ProductID
• ProductName
• ProductType
• Price

CustomerOrderItem table has the following columns

• CustomerOrderItemID
• CustomerOrderID
• ProductID
• Quantity
• SpecialInstructions

Can you create a query to return the following columns?

• CustomerOrderItemID
• CustomerOrderID
• ProductName
• Price
• Quantity

/* Answer */
select CustomerOrderItemID, CustomerOrderID, ProductName, Price, Quantity
from CustomerOrderItem c
    inner join Product p on c.ProductID = p.ProductID
where p.ProductType = 'P'

Hint: You’ll want to join on the ProductID between the CustomerOrderItem and Product tables.

Show the Answer to see the query I wrote.

Notice that I used aliases to make it easier for me to type and read the query.

Exercise #2

Create a query combining customer and customer order information.   For this query we’ll use the CustomerOrder and Customer tables.

The CustomerOrder table has the following columns

• CustomerOrderID
• CustomerID
• OrderTakerID
• OrderDate
• CouponID

And Customer has the following columns:

• CustomerID
• PhoneNumber
• Email
• LastName
• StreetAddress
• City
• StateProvidence
• PostalCode

For your query include the following:

• CustomerOrderID
• OrderDate
• LastName
• StreetAddress

Sort your result by OrderDate and LastName.

/* Answer */
Select o.CustomerOrderID, o.OrderDate, c.LastName, c.StreetAddress
from CustomerOrder o
    inner join Customer c on o.CustomerID = c.CustomerID
order by OrderDate, LastName

For this query to work, you’ll want to join on CustomerID.

Exercise #3

Create a query to return the following:

OrderDate, LastName, StreetAddress, ProductName, Quantity, Price, TotalAmount

where the TotalAmount is the Quantity times Price.

order the result by TotalAmount in descending order

You can use the Product, CustomerOrderItem, CustomerOrder, and Customer tables to build your query.

The Product table has the following columns:

• ProductID
• ProductName
• ProductType
• Price

CustomerOrderItem table has the following columns

• CustomerOrderItemID
• CustomerOrderID
• ProductID
• Quantity
• SpecialInstructions

The CustomerOrder table has the following columns

• CustomerOrderID
• CustomerID
• OrderTakerID
• OrderDate
• CouponID

And Customer has the following columns:

• CustomerID
• PhoneNumber
• Email
• LastName
• StreetAddress
• City
• StateProvidence
• PostalCode

/* Answer */
Select OrderDate, LastName, StreetAddress, ProductName, Quantity, Price, Quantity * Price TotalAmount
from CustomerOrder o
    inner join Customer c on o.CustomerID = c.CustomerID
    inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID
    inner join Product p on i.ProductID = p.ProductID
order by Quantity * Price desc

Show to Answer to see the query I wrote.

I used aliases to make it easier to read.

Also, I made sure I was able to join from one table to the next.  Notice how product is the last table for me to join.  I can only join to it once I have joined to CustomerOrderItem.