How to Create a Table View with SQL

By Allen G. Taylor

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.

Single-table view

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:

image0.jpg

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.

Multitable view

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.

Table Column Data Type Constraint
CUSTOMER CustomerID INTEGER NOT NULL
FirstName CHAR (15)
LastName CHAR (20) NOT NULL
Street CHAR (25)
City CHAR (20)
State CHAR (2)
Zipcode CHAR (10)
Phone CHAR (13)
PRODUCT ProductID INTEGER NOT NULL
Name CHAR (25)
Description CHAR (30)
Category CHAR (15)
VendorID INTEGER
VendorName CHAR (30)
INVOICE InvoiceNumber INTEGER NOT NULL
CustomerID INTEGER
InvoiceDate DATE
TotalSale NUMERIC (9,2)
TotalRemitted NUMERIC (9,2)
FormOfPayment CHAR (10)
INVOICE_LINE LineNumber INTEGER NOT NULL
InvoiceNumber INTEGER NOT NULL
ProductID INTEGER NOT NULL
Quantity INTEGER
SalePrice NUMERIC (9,2)

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.

    image1.jpg

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.

image2.jpg

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.