Analytical SQL Views

Description

This section contains SQL views used for reporting and analytical summaries.

1. vw_wedding_financial_summary

Description

Displays wedding budget and total booking expenses.

SQL Code

<pre> CREATE OR REPLACE VIEW vw_wedding_financial_summary AS SELECT

w.wedding_id, w.budget, calculate_wedding_total_cost(w.wedding_id) AS total_cost, w.budget - calculate_wedding_total_cost(w.wedding_id) AS remaining_budget

FROM wedding w; </pre>

2. vw_rsvp_overview

Description

Displays RSVP statistics for guests and events.

SQL Code

<pre> CREATE OR REPLACE VIEW vw_rsvp_overview AS SELECT

e.event_id, e.event_type, r.status, COUNT(*) AS total

FROM event_rsvp r JOIN event e ON r.event_id = e.event_id GROUP BY e.event_id, e.event_type, r.status; </pre>

3. vw_vendor_booking_overview

Description

Displays venue, photographer, and band bookings.

SQL Code

<pre> CREATE OR REPLACE VIEW vw_vendor_booking_overview AS SELECT

w.wedding_id, v.name AS venue_name, p.name AS photographer_name, b.band_name

FROM wedding w LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id LEFT JOIN venue v ON vb.venue_id = v.venue_id LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id LEFT JOIN band b ON bb.band_id = b.band_id; </pre>

4. vw_upcoming_weddings

Description

Displays upcoming weddings scheduled in the future.

SQL Code

<pre> CREATE OR REPLACE VIEW vw_upcoming_weddings AS SELECT

wedding_id, wedding_date, budget, status

FROM wedding WHERE wedding_date >= CURRENT_DATE; </pre>