JDB SQL Reference

SQL (Structured Query Language) is the database procedure language used by relational database management systems. It was developed by IBM in the early 1970s and then adapted by other software vendors. The American National Standards Institute (ANSI) issued a standard for SQL in 1986 and again in 1992. Although this standard defines a basic set of features that is common to all versions of SQL, each vendor also includes some extensions to SQL in their database products; these extensions are implemented differently.

The scope of SQL gives you complete control over your database operations. There are commands for database definition:

• CREATE DATABASE—Creates a database

• CREATE TABLE—Adds a table to the database

• DROP TABLE—Deletes a table from the database

There are also commands to access and update the data:

• SELECT—Retrieves information from the database

• INSERT—Adds information to the database

• UPDATE—Updates information in the database

• DELETE—Deletes information from the database

This chapter contains instructions for using these SQL commands in order to retrieve information from an existing database and to update the database information.

SQL Statements

In SQL, commands are called statements and consist of one or more keywords followed by various expressions and clauses. The keywords, used at the beginning of the statement, describe the major function of the statement. In addition to the keywords, most statements also reference at least one database table by name. Since the table is the main storage container for information in a relational database, the tables to be accessed are included in some clause of the statement.

SELECT Statement

The SELECT statement retrieves information from database tables and returns it to you in the form of query results.

SELECT * FROM titles;

In this example, the FROM clause lists the database tables from which data will be retrieved. The * tells the database to fetch all of the columns from the tables specified in the FROM clause. Therefore, the statement selects all the columns and all the rows from the database table titles.

The collection of rows retrieved from the database is called a result set.

To fetch data from just some of the columns as opposed to all columns, replace the asterisk (*) with a list of column names, each name separated from the next by a comma. This is called the select list.

SELECT title_id, name FROM titles;

Generally, the select list consists of a list of items separated by commas. The select list can include:

• Column names associated with the table or tables in the FROM clause.

• An expression which can be a constant, column name, function, subquery, or any combination of these connected by arithmetic and bitwise operators.

The following statement uses the arithmetic operator / to calculate the running time of the video in hours instead of minutes:

SELECT title_id, name, film_minutes / 60 FROM titles;

SQL also provides aggregate functions that compute sums, minimum values, and other such operations over all selected rows. The following statement uses the aggregate function COUNT to determine the number of rows in the titles table.

SELECT COUNT(title_id) FROM titles;

WHERE Clause

To select specific rows in a database table, add a WHERE clause to the SELECT statement:

SELECT title_id, name, film_minutes FROM titles 
	WHERE name = 'Henry V';

The WHERE clause ensures that the result set includes only certain rows of data. In this example, the results include information for all the videos having the name Henry V.

The WHERE clause can also specify a search condition to further refine the query. For example, the following SELECT statement uses the logical operator AND.

SELECT title_id, name, film_minutes FROM titles
	WHERE name = 'Henry V' 	AND dir_last_name = 'Olivier';

The result set contains the information for the version of Henry V directed by Sir Laurence Olivier.

There are a variety of search conditions and qualifying clauses which let you refine your database queries. For example, they retrieve:

• Rows with column values in a certain range (BETWEEN)

• Rows containing a certain pattern (LIKE)

• Rows in ascending or descending order (ASC, DESC)

• Rows meeting search conditions listed in a subquery (IN, EXISTS)

• Summary values on specific columns (Aggregate Functions)

Refer to Chapter 6, "SQL Reference," for detailed information on each of these SQL elements.

UPDATE Statement

The UPDATE statement lets you to modify values in columns belonging to a specified database table.

UPDATE titles SET pricecat = 'G' WHERE title_id = 62;

The first keyword, UPDATE, defines the purpose of the statement. It is followed by the table name. The SET clause specifies which columns are to be updated and defines the new values for those columns. The WHERE clause specifies which rows are to be updated.

WHERE Clause

If a WHERE clause is not included in the UPDATE statement, every row in the table gets updated. The WHERE clause must include all the column specifications you need in order to uniquely identify the rows to be updated. The following statement, without the copy number, updates all copies of this video title in the tapes table:

UPDATE tapes SET status = 'A' WHERE title_id = 62;

To update a single row in the tapes table, you need to provide the primary key, which in tapes table is a combination of the title_id and copy_num columns.

UPDATE tapes SET status = 'A' WHERE title_id = 62 AND copy_num = 2;

The INSERT statement adds new rows of data to a database table. The syntax can vary depending on whether you insert a value into every column or only into selected columns in the table.

INSERT INTO tapes VALUES (62, 2, 'A', 0);

This statement inserts a new row to the tapes table; the VALUES clause specifies the data values for every column in the table. An INSERT statement of this type, must provide values for every column in the order in used by the database table. Or, you can include column list in the statement which specifies which columns should receive values and in which order. Therefore, you are not dependent on knowing how the columns are listed in a particular database table. The following statement includes a column list and the results are identical to the previous INSERT statement:

INSERT INTO tapes 
	(title_id, copy_num, status, times_rented)
	VALUES (62, 2, 'A', 0);

With a column list, you do not have to enter a column value for each column. You can list only the columns where you plan to make an entry.

INSERT INTO tapes 	(title_id, copy_num, status) 	VALUES (62, 2, 'A');

You can also update the columns in any order; however, the order of the column list and the order of the value list must match.

INSERT INTO tapes 	(status, title_id, copy_num) 	VALUES ('A', 62, 2);

The DELETE statement removes selected rows of data from a database table. The FROM clause names the table to be modified. The WHERE clause specifies exactly which rows of the table are to be deleted. If you do not include a WHERE clause, the DELETE statement deletes every row in the table.

DELETE FROM titles WHERE title_id = 62;

The WHERE clause needs to include all the necessary column specifications to ensure that only certain rows are deleted. To delete a single row, use the row's primary key to identify the row; remember that a primary key can be a combination of more than one column.

The following statement deletes a single row from the tapes table by specifying values for both the title_id and copy_num columns.

DELETE FROM tapes WHERE title_id = 62	AND copy_num = 2;

---

SQL Concepts

The ability to select data from two or more database tables is one of the great advantages of a relational database. SQL lets you compare any pair or pairs of columns from two or more tables by matching the contents of the related columns.

You can join the two tables, tapes and titles, by equating the title_id column in the titles table with the title_id column in the tapes table. The WHERE clause specifies the relationship.

SELECT * FROM titles, tapes
	WHERE titles.title_id = tapes.title_id;

The following SELECT statement joins two tables and the result set provides you with a list of videos that are available (have status = A) for rental.

SELECT tapes.title_id, tapes.copy_num, titles.name 

For more information on different types of joins and on joining multiple tables, refer to page 6-27.

Correlation Names

When using joins, instead of using the entire table name throughout the statement, you can give the table a correlation name. The following example uses a as the correlation name for the titles table and b as the correlation name for the tapes table.

SELECT b.title_id, b.copy_num, a.name
	FROM titles a, tapes b
	WHERE a.title_id = b.title_id
	AND b.status = 'A';

You can also use correlation names to perform a self-join, which joins a table to itself so that you can compare values in the same column.

Correlation names must follow the naming conventions for identifiers. They can be 31 characters in length and include letters, numbers, and underscores.

Aggregate Functions

Aggregate functions calculate different types of summary information on rows in a database table including:

• Sums of numeric columns.

• Average, maximum and minimum values in columns.

• Number of rows containing a specific column value.

For more information on aggregate functions, refer to page 6-4.

Transactions

Transactions are units of work on a database. A transaction consists of a series of database statements to be completed as a unit. If the unit is unable to be completed, the statements can be rolled back, restoring the database to its prior state before the transaction started. This ensures the integrity of the database. For example, in the videobiz database, each new entry in the titles table also needs entries in the tapes table, and possibly in the actors and roles tables. All these entries could be grouped into one transaction so that you know the entry is complete.

Implementation in a JDB Database

Database engines implement transactions differently. In JDB, after you declare a connection, a transaction automatically starts on that connection. Additional transactions can then be defined using commit and rollback commands.

The commit command saves the changes to the database. The rollback command undoes any changes made to the database since the start of the transaction. The execution of either commit or rollback starts a new transaction.

Using the videobiz database example, a customer comes to the front desk to rent a video. When the clerk checks out the video, a transaction is started to perform the following actions:

• Insert the rental information into the rentals table.

• Update information about the customer's rentals in the customers table.

• Update information about the video tape status in the tapes table.

If any of the statements fail, the transaction must be rolled back. If all of the statements execute without any errors, the transaction can be committed.

JDB performs an automatic commit when you leave an isql or JISQL session. You must issue a rollback command if you do not want to save your database changes.

The following statements from a JISQL script illustrate the sample rental transaction which rents a video to a customer:

insert into rentals 
	(cust_id, title_id, copy_num, rental_date, due_back,
	return_date, price, late_fee, amount_paid, rental_status,
	rental_comment, modified_date, modified_by)
	values
	(3, 69, 2, '1993/10/29 19:56:00', '1993/11/01 00:00:00',
	NULL, 3.50, 1.00, 3.50, 'C', NULL, '1993/10/29 19:56:00',
	'jenny');

---

Executing SQL

In Panther, you can execute SQL commands:

• By using JISQL

• Via JPL procedures

• Through the transaction manager

The examples in this book use the JISQL syntax unless otherwise indicated.

Using JISQL

To use JISQL, start the JISQL utility and connect to a database. In the SQL scripting area, enter the text of the SQL statement followed by the termination character, a semicolon (;), to end the statement. Any date values or character strings must be enclosed in single quotation marks. For example:

SELECT title_id, name, dir_first_name, dir_last_name

retrieves the following rows:

Refer to Chapter 5, "Using JISQL," for more information on using the JISQL utility.

Using JPL

The same SQL command in a JPL procedure named query1 would look like this:

proc query1
dbms query SELECT title_id, name, dir_first_name, \
	dir_last_name FROM titles \
	WHERE name = 'Henry V'
return 0

The JPL continuation character (\) is needed whenever a command is not completed on one line. A termination character is not required at the end of the statement since it is added automatically by Panther's database interface to JDB.

See Also

For more information on writing JPL, refer to page 19-1 in the Developer's Guide.

For information on mapping data to Panther variables, refer to page 29-3 in the Developer's Guide.