Changes between Version 4 and Version 5 of Напредни извештаи од базата (SQL и складирани процедури)
- Timestamp:
- 02/12/25 23:32:32 (10 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Напредни извештаи од базата (SQL и складирани процедури)
v4 v5 48 48 49 49 CREATE VIEW UserBookingExpenditureReport AS 50 SELECT 51 u.userid, 52 u.name, 53 u.surname, 54 COUNT(b.bookingid) AS NumberOfBookings, 50 SELECT 51 u.userid, 52 u.name, 53 u.surname, 54 COUNT(b.bookingid) AS NumberOfBookings, 55 55 SUM(b.total_cost) AS TotalExpenditure 56 FROM 57 ApplicationUser u58 JOIN 56 FROM 57 application_user u 58 JOIN 59 59 Booking b ON u.userid = b.userid 60 GROUP BY 60 GROUP BY 61 61 u.userid, u.name, u.surname 62 ORDER BY 62 ORDER BY 63 63 TotalExpenditure DESC; 64 64 … … 82 82 Booking b 83 83 JOIN 84 ApplicationUser u ON b.userid = u.userid84 application_user u ON b.userid = u.userid 85 85 JOIN 86 86 Flight f ON b.flightid = f.flightid … … 95 95 96 96 CREATE VIEW TopDestinationsWishlistReport AS 97 SELECT 98 d.destinationid, 99 d.name AS DestinationName, 100 d.country, 97 SELECT 98 d.destinationid, 99 d.name AS DestinationName, 100 d.country, 101 101 COUNT(w.wishlistid) AS NumberOfWishlistEntries 102 FROM 102 FROM 103 103 Wishlist w 104 JOIN 105 Destination d ON w.target _id = d.destinationid106 GROUP BY 104 JOIN 105 Destination d ON w.targetid = d.destinationid 106 GROUP BY 107 107 d.destinationid, d.name, d.country 108 ORDER BY 108 ORDER BY 109 109 NumberOfWishlistEntries DESC 110 110 LIMIT 5; … … 117 117 118 118 CREATE VIEW TopUsersByBookings AS 119 SELECT 120 u.userid, 121 u.name, 122 u.surname, 119 SELECT 120 u.userid, 121 u.name, 122 u.surname, 123 123 COUNT(b.bookingid) AS NumberOfBookings 124 FROM 125 ApplicationUser u126 JOIN 124 FROM 125 application_user u 126 JOIN 127 127 Booking b ON u.userid = b.userid 128 GROUP BY 128 GROUP BY 129 129 u.userid, u.name, u.surname 130 ORDER BY 130 ORDER BY 131 131 NumberOfBookings DESC; 132 132 … … 146 146 Review r 147 147 JOIN 148 ApplicationUser u ON r.userid = u.userid148 application_user u ON r.userid = u.userid 149 149 WHERE 150 150 r.target_id IN (SELECT FlightID FROM Flight) … … 201 201 202 202 {{{ 203 204 CREATE VIEW TopMonhtlyReport AS 203 205 WITH MonthlyBookings AS ( 204 206 SELECT … … 243 245 SELECT month, 'Airport' AS category, airport_name AS name, departures_count AS count FROM MonthlyAirports WHERE rank <= 3 244 246 ORDER BY month, category, count DESC; 245 }}} 247 248 }}}
