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

PostgreSQL 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-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'