Create views

The following example creates a view based on a table in the stores_demo database:

CREATE VIEW name_only AS
SELECT customer_num, fname, lname FROM customer

The view exposes only three columns of the table. Because it contains no WHERE clause, the view does not restrict the rows that can appear.

The following example is based on the join of two tables:

CREATE VIEW full_addr AS
SELECT address1, address2, city, state.sname,
   zipcode, customer_num
   FROM customer, state
   WHERE customer.state = state.code

The table of state names reduces the redundancy of the database; it lets you store the full state names only once, which can be useful for long state names such as Minnesota. This full_addr view lets users retrieve the address as if the full state name were stored in every row. The following two queries are equivalent:

SELECT * FROM full_addr WHERE customer_num = 105

SELECT address1, address2, city, state.sname,
   zipcode, customer_num
   FROM customer, state
   WHERE customer.state = state.code AND customer_num = 105

However, be careful when you define views that are based on joins. Such views are not modifiable; that is, you cannot use them with UPDATE, DELETE, or INSERT statements. For an explanation of how to modify with views, see Modify with a view.

The following example restricts the rows that can be seen in the view:

CREATE VIEW no_cal_cust AS
   SELECT * FROM customer WHERE NOT state = 'CA'

This view exposes all columns of the customer table, but only certain rows. The following example is a view that restricts users to rows that are relevant to them:

CREATE VIEW my_calls AS
   SELECT * FROM cust_calls WHERE user_id = USER

All the columns of the cust_calls table are available but only in those rows that contain the user IDs of the users who can execute the query.