Main menu

EN | RU | UK

To Home Page

6. Operator SELECT. Part 2

On top
Lecture
Purpose

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.

6.13. Verification of the NULL determinant availability

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

 <value> IS [NOTNULL

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

Sample of codes and names of books that do not contain UDK
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

6.14. Aggregate functions

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

SELECT COUNT(*AS cCount

    FROM BookInventoryNumbers

    WHERE Cost > 25;

Table 6.16

Quantity of books that cost more then 25 grivnas
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

Quantity of different librarians who gave out the books
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

Total value of library books
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

Minimal, maximal and average value of the library books
Mini Maxi Avgi
10.10 74.2 38.27

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

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

Total quantity and value of books that are stored in different librarian funds
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

Logic of aggregate function operation in case of table rows group applicatiuon
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.

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

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

Quantity of books and their total cost for each library fund that contain more than six books
FundCode cCount cSum
1 9 307.95

6.15. Using subqueries in search conditions

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

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

The return dates of books by the reader whose number is a reader's ticket 28
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

Inventory numbers of books that has price above the average and difference between this price and average one.
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

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:

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)

6.16. Operators that are used with subqueries only

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:

[NOTEXISTS(<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

Codes and names of books of student library fund that were used by readers
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

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:

[NOTSINGULAR(<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

Codes and names of books that were given out to readers only once
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

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:

<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

Inventory numbers of all books whose price exceeds price of at least one book from library fund number «1»
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

Inventory numbers of all books with price that exceedes value of any book of fund no.1
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

Inventory number of the sample with average price that exceeds the value of other samples
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.

6.17. Outer join

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

Table «A» that illustrates different types of outer joints
Column Р1 Column Р2 Column Р3
A x 400
B x 200
C y 500
D NULL NULL

Table 6.31

Table «B» that illustrates different types of outer joints
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

Illustration of left outer joint between tables А and B
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

Illustration of right outer joint between tables А and B
Column A.Р1 Column A.Р2 Column B.Р2
A x 1
B x 1
C y 2
NULL NULL 2

6.18. UNION - joining of several SELECT operators outputs

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

Date of passports with series «AA»
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

Data of passports issued in 2000
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

Data of passports with numbers that start with number 4
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

Join data set
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

6.19. Rows concatenation operation

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

Readers data put in one column
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.)

6.20. Work with different databases within one query

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.

6.21. CHECK QUIZ

  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?
Conclusion

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.