-- جدول الدكاترة
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 Invoices (InvoiceID, PatientID, InvoiceDate, Amount)
VALUES (InvoiceSeq.NEXTVAL, 1, TO_DATE('2025-01-15', 'YYYY-MM-DD'), 25000);
--index لاسم الطبيب
CREATE INDEX idx_doctor_name ON Doctors(DoctorName);
Telegram: DB developers channel