A view is a virtual table. It is used to summarize data from various tables that helps to generate reports. A view always shows updated data.
Example: Query using CREATE VIEW Statement
Consider the following table titled 'Students'.
Write a query to create a view of columns such as Stud_ID, Stud_Name, City from the above table 'Students'.
Result is shown in the following table:
Note: SELECT * FROM students; statement is used to see the created view.
Example: Update view using ALTER VIEW statement.
Consider the following table titled 'Students'.
To update view titled 'Stud', the query should be written as:
Result is shown in the following table.
Syntax:
DROP VIEW View_name;
Example: Query using DROP VIEW Statement.
If user needs to delete view titled as 'Students'. The query should be written as:
DROP VIEW Students
1. CREATE VIEW
- SQL VIEW is a virtual table and it contains rows and columns just like real table.
- The fields of views are selected from one or more real tables in the database.
- The views are used to filter the data in the table and to hide some columns data from a database table.
Syntax
CREATE VIEW view_name AS
SELECT column_name
FROM table name
WHERE [condition]
Example: Query using CREATE VIEW Statement
Consider the following table titled 'Students'.
| Stud_ID | Stud_Name | City | Country |
|---|---|---|---|
| 1 | Mark | London | England |
| 2 | Alex | Paris | France |
| 3 | Bob | Sydney | Australia |
| 4 | Jaya | Delhi | India |
| 5 | Surendra | Baroda | India |
Write a query to create a view of columns such as Stud_ID, Stud_Name, City from the above table 'Students'.
CREATE VIEW Stud AS
SELECT Stud_ID, Stud_Name
FROM Students
WHERE Country='India'
Result is shown in the following table:
| Stud_ID | Stud_Name |
|---|---|
| 4 | Jaya |
| 5 | Surendra |
Note: SELECT * FROM students; statement is used to see the created view.
2. Update VIEW
User can modify the view in SQL server without dropping it by using the ALTER VIEW statement.Syntax
ALTER VIEW [Schema_name] AS
SELECT expression
FROM table_name
Where [condition]
Example: Update view using ALTER VIEW statement.
Consider the following table titled 'Students'.
| Stud_ID | Stud_Name | City | Country |
|---|---|---|---|
| 1 | Mark | London | England |
| 2 | Alex | Paris | France |
| 3 | Bob | Sydney | Australia |
| 4 | Jaya | Delhi | India |
| 5 | Surendra | Baroda | India |
To update view titled 'Stud', the query should be written as:
ALTER VIEW Stud AS
SELECT Stud_ID, Stud_Name
FROM Students
WHERE Country = 'Australia'
Result is shown in the following table.
| Stud_ID | Stud_Name |
|---|---|
| 3 | Bob |
3. SQL Server DROP VIEW
The SQL Server DROP VIEW Statement is used to delete or remove the view.Syntax:
DROP VIEW View_name;
Example: Query using DROP VIEW Statement.
If user needs to delete view titled as 'Students'. The query should be written as:
DROP VIEW Students
