Database Reference
In-Depth Information
By ThE WAy
Views are a standard and popular SQL construct. Microsoft Access, how-
ever, does not support them. Instead, in Microsoft Access, you can create
a view-equivalent
query
, name it, and then save it. You can then process the query in
the same ways that we process views in the following discussion.
SQL Server, Oracle Database, and MySQL all support views, and they are an
important structure with many uses. Do not conclude from Microsoft Access's lack of
support that views are unimportant. Read on, and, if possible, use SQL Server, Oracle
Database, or MySQL to process the statements in this section.
The following statement defines a view named CustomerNameView on the CUSTOMER table:
/* *** SQL-CREATE-VIEW-CH07-01 *** */
CREATE VIEW CustomerNameView AS
SELECT LastName AS CustomerLastName,
FirstName AS CustomerFirstName,
FROM
CUSTOMER;
Note that the results from executing this statement will be only a system message stating the
action completed. With GUI utilities such as SQL Server Management Studio, an appropriately
named object will also be created.
By ThE WAy
The current versions of SQL Server, Oracle Database, and MySQL all process
the CREATE VIEW statements as written here without difficulty. However, an
earlier version of SQL Server, SQL Server 2000, has a quirk: to create views, you have
to remove the semicolon from the CREATE VIEW statement. We have no idea why SQL
Server 2000 accepts a semicolon for all other SQL statements but will not accept one for
SQL statements that create views. If by chance you are still using SQL Server 2000, be
aware that you must remove the semicolon when writing CREATE VIEW statements. Even
better, upgrade your version of SQL Server—Microsoft stopped supporting SQL Server
2000 in April of 2013, and important security updates are no longer being provided.
Once the view is created, it can be used in the FROM clause of SELECT statements, just
like a table. The following obtains a list of customer names in sorted order:
/* *** SQL-Query-View-CH07-01 *** */
SELECT
*
FROM
CustomerNameView
ORDER BY
CustomerLastName, CustomerFirstName;
The result for the sample data in Figure 7-16 is:
Search WWH ::
Custom Search