View tables 

A view table is a view of one or more existing tables. Parts of these tables are hidden, and others remain visible.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Creating a View Table

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 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 St., APT.3
3200	Company	Datasoft	90018	Los Angeles	CA	486 Maple St.
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 St., #6
3900	Mr	Howe	75243	Dallas	TX	111 B Parkway, #23
4000	Mr	Miller	95054	Santa Clara	CA	27 5th St., 76
4100	Mrs	Baker	90018	Los Angeles	CA	200 MAIN STREET, #94
4200	Mr	Peters	92714	Irvine	CA	700 S. Ash St., APT.12
4300	Company	TOOLware	20019	Washington	DC	410 Mariposa St., #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 HOTEL Schema

 

Deleting a View Table

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