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.
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 different columns in a result row are about the same entity.
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
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.
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.
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 statements add rows to database tables. These statements specify the data to be placed in each of the columns of new rows.
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.
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.
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.
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.
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.
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.
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.
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.
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:
The question mark represents a dynamic parameter. A dynamic parameter can have its value determined at run time in JDBC.
Using a dynamic parameter turns the statement into a parametrized statement. This is rarely done except when using JDBC so the semicolon should be omitted from the end of the statement.
The parenthesized SELECT statement is called a nested SELECT. It can also be parametrized.
Nested SELECTs can almost always be avoided inside SELECT statements. However, they may be needed in DELETE or UPDATE statements since these statements only allow you to specify a single table. If you are trying to update or delete a row in one table with a condition based on its foreign reference into a target table, and you don't know the id in the target table, you can use a nested SELECT to obtain the id based on what you know about the other columns in the target table.