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