SQL CREATE VIEW

SQL > Data Definition Language (DDL) > ViewA view is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. A view can be built on top of a single table or multiple tables. It can also be built on top of another view. In the SQL Create View page, we will see how a view can be built. Views offer the following advantages: 1. Ease of use: A view hides the complexity of the database tables from end users. Essentially we can think of views as a layer of abstraction on top of the database tables. 2. Space savings: Views takes very little space to store, since they do not store actual data. 3. Additional data security: Views can include only certain columns in the table so that only the non-sensitive columns are included and exposed to the end user. In addition, some databases allow views to have different security settings, thus hiding sensitive data from prying eyes. 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