Hello! There is a schema database of debtors in subjects at the university. enter image description here

I am interested in the following sample:

For each group, select the total number of debtors and the number of debtors who have more than two debts. The main tables that participate in the request:

  • Students (StudentId (PK), GroupNumber, RecordBookNumber (record number), FirstName, LastName, MiddleName)
  • Subjects (SubjectId (PK), Name)
  • StudentsSubjects (StudentsSubjectsId (PK), SubjectId (FK), TeacherId (FK), MarkId (FK), ControlTypeId (FK), ControlPeriod (FK), CuratorID (FK))

I am able to display some data separately.

The amount of debt in the group

select Students.GroupNumber as groupNum, Count(StudentsSubjects.StId) as Kol_dolgov from StudentsSubjects, Students where Students.RecordBookNumber = StudentsSubjects.StId group by Students.GroupNumber order by groupNum

The number of debtors who have more than two debts

 select t1.Grp, count(t1.Rbn) as Kol_dol_bol2 from (select Students.GroupNumber as Grp, Students.RecordBookNumber as Rbn, count(StudentsSubjects.SubjId) as Kol_dolg from Students,StudentsSubjects, Subjects</li> where Students.RecordBookNumber = StudentsSubjects.StId and Subjects.SubjectId = StudentsSubjects.SubjId group by Students.RecordBookNumber, Students.GroupNumber having count(StudentsSubjects.SubjId) > 2) as t1 group by t1.Grp

The number of debtors in the group

 select Students.GroupNumber as Grp, count(Students.RecordBookNumber) as Kol_dolgnikov from Students group by Students.GroupNumber order by 1

But I can not combine all this in one table. Could you suggest how to do this and is it even possible, given this DB schema.

  • UNION (9 characters needed ...) - Igor

1 answer 1

There are several options:

  1. Less correct: create a view (View), placing in it the necessary fields from the tables and the fields obtained during the groupings.
  2. More correct: write a stored procedure that will merge several SELECTs into one general table.

It is known that well-written stored procedures run faster than views by storing the execution plan.

  • one
    And the rest of the query plans are not cached? - Anatol
  • I used the left outer join . select .... from (...) as table1, left outer join (...) as table2 on table1.Номер_группы = table2.Номер_группы . select .... from (...) as table1, left outer join (...) as table2 on table1.Номер_группы = table2.Номер_группы . As a result, I got the result that I needed. In this case, this decision may be correct? - Dmitry Bystrov