There is a database of tables: students, groups and statements. The last are the marks for the exams. It is necessary to somehow derive all possible pairs of students in which the first grade point has a higher average score than the second one, and sort it all into groups. It seems to me that it is required to form a large table with all possible combinations of students and their scores, and then just make a sample of it based on avg (1)> avg (2). But is it possible to sort through all combinations of students, and if so, how? Thank you in advance..

upd1. In the table of students, just a series of names: Petrov, Guskov, Ivanov, and the names of the groups in which they study.

enter image description here

In the table of statements lists the estimates for the items. enter image description here

The final goal is to get a table of 4 columns: Last name of 1 student, his average score, last name of the 2nd student, and his average score, while the average score of the first must be greater than the average score of the second

  • Complete the question with the label of your DBMS, example data, result and how you want to solve. It's hard to understand what you're asking - Viktorov
  • one
    zakzhoynit how the table itself on itself, eliminating the coincidence of students' id, and adding the avg and the group mentioned - teran
  • If I understand correctly, INNER JOIN will not work here, since with its help we can only create pairs of identical surnames ... Then FULL JOIN? Sorry for the stupid questions ... - Glimpse Unthinkable
  • INNER is fine. the union is set solely by conditions. make a condition that A.id!=B.id here and get all the pairs except connecting yourself with yourself - Mike
  • what remains after the inner join will depend on what conditions of table splicing you write. so if you specify the coincidences and id of the students of both tables, then you will get a pair of identical ones. - teran

1 answer 1

As a result, I managed without using join at all:

 SELECT с1.Фамилия AS first_stud, с2.Фамилия AS second_stud, с1.student_avg AS avg_first, с2.student_avg AS avg_second, с1.Код_группы AS код_гр_1, с2.Код_группы AS код_гр_2 FROM (SELECT Студент.Фамилия, Avg(Ведомость.Оценка) AS student_avg, Студент.Код_группы FROM Студент INNER JOIN Ведомость ON Студент.Номер_студбилета=Ведомость.Номер_студбилета GROUP BY Код_группы, Студент.Фамилия) AS с1, (SELECT Студент.Фамилия, Avg(Ведомость.Оценка) AS student_avg, Студент.Код_группы FROM Студент INNER JOIN Ведомость ON Студент.Номер_студбилета=Ведомость.Номер_студбилета GROUP BY Код_группы, Студент.Фамилия) AS с2 WHERE с1.student_avg>с2.student_avg AND с1.Код_группы=с2.Код_группы;