Database Reference
In-Depth Information
another table containing the number and name of every customer that currently has orders on file by joining
the Customer and Orders tables. The two tables created by this process have the same structure
fields named
CustomerNum and CustomerName. Because the tables are union compatible, it is possible to take the union of
these two tables, which is the appropriate operation for this example, as shown in Figure 3-55.
—
103
First query
UNION operation
Second query
FIGURE 3-55
SQL query to perform a union
The query results appear in Figure 3-56.
Customers of rep
35 or who have orders
on ile or both
FIGURE 3-56
Query results
If an SQL implementation truly supports the union operation, it will remove any duplicate rows. For
instance, any customers that are represented by sales rep 35 and that currently have orders on file will not
appear twice in the query results. Some SQL implementations have a union operation but will not remove
duplicate values.
UPDATING TABLES
There are more uses for SQL than simply retrieving data from a database and creating tables. SQL has sev-
eral other capabilities, including the ability to update a database, as demonstrated in the following examples.
NOTE
If you plan to work through the examples in this section using Access, you should use a copy of the original Premiere Products
database because the version of the database used in subsequent chapters does not include these changes. As an alternative,
if you are using a DBMS (such as Oracle or MySQL) that supports the ROLLBACK command, which reverses changes to a
database, you can ensure that your changes are undone by typing the word ROLLBACK before exiting the DBMS. If you have
any questions concerning which of these (or other) approaches is appropriate for you, check with your instructor.
