Views In SQL : DBMS

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 contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.



Sample Table:-


Student_Detail

STU_ID	NAME	ADDRESS
1	AK	Surat
2	KK	Noida
3	PK	Sirohi
4	SK	Delhi

Student_Marks

STU_ID	NAME	MARKS	AGE
1	AK	98	17
2	KK	86	21
3	PK	78	18
4	SK	90	20
5	Bunty	96	19

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.

SELECT * FROM DetailsView;  

NAME	ADDRESS
AK	Surat
KK	Noida
PK	Sirohi

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:

  SELECT * FROM MarksView;  

NAME	ADDRESS	MARKS
AK	Surat	98
KK	Noida	86
PK	Sirohi	78
SK	Delhi	90

4. Deleting View

A view can be deleted using the Drop View statement.


Syntax:-

DROP VIEW view_name;  

Example:-

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

DROP VIEW MarksView;