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 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-02-21'

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-02-21'

Console

Code editor

Result

TableConsole