SQLize | PHPize | SQLtest

-- إنشاء جدول التخصصات CREATE TABLE Specialties ( specialty_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE ); -- إنشاء جدول المرضى CREATE TABLE Patients ( patient_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, gender CHAR(1), birthdate DATE NOT NULL, phone VARCHAR(15) UNIQUE, address VARCHAR(100) ); -- إنشاء جدول الأطباء CREATE TABLE Doctors ( doctor_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, phone VARCHAR(15) UNIQUE, specialty_id INT NOT NULL, FOREIGN KEY (specialty_id) REFERENCES Specialties(specialty_id) ON DELETE CASCADE ); -- إنشاء جدول الأدوية CREATE TABLE Medications ( medication_id INT PRIMARY KEY, medication_name VARCHAR(100) NOT NULL, description TEXT ); -- إنشاء جدول المواعيد CREATE TABLE Appointments ( appointment_id INT PRIMARY KEY, patient_id INT NOT NULL, doctor_id INT NOT NULL, appointment_date DATE NOT NULL, appointment_time TIME NOT NULL, status VARCHAR(20) DEFAULT 'Scheduled', FOREIGN KEY (patient_id) REFERENCES Patients(patient_id) ON DELETE CASCADE, FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id) ON DELETE CASCADE ); -- إنشاء جدول الفواتير CREATE TABLE Invoices ( invoice_id INT PRIMARY KEY, appointment_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, payment_date DATE, payment_status VARCHAR(20) DEFAULT 'Unpaid', FOREIGN KEY (appointment_id) REFERENCES Appointments(appointment_id) ON DELETE CASCADE ); -- إنشاء جدول الوصفات CREATE TABLE Prescriptions ( prescription_id INT PRIMARY KEY, appointment_id INT NOT NULL, medication_id INT NOT NULL, dosage VARCHAR(50) NOT NULL, instructions TEXT, FOREIGN KEY (appointment_id) REFERENCES Appointments(appointment_id) ON DELETE CASCADE, FOREIGN KEY (medication_id) REFERENCES Medications(medication_id) ); -- إدخال بيانات الجداول INSERT INTO Specialties VALUES (1, 'General Medicine'), (2, 'ENT'), (3, 'Ophthalmology'), (4, 'Psychiatry'), (5, 'Urology'); INSERT INTO Patients VALUES (1, 'Ahmed', 'Ali', 'M', '1990-03-15', '+249912345678', 'Northern State'), (2, 'Fatima', 'Mohamed', 'F', '1988-06-21', '+249998765432', 'Northern State'), (3, 'Maryam', 'Hussain', 'F', '1995-11-05', '+249923456789', 'Northern State'), (4, 'Amina', 'Abdallah', 'F', '1992-12-09', '+249911122233', 'Northern State'), (5, 'Ali', 'Osman', 'M', '1987-01-25', '+249909988776', 'Northern State'); INSERT INTO Doctors VALUES (1, 'Adel', 'Osman', '+249912233445', 1), (2, 'Ayman', 'Khalid', '+249923344556', 2), (3, 'Hiba', 'Nasser', '+249934455667', 3), (4, 'Layan', 'Ali', '+249945566778', 4), (5, 'Muna', 'Hassan', '+249956677889', 5); INSERT INTO Medications VALUES (1, 'Paracetamol', 'Pain relief and fever reducer'), (2, 'Amoxicillin', 'Antibiotic used for infections'), (3, 'Ibuprofen', 'Anti-inflammatory painkiller'), (4, 'Cetirizine', 'Used for allergies'), (5, 'Omeprazole', 'Used for stomach acid control'); INSERT INTO Appointments VALUES (1, 1, 1, '2020-06-01', '08:00:00', 'Completed'), (2, 2, 2, '2020-06-02', '09:30:00', 'Scheduled'), (3, 3, 3, '2020-06-03', '11:45:00', 'Cancelled'), (4, 4, 4, '2020-06-04', '14:00:00', 'Scheduled'), (5, 5, 5, '2020-06-05', '16:15:00', 'Scheduled'); INSERT INTO Invoices VALUES (1, 1, 10.00, '2020-06-01', 'Paid'), (2, 2, 20.00, NULL, 'Unpaid'), (3, 3, 30.00, '2020-06-03', 'Paid'), (4, 4, 40.00, NULL, 'Pending'), (5, 5, 50.00, NULL, 'Unpaid'); INSERT INTO Prescriptions VALUES (1, 1, 1, '1 tablet every 6 hours', 'Take with water'), (2, 1, 2, '2 tablets daily', 'Before sleeping'), (3, 2, 3, 'Half tablet once', 'Use only if needed'), (4, 4, 4, '1 drop per eye', 'Use in the morning'), (5, 5, 5, '10ml syrup', 'After each meal'); -- عمليات إضافية INSERT INTO Patients VALUES (6, 'Sara', 'Mohamed', 'F', '1998-08-08', '+249900112233', 'Khartoum'); INSERT INTO Appointments VALUES (6, 6, 1, '2020-06-06', '10:00:00', 'Scheduled'); INSERT INTO Invoices VALUES (6, 6, 60.00, NULL, 'Unpaid'); UPDATE Patients SET phone = '+249900999999' WHERE patient_id = 1; UPDATE Doctors SET specialty_id = 3 WHERE doctor_id = 2; DELETE FROM Appointments WHERE status = 'Cancelled'; BEGIN; INSERT INTO Invoices VALUES (7, 2, 100.00, NULL, 'Unpaid'); ROLLBACK; INSERT INTO Invoices VALUES (7, 2, 100.00, '2020-06-06', 'Paid'); COMMIT; -- إنشاء فهارس CREATE INDEX idx_doctor_name ON Doctors(first_name, last_name); CREATE INDEX idx_patient_phone ON Patients(phone); -- إنشاء View لمواعيد اليوم CREATE VIEW TodayAppointments AS SELECT a.appointment_date, a.appointment_time, p.first_name AS patient, d.first_name AS doctor FROM Appointments a JOIN Patients p ON a.patient_id = p.patient_id JOIN Doctors d ON a.doctor_id = d.doctor_id WHERE a.appointment_date = CURDATE(); -- إنشاء Sequence للمريض CREATE SEQUENCE seq_patient_id START WITH 1001 INCREMENT BY 1; -- استخدام Sequence INSERT INTO Patients ( patient_id, first_name, last_name, gender, birthdate, phone, address ) VALUES ( NEXTVAL(seq_patient_id), 'New', 'Patient', 'F', '1999-09-09', '+249901234567', 'Khartoum' ); -- استعلامات تحليلية SELECT d.first_name || ' ' || d.last_name AS doctor_name, COUNT(a.appointment_id) AS appointment_count FROM Doctors d LEFT JOIN Appointments a ON d.doctor_id = a.doctor_id GROUP BY d.doctor_id; SELECT p.first_name || ' ' || p.last_name AS patient_name, SUM(i.amount) AS total_payment FROM Patients p JOIN Appointments a ON p.patient_id = a.patient_id JOIN Invoices i ON a.appointment_id = i.appointment_id GROUP BY p.patient_id; SELECT COUNT(*) AS total_patients FROM Patients; -- استعلامات متقدمة SELECT p.patient_id, p.first_name, p.last_name, COUNT(a.appointment_id) AS total_appointments FROM Patients p JOIN Appointments a ON p.patient_id = a.patient_id GROUP BY p.patient_id HAVING COUNT(a.appointment_id) > 3; SELECT d.doctor_id, d.first_name, d.last_name FROM Doctors d WHERE d.doctor_id NOT IN ( SELECT doctor_id FROM Appointments WHERE appointment_date BETWEEN CURRENT_DATE - INTERVAL '7 DAY' AND CURRENT_DATE ); SELECT p.patient_id, p.first_name, p.last_name FROM Patients p JOIN Appointments a ON p.patient_id = a.patient_id JOIN Prescriptions pr ON a.appointment_id = pr.appointment_id GROUP BY p.patient_id HAVING COUNT(DISTINCT a.doctor_id) > 1; SELECT p.patient_id, p.first_name, p.last_name FROM Patients p WHERE p.patient_id NOT IN ( SELECT DISTINCT a.patient_id FROM Appointments a JOIN Prescriptions pr ON a.appointment_id = pr.appointment_id ); -- دوال وفرز وفلترة SELECT UPPER(first_name) AS first_name_upper, UPPER(last_name) AS last_name_upper FROM Patients; SELECT first_name, last_name, FLOOR(DATEDIFF(CURDATE(), birthdate) / 365) AS age FROM Patients; SELECT appointment_id, DATE_FORMAT(appointment_date, '%W %d %M %Y') AS formatted_date, appointment_time FROM Appointments; SELECT * FROM Appointments WHERE appointment_date = '2020-06-02'; SELECT * FROM Patients WHERE YEAR(birthdate) > 1990; SELECT * FROM Doctors ORDER BY first_name; SELECT d.doctor_id, d.first_name, d.last_name, s.name AS specialty FROM Doctors d JOIN Specialties s ON d.specialty_id = s.specialty_id ORDER BY s.name;