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

Timestamp:

01/21/25 07:10:58 (30 hours ago)

Author:

201205

Comment:

--

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

@@ -46 +46 @@
     coalesce((
         select count(*) 
-        from Library l join LibraryBook lb on l.InventoryId = lb.InventoryId
+        from Library l join ContainsLibraryBook lb on l.InventoryId = lb.InventoryId
         where l.UserId=u.UserId
     ), 0) as NumberOfBooksOwned,
@@ -65 +65 @@
     extract(quarter from t.BorrowDate) as quarter,
     b.Title, b.Author, count(*) as NumberOfTimesBorrowed
-from Transaction t join TransactionBook tb on t.TransactionId=tb.TransactionId join Book b on tb.BookId = b.BookId
+from Transaction t join ExchangingBook eb on t.TransactionId=eb.TransactionId join Book b on eb.BookId = b.BookId
 group by 
     extract(year from t.BorrowDate),
@@ -91 +91 @@
 {{{
 select b.Title, b.Author, count(*) as TimesAddedToWishlist
-from WishlistBook wb join Book b on wb.BookId = b.BookId
+from ContainsWishlistBook cwb join Book b on cwb.BookId = b.BookId
 group by b.BookId, b.Title, b.Author
 order by TimesAddedToWishlist desc
@@ -104 +104 @@
 with BookSwapCount as(
         select b.Title, extract(year from t.BorrowDate) as SwapYear, count(tb.BookId) as SwapCount
-        from Transaction t join TransactionBook tb on t.TransactionId=tb.TransactionId
-                join Book b on tb.BookId=b.BookId
+        from Transaction t join ExchangingBook eb on t.TransactionId=eb.TransactionId
+                join Book b on eb.BookId=b.BookId
         group by b.title, extract(year from t.borrowdate)
 )
@@ -127 +127 @@
         select au.Username, avg(r.Rating) as AverageRating
         from AppUser au join Review r on au.UserId=r.ReceiverId
-        where r.Date between '2025-01-01' and '2025-06-30'
+        where r.ReviewDate between '2025-01-01' and '2025-06-30'
         group by au.Username
 )