Often when retrieving data, it is necessary to combine information from several related tables. This can be done using nested queries, or using a join using SQL.

Nested Queries

For our example, let's say that we needed to find out the names of nodes that visited the site www.dom2.ru. The required information can be obtained by request:

SELECT hst_name FROM hosts WHERE hst_pcode IN (SELECT vis_hstcode FROM visits, sites WHERE (sit_pcode = vis_sitcode) AND (sit_name LIKE "www.dom2.ru"));

Let's take a closer look at this request. The first SELECT statement is needed to select node names. To select the names we require, the request contains a WHERE section, in which primary key The “Nodes” table (hst_pcode) is checked for membership in a set (IN operator). Apparently, the set to check for membership should be returned by the second SELECT statement located in parentheses. Let's look at it separately:

SELECT vis_hstcode FROM visits, sites WHERE (sit_pcode = vis_sitcode) AND (sit_name LIKE "www.dom2.ru")

For the contents of the tables in our example, the subquery will return the following set of values

Connecting using SQL

As mentioned above, one way to retrieve data from multiple tables is to join the tables using SQL. The main purpose of such a join is to create a new relation that will contain data from two or more original relations.

Inner join

Let's look at an example:

SELECT hst_name, sit_name, vis_timestamp FROM hosts, visits, sites WHERE (hst_pcode = vis_hstcode) AND (vis_sitcode = sit_pcode)

This request will return the following data

hst_name	sit_name	vis_timestamp
ws1	www.dom2.ru	2012-08-01 07:59:58.209028
ws1	www.vkontakte.ru	2012-08-01 08:00:10.315083
1-1	www.vkontakte.ru	2012-08-01 08:00:20.025087
1-2	www.opennet.ru	2012-08-01 08:00:26.260159

In this example, from three tables (hosts, visits, sites), one field is selected and created new table, which will collect the names of hosts, visited sites and the time of visits. The presentation of the data being joined is governed by the conditions in the WHERE clause. You can see that there are two conditions that connect the three tables. Since the table of visits (visits) contains their identifiers instead of the host name and site name, when connecting the tables we add a condition to link the data by identifiers and then everything will fall into place. If for some reason, contrary to referential integrity, there are records in the visits table with the identifier of a non-existent node or site, they will not appear in the result set of the query in this example.

The above example is a little simplified and due to a little simplification the clarity is lost. A more visual form of a query that joins several tables and returns the same data set would look like

SELECT hst_name, sit_name, vis_timestamp FROM hosts JOIN visits ON (hst_pcode = vis_hstcode) JOIN sites ON (vis_sitcode = sit_pcode);

The request contains two JOIN… ON operators. Since "Join" can be translated as "connection" or "union", this example is more eloquent. If you try to translate the text of an SQL query into Russian, you will get something like

SELECT (fields) hst_name, sit_name, vis_timestamp FROM (table) hosts JOINING (with table) visits BY (condition) (hst_pcode = vis_hstcode) JOINING (with table) sites BY (condition) (vis_sitcode = sit_pcode);

Russian words in parentheses have been added to make the query easier to understand. You can use any of the above methods for writing queries.

Outer join

The methods used above to join tables are called inner join(inner join). This connection method has disadvantages. For example, if we had no visits to one of the sites, or one of the nodes did not make a single visit, then the site or node will not be present in the resulting data set. In the example above, you can see that the site www.yandex.ru is missing from the data, as well as node 1-3. Sometimes this is undesirable and in such cases they use outer join(outer join). The outer join can be left(left join) and right(right join). The join side (left or right) corresponds to the table from which the entire data will be selected. Thus, when using a LEFT JOIN, the data from the table to the left of the JOIN operator will be selected in its entirety. Let's back this up with an example. Let's say we need to select ALL nodes and the visits associated with them. This can be done by request

SELECT hst_name, vis_timestamp FROM hosts LEFT JOIN visits ON (hst_pcode = vis_hstcode);

Pay attention to the data that will be returned in response to the request

hst_name	vis_timestamp
ws1	2012-08-01 07:59:58.209028
ws1	2012-08-01 08:00:10.315083
1-1	2012-08-01 08:00:20.025087
1-2	2012-08-01 08:00:26.260159
1-3

It can be seen that node 1-3 does not correspond to a single visit, but it is still in the list. RIGHT JOIN works in a similar way. A query that will return the same set of data can be written using a RIGHT JOIN:

SELECT hst_name, vis_timestamp FROM visits RIGHT JOIN hosts ON (hst_pcode = vis_hstcode);

In this case, you need to change the LEFT JOIN to a RIGHT JOIN and swap the visits and hosts tables in the query.

Using UNION

Sometimes you need to get two lists of records from tables as one. For this purpose it can be used keyword UNION, which allows you to combine the result sets of two queries into one data set. Let's say we need to get a list that contains network nodes and site names. The tables are different, and therefore the queries will be different. How to combine everything into one data set? Easy, but there are certain requirements for such “gluing” of queries:

§ requests must contain the same number of fields;

§ The data types of the fields of the merged queries must also match.

For the rest, using UNION is not complicated. For example, to get a list of host names and site names as one set of data, we would run the following query:

SELECT hst_name AS name FROM hosts UNIONSELECT sit_name AS name FROM sites;

This approach may cause problems with sorting records. To ensure that the list of sites comes after the list of nodes, you can deliberately add an integer field to indicate the number that will participate in the sorting. For example

SELECT 1 AS level, hst_name AS name FROM hosts UNIONSELECT 2 AS level, sit_name AS name FROM sitesORDER BY level, name;

EXISTS and NOT EXISTS conditions

Sometimes it is necessary to select records from a table that match (or do not match) records in other tables. Let's say we need a list of sites that have not been visited. You can get such a list by requesting

SELECT sit_name FROM sites WHERE ((SELECT COUNT(*) FROM visits WHERE vis_sitcode = sit_pcode) = 0);

For our example, the list will be short:

sit_name

www.yandex.ru

The request works like this:

§ The site code and its name are selected from the sites table;

§ The site code is passed into a nested query, which counts records with this code in the visits table;

§ the COUNT(*) function will count the records and return their number, which will be passed to the condition;

§ if the condition is true (the number of records is 0), the site name is added to the list.

If some people find this query confusing, you can achieve the same results with a query using NOT EXISTS:

SELECT sit_name FROM sites WHERE NOT EXISTS (SELECT vis_pcode FROM visits WHERE vis_sitcode = sit_pcode);

The expression NOT EXISTS (in my opinion) brings additional clarity and is more accessible to understanding. The EXISTS expression works similarly, which checks for the presence of records.

Views (VIEW)

Views (VIEW) are used to enable persistence complex query on the server under specified name. Let's say you often need to request data by typing a large query. If you approach the problem progressively, you can create an idea. This is easy to do. For example,

CREATE VIEW show_dom2 ASSELECT hst_name FROM hosts WHERE hst_pcode IN (SELECT vis_hstcode FROM visits, sites WHERE (sit_pcode = vis_sitcode) AND (sit_name LIKE "www.dom2.ru"));

Actually, that's all. An attentive observer may have noticed that, in fact, you can take a request and at the very beginning add the words “CREATE VIEW<имя>AS". It is on this principle that we can recommend creating representations. Create a request, make sure it works, and then add everything necessary to save this request on the server as a view. The only downside to using views is that some particularly advanced query writing techniques may not work in views. Unfortunately, there is very little information about views in the postgreSQL documentation, and you can definitely find out what can be used and what cannot through trial and error. By saving the request on the server as a view, you can execute it as many times as you like, with a request like

SELECT * FROM show_dom2;

It is important to note that when executing a query that selects data from a view, the data is selected from the tables through a query that is stored in the view. The view is completely dynamic and the data returned by the view will be current when the data in the tables is updated. You can delete a view with a request like

DROP VIEW show_dom2;

Conclusion

data report request order

In this course work a database "Stationery Warehouse" was developed, containing all necessary information about products, customers, suppliers and orders. Using my database, you can easily and without special knowledge maintain a database that allows you to perform all operations with clients, orders, and manufacturers. That is, add, change, update, delete and view all available and entered data. Queries and reports were compiled based on the database.

CONCLUSION

BIBLIOGRAPHY

APPENDIX A

APPENDIX B

SQL allows you to nest queries within each other. Typically a subquery returns a single value, which is checked to see if the predicate is true.

Types of search terms:
. Comparison with the result of a subquery (=, >=)
. Checking for belonging to the results of a subquery (IN)
. Existence check (EXISTS)
. Multiple (quantitative) comparison (ANY, ALL)

Notes on nested queries:
. A subquery must select only one column (except for a subquery with an EXISTS predicate), and its result data type must match the data type of the value specified in the predicate.
. In some cases, you can use the DISTINCT keyword to ensure that a single value is returned.
. You cannot include an ORDER BY or UNION clause in a subquery.
. The subquery can be located either to the left or to the right of the search condition.
. Subqueries can use aggregation functions without a GROUP BY clause, which automatically return a special value for any number of rows, a special IN predicate, and column-based expressions.
. Whenever possible, you should use JOIN table joins instead of subqueries.

Examples for nested queries:

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Orders WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
SELECT * FROM Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)

2) Related subqueries

In SQL, you can create subqueries that reference a table from an outer query. In this case, the subquery is executed multiple times, once for each table row from the outer query. Therefore, it is important that the subquery uses the index. A subquery can access the same table as an outer one. If the outer query returns a relatively small number of rows, then the linked subquery will be faster than the unlinked one. If the subquery returns a small number of rows, then related request will execute slower than the unbound one.

Examples for related subqueries:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //returns all orders whose value exceeds the average order value for a given customer

3) Predicate EXISTS

Syntactic form: EXISTS ()

The predicate takes a subquery as an argument and evaluates to true if the subquery has output and false otherwise. The subquery is executed once and can contain several columns, since their values ​​are not checked, but the result of the presence of rows is simply recorded.

Notes on the EXISTS predicate:
. EXISTS is a predicate that returns TRUE or FALSE and can be used alone or with other Boolean expressions.
. EXISTS cannot use aggregation functions in its subquery.
. In correlated subqueries, the EXISTS predicate is executed for each row of the outer table.
. You can combine the EXISTS predicate with table joins.

Examples for the EXISTS predicate:

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – returns all customers if any of them live in San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – returns the numbers of sellers who served only one customer.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – returns numbers, names and cities of residence of all sellers who served several customers.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Predicates of quantitative comparison

Syntactic form: (=|>|=|) ANY|ALL ()

These predicates use a subquery as an argument, however, compared to the EXISTS predicate, they are used in conjunction with relational predicates (=,>=). In this sense, they are similar to the IN predicate, but are used only with subqueries. The standard allows the SOME keyword to be used instead of ANY, but not all DBMSs support it.

Notes on comparison predicates:
. The ALL predicate evaluates to TRUE if each value selected during the execution of the subquery satisfies the condition specified in the outer query predicate. It is most often used with inequalities.
. The ANY predicate evaluates to TRUE if at least one value selected during the execution of the subquery satisfies the condition specified in the outer query predicate. It is most often used with inequalities.
. If the subquery does not return any rows, then ALL automatically takes the value TRUE (it is considered that the comparison condition is satisfied), and for ANY it takes the value FALSE.
. If the comparison is TRUE for no rows and there are one or more rows with a NULL value, then ANY returns UNKNOWN.
. If the comparison is FALSE for no rows and there are one or more rows with a NULL value, then ALL returns UNKNOWN.

Examples for the predicate of quantitative comparison:

SELECT * FROM SalesPeople WHERE City=ANY(SELECT City FROM Customer)
SELECT * FROM Orders WHERE Amt ALL(SELECT Rating FROM Customer WHERE City=’Rome’)

5) Uniqueness predicate

UNIQUE|DISTINCT ()

The predicate is used to check the uniqueness (absence of duplicates) in the output data of the subquery. Moreover, in the UNIQUT predicate the lines with NULL values are considered unique, and in the DISTINCT predicate, two undefined values ​​are considered equal to each other.

6) Match predicate

MATCH ()

The MATCH predicate tests whether the value of a query string will match the value of any string resulting from the subquery. This subquery differs from the IN AND ANY predicates in that it allows processing “partial” (PARTIAL) matches that can occur among rows that have some NULL values.

7) Queries in the FROM section

In fact, it is legal to use a subquery wherever a table reference is allowed.

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//subquery returns the total amount of orders placed by each customer from London.

8) Recursive queries

WITH RECURSIVE
Q1 AS SELECT … FROM … WHERE …
Q2 AS SELECT … FROM … WHERE …

A subquery is a query contained within the WHERE keyword expression of another query to further constrain the output. Subqueries are also called nested queries. They are used to impose conditions on the output data. Subqueries can be used with SELECT, INSERT, UPDATE, or DELETE statements.

In some cases, a subquery can be used instead of linking tables, thereby linking table data implicitly. When using a subquery in a query, the subquery is executed first, and only then the query containing it is executed, taking into account the conditions for executing the subquery. A subquery can be used in either the WHERE keyword expression or the HAVING keyword expression of the main query. Logical operations and comparison operations like =, >,<, о, IN, NOT IN, AND, OR и т п. можно использовать в подзапросе. Все, что применимо к обычному запросу, применимо и к подзапросу.

When composing subqueries, you must adhere to the following rules.

The subquery must be enclosed in parentheses.

A subquery can only reference one column in its SELECT keyword expression, unless the main query uses a comparison with multiple columns from the subquery.

The ORDER BY keyword cannot be used in a subquery, although ORDER BY can be used in the main query. Instead of ORDER BY, you can use GROUP BY in a subquery.

A subquery that returns multiple rows of data can only be used in operators that allow multiple values, such as IN.

The BETWEEN operator cannot be used on a subquery, but it can be used within the subquery itself. The basic syntax for a subquery statement is as follows.

SELECT column_name FROM table WHERE column_name = (SELECT column_name FROM table WHERE conditions);

Just as a subquery can be nested within a main query, a subquery can also be nested within a subquery. In the main query, the subquery is executed before the main one is executed, and in the same way, in a subquery, the subquery nested in it will be executed first

Example. Let it be necessary to determine the number of subjects with a grade exceeding the average grade of a student with identifier 301:

SELECT COUNT (DISTINCT subj_id), mark FROM exam_marks GROUP BY mark HAVING mark > (SELECT AVG(mark) FROM exam_marks WHERE stud_id=301);

When you use multiple subqueries in a statement, the time required to process the request increases and the likelihood of errors increases due to the complexity of the statement.

Linked subqueries are allowed in many SQL implementations. When you use subqueries in an inner query, you can reference the table whose name is specified in the FROM clause of the outer query. Such subqueries are called linked. The associated subquery is executed once for each row of the main query table, as follows:

A row is selected from the table whose name is specified in the outer query;

The subquery is executed, and the resulting value is used to parse that row in the WHERE clause of the outer query;

Based on the result of evaluating this condition, a decision is made to include/not include the line in the output data;

The procedure is repeated for next line external query tables.

The GROUP BY clause allows you to group records returned by a SELECT query by the value of a certain field. Using the HAVING clause allows you to filter such groups during output. The HAVING clause predicate is evaluated not for each result row, but for each group of output records generated GROUP offer BY of the outer request.

SELECT exam_date, SUM(mark) FROM exam_marks a GROUP BY exam_date

HAVING 10< (SELECT COUNT(mark) FROM exam_marks b

WHERE a.exam_date=b.exam_date);

It is necessary, based on the data in the exam_marks table, to determine the sum of grades received by students (mark field values), grouping the grade values ​​by exam dates and excluding those days when the number of students taking exams during the day was less than ten.

Summary: A subquery is a query executed within another query for a job additional conditions on the output data. A subquery can be used in WHERE and HAVING keyword expressions. The syntax of subqueries is practically no different from the syntax of a regular query; there are only minor restrictions. One of these restrictions is the prohibition on using the ORDER BY keyword in subqueries; however, you can use GROUP BY instead, which achieves almost the same effect. Subqueries are used to place conditions in queries for which the exact data is not known, thereby increasing the power and flexibility of SQL.

4.Basic concepts in the conceptual design of relational databases (entity, attributes, relationships). Elements of the relational model.

Dependencies between attributes

5.Data integrity and its types. Integrity violations (anomalies).

6.Functional connections of attributes and normalization of tables. Basic normal forms (NF). Examples of sf.

7.Use of er-modeling in the conceptual design of a database. Diagrams of er-instances and er-types.

8.Converting a conceptual model into a relational one. The main stages and rules for forming relationships (example).

9. Structure and main technical characteristics of the access 200 database *. Possibility of designing personal and network applications.

10.Construction of tables in ms access archive.Field properties. Definition of data type, keys, indexes.

11. Linking tables in the access database. Logical design and ensuring referential integrity of data.

12.Tools for implementing queries in the access database. Types of requests.

5.2.3 Querying related tables

5.2.4 Removal requests

13.Implementation of queries with group operations and calculated fields. Examples.

14.Implementation of requests for modification and table creation.

15.Standards of modern implementations of the sql language. Main sections and their content in sql-Jet.

16. General format of the select instruction (select request). Example implementation.

17.Example of qbe- and sql-implementation of a cross-query.

18.Creating an application interface in the access database. Working in the form designer. Sections, controls, properties.

19.Creating nested sql queries. Example implementation.

20. Database access service programs.

21.Protection and administration of the database using access database.

22.Use of macros, reports and data access pages in ms access storage applications.

23. Matlab programming system: general characteristics. Expansion packages and specialized applications: purposes and capabilities. Simulink subsystem.

24.Data structures and basic control structures in the matlab programming system

25.Graphical tools of the matlab system. Working with the lti-Viewer tool for graphical analysis of linear control systems.

26.Stages of building a model in the Simulink subsystem. Elements of visual block modeling technology. Setting up modeling parameters and block parameters.

27. General description of simulink library blocks.

28.Implementation of the principle of hierarchy in Simulink models using blocks of ports and subsystems. Subsystem masking.

29.Virtual instrument components and their assembly into an application in the LabView environment. Basic LabView controls and indicators and their connection in a block diagram.

19.Creating nested sql queries. Example implementation.

With SQL you can nest queries inside each other. Typically, the inner query generates a value that is tested in the outer query's predicate (in the WHERE or HAVING clause) to determine whether it is true or false. In conjunction with a subquery, you can use the EXISTS predicate, which returns true if the output of the subquery is not empty.

When combined with the other features of the select operator, such as grouping, a subquery is a powerful tool for achieving the desired result. In the FROM part SELECT statement It is permissible to apply synonyms to table names if, when forming a query, we need more than one instance of a certain relation. Synonyms are specified using the AS keyword, which can be omitted altogether. So the FROM part might look like this:

FROM Rl AS A, Rl AS B

FROM Rl A. Rl B:

both expressions are equivalent and are considered to be applications of the SELECT statement to two instances of table R1.

For example, let’s show what some queries to the “Session” database look like in SQL:

 List of those who passed all required exams.

WHERE Score > 2

HAVING COUNT(*) = (SELECT COUNT(*)

WHERE R2.Group=R3.Group AND full namea.full name)

Here, the built-in query determines the total number of exams that each student in the student's class must take, and compares this number with the number of exams the student has taken.

 A list of those who were supposed to take the DB exam, but have not yet taken it.

SELEST Full name

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

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

The EXISTS (SubQuery) predicate is true when the SubQuery subquery is not empty, that is, it contains at least one tuple, otherwise the EXISTS predicate is false.

The NOT EXISTS predicate is true only when the SubQuery is empty.

Notice how NOT EXISTS with a nested query allows you to avoid the relationship difference operation. For example, formulating a query with the word “all” can be done as if with a double negative. Let's look at an example of a database that models delivery individual parts individual suppliers, it is represented by one SP relationship “Suppliers-Parts” with the schema

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

This is how the answer to the request is formulated: “Find suppliers who supply all the parts.”

SELECT DISTINCT VENDOR_NUMBER FROM SP SP1 WHERE NOT EXISTS

(SELECT part_number

FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SP2.supplier_number=SP1.supplier_number AND

sp2.part_number = P.part_number)):

In fact, we reformulated this request as follows: “Find suppliers such that there is no part that they do not supply.” It should be noted that this query can also be implemented through aggregate functions with a subquery:

SELECT DISTINCT Vendor_number

GROUP BY Vendor_number

HAVING CounKDISTINCT part_number) =

(SELECT Count(part_number)

The SQL92 standard extends comparison operators to multiple comparisons using the ANY and ALL keywords. This extension is used when comparing the value of a specific column with the data column returned by a subquery.

The ANY keyword placed in any comparison predicate means that the predicate will be true if for at least one value from the subquery the comparison predicate is true. The ALL keyword requires that the comparison predicate be true when compared against all rows in the subquery.

For example, let’s find students who passed all exams with a grade of no lower than “good.” We work with the same “Session” database, but add to it one more relation R4, which characterizes the delivery of laboratory work during the semester:

R 1 = (Name, Discipline, Grade);

R 2 = (full name, group);

R 3 = (Groups, Discipline)

R 4 = (Name, Discipline, Lab_work number, Grade);

Select R1.Full name From R1 Where 4 > = All (Select Rl.Rating

Where R1.Full name = R11.Full name)

Let's look at another example:

Select students whose exam grade is no less than at least one grade in the laboratory work they passed in this discipline:

Select R1.Name

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

Where Rl.Discipline = R4. Discipline AND R1.Full name = R4.Full name)

Merging tables.

A query can combine data from one or more tables. This join of tables is called connection (binding) tables. Distinguish internal and external connections.

Internal A join is a join in which the result set is obtained by listing the desired fields from different tables after the word SELECT. In this case, the tables must be in a one-to-one relationship.

For example,

SELECT R.fam, R.birthday,A.Foto

FROM Rabotniki R, Advanced A

where the Rabotniki and Advanced tables contain the main and additional information about the employees of the enterprise. One-to-one communication. The Rabotniki table is given the alias R, and the Advanced table is given the alias A.

In the previous example, the field name is preceded by the table name. Field and table names are separated by a dot. The table name must be specified if field names are repeated for different tables.

If you apply an inner join to one-to-many tables, the result set may contain redundant information. Selection criteria are used to eliminate redundancy.

Example 1. Given a Sotrudniki database, consisting of two tables:

An inner join query for tables related by a one-to-many relationship has the form

The number of records in the resulting data set is equal to the number of records in the Sotrudniki table multiplied by the number of records in the Doljn table. The resulting data set looks like and contains redundant information:

Selection criteria are used to limit the number of records in the resulting data set. The request in this case will look like

SELECT S_Fio,S_Birthday,D_Nazv FROM Sotrudniki, Doljn

WHERE S_Doljn=D_Code

The number of records in the resulting data set will be equal to the number of records in the Sotrudniki table. p

Example 2. It is required to generate a query for the Sotrudniki database that will allow you to obtain a list of employees who have the position “programmer”. We get

SELECT S_fio, S_birthday FROM sotrudniki, doljn

WHERE S_doljn=D_code and D_nazv="programmer"

Table self-joining is allowed in SQL queries. In this case, one table is given two aliases.

For example, To find all peers in the Sotrudniki table, you can write a query:

SELECT s1.s_fio, s2.s_fio, s1.s_birthday

FROM Sotrudniki s1, Sotrudniki s2

WHERE (EXTRACT(YEAR

FROM s1.s_birthday)=EXTRACT(YEAR

FROM s2.s_birthday))

AND (s1.s_fio!=s2.s_fio) AND (s1.s_fio

The last condition arranges surnames and eliminates duplication of results.

With an inner join, all tables whose fields are specified in the SQL query are equal. That is, for each record in the first table there was a corresponding record in the second table.

At external association (outer join) records are included in the result set regardless of whether there is a corresponding field in the second table. There are three types of outer join.

1) LEFT OUTER JOIN … ON – the left one includes in the result all records of the first table, even those for which there is no match in the second.

2) RIGHT OUTER JOIN … ON– right, includes in the result all records of the second table, even those for which there is no match in the first.

3) FULL OUTER JOIN…ON– complete, the result includes a union of records from both tables, regardless of their correspondence.

With an outer join, you can talk about which table is the main one. In the first case - left, in the second - right.

For example. Let there be surnames in the Sotrudniki table of the Sotrudniki DB that have a position not specified in the Doljn table, and there are positions in the Doljn table for which there is no surname in the Sotrudniki table. Then

1) SELECT * FROM Sotrudniki LEFT OUTER JOIN Doljn

ON S_doljn=D_code

The result will include all Sotrudniki fields and tables and Doljn tables. The number of rows corresponds to the number of records in the Sotrudniki table. In rows related to records for which Doljn did not find a match, the fields of the Doljn table are left empty.

2) SELECT * FROM Sotrudniki RIGHT OUTER JOIN Doljn

ON S_doljn=D_code

The number of rows corresponds to the number of records in the Doljn table. In rows related to records for which Sotrudniki did not find a match, the fields of the Sotrudniki table remain empty.

3) SELECT * FROM Sotrudniki FULL OUTER JOIN Doljn ON

Added rows related to the Sotrudniki table to rows related to the Doljn table, for which there is no match in the Sotrudniki table.

In an SQL query, you can use queries nested within the first one. This can be applied to operators that return aggregate characteristics as well as to operators that return multiple values.

For example,

1) Determine all namesakes in the Sotrudniki and Sotrudniki1 tables that have the same structure:

SELECT * FROM Sotrudniki

WHERE S_fio IN (SELECT S_fio FROM Sotrudniki1)

The nested SELECT statement returns the set of last names from the Sotrudniki1 table, and the WHERE clause of the main SELECT statement selects those records in the Sotrudniki table that are in the set of last names from the Sotrudniki1 table.

2) Output the last name(s) of the youngest employee from the Sotrudniki database:

SELECT S_Fio, EXTRACT(YEAR FROM S_Birthday)

WHERE EXTRACT(YEAR FROM S_Birthday)=

(SELECT max(EXTRACT(YEAR FROM S_Birthday))

FROM Sotrudniki)

A nested SELECT statement returns the maximum birth year, which is used in the WHERE clause of the main SELECT statement.

3) Output from the Students database all grades of a specific student, for example, Petrov:

SELECT S_fam, P_nazv, E_mark FROM

Examination, Predm, Students

WHERE E_student=(SELECT S_code FROM Students

WHERE S_fam="Petrov")

AND E_Predm=P_code AND E_Student=S_code

In the nested SELECT construct, the code of a student named “Petrov” is determined, and the last conditions ensure that redundancy is eliminated during internal joins of tables.

Related subqueries. An inner query can reference a table whose name is specified in the clause FROM external request. Such connected The subquery is executed once for each row of the main query table.

For example, obtain information about the subjects in which the exam was held on a specific date, for example, ‘01/14/2006’:

SELECT * FROM Predm PR

WHERE "01/14/2006" IN (SELECT E_date

FROM Examination

WHERE PR.P_code=E_predm)

The same problem can be solved using the table join operation:

SELECT DISTINCT P_nazv FROM Predm, Examination

WHERE P_code=E_predm AND E_date= "01/14/2006"

Example. Display the last name(s) of the student who received a grade above the average on the exam

SELECT DISTINCT S_fam FROM Students, Examination

E_mark>(SELECT AVG(E_mark) FROM Examination)

AND S_code=E_student

You can use keywords in the WHERE clause when working with sets of records ALL And ANY. ALL- the condition is met for all records, ANY- the condition is met for at least one record.

For example,

1) display the last names of employees from the Sotrudniki table who are not older than any employee in the Sotrudniki1 table:

WHERE S_birthday>= ALL (SELECT S_birthday

FROM Sotrudniki1)

2) display the names of employees from the Sotrudniki table who are younger than at least one employee in the Sotrudniki1 table:

SELECT S_fio,S_birthday FROM Sotrudniki

WHERE S_birthday> ANY (SELECT S_birthday FROM Sotrudniki1)

You can use the keyword in nested SELECT statements EXISTS, which means selecting only those records for which the subquery returns one or more values.

For example,

SELECT S_fio,S_birthday FROM Sotrudniki S1

WHERE EXISTS (SELECT S_fio,S_birthday

FROM Sotrudniki S2

WHERE (S1.S_birthday=S2.S_birthday)

AND (S1.S_code!=S2.S_code))

Getting a list of employees who have at least one peer.