MySQL Views Interview Questions

CREATE VIEW defines a new view. CREATE OR REPLACE VIEW modifies if exists or creates if not. ALTER VIEW changes definition. DROP VIEW deletes the view without affecting underlying tables.

-- Create view
CREATE VIEW sales_summary AS
SELECT DATE(order_date) as sales_date, SUM(amount) as daily_total
FROM orders
WHERE status = 'completed'
GROUP BY DATE(order_date);

-- Create or replace (if structure compatible)
CREATE OR REPLACE VIEW sales_summary AS
SELECT DATE(order_date) as sales_date, 
       SUM(amount) as daily_total,
       COUNT(*) as order_count
FROM orders
WHERE status = 'completed'
GROUP BY DATE(order_date);

-- Modify view
ALTER VIEW sales_summary AS
SELECT DATE(order_date) as sale_date, SUM(amount) as total_sales
FROM orders
WHERE status IN ('completed', 'shipped')
GROUP BY DATE(order_date);

-- Drop view
DROP VIEW sales_summary;

-- Drop if exists
DROP VIEW IF EXISTS sales_summary;

-- Drop multiple views
DROP VIEW IF EXISTS sales_summary, customer_summary, product_summary;

-- Create view with CHECK OPTION (restrict updates)
CREATE VIEW active_employees AS
SELECT * FROM employees WHERE status = 'active'
WITH CHECK OPTION;

-- View shows creation
SHOW CREATE VIEW sales_summary;

Why it matters: Managing views efficiently maintains database clarity and prevents errors.

Real applications: Schema updates, reporting infrastructure changes.

Common mistakes: Changing view structure breaking dependent code, not using DROP IF EXISTS.

Updatable views support INSERT, UPDATE, DELETE operations. Restrictions include: single table sources, no GROUP BY, no DISTINCT, no aggregate functions, no UNION. Non-updatable views are read-only. Use WITH CHECK OPTION to ensure updates maintain view conditions.

-- Updatable view: Simple single-table view
CREATE VIEW active_products AS
SELECT product_id, name, price FROM products
WHERE active = 1;

-- These work: Basic operations
UPDATE active_products SET price = 99 WHERE product_id = 1;
INSERT INTO active_products VALUES (10, 'New Product', 50);
DELETE FROM active_products WHERE product_id = 5;

-- Non-updatable view: Complex query
CREATE VIEW sales_summary AS
SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_spent
FROM orders
GROUP BY customer_id;

-- These DON'T work: View has GROUP BY
UPDATE sales_summary SET total_spent = 1000;  -- Error
INSERT INTO sales_summary VALUES (1, 5, 1000);  -- Error

-- CHECK OPTION: Ensure updates maintain view conditions
CREATE VIEW premium_customers AS
SELECT customer_id, name, credit_limit FROM customers
WHERE credit_limit > 10000
WITH CHECK OPTION;

-- This works: New values hold condition
UPDATE premium_customers SET credit_limit = 15000 WHERE customer_id = 1;

-- This fails: Violates CHECK OPTION
UPDATE premium_customers SET credit_limit = 5000 WHERE customer_id = 1;

-- LOCAL vs CASCADED CHECK OPTION
CREATE VIEW derived_view AS
SELECT * FROM base_view
WITH LOCAL CHECK OPTION;  -- Only check this level

CREATE VIEW derived_view AS
SELECT * FROM base_view  
WITH CASCADED CHECK OPTION;  -- Check all levels (default)

Why it matters: Understanding updateability prevents incorrect update attempts.

Real applications: Simple views often updatable, complex reporting views are read-only.

Common mistakes: Trying to update aggregate/grouped views, not using CHECK OPTION.

Materialized views (often called snapshots) store query results physically. MySQL doesn't support native materialized views but you can simulate with tables. Temporary tables exist for session duration. Both are faster than regular views but require refresh.

-- Simulate materialized view with table
CREATE TABLE sales_summary_materialized AS
SELECT DATE(order_date) as sales_date, SUM(amount) as total
FROM orders
GROUP BY DATE(order_date);

-- Add index for performance
CREATE INDEX idx_date ON sales_summary_materialized(sales_date);

-- Refresh materialized view
TRUNCATE sales_summary_materialized;
INSERT INTO sales_summary_materialized
SELECT DATE(order_date) as sales_date, SUM(amount) as total
FROM orders
GROUP BY DATE(order_date);

-- Or use stored procedure for refresh
DELIMITER //
CREATE PROCEDURE RefreshSalesSummary()
BEGIN
    DELETE FROM sales_summary_materialized;
    INSERT INTO sales_summary_materialized
    SELECT DATE(order_date) as sales_date, SUM(amount) as total
    FROM orders
    GROUP BY DATE(order_date);
END //
DELIMITER ;

-- Temporary table: Exists only for session
CREATE TEMPORARY TABLE temp_data AS
SELECT * FROM large_table WHERE status = 'active';

-- Use temporary table in session
SELECT * FROM temp_data;

-- Automatically dropped when session ends

-- Performance comparison
-- Regular view: Recalculates every query = Slow for complex queries
-- Materialized view: Pre-calculated, fast queries = Needs refresh
-- Temporary table: Session-specific data = Good for ETL

Why it matters: Materialized views significantly improve complex query performance.

Real applications: Data warehouses, reporting systems, analytics dashboards.

Common mistakes: Stale materialized data, too-frequent or infrequent refreshes.

View performance depends on query complexity and view definition. MySQL expands views inline (query flattening), so performance matches equivalent base query. Complex views with nested queries, aggregations, or large joins can be slow. Use EXPLAIN to analyze view queries.

-- Simple view: No performance impact
CREATE VIEW active_employees AS
SELECT * FROM employees WHERE status = 'active';

-- Equivalent to directly querying employees table
SELECT * FROM active_employees;
SELECT * FROM employees WHERE status = 'active';
-- Same performance!

-- Complex view: Performance considerations
CREATE VIEW sales_analytics AS
SELECT c.customer_name, 
       COUNT(o.id) as order_count,
       SUM(o.amount) as total_spent,
       AVG(o.amount) as avg_order,
       MAX(o.order_date) as last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
GROUP BY c.id
HAVING COUNT(o.id) > 5;

-- Querying complex view might be slow

-- Analyze view performance
EXPLAIN SELECT * FROM active_employees WHERE salary > 50000;

-- Optimize view queries
-- 1. Use views with WHERE conditions: Filters before aggregation
-- 2. Add indexes on join columns
-- 3. Consider materialized view for complex aggregations

-- Non-indexed view query: Slow
SELECT * FROM sales_analytics WHERE order_count > 10;

-- Solution: Materialize and index
CREATE TABLE sales_analytics_mat AS
SELECT * FROM sales_analytics;
CREATE INDEX idx_order_count ON sales_analytics_mat(order_count);

Why it matters: View performance directly impacts application responsiveness.

Real applications: Reporting systems slow with complex views, need optimization.

Common mistakes: Deep view nesting, not indexing underlying tables.

Views provide security by restricting column access (hide sensitive data), row access (show only relevant data), and operational simplicity. Users query views instead of base tables, enforcing data policies. GRANT permissions on views to control access.

-- Hide sensitive columns view
CREATE VIEW employee_public_view AS
SELECT employee_id, name, department, position
FROM employees;
-- Salary column hidden

-- Row-level security view
CREATE VIEW department_view AS
SELECT * FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE employee_id = USER_ID());

-- Create role with view access only
CREATE ROLE reporting_role;
GRANT SELECT ON views.* TO reporting_role;

-- Grant view access but not base table
GRANT SELECT ON view_name TO 'user'@'host';
REVOKE SELECT ON table_name FROM 'user'@'host';

-- Example: Salary sensitive information
CREATE VIEW hiring_view AS
SELECT name, position, hire_date FROM employees;

CREATE VIEW hr_payroll_view AS
SELECT name, salary, benefits FROM employees;

-- Grant hiring_view to recruiters
GRANT SELECT ON hiring_view TO 'recruiter'@'%';

-- Grant hr_payroll_view to HR only  
GRANT SELECT ON hr_payroll_view TO 'hr_person'@'localhost';

Why it matters: Views enforce security policies and protect sensitive data.

Real applications: Employee systems, financial data access, compliance systems.

Common mistakes: Granting base table access (bypasses view security), inconsistent permissions.

View dependencies occur when views reference other views or tables. Dropping a referenced table breaks dependent views. Use INFORMATION_SCHEMA to track dependencies. Test changes before applying to understand impact.

-- View dependencies
CREATE TABLE base_table (id INT, name VARCHAR(100));
CREATE VIEW view1 AS SELECT * FROM base_table;
CREATE VIEW view2 AS SELECT * FROM view1;  -- Depends on view1

-- If base_table changes, view1 and view2 affected
-- If view1 drops, view2 breaks

-- Query dependencies
SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%employees%';

-- Find dependencies of specific view
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'dependent_view';

-- Check what views reference a table
SELECT VIEW_NAME FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'base_table';

-- Safe column changes in base table
-- This breaks dependent views if column referenced
ALTER TABLE employees ADD COLUMN middle_name VARCHAR(100);

-- Dropping table warning
-- Before: DROP TABLE employees;
-- Check views affected first!

-- Safer: Check dependencies first
SHOW DEPENDS ON employees;  -- Not standard - use INFORMATION_SCHEMA instead

-- Delete dependent views before changing base table
DROP VIEW IF EXISTS employee_summary;
-- Modify base table
ALTER TABLE employees MODIFY COLUMN name VARCHAR(200);
-- Recreate view
CREATE VIEW employee_summary AS ...

Why it matters: Understanding dependencies prevents production issues from schema changes.

Real applications: Schema migration, column renames, database upgrades.

Common mistakes: Changing base table without checking dependent views, not testing changes.

Triggers and views together require careful design. Triggers can't fire on views directly—instead routes to underlying table. Instead INSTEAD OF triggers execute when view is modified, providing custom logic for updatable views.

-- INSTEAD OF trigger: Intercept view modifications
CREATE VIEW product_pricing_view AS
SELECT product_id, name, category_id, price
FROM products
WHERE category_id = 5;

-- Without INSTEAD OF: Can't update this view (has WHERE)
-- With INSTEAD OF: Can handle updates
DELIMITER //
CREATE TRIGGER instead_of_update_pricing INSTEAD OF UPDATE ON product_pricing_view
FOR EACH ROW
BEGIN
    -- Custom logic when updating through view
    UPDATE products 
    SET price = NEW.price
    WHERE product_id = NEW.product_id AND category_id = 5;
    
    -- Log price changes
    INSERT INTO price_change_log VALUES (NOW(), NEW.product_id, OLD.price, NEW.price);
END //
DELIMITER ;

-- Now view is updatable through trigger
UPDATE product_pricing_view SET price = 99 WHERE product_id = 1;

-- INSTEAD OF for INSERT
DELIMITER //
CREATE TRIGGER instead_of_insert_product INSTEAD OF INSERT ON product_pricing_view
FOR EACH ROW
BEGIN
    INSERT INTO products (name, category_id, price)
    VALUES (NEW.name, 5, NEW.price);
END //
DELIMITER ;

-- INSTEAD OF for DELETE
DELIMITER //
CREATE TRIGGER instead_of_delete_product INSTEAD OF DELETE ON product_pricing_view
FOR EACH ROW
BEGIN
    UPDATE products SET active = 0 WHERE product_id = OLD.product_id;
END //
DELIMITER ;

Why it matters: INSTEAD OF triggers enable complex business logic on updatable views.

Real applications: Controlled data modification, complex update logic.

Common mistakes: Not using INSTEAD OF for complex updatable views, triggering cascading changes unexpectedly.

Views are stored as database objects and reused across queries. Subqueries are inline within a query. Views offer reusability and cleaner code, while subqueries are ad-hoc and flexible. Use views for repeated logic, subqueries for one-off analysis.

-- View: Reusable, stored
CREATE VIEW high_value_customers AS
SELECT customer_id, name FROM customers
WHERE lifetime_value > 100000;

-- Multiple queries use same view
SELECT * FROM high_value_customers;
SELECT COUNT(*) FROM high_value_customers;
SELECT AVG(lifetime_value) FROM high_value_customers;

-- Subquery: Ad-hoc, inline
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
    WHERE order_date > '2024-01-01'
);

-- View advantages
// - Reusable across multiple queries
// - Cleaner, more readable code
// - Can grant permissions on view only
// - Performance optimized by database
// - Maintainability: Change view logic once

-- Subquery advantages
// - No schema management overhead
// - Flexible for one-off queries
// - Easier to test (no stored objects)
// - Can include different data each time

-- Convert subquery to view
-- Before
SELECT o.order_id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.amount > (
    SELECT AVG(amount) FROM orders
);

-- After (view)
CREATE VIEW large_orders AS
SELECT o.order_id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.amount > (SELECT AVG(amount) FROM orders);

SELECT * FROM large_orders;

Why it matters: Choosing between views and subqueries affects code organization and maintainability.

Real applications: Views for stable logic, subqueries for exploration and analysis.

Common mistakes: Complex one-off subqueries better as views, not recognizing reusable patterns.

View patterns include filtering views (restrict rows), projection views (restrict columns), joining views (simplify multi-table queries), and aggregation views (pre-calculated summaries). Best practices include clear naming, comprehensive documentation, and keeping views simple.

-- Pattern 1: Filtering view (security by rows)
CREATE VIEW active_employees_view AS
SELECT * FROM employees WHERE status = 'active' AND deleted_at IS NULL;

-- Pattern 2: Projection view (security by columns)
CREATE VIEW public_employee_view AS
SELECT name, department, position FROM employees;

-- Pattern 3: Joining view (simplify relationships)
CREATE VIEW order_details_view AS
SELECT o.order_id, o.order_date, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- Pattern 4: Aggregation view (summary data)
CREATE VIEW monthly_sales_view AS
SELECT YEAR(order_date) as year, MONTH(order_date) as month,
       COUNT(*) as orders, SUM(amount) as total_sales
FROM orders WHERE status = 'completed'
GROUP BY YEAR(order_date), MONTH(order_date);

-- Best practices
// 1. Naming: Use suffix '_view' or '_vw' for clarity
// 2. Documentation: Comment describing purpose and usage
// 3. Simplicity: Avoid deep nesting
// 4. Performance: Use indexes on base tables
// 5. Security: Follow principle of least privilege

-- Example with documentation
CREATE VIEW employee_summary_view AS
/* Purpose: Public employee directory for admin access
   Columns: Safe subset excluding salary/personal data
   Maintenance: Update when employee schema changes
   Performance: Backed by employee table indexes */
SELECT employee_id, name, department, position, email
FROM employees
WHERE status = 'active';

Why it matters: Consistent view patterns and naming improve database usability.

Real applications: Well-designed views significantly reduce query complexity.

Common mistakes: Inconsistent naming, undocumented views, overly complex view logic.

Views in replication are replicated as view definition (not data), so they must be re-created on slave. Backups include view definitions only, not materialized data. Views don't require backup space, only definition storage.

-- Views are replicated from master to slave
// 1. Master: CREATE VIEW statement is logged to binlog
// 2. Slave: Receives statement, recreates view
// 3. Result: Same view structure on both servers

// For view data to appear on slave:
// Underlying tables must replicate

-- View backup (definition only)
SHOW CREATE VIEW employee_summary_view;
-- Output:
-- CREATE VIEW employee_summary_view AS
-- SELECT ...

-- Backup views to file
mysqldump --user=root --password mysql --views > views_backup.sql

-- Restore views
mysql --user=root --password < views_backup.sql

-- When backing up database:
// Views are included in logical backups (mysqldump)
// Views are NOT included in file-based backups (copy files directly)
// Physical backups must capture .frm files (view definitions)

Why it matters: Views don't require data backup, only definitions need to be restored.

Real applications: Disaster recovery, cross-server synchronization.

Common mistakes: Assuming views replicate data (they replicate definition), not including views in backups.

View limitations include: can't pass parameters, can't reference views from procedural languages easily, SQL-only (no application logic), and potential performance issues with complex queries. Consider alternatives for dynamic requirements or complex logic.

-- Limitation 1: Views can't accept parameters
-- This doesn't work:
CREATE VIEW employee_by_dept(dept_id INT) AS
SELECT * FROM employees WHERE department_id = dept_id;

-- Solution: Use stored procedure instead
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(IN dept_id INT)
BEGIN
    SELECT * FROM employees WHERE department_id = dept_id;
END //
DELIMITER //

-- Limitation 2: Views are SQL-only
-- Can't include application logic
-- Can't call external APIs
-- Can't do conditional branching beyond CASE

-- Solution: Move logic to application layer

-- Limitation 3: Performance with complex queries
CREATE VIEW complex_analytics AS
SELECT category, 
       SUM(sales) as total_sales,
       AVG(price) as avg_price,
       COUNT(DISTINCT customer_id) as unique_customers
FROM (
    SELECT p.category, o.amount as sales, p.price, o.customer_id
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
) as base
GROUP BY category;

-- Querying complex nested view is slow
SELECT * FROM complex_analytics WHERE category = 'Electronics';

-- Solution: Materialized view
CREATE TABLE complex_analytics_mat AS
SELECT category, SUM(sales) as total_sales, ...
FROM base_query;

-- Limitation 4: Can't use UPDATE/DELETE easily on complex views
-- Solution: Use stored procedures with INSTEAD OF triggers

-- When to avoid views
// 1. Need parameterization → Use procedures
// 2. Complex business logic → Use application code
// 3. Performance critical → Use materialized views or caching
// 4. Frequent ddefinition changes → Use subqueries initially
// 5. One-time analysis → Use subqueries

Why it matters: Understanding limitations helps choose appropriate solutions.

Real applications: Complex systems use mix of views, procedures, application logic.

Common mistakes: Forcing views for every query need, not recognizing when to use alternatives.

View optimization involves indexing base tables, filtering early (WHERE conditions), avoiding aggregations when possible, and using materialized views for complex queries. EXPLAIN helps identify slow view queries.

-- Optimization 1: Index base tables
CREATE INDEX idx_status ON employees(status);
CREATE INDEX idx_dept ON employees(department_id);

-- View queries now benefit from indexes
CREATE VIEW active_sales_employees AS
SELECT * FROM employees 
WHERE status = 'active' AND department_id = 5;

-- Optimization 2: Filter early in view definition
-- Bad: View returns all data, filtering in query
CREATE VIEW all_orders AS SELECT * FROM orders;
SELECT * FROM all_orders WHERE order_date > '2024-01-01';  -- Filters after view

-- Good: Filter in view definition
CREATE VIEW recent_orders AS
SELECT * FROM orders 
WHERE order_date > '2024-01-01';

-- Optimization 3: Use EXPLAIN to analyze view queries
EXPLAIN SELECT * FROM recent_orders WHERE amount > 1000;

-- Optimization 4: Materialize complex views
-- Before: View with complex join and grouping
CREATE VIEW sales_summary AS
SELECT category, COUNT(*) as order_count, SUM(amount) as total_sales
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY category;

-- After: Materialized view
CREATE TABLE sales_summary_mat AS
SELECT category, COUNT(*) as order_count, SUM(amount) as total_sales
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY category;

-- Add indexes to materialized view
CREATE INDEX idx_category ON sales_summary_mat(category);

-- Refresh procedure
DELIMITER //
CREATE PROCEDURE refresh_sales_summary()
BEGIN
    TRUNCATE TABLE sales_summary_mat;
    INSERT INTO sales_summary_mat SELECT ...;
END //
DELIMITER //

Why it matters: Optimized views provide fast query performance without application code changes.

Real applications: Reporting dashboards need view optimization for responsiveness.

Common mistakes: Complex views without indexes, not materializing heavy aggregations.

Views abstract SELECT queries in read-only or simple update scenarios. Stored procedures provide more control, handle complex logic, transactions, and parameters. Use views for simple queries, procedures for business logic.

-- View: Simple SELECT abstraction
CREATE VIEW monthly_revenue_view AS
SELECT YEAR(order_date) as year, MONTH(order_date) as month,
       SUM(amount) as revenue
FROM orders WHERE status = 'completed'
GROUP BY YEAR(order_date), MONTH(order_date);

-- Query view simply
SELECT * FROM monthly_revenue_view WHERE year = 2024;

-- Procedure: Complex logic with parameters
DELIMITER //
CREATE PROCEDURE GetMonthlyRevenue(IN year_input INT, IN month_input INT)
BEGIN
    SELECT SUM(amount) as revenue
    FROM orders
    WHERE status = 'completed'
    AND YEAR(order_date) = year_input
    AND MONTH(order_date) = month_input;
    
    -- Could add complex logic
    -- IF revenue < threshold THEN notify_manager END IF;
END //
DELIMITER //

CALL GetMonthlyRevenue(2024, 1);

-- Comparison
// Views: Read data, simple filtering
// Procedures: Complex logic, transactions, state management

// Views: Query optimization automatic
// Procedures: Execution plan cached

// Views: Part of SELECT statement
// Procedures: Called with CALL statement

-- Use view when:
// - Simple SELECT query
// - Reusable filtering/joining
// - Security (restrict columns/rows)

-- Use procedure when:
// - Complex logic beyond SQL
// - Need parameters
// - Transaction handling required
// - Modify multiple tables
// - Need control flow (IF/ELSE, LOOP)

Why it matters: Choosing appropriate abstraction method improves code organization.

Real applications: Simple reads use views, complex operations use procedures.

Common mistakes: Force-fitting complex logic into views, overusing procedures for simple queries.

Common business views include customer summaries (lifetime value, order count), product performance (sales, rankings), employee hierarchies (reporting structure), and financial reports (revenue, expenses). These abstract complexity from application code.

-- E-Commerce Views
-- Customer lifetime value view
CREATE VIEW customer_ltv_view AS
SELECT c.customer_id, c.name, COUNT(o.id) as total_orders,
       SUM(o.amount) as lifetime_value,
       MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

-- Top selling products view
CREATE VIEW top_products_view AS
SELECT p.product_id, p.name, p.category,
       SUM(oi.quantity) as units_sold,
       SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
ORDER BY revenue DESC
LIMIT 100;

-- HR Management Views
-- Employee organizational chart view
CREATE VIEW org_hierarchy_view AS
SELECT e.employee_id, e.name, e.position,
       m.employee_id as manager_id, m.name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- Payroll summary view
CREATE VIEW payroll_summary_view AS
SELECT e.employee_id, e.name, e.base_salary,
       COALESCE(SUM(b.bonus), 0) as total_bonuses,
       e.base_salary + COALESCE(SUM(b.bonus), 0) as total_compensation
FROM employees e
LEFT JOIN bonuses b ON e.id = b.employee_id
GROUP BY e.id;

-- Financial Views
-- Monthly profit and loss view
CREATE VIEW monthly_pl_view AS
SELECT YEAR(date) as year, MONTH(date) as month,
       SUM(CASE WHEN type = 'revenue' THEN amount ELSE 0 END) as revenue,
       SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as expenses,
       SUM(CASE WHEN type = 'revenue' THEN amount ELSE -amount END) as profit
FROM transactions
GROUP BY YEAR(date), MONTH(date);

Why it matters: Well-designed business views significantly simplify application development.

Real applications: Every business system uses similar view patterns for abstraction.

Common mistakes: Not creating views for common queries, forcing application-level logic.