Changes between Initial Version and Version 1 of AdvancedReport12

Timestamp:

08/21/26 06:24:50 (29 hours ago)

Author:

235018

Comment:

--

AdvancedReport12

@@ +1 @@
+= List of clients who haven't made an order
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_clients_without_orders()
+RETURNS TABLE (
+    client_id INT,
+    client_name TEXT,
+    email TEXT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        c.client_id,
+        c.name AS client_name,
+        c.email
+    FROM client c
+    LEFT JOIN makes_order mo
+        ON c.client_id = mo.client_id
+    WHERE mo.order_num IS NULL
+    ORDER BY c.client_id;
+END;
+$$;
+}}}
+
+== Relational Algebra
+- C(client_id, name, first_name, last_name, email, password, delivery_address)
+- MO(client_id, order_num)
+
+**JOIN clients with their orders:**
+- J1 ← C ⟕C.client_id = MO.client_id MO
+
+**SELECT only clients who aren't connected to any orders:**
+- F1 ← σorder_num IS NULL(J1)
+
+**Sort by client ID:**
+- R_final ← τclient_id ASC(F1)
+
+