Напредни извештаи од базата (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,
    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
    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
WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
GROUP BY 
    book.BookID,
    book.Title,
    book.CoverImage,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate)
ORDER BY loan_count DESC
LIMIT 5;

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

SELECT 
    book.CoverImage,
    book.Title,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
    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
WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
GROUP BY 
    book.BookID,
    book.Title,
    book.CoverImage,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate)
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;