In SQL, virtual table are considered as views. Like Tables, views also contain rows and columns. We can select the fields from one or more tables to create the view. It can either have all the rows of a table or have some specific rows based on certain condition.
Detailed Answer
What is View?
In SQL, views are kind of virtual tables. A view also has rows and columns as they are in real relations.
We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.
Advantages and Disadvantages of view
Advantages of view
- Security – It increases the security by excluding the sensitive information.
- Consistency – It presents a unchanged and consistent image of the database structure.
- Space Capacity – It takes very little space to store the data.
- Logical Data Independence – It can make the database tables independent to a certain extent.
- Query Simplicity – View helps to simplify queries from the user.
- Data Integrity – If data is accessed and entered through a view, DBMS can automatically check the data to ensure that it meets the specified Integrity constraints.
- Complexity – It helps to reduce the complexity.
Disadvantages of view
- We cannot insert if the base table has any not null columns that do not appear in view.
- We can’t create view on temporary tables.
- We can’t associate rules and defaults with views.
We can’t pass parameters to the SQL server views.
Example of View
We are taking first table Marks
Table Marks
Here we take a table named Marks which has the attributes as follows
Name which show the name of the students
Physics which shows the marks of the student in physics
Chemistry which shows the marks of the student in chemistry
Maths which shows the marks of the student in maths
| Name | Physics | Chemistry | Maths |
| Anik | 55 | 74 | 92 |
| Rimi | 85 | 69 | 40 |
| Ayan | 32 | 93 | 87 |
We are taking second table Student
Table Student
Here we take a table named Student which has the attributes as follows
Roll_no which show the roll number of the students
Name which show the name of the students
Course which show the subject of the students
Address which show the address of the students
Age which show the age of the students
| Roll_no | Name | Course | Address | Age |
| 1. | Anik | Science | Kolkata | 24 |
| 2. | Rimi | Science | Durgapur | 23 |
| 3. | Ayan | Commerce | Gurgaon | 22 |
| 4. | Aritri | Arts | Kolkata | 23 |
Creating a view
1. Create view
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 Details from the table Student.
CREATE VIEW Details AS
SELECT Name, Address
FROM Student
WHERE Roll_no < 3;
SELECT * FROM Details;Output
| Name | Address |
| Anik | Kolkata |
| Rimi | Durgapur |
3. Creating View from multiple tables
In the given example, a view is created named Score from two tables Student and Marks.
CREATE VIEW Score AS
SELECT Student.Name, Student.Address, Marks.Physics
FROM Student, Marks
WHERE Student.Name = Marks.Name;
SELECT * FROM Score;Output
| Name | Physics |
| Anik | 55 |
| Rimi | 85 |
| Ayan | 32 |
4. Deleting a View
Syntax:
DROP VIEW View_name; Example:
If we want to delete the View Score, we can do this as:
DROP VIEW Score;