How to Create a view in sql and Create View SQL for summary query

In this article, you will learn how to use the CREATE VIEW SQL statement to create a view and explore methods to create a view in SQL using T-SQL (Transact-SQL). in this programmatical approach of creating, altering, and working with views using Transact-SQL.
A view is simply a virtual table, think about a query that is stored on SQL Server, and when used by a user that will look just like a table but it’s not. it is a view and does not have a structure of a table or definition. its definition and structure is simply a query, which can access many tables or a part of a table.

Create View SQL Statement

In SQL, a view is a virtual table based on the result-set of a Structured Query Language statement. a view contains rows and columns, which will be like a real table, so, the fields in a view are fields from one or more real tables in the database.
therefore, you can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Create View SQL Syntax

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

view_name
The name of the SQL VIEW that depends on your wish to create.
WHERE conditions
It is Optional that conditions must be met for the records to be included in the VIEW.

Note: The database engineer recreates data, using the view’s SQL statement, every time a user queries a view, so, a view always shows up-to-date data!

How to use the CREATE VIEW SQL?

CREATE VIEW sell_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'Vivo';

CREATE VIEW sell_orders AS

SELECT suppliers.supplier_id, orders.quantity, orders.price

FROM suppliers

INNER JOIN orders

ON suppliers.supplier_id = orders.supplier_id

WHERE suppliers.supplier_name = 'Vivo';

This CREATE VIEW SQL example, would create a virtual table, it is based on the result set of the select statement, so we can now query the SQL VIEW as the following:

SELECT *
FROM sell_orders;

1 2	SELECT * FROM sell_orders;

How to create a view in SQL with a single table

Therefore, we learn the syntax of the views, the syntax will be like below as:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

furthermore, we need to understand the specify the CREATE VIEW statement after that we have to give a name to the view. In the second row or step, we define the SELECT statement after the AS keyword. therefore, the following example will create a view that will be named as GProductSpecialList.GProductSpeciallList view fetches data from the Products table, after that it only returns the ProductID, Name, and ProductNumber columns of the Product table.
therefore, after that the creation of the view, we can retrieve data using a simple SELECT statement, furthermore, this following example shows how to fetch data from the VProductSpecialList view.

CREATE VIEW GProductSpecialList
AS
select p.ProductID AS [ProductIdNumber] , 
p.Name AS [ProductName],
p.ProductNumber [ProductMainNumber] from [Production].[Product] p 
     WHERE ProductID >500

CREATE VIEW GProductSpecialList

select p.ProductID AS [ProductIdNumber] ,

p.Name AS [ProductName],

p.ProductNumber [ProductMainNumber] from [Production].[Product] p

WHERE ProductID >500

After the creation of the view, we can retrieve data using a simple SELECT statement, therefore, the following example shows how to fetch data from the GProductSpecialList view:

SELECT * FROM GProductSpecialList

1	SELECT * FROM GProductSpecialList

	ProductIdNumber	ProductName	ProductMainNumber
1	701	Samsung note 2	SMN-002
2	702	Samsung A-654	SM-213
3	703	Nokia- SM76	N-98N
4	704	Nokia-Xper	NMR-56G
5	705	Vivo-SA43	VO-SB76
6	706	Samsung-dual432	SMD-A4F
7	707	Nokia-FM876	NFM-23FD
8	708	Samsung Galaxy-77	SMG-IO76
9	709	Vivo-S565	VP-R5656

Therefore, as you can see that in the above column names have been replaced with aliases which are defined in the query definition of the view, furthermore, in the addition the data which is fetched by the view is filtered according to the criteria of the WHERE statement.

so, there are for the different circumstances, we need some particular columns of the view for this we can only use these column names in the SELECT statement.

SELECT ProductIdNumber,ProductName FROM GProductSpecialList

1	SELECT ProductIdNumber,ProductName FROM GProductSpecialList

	ProductIdNumber	ProductName
1	701	Samsung note 2
2	702	Samsung A-654
3	703	Nokia- SM76
4	704	Nokia-Xper
5	705	Vivo-SA43
6	706	Samsung-dual432
7	707	Nokia-FM876
8	708	Samsung Galaxy-77
9	709	Vivo-S565

Update SQL VIEW

A view can be updated with the definition of a SQL VIEW without dropping it by using the SQL CREATE or REPLACE VIEW command.

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, column3 ...
FROM table_name
WHERE condition;

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, column3 ...

FROM table_name

WHERE condition;

How you can use the SQL CREATE OR REPLACE VIEW Statement?

CREATE or REPLACE VIEW sell_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'HCL';

CREATE or REPLACE VIEW sell_orders AS

SELECT suppliers.supplier_id, orders.quantity, orders.price

FROM suppliers

INNER JOIN orders

ON suppliers.supplier_id = orders.supplier_id

WHERE suppliers.supplier_name = 'HCL';

therefore, SQL CREATE OR REPLACE VIEW example would update the definition of the SQL VIEW called sell_orders without dropping it. furthermore, if the SQL VIEW did not exist, then the SQL VIEW would merely be created for the first time.

Drop SQL VIEW

Once a SQL VIEW, has been created, then you can drop it, with the SQL DROP VIEW Command or statement.
therefore, there is the following syntax for the SQL DROP VIEW Command is:

DROP VIEW view_name;

view_name
therefore, view_name indicates the name of the view that you wish to drop.

Example: How to use the SQL DROP VIEW Statement

DROP VIEW sell_orders;

1	DROP VIEW sell_orders;

As a result, this SQL DROP VIEW example would delete/drop the SQL VIEW called sell_orders.

In Interview frequently Asked Question

Question 1: Does the SQL View exist if the table is dropped from the database?
Answer: Yes, in Oracle, therefore, the SQL VIEW continues to exist even after one of the tables (that the SQL VIEW is based on) is dropped from the database.
furthermore, if you are trying to query the SQL VIEW after the table has been dropped, after that you will receive a message, which indicates that the SQL VIEW has errors.
If you recreate the table (the table that you simply had dropped), the SQL VIEW will again be fine.

Question 1: Can you update the data in a SQL VIEW?
Answer: A VIEW in SQL, which will be created by joining one or more tables. therefore, when you update the records in a view, then it updates the records in the underlying tables that make up the SQL View.
furthermore, so, yes, you can update the data in a SQL VIEW providing you have the proper privileges to the underlying SQL tables.