Hospital Management System SQL

Answers
Section A: Database Schema and Sample Data

1. Database Schema Design

Below are the SQL statements to create tables for the Hospital Management System:

1. 1. **Patients Table**

CREATE TABLE Patients (

PatientID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Gender VARCHAR(10),
Address VARCHAR(100),
PhoneNumber VARCHAR(15),
AdmittedDate DATE
);

2. 2. **Doctors Table**

CREATE TABLE Doctors (

DoctorID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Specialty VARCHAR(50),
PhoneNumber VARCHAR(15),
Email VARCHAR(50)
);

3. 3. **Appointments Table**

CREATE TABLE Appointments (

AppointmentID INT PRIMARY KEY,
PatientID INT,
DoctorID INT,
AppointmentDate DATE,
Status VARCHAR(20),
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);

4. 4. **Treatments Table**

CREATE TABLE Treatments (

TreatmentID INT PRIMARY KEY,
PatientID INT,
DoctorID INT,
TreatmentDate DATE,
TreatmentTitle VARCHAR(100),
Description TEXT,
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);

5. 5. **Medications Table**

CREATE TABLE Medications (

MedicationID INT PRIMARY KEY,
PatientID INT,
MedicationName VARCHAR(100),
Dosage VARCHAR(50),
StartDate DATE,
EndDate DATE,
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
);

2. Insert Sample Data

Below are the SQL INSERT statements for sample data:

6. 1. **Patients Table**

INSERT INTO Patients (PatientID, FirstName, LastName, DateOfBirth, Gender, Address,

PhoneNumber, AdmittedDate)
VALUES
(1, 'John', 'Doe', '1985-06-15', 'Male', '123 Elm Street', '1234567890', '2024-11-01'),
(2, 'Jane', 'Smith', '1990-04-22', 'Female', '456 Oak Avenue', '0987654321', '2024-10-20'),
(3, 'James', 'Brown', '1978-12-11', 'Male', '789 Pine Road', '1122334455', '2024-10-25');

7. 2. **Doctors Table**
INSERT INTO Doctors (DoctorID, FirstName, LastName, Specialty, PhoneNumber, Email)
VALUES
(1, 'Alice', 'Johnson', 'Cardiology', '1231231234', '[email protected]'),
(2, 'Robert', 'Miller', 'Neurology', '9879879876', '[email protected]'),
(3, 'Linda', 'Taylor', 'Pediatrics', '6546546543', '[email protected]');

8. 3. **Appointments Table**

INSERT INTO Appointments (AppointmentID, PatientID, DoctorID, AppointmentDate, Status)

VALUES
(1, 1, 1, '2024-11-05', 'Scheduled'),
(2, 2, 2, '2024-11-10', 'Scheduled'),
(3, 3, 3, '2024-11-15', 'Scheduled');

9. 4. **Treatments Table**

INSERT INTO Treatments (TreatmentID, PatientID, DoctorID, TreatmentDate, TreatmentTitle,

Description)
VALUES
(1, 1, 1, '2024-11-06', 'Heart Checkup', 'Routine heart checkup and ECG'),
(2, 2, 2, '2024-11-11', 'Brain MRI', 'Conducted a detailed MRI for neurological analysis'),
(3, 3, 3, '2024-11-16', 'Pediatric Consultation', 'Regular pediatric health checkup');

10. 5. **Medications Table**

INSERT INTO Medications (MedicationID, PatientID, MedicationName, Dosage, StartDate,

EndDate)
VALUES
(1, 1, 'Aspirin', '100mg', '2024-11-01', '2024-11-07'),
(2, 2, 'Ibuprofen', '200mg', '2024-11-05', '2024-11-12'),
(3, 3, 'Amoxicillin', '500mg', '2024-11-08', '2024-11-14');

Section B: Basic SQL Queries

Below are SQL queries for the basic tasks listed in Section B:

11. 1. Select all patients in the system.

SELECT * FROM Patients;

12. 2. Count the total number of doctors.

SELECT COUNT(*) AS TotalDoctors FROM Doctors;

13. 3. List all appointments scheduled for a specific date (example date: 2024-11-10).

SELECT * FROM Appointments WHERE AppointmentDate = '2024-11-10';

14. 4. Find the details of a specific patient using their PatientID (example PatientID: 1).

SELECT * FROM Patients WHERE PatientID = 1;

15. 5. Update the status of an appointment to 'Completed' for a specific AppointmentID

(example AppointmentID: 1).

UPDATE Appointments SET Status = 'Completed' WHERE AppointmentID = 1;

16. 6. Delete a medication that is no longer in use (example MedicationID: 3).

DELETE FROM Medications WHERE MedicationID = 3;

17. 7. Select all patients assigned to a specific doctor (example DoctorID: 2).

SELECT Patients.* FROM Patients JOIN Appointments ON Patients.PatientID =

Appointments.PatientID WHERE Appointments.DoctorID = 2;

18. 8. Retrieve the names of patients who have appointments in the next week.

SELECT Patients.FirstName, Patients.LastName FROM Patients JOIN Appointments ON

Patients.PatientID = Appointments.PatientID WHERE AppointmentDate BETWEEN
CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY;

19. 9. List all treatments administered to a specific patient (example PatientID: 1).

SELECT * FROM Treatments WHERE PatientID = 1;

20. 10. Count how many patients have been treated in the last month.

SELECT COUNT(DISTINCT PatientID) AS TreatedPatients FROM Treatments WHERE

TreatmentDate BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH) AND
CURRENT_DATE;

21. 11. Find the doctor with the highest number of appointments.

SELECT DoctorID, COUNT(*) AS NumberOfAppointments FROM Appointments GROUP BY

DoctorID ORDER BY NumberOfAppointments DESC LIMIT 1;

22. 12. Select all patients who were admitted in a specific month (example month: October
2024).

SELECT * FROM Patients WHERE MONTH(AdmittedDate) = 10 AND YEAR(AdmittedDate) =

2024;

23. 13. Retrieve the last 5 treatments provided in the hospital.

SELECT * FROM Treatments ORDER BY TreatmentDate DESC LIMIT 5;

24. 14. Count the total number of unique medications available.

SELECT COUNT(DISTINCT MedicationName) AS UniqueMedications FROM Medications;

25. 15. List all doctors who specialize in a specific field (example field: Cardiology).

SELECT * FROM Doctors WHERE Specialty = 'Cardiology';

26. 16. Select patients whose names start with the letter 'J'.

SELECT * FROM Patients WHERE FirstName LIKE 'J%';

27. 17. Find all medications prescribed to a specific patient (example PatientID: 2).

SELECT * FROM Medications WHERE PatientID = 2;

28. 18. Retrieve all appointments that were canceled.

SELECT * FROM Appointments WHERE Status = 'Canceled';

29. 19. Select all patients who have received a specific treatment (example TreatmentID: 1).

SELECT Patients.* FROM Patients JOIN Treatments ON Patients.PatientID =

Treatments.PatientID WHERE Treatments.TreatmentID = 1;

30. 20. Count how many patients are currently admitted to the hospital.

SELECT COUNT(*) AS CurrentlyAdmitted FROM Patients WHERE AdmittedDate IS NOT NULL;

Section C: Joins and Advanced Queries

31. 1. Join the Patients and Appointments tables to show each patient’s name along with
their appointment details.

SELECT Patients.FirstName, Patients.LastName, Appointments.AppointmentDate,

Appointments.Status FROM Patients JOIN Appointments ON Patients.PatientID =
Appointments.PatientID;

32. 2. Select the most frequently prescribed medication and the number of prescriptions.

SELECT MedicationName, COUNT(*) AS PrescriptionCount FROM Medications GROUP BY

MedicationName ORDER BY PrescriptionCount DESC LIMIT 1;

33. 3. Retrieve a list of all patients and their corresponding doctors using a JOIN operation.

SELECT Patients.FirstName, Patients.LastName, Doctors.FirstName AS DoctorFirstName,

Doctors.LastName AS DoctorLastName FROM Patients JOIN Appointments ON
Patients.PatientID = Appointments.PatientID JOIN Doctors ON Appointments.DoctorID =
Doctors.DoctorID;

34. 4. Find all doctors who have treated more than 10 patients.

SELECT Doctors.FirstName, Doctors.LastName, COUNT(DISTINCT Treatments.PatientID) AS

PatientCount FROM Doctors JOIN Treatments ON Doctors.DoctorID = Treatments.DoctorID
GROUP BY Doctors.DoctorID HAVING PatientCount > 10;

35. 5. List the titles of treatments that have been administered to multiple patients.

SELECT TreatmentTitle, COUNT(DISTINCT PatientID) AS PatientCount FROM Treatments

GROUP BY TreatmentTitle HAVING PatientCount > 1;

Section D: Case Study Application

36. 1. Write a SQL query to find all patients who have never had an appointment.

SELECT * FROM Patients WHERE PatientID NOT IN (SELECT PatientID FROM Appointments);

37. 2. Create a SQL statement to update the contact information of a specific patient and
display the updated information (example PatientID: 1).

UPDATE Patients SET PhoneNumber = '9998887776', Address = '321 Maple Lane' WHERE
PatientID = 1;
SELECT * FROM Patients WHERE PatientID = 1;