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:

-- Creating a view named agentview
CREATE VIEW agentview 
-- Defining the view's query to select all columns from the agents table
AS SELECT * FROM agents;

Explanation:

• This SQL code creates a view named "agentview."


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "agentview" is defined by selecting all columns (*) from the "agents" table.


• Once created, the view "agentview" can be queried like a regular table, providing a convenient way to access the data from the "agents" table without needing to rewrite the query each time.

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:

-- Creating a view named agentview
CREATE VIEW agentview
-- Defining the view's query to select all columns from the agents table
-- and filtering the rows where the working_area is 'Bangalore'
AS SELECT * 
FROM agents
WHERE working_area = 'Bangalore';

Explanation:

• This SQL code creates a view named "agentview."


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "agentview" is defined by selecting all columns (*) from the "agents" table.


• Additionally, a WHERE clause is used to filter the rows, retaining only those where the working_area is 'Bangalore'.


• Once created, the view "agentview" will contain only the rows from the "agents" table where the working_area is 'Bangalore'. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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:

-- Creating a view named agentview
CREATE VIEW agentview
-- Defining the view's query to select specific columns from the agents table
-- and filtering the rows where the working_area is 'Bangalore'
AS SELECT agent_code, agent_name, working_area
-- Selecting agent_code, agent_name, and working_area columns
FROM agents
-- Filtering rows based on the condition that the working_area is 'Bangalore'
WHERE working_area = 'Bangalore';

Explanation:

• This SQL code creates a view named "agentview."


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "agentview" is defined by selecting specific columns (agent_code, agent_name, and working_area) from the "agents" table.


• Additionally, a WHERE clause is used to filter the rows, retaining only those where the working_area is 'Bangalore'.


• Once created, the view "agentview" will contain only the specified columns from the "agents" table where the working_area is 'Bangalore'. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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:

-- Creating a view named myclient
CREATE VIEW myclient(client_name, client_no, outspercent)
-- Defining the view's query to select specific columns and compute a derived column
-- from the customer table where the cust_country is 'USA'
-- and the outspercent is greater than 50%
AS SELECT cust_name, cust_code, outstanding_amt * 100 / (opening_amt + receive_amt)
-- Selecting cust_name, cust_code, and computing outspercent
FROM customer
-- Filtering rows based on the condition that the cust_country is 'USA'
-- and the calculated outspercent is greater than 50%
WHERE cust_country = 'USA' 
AND outstanding_amt * 100 / (opening_amt + receive_amt) > 50;

Explanation:

• This SQL code creates a view named "myclient".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "myclient" is defined by selecting specific columns (client_name, client_no) from the "customer" table and computing a derived column (outspercent).


• The derived column "outspercent" is calculated as the percentage of outstanding amount compared to the total amount (opening_amt + receive_amt), multiplied by 100.


• Additionally, a WHERE clause is used to filter the rows, retaining only those where the cust_country is 'USA' and the calculated outspercent is greater than 50%.


• Once created, the view "myclient" will contain client names, client codes, and the percentage of outstanding amounts for customers in the USA with outspercent greater than 50%. This view can be queried like a regular table, providing a convenient way to access this filtered and computed subset of data.

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:

-- Creating a view named agentview
CREATE VIEW agentview
-- Defining the view's query to select all columns from the agents table
-- and filtering the rows where the working_area is 'Bangalore'
-- and the commission is greater than 0.1
AS SELECT *  
-- Selecting all columns from the agents table
FROM agents
-- Filtering rows based on the condition that the working_area is 'Bangalore'
-- and the commission is greater than 0.1
WHERE working_area = 'Bangalore'
AND commission > 0.1;

Explanation:

• This SQL code creates a view named "agentview".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "agentview" is defined by selecting all columns (*) from the "agents" table.


• Additionally, a WHERE clause is used to filter the rows, retaining only those where the working_area is 'Bangalore' and the commission is greater than 0.1.


• Once created, the view "agentview" will contain all columns from the "agents" table for agents working in Bangalore with a commission greater than 0.1. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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:

-- Creating a view named agentview
CREATE VIEW agentview
-- Defining the view's query to select all columns from the AGENTS table
-- and filtering the rows where the agent_code is among the specified values
AS SELECT * 
-- Selecting all columns from the AGENTS table
FROM AGENTS
-- Filtering rows based on the condition that the agent_code is among the specified values
WHERE agent_code IN ('A006', 'A004', 'A001', 'A009');

Explanation:

• This SQL code creates a view named "agentview".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "agentview" is defined by selecting all columns (*) from the "AGENTS" table.


• Additionally, a WHERE clause is used to filter the rows, retaining only those where the agent_code is among the specified values ('A006', 'A004', 'A001', 'A009').


• Once created, the view "agentview" will contain all columns from the "AGENTS" table for agents with agent_code values among the specified ones. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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:

-- Creating a view named customerview
CREATE VIEW customerview
-- Defining the view's query to select all columns from the customer table
-- and filtering the rows where the cust_name falls within the specified range
AS SELECT * 
-- Selecting all columns from the customer table
FROM customer
-- Filtering rows based on the condition that the cust_name falls within the specified range
WHERE cust_name BETWEEN 'A' AND 'J';

Explanation:

• This SQL code creates a view named "customerview".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "customerview" is defined by selecting all columns (*) from the "customer" table.


• Additionally, a WHERE clause is used to filter the rows, retaining only those where the cust_name falls within the range from 'A' to 'J'.


• Once created, the view "customerview" will contain all columns from the "customer" table for customers whose names fall within the specified range. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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:

-- Creating a view named agentview
CREATE VIEW agentview
-- Defining the view's query to select all columns from the agents table
-- and filtering the rows where the agent_name does not start with 'M'
AS SELECT *
-- Selecting all columns from the agents table
FROM agents
-- Filtering rows based on the condition that the agent_name does not start with 'M'
WHERE agent_name NOT LIKE 'M%';

Explanation:

• This SQL code creates a view named "agentview".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "agentview" is defined by selecting all columns (*) from the "agents" table.


• Additionally, a WHERE clause is used to filter the rows, retaining only those where the agent_name does not start with 'M'. The NOT LIKE operator is used in combination with the pattern 'M%' to specify that the agent_name should not begin with 'M'.


• Once created, the view "agentview" will contain all columns from the "agents" table for agents whose names do not start with 'M'. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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:

-- Creating a view named countgrade
CREATE VIEW countgrade(grade, gradecount) AS 
-- Defining the view's query to select the grade and the count of occurrences
-- from the customer table where the count of occurrences for each grade is 3
SELECT grade, COUNT(*) 
-- Selecting the grade and counting occurrences from the customer table
FROM customer 
-- Grouping the rows by grade
GROUP BY grade 
-- Filtering groups based on the condition that the count of occurrences is 3
HAVING COUNT(*) = 3;

Explanation:

• This SQL code creates a view named "countgrade".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view with two columns, "grade" and "gradecount".


• The view's query selects the "grade" column and the count of occurrences of each grade from the "customer" table.


• The rows are grouped by the "grade" column using the GROUP BY clause.


• The HAVING clause filters the groups, retaining only those where the count of occurrences is equal to 3.


• Once created, the view "countgrade" will contain the grades and their respective counts where the count of occurrences is exactly 3. This view can be queried like a regular table, providing a convenient way to access this aggregated subset of data.

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:

-- Creating a view named agentview
CREATE VIEW agentview
-- Defining the view's query to select specific columns from the AGENTS table
-- and ordering the results by agent_name in ascending order and commission in descending order
AS SELECT agent_name, working_area, commission
-- Selecting agent_name, working_area, and commission columns from the AGENTS table
FROM AGENTS
-- Ordering the results by agent_name in ascending order
-- and commission in descending order
ORDER BY agent_name, commission DESC;

Explanation:

• This SQL code creates a view named "agentview".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "agentview" is defined by selecting specific columns (agent_name, working_area, and commission) from the "AGENTS" table.


• The ORDER BY clause is used to sort the results. The results are sorted first by agent_name in ascending order and then by commission in descending order.


• Once created, the view "agentview" will contain agent names, working areas, and commissions sorted by agent name in ascending order and commission in descending order. This view can be queried like a regular table, providing a convenient way to access this sorted subset of data.

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:

-- Creating a view named myview
CREATE VIEW myview 
-- Defining the view's query to select specific columns from the agents and orders tables
-- and joining the agents and orders tables based on the agent_code column
-- to retrieve the order date, agent code, and agent name
AS SELECT  b.ord_date, a.agent_code, a.agent_name
-- Selecting the order date from the orders table
-- and the agent code and agent name from the agents table
FROM agents a, orders b
-- Joining the agents and orders tables based on the agent_code column
WHERE a.agent_code = b.agent_code
-- Filtering rows where the order amount matches the maximum order amount
-- for the corresponding order date
AND b.ord_amount = (
    -- Subquery to find the maximum order amount for each order date
    SELECT MAX(ord_amount)
    FROM orders c
    WHERE c.ord_date = b.ord_date
);

Explanation:

• This SQL code creates a view named "myview".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "myview" is defined by selecting specific columns (ord_date, agent_code, and agent_name) from the agents and orders tables.


• The agents and orders tables are joined based on the agent_code column to retrieve the order date, agent code, and agent name.


• The WHERE clause filters the joined rows, retaining only those where the order amount matches the maximum order amount for the corresponding order date.


• The subquery inside the WHERE clause is used to find the maximum order amount for each order date.


• Once created, the view "myview" will contain order dates, agent codes, and agent names for orders where the order amount matches the maximum order amount for the corresponding order date. This view can be queried like a regular table, providing a convenient way to access this filtered and joined subset of data.

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:

-- Creating a view named myview1
CREATE VIEW myview1
-- Defining the view's query to select distinct agent_code and agent_name columns
-- from the myview view and filtering the rows based on a condition involving counts
AS SELECT DISTINCT agent_code, agent_name
-- Selecting distinct agent_code and agent_name columns from the myview view
FROM myview a
-- Filtering rows based on a condition involving counts
WHERE 5 <= (
    -- Subquery to count the number of occurrences of each agent_code
    SELECT COUNT(*)
    FROM myview b 
    -- Joining myview with itself based on the agent_code column
    WHERE a.agent_code = b.agent_code
);

Explanation:

• This SQL code creates a view named "myview1".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "myview1" is defined by selecting distinct agent_code and agent_name columns from the "myview" view.


• The WHERE clause filters the rows based on a condition involving counts.


• The subquery inside the WHERE clause counts the number of occurrences of each agent_code in the myview view.


• Rows are filtered to retain only those where the count of occurrences of each agent_code is greater than or equal to 5.


• Once created, the view "myview1" will contain distinct agent codes and agent names for agents that have at least 5 occurrences in the original myview view. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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()