@jenyJun 02.2006 — #We can 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:
[B]Table Store_Information [/B]
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
[B]Table Geography [/B]
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,