🌲 Seattle — DELETE & ANALYTICS (CRUD Submodule 4)

You need to remove expired salmon from the database. What SQL command removes records permanently?

-- Example: Delete expired salmon
DELETE FROM fresh_salmon 
WHERE expiry_date < CURRENT_DATE 
  AND quality_grade < 'B'
  AND location = 'seattle';

-- This removes all salmon that is:
-- 1. Past expiry date
-- 2. Below B grade quality  
-- 3. Located in Seattle
    

Review this SQL query that analyzes chowder sales data before deletion:

-- Analyze chowder sales before deletion
SELECT 
    chowder_type,
    COUNT(*) as total_orders,
    AVG(price) as avg_price,
    SUM(quantity) as total_sold
FROM chowder_orders 
WHERE order_date >= '2024-01-01'
GROUP BY chowder_type
ORDER BY total_orders DESC;

-- Then delete old orders
DELETE FROM chowder_orders 
WHERE order_date < '2023-01-01';
    

When deleting a crab dish, related ingredient records must also be removed. What is this called?

-- Cascade Delete Example
-- When we delete a crab dish, related records are automatically removed

DELETE FROM crab_dishes WHERE dish_id = 'crab-001';

-- This automatically triggers:
-- DELETE FROM dish_ingredients WHERE dish_id = 'crab-001';
-- DELETE FROM nutritional_info WHERE dish_id = 'crab-001';
-- DELETE FROM customer_reviews WHERE dish_id = 'crab-001';

-- All related data is cleaned up automatically!
    

Examine this DELETE operation that removes old coffee inventory:

-- Delete old coffee inventory with multiple conditions
DELETE FROM coffee_inventory 
WHERE roast_date < DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND coffee_type IN ('espresso', 'americano', 'latte')
  AND quantity_remaining < 10
  AND location = 'seattle';

-- This removes coffee that is:
-- 1. Older than 30 days
-- 2. One of the specified types
-- 3. Has less than 10 units remaining
-- 4. Located in Seattle
    

Instead of permanently deleting shellfish records, mark them as inactive. What field typically indicates soft deletion?

-- Soft Delete Example - Mark as deleted instead of removing
UPDATE oysters_shellfish 
SET deleted_at = NOW(),
    is_active = false,
    deleted_by = 'admin'
WHERE oyster_type = 'expired_batch'
  AND harvest_date < '2024-01-01';

-- To "restore" soft deleted records:
UPDATE oysters_shellfish 
SET deleted_at = NULL,
    is_active = true,
    deleted_by = NULL
WHERE oyster_id = 'oy-001';

-- To query only active records:
SELECT * FROM oysters_shellfish WHERE deleted_at IS NULL;
    

Study this complex analytics query for sourdough bowl sales:

-- Complex analytics with subquery before deletion
SELECT 
    location_name,
    AVG(bowl_price) as avg_price,
    COUNT(*) as total_sales
FROM sourdough_sales s
JOIN locations l ON s.location_id = l.id
WHERE s.bowl_price > (
    SELECT AVG(bowl_price) * 0.8 
    FROM sourdough_sales
)
GROUP BY location_name
HAVING COUNT(*) >= 5;

-- After analysis, clean up old low-performing data
DELETE FROM sourdough_sales 
WHERE bowl_price < (SELECT AVG(bowl_price) * 0.5 FROM sourdough_sales)
  AND sale_date < DATE_SUB(NOW(), INTERVAL 90 DAY);