Changes between Version 1 and Version 2 of UseCase10
- Timestamp:
- 09/22/26 15:18:11 (22 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
UseCase10
v1 v2 18 18 p.embg 19 19 FROM patients p 20 JOIN users u_p ON p.patient_id = u_p.patient_id21 WHERE u_p.username = ' maja.veljanova';20 JOIN users u_p ON u_p.user_id = p.user_id 21 WHERE u_p.username = '1402994123456'; 22 22 }}} 23 23 … … 33 33 d.department_id 34 34 FROM doctors d 35 JOIN doctor_specialization ds 35 JOIN doctor_specialization ds 36 36 ON d.specialization_id = ds.specialization_id 37 JOIN users u_d 38 ON d.doctor_id = u_d.doctor_id39 WHERE u_d.username = 'elena.kirova ';37 JOIN users u_d 38 ON u_d.user_id = d.user_id 39 WHERE u_d.username = 'elena.kirova@medora.com'; 40 40 }}} 41 41 … … 47 47 FROM appointments a 48 48 WHERE a.doctor_id = ( 49 SELECT doctor_id 50 FROM users 51 WHERE username = 'elena.kirova') 49 SELECT d.doctor_id 50 FROM doctors d 51 JOIN users u ON u.user_id = d.user_id 52 WHERE u.username = 'elena.kirova@medora.com') 52 53 AND a.appointment_date = '2026-06-12' 53 54 AND a.status != 'CANCELLED'; … … 60 61 SELECT 1 61 62 FROM appointments a 62 WHERE a.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova') 63 AND a.doctor_id = (SELECT doctor_id FROM users WHERE username = 'elena.kirova') 63 WHERE a.patient_id = ( 64 SELECT p.patient_id 65 FROM patients p 66 JOIN users u ON u.user_id = p.user_id 67 WHERE u.username = '1402994123456') 68 AND a.doctor_id = ( 69 SELECT d.doctor_id 70 FROM doctors d 71 JOIN users u ON u.user_id = d.user_id 72 WHERE u.username = 'elena.kirova@medora.com') 64 73 AND a.appointment_date = '2026-06-12' 65 74 AND a.appointment_time = '10:00' … … 71 80 {{{ 72 81 #!sql 73 INSERT INTO appointments ( patient_id, doctor_id, appointment_date, appointment_time, status)82 INSERT INTO appointments (appointment_id, patient_id, doctor_id, appointment_date, appointment_time, status) 74 83 VALUES ( 75 (SELECT patient_id FROM users WHERE username = 'maja.veljanova'), 76 (SELECT doctor_id FROM users WHERE username = 'elena.kirova'), 84 (SELECT COALESCE(MAX(appointment_id), 0) + 1 FROM appointments), 85 (SELECT p.patient_id 86 FROM patients p 87 JOIN users u ON u.user_id = p.user_id 88 WHERE u.username = '1402994123456'), 89 (SELECT d.doctor_id 90 FROM doctors d 91 JOIN users u ON u.user_id = d.user_id 92 WHERE u.username = 'elena.kirova@medora.com'), 77 93 '2026-06-12', 78 94 '10:00', … … 97 113 FROM appointments a 98 114 JOIN doctors d ON a.doctor_id = d.doctor_id 99 JOIN users u_d ON d.doctor_id = u_d.doctor_id115 JOIN users u_d ON u_d.user_id = d.user_id 100 116 JOIN patients p ON a.patient_id = p.patient_id 101 JOIN users u_p ON p.patient_id = u_p.patient_id117 JOIN users u_p ON u_p.user_id = p.user_id 102 118 WHERE a.appointment_id = (SELECT MAX(appointment_id) FROM appointments); 103 119 }}} … … 116 132 FROM appointments a 117 133 JOIN doctors d ON a.doctor_id = d.doctor_id 118 JOIN users u_d ON d.doctor_id = u_d.doctor_id134 JOIN users u_d ON u_d.user_id = d.user_id 119 135 WHERE a.patient_id = ( 120 SELECT patient_id 121 FROM users 122 WHERE username = 'maja.veljanova') 136 SELECT p.patient_id 137 FROM patients p 138 JOIN users u ON u.user_id = p.user_id 139 WHERE u.username = '1402994123456') 123 140 AND a.appointment_date >= CURRENT_DATE 124 141 ORDER BY a.appointment_date ASC, a.appointment_time ASC;
