Changes between Initial Version and Version 1 of AdvancedReport16

Timestamp:

08/21/26 06:49:12 (28 hours ago)

Author:

235018

Comment:

--

AdvancedReport16

@@ +1 @@
+= Each store's average pay
+{{{#!sql
+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)
+
+