Java Reference
In-Depth Information
Selecting Data Subsets
Different users of a database are often interested in different data and different relation-
ships among the data. Most users require only subsets of the rows and columns. Queries
specify which subsets of the data to select from a table. You use SQL to define queries. For
example, you might select data from the
Employee
table to create a result that shows where
each department is located, presenting the data sorted in increasing order by department
number. This result is shown in Fig. 24.2. SQL is discussed in Section 24.4.
Department
Location
413
611
642
New Jersey
Orlando
Los Angeles
Fig. 24.2
|
Distinct
Department
and
Location
data from the
Employees
table.
We introduce relational databases in the context of this chapter's
books
database, which
you'll use in several examples. Before we discuss SQL, we discuss the
tables
of the
books
database. We use this database to introduce various database concepts, including how to
use SQL to obtain information from the database and to manipulate the data. We provide
a script to create the database. You can find the script in the examples directory for this
chapter. Section 24.5 explains how to use this script.
Authors
Table
The database consists of three tables:
Authors
,
AuthorISBN
and
Titles
. The
Authors
ta-
ble (described in Fig. 24.3) consists of three columns that maintain each author's unique
ID number, first name and last name. Figure 24.4 contains sample data from the
Authors
table.
Column
Description
Author's ID number in the database. In the
books
database, this integer col-
umn is defined as
autoincremented
—for each row inserted in this table, the
AuthorID
value is increased by 1 automatically to ensure that each row has a
unique
AuthorID
. This column represents the table's primary key. Autoincre-
mented columns are so-called identity columns. The SQL script we provide
for this database uses the SQL
IDENTITY
keyword to mark the
AuthorID
col-
umn as an identity column. For more information on using the
IDENTITY
keyword and creating databases, see the Java DB Developer's Guide at
AuthorID
Author's first name (a string).
FirstName
Author's last name (a string).
LastName
Fig. 24.3
|
Authors
table from the
books
database.