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

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

SELECT 
    users.FirstName,
    users.LastName,
    EXTRACT(MONTH FROM loan.LoanDate) AS month,
    COUNT(loan.LoanID) AS loan_count
FROM users
INNER JOIN loan ON users.UserID = loan.MemberID
WHERE EXTRACT(MONTH FROM loan.LoanDate) = 4  -- Change this number for different months (1-12)
GROUP BY 
    users.UserID, 
    users.FirstName, 
    users.LastName,
    EXTRACT(MONTH FROM loan.LoanDate)
ORDER BY loan_count DESC
LIMIT 5;

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

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;

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

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;

Извештај за број на копии според состојба

SELECT Condition, COUNT(CopyID) AS copy_count
FROM book_copies
WHERE Condition IN ('Good', 'New')
GROUP BY Condition;

Извештај за број на копии по книга и состојба

SELECT book.Title, book_copies.Condition, COUNT(book_copies.CopyID) AS copy_count
FROM book
INNER JOIN book_copies ON book.BookID = book_copies.BookID
WHERE book_copies.Condition IN ('Good', 'New')
GROUP BY book.BookID, book.Title, book_copies.Condition;