Lecture 15. The SQL language. Means of data manipulation

15.1. Query structure

In order to be able to more or less accurately tell about the structure of queries in the SQL / 89 standard, it is necessary to start with a syntax summary:

  <cursor specification> :: =
    <query expression> [<order by clause>]
 <query expression> :: =
    <query term>
 |  <query expression> UNION [ALL] <query term>
 <query term> :: =
    <query specification>
 |  (<query expression>)
 <query specification> :: =
    (SELECT [ALL | DISTINCT] <select list> <table expression>)
 <select statement> :: =
    SELECT [ALL |  DISTINCT] <select list>
    INTO <select target list> <table expression>
 <subquery> :: =
    (SELECT [ALL | DISTINCT] <result specification>
    <table expression>
 <table expression> :: =
    <from clause>
   [<where clause>]
   [<group by clause>]
   [<having clause>] 

The language allows for three types of syntactic constructions starting with the SELECT keyword: cursor specification (cursor specification), select statement and subquery. The basis of all of them is the syntax "table expression (table expression)". The semantics of the table expression is that based on the sequential application of the from, where, group by and having sections from the tables specified in the from section, a new resultant table is built, the order of the rows is not defined and there may be duplicates among the rows (i.e. In the general case, the table-result of a table expression is a multiset of rows). In fact, it is the structure of the table expression that most characterizes the query structure of the SQL / 89 language. We will consider below the structure and meaning of the sections of the table expression below, but before this we will discuss the three mentioned constructions, including table expressions, in more detail.

15.1.1. Cursor Specification

The most common design is the "cursor specification". A cursor is a concept of the SQL language, which allows using a set of special operators to get line-by-line access to the result of a database query. There are no restrictions on table expressions involved in cursor specification. As can be seen from the syntax summary, when defining a cursor specification, three additional constructs are used: query specification, query expression, and ORDER BY clause.

Request Specification

The query specification defines a list of samples (a list of arithmetic expressions above the values ​​of the columns of the result of a table expression and constants). As a result of applying the sample list to the result of a table expression, a new table is built, containing the same number of rows, but generally speaking a different number of columns containing the results of calculating the corresponding arithmetic expressions from the sample list. In addition, the query specification may contain the keywords ALL or DISTINCT. If the DISTINCT keyword is present, duplicate rows are removed from the table obtained by applying the sample list to the result of the table expression; when specifying ALL (or simply in the absence of DISTINCT), duplicate rows are not deleted.

Query expression

A query expression is an expression that follows the specified syntax rules based on query specifications. The only operation that is allowed to be used in query expressions is the UNION operation (joining tables) with a possible variant of UNION ALL. The tables operands of the query expression are subject to the requirement that they all must contain the same number of columns, and the corresponding columns of all the operands must be of the same type. The query expression is calculated from left to right, taking into account the brackets. When the UNION operation is performed, the usual set-theoretic union of the operands is performed, i.e. duplicates are removed from the resulting table. When the UNION ALL operation is performed, the resulting table is formed, which may contain duplicate rows.

ORDER BY section

Finally, the ORDER BY section allows you to set the desired order for viewing the result of a query expression. The ORDER BY syntax is as follows:

  <order by clause> :: =
    ORDER BY <sort specification>
   [{, <sort specification>} ...]
 <sort specification> :: =
    {<unsigned integer> |  <column specification>}
   [ASC |  DESC] 

As can be seen from these syntax rules, the list of columns of the result of the query expression is actually specified, and for each column the order of viewing the rows of the result is specified depending on the values ​​of this column (ASC - ascending (default), DESC - descending). Columns can be set by their names if and only if (1) the query expression does not contain UNION or UNION ALL operations and (2) in the query specification sample list this column corresponds to an arithmetic expression consisting only of the column name. In all other cases, the ORDER BY clause must indicate the ordinal number of the column in the table — the result of the query expression.

15.1.2. Sampling operator

The select statement is a separate SQL / 89 statement that allows you to get the result of a query in an application program without using a cursor. Therefore, the select statement has a syntax that is different from the cursor specification syntax, and when it is executed, there are restrictions on the result of a table expression. In fact, both are dictated by the specifics of the select statement as a single SQL statement: when it is executed, the result must be placed in the variables of the application program. Therefore, an INTO section appears in the statement, containing a list of variables for the application program, and the restriction arises that the resulting table must contain no more than one row. Accordingly, the result of the base table expression must contain no more than one row if the select statement does not contain the DISTINCT specification, and the table obtained by applying the sample list to the result of the table expression does not contain more than one non-matching rows if the DISTINCT specification is specified.

Note: In the Oracle SQL Database dialect, an extended version of the select statement is supported, the result of which is not necessarily a single-line table. This extension is not supported in either SQL / 89 or SQL / 92.

15.1.3. Subquery

Finally, the last SQL / 89 construct, which may contain table expressions, is a subquery, i.e. A query that can be included in the predicate of a SQL statement selection condition. In SQL / 89, the restriction applies to subqueries that the resulting table must contain exactly one column. Therefore, in the syntax rules defining the subquery, instead of the select list, the expression that evaluates the value is indicated, i.e. arithmetic expression. Note also that since the subquery is always nested in some other SQL statement, the values ​​of the columns of the current rows of the tables participating in (sub) more external level queries can be used as constants in the arithmetic expression of the WHERE and HAVING clauses. See below for more on this when describing the semantics of table expressions.