SQL Views

SQL CREATE VIEW Statement

• In SQL, a view is a virtual table based on the result-set of an SQL statement.
• 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.
• You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.
• A view is created with the CREATE VIEW statement. 

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';

SQL Updating a View

A view can be updated with the CREATE OR REPLACE VIEW statement.

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

SQL Dropping a View

A view is deleted with the DROP VIEW statement.

Syntax:

Drop view view_name;

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. 

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;

SELECT * FROM MarksView;