SQL CREATE VIEW

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:

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:

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

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<=(
SELECT COUNT(*)
FROM myview b 
WHERE a.agent_code=b.agent_code);

Output:

To execute query on this view

SQL Code:

SELECT * FROM myview1;

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Union
Next: Create view with aggregate functions count(), sum() and avg()