Why use views. View rules and restrictions.

Views use:

• Same table can be created in different forms
• Can hide certain columns of a table
• Can save time and efforts for queries that are frequently executed
• Can help in manipulating data

Rules and restriction:

• The SELECT statement cannot contain a subquery in the FROM clause.
• The SELECT statement cannot refer to system or user variables.
• Any table or view referred to in the definition must exist
• A temporary view cannot be created
• Any tables named in the view definition must exist at definition time
• A trigger cannot be associated with a view

CREATE VIEW Syntax with an example

SQL CREATE VIEW: A view is a virtual table. 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.
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example:
CREATE VIEW [sample] AS
SELECT employeeID,employeeName
FROM employee
WHERE salary > 10000

ALTER VIEW Syntax with an example

The existing view can be updated as shown below. Below we are adding an additional columns salary.
CREATE VIEW [employee_details] AS
SELECT employeeID,employeename,salary
FROM Employee
WHERE Discontinued=No

DROP VIEW Syntax with an example

The view can be dropped by using the DROP statement.
Syntax:

DROP view view_name