how to create a view in sql

SQL CREATE VIEW

Last update on February 26 2020 08:07:43 (UTC/GMT +8 hours)

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

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:

Sql creating view with arithmetic expression

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:

Sql creating view with order by in descending order

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:

Sql creating view with subqueries using distinct

To execute query on this view

SQL Code:

          SELECT * FROM myview1;

Practice SQL Exercises

SQL Exercises, Practice, Solution
SQL Retrieve data from tables [33 Exercises]
SQL Boolean and Relational operators [12 Exercises]
SQL Wildcard and Special operators [22 Exercises]
SQL Aggregate Functions [25 Exercises]
SQL Formatting query output [10 Exercises]
SQL Quering on Multiple Tables [7 Exercises]
FILTERING and SORTING on HR Database [38 Exercises]
SQL JOINS

SQL JOINS [29 Exercises]
SQL JOINS on HR Database [27 Exercises]

SQL SUBQUERIES

SQL SUBQUERIES [39 Exercises]
SQL SUBQUERIES on HR Database [55 Exercises]

SQL Union[9 Exercises]
SQL View[16 Exercises]
SQL User Account Management [16 Exercise]
Movie Database

BASIC queries on movie Database [10 Exercises]
SUBQUERIES on movie Database [16 Exercises]
JOINS on movie Database [24 Exercises]

Soccer Database

Introduction
BASIC queries on soccer Database [29 Exercises]
SUBQUERIES on soccer Database [33 Exercises]

JOINS queries on soccer Database [61 Exercises]

Hospital Database

Introduction
BASIC, SUBQUERIES, and JOINS [39 Exercises]

Employee Database

BASIC queries on employee Database [115 Exercises]
SUBQUERIES on employee Database [77 Exercises]

More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

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