Database schema (posgresql):

Book(id, title, student_id) Student(id, name, last_name, e_mail) Teacher(id, name, last_name, e_mail, subject) Groups(student_id, teacher_id)

Choose a Teacher who has the largest number of Book for all of his Student . Sort by quantity in descending order. It should look like this:

 Teacher's last_name | Book's quantity Petrov | 9 Ivanov | 5

My query returns only the names of the teachers ...

 SELECT Teacher.last_name FROM Teacher WHERE EXISTS ( SELECT Groups.teacher_id FROM Groups WHERE EXISTS ( SELECT Book.student_id, COUNT(student_id) AS cnt FROM Book GROUP BY student_id ORDER BY cnt DESC))

    1 answer 1

    You can do the following:

     SELECT t.name AS name, t.last_name AS last_name, COUNT(b.id) AS total FROM Teacher AS t JOIN Groups AS g ON t.id = g.teacher_id JOIN Student AS s ON s.id = g.student_id JOIN Book AS b ON s.id = b.student_id GROUP BY t.name, t.last_name ORDER BY total DESC;
    • Grateful to you! - TehD