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