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,carbohydrate– the amount of carbohydrates in a given product,protein– the amount of protein in a given product,fat– 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'
