CREATE DATABASE [Cooking Simulator] GO USE [Cooking Simulator]

GO CREATE TABLE [Chef] ( [ChefId] CHAR(6) PRIMARY KEY CHECK ([ChefId] LIKE 'CHF[0-9][0-9][0-9]'), [ChefName] VARCHAR (20) NOT NULL, [ChefRating] INT NOT NULL )

GO CREATE TABLE [Food] ( [FoodId] CHAR(6) PRIMARY KEY CHECK ([FoodId] LIKE 'FOD[0-9][0-9][0-9]'), [FoodName] VARCHAR (20) NOT NULL, [ChefId] CHAR(6) FOREIGN KEY REFERENCES [Chef] ([ChefId]), [FoodPrice] INT NOT NULL, [CookDuration] INT NOT NULL, [FoodType] VARCHAR(20) NOT NULL )

GO CREATE TABLE [Ingredient] ( [IngredientsId] CHAR(6) PRIMARY KEY CHECK ([IngredientsId] LIKE 'IGD[0-9][0-9][0-9]'), [IngredientsName] VARCHAR(20) NOT NULL, [IngredientsPrice] INT NOT NULL )

GO CREATE TABLE [Tools] ( [ToolsId] CHAR(6) PRIMARY KEY CHECK ([ToolsId] LIKE 'TLS[0-9][0-9][0-9]'), [ToolsName] VARCHAR (20) NOT NULL, [BrandName] VARCHAR (20) NOT NULL, [Material] VARCHAR(20) NOT NULL, [ToolsPrice] INT NOT NULL )

GO CREATE TABLE [FoodToolsDetail] ( [FoodId] CHAR(6) FOREIGN KEY REFERENCES [Food] ([FoodId]) ON UPDATE CASCADE ON DELETE CASCADE, [ToolsId] CHAR(6) FOREIGN KEY REFERENCES [Tools] ([ToolsId]) ON UPDATE CASCADE ON DELETE CASCADE, [ToolsQuantity] INT NOT NULL PRIMARY KEY ([FoodId], [ToolsId]) )

GO CREATE TABLE [FoodIngredientsDetail] ( [FoodId] CHAR (6) FOREIGN KEY REFERENCES [Food] ([FoodId]) ON UPDATE CASCADE ON DELETE CASCADE, [IngredientsId] CHAR (6) FOREIGN KEY REFERENCES [Ingredient] ([IngredientsId]) ON UPDATE CASCADE ON DELETE CASCADE, [IngredientsQuantity] INT NOT NULL PRIMARY KEY ([FoodId], [IngredientsId]) )

GO INSERT INTO [Chef] ([ChefId], [ChefName], [ChefRating]) VALUES ('CHF001', 'Juma', 5), ('CHF002', 'Ben', 4), ('CHF003', 'Gordon Kamsay', 5), ('CHF004', 'Mlavin', 2), ('CHF005', 'Kimi', 3)

GO INSERT INTO [Ingredient] ([IngredientsId], [IngredientsName], [IngredientsPrice]) VALUES ('IGD001', 'Garlic', 5000), ('IGD002', 'Cheese', 20000), ('IGD003', 'Noodle', 4000), ('IGD004', 'Ice Cream', 10000), ('IGD005', 'Chili', 25000), ('IGD006', 'Sugar', 8000), ('IGD007', 'Potato', 10000), ('IGD008', 'Rice', 12000), ('IGD009', 'Egg', 10000), ('IGD010', 'Salt', 6000)

GO INSERT INTO [Tools] ([ToolsId], [ToolsName], [BrandName], [Material], [ToolsPrice]) VALUES ('TLS001', 'Bowl', 'Tiger Star', 'Aluminium', 20000), ('TLS002', 'Pan', 'Pyrex', 'Aluminium', 30000), ('TLS003', 'Mixer', 'Onix', 'Plastic', 50000), ('TLS004', 'Oven', 'Rinnai', 'Plastic', 150000), ('TLS005', 'Knife', 'Boker', 'Iron', 50000)

GO INSERT INTO [Food] ([FoodId], [FoodName], [ChefId], [FoodPrice], [CookDuration], [FoodType]) VALUES ('FOD001', 'Lasagna', 'CHF003', 50000, 120, 'Main Dish'), ('FOD002', 'Indomie Laksa', 'CHF004', 70000, 35, 'Main Dish'), ('FOD003', 'Cheese Pudding', 'CHF005', 150000, 90, 'Dessert'), ('FOD004', 'Cinnamon Roll', 'CHF001', 45000, 100, 'Dessert'), ('FOD005', 'French Fries', 'CHF002', 50000, 10, 'Appetizer'), ('FOD006', 'Fried Calamari', 'CHF002', 60000, 50, 'Appetizer'), ('FOD007', 'Omurice', 'CHF005', 150000, 15, 'Main Dish'), ('FOD008', 'Ice Cream Volcano', 'CHF002', 30000, 10, 'Dessert'), ('FOD009', 'Seafood Fried Rice', 'CHF004', 50000, 10, 'Main Dish'), ('FOD010', 'Mixed Cocktail', 'CHF001', 20000, 10, 'Beverage')

GO INSERT INTO [FoodIngredientsDetail] ([FoodId], [IngredientsId], [IngredientsQuantity]) VALUES ('FOD001', 'IGD002', 5), ('FOD001', 'IGD010', 10), ('FOD002', 'IGD003', 15), ('FOD002', 'IGD010', 10), ('FOD003', 'IGD002', 20), ('FOD004', 'IGD006', 20), ('FOD005', 'IGD007', 20), ('FOD005', 'IGD010', 25), ('FOD006', 'IGD010', 15), ('FOD007', 'IGD008', 2), ('FOD007', 'IGD009', 25), ('FOD008', 'IGD004', 50), ('FOD008', 'IGD005', 20), ('FOD009', 'IGD008', 50), ('FOD009', 'IGD009', 10), ('FOD009', 'IGD010', 5), ('FOD010', 'IGD006', 15), ('FOD010', 'IGD001', 5)

GO INSERT INTO [FoodToolsDetail] ([FoodId], [ToolsId], [ToolsQuantity]) VALUES ('FOD001', 'TLS004', 1), ('FOD002', 'TLS001', 1), ('FOD003', 'TLS001', 2), ('FOD004', 'TLS003', 1), ('FOD005', 'TLS002', 1), ('FOD005', 'TLS005', 1), ('FOD006', 'TLS002', 1), ('FOD007', 'TLS002', 1), ('FOD008', 'TLS003', 1), ('FOD009', 'TLS002', 1), ('FOD010', 'TLS003', 1)

BASED ERD AND QUERY ABOVE, PLEASE ANSWER THESE 2 QUESTION:

1) QUESTION 1

2) Question 2

*Notes: Please Used syntax that permitted for each Question

Exampe For Question 2, Removing First Word: SELECT SUBSTRING(FoodName, CHARINDEX(' ', FoodName) + 1, LEN(FoodName)) AS [Food Name]

Tools Toolsid Tools Name BrandName Material ToolsPrice Food ToolsDetail Food FK Foodid PK Foodid FK Toolsid FK Cheid Too IsQuantity FoodName Food Price Chef CookDuration Chefld FoodType Che Name Chef Rating Food IngredientsDetail PX FK Foodid Ingredient FK Ingredientsid PX Ingredientsid IngredientsQuantity IngredientsName IngredientsPrice Display Ingredient Name (obtained from unique value of IngredientsName) for every food which price between 50000 and 100000 and contains those ingredients. (distinct, in, subquery) Ingredient Name Cheese Egg 1 W N 4 Noodle Potato Rice Salt 5 6 Select Food Name (obtained by removing the first word of FoodName), Price (obtained from FoodPrice) for every food that have at least 2 words in its original name and Food Price greater than average price of all 'Beverage FoodTyrs. (substring, charindex. len, alias subquery, average, like) 1 Food Name Cream Volcano Roll w N Price 30000 45000 50000 50000 60000 70000 150000 4 Fried Rice Fries Calamari Laksa Pudding 5 6 7