Views & Stored Procedures

-- A VIEW is a saved, named query. It stores NO data of its own —
-- it just remembers the SELECT and re-runs it every time you use it.
CREATE VIEW electronics AS
SELECT product_name, price, stock
FROM products
WHERE category = 'Electronics';

-- Now query the view exactly like a table:
SELECT * FROM electronics;

-- Behind the scenes the database runs the saved SELECT again,
-- so the view is ALWAYS up to date with the products table.

-- Because a view behaves like a table, you can filter,
-- sort, and pick columns from it — no need to repeat the WHERE.
SELECT product_name, price
FROM electronics          -- the view we created above
WHERE price > 20
ORDER BY price DESC;

-- You wrote the "category = 'Electronics'" rule ONCE, in the view.
-- Every query against the view reuses it automatically.

-- A view can hide columns you don't want everyone to see.
-- Imagine a 'staff' table with a salary column — this view
-- exposes only the safe columns and keeps salary private.
CREATE VIEW staff_public AS
SELECT staff_id, first_name, department
FROM staff;
-- salary is simply not selected, so it can never leak through staff_public.

-- A view can also rename and combine columns for a clean report:
CREATE VIEW price_list AS
SELECT
    product_name AS item,
    price        AS "Price (USD)"
FROM 
...

-- SIMPLE views (one table, no GROUP BY / DISTINCT / aggregate)
-- are often UPDATABLE — writing to them writes to the real table:
UPDATE electronics
SET price = price * 0.9     -- 10% off all electronics
WHERE product_name = 'Wireless Mouse';
-- This actually changes the products table, because 'electronics'
-- maps row-for-row back to it.

-- COMPLEX views are READ-ONLY. This view groups rows, so the
-- database can't tell which real row an UPDATE should touch:
CREATE VIEW category_counts AS
S
...

-- Replace a view's definition without dropping it first:
CREATE OR REPLACE VIEW electronics AS
SELECT product_name, price          -- dropped the 'stock' column
FROM products
WHERE category = 'Electronics';

-- Remove a view entirely (the underlying table is untouched):
DROP VIEW electronics;

-- 'IF EXISTS' avoids an error if the view was already gone:
DROP VIEW IF EXISTS electronics;

-- A stored procedure is a reusable, named block of SQL you run by name.
-- This MySQL example takes one input parameter (a category) and
-- returns the matching products. CALL runs it.
DELIMITER //                         -- let the body use ; safely (MySQL only)
CREATE PROCEDURE products_in(
    IN wanted_category VARCHAR(50)   -- IN = a value you pass in
)
BEGIN
    SELECT product_name, price
    FROM products
    WHERE category = wanted_category
    ORDER BY price;
END //
DELIMITER ;        
...

-- Procedures can also hand a value back through an OUT parameter.
DELIMITER //
CREATE PROCEDURE count_in(
    IN wanted_category VARCHAR(50),  -- value you pass in
    OUT how_many INT                 -- value the procedure fills in for you
)
BEGIN
    SELECT COUNT(*) INTO how_many    -- store the count in the OUT parameter
    FROM products
    WHERE category = wanted_category;
END //
DELIMITER ;

CALL count_in('Electronics', @result);  -- @result captures the OUT value
SELECT @result;        
...

-- IMPORTANT: procedure syntax differs by database engine.

-- PostgreSQL — no DELIMITER; the body lives inside a $$ ... $$ block:
CREATE PROCEDURE products_in(wanted_category TEXT)
LANGUAGE SQL
AS $$
    SELECT product_name, price FROM products WHERE category = wanted_category;
$$;
CALL products_in('Electronics');

-- SQL Server — uses AS (no BEGIN required) and EXEC to run it:
CREATE PROCEDURE products_in @wanted_category VARCHAR(50)
AS
    SELECT product_name, price FROM products WHERE catego
...