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 commands can be executed in two distinct contexts:
In NetBeans, for example, you can open an SQL file in the editor pane. When you do so NetBeans adds a toolbar above the edit text. On of the widgets in the toolbar lets you select a database connection. Clicking on an execute widget sends the commands in the SQL file directly to the selected database.
You can also execute the file by right-clicking on it and selecting "Run" from the popup menu. A dialog will ask you to select the database connection.
SQL that is sent directly to a database can contain multiple SQL statements. Each statement must be terminated by a semicolon (";").
Java programs, including JSF applications, always send SQL to a database using JDBC.
When JDBC is used, SQL statements must be sent to the database one statement at a time. The statement must not be terminated by a semicolon.
A CREATE TABLE statement is executed in JDBC as follows:
statement.execute(sql_text);
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.
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.
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.
A SELECT statement is executed in JDBC as follows:
ResultSet resultSet = statement.executeQuery(sql_text);
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.