- ABOUT PROJECT
- 1. Comparison of different DBMS technologies
- 2. DB designing
- 3. The normalization of relationships during the DB designing
- 4. Data structure physical designing
- 5. Relational algebra
- 6. Operator SELECT. Part 1
- 6. Operator SELECT. Part 2
- 7. Corporate integrity limitation
- 8. Views
- 9. Transactions
- 10. Optimization of database operations
- 11. Database unauthorized access security
- Literature
- APPENDIX A. Logical model of the database "LIBRARY"
- APPENDIX B. Physical model of the database "LIBRARY"
- APPENDIX C. Adding information to the database "LIBRARY"

Lecture

The objective of the chapter is to examine operator SELECТ, that is one of the most important and asked-for SQL operators. Complete format is presented, examples are illustrated.

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

* <value> IS [NOT] NULL*

EXAMPLE 6.17

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

**SELECT** Code, Name

**FROM** Books

**WHERE** UDK **IS NULL**;

Table 6.15

Code | Name |
---|---|

1 | Automation of industrial processes on concentration plant |

2 | Concentration and metallurgy processes automation problem solving |

3 | Asymptotic methods of optimal management |

4 | Optimum automotive systems synthesis |

5 | Stochastic systems optimization methods |

6 | Automotive systems of ore-dressing treatment technological process management |

7 | C/C++ High-level language programming |

8 | Computer networks. Conceptions, technologies, protocols |

9 | Directory of differential equations with 1-st derivatives |

10 | Probability theory and mathematical statistics |

11 | C#. High-level language programming |

12 | Probability theory and mathematical statistics |

13 | Probability theory and mathematical statistics |

14 | Discret-batch methods of simple differential equations integration |

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).

**SELECT** **COUNT**(*****) **AS** cCount

**FROM** BookInventoryNumbers

**WHERE** Cost > 25;

Table 6.16

cCount |
---|

10 |

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:

**COUNT** (**DISTINCT** <expression>)

EXAMPLE 6.19

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

**SELECT** **COUNT**(**DISTINCT** OutLibrarianCode) **AS** cCount

**FROM** BookInventoryNumbers

Table 6.17

cCount |
---|

6 |

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).

**SELECT** **SUM**(Cost) **AS** cSum

**FROM** BookInventoryNumbers

Table 6.18

cCount |
---|

6 |

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).

**SELECT** **MIN**(Cost) **AS** Mini, **MAX**(Cost) **AS** Maxi, **AVG** (Cost) **AS** Avgi

**FROM** BookInventoryNumbers

Table 6.19

Mini | Maxi | Avgi |
---|---|---|

10.10 | 74.2 | 38.27 |

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).

**SELECT** FundCode, **COUNT**(BookCode) **AS** cCount, **SUM**(Cost) **AS** cSum

**FROM** BookInventoryNumbers

**GROUP BY** FundCode

**ORDER BY** FundCode

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

FundCode | cCount | cSum |
---|---|---|

1 | 9 | 307.95 |

2 | 6 | 266.05 |

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

Table 6.21

FundCode | BookCode | Cost | СOUNT(BookCode) | SUM(Cost) | |
---|---|---|---|---|---|

1 | 1 | 15.56 | 9 | 307.95 | |

1 | 2 | 22.33 | |||

1 | 3 | 34.01 | |||

1 | 4 | 12.99 | |||

1 | 9 | 36.05 | |||

1 | 10 | 74.2 | |||

1 | 12 | 36.69 | |||

1 | 13 | 48.13 | |||

1 | 14 | 27.99 | |||

2 | 5 | 56.78 | 6 | 266.05 | |

2 | 6 | 10.10 | |||

2 | 7 | 73.50 | |||

2 | 7 | 45.10 | |||

2 | 8 | 59.25 | |||

2 | 11 | 21.32 |

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*.

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).

**SELECT** FundCode, *COUNT*(BookCode) *AS* cCount, **SUM**(Cost) **AS** cSum

**FROM** BookInventoryNumbers

**GROUP BY** FundCode

**HAVING** **COUNT**(BookCode) > 6

**ORDER BY** FundCode

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

FundCode | cCount | cSum |
---|---|---|

1 | 9 | 307.95 |

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:

- 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;
- lowercase subquery generates values of several table columns but as a single row; it can be used everywhere when lowercase values constructor is applied;
- 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:

- There should be no phrase
*ORDER BY*in subquery, even though it can be present in outer query. - 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. - 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. - 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.

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).

**SELECT** ReturnDate, FactreturnDate

**FROM** BookGiveOutRecord

**WHERE** ReaderCode = (**SELECT** Code

**FROM** Readers

**WHERE** ReaderCardNumber = 28)

Inner operator

**SELECT** Code

**FROM** Readers

**WHERE** ReaderCardNumber = 28)

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:

**SELECT** ReturnDate, FactreturnDate

**FROM** BookGiveOutRecord

**WHERE** ReaderCode = 2

Table 6.23

ReturnDate | FactreturnDate |
---|---|

25-SEP-04 | 24-SEP-04 |

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).

**SELECT** InventoryNumber,

*Cost – ( SELECT AVG(Cost)*

**FROM** BookInventoryNumbers) **AS** Cost_diff

**FROM** BookInventoryNumbers

**WHERE** Cost > (**SELECT AVG**(Cost)

**FROM** BookInventoryNumbers)

Table 6.24

InventoryNumber | Cost_diff |
---|---|

4678532 | 18.51 |

7569832 | 35.23 |

5478956 | 6.83 |

2145876 | 20.98 |

5268933 | 35.93 |

7812639 | 9.86 |

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:

**SELECT** InventoryNumber, Cost – 38.27 **AS** Cost_diff

**FROM** BookInventoryNumbers

**WHERE** Cost > 38.27

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:

**SELECT** ReaderCode, FamilyName, Name, B.Code, InventoryCode

**FROM** Readers R

**WHERE** R.Code **IN** (**SELECT** ReaderCode

**FROM** BookGiveOutRecord B)

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.

**SELECT** ReaderCode

**FROM** BookGiveOutRecord B)

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).

**SELECT** Code, Name

**FROM** Books

**WHERE** Code **IN** (**SELECT** BookCode

**FROM** BookInventoryNumbers

**WHERE** FundCode = (**SELECT** Code

**FROM** BookFunds

**WHERE** Name ='Students') **AND**

**EXISTS** (**SELECT** Code

**FROM** BookGiveOutRecord

**WHERE** InventoryCode = BookInventoryNumbers.Code))

Table 6.25

Code | Name |
---|---|

6 | Automotive systems of ore-dressing treatment technological process management |

7 | C/C++ High-level language programming |

11 | C#. High-level language programming |

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).

**SELECT** B.Code, B.Name

**FROM** Books B

**WHERE** B.Code **IN** (**SELECT** BIN.BookCode

**FROM** BookInventoryNumbers BIN

**WHERE** **SINGULAR** (**SELECT** BG.InventoryCode

**FROM** BookGiveOutRecord BG

**WHERE** BIN.Code = BG.InventoryCode))

Table 6.26

Code | Name |
---|---|

3 | Asymptotic methods of optimal management |

4 | Optimum automotive systems synthesis |

7 | C/C++ High-level language programming |

9 | Directory of differential equations with 1-st derivatives |

14 | Discret-batch methods of simple differential equations integration |

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:

*<value> [ NOT] <comparison operator> {ALL | SOME | ANY} (<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*):

**WHERE** STOLBEZ > **ALL** (**SELECT** POLE **FROM** TABLIZA)

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

**WHERE** STOLBEZ > **SOME** (**SELECT** POLE **FROM** 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).

**SELECT** BookCode, InventoryNumber, Cost

**FROM** BookInventoryNumbers

**WHERE** Cost > **SOME** (**SELECT** Cost

**FROM** BookInventoryNumbers

**WHERE** FundCode = '1'

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

BookCode | InventoryNumber | Cost |
---|---|---|

1 | 4567890 | 15,56 |

2 | 4510000 | 22,33 |

3 | 4532477 | 34,01 |

5 | 4678532 | 56,78 |

7 | 7569832 | 73,50 |

7 | 5478956 | 45,10 |

8 | 2145876 | 59,25 |

9 | 5214786 | 36,05 |

10 | 5268933 | 74,20 |

11 | 7865890 | 21,32 |

12 | 6589321 | 36,69 |

13 | 7812639 | 48,13 |

14 | 7523690 | 27,99 |

EXAMPLE 6.29

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

**SELECT** BookCode, InventoryNumber, Cost

**FROM** BookInventoryNumbers

**WHERE** Cost > **ALL** (**SELECT** Cost

**FROM** BookInventoryNumbers

**WHERE** FundCode = '1'

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

BookCode | InventoryNumber | Cost |
---|---|---|

10 | 5268933 | 74,20 |

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).

**SELECT** BIN1.Bookcode, InventoryNumber, **AVG**(BIN1.Cost) **AS** AvgCost

**FROM** BookInventoryNumbers BIN1

**GROUP BY** BIN1.Bookcode

**HAVING AVG**(Cost) >= **ALL** (**SELECT** **AVG**(BIN2.Cost)

**FROM** BookInventoryNumbers BIN2

**GROUP BY** BIN2.Bookcode)

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

BookCode | InventoryNumber | Cost |
---|---|---|

10 | 5268933 | 74,20 |

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:

**SELECT** {* | <value> [, <value> ...]}

**FROM** <table> <join type> **JOIN** <table> **ON** <search condition>

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

Column Р1 | Column Р2 | Column Р3 |
---|---|---|

A | x | 400 |

B | x | 200 |

C | y | 500 |

D | NULL | NULL |

Table 6.31

Column Р1 | Column Р2 |
---|---|

X | 1 |

Y | 2 |

Z | 2 |

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

**SELECT** A.P1, A.P2, B.P2

**FROM** A **LEFT** **JOIN** B **ON** A.P2 = B.P1

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

Column A.Р1 | Column A.Р2 | Column B.Р2 |
---|---|---|

A | x | 1 |

B | x | 1 |

C | y | 2 |

D | NULL | NULL |

Let’s record operator *SELECT* for the right join:

**SELECT** A.P1, A.P2, B.P2

**FROM** A **RIGHT** **JOIN** B **ON** A.P2 = B.P1

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

Column A.Р1 | Column A.Р2 | Column B.Р2 |
---|---|---|

A | x | 1 |

B | x | 1 |

C | y | 2 |

NULL | NULL | 2 |

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).

**SELECT** *

**FROM** PasportData

**WHERE** Series = 'AA'

Table 6.34

Code | Series | Number | Birthday | Birthplace | Sex | IssuePlace | IssueDate | Note |
---|---|---|---|---|---|---|---|---|

1 | AA | 45003 | 30.05.1930 | Russia, Opochki city | F | Dniepropetrovsk | 12.01.1995 | NULL |

2 | AA | 15700 | 23.02.1930 | Russia, Vladymir city | F | Zhitomir | 16.03.2000 | NULL |

2) Passports issued in 2000 (table 6.35);

**SELECT** *

**FROM** PasportData

**WHERE** IssueDate **BETWEEN** ‘01/01/2000’ **AND** ‘12/31/2000’

Table 6.35

Code | Series | Number | Birthday | Birthplace | Sex | IssuePlace | IssueDate | Note |
---|---|---|---|---|---|---|---|---|

2 | AA | 15700 | 23.02.1930 | Russia, Vladymir city | F | Zhitomir | 16.03.2000 | NULL |

8 | AK | 23490 | 05.01.1961 | Russia, Samara city | F | Dnipropetrovsk | 13.09.2000 | NULL |

20 | AK | 12578 | 11.11.1987 | Donetsk, Kramatorsk | F | Kiev | 26.01.2000 | NULL |

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

**SELECT** *

**FROM** PasportData

**WHERE** Number **LIKE** '4%'

Table 6.36

Code | Series | Number | Birthday | Birthplace | Sex | IssuePlace | IssueDate | Note |
---|---|---|---|---|---|---|---|---|

1 | AA | 45003 | 30.05.1930 | Russia, Opochki city | F | Dnipropetrovsk | 12.01.1995 | NULL |

7 | AZ | 43188 | 13.11.1970 | Dnipropetrovsk region, Dneprodzerzhinsk city | F | Dnipropetrovsk region, Dneprodzerzhinsk city | 15.05.1998 | NULL |

12 | IK | 45190 | 18.07.1983 | Dnipropetrovsk region, Petropavlovka settlement | F | Dnipropetrovsk region, Petropavlovka settlement | 20.09.1999 | NULL |

18 | AG | 45879 | 04.02.1961 | Dniepropetrovsk | F | Dneprodzerzhinsk | 14.03.1980 | NULL |

22 | AY | 45789 | 07.08.1972 | Ugorshchina | F | Ivano-Frankovsk | 03.10.1988 | NULL |

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

**SELECT** *

**FROM** PasportData

**WHERE** Series = 'AA'

**UNION**

**SELECT** *

**FROM** PasportData

**WHERE** IssueDate **BETWEEN** ‘01/01/2000’ **AND** ‘12/31/2000’

**UNION**

**SELECT** *

**FROM** PasportData

**WHERE** Number **LIKE** '4%'

Table 6.37

Code | Series | Number | Birthday | Birthplace | Sex | IssuePlace | IssueDate | Note |
---|---|---|---|---|---|---|---|---|

1 | AA | 45003 | 30.05.1930 | Russia, Opochki city | F | Dniepropetrovsk | 12.01.1995 | NULL |

2 | AA | 15700 | 23.02.1930 | Russia, Vladymir city | F | Zhitomir | 16.03.2000 | NULL |

7 | AZ | 43188 | 13.11.1970 | Dnipropetrovsk region, Dneprodzerzhinsk city | F | Dnipropetrovsk region, Dneprodzerzhinsk city | 15.05.1998 | NULL |

8 | AK | 23490 | 05.01.1961 | Russia, Samara city | F | Dnipropetrovsk | 13.09.2000 | NULL |

12 | IK | 45190 | 18.07.1983 | Dnipropetrovsk region, Petropavlovka settlement | F | Dnipropetrovsk region, Petropavlovka settlement | 20.09.1999 | NULL |

18 | AG | 45879 | 04.02.1961 | Dniepropetrovsk | F | Dneprodzerzhinsk | 14.03.1980 | NULL |

20 | AK | 12578 | 11.11.1987 | Donetsk, Kramatorsk | F | Kiev | 26.01.2000 | NULL |

22 | AY | 45789 | 07.08.1972 | Ugorshchina | F | Ivano-Frankovsk | 03.10.1988 | NULL |

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).

**SELECT** FamilyName ||’ ‘ Name || Patronymic || ‘(‘ || Job || ‘)’

**FROM** Readers

Table 6.38

COLUMN1 |
---|

Ivanov Petr Ivanovich (NMU CN dep.) |

Fedorez Irina Olegovna (NMU, AEC) |

Ilin Ivan Petrovich (NMU, physics dep.) |

Surenko Dmitry Pavlovich (NMU, geophysics dep.) |

Korshunova Natalia Yurievna (NMU geoinformatics dep.) |

Nosenko Oleg Vladimirovitch (NMU, ICC) |

Brusov Vladimir Mikhajlovitch (NMU, geodesy dep.) |

Kozirev Alexey Sergeevich (NMU, criminology dep.) |

Levchenko Julia Pavlovna (NMU, political theory dep.) |

Svetlaya Tatyana Ivanovna (NMU, translation dep.) |

Sheglov Petr Yevgenievich (NMU, power supply dep.) |

Kirilenko Victor Alexandrovich (NMU, electric drive dep.) |

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»):

**SELECT** U.*

**FROM** ":MONITOR:NLS" N, ":DWH:OLAP_UPE" U

**WHERE** U.SC_CODE = N.COD_SCENARIO

**ORDER BY** U.SC_CODE;

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

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

Thanks to the possibility to compute arithmetic expressions, to apply aggregate functions, different types of search conditions, nested subqueries SELECT operator covers mostly all needs of information representation in the form acceptable for the user. Those needs that can not be satisfied by means of one operator only, several operators are carried out sequentally, taking the advantage of the fact that the product of any SELECT operator is a two-dimensional table that can be incoming for the next operator.

© Yaroslav Kuvaiev, 2005—2020.

All rights reserved.

All information placed on this Web site is designed for internal use only. Its reproduction or distribution in any form is prohibited without the written permission from Author.