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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×