SQL Summary

SQL commands can be executed in two distinct contexts:

The syntax is slightly different in these two contexts.

Most databases have multiple tables. They frequently use foreign references to allow data from one table to be referenced from another table.

SQL has five important types of statements:

DELETE, UPDATE, and SELECT statements can use a WHERE clause to limit the rows that are affected or returned and to capture foreign reference relationships.

Context

SQL commands can be executed in two distinct contexts:

Foreign Reference

Most databases have multiple tables. They frequently use foreign references to allow data from one table to be referenced form another table. This is important for two reasons:

When a table is created that uses foreign references, constraints can be used to ensure that deleting referenced rows from the referenced table does not result in dangling references.

Foreign reference conditions are frequently used in WHERE clauses in SELECT statements from multiple tables to ensure that differents columns in a result row are about the same entity.

CREATE TABLE

CREATE TABLE statements create database tables. These statements specify each of the columns in a table and specify table-level constraints such as foreign references. The general form for a columnDefinition is

dataType columnConstraint

JDBC

A CREATE TABLE statement is executed in JDBC as follows:

statement.execute(sql_text);

References

Foreign References

Foreign references involve a foreign reference column in one table that references an identity column in a target table. Both of the columns need special constraints.

Identity Columns

If a table is a target for foreign references it should have a column with the following definition:

id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY

If you are not sure if the table will be a target for foreign references you should add the id column anyway. If the column proves to be unnecessary it can be removed easily. It may be much harder to add the column after the table is populated with data.

Foreign Reference Columns

When one table has a foreign reference to another table, it can be declared with a foreign key constraint. This is a table constraint with the following form:

FOREIGN KEY (foreignKeyColumn) REFERENCES targetTable (targetIDColumn)

Adding this constraint prevents deletes from the target table that would result in a dangling reference.

INSERT

INSERT statements add rows to database tables. These statements specify the data to be placed in each of the columns of new rows.

JDBC

An INSERT statement is executed in JDBC as follows:

int rowCount = statement.executeUpdate(sql_text);

Declaring the rowCount variable for the return value may be omitted. It can, however, be useful for checking if the insert was successful.

DELETE

DELETE statements delete rows from database tables. These statements almost always use a WHERE clause to limit the rows that are deleted. A DELETE statement without a WHERE clause deletes all of the rows in a table.

JDBC

A DELETE statement is executed in JDBC as follows:

int rowCount = statement.executeUpdate(sql_text);

Declaring the rowCount variable for the return value may be omitted. It can, however, be useful for checking if the delete was successful.

UPDATE

UPDATE statements modify rows in database tables. These statements specify the columns to be updated and the new data for those columns. They almost always use a WHERE clause to limit the rows that are updated. An UPDATE statement without a WHERE clause updates all of the rows in a table.

JDBC

An UPDATE statement is executed in JDBC as follows:

int rowCount = statement.executeUpdate(sql_text);

Declaring the rowCount variable for the return value may be omitted. It can, however, be useful for checking if the update was successful.

SELECT

SELECT statements are used to retrieve data from database tables. These statements return tables, often constructed from columns from multiple tables. In a simple SELECT statement, the list of columns can be replaced by an asterisk ("*") to specify all columns of the table(s) specified in the FROM clause.

SELECT statements can use a WHERE clause to limit the rows that are returned and to capture foreign reference relationships. They can also use AS phrases to rename the column headers that appear in the returned table.

Note that for SELECT statements you can use column expressions to specify the columns of the result table. Expressions allow you to combine multiple column and literal values using functions and operators to form result columns. You can also rename for result columns using the AS keyword. This is especially useful for columns specified by complex expressions.

JDBC

A SELECT statement is executed in JDBC as follows:

ResultSet resultSet = statement.executeQuery(sql_text);

Column Renaming

Columns in the result of a SELECT can be renamed using an AS phrase after the expression for the column. In other words, a column expression with a renamed column has the following form:

columnExpression AS "newName";

Column renaming is especially useful when the columnExpression is not just a column name, but is a complex expression. Without the AS phrase, the text of the expression is used as a column header.

Expressions

The columns in a SELECT result can not only be columns from database tables; they can be computed from multiple tables using expressions. These expressions can use functions to transform values and operators to combine values.

References

Aggregate Functions

Aggregate functions evaluate an expression over a set of rows. They operate on a set of values and reduce them to a single scalar value. Derby aggregate functions can compute the minimum, maximum, sum, and average of an expression over a set of rows. They can also count rows.

Aggregate functions by themselves compute a single value for all rows that satisfy the WHERE conditions. You can also use aggregate funtions on groups of rows by adding a GROUP BY clause after the WHERE clause. This clause has the following form:

GROUP BY columnName1, columnName2, ...

This results in the aggregate function being applied to groups of rows. The groups consist of all rows that have the same values in each of the specified columns.

References

ORDER BY Clauses

In a SELECT statement, an ORDER BY clause can be added after the WHERE clause and GROUP BY clause to specify the order of the rows in the result table. It most commonly has the following form:

ORDER BY columnName1, columnName2, ...

Here, the columns are order first by columnName1. If there are ties then the order of tied rows is determined by columnName2, and so on. Each column name can be followed by either of the keywords ASC or DESC to specify whether the column is sorted in ascending or descending order. ASC is the default.

WHERE Clauses

DELETE, UPDATE, and SELECT statements can use a WHERE clause to limit the rows that are affected or returned and to capture foreign reference relationships. They have the following general form:

WHERE
  booleanExpression;

There are numerous ways of forming a simple boolean expression from column values and literal values. Simple boolean expressions are often combined using the AND operator.

Many of the simple boolean expressions in SELECT queries have the following form:

foreignReferenceColumnName = targetTableName.targetTableIDColumnName

Such expresion establish foreign reference conditions. They ensure consistency of column data from multiple tables.

References

Values

Basic values in WHERE clauses are either column names or literal values. Wherever a literal value is used it can be replaced by one of the following:

References

Example

The following example SQL files illustrate how dynamic parameters and nested selects can be substituted for literal values.