Aggregate functions of a data composition system.

iPad

Most queries to the database will not require the same level of detail as the SQL queries considered in previous examples.
So, for all the search queries further, it is necessary to find out one or more values that represent information that should be placed in the database:
1) what is the amount of income of all the poor people?
2) what is the largest and smallest foreign income of a local resident?
3) what is the average per capita income of a Zelenograd citizen?

4) what is the average per capita income of middle class residents? 5) how many bourgeois people are there in each apartment? on

mov SQL

Statements of this type can be created using additional aggregate functions and the GROUP BY and HAVING clauses that are used in the SELECT statement. Vikoristannya of aggregate functions To keep track of the information that should be placed in the database, SQL transfer

aggregate functions

The aggregate function takes as an argument a set of data as a whole, and rotates one value, as it sums up the whole set.

For example, the aggregate function AVG() takes a series of numbers as an argument and calculates the average value. To calculate the average per capita income of a citizen of Zelenograd, the following query is required: SELECT 'PER CAPITA INCOME, AVG(SUMD) FROM PERSON

SQL has six aggregate functions that allow you to

different views

pouch information (Fig. 3.16):
SUM() calculates the sum of all values contained in the column;
AVG() calculates the average value of the column;

- MIN() to know the lowest average value, like the one in the table;

- MAX() to find the highest average value, like the one in the counter;

- COUNT() equals the same number of values as in the table;

COUNT(*) specifies the maximum number of rows in the query results table.

The argument of the aggregate function can be a simple statement, as in the front case, or a expression, like the front line, which means the calculation of the average per capita donation:

SELECT AVG(SUMD*0.13)

Small

The aggregate function can be used to calculate the sums of a result table that is drawn from several output tables.

For example, you can calculate the total amount of income received by residents from the village under the name “Scholarship”:

SELECT SUM(MONEY)

FROM PROFIT, HAVE_D

WHERE PROFIT.ID=HAVE_D.ID

AND PROFIT.SOURCE^Scholarship’

The aggregate functions MIN() and MAX() allow you to find out the minimum and maximum values in a table.

In this case, you can replace numerical and row values of either date or hour.

For example, you can mean:

(a) the smallest foreign income withdrawn by residents, and the largest tax that contributes to payment:

SELECT MIN(SUMD), MAX(SUMD*0.13)

(b) dates of birth of the oldest and youngest resident:

SELECT MIN(RDATE), MAX(RDATE)

SELECT MIN(FIO), MAX(FIO)

In the case of aggregate functions, it is necessary to remember that the numerical data is equal to the arithmetic rules, the alignment of dates is obtained sequentially (earlier date values are respected by smaller ones, later ones are aligned), At intervals of an hour it is concluded on the stand of their trifles.

When using the MIN() and MAX() functions with row data, the result of the alignment of two rows is stored in the symbol encoding table, which is being processed.

The aggregate function COUNT() determines the number of values of any type:

(a) how many apartments are there in the 1st microdistrict?

SELECT COUNT(ADR)

WHERE ADR LIKE *%, 1_

(b) how many poor people are trying to earn an income?

SELECT C0UNT(DISTINCT NOM)

Keyword

"DISTINCT" indicates that duplicate values in the column are to be avoided.

The special aggregate function COUNT(*) subtracts the rows of the results table, rather than the data values:

(a) how many apartments are there in the 2nd microdistrict?

WHERE ADR LIKE "%, 2_-%’

(b) how much income did Ivanov Ivan Ivanovich receive?

FROM PERSON, HAVE_D

WHERE FIO = "Ivanov Ivan Ivanovich"

AND PERSON.NOM = HAVE_D.NOM

For example, let’s look at the next question: find out the average per capita income, the amount of total income of residents, as well as the average income per person in hundreds of total income of a resident.

The operator gives the confirmation

SELECT AVG(SUMD), SUM(SUMD), (100*AVG(MONEY/SUMD))

FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID

Without aggregate functions it would look like this:

SELECT SUMD, SUMD, M0NEY/SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID

and turning over one row of results for a skin bag and a specific income.

Aggregate functions vikorista columns of the table of results of this query by extracting a single-row table with sub-results.

For a number of columns that rotate, instead of the name of any column, you can specify an aggregate function.

For example, it can be included in a form in which the meaning of two aggregate functions is implied or understood:

SELECT MAX(SUMD)-MIN(SUMD)

Another aggregate function can be an argument for another aggregate function, then.

the contribution of the aggregate function is blocked.

In addition, in the list of items that are being rotated, it is not possible to immediately select aggregate functions and initial names of items, there are parts in which there is no sense, for example: SELECT FIO, SUM(SUMD) Here the first element of the list instructs the DBMS to create a table that consists of several rows and one row per skin bag.

Another element in the list allows the DBMS to select one result value, which is the sum of the value of the SUMD column.

These two boxes are meant to be one and the same, which will lead to mercy.

It is said not to proceed to the processing of sub-queries and queries from groupings.SQL has added


additional functions
	which allow the calculation of formalized group values.	To consolidate aggregate functions, the forward grouping operation is transferred.	Evaluation
Group 1	Petrov F. I.	Bazi tributes
	Sidorov K. A.	Bazi tributes
	Mironov A.V.	Bazi tributes
	Stepanova K.Ye.	Bazi tributes
	Krilova T.Z.	Bazi tributes
	Volodimirov V. A.	Bazi tributes
Group 2	Sidorov K. A.	Information theory
	Stepanova K.Ye.	Information theory
	Krilova T.Z.	Information theory
	Mironov A.V.	Information theory	Null
Group 3	Trofimov P. A.	Measures and telecommunications
	Ivanova E. A.	Measures and telecommunications
	Utkina N.V.	Measures and telecommunications
Group 4	Volodimirov V. A.	English language
	Trofimov P. A.	English language
	Ivanova E. A.	English language
	Petrov F. I.	English language	i

Aggregate functions work similarly to field names in a SELECT statement, with one caveat: they don't take field names as an argument.

The SUM and AVG functions can also be used to manipulate numeric fields.

The COUNT, MAX and MIN functions can be used to manipulate both numeric and character fields.

When vicinating with character fields MAX and MIN, they are translated into the equivalent ASCII code and processed in alphabetical order.

DBMS systems allow the use of a variety of input aggregates, in addition to extensions according to the ANSI standard with all the inheritances that are added.

For example, it is possible to count the number of students who have experienced skin discipline.

For this purpose, you will need to search for groupings in the “Discipline” field and enter as a result the name of the discipline and the number of rows the group has for this discipline.

The use of the * symbol as an argument to the COUNT function means the subtraction of all rows of the group.

SELECT R1. Discipline.

COUNT(*)

GROUP BY R1 Discipline

Result:


If we want to protect the number of those who are subject to any kind of discipline, then we need to exclude unimportant values from the relationship before the groupings.	Information theory	Null

Whose answer will be written like this:

SELECT R1. Discipline.

COUNT(*)

FROM R1 WHERE R1.

IS NOT NULL rating

GROUP BY Rl.Discipline

COUNT(DISTINCT R1.Score)

WHERE R1.Evaluation IS NOT NULL

COUNT(*)

DBMS systems allow the use of a variety of input aggregates, in addition to extensions according to the ANSI standard with all the inheritances that are added.

As a result, you can turn on grouping field values and a number of aggregate functions, and you can select a number of fields for grouping.

In this case, groups are created according to a set of tasks and grouping fields.

Operations with aggregate functions may be frozen until the output tables are cleared.

For example, we supply nutrition: determine for the skin group and skin discipline the number of those who will successfully complete the skin group and the average score of the discipline.

SELECT R2.Group.

R1. Discipline.

DBMS systems allow the use of a variety of input aggregates, in addition to extensions according to the ANSI standard with all the inheritances that are added.

COUNT(*), AVP(Evaluation)

WHERE Rl.PIB = R2.PIB AND Rl.Rating IS NOT NULL AND

Rl.Rating> 2

GROUP BY R2.Group.

Operations with aggregate functions may be frozen until the output tables are cleared.

Rl.Discipline

We cannot substitute aggregate functions in the WHERE clause, since predicates are evaluated in terms of a single row, and aggregate functions in terms of groups of rows.

Proposition GROUP

BY allows you to define a submultiple value in a particular field in terms of another field and set the function to the aggregate to the submultiple.

This makes it possible to combine fields and aggregate functions in a single SELECT statement. ;

Aggregate functions can be constrained both in the expression of the result of the SELECT row and in the determined processing of the formed HAVING groups.

And here the skin aggregate function is calculated by the visible skin group.

The values removed when calculating aggregate functions can be used to obtain consistent results or to better select groups.

We will ask you to display groups in which more than one double was removed from one discipline:

SELECT R2.Group

GROUP BY defines the aggregate function independently of the skin group, indicated by the additional value of the Branch field.

The group is formed in rows with the same values of the File field and the SUM function is set aside for each such group, so that the total surplus on the racks is filled with edge for the skin file.

The values of the field that is set to GROUP BY may, after the values, be the same as the value of the group displayed, as a result of the operation of the aggregate function.

Therefore, we can combine the aggregate and field in one statement.

You can also use GROUP BY with multiple fields.

It is acceptable that we would like to increase the sum of the surplus on the market, which would exceed $5,000.

To increase your total surplus over $5000, you need to use the HAVING proposition.

The HAVING clause specifies the criteria to be used to remove song groups from the top, just as the WHERE clause does the same for individual rows.

The correct command would be:

SELECT File, SUM(Zalishok)

GROUP BY Philia

HAVING SUM(Suspended) > 5000;

The arguments of the HAVING statement are ordered by the same rules as those of the SELECT statement, which are followed by GROUP BY.

The stench is to blame for the mother's one significant group of eliminations.

The attacking team will be defended:

SELECT File.SUM(Balance) FROM F GROUP BY Philia HAVING DateVidCrittya = 27/12/1999;

The Date Date field cannot be added to the HAVING clause, so you can have more than one value per display group.

To avoid such a situation, the HAVING clause can be applied only to the aggregates and fields selected GROUP BY.

And the correct way to do fortune-telling is to ask:

You can also use GROUP BY with multiple fields.

SELECT File,SUM(Zalishok)

Therefore, in arithmetic expressions of predicates that are included in the mental selection of the HAVING section, you can select only specifications of clauses designated as grouping clauses in the GROUP BY section.

Other columns can be specified in the middle of the specification of the aggregate functions COUNT, SUM, AVG, MIN and MAX, which calculate the aggregate value for the entire group of rows in this section.

In the same way, deal with the sub-queries that are included in the brain selection predicate in the HAVING section: if the sub-query is used to determine the characteristic of the flow group, it can be specified by the way of sending the grouping station. The result of the search in the HAVING section is a grouped table to contain only a group of rows for which the calculation result is TRUE.

Zokrema, since the HAVING section is present in the table virus, which does not interfere with GROUP BY, the result of its vacancy will be either an empty table, or the result of vacancy of the front sections of the table virus, which is seen as one group without group boundaries Nya.

Investments

run SQL

Now let’s go back to the “Session” database and take a look at its application for inserting queries.

For additional SQL help, you can put it all together.

The call of the internal command generates values that are verified by the predicate of the external query (propositions WHERE and HAVING), the original one, which is true.

Together with the sub-charge, you can use the predicate EXISTS, which turns the truth, since the sub-charge is not empty.

Combined with other operator selection options, such as grouping, it will work harder to achieve the desired result.

In the FROM part of the SELECT statement, it is permissible to add synonyms to table names, since when forming a query we need more than one instance of the same relation.

Synonyms are given in addition to the keyword AS, which can be omitted.

So the FROM part can look like this:

FROM Rl AS A, Rl AS FROM Rl A. Rl U: The two expressions are equivalent and are considered as congestion of the SELECT statement to two instances of table R1.

The list of those who may not be added to the database, but have not yet been added.

SELESTFIO

WHERE R2.Fpynna=R3.Group AND Discipline = "DB" AND NOT EXISTS

(SELECT PIB FROM Rl WHERE PIB=a.PIB AND Discipline = "DB")

The predicate EXISTS (SubQuery) is valid if the subQuery is not empty, so you want to place one tuple, otherwise the predicate EXISTS is folded.

The predicate NOT EXISTS back is only valid if it feeds an empty SubQuery.

Note that NOT EXISTS with an embedded comma allows you to avoid the operation of different lines.

For example, the formula followed by the word “everything” could be written as if it were a subordinate list.

Let's take a look at the butt of the base, which models the production of several parts by four postal parts, it is represented by one of the SP “Postal parts-details” with a diagram

SP (Part_number. Part_number) P (Part_number. Name)

The axis is how the answer to the question is formulated: “Know the postmasters who will supply all the details.”

SELECT DISTINCT RESIDENT_NUMBER FROM SP SP1 WHERE NOT EXISTS

(SELECT part_number

FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SP2.postal number=SP1.postal number AND

sp2.part_number = P.part_number)):

In fact, we reformulated this question as follows: “To know the postmasters who do not care about the details, so that the stink would not be knocked.”

The following indicates that this power supply can be implemented through powered aggregate functions:

SELECT DISTINCT Postal number

GROUP BY Postal number

HAVING CounKDISTINCT part_number) =

(SELECT Count(part_number)

In the SQL92 standard, the alignment operators are expanded to a large number of alignments with the following keywords: ANY and ALL.

This expansion occurs when the value of the song column is equal to the data column, which is rotated by the enclosed comma.

The keyword ANY, placed in any predicate, means that the predicate will be active, as if for one value of the subscript the predicate would be active.

The ALL keyword ensures that the equalization predicate is true when the rows are equalized.

For example, we know students who have put everything into a grade of no less than “good.”

We work with the same “Session” base, but add to it one more relation R4, which characterizes the delivery of laboratory work throughout the semester:

R 1 = (PIB, Discipline, Assessment);

Select students whose academic grade is at least equal to or less than one grade in their building laboratory robots for this discipline:

Select R1.Full name

View R1 Where R1.Rating>= ANY (Select R4.Rating

Where Rl.Discipline = R4.

Discipline AND R1.Name = R4.Name)

External SQL communications

The SQL2 standard has expanded the concept of mental communication. In the SQL1 standard, when the inputs were combined, the inputs that were specified in the WHERE clause of the SELECT statement were used, and as a result, only the tuples of output inputs for which This is where our minds came into play and our actions. However, in reality, it is often necessary to combine tables in such a way that the result loses all the rows from the first table, and instead of the rows of another table, for which there is no mental connection, the result loses unimportant values.

Otherwise, all rows from the right (other) table are included, and all parts of the rows from the first table are supplemented with unimportant values.<список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1>

Such associations were called external in contrast to the associations established by the SQL1 standard, which were called internal.

[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединениям:: =

<имя_таблицы_1>U<имя_таблицы_2>

<выражение перекрестного объединениям: = <имя_таблицы_1>Zagalny Vipadka<имя_таблицы_2>

<выражение запроса на объединением:=

<имя_таблицы_1>The syntax of the FROM part in the SQL2 standard looks like this:<имя_таблицы_2>

<выражение объединениям:= <имя_таблицы_1>FROM

[Name of synonym for table_1] [ ... ]<имя_таблицы_2>

NATURAL (INNER | FULL | LEFT | RIGHT ) JOIN

CROSS JOIN

Let's take a look at the application of external information.

Let’s turn again to the “Session” database.

We are making a decision, in which all grades stand, rejected by all students from previous studies, as they are too small to add up.

DBMS systems allow the use of a variety of input aggregates, in addition to extensions according to the ANSI standard with all the inheritances that are added.


which allow the calculation of formalized group values.	To consolidate aggregate functions, the forward grouping operation is transferred.	Evaluation
Petrov F. I.	Bazi tributes
Sidorov K. A.	Bazi tributes	4
If a student is not a good student, then instead of a grade he has an unimportant value.	Bazi tributes
Stepanova K.Ye.	Bazi tributes
Krilova T.Z.	Bazi tributes
Volodimirov V. A.	Bazi tributes
Petrov F. I.	Information theory	Null
Sidorov K. A.	Information theory
Mironov A.V.	Information theory	Null
Stepanova K.Ye.	Information theory
Krilova T.Z.	Information theory
Volodimirov V. A.	Information theory	Null
Petrov F. I.	English language
Sidorov K. A.	English language	Null
Mironov A.V.	English language	Null
Stepanova K.Ye.	English language	Null
Krilova T.Z.	English language	Null
Volodimirov V. A.	English language
Trofimov P. A.	Measures and telecommunications
Ivanova E. A.	Measures and telecommunications

For this purpose we connect the internal internal table R2 and R3 behind the Group attribute, and the opposite relation is connected to the left external natural data with table R1, vikorista PIB and Disc plіna.

In this case, the standard allows the bracket structure to be used, so that the result of the concatenation can be one of the arguments in the FROM part of the SELECT statement.

SELECT Rl.PIB, R1.Discipline.

Rl.Evaluation

FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN Rl USING (PIB. Discipline)

Mironov L.V.
Let's take a look at another example, for which we'll take the "Library" database.
It consists of three entries, the names of the attributes here are typed in Latin letters, which is necessary in most commercial DBMSs.
BOOKS(ISBN, TITL. AUTOR. COAUTOR. YEARJZD, PAGES)
READER(NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)
EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)

Here the BOOKS table describes all the books that the library has, and it has the following attributes:

ISBN – unique cipher of the book;
TITL – book title;
AUTOR – nickname of the author;
COAUTOR – nickname of the author;
YEARIZD – r_k vidannya;
PAGES – number of pages.

The READER table saves information about all library readers and contains the following attributes:

NUM_READER – unique number of the reading receipt;
NAME_READER - the reader's nickname and initials;
ADRESS – reader addresses;
HOOM_PHONE – home phone number;
WORK_PHONE – work phone number;

The overflow of the skin reader's books is significant;

If the reader does not have books, then the copy number of the book is NULL.

To complete this search, we need to use the left-hand side, so we take all the rows from the READER table and combine them with the rows from the EXEMPLARE table, since there is no row with a similar one in the other table number of the reader's receipt, then in the row of the resulting relationship the EXEMPLARE attribute .INV will have an unsigned value of NULL:

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER

The operation of external unification, as we already guessed, can be used to form the elements of the FROM sentence, so let us assume, for example, that the next text is written:

FROM (BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN (READER NATURAL JOIN EXEMPLARE)

In case of books whose copies are not in the hands of the readers, the meaning of the number of the reader's receipt and the date of receipt and turning of the book will be unimportant. Reinterpreted as interpreted by the SQL2 standard, it is an operation of an extended Cartesian creation, which is an operation that joins two tables when a row in the first table is joined with a row in another table. Operation

I'll ask for lunch

is equivalent to the multiplicity theoretic operation in algebra.

In this way, the equivalence of the output terminal circuits is preserved.

Ask for the following scheme:

SELECT - asked

UNION SELECT - asked

All you need to know is that the operation is unified, there is nothing to blame, place viruses, and calculate the fields."!}!}

For example, you need to display a list of readers who are holding the book “Idiot” or the book “Evil and Punishment” in their hands.

The Yak axis is written:

SELECT READER.

NAME_READER

FROM READER, EXEMPLARE.BOOKS"!}!}

BOOKS.TITLE = "Idiot

SELECT READER.NAME_READER

FROM READER, EXEMPLARE, BOOKS

SELECT - asked

WHERE EXEMPLARE.NUM_READER=READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

NAME_READER

BOOKS.TITLE = "Wrongful Punishment"!}!}

If you need to save all the rows from the output columns, you need to use the ALL keyword in the merge operation.

I'll ask for lunch

If you save duplicate tuples, the connection scheme will look like this:

However, the same result can be obtained by simply changing the WHERE clause of the first part of the output query, connecting local minds with a logical operation or turning off duplicate tuples.

SELECT DISTINCT READER.NAME_READER

SELECT READER.

NAME_READER

All you need to know is that the operation is unified, there is nothing to blame, place viruses, and calculate the fields." OR!}!}

FROM READER." OR!}!}

BOOKS.TITLE = "Wrongful Punishment"!}!}

EXEMPLARE.BOOKS

BOOKS.TITLE = "Mischief and punishment

The use of output queries in the UNION operation is not guilty of interfering with the ordering clause of the result of ORDER BY, but the result of the union can be an ordering, for which the ORDER BY clause from the designated list of ordering clauses is written to If the text of the remaining output SELECT statement. Enter - SQL (Structured Query Language) -

Structured Language Zapitiv

The standard language is for queries to work with relational databases.

The first international standard for Movi SQL was adopted in 1989. (hereinafter referred to as SQL/89 or SQL1).

Sometimes the SQL1 standard is also called the ANSI/ISO standard, and it is important that the majority of DBMSs available on the market support this standard throughout.

For example, 1992 r. A new international language standard, SQL (which is now called SQL/92 and SQL2), was adopted.

And there is no need to reduce shortcomings, but at the same time it is significantly more precise and increased, lower than SQL/89.

Currently, most DBMS vendors are making changes to their products so that they are more satisfied with the SQL2 standard.

The remaining SQL language standard was released in 1996. Vin names SQL3.	SQL cannot be fully integrated into traditional programming: it is impossible to use traditional operators for controlling the progress of programs, type description operators, and much more; it is impossible to use a set of standard access operators to the data, as in the database.	SQL statements are built into basic language programming, which may be standard language like C++, PL, COBOL, etc.
In addition, SQL statements can be executed directly in interactive mode.	1. SQL structure.	SQL contains the following sections:
1. Data definition operators DDL (Data definition language).	Operator	Sens
Diya	CREATE TABLE	Create a table
Create a new table in the database	DROP TABLE	Vidality table
Views the table from the database	ALTER TABLE	Changes the structure or replaces a virtual table
DROP VIEW	Vidality Vistavu	Displays the description of the virtual table.
Please register the table itself, because	She really doesn’t sleep.	CREATE INDEX
Create index	Creates a special physical structure, called an index, which ensures faster access to data	DROP INDEX
Delete index	You can see the created structure
CREATE SYNONYM	Create a synonym

2. DROP SYNONYM

3. Visibility synonym

4. Data Manipulation Language (DML) Data Manipulation Operators

5. Language of queries Data Query Language (DQL)

Setting up transactions (DCL)

Data administration details (DDL)

Programmatic SQL

2. Tipi tributes

SQL/89 supports the following data types: CHARACTER, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION.

These types of data are classified into the types of series of symbols, exact numbers and approximate numbers.

The SQL92 standard has the following data types:

VARCHAR(n) – rows of symbols of the exchange dove

NCHAR(N) – rows of localized symbols of permanent dowry

NCHAR VARYING(n) - rows of localized symbols of the changeable day

BIT(n) - row of bets of a steady dowry

BIT VARYING(n) - a row of bits of the changeover

3. DATE calendar date

TIMESTAMP(accuracy) date and hour

INTERVAL hour interval

SELECT statement

Select is a single search operator that replaces all operations of relational algebra.

The syntactic diagram of the SELECT statement is shown in Fig. 1

Here the keyword ALL means that the resulting set of rows includes all the rows that satisfy the question.

The keyword DISTINCT means that the result set is included in the same row.

Duplicate rows in the result are not included until set.

If the same keyword is not present, this situation is interpreted as the presence of the ALL keyword.

The * symbol means that all columns from the output table are included before the result set.

The FROM part indicates the transfer of output inputs (table) to the power supply.

The WHERE part specifies the choice of terms for the result or the combination of tuples of the output tables.

The GROUP BY part specifies a list of grouping fields. { =, <>, >,<,>=,<=,}. The HAVING part specifies predicates that are applied to the skin group.

predicate IN - enter before the multiplicity / do not enter before the multiplier.

The predicate IN or NOT IN can also be modified to equalize the expression that is checked with the subquery, in which case the syntactic diagram is shown in Fig.

The IN predicate is valid if the value of the attribute specified in the expression for the stream tuple is avoided, either with one of the impersonal values removed as a result of the connection of the subcontinent or the value that fits in the list.

І back, the NOT IN predicate is only valid if the value of the specified attribute in the stream tuple does not coincide with the same value as the value specified by the input subquery or the specified list of values.

predicate LIKE - includes (similar)

The template can be mixed with symbols _ armchair to assign any symbol;<шаблон>.

% symbol symbol – for designating any sufficient sequence of symbols.<шаблон>.

The LIKE predicate is true if the value of the attribute specified by the work items in the stream tuple includes jobs

Predicate NOT LIKE – valid if the value of the attribute in the flow tuple does not include tasks

· Predicate NULL – invisible, unsigned

The syntactic diagram of the predicate is shown in Fig.

7. In any case, all the predicates looked at earlier may be used. Having now learned about groupings, let’s take a closer look at the first three rows of the SELECT statement: SELECT - a keyword that informs the DBMS that this command is written. All entries begin with this word and a leading space. * It can be followed by a selection method - with deleted duplicates (

Otherwise, all rows from the right (other) table are included, and all parts of the rows from the first table are supplemented with unimportant values.- the key word is how it may be served to the skin.

After the FROM keyword there follows one or a number of spaces and a list of output tables that are used in the query.

· The table names are reinforced one by one. Tables can be assigned alias names, which makes it useful for this operation to join the table itself, or to access from an embedded sub-query before stream recording of an external are given).

An alias is the current name of the table, which is only used in this entry and will not be stagnated in the future.

The alias is added to the main name of the receiving table with one blank.)

The syntactic diagram of the FROM part is shown in Fig.

All parts of the SELECT statement are non-binding.

· WHERE

- a keyword followed by a predicate-mind, which means those records that can be used in the result of a set of data in a query. Let's take a look at the database that models the issue of a session in any initial deposit.
Let it be made up of three containers, , .

· Let us take into account that they are presented in tables R1, R2 and R3 clearly.

R1=(PIB, Discipline, Assessment
R2=(PIB, Group)
R3=(Group, Discipline) To consolidate aggregate functions, the forward grouping operation is transferred. Let's look at some applications of the SELECT statement. Evaluation = 5

· List of all successful groups (without repetition) that you can go through and try

SELECT DISTINCT Groupie
FROM R3
R3=(Group, Discipline) List of students who have been registered in the database as “vidminno” = SELECT PIB

FROM R1

· WHERE

SELECT DISTINCT = "DB" AND
A list of all students who need to submit, together with the name of the discipline. SELECT
R3=(Group, Discipline) PIB, Discipline FROM R2, R3
R1.Group <> R2.Group FROM R2, R3
Here the WHERE part means the connection between R1 and R2.<= 2 FROM R2, R3 For all the minds combined in the WHERE clause, the result will be equivalent to an extended Cartesian creation and, in which case, each student would be assigned all the disciplines from the R2 relationship, and not those that are responsible for putting together his group.<= 2

List of rogues, who are a handful of two

In these applications, it is clearly visible that the logic of the selection operator (Cartesian selection-projection) does not follow the order of the description in the data (first the list of fields for the projection, then the list of tables for the Cartesian creation, then the dnannya).

On the right is that SQL has been broken up from the beginning to be frozen in the end koristuvach, and it has been pushed closer to natural language, and not to algorithmic language.

SELECT DISTINCT Groupie

R3=(Group, Discipline) Evaluation Of course, as the English language is natural, as is the international language, it is widely used in computing technology and programming.

For these reasons, SQL is starting to attract the attention of the cob and the development of professional programmers who are willing to talk to the machine itself on algorithmic language.

Therefore, from the looks of correctness, we would like to draw a diagram of the database “Session” of taxes from the view

R1 = (St_name, Discipline, Mark)

R2=(St_name,N_group)

R3 = (N_group, Discipline)

And finally, change all your entries.

Definition of aggregate functions and input inputs from the operator of choice

Writers can calculate the same group values of fields as the same as the values of a single field.

Try for additional aggregate functions.

Aggregate functions rotate individual values of all groups in the table.

List of these functions:
R2=(PIB, Group)
Aggregate functions work similarly to field names in a SELECT statement, with one caveat: they don't take field names as an argument.

The SUM and AVG functions can also be used to manipulate numeric fields.

With the COUNT, MAX and MIN functions, both numeric and character fields can be modified. 7. When dealing with character fields, MAX and MIN translate them into an ASCII equivalent and arrange them in alphabetical order. DBMS systems allow the use of a variety of input aggregates, in addition to extensions according to the ANSI standard with all the inheritances that are added. Returning again to the “Session” database (tables R1, R2, R3), we find the number of successfully completed trials:

SELECT COUNT(*)

For example, this will be written in the future:

Select A

Group By B

Honestly, let's figure it out. What do we want to know? We are trying to inform you about the significance of the situation A from the table T, and in this case grouping according to another principle, that is,

Art.

Consistently grouped - this means that we select all rows with the same values of column B into one group and then, unsurprisingly, we display the values of column A, but in one group there may be no meaning of different values Stovptsya A. So how can we deduce the meanings?)

Neither we nor the computer understood this.

Another element in the list allows the DBMS to select one result value, which is the sum of the value of the SUMD column.

He himself is encouraged to remove such a statement and declares that we can do a syntactic correction.

Let's go back to our database "Session", and additionally a few more attributes.

For example, it is possible to count the number of students who have experienced skin discipline.

For this requirement, search for groupings in the “Discipline” field and enter as a result the name of the discipline and the number of rows in the group with this discipline.

DBMS systems allow the use of a variety of input aggregates, in addition to extensions according to the ANSI standard with all the inheritances that are added.

The use of the * symbol as an argument to the COUNT function means the subtraction of all rows of the group.

SELECT R1.Discipline, COUNT(*)FROM R1GROUP BY R1.Discipline

butt. Select the list of disciplines that include at least 5 subjects:

Investments

SELECT R1.DisciplineFROM R1GROUP BY R1.DisciplineHAVING COUNT(*) >= 5 Result: Here the HAVING statement selects groups that satisfy the given mind.

Deposits

Development D (Details)

Vіdnoshennya PD (Postachannya)<

1. Select the list of post-owners whose status is lower than the maximum status in the table of post-owners (alignment with sub-power):

WHERE P.STATYS(SELECT MAX(P.STATUS) Respect.

WHERE P.STATYS

. Because the P.STATUS field is equal to the result of the subquery, then the subquery can be filled with formulas so as to rotate the table that is formed
exactly one row and one column

Viconati once

investments will be subject to the maximum value of the status.

Scroll the table of postmasters P, each time equaling the status of the postman with the result of the subquery, and select those rows in which the status is less than the maximum.

WHERE P.STATYS 2. Vykoristannya predicate

WHERE P.STATYS IN

. Because(SELECT DISTINCT PD.PNUM
WHERE PD.DNUM = 2);

. For which type of investment you can rotate the table to place a number of rows. .

The result of the next step will be equivalent to the result of the next sequence of actions:

WHERE P.STATYS IN

deposits will ask and contain a list of postal numbers that will receive item number 2. Scan the table of postmasters P, checking immediately to see which postman's number appears as a result of the query. 3. Vykoristannya predicate
EXISTS

WHERE P.STATYS. Select the list of recipients who will receive item number 2: (PD.PNUM = P.PNUM AND ).

WHERE P.STATYS An external message can accept different values for the candidate skin row that is evaluated for an additional subquery, which can then be calculated again for the skin row that is selected in the main query. Such subqueries are characteristic of the EXISTS predicate, but may also be substituted in other subqueries.. You can ask what to drink, what to drink, what to drink, what to drink, what to drink more, what to drink with non-correct drinks. It’s not right, because

those who are koristuvach, having formulated it, doesn't mean How will this be washed down?

SQL language is not procedural, but declarative.

The result of the next step will be equivalent to the result of the next sequence of actions:

WHERE P.STATYS This means that the writer, who formulates the question, simply describes,

What is the result?

, and how this result will be rejected - the DBMS itself confirms this.

SQL language is not procedural, but declarative.

4. Vykoristannya predicate

NOT EXISTS

WHERE P.STATYS. Select the list of postal recipients, if you do not post item number 2:.

WHERE NOT EXISTS

Just like in the previous example, here the vikorist is encouraged to drink.

In general, the syntax of the FROM part in the SQL2 standard looks like this:

Otherwise, all rows from the right (other) table are included, and all parts of the rows from the first table are supplemented with unimportant values.<список исходных таблиц>< выражение естественного соединения >< выражение соединения >< выражение перекрестного соединения >< выражение запроса на объединение ><список исходных таблиц>::= <имя_таблицы_1>[ synonym for table_1] [ …] [,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]<выражение естественного соединения>:: =<имя_таблицы_1>NATURAL (INNER | FULL LEFT | RIGHT ) JOIN<имя_таблицы_2><выражение перекрестного соединения>:: = <имя_таблицы_1>Zagalny Vipadka<имя_таблицы_2><выражение запроса на объединение>::=<имя_таблицы_1>The syntax of the FROM part in the SQL2 standard looks like this:<имя_таблицы_2><выражение соединения>::= <имя_таблицы_1>( INNERFULL | LEFT | RIGHT ) JOIN ( ON mind | )<имя_таблицы_2>

For these values, INNER means internal (natural) connection, LEFT means left connection, so that the result includes all rows of table 1, and parts of the resulting tuples, for which there were no corresponding values in table 2, NULL values (unvalued) .

The keyword RIGHT means the right of the outer connection, and on the basis of the left connection, all rows of table 2 are included as a result, and the parts that are present in table 1 are added insignificant values.

The keyword FULL means external connection: both left and right.

When the external connections are added, both the right and left external connections are added together and, as a result, all rows in table 1 are included, supplemented by unimportant values, and all rows in table 2 are also added and unimportant values.

DBMS systems allow the use of a variety of input aggregates, in addition to extensions according to the ANSI standard with all the inheritances that are added.

which allow the calculation of formalized group values.	To consolidate aggregate functions, the forward grouping operation is transferred.	Evaluation
Petrov F. I.	Bazi tributes
Sidorov K. A.	Bazi tributes
Mironov A.V.	Bazi tributes
Stepanova K.Ye.	Bazi tributes
The OUTER keyword means outer, and if the keywords FULL, LEFT, RIGHT are specified, the connection is always respected by outer.	Bazi tributes
Volodimirov V. A.	Bazi tributes
Petrov F. I.	Information theory	Null
Sidorov K. A.	Information theory
Mironov A.V.	Information theory	Null
Stepanova K.Ye.	Information theory
The OUTER keyword means outer, and if the keywords FULL, LEFT, RIGHT are specified, the connection is always respected by outer.	Information theory