Views in SQL - Theory with Example

 Views in SQL

1. View can be created by selecting the fields from one or more tables present in the database.
2. It can be either have specific records based on certain condition or all the records of a table.
3. Like table in SQL view also contains rows and columns.
4. Views are treated as a virtual/logical table used to view or manipulate parts of the table.
5. The view is a virtual entity, which means data is not actually stored in the view.
6. The view generates a slow result because it renders the
7. information from the table every time we query it.
8. It depends on the table. Therefore we cannot create a view without using tables.
9. It is an easy task to replace the view and recreate it whenever needs.
10. It does not occupies space on the systems.

For Example :

Consider below mentioned are the 2 tables available with database :

First table Student_info(Stud_id , Stud_name, City)

and

Second table

Student_div_info(Stud_id, Stud_name, Department,

Stud_div)

View can be created using the CREATE VIEW statement.

Creating View from a single table :

>>create view view1 as select Stud_name,City from Student_info where

Stud_id<3;

>>select * from view1;

Stud_name          City

Prashant               Pune

Ritesh                  Mumbai

Creating View from a multiple table :

CREATE VIEW view2 AS SELECT Student_info.Stud_name, Student_info.City,

Student_div_info.Department, Student_div_info.Stud_div FROM Student_info,

Student_div_info WHERE Student_info.Stud_name = Student_div_info.Stud_name;

select * from view2;

 

Stud_name                  City                     Department                 Stud_div

1)Prashant                Pune                      AINDS                        A

2)Ritesh                   Mumbai               COMPUTER                A

3)Vivek                    Nashik                  AINDS                         B

4)Rahul                    Nagpur                  IT                                  A