A view table is a view of one or more existing tables. Parts of these tables are hidden, and others remain visible.
You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
To create a view table, use the CREATE VIEW statement.
CREATE VIEW hotel.customer_addr (cno, title, name, zip, city, state, address)
AS SELECT customer.cno, customer.title, customer.name, customer.zip, city.name, city.state, customer.address
FROM hotel.customer, hotel.city
WHERE customer.zip = city.zip
The customer_addr view table is comprised of information from the customer and city tables.
When the tables have been filled with the data specified for the SQL tutorial (SQL Statements for the HOTEL Demo Schema), you can get the following data from the customer_addr view table:
SELECT * FROM hotel.customer_addr ORDER BY cno
Result
CNO |
TITLE |
NAME |
ZIP |
CITY |
STATE |
ADDRESS |
---|---|---|---|---|---|---|
3000 |
Mrs |
Porter |
10580 |
New York |
NY |
1340 N.Ash Street, #3 |
3100 |
Mr |
Brown |
48226 |
Detroit |
MI |
1001 34th Str., APT.3 |
3200 |
Company |
Datasoft |
90018 |
Los Angeles |
CA |
486 Maple Str. |
3300 |
Mrs |
Brian |
75243 |
Dallas |
TX |
500 Yellowstone Drive, #2 |
3400 |
Mrs |
Griffith |
20005 |
Washington |
DC |
3401 Elder Lane |
3500 |
Mr |
Randolph |
60615 |
Chicago |
IL |
340 MAIN STREET, #7 |
3600 |
Mrs |
Smith |
75243 |
Dallas |
TX |
250 Curtis Street |
3700 |
Mr |
Jackson |
45211 |
Cincinnati |
OH |
133 BROADWAY APT. 1 |
3800 |
Mrs |
Doe |
97213 |
Portland |
OR |
2000 Humboldt Str., #6 |
3900 |
Mr |
Howe |
75243 |
Dallas |
TX |
111 B Parkway, #23 |
4000 |
Mr |
Miller |
95054 |
Santa Clara |
CA |
27 5th Str., 76 |
4100 |
Mrs |
Baker |
90018 |
Los Angeles |
CA |
200 MAIN STREET, #94 |
4200 |
Mr |
Peters |
92714 |
Irvine |
CA |
700 S. Ash Str., APT.12 |
4300 |
Company |
TOOLware |
20019 |
Washington |
DC |
410 Mariposa Str., #10 |
4400 |
Mr |
Jenkins |
20903 |
Silver Spring |
MD |
55 A Parkway, #15 |
When you create a view table, you can rename and rearrange columns. You can link together several tables. To define a View table, you can use every SELECT statement that does not contain ORDER BY.
You can always use a view table name in SELECT statements and, in special cases, in INSERT, UPDATE and DELETE statements.
See also: SQL Reference Manual, CREATE VIEW Statement (create_view_statement)
Evaluating System Tables, VIEWS, VIEWDEFS, VIEWCOLUMNS
For more CREATE View example statements see: Concepts of the Database System, Objects in the Schema HOTEL
To delete a View table, use the DROP VIEW statement.
DROP VIEW hotel.customer_addr
You can use this SQL statement to delete the view definition. This does not affect the table contents.
See also: SQL Reference Manual, DROP VIEW Statement (drop_view_statement)
More examples for Data Definition