Changes between Version 3 and Version 4 of Напредни извештаи од базата (SQL и складирани процедури)
- Timestamp:
- 02/12/25 21:54:56 (10 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Напредни извештаи од базата (SQL и складирани процедури)
v3 v4 6 6 7 7 CREATE VIEW MostPopularFlightsReport AS 8 SELECT 9 f. FlightID,10 f. FlightNumber,11 f. DepartureAirport,12 f. ArrivalAirport,8 SELECT 9 f.flightid, 10 f.flight_number, 11 f.departure_airport, 12 f.arrival_airport, 13 13 COUNT(b.BookingID) AS NumberOfBookings 14 FROM 14 FROM 15 15 Flight f 16 JOIN 17 Booking b ON f. FlightID = b.FlightID18 GROUP BY 19 f. FlightID, f.FlightNumber, f.DepartureAirport, f.ArrivalAirport20 ORDER BY 16 JOIN 17 Booking b ON f.flightid = b.flightid 18 GROUP BY 19 f.flightid, f.flight_number, f.departure_airport, f.arrival_airport 20 ORDER BY 21 21 NumberOfBookings DESC; 22 22 … … 28 28 29 29 CREATE VIEW AverageDestinationRatingReport AS 30 SELECT 31 d. DestinationID,32 d. Name AS DestinationName,33 AVG(r. Rating) AS AverageRating34 FROM 30 SELECT 31 d.destinationid, 32 d.name AS DestinationName, 33 AVG(r.rating) AS AverageRating 34 FROM 35 35 Review r 36 JOIN 37 Destination d ON r. TargetID= d.DestinationID38 GROUP BY 39 d. DestinationID, d.Name40 ORDER BY 36 JOIN 37 Destination d ON r.target_id = d.DestinationID 38 GROUP BY 39 d.destinationid, d.name 40 ORDER BY 41 41 AverageRating DESC; 42 42 … … 49 49 CREATE VIEW UserBookingExpenditureReport AS 50 50 SELECT 51 u. UserID,52 u. Name,53 u. Surname,54 COUNT(b. BookingID) AS NumberOfBookings,55 SUM(b. TotalCost) AS TotalExpenditure51 u.userid, 52 u.name, 53 u.surname, 54 COUNT(b.bookingid) AS NumberOfBookings, 55 SUM(b.total_cost) AS TotalExpenditure 56 56 FROM 57 57 ApplicationUser u 58 58 JOIN 59 Booking b ON u. UserID = b.UserID60 GROUP BY 61 u. UserID, u.Name, u.Surname59 Booking b ON u.userid = b.userid 60 GROUP BY 61 u.userid, u.name, u.surname 62 62 ORDER BY 63 63 TotalExpenditure DESC; … … 70 70 71 71 CREATE VIEW PendingPaymentBookingsReport AS 72 SELECT 73 b. BookingID,74 u. Name AS UserName,75 u. Surname AS UserSurname,76 f. FlightNumber,77 f. DepartureAirport,78 f. ArrivalAirport,79 b. BookingDate,80 b. TotalCost81 FROM 72 SELECT 73 b.bookingid, 74 u.name AS UserName, 75 u.surname AS UserSurname, 76 f.flight_number, 77 f.departure_airport, 78 f.arrival_airport, 79 b.booking_date, 80 b.total_cost 81 FROM 82 82 Booking b 83 JOIN 84 ApplicationUser u ON b. UserID = u.UserID85 JOIN 86 Flight f ON b. FlightID = f.FlightID87 WHERE 88 b. PaymentStatus = 'Pending';83 JOIN 84 ApplicationUser u ON b.userid = u.userid 85 JOIN 86 Flight f ON b.flightid = f.flightid 87 WHERE 88 b.payment_status = 'PENDING'; 89 89 90 90 }}} … … 96 96 CREATE VIEW TopDestinationsWishlistReport AS 97 97 SELECT 98 d. DestinationID,99 d. Name AS DestinationName,100 d. Country,101 COUNT(w. WishlistID) AS NumberOfWishlistEntries98 d.destinationid, 99 d.name AS DestinationName, 100 d.country, 101 COUNT(w.wishlistid) AS NumberOfWishlistEntries 102 102 FROM 103 103 Wishlist w 104 104 JOIN 105 Destination d ON w. TargetID = d.DestinationID106 GROUP BY 107 d. DestinationID, d.Name, d.Country105 Destination d ON w.target_id = d.destinationid 106 GROUP BY 107 d.destinationid, d.name, d.country 108 108 ORDER BY 109 109 NumberOfWishlistEntries DESC … … 118 118 CREATE VIEW TopUsersByBookings AS 119 119 SELECT 120 u. UserID,121 u. Name,122 u. Surname,123 COUNT(b. BookingID) AS NumberOfBookings120 u.userid, 121 u.name, 122 u.surname, 123 COUNT(b.bookingid) AS NumberOfBookings 124 124 FROM 125 125 ApplicationUser u 126 126 JOIN 127 Booking b ON u. UserID = b.UserID128 GROUP BY 129 u. UserID, u.Name, u.Surname127 Booking b ON u.userid = b.userid 128 GROUP BY 129 u.userid, u.name, u.surname 130 130 ORDER BY 131 131 NumberOfBookings DESC; … … 139 139 CREATE VIEW FrequentFlightReviewers AS 140 140 SELECT 141 u. UserID,142 u. Name,143 u. Surname,144 COUNT(r. ReviewID) AS NumberOfReviews141 u.userid, 142 u.name, 143 u.surname, 144 COUNT(r.review_id) AS NumberOfReviews 145 145 FROM 146 146 Review r 147 147 JOIN 148 ApplicationUser u ON r. UserID = u.UserID148 ApplicationUser u ON r.userid = u.userid 149 149 WHERE 150 r. TargetIDIN (SELECT FlightID FROM Flight)151 GROUP BY 152 u. UserID, u.Name, u.Surname150 r.target_id IN (SELECT FlightID FROM Flight) 151 GROUP BY 152 u.userid, u.name, u.surname 153 153 ORDER BY 154 154 NumberOfReviews DESC; … … 156 156 }}} 157 157 158 * Извештај за следни летови159 160 {{{161 162 CREATE VIEW UpcomingFlightsWithAvailability AS163 SELECT164 f.FlightID,165 f.FlightNumber,166 f.DepartureAirport,167 f.ArrivalAirport,168 f.DepartureTime,169 f.ArrivalTime,170 f.AvailableSeats171 FROM172 Flight f173 WHERE174 f.DepartureTime > NOW() AND f.AvailableSeats > 0175 ORDER BY176 f.DepartureTime;177 178 }}}179 158 180 159 * Извештај за најдобри начини на плаќање според вкупната потрошувачка … … 184 163 CREATE VIEW PaymentMethodSpendReport AS 185 164 SELECT 186 p. PaymentMethod,165 p.payment_method, 187 166 SUM(p.Amount) AS TotalAmountSpent 188 167 FROM 189 168 Payment p 190 169 GROUP BY 191 p. PaymentMethod170 p.payment_method 192 171 ORDER BY 193 172 TotalAmountSpent DESC; … … 200 179 201 180 CREATE VIEW FlightBookingByCity AS 202 SELECT 203 a. CityID,204 c. Name AS CityName,205 COUNT(b. BookingID) AS NumberOfBookings206 FROM 181 SELECT 182 a.airportid, 183 c.name AS CityName, 184 COUNT(b.bookingid) AS NumberOfBookings 185 FROM 207 186 Booking b 208 JOIN 187 JOIN 209 188 Flight f ON b.FlightID = f.FlightID 210 JOIN 211 Airport a ON f. DepartureAirport = a.AirportID212 JOIN 213 City c ON a.CityID = c.CityID214 GROUP BY 215 a. CityID, c.Name216 ORDER BY 189 JOIN 190 Airport a ON f.departure_airport = a.airportid 191 JOIN 192 Destination c ON a.city = c.destinationid 193 GROUP BY 194 a.airportid, c.name 195 ORDER BY 217 196 NumberOfBookings DESC; 218 197 … … 223 202 {{{ 224 203 WITH MonthlyBookings AS ( 225 SELECT 226 DATE_TRUNC('month', b.booking Date) AS month,227 d. Name AS destination_name,228 COUNT(b. BookingID) AS bookings_count,229 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking Date) ORDER BY COUNT(b.BookingID) DESC) AS rank204 SELECT 205 DATE_TRUNC('month', b.booking_date) AS month, 206 d.name AS destination_name, 207 COUNT(b.bookingid) AS bookings_count, 208 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank 230 209 FROM Booking b 231 JOIN Flight f ON b. FlightID = f.FlightID232 JOIN Airport a ON f. ArrivalAirport = a.AirportID233 JOIN Destination d ON a. City = d.DestinationID210 JOIN Flight f ON b.flightid = f.flightid 211 JOIN Airport a ON f.arrival_airport = a.airportid 212 JOIN Destination d ON a.city = d.destinationid 234 213 GROUP BY month, destination_name 235 ), 214 ), 236 215 237 216 MonthlyFlights AS ( 238 SELECT 239 DATE_TRUNC('month', b.booking Date) AS month,240 f. FlightNumber,241 COUNT(b. BookingID) AS bookings_count,242 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking Date) ORDER BY COUNT(b.BookingID) DESC) AS rank217 SELECT 218 DATE_TRUNC('month', b.booking_date) AS month, 219 f.flight_number, 220 COUNT(b.bookingid) AS bookings_count, 221 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank 243 222 FROM Booking b 244 JOIN Flight f ON b. FlightID = f.FlightID245 GROUP BY month, f. FlightNumber246 ), 223 JOIN Flight f ON b.flightid = f.flightid 224 GROUP BY month, f.flight_number 225 ), 247 226 248 227 MonthlyAirports AS ( 249 SELECT 250 DATE_TRUNC('month', b.booking Date) AS month,251 a. Name AS airport_name,252 COUNT(b. BookingID) AS departures_count,253 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking Date) ORDER BY COUNT(b.BookingID) DESC) AS rank228 SELECT 229 DATE_TRUNC('month', b.booking_date) AS month, 230 a.name AS airport_name, 231 COUNT(b.bookingid) AS departures_count, 232 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank 254 233 FROM Booking b 255 JOIN Flight f ON b. FlightID = f.FlightID256 JOIN Airport a ON f. DepartureAirport = a.AirportID257 GROUP BY month, a. Name234 JOIN Flight f ON b.flightid = f.flightid 235 JOIN Airport a ON f.departure_airport = a.airportid 236 GROUP BY month, a.name 258 237 ) 259 238 260 239 SELECT month, 'Destination' AS category, destination_name AS name, bookings_count AS count FROM MonthlyBookings WHERE rank <= 3 261 240 UNION ALL 262 SELECT month, 'Flight' AS category, FlightNumber AS name, bookings_count AS count FROM MonthlyFlights WHERE rank <= 3241 SELECT month, 'Flight' AS category, flight_number AS name, bookings_count AS count FROM MonthlyFlights WHERE rank <= 3 263 242 UNION ALL 264 243 SELECT month, 'Airport' AS category, airport_name AS name, departures_count AS count FROM MonthlyAirports WHERE rank <= 3
