JDB SQL Reference

Chapter 6. SQL Reference

This chapter includes an explanation of the SQL commands and concepts in alphabetical order. You can execute the SQL commands described in this chapter using JPL procedures or using JISQL. For an example, refer to page 3-10.

Reference Organization

The reference material is listed alphabetically for the following topics:

SQL Statements

CREATE DATABASE

CREATE TABLE

DELETE

DROP DATABASE

DROP TABLE

INSERT

SELECT

UPDATE

SQL Clauses and Keywords

BETWEEN

GROUP BY

HAVING

LIKE

ORDER BY

WHERE

SQL Concepts

Aggregate Functions

Data Types

Joins

Null Values

Operators

Subqueries

Notation Conventions

This chapter includes a section for each command or topic. Each section can include the following subsections:

Syntax
Arguments
Description
Examples
Variants
See Also

The examples included in this section use the JISQL syntax and are based on the videobiz database. For a complete description of this database, refer to Figure 2-2 or Appendix D, "Videobiz Database."

Aggregate Functions

Obtain information about rows or groups of rows
functionName ([DISTINCT] expression)

functionName
One of the following aggregate functions: AVG, COUNT, MAX, MIN or SUM.
DISTINCT
Eliminates duplicate values before the function is applied. This keyword can be used with AVG, COUNT or SUM. It is not allowed with COUNT(*), MAX or MIN.
expression
A constant, column name, subquery, or any combination of these connected by arithmetic or bitwise operators (AND and OR).

Description

Aggregate functions calculate different types of summary information on rows in a database table. All of the aggregate functions ignore null values, with the exception of COUNT(*). Table 6-1 lists the aggregate functions supported in JDB.

Table 6-1 Aggregate functions supported in JDB

Aggregate function Description

COUNT

Counts the total number of rows retrieved with the SELECT statement. COUNT(*) calculates the number of rows retrieved. COUNT(columnName) calculates the number of rows containing a value in the specified column; therefore, it ignores null values.

AVG

Calculates and returns the average value of the specified numeric column or expression.

MAX

Returns the largest value of the specified column or expression.

MIN

Returns the lowest value of the specified column or expression.

SUM

Returns the sum of the values entered in the specified numeric column or expression.

Aggregate functions generally appear in a select list, in a HAVING clause, or in conjunction with a GROUP BY clause. When used in the same statement as a GROUP BY clause, aggregate functions return summary information on each group of data. Aggregate functions are not valid in the WHERE clause of SELECT statements.

**Table 6-1 Aggregate functions supported in JDB**
Aggregate function	Description
`COUNT`	Counts the total number of rows retrieved with the `SELECT` statement. `COUNT(*)` calculates the number of rows retrieved. `COUNT(columnName)` calculates the number of rows containing a value in the specified column; therefore, it ignores null values.
`AVG`	Calculates and returns the average value of the specified numeric column or expression.
`MAX`	Returns the largest value of the specified column or expression.
`MIN`	Returns the lowest value of the specified column or expression.
`SUM`	Returns the sum of the values entered in the specified numeric column or expression.

Example

The following statement finds the number of video titles entered in the database by querying for a count of the rows in the titles table:
SELECT COUNT(*) FROM titles;
The following statement uses the DISTINCT keyword to calculate the number of video titles that have a copy of the tape available for rental.
SELECT COUNT(distinct title_id) FROM tapes WHERE status = 'A';
The following statement calculates the average number of rentals per customer and the average rental amount:
SELECT AVG(num_rentals), AVG(rent_amount) FROM customers;
The following statement queries for the least number of times a copy of a video has been rented:
SELECT MIN(times_rented) FROM tapes;
The following statement calculates the money collected from video rentals for a particular day:
SELECT SUM(amount_paid) FROM rentals
	WHERE rental_date LIKE '1993/10/22%';
The following statement calculates the number of times a particular title has been rented:
SELECT SUM(times_rented) FROM tapes
	WHERE title_id = 12;

BETWEEN Predicate

Specify a range of data values
[NOT] BETWEEN x AND y

Description

The BETWEEN predicate, located in the WHERE Clause, specifies a range of database values to be used in determining a result set. The range specified is inclusive of x and y.
If the NOT keyword is specified, only rows outside the specified range are included in the result set.

Example

The following statement lists videos whose length is between an hour and two hours:
SELECT title_id, name, film_minutes FROM titles
	WHERE film_minutes BETWEEN 60 AND 120;
The following statement deletes all the film rentals that occurred in 1989:
DELETE FROM rentals WHERE rental_date
	BETWEEN '1989/01/01 00:00:00' AND '1989/12/31 23:59:59';
The following statement finds which current customers live in a series of postal codes:
SELECT cust_id, first_name, last_name FROM customers
	WHERE postal_code BETWEEN 10200 AND 10299
	AND member_status <> 'I';

Variants

The following statement performs the same query, finding the current customers in the designated series of postal codes, without the BETWEEN predicate:
SELECT cust_id, first_name, last_name FROM customers
	WHERE postal_code >= 10000 AND postal_code <= 10199
	AND member_status <> 'I';

CREATE DATABASE Statement

Create a new database
CREATE DATABASE database-name

database-name
A unique identifier for the database. Since the database appears as a file on the operating system, its identifier must follow the naming conventions for the operating system. If the database name contains characters that are not alphanumeric or if you are including a path name, the name must be enclosed in single quotation marks.

Description

The CREATE DATABASE statement creates a new database. A database must be created before you can declare a connection to it. You can create a database when you are connected to JDB using the identifier @system, when you are connected to another JDB database, or when you are using JISQL.
Creating the First Database

You can create your first database in JDB either by using JISQL or by writing a JPL procedure.
JISQL
To create the database in JISQL, first you need to start the program. For UNIX systems, it is usually located in $SMBASE/util. To start it, type:
jisql

Or, click on the JISQL icon.
The JDB ISQL window opens.
To create the database, choose DatabaseCreate Database. The Create Database window opens.
Enter the name of the database you want to create, select the Connect after creation check box, and choose OK. This creates the database and automatically connects to it so that you can then create database tables.
JPL
The equivalent JPL procedure is as follows:
dbms declare syscon connection for database @system
dbms run create database database-namedbms close connection syscon
dbms declare c1 connection for database database-namedbms run create table table-name ...

Example

CREATE DATABASE videobiz;

If the database name contains non-alphanumeric characters or if you are including a path name, enclose the name in single quotation marks:
CREATE DATABASE 'video.db';
CREATE DATABASE '/usr/home/videobiz';

CREATE TABLE Statement

Creates a new database table

CREATE TABLE table-name (
column-name  data-type [(length)] [NOT NULL] [, column-name ...]
	[PRIMARY KEY (column-name [, column-name ... ]  ), ]
	[UNIQUE (column-name [, column-name ... ] ), ]
	[FOREIGN KEY (column-name [, column-name ... ] )
	REFERENCES table-name (column-name [, column-name ...]) [ ,] ]
	)

table-name
Identifier for the table to be created. This identifier must be unique to the database. Identifiers in JDB must start with a letter but may contain letters, numbers, and underscores.
column-name
Identifier for the column. Each column identifier must be unique within the table.
data-type
Data type for the column. For char data types, a length must also be specified. For more information on data types, refer to page 6-15.
NOT NULL
Specifies that a value must be entered for the column. The value for the column cannot be null.
PRIMARY KEY
Specifies the primary key column(s) for this table. Any column specified as a primary key must be specified as NOT NULL.
UNIQUE
Specifies that a column or group of columns must contain a unique entry. Any column specified as unique must be specified as NOT NULL. Column(s) specified in a PRIMARY KEY clause do not need to be declared as UNIQUE.
FOREIGN KEY
Specifies the foreign key columns for this table. Any such column must refer to a primary or unique key in the referenced table. Matching between the foreign and primary keys is performed in the order the columns are listed, not by their names.
REFERENCES
Specifies the database table and the column name in that table for the foreign key column. If more than one column is listed, the order of the columns listed in the FOREIGN KEY clause must match the order of the columns in the REFERENCES clause.

Description

The CREATE TABLE statement creates a new table in the current database with the specified columns. For each column, you must specify the following:

Column name

Data type

Length, if the data type is char

JDB is a case-insensitive database system. No matter which case you use to enter your table and column names, JDB stores the names in lower case.
Specify Primary and Foreign Keys

You need to specify the primary and foreign keys when you create the table. The primary key is the column containing a different value in every row, which ensures that all rows are unique. In cases where one column does not perform this function, you must specify two or more columns whose values together form a unique entry. This is called a composite key. Null values are not allowed in the primary key columns; therefore, the column definitions for those columns should contain the keyword NOT NULL.
Foreign keys are columns in the database table that are primary or unique keys in another database table. Data entered into a foreign key column should exist as a value in the other database table. The data type for the foreign key column and its corresponding primary or unique key must be the same.
Although JDB does not enforce referential integrity based on your primary and foreign keys, it is recommended that you enter primary and foreign key information for your database tables.
Maximum Row Length

In JDB, there is a maximum row length of 1K. In other words, the sum of the table's column sizes cannot exceed 1K. The base length of the various columns is:

Data type Base length

CHAR

Specified length

INT

4 bytes (stored as LONG in the current release)

LONG

4 bytes

FLOAT

12 bytes

DOUBLE

12 bytes

DATETIME

9 bytes

The length of a column is defined as its base length plus an additional 2 bytes for flags.
The following statement creates a table whose size equals 1028 ((255+2) * 4). Since that total is greater than 1024, JDB reports the error "Maximum record length exceeded."
CREATE TABLE toobig (
	a	CHAR		(255),
	b	CHAR		(255),
	c	CHAR		(255),
	d	CHAR		(255));

Data type	Base length
`CHAR`	Specified length
`INT`	4 bytes (stored as `LONG` in the current release)
`LONG`	4 bytes
`FLOAT`	12 bytes
`DOUBLE`	12 bytes
`DATETIME`	9 bytes

Example

The following statement creates the actors table with actor_id as the primary key:

CREATE TABLE actors (
    actor_id            INT              NOT NULL,
    last_name           CHAR    ( 25)    NOT NULL,
    first_name          CHAR    ( 20)    ,
    PRIMARY KEY (actor_id));

The following statement creates the rentals table:

CREATE TABLE rentals (
    cust_id            INT             NOT NULL,
    title_id           INT             NOT NULL,
    copy_num           INT             NOT NULL,
    rental_date        DATETIME        NOT NULL,
    due_back           DATETIME        NOT NULL,
    return_date        DATETIME        ,
    price              FLOAT           NOT NULL,
    late_fee           FLOAT           NOT NULL,
    amount_paid        FLOAT           NOT NULL,
    rental_status      CHAR    (  1)   NOT NULL,
    rental_comment     CHAR    ( 76)   ,
    modified_date      DATETIME        NOT NULL,
    modified_by        CHAR    (  8)   NOT NULL,
    PRIMARY KEY (cust_id, title_id, copy_num, rental_date),
    FOREIGN KEY (cust_id) REFERENCES customers (cust_id),
    FOREIGN KEY (title_id, copy_num)
        REFERENCES tapes (title_id, copy_num),
    FOREIGN KEY (modified_by) REFERENCES users (user_id));

Data Types

List the data types available in JDB

Description

The JDB data types are described in this section.
CHAR (n)
Character column containing ASCII characters (letters, numbers and symbols). Specify the maximum size of the column with n. n can range in value from 1 to 255. The size of a CHAR column is n no matter how many characters are entered into the column. If the character string is longer than n, the string is truncated to the specified length. If the character string is shorter than n, the string is blank-padded to the specified length. For example, an entry of
'A12'
in a CHAR(4) column would be stored as
'A12 '
The storage size of a CHAR column is n plus 2 bytes for flags.
To enter values into CHAR columns using JISQL, enclose the character string in single quotation marks. To include a single quotation mark as part of the entry, enter two consecutive single quotation marks.
If you use colon plus processing or binding in a JPL procedure, Panther automatically formats the character string by enclosing the character string in single quotation marks and converting each single quotation mark to two single quotation marks.
INT
Numeric column containing whole numbers. In the current version of JDB, all INT values are stored as LONG values.
LONG
Numeric column containing whole numbers ranging from -2,147,483,647 to +2,147,483,647. The storage size for an LONG column is 4 bytes plus 2 bytes for flags.
FLOAT
Numeric column containing positive or negative floating point numbers. The hardware platform determines the precision and range of FLOAT columns. The storage size is 12 bytes plus 2 bytes for flags.
DOUBLE
Numeric column containing double precision numbers. The hardware platform determines the precision and range of DOUBLE columns. The storage size is 12 bytes plus 2 bytes for flags.
DATETIME
Date column containing both a date and time of day. The storage size is 9 bytes, plus 2 bytes for flags. The default format for a DATETIME column is:
yyyy/mm/dd hh:mm:ss
For example, January 28, 1993 at 2:40 p.m. is entered as follows:
1993/01/28 14:40:00
Alternate formats for DATETIME values include using periods instead of colons to separate time entries and using spaces instead of slashes to separate date entries.
To enter DATETIME values in JISQL, enclose the date entry in single quotes as in:
'1993/01/28 14:40:00'
To enter DATETIME values in JPL, the date should both be enclosed in single quotes and contain double colons:
DBMS RUN UPDATE titles \
SET release_date = '1994/01/28 00::00::00' \
WHERE title_id = :+title_id
If DT_DATETIME is the Panther type of the widget containing the entry, Panther automatically formats the date according to the specified Date/Time format.
The data type of each column is stored in the system table, syscols. You can query this table to find the data type for any column. Refer to page 4-3 for more information on the syscols table.
Numeric Columns

In JDB, you cannot enter numbers with leading zeros in numeric columns.

Example

The following statement creates the titles table:

CREATE TABLE titles (
    title_id          INT             NOT NULL,
    name              CHAR    ( 60)   NOT NULL,
    genre_code        CHAR    (  4)   ,
    dir_last_name     CHAR    ( 25)   ,
    dir_first_name    CHAR    ( 20)   ,
    film_minutes      INT             ,
    rating_code       CHAR    (  4)   ,
    release_date      DATETIME        ,
    pricecat          CHAR    (  1)   NOT NULL
    PRIMARY KEY (title_id),
    FOREIGN KEY (pricecat) REFERENCES pricecats (pricecat));

The titles table contains columns of various data types. The following statement inserts a row into this table:

INSERT INTO titles (title_id, name, genre_code,
	dir_last_name, dir_first_name, film_minutes, rating_code,
	release_date, pricecat)
	VALUES (72, 'Howards End', 'DRAM', 'Ivory', 'James', 140,
	'PG', '1992/01/01 00:00:00', 'G');

DELETE Statement

Remove information from a database table
DELETE FROM table-name [WHERE search-conditions]

table-name
Identifier for the database table.
WHERE
The WHERE clause specifies which rows will be deleted. Refer to page 6-54 for more information on the WHERE clause.

Description

The DELETE statement removes a row or rows from the specified table. To keep data consistent across a database, you may need to delete or update rows in other database tables whose values depend on the deleted row.
Warning: If no WHERE clause is specified, all the information in the table is deleted.

Example

If a customer drops his membership, you can delete that customer from the database:
DELETE FROM customers WHERE cust_id = 123;
To delete a video title from the database, you would need to delete rows from titles, title_dscr, tapes and roles:
DELETE FROM title_dscr WHERE title_id = 134;
DELETE FROM roles WHERE title_id = 134;
DELETE FROM tapes WHERE title_id = 134;
DELETE FROM titles WHERE title_id = 134;
You can delete rows using a subquery in the WHERE clause:
DELETE FROM actors WHERE actor_id IN
	(SELECT actor_id FROM roles WHERE title_id = 134);

DROP DATABASE Statement

Remove a database
DROP DATABASE database-name

database-name
Name of the database to be removed.

Description

The DROP DATABASE statement deletes the specified database. The file containing the database is removed from the operating system. If the database name contains characters that are not alphanumeric, enclose the name in single quotation marks.
You cannot drop the current database. First, you must close the connection with the current database and connect either to another database or to the system catalog.
When you drop a database, the journal files are not deleted.

Example

DROP DATABASE videobiz;

Enclose the name in single quotation marks if it contains non-alphanumeric characters.
DROP DATABASE 'video.db';

DROP TABLE Statement

Remove a table from the database
DROP TABLE table-name

table-name
Name of the table to be deleted.

Description

The DROP TABLE statement deletes the specified table from the database, including the data stored in the table.

Example

DROP TABLE rentals;

GROUP BY Clause

Divide the returned data into groups according to the specified column(s)
GROUP BY [correlation-name.column-name[ , ... ]

correlation-name
Identifier which substitutes for the table name.
column-name
Column used to group the data.

Description

A GROUP BY clause included in a SELECT statement lets you specify the column or columns to be used to divide the table into groups. Rows having an identical value in the specified columns are grouped together.
A GROUP BY clause is most often combined with an aggregate function in order to obtain summary information on each group. A GROUP BY clause can also be followed by a HAVING clause in order to define which groups appear in the result set.
In a SELECT statement containing a GROUP BY clause, the columns specified in the select list or in the HAVING clause must either be listed in the GROUP BY clause or be parameters of aggregate functions.

Example

This statement finds the number of videos in each rating category:
SELECT rating_code, COUNT(*) FROM titles
	GROUP BY rating_code;
A GROUP BY clause can be used to find unique entries in a SELECT statement; however, the DISTINCT generally used for this purpose. The following statement lists the types of videos found in the titles table:
SELECT genre_code FROM titles GROUP BY genre_code;
This statement using both a GROUP BY clause and a HAVING clause determines the people who directed more than three videos:
SELECT dir_last_name FROM titles GROUP BY dir_last_name
	HAVING COUNT(*) > 3;
If your SELECT statement also includes a WHERE clause, place the GROUP BY clause after WHERE clause.
SELECT title_id, COUNT (*) FROM tapes WHERE status = 'A'
	GROUP BY title_id;

HAVING Clause

Set search conditions in order to obtain a subset of data
HAVING search-conditions

search-conditions
Specifies the conditions for the selection of data. For a complete listing of available conditions, refer to page 6-54.

Description

A HAVING clause included in a SELECT statement allows you to select a subset of data which has a certain value.
Generally, a HAVING clause appears in conjunction with a GROUP BY clause. When this occurs, the HAVING clause selects its subsets after the GROUP BY clause has been applied.
Unlike the WHERE clause, a HAVING clause can include aggregate functions.
In statements using both a WHERE clause and a HAVING clause, the following steps occur:

The WHERE clause selects the rows meeting its search conditions.
The GROUP BY clause divides these rows into groups according to the specified column(s).
The HAVING clause excludes groups not meeting its search conditions.
Any aggregate function specified in the select list performs its calculations for each group.

Example

The following statement finds the customers that are frequent renters for the month:
SELECT cust_id FROM rentals
	WHERE rental_date
	BETWEEN '1993/10/01 00:00:00' AND '1993/10/31 23:59:59'
	GROUP BY cust_id
	HAVING COUNT (*) > 4;

INSERT Statement

Add information to a database table

INSERT INTO table-name [(column-list)] 	VALUES (literal | NULL [ , ... ])
INSERT INTO table-name [(column-list)] query-expression

table-name
Unique identifier for the database table.
column-list
Columns which will have values inserted. See the description below.
VALUES
Columns which will have values inserted. See the description below.
query-expression
Subquery used to specify data to be inserted.

Description

The INSERT statement enters information into the specified table. There are two forms of the INSERT statement. In the first form, you insert a single row by specifying values for the specified columns. In the second form, you use a query to select rows from other tables to be inserted into the specified table.
Within the first form of the INSERT statement, several format variations exist. The simplest format includes a VALUES clause without a column list. In this format, you must provide a value for each column in the table. The values are listed in the same order that was used to create the columns in the database table.
INSERT INTO roles
	VALUES (72, 144, 'Margaret Schlegel');
In a VALUES clause, the column values are separated by commas. You can enter character strings, date strings, and numeric constants as column values. If you are entering the data using JISQL, character strings and date values must be enclosed with single quotation marks.
Inserting Rows Using a Column List

If you do not know the column order or if you do not want to enter a value for each column, you can add a column list to the statement:
INSERT INTO roles (title_id, actor_id, role)
	VALUES (72, 144, 'Margaret Schlegel');
With this format, the first column value, 72, is entered into the first column found in the column list, title_id. The second value goes into the second column listed, etc.
If you do not specify a value for a column, its value will be set to NULL.
Inserting Rows Containing a Null Value

You can also enter an unknown value for any column using NULL as the column value:
INSERT INTO roles (title_id, actor_id, role)
	VALUES (72, 144, NULL);
However, this syntax is not available if the column was specified as NOT NULL in the CREATE TABLE Statement.
Inserting Rows Using a Subquery

The second syntax statement illustrates the insertion of rows using a subquery. Multiple rows can be inserted with this format; however, you cannot have the same table named in the INTO clause and the SELECT statement of the query.
INSERT INTO roles
	(title_id)
	SELECT title_id FROM titles WHERE title_id > 75;

Joins

Specify the interconnection between two tables

... FROM table-name, table-name
	WHERE table-name.column-name join-operator
		table-name.column-name
	[ {AND|OR|NOT} table-name. column-name join-operator
		table-name.column-name ... ]

FROM
The FROM clause lists the tables included in the join.
table-name
Identifier for the database table.
WHERE
The WHERE clause specifies the relationship between each set of tables in addition to the search conditions to be used for the statement.
column-name
Column from one of the specified database tables.
join-operator
One of the following operators: =, >, <, >=, <=, or <>.

Description

A join connects two or more database tables by specifying the relationship between each set of tables. To specify the relationship, you connect one column from one table to a column in another table. The column names must be qualified with the table name if the table location is ambiguous. A join can be part of a SELECT, UPDATE, INSERT, or DELETE statement. A join can also be included in a subquery. There are several types of joins which will be discussed in the following paragraphs.
Equi-joins

An equi-join is based on equality as indicated by the equal sign (=). In an equi-join, all the columns in the tables being joined are included in the result set. For example,
SELECT * FROM roles, actors
	WHERE roles.actor_id = actors.actor_id;
This statement joins the actors and roles tables using the actor_id column in each table. The result set lists the actor for each role included in the roles table.

Natural Joins

A natural join is structured so that there is no duplication of data. The same query as a natural join would appear as follows:
SELECT title_id, roles.actor_id, first_name, last_name, role
	FROM roles, actors
	WHERE roles.actor_id = actors.actor_id;
The select list names the columns to be included so that the actor_id is displayed only once.

Multiple Table Joins

A multiple table join involves more than two tables using one or more columns to make the connection. The following statement adds the name of the video to the result set.
SELECT roles.title_id, titles.name, actors.actor_id,
	actors.first_name, actors.last_name, roles.role
	FROM roles, titles, actors
	WHERE roles.title_id = titles.title_id
	AND roles.actor_id = actors.actor_id;
You could also use correlation names to formulate the query:
SELECT r.title_id, t.name, a.actor_id,
	a.first_name, a.last_name, r.role 
	FROM roles r, titles t, actors a
	WHERE r.title_id = t.title_id
	AND r.actor_id = a.actor_id;
Additional search conditions can be added to the WHERE clause to further restrict the result set:
SELECT roles.title_id, titles.name, actors.actor_id,
	actors.first_name, actors.last_name, roles.role
	FROM roles, titles, actors
	WHERE roles.title_id = titles.title_id
	AND roles.actor_id = actors.actor_id
	AND titles.title_id = 19;
Non equi-joins

In addition to the equal sign, there are additional operators that can be specified. Table 6-2 lists all the relational operators that can be used in joins.

Table 6-2 Join operators

Operator Description

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

You can also use a BETWEEN predicate to specify a range of values.
The following query lists the videos that have the same name but have been directed by different people:
SELECT t.title_id, t.name, t.dir_last_name
	FROM titles t, titles d
	WHERE t.name = d.name
	AND t.dir_last_name <> d.dir_last_name;
Self-joins

The previous query is called a self-join which joins a table to itself so that you can compare values in the same column. To make a self-join, use correlation names for the database tables in the FROM clause and in the column names.
The following self-join finds the directors who have made two different types of films–for example, directors who have made both comedy and adventure films. All of this information is in the titles table. For this query, the join condition is made on the director's last name. Then, the two genre_code entries in each join are compared, and if they differ, the director's last name, the genre code and the name of each corresponding video are written to the result set.
SELECT dir.dir_last_name, dir.genre_code, dir.name
	FROM titles gen, titles dir
	WHERE gen.dir_last_name = dir.dir_last_name
	AND gen.genre_code <> dir.genre_code
The following self-join finds the actors in video #50 who are entered in the roles table only for that video. It uses one version of the roles table to find all the actor_id codes in video #50. It uses the other version of the table to find the actor_id codes that are entered in the roles table only once.
SELECT r.actor_id FROM roles r, roles j 
	WHERE r.title_id = 50 
	AND r.actor_id = j.actor_id  
	GROUP BY r.actor_id HAVING COUNT(j.actor_id) = 1;

**Table 6-2 Join operators**
Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

LIKE Predicate

Obtain data matching a specified pattern
column-name [NOT] LIKE literal [ESCAPE literal]

column-name
Column whose value you want to specify.
literal
Wildcard characters intermixed with portions of column values.

Description

A LIKE predicate selects rows in which a column value matches a specified pat tern. You can enter values for character strings or date strings. You can also enter a wildcard character to substitute for a portion of the string. Table 6-3 lists the wild card characters that can be used in JDB.

Table 6-3 Wildcard characters

Wildcard Description

% (percent sign)

Substitutes for any string of zero or more characters

_ (underline)

Substitutes for any single character

With the specification of an ESCAPE clause, the special meaning given to "_" and "%" can be disabled. NOT LIKE selects rows that do not match the specified pattern.

**Table 6-3 Wildcard characters**
Wildcard	Description
% (percent sign)	Substitutes for any string of zero or more characters
_ (underline)	Substitutes for any single character

Example

This query finds all the videos released in 1989:
SELECT title_id, name FROM titles
	WHERE release_date LIKE '1989%';
The following example returns rows where the dscr_text begins with an underscore. The backslash removes the special meaning for the underscore, but not for the percent sign:
SELECT * FROM title_dscr
	WHERE dscr_text LIKE '\_%' ESCAPE `\';

Null Values

Specify an unknown value

In INSERT statements
... VALUES {literal | NULL} [ , {literal | NULL} ]

In SELECT statements,
... WHERE column-name IS [NOT] NULL

In UPDATE statements
... SET column-name  = {literal | NULL}
... [ , column-name = {literal | NULL} ]

Description

When a column is set to NULL, it specifies an unknown or an unspecified value. A NULL value is not the same as a blank or a zero entered into a column.
If you are using a comparison operator, be aware that NULL is not a value and therefore cannot be compared to any other value. As an example, the following WHERE clause would evaluate to true for all values of the times_rented column that are greater than 75, but would evaluate to false if the column is set to NULL.
WHERE times_rented > 75

Examples

The examples illustrate the uses of NULL values in different types of statements.
The following statement inserts a null value into the role column:
INSERT INTO roles (title_id, actor_id, role)
	VALUES (16, 276, NULL);
An error occurs if you attempt to insert a null value into a column which was created as NOT NULL. The following statement returns the error NULL not allowed since the column actor_id was specified as NOT NULL in the CREATE TABLE statement for the roles table.
INSERT INTO roles (title_id, actor_id, role)
	VALUES (27, NULL, 'Aunt Gussie');
The following statement selects rows where the rating_code column contains a null value:
SELECT name FROM titles WHERE rating_code IS NULL;
The following statement updates the rental_comment column to a null value for every row in the rentals table:
UPDATE rentals SET rental_comment = NULL;
The following statement updates the rental_comment to a null value for a specific rental:
UPDATE rentals SET rental_comment = NULL
	WHERE cust_id = 6
	AND title_id = 69
	AND copy_num = 2
	AND rental_date = '1993/10/29 18:00:00';
In order to obtain a unique entry for the rentals table, you must include an entry for the cust_id, title_id, copy_num and rental_date columns.

Operators

Description

This section describes the various operators available in JDB.
Arithmetic Operators

Arithmetic operators allow you to perform calculations on data in the database without altering the data. They are available to use with any numeric column. If the value in the column is NULL, the result will also be NULL.
Table 6-4 lists the arithmetic operators that are available in JDB.

Table 6-4 Arithmetic operators

Operator Definition

+

Addition

-

Subtraction

*

Multiplication

/

Division

The arithmetic operators adhere to the following order of precedence:

multiplication, division
subtraction, addition

Among operators that have the same level of precedence, the order of execution is from left to right. The order of precedence can also be explicitly specified using parentheses. For more information, refer to the section on logical operators.
The following statement uses an arithmetic operator to calculate the price with sales tax on an item:
SELECT pricecat, price * 1.0825 FROM pricecats
	WHERE pricecat = 'N';
Arithmetic operators can also be used in calculations that perform comparisons. The following statement finds rentals where the amount paid was greater than double the rental fee:
SELECT cust_id, title_id, rental_date FROM rentals
	WHERE amount_paid > price * 2;
Comparison Operators

Comparison operators allow you to compare one expression with another expression, where an expression is defined as a column name, a constant, a function, or any combination of column names, constants and functions.
Table 6-5 lists the comparison operators that are available in JDB.

Table 6-5 Comparison operators

Operator Definition

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

When character or date strings are used in comparisons, they need to be enclosed in single quotation marks. Also, in these comparisons, numbers are greater than uppercase letters, and uppercase letters are greater than lowercase letters. For character strings, > asks for character strings closer to the end of the alphabet, < for character strings closer to the beginning of the alphabet. For date strings, > asks for dates later than the one specified and < asks for dates earlier than the one specified.
The following query asks for the videos whose length is greater than three hours:
SELECT title_id, name FROM titles WHERE film_minutes > 180
The following query lists the customers who joined during the current year:
SELECT cust_id, first_name, last_name FROM customers
	WHERE member_date >= '1993/01/01 00:00:00';
Querying for a specific range of values can be accomplished using a series of comparison operators or a BETWEEN predicate. The following statements would return the same data:
SELECT title_id, name FROM titles
	WHERE film_minutes BETWEEN 150 AND 180;
SELECT title_id, name FROM titles
WHERE film_minutes >=150 AND film_minutes <= 180;

Logical Operators

Logical operators join sets of search conditions together.
Table 6-6 lists the logical operators that are available in JDB.

Table 6-6 Logical operators

Operator Definition

AND

Joins two conditions and returns results when both conditions are true.

OR

Joins two conditions and returns results when either condition is true.

AND operators take precedence over OR operators unless you change the order of execution by using parentheses. Also, NOT takes precedence over AND.
If you wanted to find the science fiction videos that either have a PG or G rating or that are over three hours long, the following query would not return the correct results. This query first finds the science fiction videos that have a PG or G rating. Then, it finds any videos over three hours long.
SELECT title_id, name, film_minutes FROM titles
	WHERE genre_code = 'SCFI'
	AND rating_code IN ('G', 'PG')
	OR film_minutes > 180;
The addition of parentheses finds science fiction videos that either have a PG or G rating or that are over three hours long.
SELECT title_id, name, film_minutes FROM titles
	WHERE genre_code = 'SCFI'
	AND (rating_code IN ('G', 'PG')
	OR film_minutes > 180);
Remember that if a column is set to NULL, no comparison operator will retrieve it. The value of null is unknown. The following example would find the video titles whose length is less than an hour but would not find the ones whose length is entered as NULL:
SELECT title_id, name FROM titles
	WHERE film_minutes < 60;

**Table 6-4 Arithmetic operators**
Operator	Definition
+	Addition
-	Subtraction
*	Multiplication
/	Division

**Table 6-5 Comparison operators**
Operator	Definition
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

**Table 6-6 Logical operators**
Operator	Definition
`AND`	Joins two conditions and returns results when both conditions are true.
`OR`	Joins two conditions and returns results when either condition is true.

ORDER BY Clause

Specify the order for the query results
ORDER BY {integer | [correlation-name.]column-name} [ , ... ]
	[ASC | DESC]

integer
If integer is specified instead of a column-name, it refers to the position of a column or expression in the select list.
correlation-name
Identifier which substitutes for the table name.
column-name
Specifies the column or columns to be used for sorting the result set.
ASC
Specifies that the result set is to be sorted in ascending order. This is the default.
DESC
Specifies that the result set is to be sorted in descending order.

Description

An ORDER BY clause sorts the result set according to the specified column or columns. The columns specified in the ORDER BY clause must also be specified in the select list of the SELECT statement. By default, the sort occurs in ascending order which lists the smallest value first. You can set the sort order by specifying ASC for ascending or DESC for descending order.
If you list more than one column in the ORDER BY clause, it creates a nested sort. The sort for the first column takes precedence and occurs first. Then, within each of these groups, the rows are sorted again according to the value of the second column.
Instead of listing column names in the ORDER BY clause, you can use integers to refer to the column position.

Example

The following SELECT statement without an ORDER BY clause returns the list of video titles in the order shown in the result set:
SELECT title_id, name, genre_code FROM titles;
With the addition of an ORDER BY clause on the genre code, followed by the video name, the statement returns the data in the following order:
SELECT title_id, name, genre_code FROM titles
	ORDER BY genre_code, name;
If you use integers in the ORDER BY clause to refer to the column position, the previous statement appears in the following syntax:
SELECT title_id, name, genre_code FROM titles
	ORDER BY 3, 2;
genre_code is the third column appearing in the select list, and name is the second column in the select list.

SELECT Statement

Obtain information from a database table

SELECT [DISTINCT] {select-list | *} FROM table-name
		[correlation-name] [, ...]
	[WHERE search-conditions]
	[GROUP BY [correlation-name.]column-name[, ...]]
	[HAVING search-conditions]
	[ORDER BY {integer | [correlation-name.]column-name} [, ...]]

DISTINCT
Exclude any duplicate rows from the result set.
select-list
A series of column names, qualified by the table name if more than one database table is being accessed, and/or aggregate functions.
*
Selects every column from every table listed in the FROM clause.
table-name
Identifier for a database table.
correlation-name
Identifier which substitutes for the table name in the remainder of the statement.
WHERE
The WHERE clause specifies a search condition or a join. For more information on joins, refer to page 6-27. For more information on the WHERE clause, refer to page 6-54.
search-conditions
Specifies the conditions for the selection of data. For more information, refer to page 6-54.
GROUP BY
The GROUP BY clause specifies the column used to divide the result set into groups. For more information, refer to page 6-21.
HAVING
The HAVING clause specifies a search condition. For more information, refer to page 6-23
ORDER BY
The ORDER BY clause specifies the column(s) used to sort the result set. For more information, refer to page 6-42.

Description

The SELECT statement obtains data from the specified database table or tables. In its simplest form, the SELECT statement retrieves all the data from all the columns in the named table:
SELECT * FROM titles;
However, this syntax is not recommended for use inside an application. It is recommended that you include a select list in a SELECT statement.
Specifying a Select List

A select list determines which columns will be included in the result set. In the following example, the select list contains the name, genre_code, dir_last_name and film_minutes columns:
SELECT name, genre_code, dir_last_name, film_minutes
	FROM titles;
The select list can also include aggregate functions.
SELECT AVG (film_minutes) FROM titles;
Specifying a WHERE Clause

You can choose which rows will be a part of the result set by including a WHERE clause:
SELECT name, genre_code, dir_last_name, film_minutes
	FROM titles WHERE dir_last_name = 'Weir';
There are other search conditions available. Refer to page 6-54 for information on the WHERE clause.
Obtaining Unique Entries

You can include only unique rows in the result set by specifying the keyword DISTINCT. The following statement gets a list of directors:
SELECT dir_last_name FROM titles;
By using DISTINCT, the duplicate names are excluded from the result set:
SELECT DISTINCT dir_last_name FROM titles;
Obtaining Data from Multiple Tables

You can obtain information from more than one database table by using joins:
SELECT name, first_name, last_name, role
	FROM actors, titles, roles
	WHERE titles.title_id = roles.title_id
	AND actors.actor_id = roles.actor_id;
Refer to page 6-27 for more information on joins.

Subqueries

Nest a SELECT statement within another statement

Description

In a subquery, you can nest a SELECT statement within a SELECT, INSERT, UPDATE, or DELETE statement. The main syntax restriction is that a subquery cannot contain an ORDER BY clause. Refer to the sections on each keyword for any additional syntax restrictions. The subquery is enclosed in parentheses. Many statements using subqueries can alternatively be constructed using joins.
There are five keywords used for subqueries: EXISTS, IN, ANY, ALL, and SOME. These are explained below.
EXISTS
WHERE [NOT] EXISTS (subquery)
The EXISTS keyword tests for the presence of a result set from the subquery. The subquery can contain one or more columns. Since you are testing to see if any rows are returned, you can use SELECT * instead of a select list in the subquery.
If the NOT keyword is also specified, the WHERE clause is satisfied if there are no rows in the result set.
The following query checks to see if all of the video titles have entries in the roles table:
SELECT title_id FROM titles WHERE NOT EXISTS
	(SELECT * FROM roles
	WHERE roles.title_id = titles.title_id);
IN
WHERE expression [NOT] IN (subquery)
The IN subquery condition evaluates whether the expression in the WHERE clause matches any row returned in the subquery. The subquery using IN can only return one column, but it can return more than one row.
A subquery using the keyword IN is equivalent to the same subquery using =ANY.
The following query lists which science fiction movies a customer has previously rented:
SELECT title_id, name FROM titles WHERE genre_code = 'SCFI'  
	AND title_id IN
	(SELECT title_id FROM rentals
	WHERE cust_id = 6);
ANY, ALL, SOME
WHERE expression comparison-operator ANY (subquery)WHERE expression comparison-operator ALL (subquery)WHERE expression comparison-operator SOME (subquery)
The keywords ANY, ALL, or SOME are used with a subquery with one of the following comparison operators: >, >=, <, <=, <>, or =.
A subquery using ANY or SOME tests to see if the comparison is true for at least one of the values returned by the subquery. If the subquery returns no value, the search condition is false.
SELECT title_id, name FROM titles WHERE title_id = ANY
	(SELECT title_id FROM tapes WHERE status = 'I');
A subquery using ALL tests to see if the comparison is true for every value returned by the subquery. If the subquery returns no value, the search condition is true as well.
The following query tests to see which actors are not in the roles table:
SELECT actor_id, first_name, last_name FROM actors
	WHERE actor_id <> ALL
	(SELECT actor_id FROM roles);
The keywords ANY, ALL, or SOME can be omitted if you know that the subquery will return exactly one value. The following example returns one value by using an aggregate function. This query finds the customers whose rental amount was higher than average:
SELECT cust_id, first_name, last_name FROM customers
	WHERE rent_amount >
	(SELECT AVG(rent_amount) FROM customers);
Nested Subqueries

A subquery can also contain another subquery.
The following query finds the videos depicting dramatic stories that are also in the rentals table:
SELECT title_id, name FROM titles WHERE title_id IN
	(SELECT title_id FROM rentals WHERE title_id IN
	(SELECT title_id FROM titles WHERE genre_code = 'DRAM'));

UPDATE Statement

Update information in a database table

UPDATE table-name SET column-name = value [, ...]
	[WHERE search-conditions]

table-name
Unique identifier for the database table.
SET
The SET clause lists both the columns to be updated and the new values for those columns.
column-name
Name of the column to be modified.
WHERE
The WHERE clause specifies which rows will be updated. Refer to page 6-54 for more information on the WHERE clause.
search-conditions
Specifies the conditions for the selection of data. For more information, refer to page 6-54.

Description

The UPDATE statement modifies the value of one or more columns in the specified table.
If the UPDATE statement is part of a transaction, the update can be undone by rolling back the transaction.
Warning: If you omit the WHERE clause, all rows in the table are updated.

Examples

The following statement increases each price category by 10%. Since there is no WHERE clause, this statement updates each row in the pricecats table:
UPDATE pricecats SET price = price * 1.1;
The following statement updates the price category for a video:
UPDATE titles SET pricecat = 'G' WHERE title_id = 57;
The following query updates the member status to the frequent renter category if a customer rents over 10 videos a month:
UPDATE customers SET member_status = 'F'
	WHERE cust_id IN
	(SELECT cust_id FROM rentals
	WHERE rental_date
	BETWEEN '1993/09/01 00:00:00' AND '1993/10/01 00:00:00' 
	GROUP BY cust_id HAVING COUNT(*) > 10 ');

WHERE Clause

Specify search conditions and/or specify the relationship between tables
WHERE search-conditions
WHERE column-name join-operator column-name

search-conditions
Specifies the conditions for the selection of data.
column-name
Specifies a column in each of the tables to be joined.
join-operator
Specifies the join operator. Refer to page 6-27 for more information about joins.

Description

The WHERE clause performs two functions:

Specifying the search conditions for the result set.

Specifying the connection between tables named in the FROM clause.

A result set contains only the rows in the database that meet the search conditions. If more than one search condition is included in a WHERE clause, connect the conditions with the logical operators AND or OR.

Search Conditions

Search conditions can include the following:
BETWEEN
WHERE [NOT] expression [NOT] BETWEEN expression AND expression
The BETWEEN predicate specifies a range of database values. The following statement returns all the customers who joined during a certain year:
SELECT cust_id, first_name, last_name FROM customers
WHERE member_date BETWEEN '1992/01/01 00:00:00'
AND '1993/01/01 00:00:00';
EXISTS
WHERE [NOT] EXISTS subquery
The EXISTS keyword tests for the presence of a result set from the subquery. If the NOT keyword is also specified, the WHERE clause is satisfied if there are no rows in the result set. The subquery is enclosed in parentheses.
Notice that the subquery uses an * instead of a select list since you are merely testing whether rows meet the subconditions specified in the query.
SELECT title_id, name FROM titles WHERE EXISTS
(SELECT * FROM tapes WHERE title_id = tapes.title_id
AND status = 'I');
IN
WHERE expression [NOT] IN subquery WHERE expression [NOT] IN values-list
The IN keyword evaluates whether or not the expression in the WHERE clause matches a row in the subquery or a value in the values list. The subquery using IN can only return one column, but it can return more than one row.
The following query uses a values list to find the adventure and science fiction videos. It tests whether the genre_code for each video matches ADV or SCFI.
SELECT name, rating_code FROM titles
WHERE genre_code IN ('ADV', 'SCFI');
IS NULL
WHERE column-name IS [NOT] NULL
The keyword IS NULL searches for null values in the specified column.
SELECT name FROM titles WHERE rating_code IS NULL;
LIKE
WHERE column-name [NOT] LIKE literal [ESCAPE literal]
A LIKE predicate selects rows where a column value matches a specified pattern. The following query finds the video titles that begin with "M."
SELECT title_id, name FROM titles WHERE name LIKE 'M%'
Operators
WHERE expression {> | < | >= | <= | = | <>}{expression | subquery}
Operators allow you to compare column values. The following query finds the customers who have rented more than 2000 videos. For more information on using operators in subqueries, refer to page 6-48.
SELECT cust_id, first_name, last_name FROM customers
WHERE num_rentals > 200;
Specifying Joins

The WHERE clause also specifies the interconnecting columns between tables in joins. The following statement illustrates a multiple join. For additional information on joins, refer to page 6-27.
SELECT name, first_name, last_name, role
	FROM titles, actors, roles
	WHERE titles.title_id = roles.title_id
	AND roles.actor_id = actors.actor_id 
	AND titles.title_id = 62;

SQL Syntax Summary

CREATE DATABASE database-name

CREATE TABLE table-name (
	column-name data-type [(length)] [NOT NULL] [, column-name ...]
	[PRIMARY KEY (column-name, column-name ...]), ]
	[UNIQUE (column-name [, column-name ...]), ]
	[FOREIGN KEY (column-name [, column-name ...])
	REFERENCES table-name (column-name [, column-name ...]) [,]
		]

CREATE TABLE table-name (
	column1 data-type [(length)] NOT NULL,
	column2 data-type [(length)] NOT NULL,
	column3 data-type [(length)] NOT NULL,
	column4 data-type [(length)] NOT NULL,
	column5 data-type [(length)],
	column6 data-type [{length)],
	PRIMARY KEY (column1, column2),
	UNIQUE (column2),
	UNIQUE (column3, column4),
	FOREIGN KEY (column5, column6)
		REFERENCES table2 (column, column),
	FOREIGN KEY (column5) REFERENCES table3 (column)
	)

DELETE FROM table-name [WHERE search-conditions]

DROP DATABASE database-name

DROP TABLE table-name

INSERT INTO table-name [(column-list)]
	VALUES (literal| NULL [, ...])

INSERT INTO table-name [column-name [, ...]] query-expression

SELECT [DISTINCT] {select-list | *}
	FROM table-name [correlation-name] [, ...]
	[WHERE search-conditions]
	[GROUP BY [correlation-name.]column-name [, ...]]
	[HAVING search-conditions]
	[ORDER BY {integer| [correlation-name.]column-name} [, ...] ]

UPDATE table-name SET column-name = value [, ...]
	[WHERE search-conditions]

Chapter 6. SQL Reference

Reference Organization

SQL Clauses and Keywords

SQL Concepts

Notation Conventions

Description

Example

See Also

BETWEEN Predicate

Description

Example

Variants

See Also

CREATE DATABASE Statement

Description

Example

CREATE TABLE Statement

Description

Example

See Also

Data Types

Description

Numeric Columns

Example

DELETE Statement

Description

Example

See Also

DROP DATABASE Statement

Description

Example

DROP TABLE Statement

Description

Example

GROUP BY Clause

Description

Example

See Also

HAVING Clause

Description

Example

See Also

INSERT Statement

Description

See Also

Joins

Description

See Also

LIKE Predicate

Description

Example

See Also

Null Values

Description

Examples

Operators

Description

Comparison Operators

ORDER BY Clause

Description

Example

SELECT Statement

Description

See Also

Subqueries

Description

Nested Subqueries

See Also

UPDATE Statement

Description

Examples

See Also

WHERE Clause

Description

Search Conditions

Specifying Joins

See Also

SQL Syntax Summary