Views (Virtual Tables) in SQL

October 04, 2025

Views (Virtual Tables) in SQL

A view is one of the most important SQL abstractions. It supports simplicity, security, and logical data independence.

1. What Is a View?

A view is:

A virtual table
Derived from one or more base tables (or other views)
Defined using a SELECT query
Not stored physically (in most cases)

📌 Base tables → physically stored
📌 Views → logically stored as definitions

Why “Virtual Table”?

A view behaves like a table for querying
But its tuples are computed dynamically
Data is fetched from base tables when the view is queried

👉 Hence, views are always up-to-date

2. Motivation for Views

Views are used to:

Simplify complex queries
Provide security and restricted access
Present customized data
Hide complexity of joins
Support logical data independence

3. Example Scenario (COMPANY Database)

Suppose we frequently need:

Employee names
Project names
Hours worked

Instead of repeatedly joining:

EMPLOYEE
WORKS_ON
PROJECT

👉 We define a view once, then query it like a table.

4. Creating Views in SQL (CREATE VIEW)

General Syntax


CREATE VIEW view_name [(attribute_list)]
AS SELECT_query;

5. View Example 1: WORKS_ON1


CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn = Essn AND Pno = Pnumber;

Explanation

View name: WORKS_ON1
Derived from three tables
No new attribute names specified
Attribute names inherited automatically

📌 WORKS_ON1 schema:


(Fname, Lname, Pname, Hours)

6. View Example 2: DEPT_INFO (with aggregation)


CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)
AS SELECT Dname, COUNT(*), SUM(Salary)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber = Dno
GROUP BY Dname;

Explanation

Explicit attribute renaming using CREATE VIEW
Uses GROUP BY and aggregate functions
Produces one row per department

📌 This is a summary view

7. Querying a View

Once created, a view is queried just like a table.

Example Query on View


SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname = 'ProductX';

✔ No joins required
✔ Much simpler than querying base tables

8. Dropping a View


DROP VIEW WORKS_ON1;

Removes the view definition
Does not affect base tables

9. View Implementation Strategies

9.1 Query Modification (View Expansion)

DBMS rewrites view query into base-table query
View definition is substituted

Example:


SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname = 'ProductX';

Automatically becomes:


SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn = Essn
  AND Pno = Pnumber
  AND Pname = 'ProductX';

📌 Pros: No storage overhead
📌 Cons: Slow for complex views used frequently

9.2 View Materialization

View is physically stored
Stored as a temporary or permanent table
DBMS keeps it synchronized with base tables

Update Strategies:

Immediate: update view instantly
Lazy: update when queried
Periodic: update at intervals (may be stale)

📌 Used in data warehouses

10. Updating Views (Major Issue)

10.1 Why View Updates Are Hard

Views may involve:
- Joins
- Aggregation
- Derived attributes
One view update may map to multiple base-table updates

👉 DBMS cannot always determine the user’s intention

11. Example: Ambiguous View Update


UPDATE WORKS_ON1
SET Pname = 'ProductY'
WHERE Fname='John' AND Lname='Smith'
AND Pname='ProductX';

Possible Interpretations:

(a) Update WORKS_ON (correct intention)

Change project assignment of John Smith

(b) Update PROJECT (wrong side effect)

Rename ProductX to ProductY for all employees

📌 Because multiple interpretations exist, DBMS rejects the update

12. Non-Updatable Views

Example: Aggregate View Update


UPDATE DEPT_INFO
SET Total_sal = 100000
WHERE Dname = 'Research';

🚫 Invalid because:

Total_sal is derived
Cannot be directly updated

13. When Is a View Updatable?

General Rules

✔ View is updatable if:

Defined on single base table
Includes primary key
No aggregation, GROUP BY, DISTINCT, joins

🚫 View is NOT updatable if:

Uses joins
Uses aggregate functions
Uses GROUP BY / HAVING

14. WITH CHECK OPTION


CREATE VIEW DEPT5EMP AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5
WITH CHECK OPTION;

✔ Ensures updates through the view do not violate view condition

Example:

Cannot insert employee with Dno ≠ 5 through this view

15. Inline Views (Subquery in FROM Clause)

A view can be defined inside a query:


SELECT *
FROM (
  SELECT Dno, AVG(Salary)
  FROM EMPLOYEE
  GROUP BY Dno
) AS DeptAvg;

📌 Exists only for that query execution

16. Views as Authorization Mechanisms

Views help restrict access.

Restrict Rows


CREATE VIEW DEPT5EMP AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

✔ User sees only department 5 employees

Restrict Columns


CREATE VIEW BASIC_EMP_DATA AS
SELECT Fname, Lname, Address
FROM EMPLOYEE;

✔ Sensitive attributes (Salary, SSN) hidden

17. Advantages of Views

✔ Simplify complex queries
✔ Improve security
✔ Logical data independence
✔ Data abstraction
✔ Reusability

18. Summary Table

Aspect	Views
Storage	Virtual (logical)
Data	Always up-to-date
Querying	Fully supported
Updates	Limited
Security	Excellent
Complexity	Hidden

Final Takeaway

A view is a virtual table defined by a query that simplifies data access, improves security, and provides logical abstraction—but with restrictions on updates.