SQL CREATE VIEW

Views can be considered as virtual tables. Generally speaking, a table has a set of definition that sets the structure of the table, and physically stores the data. A view also has a set of definitions, which is built on top of the table(s) or another 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

(First_Name     char(50),
Last_Name       char(50),
Address         char(50),
City            char(50),
Country         char(25),
Birth_Date      date)

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 as below:

View          V_Customer
(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 when they need to combine the information from these two tables. Let’s say we have the following two tables:
Table Store_Information

store_name    Sales   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