The Structured Query Language

JDBC lets you communicate with databases using SQL, which is the command language for essentially all modern relational databases. Desktop databases usually have a graphical user interface that lets users manipulate the data directly, but server-based databases are accessed purely through SQL. Most desktop databases have a SQL interface as well, but it often does not support the full SQL standard.

The JDBC package can be thought of as nothing more than an application programming interface (API) for communicating SQL statements to databases. We briefly introduce SQL in this section. If you have never seen SQL before, you may not find this material sufficient. If so, you should turn to one of the many books on the topic. We recommend Client/Server Databases by James Martin and Joe Leben [Prentice-Hall 1998] or the opinionated classic, A Guide to the SQL Standard by C. J. Date and Hugh Darwen [Addison-Wesley 1997].

You can think of a database as a bunch of named tables with rows and columns. Each column has a column name. The rows contain the actual data. These are sometimes called records.

As the example database for this book, we use a set of tables that describe a collection of classic computer science books.

Table 4-1. The Authors Table

Author_ID	Name	Fname
ALEX	Alexander	Christopher
BROO	Brooks	Frederick P.
…	…	…

Table 4-2. The Books Table

Title	ISBN	Publisher_ID	Price
A Guide to the SQL Standard	0-201-96426-0	0201	47.95
A Pattern Language: Towns, Buildings, Construction	0-19-501919-9	019	65.00
…	…	…	…

Table 4-3. The BooksAuthors Table

ISBN	Author_ID	Seq_No
0-201-96426-0	DATE	1
0-201-96426-0	DARW	2
0-19-501919-9	ALEX	1
…	…	…

Table 4-4. The Publishers Table

Publisher_ID	Name	URL
0201	Addison-Wesley	www.aw-bc.com
0407	John Wiley & Sons	www.wiley.com
…	…	…

Figure 4-4 shows a view of the Books table. Figure 4-5 shows the result of joining this table with the Publishers table. Both the Books and the Publishers table contain an identifier for the publisher. When we join both tables on the publisher code, we obtain a query result made up of values from the joined tables. Each row in the result contains the information about a book, together with the publisher name and web page URL. Note that the publisher names and URLs are duplicated across several rows because we have several rows with the same publisher.

Figure 4-4. Sample table containing books

Figure 4-5. Two tables joined together

The benefit of joining tables is to avoid unnecessary duplication of data in the database tables. For example, a naive database design might have had columns for the publisher name and URL right in the Books table. But then the database itself, and not just the query result, would have many duplicates of these entries. If a publisher's web address changed, all entries would need to be updated. Clearly, this is somewhat error prone. In the relational model, we distribute data into multiple tables such that no information is ever unnecessarily duplicated. For example, each publisher URL is contained only once in the publisher table. If the information needs to be combined, then the tables are joined.

In the figures, you can see a graphical tool to inspect and link the tables. Many vendors have tools to express queries in a simple form by connecting column names and filling information into forms. Such tools are often called query by example (QBE) tools. In contrast, a query that uses SQL is written out in text, with SQL syntax. For example,

 SELECT Books.Title, Books.Publisher_Id, Books.Price, Publishers.Name, Publishers.URL FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id 

In the remainder of this section, you will learn how to write such queries. If you are already familiar with SQL, just skip this section.

By convention, SQL keywords are written in capital letters, although this is not necessary.

The SELECT statement is quite flexible. You can simply select all rows in the Books table with the following query:

 SELECT * FROM Books 

The FROM clause is required in every SQL SELECT statement. The FROM clause tells the database which tables to examine to find the data.

You can choose the columns that you want.

 SELECT ISBN, Price, Title FROM Books 

You can restrict the rows in the answer with the WHERE clause.

 SELECT ISBN, Price, Title FROM Books WHERE Price <= 29.95 

Be careful with the "equals" comparison. SQL uses = and <> rather than == or != as in the Java programming language, for equality testing.

NOTE

Some database vendors support the use of != for inequality testing. This is not standard SQL, so we recommend against such use.

The WHERE clause can also use pattern matching by means of the LIKE operator. The wildcard characters are not the usual * and ?, however. Use a % for zero or more characters and an underscore for a single character. For example,

 SELECT ISBN, Price, Title FROM Books WHERE Title NOT LIKE '%n_x%' 

excludes books with titles that contain words such as UNIX or Linux.

Note that strings are enclosed in single quotes, not double quotes. A single quote inside a string is denoted as a pair of single quotes. For example,

 SELECT Title FROM Books WHERE Title LIKE '%''%' 

reports all titles that contain a single quote.

You can select data from multiple tables.

 SELECT * FROM Books, Publishers 

Without a WHERE clause, this query is not very interesting. It lists all combinations of rows from both tables. In our case, where Books has 20 rows and Publishers has 8 rows, the result is a set of rows with 20 x 8 entries and lots of duplications. We really want to constrain the query to say that we are only interested in matching books with their publishers.

 SELECT * FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id 

This query result has 20 rows, one for each book, because each book has one publisher in the Publisher table.

Whenever you have multiple tables in a query, the same column name can occur in two different places. That happened in our example. There is a column called Publisher_Id in both the Books and the Publishers table. When an ambiguity would otherwise result, you must prefix each column name with the name of the table to which it belongs, such as Books.Publisher_Id.

You can use SQL to change the data inside a database as well by using so-called action queries (i.e., queries that move or change data). For example, suppose you want to reduce by $5.00 the current price of all books that have "C++" in their title.

 UPDATE Books SET Price = Price - 5.00 WHERE Title LIKE '%C++%' 

Probably the most important action besides UPDATE is DELETE, which allows the query to delete those records that satisfy the criteria of the WHERE clause.

Moreover, SQL comes with built-in functions for taking averages, finding maximums and minimums in a column, and a lot more. A good source for this information is http://sqlzoo.net. (That site also contains a nifty interactive SQL tutorial.)

Typically, to insert values into a table, you use the INSERT statement:

 INSERT INTO Books VALUES ('A Guide to the SQL Standard', '0-201-96426-0', '0201', 47.95) 

You need a separate INSERT statement for every row being inserted in the table.

Of course, before you can query, modify, and insert data, you must have a place to store data. Use the CREATE TABLE statement to make a new table. You specify the name and data type for each column. For example,

 CREATE TABLE Books (    Title CHAR(60),    ISBN CHAR(13),    Publisher_Id CHAR(6),    Price DECIMAL(10,2) ) 

Table 4-5 shows the most common SQL data types.

Table 4-5. SQL Data Types

Data Types	Description
INTEGER or INT	Typically, a 32-bit integer
SMALLINT	Typically, a 16-bit integer
NUMERIC(m,n), DECIMAL(m,n) or DEC(m,n)	Fixed-point decimal number with m total digits and n digits after the decimal point
FLOAT(n)	A floating-point number with n binary digits of precision
REAL	Typically, a 32-bit floating-point number
DOUBLE	Typically, a 64-bit floating-point number
CHARACTER(n) or CHAR(n)	Fixed-length string of length n
VARCHAR(n)	Variable-length strings of maximum length n
BOOLEAN	A boolean value
DATE	Calendar date, implementation dependent
TIME	Time of day, implementation dependent
TIMESTAMP	Date and time of day, implementation dependent
BLOB	A binary large object
CLOB	A character large object

In this book, we do not discuss the additional clauses, such as keys and constraints, that you can use with the CREATE TABLE command.