Views

Code

-- Create view
CREATE VIEW active_users AS
SELECT id, name, email, last_login
FROM users
WHERE active = true AND deleted_at IS NULL;

-- Use view
SELECT * FROM active_users ORDER BY last_login DESC;

-- View with aggregation
CREATE VIEW dept_stats AS
SELECT dept,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY dept;

-- Updatable view
CREATE OR REPLACE VIEW user_summary AS
SELECT id, name, email, active FROM users;

UPDATE user_summary SET active = false WHERE id = 10;

-- Drop view
DROP VIEW IF EXISTS active_users;

Explanation

Views are virtual tables that simplify complex queries and provide a data security layer; simple views are updatable.

More SQL Snippets