Напредни извештаи од базата (SQL и складирани процедури)

Извештај за членови со најмногу позајмувања

SELECT users.FirstName, users.LastName, COUNT(loan.LoanID) AS loan_count
FROM users
INNER JOIN loan ON users.UserID = loan.MemberID
GROUP BY users.UserID, users.FirstName, users.LastName
ORDER BY loan_count DESC
LIMIT 5;

Извештај за најпозајмени книги

{{{$!sql SELECT book.CoverImage, book.Title, book.Genre, author.FirstName, author.LastName, COUNT(loan.LoanID) AS loan_count FROM book INNER JOIN book_copies ON book.BookID = book_copies.BookID LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID INNER JOIN book_author ON book.BookID = book_author.BookID INNER JOIN author ON book_author.AuthorID = author.AuthorID GROUP BY book.BookID, book.Title, book.CoverImage, book.Genre, author.FirstName, author.LastName ORDER BY loan_count DESC LIMIT 5; }}}

Извештај за најмалку позајмувани книги

{{{$!sql SELECT book.CoverImage, book.Title, book.Genre, author.FirstName, author.LastName, COUNT(loan.LoanID) AS loan_count FROM book INNER JOIN book_copies ON book.BookID = book_copies.BookID LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID INNER JOIN book_author ON book.BookID = book_author.BookID INNER JOIN author ON book_author.AuthorID = author.AuthorID GROUP BY book.BookID, book.Title, book.CoverImage, book.Genre, author.FirstName, author.LastName ORDER BY loan_count ASC LIMIT 5; }}}