Changes between Version 1 and Version 2 of UseCase13

Timestamp:

09/22/26 15:48:18 (21 hours ago)

Author:

236021

Comment:

--

UseCase13

@@ -16 +16 @@
 JOIN lab_tests lt ON plt.test_id = lt.test_id
 WHERE plt.patient_id = (
-SELECT patient_id 
-FROM users 
-WHERE username = 'maja.veljanova')
+    SELECT p.patient_id
+    FROM patients p
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456')
   AND plt.test_date = '2026-06-05';
 }}}
@@ -26 +27 @@
 {{{
 #!sql
-INSERT INTO billing (total_cost, payment_status, record_id, admin_id)
+INSERT INTO billing (bill_id, total_cost, payment_status, record_id, admin_id)
 VALUES (
-  75.00,
+  (SELECT COALESCE(MAX(bill_id), 0) + 1 FROM billing),
+  30.00,
   'PENDING',
-  (SELECT record_id FROM medical_records WHERE patient_id = (
-SELECT patient_id 
-FROM users 
-WHERE username = 'maja.veljanova')),
-  (
-SELECT admin_id 
-FROM admin LIMIT 1)
+  (SELECT mr.record_id
+   FROM medical_records mr
+   JOIN patients p ON mr.patient_id = p.patient_id
+   JOIN users u ON u.user_id = p.user_id
+   WHERE u.username = '1402994123456'),
+  (SELECT admin_id FROM admin LIMIT 1)
 )
 RETURNING bill_id;
@@ -48 +49 @@
 VALUES (
   (SELECT MAX(bill_id) FROM billing),
-  (SELECT test_id FROM lab_tests WHERE test_name = 'Complete Blood Count')
+  (SELECT test_id FROM lab_tests WHERE test_name = 'Blood test')
 );
 }}}
@@ -76 +77 @@
 JOIN medical_records mr ON b.record_id = mr.record_id
 JOIN patients p ON mr.patient_id = p.patient_id
-JOIN users u_p ON p.patient_id = u_p.patient_id
+JOIN users u_p ON u_p.user_id = p.user_id
 WHERE b.payment_status = 'PENDING'
 ORDER BY b.bill_id;
@@ -99 +100 @@
 JOIN medical_records mr ON b.record_id = mr.record_id
 JOIN patients p ON mr.patient_id = p.patient_id
-JOIN users u_p ON p.patient_id = u_p.patient_id
+JOIN users u_p ON u_p.user_id = p.user_id
 LEFT JOIN billing_lab_tests blt ON b.bill_id = blt.bill_id
 LEFT JOIN lab_tests lt ON blt.test_id = lt.test_id