6. Operator SELECT. Part 1

SELECT operator is one of the most important SQL operators that are applied more often than other operators. This operator is very high-end [4, 6]. It provides the possibility to select necessary information from DB relations and convert them into the form that will satisfy users. Using this operator we can implement rather complicated and hungus terms of data sample from different interrelated tables.

SELECT operator is completely abstract away from data representation [4, 6 … 8]. All attention is concentrated of the data access problem. It has the following format:

At first glance this format is rather hungus and thus looks like rather complicated one. However after examining step by step the capabilities of SELECT operator you will see how simple it is.

In the simplest case SELECT operator has the following form:

From the standpoint of relational algebra present operator carries out the operation of projection. After FROM the list of DB tables that contain information for access is presented. The list of values is presented after SELECT operator. In most cases these are names of tables columns listed after proposition FROM. Asterix indicates that all columns of the tables must be included in the query result.

EXAMPLE 6.1

To give out data set that contains all attributes and corteges of relation Readers (table 6.1).

SELECT *

    FROM Readers

This is equal to:

Table 6.1

Operator SELECT permits to form the columns that contain the results of calculations. The process is carried out with implementation of arithmetic expressions that are formed according to the rules that are determined for algorithmic language.

EXAMPLE 6.2

For each book from the table BookInventoryNumbers we compute the product of its price by sum of book code values and books fund.

It is necessary to understand that this example has no logical sense. It is presented for illustration of SELECT operator capacity only. The product of computing expression (BookCode + FundCode) × Cost for each record from the table BookInventoryNumbers is recorded in the column that was given name Column3 by DBMS on default (table 6.2).

Table 6.2

It is possible to rename any column of the table that is a product of SELECT operator processing. We have to put AS proposition and the new name after expression or name of the column:

EXAMPLE 6.3

Give the name «Example» to the column that is computed by means of arithmetic expression from the previous example.

Iterative records are defined as records that contain identical values in all columns of data set. If final data set must not contain them, DISTINCT proposition is put after SELECT. If final data set must contain all records, ALL is put after SELECT. This proposition is run on default:

EXAMPLE 6.4

Insert code of librarians who gave out books (table 6.3).

SELECT OutLibrarianCode

    FROM BookGiveOutRecord

Table 6.3

Pay attention that the result of query execution contains replicate values because in contrast to relational algebra operation projection operator SELECT doesn’t contain replicate values within execution of one or several columns projection. To delete replicate rows from final table DISTINCT is applied (table 6.4):

Table 6.4

In the relation that generates SELECT operator order of corteges in undefined. It is convenient, but not always. ORDER BY proposition permits to determine what attributes must be used to sort out corteges. It contains the list of columns names that are separated by comas and that will be used for sorting out. The first column in the list will be used for global sorting, second column - for sorting within the group that is determined by the unique value of the first column and etc.:

EXAMPLE 6.5

Create the list of readers by A/Z (table 6.5).

Table 6.5

Data set, that is returned by operator SELECT, can include only those records that meet some determined search and sample requirements that are indicated in the WHERE proposition of SELECT operator:

There are five basic types of search conditions. According to ISO vocabulary there are five predicates: comparison, enclosure to the range, correspondence to the template, set membership and NULL determinant presence.

Comparison gives the possibility to compare output computations of different expressions. As expressions can be defined: constants, attribute values, scalar operators SELECT. The following comparison operators can be used between expressions: = (equal), < (less), > (more), <=(less or equal);>= (more or equal), !< (not less, that is more or equal), !> (not more, that is less or equal), <> (not equal), != (not equal).

The results of comparison of several expressions can be joined by operators OR and AND. Application of operator NOT permits to get inversion of the expressions comparison result. The verification of the range membership can be organized by join of two expressions comparison results carried out by operator AND. Also SQL language contains special function BETWEEN that implements verification of given range membership for results of expression computing. To check the template membership SQL language foresees the following operators: LIKE, CONTAINING, STARTING. Set membership for results of expression computing is verified by means of IN operator. Operator IS NULL implements verification of undetermined value in the attribute of relation.

6.7.1. Comparison of constant with relation’s attribute values

Comparison of constant with relation’s attribute values is the simplest search condition that is used in WHERE proposition of SELECT operator.

EXAMPLE 6.6

Compose the list of librarians with clock number more than 80.

Librarians is an output value, and expression ClockNumber > 80 is a predicate. Sample operation generates new relation that contains only those output relation corteges where value of attribute ClockNumber exceeds 80 (table 5.1).

6.7.2. Relation’s attributes values comparison

Relation’s attributes values comparison in most cases is applied for implementation of inner join.

EXAMPLE 6.7

Compose the list of all readers who have ever taken books in the library (table 5.4).

In the example with theta-join to compose such list equi-join was used. It contained two attributes Readers.Code and ReaderCode. If they have same names, for example ReaderCode, then to delete one of them from final relation we could apply natural join operation:

While SELECT operator execution for each cortege of relation BookGiveOutRecord the row of the table Readers is searched. ReaderCode attribute value must concur with Code attribute values of the current cortege of relation Readers. Moreover, it is not important how to place rows of the tables in the search conditions: Readers.Code = BookGiveOutRecord.ReaderCode is equal to BookGiveOutRecord.ReaderCode = Readers.Code.

For theta-join integration of two relations R and S logical order of the final relation formation can be formed according to the following sequencing:

1. Attributes that are indicated after SELECT proposition form Cartesian product by catenation of final attributes of each cortege from relation R and final attributes of each cortege from relation S.
2. All corteges that do not meet search conditions of WHERE proposition are deleted from relation that is a result of formed product.

WARNING! Term «final relation generation logical order» is not used on occasion. There are two separate levels of data design - logical and physical. Logical level is usually an abstract level: it helps us to understand processes better. Physical level determines processes that really take place but are hidden in most cases. Physical processes that are really carried out during the query execution can differ from their logical concept.

During query execution SQL-server always seeks to optimize it that is to carry it out as quickly as possible and with minimum computational burden. Also queries optimization in InterВase represents «black box». In other words it is difficult to say how the query will be executed as during optimization significant role is dedicated to current state of DB. Below you can find an example of queries optimization.

6.7.3. Comparison of the column value with the result of the expression computing

Comparison of the column value with the result of the expression computing is usually applied when nesting subqueries mechanism is used (nested SELECT operators). It will be discussed later. First of all let’s study case when the expression computing result is compared with content of relation attribute.

EXAMPLE 6.8

Let’s determine product of price by sum of book code and fund values for each book. We’ll select only those rows that have a result of abovementioned expression more then 120 (table 6.6).

Table 6.6

6.7.4 Application of AND, OR and NOT operators

Application of AND, OR and NOT operators permits to construct more complicated search conditions. It is recommended to use brackets in order to eliminate any possible assessments. Expression computing is carried out according to the following rules:

the expression is computed from the left to the right;

subexpression in brackets is first to be computed;

operators NOT are executed before operators AND and OR;

orators AND are executed before operators OR;

EXAMPLE 6.9

List family names, names and patronymics of readers who hold positions of associate professors or assistants (table 6.7).

In this example to select the information about readers that hold positions of associate professors or assistants logical operator OR of WHERE proposition is applied: Post = ‘Associate professor’ or Post = ’Assistant’.

Table 6.7

In the example 6.7, after SELECT proposition in the list of columns and after WHERE in search conditions name of the table is recorded through dot before the name of column. Sometimes it is absolutely necessary to indicate the name of the table before the name of the column as there can be found similar columns in different tables (as in our example) and SQL-server must know what column is exactly in process.

Using names of the table for identification of columns is not convenient as it is hungus. Aliases that are indicated through space after the name of the table in the proposition FROM, make the record of SELECT operator more compact:

For example, query that is examined in the example 6.7, becomes much more compact after input of tables aliases:

Application of operator of comparison BETWEEN provides the possibility within search condition to determine that some value must remain in the space between two others. Reserved word NOT inverts the condition.

EXAMPLE 6.10

Count inventory numbers of books that have price within the range from 20 to 60 grivnas inclusive (table 6.8).

Above presented query can be recorded as following:

Many experts consider that verification of the expression being a part of the given range by means of operator BETWEEN is simpler record mode than normal verification that is represented in the last SELECT operator.

Table 6.8

6.10.1. Operator LIKE

Operator LIKE determines the template of lowercase values comparison. It is register-sensitive. If it is necessary to verify the correspondence of column value or lowercase expressions computing result of the template (<value> after proposition LIKE), it is necessary indicate in search condition:

Special symbols «%» and «_» are used in the template. Symbol «%» (percent) is used to indicate any value of any length, and symbol «_» (underlining) is applied to indicate any single symbol. For example:

IssuePlace LIKE 'M%' – – this template means that the first symbol of value can be capital letter «M» only, and all other symbols are of no importance and are not verified;

IssuePlace LIKE ‘M_’ - this template means that values can be only two symbols long, moreover the first symbol of value can be «M» only;

IssuePlace LIKE '%e' - this template determines any sequence at least one symbol long, moreover the last symbol must be symbol «e» only;

IssuePlace LIKE 'M%' - this template determines that any rows that do not begin with symbol «M» are wanted.

If it is necessary row must contain also service digit that is used as substitution symbol, we have to apply «escape»-symbol, putting it before substitution symbol. For example, to check the correspondence of values to ‘15%’ we can use the following search condition:

LIKE '15#%' ESCAPE '#'

EXAMPLE 6.11

To use mechanism of search according to template to enter code, series and number of passport for people who live in Dniepropetrovsk region (table 6.9).

Table 6.9

WARNING! Streaktly speaking last SQL-query supplements final relation with information about people who are represented in IssuePlace attribute with the sequence of symbols that corresponds the sech conditions template (see PasportData). For example, a person with attribute Code = 18, lives in Dniepropetrovsk that is located in Dnipropetrovsk region. However, value of IssuePlace attribute doesn’t correspond to defined template. That highlights the lack of rules for IssuePlace attribute data input.

6.10.2. Operator STARTING

Operator STARTING is register-sensitive. It is used if it is necessary that some symbol column or expression starts with specific consequence of symbols:

EXAMPLE 6.12

Let’s select codes, family names, names and patronymics of readers that start with letter «I» (table 6.10).

Table 6.10

6.10.3. Operator CONTAINING

Operator CONTAINING is not register-sensitive. It is used if it is necessary that some column value or expression contains (from any position) some consequence of symbols:

EXAMPLE 6.13

Let’s select codes, family names, names and patronymics of readers where family names contain sequence of letters «ko» (table 6.11).

Table 6.11

6.10.4. Function UPPER

Function UPPER(<value>) is used to convert letters of character representation (content of the column, expression computing product) into uppercase letters. Function UPPER can be used either in the list of columns of the final data set (after SELECT proposition) or in the search condition of WHERE proposition. It is used in search conditions when letter case must be ignored.

EXAMPLE 6.14

Let’s select codes, family names, names and patronymics of readers whose family names contain sequence of letters «ko» either lowercase or uppercase. Family name is entered with uppercase letters (table 6.12).

Table 6.12

6.10.5. CAST function

CAST function is used when it is necessary to interprete the value of one type as a value of different one. For example, to use numeric as a symbolic row or on the contrary. In such case function is applied:

Function CAST copies value, converting it into indicated data type. Moreover we must not forget about plenty data types that can be used for conversion:

EXAMPLE 6.15

Let’s select passport codes, series and numbers that contain number «84» (table 6.13).

Table 6.13

Such verification is carried out by means of operator IN:

Data set can be formed by means of SELECT operator. Such example will be examined below within the subject of nested sub-queries that return values set.

EXAMPLE 6.16

To use PasportData table, insert passport code, series and number for persons that live in Donetsk or Kiev (table 6.14).

The same as for operator BETWEEN application of key word IN is the most efficient mode of search conditions record, especially if the list of acceptable values is rather big. The same query can be recorded by means of the following query:

Table 6.14

1. What reserved words (propositions) that belong to operator SELECT you know?
2. What operation of relational algebra is carried out by operator SELECT that is the simplest one?
3. What should one do to rename columns of the SELECT operator final data set?
4. How it is possible to delete final data set records that contain identical data in all columns from operator SELECT?
5. What is the concept of operator SELECT sorting mechanism?
6. What search predicates you know?
7. What mutual features and differences have comparison of constant and expression computing product with value in relation attribute?
8. What operations of relational algebra are carried out by comparison predicate application?
9. What is the difference between logical and physical levels of SELECT operator products generation?
10. How can be implemented complex SELECT operator search conditions?
11. What must be indicated before the column name in operator SELECT?
12. How to realize verification of the expression being a part of the given range?
13. What mechanism of verification of the expression correspondence to the given template exists?
14. What mutual features and differences have operators that determine correspondence of the expression value to given template?
15. What is a purpose of service digits that form templates for verification of expressions values correspondence application?
16. How to achieve dropping of lowercase literals register in search conditions?
17. How can be carried out the SELECT operator data types adjustment?
18. How can be carried out verification of value being a part of a set you know?