Changes between Version 4 and Version 5 of Напредни извештаи од базата (SQL и складирани процедури)

Timestamp:

12/23/24 16:03:58 (3 weeks ago)

Author:

222039

Comment:

--

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

@@ -10 +10 @@
 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;
+}}}