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

== Извештај за членови со најмногу позајмувања ==
{{{#!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;
}}}

=== Извештај за број на копии според состојба ===
{{{#!sql
SELECT Condition, COUNT(CopyID) AS copy_count
FROM book_copies
WHERE Condition IN ('Good', 'New')
GROUP BY Condition;
}}}