Why use views, View rules and restrictions.
Views use:1. Same table can be created in different forms
2. Can hide certain columns of a table
3. Can save time and efforts for queries that are frequently executed
4. Can help in manipulating data
Rules and restriction:1. The SELECT statement cannot contain a subquery in the FROM clause.
2. The SELECT statement cannot refer to system or user variables.
3. Any table or view referred to in the definition must exist.
4. A temporary view cannot be created.
5. Any tables named in the view definition must exist at definition time.
6. 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
