Introduction

This SQL project is part of my portfolio.

In this project, I will be demonstrating my proficiency in SQL and my ability to design, implement, and query a relational database that simulates the complex operations of a hospital’s admissions process.

Objective

The primary objective of this project is to highlight my skills in database schema design, data manipulation, and query optimization.

By simulating real-world data interactions within a hospital setting, I aim to demonstrate my understanding of relational database management systems (RDBMS) and my ability to extract actionable insights from complex datasets.

Database Diagram/Schema/ERD

This Entity Relationship Diagram (ERD) between the three tables shows their respective primary keys.

  • Patients Table: Stores basic information about patients, including an auto-incrementing PatientID as the primary key, first name, last name, date of birth, and gender.
  • Departments Table: Contains details of hospital departments, with an auto-incrementing DepartmentID as the primary key and the name of the department.
  • Admissions Table: Tracks patient admissions, linking to both the Patients and Departments tables via foreign keys. It includes an auto-incrementing AdmissionID as the primary key, the IDs of the patient and department, admission and discharge dates, and the reason for admission.

For more complex queries used later, I added three more.

I have added three more tables:

  • Doctors Table: Stores information about medical professionals, including their names, specialties, and associated departments, facilitating the tracking of doctors’ assignments to various hospital departments.
  • Treatments Table: Catalogs the different medical treatments available within the hospital, each linked to a specific department to highlight departmental areas of expertise and treatment offerings.
  • HospitalRooms Table: Details the characteristics and allocation of hospital rooms, including room numbers, types, capacity, and associated departments, to manage patient accommodation efficiently.

Technologies Used

This project was developed using SQL Server Management Studio (SSMS), a leading environment for managing, accessing, and developing all components of SQL Server.

SSMS provides an extensive toolset for database management, making it the ideal choice for this project.

Furthermore, a locally hosted database is important for the safe and easy retrieval of healthcare data.

Why Build Such a Database?

Singapore’s hospital data tends to get messy and spans across multiple fields.

As such, downloading relevant data fields from a central health intelligence OBIEE software such as eHintS as CSV files and performing ETL actions to a local RDBMS database is important for connecting it to new data visualisation software such as Tableau.

This RDBMS database will serve as a “single source of truth” for healthcare analytics.

In my previous work at SGH, our team struggled to manage multiple large flat files over the years.

Building a local RDBMS makes sense as it does away with redundant rows of data by using IDs as unique identifiers for patients, cases, and admission numbers. This reduces the time taken to connect the SQL database to Tableau for visualisations.

Let’s begin.

Step 1: Create Tables

First, let’s define the structure of our database by creating the necessary tables.

I’m creating the six tables here:

  1. dbo.Patients
  2. dbo.Departments
  3. dbo.Admissions
  4. dbo.Doctors
  5. dbo.Treatments
  6. dbo.HospitalRooms
SQL
-- Create a table for storing patient details
CREATE TABLE Patients (
    PatientID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DOB DATE,
    Gender CHAR(1)
);

-- Create a table for storing department details
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100),
    ADD CONSTRAINT FK_Treatments_Departments FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Create a table for storing admission details
CREATE TABLE Admissions (
    AdmissionID INT PRIMARY KEY,
    PatientID INT,
    DepartmentID INT,
    AdmissionDate DATE,
    DischargeDate DATE,
    ReasonForAdmission VARCHAR(255),
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Create Doctors Table
CREATE TABLE Doctors (
    DoctorID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Specialty NVARCHAR(100) NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Create Treatments Table
CREATE TABLE Treatments (
    TreatmentID INT IDENTITY(1,1) PRIMARY KEY,
    TreatmentName NVARCHAR(100) NOT NULL,
    Description NVARCHAR(MAX),
    DepartmentID INT
);

-- Create HospitalRooms Table
CREATE TABLE HospitalRooms (
    RoomID INT IDENTITY(1,1) PRIMARY KEY,
    RoomNumber NVARCHAR(10) NOT NULL,
    RoomType NVARCHAR(50) NOT NULL,
    Capacity INT NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Next, I’ll be inserting sample data into the individual tables.

Step 2: Insert Sample Data

Insert some sample data into the tables you’ve created.

SQL
-- Insert sample data into Patients
INSERT INTO Patients (PatientID, FirstName, LastName, DOB, Gender) VALUES
(1, 'John', 'Doe', '1980-04-12', 'M'),
(2, 'Jane', 'Smith', '1992-08-03', 'F'),
(3, 'Emily', 'Jones', '1987-11-19', 'F'),
(4, 'Michael', 'Brown', '1970-05-27', 'M'),
(5, 'Linda', 'Taylor', '1995-07-20', 'F'),
(6, 'Robert', 'Davis', '1982-09-15', 'M'),
(7, 'Patricia', 'Miller', '1976-03-05', 'F'),
(8, 'David', 'Wilson', '1990-12-01', 'M'),
(9, 'Jennifer', 'Moore', '1984-06-30', 'F'),
(10, 'James', 'Anderson', '1978-01-22', 'M');

-- Insert sample data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Cardiology'),
(2, 'Neurology'),
(3, 'Orthopedics'),
(4, 'Pediatrics'),
(5, 'Oncology');

-- Insert sample data into Admissions
INSERT INTO Admissions (AdmissionID, PatientID, DepartmentID, AdmissionDate, DischargeDate, ReasonForAdmission) VALUES
(1, 1, 1, '2023-01-15', '2023-01-20', 'Heart Attack'),
(2, 2, 2, '2023-02-05', '2023-02-15', 'Migraine'),
(3, 3, 3, '2023-03-10', '2023-03-20', 'Fractured Leg'),
(4, 4, 1, '2023-04-12', '2023-04-18', 'Cardiac Arrest'),
(5, 5, 2, '2023-05-22', '2023-06-01', 'Epilepsy'),
(6, 6, 4, '2023-07-15', '2023-07-22', 'Asthma'),
(7, 7, 5, '2023-08-03', '2023-08-20', 'Breast Cancer'),
(8, 8, 1, '2023-09-10', '2023-09-15', 'Hypertension'),
(9, 9, 3, '2023-10-05', '2023-10-15', 'Knee Replacement'),
(10, 10, 2, '2023-11-12', '2023-11-26', 'Stroke'),
(11, 2, 4, '2023-12-01', '2023-12-10', 'Pneumonia'),
(12, 3, 1, '2024-01-20', '2024-01-30', 'Arrhythmia'),
(13, 4, 5, '2024-02-15', '2024-03-01', 'Lung Cancer'),
(14, 5, 3, '2024-03-22', '2024-04-02', 'Hip Replacement'),
(15, 6, 4, '2024-04-18', '2024-04-28', 'Bronchitis');

-- Insert sample data into Doctors
INSERT INTO Doctors (FirstName, LastName, Specialty, DepartmentID) VALUES
('Sarah', 'Miller', 'Cardiology', 1),
('James', 'Wilson', 'Neurology', 2),
('Emily', 'Clark', 'Orthopedics', 3),
('John', 'Smith', 'Pediatrics', 4),
('Linda', 'Johnson', 'Oncology', 5);

-- Insert sample data into Treatments
INSERT INTO Treatments (TreatmentName, Description, DepartmentID) VALUES
('Angioplasty', 'Procedure to restore blood flow through the artery.', 1),
('Chemotherapy', 'Use of drugs to kill cancer cells.', 5),
('Physical Therapy', 'Exercises to help patients regain movement and strength.', 3),
('Bronchoscopy', 'A procedure that allows your doctor to look at your airway.', 2),
('Joint Replacement', 'Surgical procedure to remove and replace an arthritic or damaged joint.', 3);

-- Insert sample data into Hospital Rooms
INSERT INTO HospitalRooms (RoomNumber, RoomType, Capacity, DepartmentID) VALUES
('101A', 'Single', 1, 1),
('102B', 'Double', 2, 2),
('201A', 'ICU', 1, 3),
('202B', 'General', 4, 4),
('301A', 'Deluxe', 1, 5);

Step 3: Query the Data

Now, let’s write some queries to extract information from our database.

  1. List all patients admitted to Cardiology.

First, we’ll pick out the FirstName and LastName from the Patients table, and AdmissionDate and DischargeDate from the Admissions table.

To do this, we need to write a JOIN between the two tables on PatientID.

Then, to get details of their Department, another JOIN function needs to be written to join the Departments to Admissions on DepartmentID.

Next, we can filter DepartmentName for ‘Cardiology’, our desired department.

Here’s the full SQL query:

SQL
-- Gets a list of all patients that were admitted into Cardiology
SELECT p.FirstName, p.LastName, a.AdmissionDate, a.DischargeDate
FROM Patients p
JOIN Admissions a ON p.PatientID = a.PatientID
JOIN Departments d ON a.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Cardiology';

Our result will be the details of patients within the Cardiology department:

  1. Find the total number of admissions for each department.

We’ll have two columns as the output.

  1. DepartmentName
  2. TotalAdmissions (A count of the total admissions.)

We’ll then do a JOIN function to join the dbo.Departments table with the dbo.Admissions table on the DepartmentID.

To summarize the data into just groups with department names, we’ll do a GROUP BY function.

Here’s the full SQL query:

SQL
-- Gets the total admissions count per department
SELECT d.DepartmentName, COUNT(a.AdmissionID) AS TotalAdmissions
FROM Departments d
JOIN Admissions a ON d.DepartmentID = a.DepartmentID
GROUP BY d.DepartmentName;

And here’s the final summarized table of the admissions count per department.

Admission Count by Department
  1. Identify patients who have been admitted more than once.

This is to get a sense of readmission rates. More complex queries can be done within or outside SQL (Python/R) to calculate rates and visualise a trend with time in Tableau.

SQL
-- Gets patient details of patients who were admitted > 1 time
SELECT p.PatientID, p.FirstName, p.LastName, COUNT(a.AdmissionID) AS AdmissionCount
FROM Patients p
JOIN Admissions a ON p.PatientID = a.PatientID
GROUP BY p.PatientID, p.FirstName, p.LastName
HAVING COUNT(a.AdmissionID) > 1;

Here’s what we got back:

Step 4: Intermediate Queries

Next, I’ll demonstrate more advanced queries.

  1. Calculate the average length of stay for each department.

In order to get the average length of stay for each department, we’ll need to join the Admissions and Departments tables together.

We’ll then follow this formula to calculate the length of stay of each admission.

LengthOfStay = DischargeDate – AdmissionDate

The DATEDIFF function does that for us.

The AVG function can then be used along with the argument DISTINCT to find the average Length of Stay.

Then, the data is grouped by DepartmentName.

Here’s the full SQL query:

SQL
-- Gets the average length of stay for each department
SELECT d.DepartmentName, AVG(DISTINCT DATEDIFF(DAY, a.AdmissionDate, a.DischargeDate)) AS LengthOfStay
FROM Admissions a
JOIN Departments d ON a.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

The result is a summarized table of the average length of stay for each department.

Step 5: Complex Queries

Next, we’ll update some tables to include foreign key identifiers.

SQL
-- Alter the Treatments table to add DepartmentID column
ALTER TABLE Treatments
ADD DepartmentID INT;

-- Add a foreign key constraint to link to the Departments table
ALTER TABLE Treatments
ADD CONSTRAINT FK_Treatments_Departments FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

We’re now ready to do more complex queries.

Complex Query 1: Department-wise Patient Admission Report with Assigned Doctor and Room Details

This query provides a detailed report of patient admissions for each department, including the assigned doctor, treatment, and room details.

It’s useful for assessing departmental workload, patient care specifics, and resource utilization.

Here is the full SQL query:

SQL
SELECT 
    d.DepartmentName,
    CONCAT(p.FirstName, ' ', p.LastName) AS PatientName,
    CONCAT(doc.FirstName, ' ', doc.LastName) AS AssignedDoctor,
    t.TreatmentName,
    hr.RoomNumber,
    a.AdmissionDate,
    a.DischargeDate
FROM Admissions a
JOIN Patients p ON a.PatientID = p.PatientID
JOIN Departments d ON a.DepartmentID = d.DepartmentID
JOIN Doctors doc ON d.DepartmentID = doc.DepartmentID
JOIN Treatments t ON d.DepartmentID = t.DepartmentID
JOIN HospitalRooms hr ON d.DepartmentID = hr.DepartmentID
-- WHERE a.DischargeDate IS NULL  -- focusing on currently admitted patients
ORDER BY d.DepartmentName, a.AdmissionDate;

This query assumes that doctors, treatments, and rooms are directly associated with departments and indirectly linked to admissions through those departments.

From here, this result can then be used to connect to a Tableau dashboard to visualize a hospital’s KPI:

  1. Workload (Patient count for each day)
  2. Resource Utilisation (Room usage)
  3. Prioritize based on Treatment.

Query 2: Analysis of Treatment Frequency by Department with Average Stay Duration

This query aims to analyze the frequency of different treatments across departments and calculate the average stay duration for patients undergoing each treatment.

This information is critical for understanding departmental treatment specializations and patient recovery times.

Here’s the full SQL query:

SQL
SELECT 
    d.DepartmentName,
    t.TreatmentName,
    COUNT(*) AS NumberOfPatients,
    AVG(DATEDIFF(DAY, a.AdmissionDate, a.DischargeDate)) AS AverageStayDuration
FROM Admissions a
JOIN Patients p ON a.PatientID = p.PatientID
JOIN Departments d ON a.DepartmentID = d.DepartmentID
JOIN Treatments t ON d.DepartmentID = t.DepartmentID
GROUP BY d.DepartmentName, t.TreatmentName
ORDER BY d.DepartmentName, NumberOfPatients DESC;

This query provides a departmental breakdown of treatment frequencies and the average duration of stay associated with each treatment.

Here’s the result:

It’s beneficial for hospital administrators to identify high-demand services and potential areas for resource optimization.

Respective Heads of Departments can decide how to manage patients and how long they stay and keep track of the Treatments that take up a longer stay duration.