6. Operator SELECT. Part 2

NULL determinant availability in the rows of the table is verified by means of following construction:

EXAMPLE 6.17

Select codes and names of books that do not contain UDK (table 6.15).

Table 6.15

6.14.1. Computation of final values in SELECT operator

Computation of final values in SELECT operator is carried out by means of aggregate or statistic functions: COUNT(), SUM(), AVG(), МАХ(), МIN().

Function COUNT(<expression>) computes occurrence of expression quantity into all records of final data set. It operates with expressions of any type: numeric, symbolic or «date/time» data type.

EXAMPLE 6.18

Determine how many books have cost that exceeds 25 grivnas (table 6.16).

Table 6.16

If it is necessary to consider only one of several similar records, proposition DISTINCT is put in brackets before the expression. Expression can be also computed according to values of several tables:

EXAMPLE 6.19

Determine how many librarians gave out books (table 6.17).

Table 6.17

Total quantity of records that meet determined condition can be indicated by means of COUNT function excluding phrase DISTINCT. However, the same librarian can give out books to different readers only. Proposition DISTINCT permits to exclude replicate values.

Function SUM(<expression>) computes the sum of all values of column or expression. In such case column must contain numeric data type (contains integral numbers, floating point numbers or values of money). The product that is returned by this function has the same data type as column or expression, but the accuracy (width) of the result can be higher. For example if we apply function SUM() for the column that contain 16-byte integral numbers it can be returned as 32-byte integral number.

EXAMPLE 6.20

Determine total value of library fund books (table 6.18).

Table 6.18

Functions MIN(<expression>), MAX(<expression>) and AVG(<expression>) provide the possibility to find the lowest, the highest and average value correspondingly. MIN(), MAX() operate with numeric, lowercase and «date/time» data types. AVG() operates with numeric data types only. The product that is returned by these functions has the same data type as the expression.

EXAMPLE 6.21

Determine minimal, maximal and average book cost (table 6.19).

Table 6.19

6.14.2. Grouping

Grouping of records is applied if it is necessary to give out aggregate values not for all relations but separately for each included group of corteges that are characterized by common feature. Usually these are similar values in some attribute or group of attributes. In this case proposition GROUP BY of operator SELECT is used:

GROUP BY <column>[, <column> ...]

In such case it is necessary that one of the columns of the final data set is processed by aggregate function.

EXAMPLE 6.22

Determine quantity of books that are kept in different funds of library and also their total value (table 6.20).

It is not necessary to include names of columns BookCode and Cost into the list of phrase GROUP BY, as in the list of SELECT proposition they are applied in generalizing functions only. At the same time FundCode column in the list of SELECT proposition is not connected with any generalizing function and that is why must not obligatory be indicated in the phrase GROUP BY.

Table 6.20

During the processing of this query several logical operations are carried out

Table 6.21

Rows of the table BookInventoryNumbers are distributed in the group according to the values in the column of fund code number (FundCode). Within the bounds of each group books codes data is determined for one of the funds. In our example two groups are generated. For each group we compute total quantity of rows, that is equal to the quantity of books codes, and the sum of Cost column values, that is a sum of books cost for each fund, that we are interested in. Then integrated summarized row for the whole group of output rows is generated. Finally, generated rows of the final table are transposed in ascending order of fund code number that is indicated in the column FundCode.

6.14.3. Sample conditions for grouped records

Sample conditions for grouped records is indicated in proposition

HAVING <search conditions>

Search conditions are determined according to the same rules as for WHERE. HAVING and WHERE propositions sharing is allowed. First of all, proposition WHERE cancels rows that doesn’t meet search conditions. Then HAVING proposition search conditions are applied. They filter data set to detect rows that has wrong total value.

ISO standard demands that names of columns used in proposition HAVING are present in the GROUP BY list or applied in aggregate functions. On practice, search conditions in the phrase HAVING always include at least one aggregate function. Search conditions that do not contain aggregate functions must be put in WHERE and applied for separate rows selection. In search conditions WHERE to apply aggregate functions is forbidden.

EXAMPLE 6.23

Determine quantity of books and their total value for each library fund that contains more than six books (table 6.22).

This example is similar to previous one, but in this case additional limitations are implemented. They underline that we are interested only in those funds that contain more than six books. Such requirment relates groups and thus it is necessary to use phrase HAVING in query.

Table 6.22

6.15.1. Subquery

Subquery is an instrument of temporal table generation where content is processed by outer operator. Inner queries can be used for computing of expression values after SELECT proposition and for search terms after comparison operators (=, <,>, >=, <=, <<) or IN at WHERE and HAVING propositions. Moreover subqueries can be used in operatotrs INSERT, UPDATE and DELETE. Text of subquery must be put in brackets.

There are three types of subqueries:

1. scalar subquery generates table that is composed of one column and one row; this is one value; it can be used everywhere when it is necessary to indicate one value;
2. lowercase subquery generates values of several table columns but as a single row; it can be used everywhere when lowercase values constructor is applied;
3. table subquery generates values from one or more table columns, placed in several rows. It can be used everywhere when it is possible to determine values sets, for example as IN predicate operand.

There are several rules and limitations for handling subqueries:

1. There should be no phrase ORDER BY in subquery, even though it can be present in outer query.
2. The list in SELECT proposition must contain names of separate columns or generated expressions apart from occasions when subquery is an operand of EXISTS operator.
3. Columns names are refered to table that has name determined in FROM proposition on default. However it is acceptable to refere to the columns of table indicated in FROM of outer query. Then qualified names of columns are used. Such subqueries are called correlated.
4. If subquery is one of two operands that were applied in comparison operation, it must be indicated in the right part of this operation.

When it is impossible to use one subquery only, nested subquery is used inside it and etc.

6.15.2. Saclar subqueries

Saclar subqueries generate table that is composed from one column and one row. To generate such table it is necessary to indicate one column after SELECT and to organize search condition in WHERE by candidate key.

EXAMPLE 6.24

Display all dates when reader that has reader card number 28 returned back books (table 6.23).

Inner operator

is assigned to detect code of reader that has reader card number 28. After that, outer subquery is carried out. In other words, inner operator SELECT generates table that contains only one value - Code = 2. As a result outer operator SELECT has the following record:

Table 6.23

Table with one column and one row can be generated if after SELECT we put one aggregate function without applying records grouping in GROUP BY proposition. In such case sampling conditions that are indicated in WHERE and HAVING are not important.

EXAMPLE 6.25

Compose list of books inventory numbers that have price higher then averege. Indicate how their price exceeds the average price of library books (table 6.24).

Table 6.24

It is necessary to underline that it is impossible to apply «WHERE Cost > AVG(Cost)», as it is prohibited to apply aggregate functions in WHERE proposition. To achieve desired result we must generate subquery that computes the average price of books and then to apply in outer operator SELECT that is dedicated for sampling information about books that have price higher then average value. In other words, subquery generates value of average book price in the library that is 38,27 grivnas. The result of this scalar subquery execution is applied in outer operator SELECT for computing price deviation from average level and for sampling of information about books. That is why outer operator SELECT is recorded as following:

6.15.3. Subqueries that return values set

Subqueries that return values set are used as operands of IN predicate. For example table subquery can be used instead of inner table join. To illustrate this process, let’s return back to EXAMPLE 6.7: «Compose the list of all readers who have ever taken books in the library». Using table subquery and aliases of tables operator SELECT will be recorded as:

All readers who have ever taken books in the library are registered in the table BookGiveOutRecord. Thus we have to cancel from the table Readers family names of only those readers, whose codes are contained in the values set that is generated by subquery.

6.16.1. Operator EXISTS

Operator EXISTS is applied when it is necessary to indicate in the search conditions that only those records that receive back one or more values from subquery must be selected:

[NOT] EXISTS(<subquery>)

Thus operator EXISTS is applied together with subqueries only. It generates TRUE if there is one or more records in the final subquery data set. Otherwise it generats FALSE. Rules for NOT EXISTS are contrary.

EXAMPLE 6.26

Select codes and names of books of student library fund that have been even given out to readers (table 6.25).

Table 6.25

6.16.2. Operator SINGULAR

Operator SINGULAR is applied when it is necessary to indicate in search conditions that only those records that receive back only one value from subquery must be selected from the table:

[NOT] SINGULAR(<subquery>)

EXAMPLE 6.27

To select codes and names of books that were given out to readers only once (table 6.26).

Table 6.26

6.16.3. Operators ALL, SOME, ANY

Operators ALL, SOME, ANY are applied when it is necessary to indicate in search conditions that values (column values, result of expression computing), that is to be compared must specifically correlate to all values from the set that is generated by subquery:

Subquery can generate more then one value. Operator determines comparison operation (>, >=, < and etc.). Relations between values that are compared and values that are returned back by subquery are defined by means of operators ALL and SOME (ANY):

generates TRUE, if current value of attribute «STOLBEZ» will be bigger then all values of the attribute «POLE» of relation «TABLIZA»;

generates TRUE, if current value of attribute «STOLBEZ» will be bigger then at least one value in the attribute «POLE» of relation «TABLIZA».

EXAMPLE 6.28

Find inventory numbers of all books whose price exceeds price of at least one book from library fund number «1» (table 6.27).

In this case inner subquery will generate following values: {15.56, 22.33, 34.01, 12.99, 36.05, 74.20, 36.69, 48.13, 27.99}, and outer query selects information about books that have price that exceeds at least one value of mentioned set (in fact, more then minimal value – 12.99).

Table 6.27

EXAMPLE 6.29

Find inventory numbers of all books whose price exceeds price of any book from library fund number «1» (table 6.28).

Outer query selects information about books whose price exceeds maximal value of set {15.56, 22.33, 34.01, 12.99, 36.05, 74.20, 36.69, 48.13, 27.99}.

Table 6.28

Proposition HAVING is applied when it is necessary to determine aggregate function in search conditions for nested subquery.

EXAMPLE 6.30

Copies of the same book can be sent to the library in different periods of time and their prices can differ. It is necessary to select code and average cost of the book copies whose average price of one copy is higher then average cost of other books (table 6.29).

First of all, average cost of all copies is determined according to the table BookInventoryNumbers (nested subquery). Then we select code and average cost of the book with the highest price from the same table.

Table 6.29

Pay attention that in such case alias of the table is used for determination of attributes necessary for outer and inner SELECT operators processing rather then for reduction of logs.

Outer join is determined in the proposition FROM of SELECT operator. It is recorded in the following format:

Final data set includes all logs of the master table. Which table will be a master one is determined by the type of join:

LEFT – (left outer join), when master table is one that is placed on the left from join;

RIGHT – (right outer join), when master table is one that is placed on the right from join;

FULL – final data set includes all table rows that are placed on the left from the join type and table that is placed on the right from the join type even if search conditions are not carried out for them.

EXAMPLE 6.31

To execute outer left and right joins of tables 6.30 and 6.31 according to attributes Р2 and Р1 correspondingly.

Table 6.30

Table 6.31

Let’s record operator SELECT for left join indicating tables 6.30 and 6.31 as A and B correspondingly.

Columns of master table А are indicated with grey color. As you can see, for the record of table А, where column А.Р1 contains value «D», there are no pair logs in the table В that would meet search condition А.Р2 = В.Р1. That is why this record in the table А is indicated in the join with an empty log (table 6.32).

Table 6.32

Let’s record operator SELECT for the right join:

Column of the master table В is marked with grey color. As you can see, for the record of table В, where column В.Р1 contains value «z» and column В.Р2 contains value «2», there are no pair logs in the table А that would meet search condition А.Р2 = В.Р1. That is why this record in the table В is joint with an empty log (table 6.33).

Table 6.33

Sometimes it is necessary to unite two or more final data sets that are generated by separate SELECT operators. Then final data sets must have similar structure, that meant equal quantity and types of columns that are generated. Such data sets are compatible for join. Replicate logs are deleted from join data set.

EXAMPLE 6.32

To join products of three queries:

1. Passport with series «AA» (table 6.34).

Table 6.34

2) Passports issued in 2000 (table 6.35);

Table 6.35

3) Passports with numbers that start with 4 (table 6.36);

Table 6.36

Let’s join three data sets: (table 6.37).

Table 6.37

Operation || joins two row values that can be presented with phrases: <row value1> || <row value2>.

Operation || can be used either after SELECT proposition to determine values that are generated or in WHERE.

EXAMPLE 6.33

Put the family name, name, patronymic and place of work of readers in one column. Moreover, put place of work in parenthesis (table 6.38).

Table 6.38

It is possible to use tables from different DB within one query. In such case name of the table in the Interbase DBMS is indicated in the format

:DB alias :Table name

DB alias is an alias determined in BDE Administrator applet. Below you can see the example of a call within one query to the InterBase DB tables (alias «MONITOR») and Oracle (alias «DWH»):

Format of SELECT operator must be adjusted in corresponding documentation that is provided for the DBMS that was chosen.

1. What construction of SELECT operator is responsible for verification of the NULL determinant availability?
2. What differences and mutual features of functions that compute summarized values of SELECT operator you know?
3. What for is used proposition GROUP BY of operator SELECT?
4. What are the differences between search conditions of propositions HAVING and WHERE?
5. Where can be subqueries applied? What is the purpose?
6. What are the differences between final data sets that are generated by subqueries familiar to you?
7. What rules and limitations refer to subqueries formation you know?
8. How can subquery provide the possibility to use aggregate functions in search conditions of WHERE proposition?
9. What values and in what situations are generated by operators EXISTS and SUNGULAR?
10. How is it possible to compare in search conditions relation of scalar value with all possible values of set that is generated by subquery?
11. What is the difference between types of outer relations join familiar to you?
12. What is the difference between outer and inner relations join?
13. What conditions must meet SELECT operator final data sets that are joined by operator UNION?
14. How is it possible to unite in one column data that are placed in different columns of output tables?
15. Is it possible to operate different tables that are placed in different DB within one SELECT operator?