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.
Contents:
- Syntax & Parameters
- Example: SQL View
- SQL Create View with WHERE
- SQL Create View with specific columns and WHERE
- SQL Create View with arithmetic expression
- SQL Create View with AND operator
- SQL Create View with IN
- SQL Create View with BETWEEN
- SQL Create View with LIKE
- SQL Create View with HAVING
- SQL Create View with order by in descending order
- SQL Create a View from a View
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. 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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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()
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql/creating-views/creating-view.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics