Views in SQL

• Views in SQL are considered as a virtual table. A view also contains rows and columns.
• To create the view, we can select the fields from one or more tables present in the database.
• A view can either have specific rows based on certain condition or all the rows of a table.

Sample table:

Student_Detail

STU_ID	NAME	ADDRESS
1	Stephan	Delhi
2	Kathrin	Noida
3	David	Ghaziabad
4	Alina	Gurugram

Student_Marks

STU_ID	NAME	MARKS	AGE
1	Stephan	97	19
2	Kathrin	86	21
3	David	74	18
4	Alina	90	20
5	John	96	18

1. Creating view

A view can be created using the CREATE VIEW statement. We can create a view from a single table or multiple tables.

Syntax:

1. CREATE VIEW view_name AS  
2. SELECT column1, column2.....  
3. FROM table_name  
4. WHERE condition;  

2. Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.

Query:

1. CREATE VIEW DetailsView AS  
2. SELECT NAME, ADDRESS  
3. FROM Student_Details  
4. WHERE STU_ID < 4;  

Just like table query, we can query the view to view the data.

1. SELECT * FROM DetailsView;  

Output:

NAME	ADDRESS
Stephan	Delhi
Kathrin	Noida
David	Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT statement.

In the given example, a view is created named MarksView from two tables Student_Detail and Student_Marks.

Query:

1. CREATE VIEW MarksView AS  
2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS  
3. FROM Student_Detail, Student_Mark  
4. WHERE Student_Detail.NAME = Student_Marks.NAME;  

To display data of View MarksView:

1. SELECT * FROM MarksView;  

NAME	ADDRESS	MARKS
Stephan	Delhi	97
Kathrin	Noida	86
David	Ghaziabad	74
Alina	Gurugram	90

4. Deleting View

A view can be deleted using the Drop View statement.

Syntax

1. DROP VIEW view_name;  

Example:

If we want to delete the View MarksView, we can do this as:

1. DROP VIEW MarksView;