What is VIEW?
A VIEW is actually a query and the output of the query becomes the content of the view. The VIEW can be treated as a base table and it can be QUERIED, UPDATED, INSERTED INTO, DELETED FROM and JOINED with other tables and views.
A VIEW is a data object which does not contain any data. Its contents are the resultant of a base table. They are operated just like the base table but they don’t contain any data of their own.
A view can be accessed with the use of SQL SELECT statement like a table. A view can also be made up by selecting data from more than one tables.
SQL CREATE VIEW
Syntax:
CREATE [RECURSIVE] VIEW view_name {[[column[, ...]]] | [OF udt_name [UNDER supertype_name [REF IS column_name {SYSTEM GENERATED | USER GENERATED | DERIVED}] [column_name WITH OPTIONS SCOPE table_name]]]} AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]Parameters:
| CREATE VIEW view_name | Creates a view with a name of the view. |
| RECURSIVE | Creates a view which collects values from itself and must have a column clause and may not use the WITH clause. |
| [[column[, ...]]] | Names all of the columns in the view. The number of columns specified must match the number of columns in the select_statement. The view will create the name of columns names from the columns in the table when not mention the columns. |
| OF udt_name [UNDER supertype_name] | Defines the view on a UDT rather than on the column clause.Use the UNDER clause to define a view on a subtype. |
| REF IS column_name {SYSTEM GENERATED | USER GENERATED | DERIVED | Defines the object-ID column for the view. |
| column_name WITH OPTIONS SCOPE table_name | Provides scoping for a reference column in the view. |
| AS select_statement | Defines the exact SELECT statement that provides the data of the view. |
| WITH [CASCADED | LOCAL] CHECK OPTION | Used only on views that allow updates to their base tables and ensure that only those data which may be read by the view those only may be inserted, updated, or deleted by the view. CASCADED performs the check option for the current view and all views upon which it is built and LOCAL performs the check option only for the current view, even when it is built upon other views. |
Example:
Sample table: agents
To create a view 'agentview' as the table 'agents', the following SQL statement can be used:
SQL Code:
CREATE VIEW agentview AS SELECT * FROM agents;Output:
To execute query on this view
SQL Code:
SELECT * FROM agentview;SQL Create View with WHERE
Here we are going to discuss the usage of WHERE clause along with the VIEW command to store records in the view based on certain conditions.
Example:
Sample table: agents
To create a view 'agentview' as the table 'agents' with the following condition -
1. 'working_area' must be 'Bangalore',
the following SQL statement can be used:
SQL Code:
CREATE VIEW agentview AS SELECT * FROM agents WHERE working_area=’Bangalore’;Output:
To execute query on this view
SQL Code:
SELECT * FROM agentview;SQL Create View with specific columns and WHERE
Here we are going to discuss, how some specific columns of another table can make a view in CREATE VIEW statement.
Example:
Sample table: agents
To create a view 'agentview' with the columns agent_code, agent_name and working_area of the table 'agents' with the following condition -
1.'working_area' must be 'Bangalore',
the following SQL statement can be used:
SQL Code:
Output:
To execute query on this view
SQL Code:
SELECT * FROM agentview;SQL Create View with arithmetic expression
Here we are discussing that, arithmetic expression can also be used to create a view in a CREATE VIEW statement.
Example:
Sample table: customer
To create a view 'myclient' with three columns 'client_name', 'client_no' and 'outspercent' from the table 'customer' with following conditions -
1. 'outspercent' column must be created with 'outstanding_amt'*100/['opening_amt'+'receive_amt'] from the customer table,
2. 'cust_country' of 'customer' table must be 'USA',
3. 'outspercent' must be greater than 50,
the following SQL statement can be used:
SQL Code:
CREATE VIEW myclient[client_name,client_no,outspercent] AS SELECT cust_name,cust_code,outstanding_amt*100/[opening_amt+receive_amt] FROM customer WHERE cust_country='USA' AND outstanding_amt*100/[opening_amt+receive_amt]>50;Output:
To execute query on this view
SQL Code:
SELECT * FROM myclient;SQL Create View with AND operator
Here we are going to discuss the usage of WHERE clause and AND operator along with the CREATE VIEW command.
Example:
Sample table: agents
To create a view 'agentview' as the table 'agents' with following conditions-
1. 'working_area' must be ' 'Bangalore',
2. 'commission' must be greater than .1,
the following SQL statement can be used :
SQL Code:
CREATE VIEW agentview AS SELECT * FROM agents WHERE working_area='Bangalore' AND commission>.1;Output:
To execute query on this view
SQL Code:
SELECT * FROM agentview;SQL Create View with IN
Here we are going to discuss the usage of IN operator along with the VIEW command in a CREATE VIEW statement.
Example:
Sample table: agents
To create a view 'agentview' as the table 'agents' with the following condition -
1. 'agent_code' must be any of the following agent_codes - 'A006', 'A004', 'A001', 'A009',
the following SQL statement can be used:
SQL Code:
CREATE VIEW agentview AS SELECT * FROM AGENTS WHERE agent_code IN ['A006','A004','A001','A009'];Output:
To execute query on this view
SQL Code:
SELECT * FROM agentview;SQL Create View with BETWEEN
Here we are going to discuss the usage of BETWEEN operator along with the VIEW command in a CREATE VIEW statement.
Example:
Sample table: customer
To create a view 'customerview' as the table 'customer' with the following condition -
1. 'cust_name' must begin with the letter from 'A' through 'J',
the following SQL statement can be used:
SQL Code:
CREATE VIEW customerview AS SELECT * FROM customer WHERE cust_name BETWEEN 'A' AND 'J';Output:
To execute query on this view
SQL Code:
SELECT * FROM customerview;SQL Create View with LIKE
Here we are going to discuss the usage of LIKE operator along with the VIEW command in a CREATE VIEW statement.
Example:
Sample table: customer
To create a view 'agentview' as the table 'agents' with the following condition -
1. 'cust_name' must not begin with the letter 'M',
the following SQL statement can be used:
SQL Code:
CREATE VIEW agentview AS SELECT * FROM agents WHERE agent_name NOT LIKE 'M%';Output:
To execute query on this view
SQL Code:
SELECT * FROM agentview;SQL Create View with HAVING
Here we are discussing the usage of aggregate COUNT[] and HAVING clause along with the CREATE VIEW command. The aggregate function COUNT can’t be used as a predicate with WHERE clause but HAVING can be used.
Example:
Sample table: customer
To create a view 'countgrade' with two columns 'grade' and 'gradecount' from the table 'customer' with following conditions -
1. 'gradecount' column creating with count[*] from the customer table,
2. unique 'grade' must be within the group,
3. number of grades per group must be 3,
the following SQL statement can be used:
SQL Code:
CREATE VIEW countgrade[grade,gradecount] AS SELECT grade,COUNT[*] FROM customer GROUP BY grade HAVING count[*]=3;Output:
To execute query on this view
SQL Code:
SELECT * FROM countgrade;SQL Create View with order by in descending order
Here we are going to discuss the usage of ORDER BY along with the CREATE VIEW command which arranges the view in an order.
Example:
Sample table: agents
To create a view 'agentview' with the columns 'agent_name', 'working_area' and 'commission' of the table 'agents' with the following condition -
1. 'agent_name' and 'commission' must be arranged in descending order,
the following SQL statement can be used:
SQL Code:
CREATE VIEW agentview AS SELECT agent_name, working_area, commission FROM AGENTS ORDER BY agent_name,commission DESC;Output:
To execute query on this view
SQL Code:
SELECT * FROM agentview;SQL Create a View from a View
Here, we are going to discuss how to create a SQL VIEW from a VIEW using CREATE VIEW statement.
Example:
Sample table: agents
Sample table: orders
The statement bellow creates the VIEW 'myview' -
SQL Code:
CREATE VIEW myview AS SELECT b.ord_date,a.agent_code,a.agent_name FROM agents a, orders b WHERE a.agent_code=b.agent_code AND b.ord_amount=[ SELECT MAX[ord_amount] FROM orders c WHERE c.ord_date=b.ord_date];To create a view 'myview1' from the view 'myview' with following conditions -
1. 'a' and 'b' are the aliases of 'myview' view,
2. unique 'agent_code' and 'agent_name' combination form 'myview' of alias 'a' will comes once which satisfies the condition bellow:
i]. the number of rows for alias 'b' must be less than or equal to 5 which satisfies the condition bellow :
a]. 'agent_code' of alias 'a' and alias 'b' must be same,
the following SQL statement can be used:
SQL Code:
CREATE VIEW myview1 AS SELECT DISTINCT agent_code,agent_name FROM myview a WHERE 5