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

SQL JOINs

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-01-22, show:

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

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-01-22'

Type:

SELECT
  sh.date,
  prod.name AS comp_name,
  p.name AS product_name,
  amount * price AS total_price
FROM product p
JOIN producer prod
  ON p.producer_id = prod.id
JOIN sales_history sh
  ON sh.product_id = p.id
WHERE date BETWEEN '2015-01-15' AND '2015-01-22';