Views

This lesson on Views is hands-on and example-driven. You will be able to encapsulate complex multi-table joins and queries into reusable virtual tables using the CREATE VIEW syntax. This simplifies downstream analytical queries, eliminates repetitive boilerplate, and lets you query virtual schemas seamlessly with standard SQL aggregation and filtering clauses.

What You'll Be Able To Do

• Define a persistent virtual table using the CREATE VIEW ... AS syntax.
• Inspect and describe view schemas using standard database inspection commands like SHOW TABLES and DESCRIBE.
• Refactor complex multi-table joins into modular queries that reference views.
• Apply downstream analytical clauses including GROUP BY, ORDER BY, and aggregate functions directly to views.
• Identify limitations regarding data modification (INSERT and UPDATE operations) when working with views.

Detailed Concept Walkthrough

1. Virtual Table Abstraction with Views

A view acts as a named alias or virtual table that encapsulates a SELECT query without physically storing its resulting data rows on disk.

• Mechanism: You create a view using CREATE VIEW <view_name> AS <select_query>, which persists the query definition inside the database metadata schema.
• Under the Hood: When you execute a query against a view, the database engine dynamically evaluates the underlying SELECT query in real time, merging the view's definition with your outer query.
• Syntax Rule: The database catalog exposes the view as if it were a standard table, allowing commands like SHOW TABLES and DESCRIBE <view_name> to introspect its generated columns.

-- Combine employee personal data and department assignments
CREATE VIEW full_employee_departments AS
SELECT 
    e.emp_no, e.first_name, e.last_name, e.gender, e.hire_date,
    d.dept_no, d.dept_name, de.from_date, de.to_date
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no;

Key Takeaway: Views store query logic rather than data, allowing complex multi-table relationships to be referenced under a single identifier.

2. Querying and Analyzing Through Views

Views can be queried exactly like physical tables using standard filtering, aggregation, and grouping clauses.

• Execution Flow: A query selecting from a view runs downstream analytical clauses—such as GROUP BY, ORDER BY, aggregate functions, and window functions—on top of the view's evaluated dataset.
• Mechanism: Downstream analysts can focus on business logic without repeatedly copying complex multi-table join criteria across different scripts.
• Best Practice: Always inspect the view with DESCRIBE to confirm available column names and types before composing complex analytical queries.

-- Query the view to count employees by gender per department
SELECT 
    dept_name,
    gender,
    COUNT(*) AS total_employees
FROM full_employee_departments
GROUP BY dept_name, gender
ORDER BY dept_name, gender;

Key Takeaway: Querying a view is syntactically identical to querying a real table, abstracting query complexity away from analytical operations.

3. Data Modification Limitations in Views

While views behave like physical tables for read operations, most analytical views do not support direct write operations like INSERT or UPDATE.

• Mechanism: A view containing joins, aggregations, GROUP BY, or distinct clauses loses the 1-to-1 row mapping back to the underlying base tables.
• Under the Hood: Because the database cannot unambiguously determine which underlying base table row to modify, write operations on complex views will fail.
• Best Practice: Treat views containing joins or aggregations as read-only abstractions intended strictly for query simplification and reporting.

-- Attempting to insert into a multi-table view will generally fail
-- Error: View is not updatable or insertable
INSERT INTO full_employee_departments (emp_no, first_name, dept_name)
VALUES (99999, 'Jane', 'Engineering');

Key Takeaway: Views composed of multi-table joins or aggregations are generally non-updatable and non-insertable.

Topics Covered in Views

1. Introduction to Views (0:00 - 0:30) — The instructor introduces views as reusable query abstractions that behave like virtual tables.
2. Multi-Table Join Problem (0:30 - 1:15) — A complex query joining employees, departments, and junction tables demonstrates repetitive query friction.
3. Creating a View (1:15 - 1:45) — The CREATE VIEW syntax stores the multi-table join under a reusable virtual table name.
4. Inspecting Virtual Tables (1:45 - 2:15) — The database interface confirms the view behaves like a table via SHOW TABLES and DESCRIBE.
5. Analytical Queries on Views (2:15 - 2:45) — Grouping and aggregation queries run cleanly against the newly created view.
6. View Updatability Limits (2:45 - 3:20) — The instructor explains that views containing complex joins or grouping clauses are not updatable or insertable.

SQL Cheat Sheet

• CREATE VIEW <name> AS <query> — Creates a named virtual table from a query

  CREATE VIEW emp_dept_view AS SELECT * FROM employees;
  

• SELECT * FROM <view_name> — Executes the underlying view query and returns rows

  SELECT * FROM full_employee_departments;
  

• SHOW TABLES — Lists both base tables and views in database

  SHOW TABLES;
  

• DESCRIBE <view_name> — Shows column names and datatypes of a view

  DESCRIBE full_employee_departments;
  

• GROUP BY on Views — Aggregates virtual table data by specified columns

  SELECT dept_name, COUNT(*) FROM full_employee_departments GROUP BY dept_name;
  

Comparison Table

Common Pitfalls

• Mistake: Expecting a view to store a static snapshot of data. Avoid: Remember views dynamically re-run the underlying query every time they are selected.
• Mistake: Attempting INSERT or UPDATE operations on multi-table join views. Avoid: Apply data modifications directly to the underlying base tables instead.
• Mistake: Duplicating multi-table join logic across analytical scripts. Avoid: Encapsulate the join once in a view and query the view.

FAQs

• Does creating a view duplicate or consume storage for the underlying table data? No, views only store the SQL query definition in database metadata, not the underlying row data.
• Why do views appear in the output of SHOW TABLES? Most relational database systems treat views as virtual tables, exposing them alongside regular tables in schema inspection commands.
• Can I use aggregate functions, window functions, and GROUP BY on a view? Yes, you can query a view with any standard SQL analytical clause just as you would with a physical table.
• Can I insert new rows directly into a view that joins multiple tables? Generally no, views that include joins, aggregations, or complex clauses are not insertable or updatable.