Changes between Version 2 and Version 3 of UseCase11

Timestamp:

09/22/26 15:28:34 (22 hours ago)

Author:

236021

Comment:

--

UseCase11

@@ -17 +17 @@
 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'
 );
 }}}
@@ -33 +36 @@
 FROM doctors d
 JOIN doctor_specialization ds ON d.specialization_id = ds.specialization_id
-JOIN users u_d ON d.doctor_id = u_d.doctor_id
+JOIN users u_d ON u_d.user_id = d.user_id
 ORDER BY ds.specialization_name, u_d.last_name;
 }}}
@@ -41 +44 @@
 {{{
 #!sql
-INSERT INTO referrals (record_id, from_doctor_id, to_doctor_id, reason, referral_date)
+INSERT INTO referrals (referral_id, record_id, from_doctor_id, to_doctor_id, reason, referral_date)
 VALUES (
-  (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
-  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
-  (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov'),
+  (SELECT COALESCE(MAX(referral_id), 0) + 1 FROM referrals),
+  (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 d.doctor_id
+   FROM doctors d
+   JOIN users u ON u.user_id = d.user_id
+   WHERE u.username = 'elena.kirova@medora.com'),
+  (SELECT d.doctor_id
+   FROM doctors d
+   JOIN users u ON u.user_id = d.user_id
+   WHERE u.username = 'nikola.trajkov@medora.com'),
   'Cardiology consultation for hypertension management',
   '2026-05-30'
@@ -56 +70 @@
 {{{
 #!sql
-INSERT INTO appointments (patient_id, doctor_id, appointment_date, appointment_time, status)
+INSERT INTO appointments (appointment_id, patient_id, doctor_id, appointment_date, appointment_time, status)
 VALUES (
-  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
-  (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov'),
-  '2026-06-15',
+  (SELECT COALESCE(MAX(appointment_id), 0) + 1 FROM appointments),
+  (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 = 'nikola.trajkov@medora.com'),
+  '2026-10-20',
   '14:00',
   'SCHEDULED'
@@ -83 +104 @@
 FROM referrals r
 JOIN doctors d_from ON r.from_doctor_id = d_from.doctor_id
-JOIN users u_from ON d_from.doctor_id = u_from.doctor_id
+JOIN users u_from ON u_from.user_id = d_from.user_id
 JOIN doctors d_to ON r.to_doctor_id = d_to.doctor_id
-JOIN users u_to ON d_to.doctor_id = u_to.doctor_id
-JOIN appointments a ON a.patient_id = r.record_id AND a.doctor_id = r.to_doctor_id
+JOIN users u_to ON u_to.user_id = d_to.user_id
+JOIN medical_records mr2 ON mr2.record_id = r.record_id
+JOIN appointments a ON a.patient_id = mr2.patient_id AND a.doctor_id = r.to_doctor_id
 WHERE r.referral_id = (SELECT MAX(referral_id) FROM referrals)
 ORDER BY a.appointment_date DESC
@@ -105 +127 @@
 JOIN medical_records mr ON r.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
-WHERE r.to_doctor_id = (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov')
+JOIN users u_p ON u_p.user_id = p.user_id
+WHERE r.to_doctor_id = (
+    SELECT d.doctor_id
+    FROM doctors d
+    JOIN users u ON u.user_id = d.user_id
+    WHERE u.username = 'nikola.trajkov@medora.com')
 ORDER BY r.referral_date DESC;
 }}}