Healthy Shop database
4. Get to know the nutrition_data table
MULTIPLE JOINs – Exercises

Instruction

The shop really wants to keep all its customers well informed. That's why every product has a calories label available. The information about the calorie count of a product is stored in the nutrition_data table.

We have three tables to work with. That means we can join all three of them in a single query which allows us to select data from these three tables at the same time. Take a look:

SELECT
  *
FROM product
JOIN department
  ON product.department_id = department.id
JOIN nutrition_data
  ON nutrition_data.product_id = product.id
WHERE department.name = 'bakery'

We first join the product table with the department table and then join that resulting table with the nutrition_data table.

Exercise

The nutrition_data table consists of the following five columns:

  • product_id – the ID of a given product,
  • carbohydrates – the amount of carbohydrates in a given product,
  • proteins – the amount of protein in a given product,
  • fats – the amount of fat in a given product,
  • calories – the calorific value of a given product.

Let's use this table right away. Show the name of each product and its calorific value for all products that are in the 'dairy' department.

Stuck? Here's a hint!

You have to join all 3 tables.

Type:

SELECT
  product.name,
  nutrition_data.calories
FROM product
JOIN department
  ON product.department_id = department.id
JOIN nutrition_data
  ON nutrition_data.product_id = product.id
WHERE department.name = 'dairy'

Console

Code editor

Result

TableConsole