CREATE TABLE patients (
  patient_id INTEGER PRIMARY KEY,
  name VARCHAR(50),
  gender VARCHAR(10),
  date_of_birth DATE,
  phone_no VARCHAR(40),
  email VARCHAR(100)
);
CREATE TABLE MedicalStaff (
    staff_id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    phone_no VARCHAR(40),
    email VARCHAR(100),
    designation VARCHAR(150)
);
CREATE TABLE Diagnoses(
    diagnoses_id INT PRIMARY KEY,
    patient_id INT,
    diagnoses_name VARCHAR(60),
    diagnoses_date DATE,
    symptoms VARCHAR(200),
    FOREIGN KEY(patient_id) REFERENCES patients(patient_id)
);
CREATE TABLE Treatment(
    patient_id INT,
    staff_id INT,
    diagnoses_id INT,
    treatment_name TEXT,
    treatment_startdate DATE,
    cost DECIMAL(10,2),
    FOREIGN KEY(patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY(staff_id) REFERENCES MedicalStaff(staff_id),
    FOREIGN KEY(diagnoses_id) REFERENCES Diagnoses(diagnoses_id)
);
CREATE TABLE Appointment(
    appointment_id INT PRIMARY KEY,
    patient_id INT,
    staff_id INT,
    appointment_date DATE,
    appointment_time TIME,
    scheduled_status BOOLEAN, 
    FOREIGN KEY(patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY(staff_id) REFERENCES MedicalStaff(staff_id)
    
);
INSERT INTO patients VALUES
(101, 'Rahul Sharma','Male', '2002-05-15',  '9876543210', 'rahulsharma@gmail.com'),
(102, 'Priya Das','Female', '2001-08-20',  '9123456780', 'priyadas@gmail.com'),
(103, 'Amit Roy', 'Male','1999-12-10',  '9988776655', 'amitroy@gmail.com'),
(104, 'Sneha Sen', 'Female','2003-03-25',  '9834567890', 'snehasen@gmail.com'),
(105, 'Arjun Gupta','Male',  '2000-11-18', '9876501234', 'arjungupta@gmail.com');

INSERT INTO MedicalStaff VALUES
(201, 'Anil Verma', '9871112233', 'anil.verma@gmail.com','Cardiology'),
(202, 'Sneha Mukherjee', '9812345678', 'sneha.m@gmail.com','Neurology'),
(203, 'Rakesh Singh', '9898989898', 'rakesh.s@gmail.com' ,'Orthopedics'),
(204, 'Priya Mehta',  '9876543211', 'priya.mehta@gmail.com','Dermatology'),
(205, 'Raj Kapoor', '9123456789', 'raj.kapoor@gmail.com','General Medicine');

INSERT INTO Diagnoses VALUES
(301, 101, 'High Blood Pressure', '2026-08-01', 'Patient has elevated blood pressure'),
(302, 102,'Migraine', '2026-08-02', 'Frequent severe headaches'),
(303, 103, 'Knee Injury', '2026-08-03', 'Minor ligament injury'),
(304, 104,'Skin Allergy', '2026-08-04', 'Allergic skin reaction'),
(305, 105, 'Common Cold', '2026-08-05', 'Fever, cough and cold symptoms');

INSERT INTO Treatment VALUES
(101,201,301, 'Blood Pressure Medication', '2026-08-01', 1500.00),
(102,203, 302, 'Migraine Medication', '2026-08-02', 1000.00),
(104,204, 303, 'Physiotherapy', '2026-08-03', 2500.00),
(103,202,304, 'Anti Allergy Medication', '2026-08-04', 800.00),
(105,205, 305, 'Cold and Fever Medication', '2026-08-05', 500.00);

INSERT INTO Appointment VALUES
(501, 101, 201, '2026-08-10', '10:00:00', True),
(502, 102, 202, '2026-08-11', '11:30:00',False),
(503, 103, 203, '2026-08-12', '14:00:00',True),
(505, 105, 205, '2026-08-14', '09:30:00',True);

SELECT p.name,d.diagnoses_name
FROM patients AS p
JOIN Diagnoses AS d
ON p.patient_id=d.patient_id
WHERE p.name="Rahul Sharma";

Select d.diagnoses_name,
COUNT(patient_id) AS number_of_patients
FROM Diagnoses AS date_of_birth
Group by diagnoses_name;