Views

-- The simplest useful view: one table, one filter
CREATE VIEW active_students AS
SELECT id, name, email, branch, joined_on
FROM students
WHERE is_active = TRUE;

-- Query it exactly as you would query a table
SELECT * FROM active_students;
SELECT name FROM active_students WHERE branch = 'CSE';

-- Nothing was copied. Insert into the table...
INSERT INTO students (name, email, branch, is_active, joined_on)
VALUES ('Ananya Sharma', 'ananya@example.com', 'CSE', TRUE, '2026-07-01');

-- ...and the view already knows about it
SELECT COUNT(*) FROM active_students;

-- One place for the logic everybody needs
CREATE VIEW student_order_summary AS
SELECT
    s.id                        AS student_id,
    s.name                      AS student_name,
    s.branch                    AS branch,
    COUNT(o.id)                 AS order_count,
    COALESCE(SUM(o.amount), 0)  AS total_spent,
    MAX(o.ordered_at)           AS last_order_at
FROM students s
LEFT JOIN orders o ON o.student_id = s.id
GROUP BY s.id, s.name, s.branch;

-- Now the complicated part is one word long
SELECT student_name, total_spent
FROM student_order_summary
WHERE total_spent > 5000
ORDER BY total_spent DESC;

-- A view can be joined like any other table
SELECT v.student_name, v.total_spent, b.hostel_block
FROM student_order_summary v
JOIN hostel_allocations b ON b.student_id = v.student_id;

-- Changing a view: same columns, same order, extra ones allowed
CREATE OR REPLACE VIEW active_students AS
SELECT id, name, email, branch, joined_on, phone
FROM students
WHERE is_active = TRUE;

-- Listing views
SHOW FULL TABLES WHERE Table_type = 'VIEW';    -- MySQL
SHOW CREATE VIEW student_order_summary;        -- MySQL: see the stored SQL
-- PostgreSQL: SELECT * FROM pg_views WHERE schemaname = 'public';

DROP VIEW IF EXISTS student_order_summary;

-- Hide the columns nobody outside the team needs to see
CREATE VIEW students_public AS
SELECT id, name, branch, joined_on
FROM students;
-- email, phone, address and password_hash are simply not exposed

-- Restrict the rows as well
CREATE VIEW cse_students AS
SELECT id, name, email, joined_on
FROM students
WHERE branch = 'CSE';

-- Grant on the view, and nothing on the table (MySQL)
CREATE USER 'reporting'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT SELECT ON campus.students_public TO 'reporting'@'localhost';
-- Deliberately NOT granted: campus.students

-- Spell out which security model the view uses (MySQL)
CREATE SQL SECURITY DEFINER VIEW students_public_v2 AS
SELECT id, name, branch, joined_on FROM students;

CREATE SQL SECURITY INVOKER VIEW convenience_view AS
SELECT id, name FROM students WHERE is_active = TRUE;

-- PostgreSQL 15+: make a view run as the caller instead of the owner
-- CREATE VIEW students_public
--   WITH (security_invoker = true)
--   AS SELECT id, name, branch, joined_on FROM students;

-- Merged: the view's WHERE and yours are combined into one query,
-- and an index on branch is used normally
CREATE VIEW active_students AS
SELECT id, name, email, branch FROM students WHERE is_active = TRUE;

SELECT name FROM active_students WHERE branch = 'CSE';
-- effectively:
-- SELECT name FROM students WHERE is_active = TRUE AND branch = 'CSE';

-- Materialised: the GROUP BY forces the whole view to be built first
SELECT * FROM student_order_summary WHERE student_id = 42;
-- The engine aggregates every student, then keeps one row.

-- If that is a query you run constantly, ask the question directly
SELECT
    s.id, s.name,
    COUNT(o.id)                AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM students s
LEFT JOIN orders o ON o.student_id = s.id
WHERE s.id = 42
GROUP BY s.id, s.name;

-- Ask the optimiser what it is really doing
EXPLAIN SELECT * FROM student_order_summary WHERE student_id = 42;

-- MySQL lets you state the algorithm, though it may still override you
-- CREATE ALGORITHM = MERGE VIEW active_students AS ...

-- Updatable: one table, a plain filter, no aggregation
UPDATE active_students SET branch = 'ECE' WHERE id = 42;
DELETE FROM active_students WHERE id = 43;

-- Not updatable, and could not be: this row summarises many rows
-- UPDATE student_order_summary SET total_spent = 0 WHERE student_id = 42;

-- The disappearing row. The update works; the row leaves the view.
UPDATE active_students SET is_active = FALSE WHERE id = 42;
SELECT * FROM active_students WHERE id = 42;   -- no rows

-- WITH CHECK OPTION refuses writes that would fall outside the view
CREATE OR REPLACE VIEW active_students AS
SELECT id, name, email, branch, is_active
FROM students
WHERE is_active = TRUE
WITH CHECK OPTION;

-- Now the same statement is rejected instead of silently hiding the row
-- UPDATE active_students SET is_active = FALSE WHERE id = 42;  -- error

-- A CTE: the same idea, scoped to a single statement
WITH branch_totals AS (
    SELECT branch, COUNT(*) AS students
    FROM students
    GROUP BY branch
)
SELECT branch, students FROM branch_totals WHERE students > 30;

-- PostgreSQL and Oracle store the results and need refreshing
-- CREATE MATERIALIZED VIEW student_order_summary_mv AS SELECT ...;
-- REFRESH MATERIALIZED VIEW student_order_summary_mv;

-- MySQL has no materialized views. Build a summary table instead.
CREATE TABLE student_order_summary_daily (
    student_id  INT PRIMARY KEY,
    order_count INT,
    total_spent DECIMAL(12,2),
    refreshed_at DATETIME
);