View in SQL

Jayden Bell 1953 05-Jul-2016

A view is a SQL statement which is stored in the database with a name. A

view is actually a formation of a table in the form of a predefined

Structured Query Language (SQL) query.

A view can select rows or contain all rows of a table. A view is created from

one or many tables which depends SQL query to create a view. A view

contains columns and rows, like a real table. The columns in a view are

columns from one or more real tables.

Views are virtual tables, which enables users to do the following:·

Structure data is a way that classes of users or users find natural or intuitive.
Summarize data from tables which is used to generate reports.
Restrict access to the value such that a user can see and update exactly what they need and no more.

Following is the syntax to create a view table:

SQL CREATE VIEW

Syntax

CREATE VIEW name_of_view AS
SELECT name_of_column
FROM name_of_table
WHERE condition

SQL CREATE VIEW example

Code implementation

NAME

AGE

ADDRESS

MARKS

Neha

Ragini

Shreya

Kopal

Rohan

Avi

Mumbai

Ahmedabad

Pune

Allahabad

Banaras

Kanpur

CREATE VIEW [Student list] AS
SELECT studentID, studentName
FROM student
WHERE Discontinued=No

In the above example, The view "student List" lists of all students from the

"student" table.

Now, you can query student in similar way as you query an actual table.

Following is the example:


SELECT * FROM student;

Name

Neha

Ragini

Shreya

Kopal

Rohan

Avi

SQL Updating a View

Following is the syntax of updating a view:

Syntax

CREATE OR REPLACE VIEW name_of_view AS
SELECT name_of_column
FROM name_of table
WHERE condition

Following is the example of updating a view:

Code implementation

UPDATE student SET AGE = 20
WHERE name='Neha';

Code output

NAME

AGE

ADDRESS

MARKS

Neha

Ragini

Shreya

Kopal

Rohan

Avi

Mumbai

Ahmedabad

Pune

Allahabad

Banaras

Kanpur

Deleting Rows into a View:

Rows can be deleted from a view. The same rules are applied to DELETE

command that apply to the UPDATE command. Following is the example

to delete a row in a view:

Code implementation

DELETE FROM student
      WHERE age = 20;

Code output

NAME

AGE

ADDRESS

MARKS

Ragini

Shreya

Kopal

Rohan

Avi

Ahmedabad

Pune

Allahabad

Banaras

Kanpur

As from the above example, we can see that record of “Neha” is deleted.

Inserting Rows into a View:

In a view rows can be inserted. The same rules are applied to INSERT

command that apply to the UPDATE command.

Here we cannot insert rows in student table because we have not included

NOT NULL columns in view, otherwise you can insert rows in a similar way

as you insert them in a table.

Dropping Views:

If view is no longer needed you can drop the view. Following is the syntax

for dropping views as given below:

DROP view name of view;

Following is the example of dropping view:


DROP view student;

Student view will be deleted.

blog

View in SQL

Jayden Bell

Leave Comment

1 Comments

Comments

Liked By