Views in SQL — Database Management Systems

A view is a virtual table defined by a stored SQL query. It does not physically store data — instead, it dynamically retrieves data from the underlying base...

What is a View?

A view is a virtual table defined by a stored SQL query. It does not physically store data — instead, it dynamically retrieves data from the underlying base tables whenever it is queried.

Base Table: Employee

→

View filters columns/rows

→

EmployeePublic hides SSN and Salary

Using Views

Once created, a view is used exactly like a table:

sql example

-- Query a view
SELECT * FROM EmployeeDetails WHERE dept_name = 'Mathematics';

-- Filter and sort
SELECT name, salary FROM CS_Employees ORDER BY salary DESC;

-- Join a view with a table
SELECT v.name, v.salary, p.project_title
FROM CS_Employees v
JOIN Works_On w    ON v.emp_id = w.emp_id
JOIN Project p     ON w.proj_id = p.proj_id;

Modifying and Dropping Views

sql example

-- Redefine a view (replace existing)
CREATE OR REPLACE VIEW CS_Employees AS
SELECT emp_id, name, salary, hire_date
FROM Employee
WHERE dept_id = 1;

-- Drop a view
DROP VIEW CS_Employees;
DROP VIEW IF EXISTS EmployeeDetails;

Updatable Views

Some views can be used to INSERT, UPDATE, or DELETE data, which propagates to the base table.

A view is updatable if:

Based on a single table (no joins)
No GROUP BY, HAVING, DISTINCT, aggregate functions
No subqueries in SELECT list
Includes the primary key of the base table

sql example

-- Updatable view
CREATE VIEW CS_Staff AS
SELECT emp_id, name, salary FROM Employee WHERE dept_id = 1;

-- This UPDATE affects the Employee base table
UPDATE CS_Staff SET salary = 85000 WHERE emp_id = 101;

-- WITH CHECK OPTION ensures updates/inserts satisfy the view's WHERE
CREATE VIEW CS_Staff AS
SELECT emp_id, name, salary FROM Employee WHERE dept_id = 1
WITH CHECK OPTION;
-- Now: INSERT INTO CS_Staff ... will fail if dept_id != 1

Non-Updatable Views

Views are NOT updatable if they contain:

JOIN across multiple tables
GROUP BY or HAVING
DISTINCT
Aggregate functions (SUM, COUNT, AVG, etc.)
UNION
Subqueries

Advantages of Views

Advantage	Explanation
Security	Hide sensitive columns (salary, SSN) from certain users
Simplicity	Encapsulate complex joins/aggregations into a simple name
Data independence	Applications use views; underlying table changes don't break apps
Reusability	Define complex logic once; reuse in multiple queries
Logical data independence	Rename or restructure tables; keep views the same

Views vs. Materialized Views

Feature	View (Standard)	Materialized View
Data storage	Not stored	Physically stored on disk
Freshness	Always current (live query)	Stale until refreshed
Performance	May be slow (runs query every time)	Fast (pre-computed)
Refresh	Automatic (real-time)	Manual or scheduled
Storage cost	None	Disk space required
Support	All RDBMS	Oracle, PostgreSQL (not standard MySQL)

sql example

-- Materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW dept_stats AS
SELECT dept_id, COUNT(*), AVG(salary)
FROM Employee
GROUP BY dept_id;

-- Refresh when base data changes
REFRESH MATERIALIZED VIEW dept_stats;

View Diagram

Users / Applications

→

Query the View

→

View translates to base tables

→

Result returned

What is a View?

A view is a virtual table defined by a stored SQL query. It does not physically store data — instead, it dynamically retrieves data from the underlying base tables whenever it is queried.

Base Table: Employee

→

View filters columns/rows

→

EmployeePublic hides SSN and Salary

Using Views

Once created, a view is used exactly like a table:

sql example

-- Query a view
SELECT * FROM EmployeeDetails WHERE dept_name = 'Mathematics';

-- Filter and sort
SELECT name, salary FROM CS_Employees ORDER BY salary DESC;

-- Join a view with a table
SELECT v.name, v.salary, p.project_title
FROM CS_Employees v
JOIN Works_On w    ON v.emp_id = w.emp_id
JOIN Project p     ON w.proj_id = p.proj_id;

Modifying and Dropping Views

sql example

-- Redefine a view (replace existing)
CREATE OR REPLACE VIEW CS_Employees AS
SELECT emp_id, name, salary, hire_date
FROM Employee
WHERE dept_id = 1;

-- Drop a view
DROP VIEW CS_Employees;
DROP VIEW IF EXISTS EmployeeDetails;

Updatable Views

Some views can be used to INSERT, UPDATE, or DELETE data, which propagates to the base table.

A view is updatable if:

Based on a single table (no joins)
No GROUP BY, HAVING, DISTINCT, aggregate functions
No subqueries in SELECT list
Includes the primary key of the base table

sql example

-- Updatable view
CREATE VIEW CS_Staff AS
SELECT emp_id, name, salary FROM Employee WHERE dept_id = 1;

-- This UPDATE affects the Employee base table
UPDATE CS_Staff SET salary = 85000 WHERE emp_id = 101;

-- WITH CHECK OPTION ensures updates/inserts satisfy the view's WHERE
CREATE VIEW CS_Staff AS
SELECT emp_id, name, salary FROM Employee WHERE dept_id = 1
WITH CHECK OPTION;
-- Now: INSERT INTO CS_Staff ... will fail if dept_id != 1

Non-Updatable Views

Views are NOT updatable if they contain:

JOIN across multiple tables
GROUP BY or HAVING
DISTINCT
Aggregate functions (SUM, COUNT, AVG, etc.)
UNION
Subqueries

Advantages of Views

Advantage	Explanation
Security	Hide sensitive columns (salary, SSN) from certain users
Simplicity	Encapsulate complex joins/aggregations into a simple name
Data independence	Applications use views; underlying table changes don't break apps
Reusability	Define complex logic once; reuse in multiple queries
Logical data independence	Rename or restructure tables; keep views the same

Views vs. Materialized Views

Feature	View (Standard)	Materialized View
Data storage	Not stored	Physically stored on disk
Freshness	Always current (live query)	Stale until refreshed
Performance	May be slow (runs query every time)	Fast (pre-computed)
Refresh	Automatic (real-time)	Manual or scheduled
Storage cost	None	Disk space required
Support	All RDBMS	Oracle, PostgreSQL (not standard MySQL)

sql example

-- Materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW dept_stats AS
SELECT dept_id, COUNT(*), AVG(salary)
FROM Employee
GROUP BY dept_id;

-- Refresh when base data changes
REFRESH MATERIALIZED VIEW dept_stats;

View Diagram

Users / Applications

→

Query the View

→

View translates to base tables

→

Result returned

Views in SQL — Database Management Systems

What is a View?

Using Views

Modifying and Dropping Views

Updatable Views

Non-Updatable Views

Advantages of Views

Views vs. Materialized Views

View Diagram

Study next in this module

Views in SQL — Database Management Systems

What is a View?

Using Views

Modifying and Dropping Views

Updatable Views

Non-Updatable Views

Advantages of Views

Views vs. Materialized Views

View Diagram

Study next in this module