w3resource logo


>SQL Create View

SQL CREATE VIEW

Secondary Nav

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.

Contents:

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 :

Keywords Description
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. Tthe 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 :

CREATE VIEW agentview 
AS SELECT * FROM agents;

Output :

Sql select re-ordering columns

To execute query on this view

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 :

CREATE VIEW agentview
AS SELECT * 
FROM agents
WHERE working_area=’Bangalore’;

Output :

Sql select re-ordering columns

To execute query on this view

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 :

CREATE VIEW agentview
AS SELECT  agent_code,agent_name,working_area
FROM agents
WHERE working_area='Bangalore';

Output :

Sql select re-ordering columns

To execute query on this view

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 :

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 :

Sql creating view with arithmetic expression

To execute query on this view

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 :

CREATE VIEW agentview
AS SELECT *  
FROM agents
WHERE working_area='Bangalore'
AND commission>.1;

Output :

SQL create views using and operator

To execute query on this view

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 :

CREATE VIEW agentview
AS SELECT * 
FROM AGENTS
WHERE agent_code IN ('A006','A004','A001','A009');

Output :

Sql creating view with 'IN'

To execute query on this view

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 :

CREATE VIEW customerview
AS SELECT *
FROM customer
WHERE cust_name BETWEEN 'A' AND 'J';

Output :

Sql creating view with 'BETWEEN'

To execute query on this view

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 :

CREATE VIEW agentview
AS SELECT *
FROM agents
WHERE agent_name  NOT LIKE 'M%';

Output :

SSql creating view with 'LIKE'

To execute query on this view

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 :

CREATE VIEW countgrade(grade,gradecount) AS 
SELECT grade,COUNT(*) 
FROM customer 
GROUP BY grade 
HAVING count(*)=3;

Output :

Sql creating view with having

To execute query on this view

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 :

CREATE VIEW agentview
AS SELECT agent_name, working_area, commission
FROM AGENTS
ORDER BY agent_name,commission DESC;

Output :

Sql creating view with order by in descending order

To execute query on this view

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' -

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 :

CREATE VIEW myview1
AS SELECT DISTINCT agent_code,agent_name FROM myview a WHERE 5<=( SELECT COUNT(*) FROM myview b WHERE a.agent_code=b.agent_code);

Output :

Sql creating view with subqueries using distinct

To execute query on this view

SELECT * FROM myview1; 


Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program