DBMS SQL View

by Online Tutorials Library July 14, 2022

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:

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

2. Creating View from a single table

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

Query:

  CREATE VIEW DetailsView AS  SELECT NAME, ADDRESS  FROM Student_Details  WHERE STU_ID < 4;  

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

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:

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

To display data of View 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

Example:

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

Next TopicDBMS SQL Index