The VIEWS is representation of a data from one or more tables. Data can be extracted from tables using statements like WHERE, JOIN ... etc. A VIEW is a database specific; there can be a database name prefixed with VIEW name.

The syntax for creating VIEWS is as shown:

Syntax

CREATE VIEW viewName AS 

SELECT columnName/columnNames 

FROM tableName WHERE condition;

The example of creating VIEW using INNER JOIN is as given:

CREATE VIEW patientInfo AS 

SELECT pat.PatientID, pat.NameOfPatient, pres.CaseDateTime 

FROM tblpatients pat 

INNER JOIN tblprescription pres ON pat.PatientID=pres.PatientID;

 

SELECT * FROM patientInfo;

This VIEW displays columns of data of PatientID, NameofPatient, CaseDateTime.

Views can also be selected with sub-query as below:

CREATE VIEW items AS 

SELECT productCode, productName FROM tblproducts 

WHERE profitMargin > (SELECT AVG (profit) FROM tblproducts);

If a view has to be created or if it already exists and it has to be replaced, we use syntax from below:

CREATE OR REPLACE VIEW viewName AS

SELECT columnName/columnNames 

FROM tableName WHERE condition;

Or:

CREATE OR REPLACE VIEW patientInfo AS

SELECT pat.PatientID, pat.NameOfPatient, pres.CaseDateTime 

FROM tblpatients pat 

INNER JOIN tblprescription pres ON pat.PatientID=pres.PatientID;