Changes between Version 2 and Version 3 of UseCase06

Timestamp:

09/22/26 14:44:04 (22 hours ago)

Author:

236021

Comment:

--

UseCase06

@@ -18 +18 @@
 FROM medical_records mr
 WHERE mr.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'
 );
 }}}
@@ -39 +42 @@
 {{{
 #!sql
-INSERT INTO performed_lab_tests (patient_id, doctor_id, technician_id, test_id, test_date, notes)
+INSERT INTO performed_lab_tests (performed_test_id, patient_id, doctor_id, technician_id, test_id, test_date, notes)
 VALUES (
-  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
-  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
+  (SELECT COALESCE(MAX(performed_test_id), 0) + 1 FROM performed_lab_tests),
+  (SELECT p.patient_id
+   FROM patients p
+   JOIN users u ON u.user_id = p.user_id
+   WHERE u.username = '1402994123456'),
+  (SELECT d.doctor_id
+   FROM doctors d
+   JOIN users u ON u.user_id = d.user_id
+   WHERE u.username = 'elena.kirova@medora.com'),
   (SELECT technician_id FROM lab_technician WHERE username = 'lab_marina'),
-  (SELECT test_id FROM lab_tests WHERE test_name = 'Complete Blood Count'),
+  (SELECT test_id FROM lab_tests WHERE test_name = 'Blood test'),
   '2026-06-05',
   'Blood work for hypertension monitoring'
@@ -59 +69 @@
 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';
 }}}
@@ -69 +81 @@
 {{{
 #!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 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)
 )
@@ -86 +103 @@
 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')
 );
 }}}
@@ -102 +119 @@
 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'
+);
 }}}