-- Create the database
CREATE DATABASE CulinaryBook;
GO
USE CulinaryBook;
GO
-- Create the Recipes table
CREATE TABLE Recipes (
RecipeID INT PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(255) NOT NULL,
Cuisine NVARCHAR(100),
PrepTime INT,
CookTime INT,
Servings INT,
Instructions NVARCHAR(MAX),
Difficulty NVARCHAR(50),
Calories INT
);
GO
-- Create the Ingredients table
CREATE TABLE Ingredients (
IngredientID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(100) NOT NULL,
Quantity DECIMAL(10, 2),
Measurement NVARCHAR(50),
Temperature INT -- Temperature in degrees (optional)
);
GO
-- Create the RecipeIngredients table (junction table)
CREATE TABLE RecipeIngredients (
RecipeIngredientID INT PRIMARY KEY IDENTITY(1,1),
RecipeID INT NOT NULL,
IngredientID INT NOT NULL,
FOREIGN KEY (RecipeID) REFERENCES Recipes(RecipeID),
FOREIGN KEY (IngredientID) REFERENCES Ingredients(IngredientID)
);
GO
-- Create the Authors table
CREATE TABLE Authors (
AuthorID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(100) NOT NULL,
Surname NVARCHAR(100),
Bio NVARCHAR(MAX),
YouTubeChannel NVARCHAR(255)
);
GO
-- Create the RecipeAuthors table (junction table)
CREATE TABLE RecipeAuthors (
RecipeAuthorID INT PRIMARY KEY IDENTITY(1,1),
RecipeID INT NOT NULL,
AuthorID INT NOT NULL,
FOREIGN KEY (RecipeID) REFERENCES Recipes(RecipeID),
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);
GO,
INSERT
-- Inserting sample data into Ingredients table
INSERT INTO Ingredients (Name, Quantity, Measurement, Temperature)
VALUES
('Flour', 2.5, 'cups', NULL),
('Sugar', 1.0, 'cup', NULL),
('Butter', 200.00, 'grams', 21), -- Room temperature
('Eggs', 3.00, 'pieces', 21), -- Room temperature
('Milk', 1.5, 'cups', NULL),
('Vanilla Extract', 2.00, 'teaspoons', NULL),
('Salt', 0.5, 'teaspoon', NULL),
('Chocolate Chips', 100.00, 'grams', NULL),
('Garlic', 2.00, 'cloves', NULL),
('Olive Oil', 3.00, 'tablespoons', NULL);
-- Inserting sample data into Authors table
INSERT INTO Authors (Name, Surname, Bio, YouTubeChannel)
VALUES
('Jamie', 'Oliver', 'Celebrity chef known for his focus on healthy eating and easy-to-make recipes.',
'https://www.youtube.com/user/jamieoliver'),
('Gordon', 'Ramsay', 'Chef, restaurateur, and television personality known for his fiery temper and high
standards in the kitchen.', 'https://www.youtube.com/user/gordonramsay'),
('Nigella', 'Lawson', 'Food writer and broadcaster known for her comforting home cooking style.'),
('Rachael', 'Ray', 'Television personality and celebrity chef who hosts a popular talk show and cooking
program.', 'https://www.youtube.com/user/rachaelray');
-- Inserting sample data into Recipes table
INSERT INTO Recipes (Title, Cuisine, PrepTime, CookTime, Servings, Instructions, Difficulty, Calories)
VALUES
('Chocolate Chip Cookies', 'American', 15, 12, 24, 'Preheat oven to 350°F. Mix flour, sugar, and butter.
Add eggs, vanilla extract, and chocolate chips. Bake for 12 minutes.', 'Easy', 250),
('Spaghetti Aglio e Olio', 'Italian', 10, 10, 2, 'Cook spaghetti. Sauté garlic in olive oil, toss with spaghetti,
and garnish with parsley.', 'Easy', 350),
('Beef Wellington', 'British', 45, 60, 4, 'Prepare beef with mushroom duxelles, wrap in puff pastry, and
bake at 400°F for 25-30 minutes.', 'Hard', 800),
('Chicken Alfredo', 'Italian', 20, 20, 4, 'Cook chicken, make alfredo sauce with butter, cream, and
Parmesan cheese. Toss with cooked pasta.', 'Medium', 600);
-- Inserting sample data into RecipeIngredients table (linking recipes and ingredients)
INSERT INTO RecipeIngredients (RecipeID, IngredientID)
VALUES
(1, 1), -- Chocolate Chip Cookies: Flour
(1, 2), -- Chocolate Chip Cookies: Sugar
(1, 3), -- Chocolate Chip Cookies: Butter
(1, 4), -- Chocolate Chip Cookies: Eggs
(1, 5), -- Chocolate Chip Cookies: Milk
(1, 6), -- Chocolate Chip Cookies: Vanilla Extract
(1, 7), -- Chocolate Chip Cookies: Salt
(1, 8), -- Chocolate Chip Cookies: Chocolate Chips
(2, 1), -- Spaghetti Aglio e Olio: Flour (for spaghetti)
(2, 9), -- Spaghetti Aglio e Olio: Garlic
(2, 10), -- Spaghetti Aglio e Olio: Olive Oil
(3, 1), -- Beef Wellington: Flour (for pastry)
(3, 3), -- Beef Wellington: Butter
(3, 4), -- Beef Wellington: Eggs
(3, 10), -- Beef Wellington: Olive Oil
(4, 3), -- Chicken Alfredo: Butter
(4, 5), -- Chicken Alfredo: Milk
(4, 6), -- Chicken Alfredo: Vanilla Extract (optional for the sauce, though it could be used sparingly)
(4, 7); -- Chicken Alfredo: Salt
-- Inserting sample data into RecipeAuthors table (linking recipes to authors)
INSERT INTO RecipeAuthors (RecipeID, AuthorID)
VALUES
(1, 1), -- Chocolate Chip Cookies: Jamie Oliver
(2, 2), -- Spaghetti Aglio e Olio: Gordon Ramsay
(3, 3), -- Beef Wellington: Nigella Lawson
(4, 4); -- Chicken Alfredo: Rachael Ray
QUERIES
1)Authors without youtube channel
SELECT Name, Surname
FROM Authors
WHERE YouTubeChannel IS NULL;
2)Easy making recipes
SELECT Title, Cuisine, PrepTime, CookTime, Servings
FROM Recipes
WHERE Difficulty = 'Easy';
3)More than 400 calories recipes
SELECT Title, Cuisine, Calories
FROM Recipes
WHERE Calories > 400;
4)Y.channel of X author
SELECT Name, Surname, YouTubeChannel
FROM Authors
WHERE Name = 'Jamie' AND Surname = 'Oliver';
5)More than 15 minutes prep time recipes
SELECT Title, Cuisine, PrepTime
FROM Recipes
WHERE PrepTime > 15;
LAB 5
1)
SELECT r.Title AS RecipeTitle, a.Name AS AuthorName
FROM Recipes r
JOIN RecipeAuthors ra ON r.RecipeID = ra.RecipeID
JOIN Authors a ON ra.AuthorID = a.AuthorID;
2)
SELECT r.Title AS RecipeTitle, i.Name AS IngredientName, ri.Quantity, ri.Measurement
FROM Recipes r
JOIN RecipeIngredients ri ON r.RecipeID = ri.RecipeID
JOIN Ingredients i ON ri.IngredientID = i.IngredientID;
3)
SELECT a.Name AS AuthorName, COUNT(r.RecipeID) AS RecipeCount
FROM Authors a
LEFT JOIN RecipeAuthors ra ON a.AuthorID = ra.AuthorID
LEFT JOIN Recipes r ON ra.RecipeID = r.RecipeID
GROUP BY a.Name;
4_)
SELECT r.Cuisine, r.Title AS RecipeTitle, i.Name AS IngredientName, ri.Quantity, ri.Measurement
FROM Recipes r
JOIN RecipeIngredients ri ON r.RecipeID = ri.RecipeID
JOIN Ingredients i ON ri.IngredientID = i.IngredientID
ORDER BY r.Cuisine;
5)
SELECT r.Title AS RecipeTitle
FROM Recipes r
JOIN RecipeIngredients ri ON r.RecipeID = ri.RecipeID
JOIN Ingredients i ON ri.IngredientID = i.IngredientID
WHERE i.Name = 'Sugar';
6)
SELECT r.Title AS RecipeTitle, r.PrepTime, r.CookTime, a.Name AS AuthorName
FROM Recipes r
JOIN RecipeAuthors ra ON r.RecipeID = ra.RecipeID
JOIN Authors a ON ra.AuthorID = a.AuthorID;
7)
SELECT r.Title AS RecipeTitle, i.Name AS IngredientName, ri.Quantity, ri.Measurement
FROM Recipes r
JOIN RecipeAuthors ra ON r.RecipeID = ra.RecipeID
JOIN Authors a ON ra.AuthorID = a.AuthorID
JOIN RecipeIngredients ri ON r.RecipeID = ri.RecipeID
JOIN Ingredients i ON ri.IngredientID = i.IngredientID
WHERE a.Name = 'Jamie';
8)
SELECT r.Cuisine, r.Title AS RecipeTitle, r.Difficulty
FROM Recipes r
ORDER BY r.Cuisine;
9)
SELECT r.Title AS RecipeTitle, r.Calories, SUM(ri.Quantity) AS TotalIngredientQuantity
FROM Recipes r
JOIN RecipeIngredients ri ON r.RecipeID = ri.RecipeID
GROUP BY r.Title, r.Calories;
10)
SELECT a.Name AS AuthorName, a.YouTubeChannel, r.Title AS RecipeTitle
FROM Authors a
JOIN RecipeAuthors ra ON a.AuthorID = ra.AuthorID
JOIN Recipes r ON ra.RecipeID = r.RecipeID;
LAB 6
Group by queries
1)
SELECT a.Name AS AuthorName, COUNT(r.RecipeID) AS TotalRecipes
FROM Authors a
JOIN RecipeAuthors ra ON a.AuthorID = ra.AuthorID
JOIN Recipes r ON ra.RecipeID = r.RecipeID
GROUP BY a.Name;
2)
SELECT r.Cuisine, AVG(r.PrepTime) AS AveragePrepTime
FROM Recipes r
GROUP BY r.Cuisine;
3)
SELECT r.Title AS RecipeTitle, COUNT(ri.IngredientID) AS TotalIngredients
FROM Recipes r
JOIN RecipeIngredients ri ON r.RecipeID = ri.RecipeID
GROUP BY r.Title;
4)
SELECT a.Name AS AuthorName, COUNT(r.RecipeID) AS TotalRecipes
FROM Authors a
JOIN RecipeAuthors ra ON a.AuthorID = ra.AuthorID
JOIN Recipes r ON ra.RecipeID = r.RecipeID
GROUP BY a.Name
HAVING COUNT(r.RecipeID) > 5;
5)
SELECT r.Difficulty, SUM(r.Calories) AS TotalCalories
FROM Recipes r
GROUP BY r.Difficulty;
6)
SELECT r.Cuisine, COUNT(r.RecipeID) AS RecipeCount
FROM Recipes r
GROUP BY r.Cuisine
WITH ROLLUP;
7)
SELECT r.Cuisine, AVG(r.CookTime) AS AverageCookTime
FROM Recipes r
WHERE r.Calories > 500
GROUP BY r.Cuisine;
8)
SELECT i.Name AS IngredientName, SUM(ri.Quantity) AS TotalQuantity
FROM Ingredients i
JOIN RecipeIngredients ri ON i.IngredientID = ri.IngredientID
GROUP BY i.Name;
9)
SELECT a.Name AS AuthorName, AVG(r.Servings) AS AverageServings
FROM Authors a
JOIN RecipeAuthors ra ON a.AuthorID = ra.AuthorID
JOIN Recipes r ON ra.RecipeID = r.RecipeID
GROUP BY a.Name;
10)
SELECT r.Difficulty, SUM(r.PrepTime) AS TotalPrepTime
FROM Recipes r
GROUP BY r.Difficulty
WITH ROLLUP;
