SQL Views

A View is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Why Use Views?

1. Simplicity: You can hide complex queries (like 5-table joins) behind a simple view name.
2. Security: You can restrict user access to specific columns (e.g., allow HR to see employee names but not salaries).
3. Consistency: You can ensure everyone uses the same logic for calculations (e.g., "Active Users" is always defined the same way).

1. Creating a View

The CREATE VIEW statement is used to create a new virtual table.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Customer Order Summary

Before creating a view, we need some source tables to work with. Run this block to set up the Customers and Orders tables:

DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Customers;

CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactName VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    TotalAmount DECIMAL(10, 2)
);

INSERT INTO Customers (CustomerID, CustomerName, ContactName)
VALUES (1, 'Alice Corp', 'Alice'),
       (2, 'Bob Ltd', 'Bob');

INSERT INTO Orders (OrderID, CustomerID, TotalAmount)
VALUES (101, 1, 500.00),
       (102, 1, 600.00),
       (103, 2, 300.00);

SELECT 'Source Tables Ready' AS Status;

Now, we can create a view that joins these tables to show how much each customer has spent:

CREATE VIEW CustomerOrderSummary AS
SELECT 
    c.CustomerName, 
    c.ContactName, 
    SUM(o.TotalAmount) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.ContactName;

SELECT 'View Created' AS Status;

2. Using a View

Once created, you can query the view exactly like a regular table.

Query:

SELECT * FROM CustomerOrderSummary
WHERE TotalSpent > 100;

Result:

Note: The view does not store the data physically. It runs the underlying query every time you access it.

3. Updating a View

You can update a view's definition using CREATE OR REPLACE VIEW (in MySQL or PostgreSQL). However, in many databases like SQLite, you must drop the view first and then recreate it.

Example (Universal Pattern):

DROP VIEW IF EXISTS CustomerOrderSummary;

CREATE VIEW CustomerOrderSummary AS
SELECT 
    c.CustomerName, 
    SUM(o.TotalAmount) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

SELECT 'View Updated' AS Status;

4. Dropping a View

To remove a view, use the DROP VIEW statement. This only deletes the virtual table, not the data in the underlying source tables.

Query:

DROP VIEW CustomerOrderSummary;

SELECT * FROM CustomerOrderSummary;

Error as the view is no more.

Hands-on Exercise

Task: Create a view named HighValueOrders that shows the OrderID and TotalAmount for all orders with a TotalAmount greater than 500.

Your Query:

-- Write your query here

Expected Results: