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))