SQL > Data Definition Language (DDL) > Create View Statement

Views can be considered as virtual tables. Generally speaking, a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.

The syntax for creating a view is as follows:

CREATE VIEW "VIEW_NAME" AS "SQL Statement";

"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:

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;

Result:

REGION  SALES
East  700
West  2050

Next: SQL Index

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact