What are SQL Views?

SQL Views are virtual tables that represent the result of a query. Unlike tables, views do not store data themselves but present data from one or more tables as if it were a single table. Views simplify complex queries and enhance data security by restricting access to specific data.

Creating a View

Syntax:

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

Examples

Example 1: Single Table View

Create a view named DetailsView from StudentDetails:

CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

To query this view:

SELECT * FROM DetailsView;

Example 2: View with Sorting

Create a view named StudentNames from StudentDetails:

CREATE VIEW StudentNames AS
SELECT S_ID, NAME
FROM StudentDetails
ORDER BY NAME;

To query this view:

SELECT * FROM StudentNames;

Example 3: View from Multiple Tables

Create a view named MarksView combining StudentDetails and StudentMarks:

CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

To query this view:

SELECT * FROM MarksView;

Listing Views

To list views in a database, use:

SHOW FULL TABLES WHERE table_type LIKE '%VIEW';

Or query the information_schema:

SELECT table_name
FROM information_schema.views
WHERE table_schema = 'database_name';

Deleting a View

To delete a view:

DROP VIEW view_name;

Example:

DROP VIEW MarksView;

Updating a View

To update the view definition:

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

Example:

Add the AGE field to MarksView:

CREATE OR REPLACE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks.AGE
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

Inserting and Deleting Rows in a View

Insert Row:

INSERT INTO DetailsView(NAME, ADDRESS)
VALUES ('Suresh', 'Gurgaon');

Delete Row:

DELETE FROM DetailsView
WHERE NAME = 'Suresh';

WITH CHECK OPTION Clause

The WITH CHECK OPTION clause ensures that all modifications (INSERT or UPDATE) to the view meet the view’s conditions.

Example:

Create a view with WITH CHECK OPTION:

CREATE VIEW SampleView AS
SELECT S_ID, NAME
FROM StudentDetails
WHERE NAME IS NOT NULL
WITH CHECK OPTION;

Attempting to insert a row with a NULL NAME will result in an error.

Uses of a View

• Restricting Data Access: Limit access to specific rows and columns.
• Hiding Data Complexity: Simplify complex queries.
• Simplifying Commands: Abstract joins and other complexities.
• Storing Complex Queries: Store reusable complex queries.
• Renaming Columns: Change column names without altering base tables.
• Multiple Views: Create different views for different users.

Key Takeaways

• Views are virtual tables that do not store data but present data from one or more tables.
• They are created with CREATE VIEW and deleted with DROP VIEW.
• Views can be updated with CREATE OR REPLACE VIEW.
• WITH CHECK OPTION prevents data modifications that violate the view’s conditions.