Напредни извештаи од базата (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 
    EXTRACT(YEAR FROM finedate) AS fine_year,
    SUM(CASE WHEN status = 'Paid' THEN fineamount ELSE 0 END) AS total_paid,
    SUM(CASE WHEN status = 'Unpaid' THEN fineamount ELSE 0 END) AS total_unpaid,
    SUM(fineamount) AS total_fines
FROM fine
WHERE EXTRACT(YEAR FROM finedate) = 2024  -- Change year as needed
GROUP BY EXTRACT(YEAR FROM finedate)
ORDER BY fine_year;

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

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;