Create View in SQL - sql - sql tutorial - learn sql




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.
  • A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
  • 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.

Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition; 
  • "SQL Statement" can be any of the SQL statements we have discussed in this tutorial.
  • Let's use a simple example to illustrate. Say we have the following table:

Table Customer

Column Name Data Type
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date datetime
  • and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer;
  • Now we have a view called V_Customer with the following structure:
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

View V_Customer

Column Name Data Type
First_Name char(50)
Last_Name char(50)
Country char(25)
  • We can also use a view to apply joins to two tables.
  • In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler.
  • Let's say we have the following two tables:

Table Store_Information

Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999

Table Geography

Region_Name Store_Name
East Boston
East New York
West Los Angeles
West San Diego
  • And we want to build a view that has sales by region information. We would issue the following SQL statement:
CREATE VIEW V_REGION_SALES
AS SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.Store_Name = A2.Store_Name
GROUP BY A1.Region_Name;
  • This gives us a view, V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view, we type in,
SELECT * FROM V_REGION_SALES;

REGION SALES
East 700
West 2050

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Create View in SQL