script.sql

-- QUERIES

-- Query 1: Retrieve recipes from the 'Dessert' category with less than 300 calories.
SELECT r.name, r.recipe_category, n.calories
FROM Recipes r
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
WHERE r.recipe_category = 'Dessert' AND n.calories < 300;


-- Query 2: Calculate the average calorie content grouped by each recipe category.
SELECT r.recipe_category, AVG(n.calories) AS average_calorie
FROM Recipes r
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
GROUP BY r.recipe_category;


-- Query 3: Group authors to count their total submitted recipes and sort by the most active.
SELECT a.author_name, COUNT(r.recipe_ıd) AS total_submitted_recipes
FROM Authors a
LEFT JOIN Recipes r ON a.author_ıd = r.Authors_author_ıd
GROUP BY a.author_ıd, a.author_name
ORDER BY total_submitted_recipes DESC;


-- Query 4: Retrieve high-rated recipes (rating >= 4) along with their corresponding authors.
SELECT r.name AS recipe_name, a.author_name AS author_name, rw.rating
FROM Recipes r
INNER JOIN Authors a ON r.Authors_author_ıd = a.author_ıd
INNER JOIN Reviews rw ON r.recipe_ıd = rw.Recipes_recipe_ıd
WHERE rw.rating >= 4;


-- Query 5: Filter recipes that provide a high protein content of over 40 grams.
SELECT r.name, n.protein_content
FROM Recipes r
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
WHERE n.protein_content > 40.0;


-- Query 6: Filter low-fat/diet-friendly recipes where the fat content is under 5 grams.
SELECT r.name, n.fat_content
FROM Recipes r
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
WHERE n.fat_content < 5.0;


-- Query 7: Identify the top 10 meals containing the highest amount of cholesterol.
SELECT r.name, n.cholesterol_content
FROM Recipes r
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
ORDER BY n.cholesterol_content DESC
LIMIT 10;



-- Query 8: Filter recipes with carbohydrate content falling between 50.0 and 100.0 grams.
SELECT r.name, n.carbonhydrate_content
FROM Recipes r
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
WHERE n.carbonhydrate_content BETWEEN 50.0 AND 100.0;


-- Query 9: Detect unreviewed recipes by utilizing a LEFT JOIN combined with an IS NULL check.
SELECT r.name, r.recipe_category
FROM Recipes r
LEFT JOIN Reviews rw ON r.recipe_ıd = rw.Recipes_recipe_ıd
WHERE rw.review_ıd IS NULL;


-- Query 10: Identify the top 10 most popular recipes based on the highest total count of submitted reviews.
SELECT r.name, COUNT(rw.review_ıd) AS total_review
FROM Recipes r
INNER JOIN Reviews rw ON r.recipe_ıd = rw.Recipes_recipe_ıd
GROUP BY r.recipe_ıd, r.name
ORDER BY total_review DESC
LIMIT 10;


-- Query 11: Analyze recipe publication and user engagement during the COVID-19 lockdown period (Spring 2020).
SELECT r.name AS tarif_adi, 
       r.recipe_category AS category, 
       r.date_published AS date_published
FROM Recipes r
WHERE r.date_published BETWEEN '2020-03-15' AND '2020-06-15'
ORDER BY r.date_published ASC;


-- Query 12: Retrieve the top 20 quickest recipes based on the shortest total preparation and cooking time.
SELECT r.name AS recipe_name, 
       rt.total_time AS total_time, 
       r.recipe_category AS category
FROM Recipes r
INNER JOIN Recipe_Time rt ON r.recipe_ıd = rt.Recipes_recipe_ıd
WHERE rt.total_time NOT LIKE '%H%'
  AND rt.total_time LIKE '%M%'
ORDER BY CAST(SUBSTRING_INDEX(REPLACE(rt.total_time, 'PT', ''), 'M', 1) AS UNSIGNED) ASC
LIMIT 20;


-- VIEWS

-- View 1: Popular Recipes Report (Virtual Table for Metrics Tracking)
CREATE VIEW PopularRecipesReport AS
SELECT 
    r.recipe_ıd, 
    r.name AS name, 
    r.recipe_category AS category, 
    COUNT(rw.review_ıd) AS total_review, 
    AVG(rw.rating) AS average_score
FROM Recipes r
INNER JOIN Reviews rw ON r.recipe_ıd = rw.Recipes_recipe_ıd
GROUP BY r.recipe_ıd, r.name, r.recipe_category;
SELECT * FROM PopularRecipesReport;


-- View 2: Diet and Health Summary (Virtual Table for Macro Filtering)
CREATE VIEW DietAndHealthSummary AS
SELECT 
    r.recipe_ıd, 
    r.name AS name, 
    r.recipe_category AS category, 
    n.calories AS calorie, 
    n.protein_content AS protein_content, 
    n.fat_content AS fat_content,
    n.carbonhydrate_content AS carbonhydrate_content
FROM Recipes rpopularrecipesreport
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd;
SELECT * FROM DietAndHealthSummary;


-- View 3: High Risk Nutritional Values (Virtual Table for Health Alerts)
CREATE VIEW HighRiskNutritionalValues AS
SELECT 
    r.recipe_ıd, 
    r.name AS name, 
    r.recipe_category AS category, 
    n.sodium_content AS sodium_content, 
    n.sugar_content AS sugar_content, 
    n.saturated_fat_content AS saturated_fat_content
FROM Recipes r
INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
WHERE n.sodium_content > 1000.0      
   OR n.sugar_content > 30.0         
   OR n.saturated_fat_content > 15.0;
SELECT * FROM HighRiskNutritionalValues


-- STORED PROCEDURES


-- STORED PROCEDURE-1
-- Purpose: Fetch all recipes published by a specific author using their unique ID, sorted chronologically.
DELIMITER //
CREATE PROCEDURE sp_GetAuthorRecipes(IN p_author_ıd INT)
BEGIN
    SELECT recipe_ıd, name AS name, recipe_category AS category, date_published AS date_published
    FROM Recipes
    WHERE Authors_author_ıd = p_author_ıd
    ORDER BY date_published DESC;
END //
DELIMITER ;
CALL sp_GetAuthorRecipes(30534);


-- STORED PROCEDURE-2
-- Purpose: Retrieve calorie and protein metrics via OUT parameters and classify the recipe as 'Diet Friendly' or 'High Calorie' using IF-ELSE logic.
DELIMITER //
CREATE PROCEDURE sp_GetRecipeNutritionalStatus(
    IN p_recipe_ıd INT,
    OUT p_calories FLOAT,
    OUT p_protein FLOAT,
    OUT p_status VARCHAR(50)
)
BEGIN
    SELECT calories, protein_content 
    INTO p_calories, p_protein
    FROM Nutrition
    WHERE Recipes_recipe_ıd = p_recipe_ıd;

    IF p_calories < 400.0 THEN
        SET p_status = 'Diet Friendly';
    ELSE
        SET p_status = 'High Calorie';
    END IF;
END //
DELIMITER ;
CALL sp_GetRecipeNutritionalStatus(411, @v_cal, @v_prot, @v_stat);
SELECT @v_cal AS Calorie, @v_prot AS Protein, @v_stat AS Status;


-- STORED PROCEDURE-3
-- Explanation: Retrieve a healthier (lower calorie) alternative recipe from the exact same category as the provided recipe ID, acting as a dynamic
--              recommendation engine.
DELIMITER //
CREATE PROCEDURE sp_FindHealthierAlternative(
    IN p_recipe_ıd INT,
    OUT p_alt_recipe_name VARCHAR(255)
)
BEGIN
    SELECT r2.name INTO p_alt_recipe_name
    FROM Recipes r1
    JOIN Recipes r2 ON r1.recipe_category = r2.recipe_category
    JOIN Nutrition n1 ON r1.recipe_ıd = n1.Recipes_recipe_ıd
    JOIN Nutrition n2 ON r2.recipe_ıd = n2.Recipes_recipe_ıd
    WHERE r1.recipe_ıd = p_recipe_ıd 
      AND r2.recipe_ıd != p_recipe_ıd 
      AND n2.calories < n1.calories
    ORDER BY n2.calories ASC
    LIMIT 1;
        IF p_alt_recipe_name IS NULL THEN
        SET p_alt_recipe_name = 'The lightest dish in this category is already this one!';
    END IF;
END //
DELIMITER ;
CALL sp_FindHealthierAlternative(265, @v_alternatif_yemek);
SELECT @v_alternatif_yemek AS 'More Healthy Alternative';


-- STORED PROCEDURE-4
-- Explanation: Dynamically filter and list recipes based on user-defined maximum carbohydrate, maximum fat, and minimum protein thresholds, sorted by
--              highest protein content.
DELIMITER //
CREATE PROCEDURE sp_ListMealsByMacros(
    IN p_max_carb FLOAT,
    IN p_max_fat FLOAT,
    IN p_min_protein FLOAT
)
BEGIN
    SELECT 
        r.name AS name, 
        r.recipe_category AS category, 
        n.protein_content AS protein_content,
        n.carbonhydrate_content AS carbonhydrate_content,
        n.fat_content AS fat_content,
        n.calories AS calorie
    FROM Recipes r
    INNER JOIN Nutrition n ON r.recipe_ıd = n.Recipes_recipe_ıd
    WHERE n.carbonhydrate_content <= p_max_carb
      AND n.fat_content <= p_max_fat
      AND n.protein_content >= p_min_protein
    ORDER BY n.protein_content DESC;
END //
DELIMITER ;
-- Parameters sequences: Max Carbonhydrate, Max Fat, Min Protein
CALL sp_ListMealsByMacros(24,53,78);