Each store's average pay

CREATE OR REPLACE FUNCTION get_stores_average_pay()
RETURNS TABLE (
    store_id INT,
    store_name TEXT,
    average_pay NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.store_id,
        s.name AS store_name,
        COALESCE(AVG(w.wage), 0) AS average_pay
    FROM store s
    LEFT JOIN worked w
        ON s.store_id = w.store_id
    GROUP BY
        s.store_id,
        s.name
    ORDER BY
        average_pay DESC;
END;
$$;

Relational Algebra

• S(store_id, name, date_of_founding, physical_address, store_email, rating)
• W(id, date, store_ID, week, total_week, pay_method, wage, working_hours)

JOIN stores with employee records:

• J1 ← S ⟕S.store_id = W.store_ID W

Calculate average wage for each store:

• R ← γstore_id, name;

AVG(wage) → average_pay (J1)

Sort by average wage:

• R_final ← τaverage_pay DESC(R)