How to Create a Table View with SQL
At times, you want to retrieve specific information from the CUSTOMER table in SQL. You don’t want to look at everything — only specific columns and rows. What you need is a view.
A view is a virtual table. In most implementations, a view has no independent physical existence. The view’s definition exists only in the database’s metadata, but the data comes from the table or tables from which you derive the view. The view’s data is not physically duplicated.
If the information you want exists in a single table, you can create a single-table view of the data. For example, suppose you want to look at the names and telephone numbers of all customers who live in the state of New Hampshire. You can create a view from the CUSTOMER table that contains only the data you want. The following SQL statement creates this view:
CREATE VIEW NH_CUST AS SELECT CUSTOMER.FirstName, CUSTOMER.LastName, CUSTOMER.Phone FROM CUSTOMER WHERE CUSTOMER.State = 'NH' ;
You can accomplish the same task with less typing if your SQL implementation assumes that all table references are the same as the ones in the FROM clause. If your system makes that reasonable default assumption, you can reduce the statement to the following lines:
CREATE VIEW NH_CUST AS SELECT FirstName, LastName, Phone FROM CUSTOMER WHERE STATE = 'NH';
Although the second version is easier to write and read, it’s more vulnerable to disruption from ALTER TABLE commands. Such disruption isn’t a problem for this simple case, which has no JOIN, but views with JOINs are more robust when they use fully qualified names.
More often than not, you need to pull data from two or more tables to answer your question. Suppose, for example, that you work for a sporting goods store, and you want to send a promotional mailing to all the customers who have bought ski equipment since the store opened last year.
You need information from the CUSTOMER table, the PRODUCT table, the INVOICE table, and the INVOICE_LINE table. You can create a multitable view that shows the data you need. Each time you use the view, it reflects any changes that occurred in the underlying tables since you last used the view.
The database for this sporting goods store contains four tables: CUSTOMER, PRODUCT, INVOICE, and INVOICE_LINE.
|LastName||CHAR (20)||NOT NULL|
Notice that some of the columns contain the constraint NOT NULL. These columns are either the primary keys of their respective tables or columns that you decide must contain a value. A table’s primary key must uniquely identify each row. To do that, the primary key must contain a non-null value in every row.
The tables relate to each other through the columns that they have in common. The following list describes these relationships:
The CUSTOMER table bears a one-to-many relationship to the INVOICE table. One customer can make multiple purchases, generating multiple invoices. Each invoice, however, deals with one, and only one, customer.
The INVOICE table bears a one-to-many relationship to the INVOICE_LINE table. An invoice may have multiple lines, but each line appears on one, and only one, invoice.
The PRODUCT table also bears a one-to-many relationship to the INVOICE_LINE table. A product may appear on more than one line on one or more invoices. Each line, however, deals with one, and only one, product.
The CUSTOMER table links to the INVOICE table by the common CustomerID column. The INVOICE table links to the INVOICE_LINE table by the common InvoiceNumber column. The PRODUCT table links to the INVOICE_LINE table by the common ProductID column. These links are what makes this database a relational database.
To access the information about customers who bought ski equipment, you need FirstName, LastName, Street, City, State, and Zipcode from the CUSTOMER table; Category from the PRODUCT table; InvoiceNumber from the INVOICE table; and LineNumber from the INVOICE_LINE table. You can create the view you want in stages by using the following statements:
CREATE VIEW SKI_CUST1 AS SELECT FirstName, LastName, Street, City, State, Zipcode, InvoiceNumber FROM CUSTOMER JOIN INVOICE USING (CustomerID) ; CREATE VIEW SKI_CUST2 AS SELECT FirstName, LastName, Street, City, State, Zipcode, ProductID FROM SKI_CUST1 JOIN INVOICE_LINE USING (InvoiceNumber) ; CREATE VIEW SKI_CUST3 AS SELECT FirstName, LastName, Street, City, State, Zipcode, Category FROM SKI_CUST2 JOIN PRODUCT USING (ProductID) ; CREATE VIEW SKI_CUST AS SELECT DISTINCT FirstName, LastName, Street, City, State, Zipcode FROM SKI_CUST3 WHERE CATEGORY = 'Ski' ;
These CREATE VIEW statements combine data from multiple tables by using the JOIN operator.
Here’s a rundown of the four CREATE VIEW statements:
The first statement combines columns from the CUSTOMER table with a column of the INVOICE table to create the SKI_CUST1 view.
The second statement combines SKI_CUST1 with a column from the INVOICE_LINE table to create the SKI_CUST2 view.
The third statement combines SKI_CUST2 with a column from the PRODUCT table to create the SKI_CUST3 view.
The fourth statement filters out all rows that don’t have a category of Ski. The result is a view (SKI_CUST) that contains the names and addresses of all customers who bought at least one product in the Ski category.
The DISTINCT keyword in the fourth CREATE VIEW’s SELECT clause ensures that you have only one entry for each customer, even if some customers made multiple purchases of ski items.