Question

USING THE FOLLOWING DATABASE: [Please read the instructions thoroughly, this is my 6th time posting this same question.]

=========

-- SQL Script for Generating Kitchen Database

drop database if exists kitchen;
create database kitchen;
use kitchen;
drop table if exists recipe;

drop table if exists food;
create table food
(fid int,
fname varchar(45) not null unique,
primary key(fid));

drop table if exists ingredient;
create table ingredient
(iid int,
iname varchar(45) not null unique,
caloriepergram float,
category varchar(20),
primary key(iid)
);

create table recipe (
fid int,
iid int,
amount int not null,
primary key(fid,iid),
foreign key(fid) references food(fid),
foreign key(iid) references ingredient(iid)
);

INSERT INTO `food` VALUES (1,'Fried Rice'),(2,'Orange Chicken'),(3,'BBQ Pork'),(4,'BBQ Chicken'),(5,'Fried Noodle'),(6,'Mongolian Beef');

INSERT INTO `ingredient` VALUES(21,'Pork', 1.44, 'Meat'),(23,'Chicken', 1.65, 'Meat'),(24,'Orange', 0.47, 'Vegetable'),(28,'Green Onion', 0.10, 'Vegetable'),(29,'Egg Noodle', 2.0, 'Carbohydrate'),(31,'Beef', 1.8, 'Meat'),(32,'Cilantro',0.10,'Vegetable'),(33,'Carrot', 0.10, 'Vegetable'),(34,'Green Pepper', 1.00, 'Vegetable');

INSERT INTO `recipe` VALUES (1, 21, 10), (2,23,60),(2,24,30),(3,21,60),(4,23,6),(5,23,200),(5,28,60),(5,29,6),(6,28,5),(6,31,60),(1,32,3),(1,33,3),(4,32,1),(6,32,3);

=========

DO THE FOLLOWING:

e) (7 points) List values of fid, fname, iid, iname, and calories of each ingredient for all food items that have chicken as one of the ingredients. Do not include food items that do not have chicken as one of the ingredients. The calories of each ingredient are calculated as the amount of the ingredient (in grams) multiplied with the calories per gram for that ingredient. Return the list in ascending order of fid. Show total calories as a floating point number with two decimal places.
Hint: Join of all three tables. Use the cast() function to show the calorie value as a floating point number with two decimal places.
The correct output has 7 rows!!!!!!!!!!!!

Once again. You need to list ALL INGREDIENTS OF EACH ITEM that has chicken in it. NOT JUST THE ITEM ITS SELF.

Answer 1

Below is the query to find ingredient chicken in recipe

SELECT r.* FROM ingredient i INNER JOIN recipe r ON i.iid = r.iid WHERE iname = "chicken"

Result will look like below.

Below is the query to find fid and fname that have chicken as one of the ingredients.

SELECT f.* FROM food f INNER JOIN recipe r ON f.fid = r.fid WHERE f.fid IN
(SELECT r.fid FROM ingredient i INNER JOIN recipe r ON i.iid = r.iid WHERE iname = "chicken")

Result will look like below.

To display iid and iname perform join with ingredient table.Query is below:

SELECT f.*,i1.iid,i1.iname FROM food f INNER JOIN recipe r ON f.fid = r.fid INNER JOIN ingredient i1
ON i1.iid = r.iid WHERE f.fid IN
(SELECT r.fid FROM ingredient i INNER JOIN recipe r ON i.iid = r.iid WHERE iname = "chicken")

Result will look like below.

Now to display calories as multiplication of caloriepergram and amount of the ingredient and calorie value as floating point number with two decimal places and return the list in ascending order of fid use below query:

SELECT f.*,i1.iid,i1.iname,CAST(r.amount*i1.caloriepergram AS DECIMAL(10,2)) AS calories
FROM food f INNER JOIN recipe r ON f.fid = r.fid INNER JOIN ingredient i1 ON i1.iid = r.iid WHERE f.fid IN
(SELECT r.fid FROM ingredient i INNER JOIN recipe r ON i.iid = r.iid WHERE iname = "chicken") ORDER BY fid ASC

Result will look like below.