Context Navigation

Phase 6 – Complex DB Reports (SQL, Stored Procedures, Relational Algebra)

Overview

In this phase we demonstrate how to extract, analyze and summarize data from the Wedding Planner database using advanced SQL. We focus on generating complex reports across multiple related tables, and implementing reusable logic through stored procedures and views.

These techniques are important because in a real system the database is not used only for storing data, but also for producing meaningful insights (guest statistics, cost reports, utilization reports, timelines, etc.).

What we cover in this phase

Complex reports using JOINs, GROUP BY and aggregate functions
Stored procedures for reusable database logic
Views for simplified access to frequently needed reports
Relational Algebra (theoretical foundation behind SQL)
Grouping and filtering techniques
Cost analysis and capacity utilization examples

Implementation / Examples

1) Complex Reports

Complex reports combine data from multiple tables and return analytical information that is useful for managing weddings. In our Wedding Planner system, reports are useful for guest status tracking, cost breakdown, scheduling and venue usage.

Example 1: Total guests and RSVP status per wedding

This query returns the total number of guests per wedding, and how many RSVPs are CONFIRMED vs CANCELLED.

SELECT 
    w.wedding_id,
    w.date AS wedding_date,
    COUNT(DISTINCT g.guest_id) AS total_guests,
    COUNT(CASE WHEN r.status = 'CONFIRMED' THEN 1 END) AS confirmed_guests,
    COUNT(CASE WHEN r.status = 'CANCELLED' THEN 1 END) AS cancelled_guests
FROM wedding w
LEFT JOIN guest g ON w.wedding_id = g.wedding_id
LEFT JOIN event e ON w.wedding_id = e.wedding_id
LEFT JOIN event_rsvp r ON e.event_id = r.event_id AND g.guest_id = r.guest_id
GROUP BY w.wedding_id, w.date;

*Purpose:* Helps the wedding owner see how many guests are invited and their RSVP status.

Example 2: Costs per wedding (venue + band + photographer)

This report computes the cost of a wedding by summing the cost of booked providers.

SELECT 
    w.wedding_id,
    w.date,
    COALESCE(SUM(vb.price), 0) AS venue_cost,
    COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0) AS band_cost,
    COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0) AS photographer_cost
FROM wedding w
LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
LEFT JOIN band b ON bb.band_id = b.band_id
LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
GROUP BY w.wedding_id, w.date;

*Purpose:* Gives a clear financial summary for budgeting and comparing providers.

Example 3: Venue utilization (capacity report)

This query checks how many guests are seated, and how many free seats are still available at the booked venue.

SELECT
    v.name AS venue_name,
    v.capacity,
    w.wedding_id,
    COUNT(a.guest_id) AS seated_guests,
    (v.capacity - COUNT(a.guest_id)) AS free_seats
FROM venue v
JOIN venue_booking vb ON v.venue_id = vb.venue_id
JOIN wedding w ON vb.wedding_id = w.wedding_id
LEFT JOIN attendance a ON a.event_id IN (
    SELECT event_id FROM event WHERE wedding_id = w.wedding_id
)
GROUP BY v.name, v.capacity, w.wedding_id;

*Purpose:* Useful to see if the wedding has exceeded the venue capacity or still has room.

Example 4: Temporal distribution of events

This query returns the schedule of events ordered by date and time.

SELECT
    w.wedding_id,
    e.event_type,
    e.date,
    e.start_time,
    e.end_time,
    EXTRACT(EPOCH FROM (e.end_time - e.start_time))/3600 AS duration_hours
FROM wedding w
JOIN event e ON w.wedding_id = e.wedding_id
ORDER BY e.date, e.start_time;

*Purpose:* Helps planning timelines and checking event overlaps in the schedule.

2) Stored Procedures

Stored procedures allow us to encapsulate database logic into reusable functions that can be called when needed. They reduce repetition and help organize logic for reporting and automation.

Example 1: Total wedding cost

This procedure calculates the full total cost of a wedding (venue + band + photographer).

CREATE OR REPLACE PROCEDURE wedding_total_cost(
    IN p_wedding_id INT,
    OUT total_cost NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT 
        COALESCE(SUM(vb.price),0)
      + COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour),0)
      + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour),0)
    INTO total_cost
    FROM wedding w
    LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
    LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
    LEFT JOIN band b ON bb.band_id = b.band_id
    LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
    LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
    WHERE w.wedding_id = p_wedding_id;
END;
$$;