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 NutritionData 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.DepartmentId = Department.Id JOIN NutritionData ON NutritionData.ProductId = Product.Id WHERE Department.Name = 'bakery';
We first join the Product table with the Department table and then join that resulting table with the NutritionData table.
Exercise
The NutritionData table consists of the following five columns:
ProductId– 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, NutritionData.Calories FROM Product JOIN Department ON Product.DepartmentId = Department.Id JOIN NutritionData ON NutritionData.ProductId = Product.Id WHERE Department.Name = 'dairy'
