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.
24.3 A books Database
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
http://docs.oracle.com/javadb/10.10.1.1/devguide/derbydev.pdf .
AuthorID
Author's first name (a string).
FirstName
Author's last name (a string).
LastName
Fig. 24.3 | Authors table from the books database.
 
 
Search WWH ::




Custom Search