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

Timestamp:

02/05/25 01:16:25 (9 months ago)

Author:

173067

Comment:

--

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

@@ +1 @@
+== Напредни извештаи од базата
+
+* Извештај за најпопуларни летови по резервација
+
+{{{
+
+CREATE VIEW MostPopularFlightsReport AS
+SELECT 
+    f.FlightID, 
+    f.FlightNumber, 
+    f.DepartureAirport, 
+    f.ArrivalAirport, 
+    COUNT(b.BookingID) AS NumberOfBookings
+FROM 
+    Flight f
+JOIN 
+    Booking b ON f.FlightID = b.FlightID
+GROUP BY 
+    f.FlightID, f.FlightNumber, f.DepartureAirport, f.ArrivalAirport
+ORDER BY 
+    NumberOfBookings DESC;
+
+}}}
+
+* Извештај за просечен рејтинг по дестинација
+
+{{{
+
+CREATE VIEW AverageDestinationRatingReport AS
+SELECT 
+    d.DestinationID, 
+    d.Name AS DestinationName, 
+    AVG(r.Rating) AS AverageRating
+FROM 
+    Review r
+JOIN 
+    Destination d ON r.TargetID = d.DestinationID
+GROUP BY 
+    d.DestinationID, d.Name
+ORDER BY 
+    AverageRating DESC;
+
+}}}
+
+* Извештај за историја на резервации на корисници со вкупни трошоци
+
+{{{
+
+CREATE VIEW UserBookingExpenditureReport AS
+SELECT 
+    u.UserID, 
+    u.Name, 
+    u.Surname, 
+    COUNT(b.BookingID) AS NumberOfBookings, 
+    SUM(b.TotalCost) AS TotalExpenditure
+FROM 
+    ApplicationUser u
+JOIN 
+    Booking b ON u.UserID = b.UserID
+GROUP BY 
+    u.UserID, u.Name, u.Surname
+ORDER BY 
+    TotalExpenditure DESC;
+
+}}}
+
+* Извештај за резервации со статус 'Pending'
+
+{{{
+
+CREATE VIEW PendingPaymentBookingsReport AS
+SELECT 
+    b.BookingID, 
+    u.Name AS UserName, 
+    u.Surname AS UserSurname, 
+    f.FlightNumber, 
+    f.DepartureAirport, 
+    f.ArrivalAirport, 
+    b.BookingDate, 
+    b.TotalCost
+FROM 
+    Booking b
+JOIN 
+    ApplicationUser u ON b.UserID = u.UserID
+JOIN 
+    Flight f ON b.FlightID = f.FlightID
+WHERE 
+    b.PaymentStatus = 'Pending';
+
+}}}
+
+* Извештај за топ 5 дестинации според Wishlist
+
+{{{
+
+CREATE VIEW TopDestinationsWishlistReport AS
+SELECT 
+    d.DestinationID, 
+    d.Name AS DestinationName, 
+    d.Country, 
+    COUNT(w.WishlistID) AS NumberOfWishlistEntries
+FROM 
+    Wishlist w
+JOIN 
+    Destination d ON w.TargetID = d.DestinationID
+GROUP BY 
+    d.DestinationID, d.Name, d.Country
+ORDER BY 
+    NumberOfWishlistEntries DESC
+LIMIT 5;
+
+}}}
+
+* Извештај за корисници кои имаат највеќе резервации
+
+{{{
+
+CREATE VIEW TopUsersByBookings AS
+SELECT 
+    u.UserID, 
+    u.Name, 
+    u.Surname, 
+    COUNT(b.BookingID) AS NumberOfBookings
+FROM 
+    ApplicationUser u
+JOIN 
+    Booking b ON u.UserID = b.UserID
+GROUP BY 
+    u.UserID, u.Name, u.Surname
+ORDER BY 
+    NumberOfBookings DESC;
+
+}}}
+
+* Извештај за корисници со највеќе рецензии
+
+{{{
+
+CREATE VIEW FrequentFlightReviewers AS
+SELECT 
+    u.UserID, 
+    u.Name, 
+    u.Surname, 
+    COUNT(r.ReviewID) AS NumberOfReviews
+FROM 
+    Review r
+JOIN 
+    ApplicationUser u ON r.UserID = u.UserID
+WHERE 
+    r.TargetID IN (SELECT FlightID FROM Flight)
+GROUP BY 
+    u.UserID, u.Name, u.Surname
+ORDER BY 
+    NumberOfReviews DESC;
+
+}}}
+
+* Извештај за следни летови
+
+{{{
+
+CREATE VIEW UpcomingFlightsWithAvailability AS
+SELECT 
+    f.FlightID, 
+    f.FlightNumber, 
+    f.DepartureAirport, 
+    f.ArrivalAirport, 
+    f.DepartureTime, 
+    f.ArrivalTime, 
+    f.AvailableSeats
+FROM 
+    Flight f
+WHERE 
+    f.DepartureTime > NOW() AND f.AvailableSeats > 0
+ORDER BY 
+    f.DepartureTime;
+
+}}}
+
+* Извештај за најдобри начини на плаќање според вкупната потрошувачка
+
+{{{
+
+CREATE VIEW PaymentMethodSpendReport AS
+SELECT 
+    p.PaymentMethod, 
+    SUM(p.Amount) AS TotalAmountSpent
+FROM 
+    Payment p
+GROUP BY 
+    p.PaymentMethod
+ORDER BY 
+    TotalAmountSpent DESC;
+
+}}}
+
+* Извештај за резервација на летови по град на поаѓање
+
+{{{
+
+CREATE VIEW FlightBookingByCity AS
+SELECT 
+    a.CityID, 
+    c.Name AS CityName, 
+    COUNT(b.BookingID) AS NumberOfBookings
+FROM 
+    Booking b
+JOIN 
+    Flight f ON b.FlightID = f.FlightID
+JOIN 
+    Airport a ON f.DepartureAirport = a.AirportID
+JOIN 
+    City c ON a.CityID = c.CityID
+GROUP BY 
+    a.CityID, c.Name
+ORDER BY 
+    NumberOfBookings DESC;
+
+}}}