SQL | Views
In SQL, a view is a virtual table based on the result-set of an SQL statement. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database.
- Views in SQL are kind of virtual tables.
- A View can either have all the rows of a table or specific rows based on certain condition.
- The changes made in a View are not reflected back to the actual table in the database.
- SQL functions like WHERE, and JOIN can be applied to a view and present the data as if the data were coming from one single table.
- A view of table always shows up-to-date data. Everytime a uswer queries a view, the table is recreated.
StudentDetails
StudentMarks
CREATING VIEWS
We can create View using CREATE VIEW statement. A View can be created from a single table or multiple tables.
Syntax:
CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE condition; view_name: Name for the View table_name: Name of the table condition: Condition to select rows
Examples:
- Creating View from a single table: In this example we will create a View named DetailsView from the table StudentDetails.
Query:CREATE VIEW DetailsView AS SELECT NAME, ADDRESS FROM StudentDetails WHERE S_ID < 5;
To see the data in the View, we can query the view in the same manner as we query a table.
SELECT * FROM DetailsView;
Output:
- Creating View from multiple table: In this example we will create a View named MarksView from two tables StudentDetails and StudentMarks. To create a View from multiple tables we can simply include multiple tables in the SELECT statemen.Query:
CREATE VIEW MarksView AS SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS FROM StudentDetails, StudentMarks WHERE StudentDetails.NAME = StudentMarks.NAME;
To display data of View MarksView:
SELECT * FROM MarksView;
Output:
DELETING VIEWS
We have learned about creating a View, but what if a created View is not needed any more? Obviously we will want to delete it. SQL allows us to delete an existing View. We can delete or drop a View using the DROP statement.
Syntax:
DROP VIEW view_name; view_name: Name of the View which we want to delete.
For example, if we want to delete the View MarksView, we can do this as:
DROP VIEW MarksView;
UPDATING VIEWS
We can use the CREATE OR REPLACE VIEW statement to add or remove fields from a view.
Syntax:
CREATE OR REPLACE VIEW view_name AS SELECT column1,coulmn2,.. FROM table_name WHERE condition;
For example, if we want to update the view MarksView and add the field AGE to this View from StudentMarks Table, we can do this as:
CREATE OR REPLACE VIEW MarksView AS SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks.AGE FROM StudentDetails, StudentMarks WHERE StudentDetails.NAME = StudentMarks.NAME;
If we fetch all the data from MarksView now as:
SELECT * FROM MarksView;
Output:
Contact Us