Home > DeveloperSection > Blogs > View in SQL

View in SQL


MYSQL Database  SQL Server  SQL 
Ratings:
0 Comment(s)
 165  View(s)
Rate this:

View in SQL


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

ID

NAME

AGE

ADDRESS

MARKS

1

2

3

4

5

6

Neha

Ragini

Shreya

Kopal

Rohan

Avi

18

19

17

18

18

19

Mumbai

Ahmedabad

Pune

Allahabad

Banaras

Kanpur

35

45

23

24

43

36

 

 

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;

 

Id

Name

1

2

3

4

5

6

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

ID

NAME

AGE

ADDRESS

MARKS

1

2

3

4

5

6

Neha

Ragini

Shreya

Kopal

Rohan

Avi

20

19

17

18

18

19

Mumbai

Ahmedabad

Pune

Allahabad

Banaras

Kanpur

35

45

23

24

43

36

 

 

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

ID

NAME

AGE

ADDRESS

MARKS

2

3

4

5

6

Ragini

Shreya

Kopal

Rohan

Avi

19

17

18

18

19

Ahmedabad

Pune

Allahabad

Banaras

Kanpur

45

23

24

43

36

 

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.


Don't want to miss updates? Please click the below button!

Follow MindStick