Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

JOINs in MS SQL Server

Healthy Shop database
MULTIPLE JOINs – Exercises
10. Exercise 6

Instruction

You're doing great! Only a few more to go.

Exercise

For every sale that happened between 2015-01-15 and 2015-02-21, show:

  • the date of the sale,
  • the name of the producer (rename the column to CompName),
  • product name - rename the column to ProductName,
  • the total price for this product (calculated using the price per unit and amount). Alias the column to TotalPrice.

Stuck? Here's a hint!

That's a bit trickier one.

In order to get the correct result range, you have to filter the result by date. To do so, you can use the BETWEEN … AND … clause:

WHERE Date BETWEEN '2015-01-15' AND '2015-02-21'

Type: 

SELECT
  Sh.Date,
  Prod.Name AS CompName,
  P.Name AS ProductName,
  Amount * Price AS TotalPrice
FROM Product P
JOIN Producer Prod
  ON P.ProducerId = Prod.Id
JOIN SalesHistory Sh
  ON Sh.ProductId = P.Id
WHERE Date BETWEEN '2015-01-15' AND '2015-02-21'