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 tutorialView V_Customer
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 |