SQLize | PHPize | SQLtest

-- جدول الدكاترة CREATE TABLE Doctors( DoctorID INT PRIMARY KEY, DoctorName VARCHAR(50) NOT NULL, Specialty VARCHAR(50) ); -- جدول التخصصات CREATE TABLE Specialties( SpecialtyID INT PRIMARY KEY, SpecialtyName VARCHAR(50) NOT NULL ); -- جدول المرضى CREATE TABLE Patients ( PatientID INT PRIMARY KEY, PatientName VARCHAR(50) NOT NULL, BirthDate DATE, DoctorID INT, Phone VARCHAR(20), FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID) ); -- جدول الادوية CREATE TABLE Medications( MedicationID INT PRIMARY KEY, MedicationName VARCHAR(50) ); -- جدول الوصفات CREATE TABLE Prescriptions( PrescriptionID INT PRIMARY KEY, PatientID INT, MedicationID INT, Dosage VARCHAR(50), FOREIGN KEY (PatientID) REFERENCES Patients(PatientID), FOREIGN KEY (MedicationID) REFERENCES Medications(MedicationID) ); -- جدول المواعيد CREATE TABLE Appointments( AppointmentID INT PRIMARY KEY, PatientID INT, DoctorID INT, AppointmentDate DATE, FOREIGN KEY (PatientID) REFERENCES Patients(PatientID), FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID) ); -- جدول الفواتير CREATE TABLE Invoices( InvoiceID INT PRIMARY KEY, PatientID INT, InvoiceDate DATE, Amount INT, FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) ); -- بيانات الدكاترة INSERT INTO Doctors VALUES (1,'Dr.Alsir','General Medicine'); INSERT INTO Doctors VALUES (2,'Dr.Awab','General Surgery'); INSERT INTO Doctors VALUES (3,'Dr.Mayada','Pediatrics'); INSERT INTO Doctors VALUES (4,'Dr.Moddther','Internal Medicine'); INSERT INTO Doctors VALUES (5,'Dr.Fatima','Obstetrics and Gynecology'); -- التخصصات INSERT INTO Specialties VALUES (1,'General Surgery'); INSERT INTO Specialties VALUES (2,'General Medicine'); INSERT INTO Specialties VALUES (3,'Pediatrics'); INSERT INTO Specialties VALUES (4,'Internal Medicine'); INSERT INTO Specialties VALUES (5,'Obstetrics and Gynecology'); INSERT INTO Specialties VALUES (6,'Dentistry'); -- المرضى INSERT INTO Patients VALUES(1,'HamedAhmed',TO_DATE('1997-01-01','YYYY-MM-DD'),1,'0115452781'); INSERT INTO Patients VALUES(2,'Mohammed Sadig',TO_DATE('2003-06-15','YYYY-MM-DD'),2,'0907885682'); INSERT INTO Patients VALUES(3,'Omina Mohammed',TO_DATE('2009-03-20','YYYY-MM-DD'),1,'0123729078'); INSERT INTO Patients VALUES(4,'Saher Isam',TO_DATE('2000-09-10','YYYY-MM-DD'),4,'0936511039'); INSERT INTO Patients VALUES(5,'Hassen Ahmed',TO_DATE('2015-02-05','YYYY-MM-DD'),3,'0900451337'); -- المواعيد INSERT INTO Appointments VALUES(1,1,1,DATE '2025-01-01'); INSERT INTO Appointments VALUES(2,2,2,DATE '2025-01-02'); INSERT INTO Appointments VALUES(3,3,1,DATE '2025-01-03'); INSERT INTO Appointments VALUES(4,4,4,DATE '2025-01-04'); INSERT INTO Appointments VALUES(5,5,3,DATE '2025-01-05'); -- الفواتير INSERT INTO Invoices VALUES(1,2,TO_DATE('2025-01-02','YYYY-MM-DD'),30000); INSERT INTO Invoices VALUES(2,5,TO_DATE('2025-01-05','YYYY-MM-DD'),15000); INSERT INTO Invoices VALUES(3,1,TO_DATE('2025-01-01','YYYY-MM-DD'),50000); INSERT INTO Invoices VALUES(4,4,TO_DATE('2025-01-04','YYYY-MM-DD'),10000); INSERT INTO Invoices VALUES(5,3,TO_DATE('2025-01-03','YYYY-MM-DD'),70000); -- الأدوية INSERT INTO Medications VALUES(1,'Analgesic'); INSERT INTO Medications VALUES(2,'Amoxicillin'); INSERT INTO Medications VALUES(3,'Omeprazole'); INSERT INTO Medications VALUES(4,'Artemether'); INSERT INTO Medications VALUES(5,'Folic Acid'); -- وصفات INSERT INTO Prescriptions VALUES(1,2,1,'1 tablet When necessary'); INSERT INTO Prescriptions VALUES(2,1,3,'1 tablet per day'); INSERT INTO Prescriptions VALUES(3,3,5,'2 tablet per day'); INSERT INTO Prescriptions VALUES(4,4,2,'3 tablet per day'); INSERT INTO Prescriptions VALUES(5,5,4,'2 tablet per day'); -- مريض جديد INSERT INTO Patients VALUES(6,'Amal Hassan',TO_DATE('1997-08-01','YYYY-MM-DD'),4,'0123456789'); -- موعد جديد INSERT INTO Appointments VALUES(6,6,4,TO_DATE('2025-01-10','YYYY-MM-DD')); -- فاتورة جديدة INSERT INTO Invoices VALUES(6,6,TO_DATE('2025-01-10','YYYY-MM-DD'),20000); -- تعديل رقم تلفون UPDATE Patients SET Phone = '0912345670' WHERE PatientID = 3; -- تعديل تخصص UPDATE Doctors SET Specialty = 'Dentistry' WHERE DoctorID = 1; -- حذف موعد DELETE FROM Appointments WHERE AppointmentID = 4; -- تعديل بتأكيد UPDATE Doctors SET Specialty = 'General Medicine' WHERE DoctorID = 2; COMMIT; -- تعديل ورجوع UPDATE Doctors SET Specialty = 'Dentistry' WHERE DoctorID = 2; ROLLBACK; -- كل المرضى SELECT * FROM Patients; -- دكاترة وتخصصاتهم SELECT DoctorName, Specialty FROM Doctors; -- المريض والطبيب وتاريخ الموعد SELECT P.PatientName, D.DoctorName, A.AppointmentDate FROM Appointments A JOIN Patients P ON A.PatientID = P.PatientID JOIN Doctors D ON A.DoctorID = D.DoctorID; -- مواعيد بتاريخ SELECT * FROM Appointments WHERE AppointmentDate = TO_DATE('2025-01-10','YYYY-MM-DD'); -- بعد تاريخ SELECT * FROM Appointments WHERE AppointmentDate > TO_DATE('2025-01-03','YYYY-MM-DD'); -- ترتيب دكاترة SELECT * FROM Doctors ORDER BY DoctorName; -- أسماء المرضى بالحروف الكبيرة SELECT UPPER(PatientName) FROM Patients; -- عدد مواعيد لكل دكتور SELECT D.DoctorName, COUNT(A.AppointmentID) FROM Doctors D LEFT JOIN Appointments A ON D.DoctorID = A.DoctorID GROUP BY D.DoctorName; -- المبلغ الكلي للمريض SELECT P.PatientName, SUM(I.Amount) FROM Patients P JOIN Invoices I ON P.PatientID = I.PatientID GROUP BY P.PatientName; -- عدد المرضى SELECT COUNT(*) FROM Patients; -- وصفات بالأسماء SELECT Pr.PrescriptionID, P.PatientName, M.MedicationName FROM Prescriptions Pr JOIN Patients P ON Pr.PatientID = P.PatientID JOIN Medications M ON Pr.MedicationID = M.MedicationID; -- المرضى الذين عندهم أكثر من 3 مواعيد SELECT P.PatientName, COUNT(A.AppointmentID) AS TotalAppointments FROM Patients P JOIN Appointments A ON A.PatientID = P.PatientID GROUP BY P.PatientName HAVING COUNT(A.AppointmentID) > 3; -- المرضى الذين زاروا أكثر من طبيب SELECT P.PatientName, COUNT(A.DoctorID) AS DoctorCount FROM Patients P JOIN Appointments A ON A.PatientID = P.PatientID GROUP BY P.PatientName HAVING COUNT(A.DoctorID) > 1; المرضى بدون وصفات-- SELECT P.PatientName FROM Patients P LEFT JOIN Prescriptions Pr ON P.PatientID = Pr.PatientID WHERE Pr.PrescriptionID IS NULL; --حساب عمر المريض SELECT PatientName, TRUNC(MONTHS_BETWEEN(SYSDATE, BirthDate) / 12) AS Age FROM Patients; --تنسيق تاريخ الموعد SELECT AppointmentID, PatientID, DoctorID, TO_CHAR(AppointmentDate, 'DD Month YYYY') AS AppointmentDate FROM Appointments; --الاطباء الذين ليس لديهم مواعيد هذا الاسبوع SELECT D.DoctorID, D.DoctorName FROM Doctors D LEFT JOIN Appointments A ON D.DoctorID = A.DoctorID AND A.AppointmentDate BETWEEN CURRENT_DATE AND CURRENT_DATE + 7 WHERE A.AppointmentID IS NULL; -- View لمواعيد اليوم CREATE OR REPLACE VIEW CurrentAppointment AS SELECT D.DoctorName, P.PatientName, A.AppointmentDate FROM Appointments A JOIN Doctors D ON A.DoctorID = D.DoctorID JOIN Patients P ON A.PatientID = P.PatientID WHERE A.AppointmentDate = CURRENT_DATE; -- عرض من الـ View SELECT * FROM CurrentAppointment; -- عمل sequence للفواتير CREATE SEQUENCE InvoiceSeq START WITH 7 INCREMENT BY 1; INSERT INTO InvoicesVALUES (InvoiceSeq.NEXTVAL, 1, TO_DATE('2025-01-15', 'YYYY-MM-DD'), 25000); --index لاسم الطبيب CREATE INDEX idx_doctor_name ON Doctors(DoctorName);