๐ SQL Project Series #7Hospital Management Analysis ๐ฅLearn how hospitals use SQL to analyze patient data, optimize operations, improve resource utilization, and generate healthcare insights.
๐ฏ Business Objectives โ
Analyze patient admissions
โ
Monitor doctor performance
โ
Track appointment trends
โ
Measure bed occupancy
โ
Analyze treatment costs
โ
Identify readmitted patients
โ
Calculate average length of stay
โ
Improve hospital efficiency
๐ Step 1: Create Database CREATE DATABASE hospital_db;
USE hospital_db;
๐ Step 2: Create Patients Table CREATE TABLE patients (
patient_id INT PRIMARY KEY,
patient_name VARCHAR(100),
gender VARCHAR(10),
age INT,
city VARCHAR(50),
registration_date DATE
);
๐ Step 3: Create Doctors Table CREATE TABLE doctors (
doctor_id INT PRIMARY KEY,
doctor_name VARCHAR(100),
specialization VARCHAR(100),
department VARCHAR(100)
);
๐ Step 4: Create Appointments Table CREATE TABLE appointments (
appointment_id INT PRIMARY KEY,
patient_id INT,
doctor_id INT,
appointment_date DATE,
status VARCHAR(20),
consultation_fee DECIMAL(10,2),
FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);
๐ Step 5: Create Admissions Table CREATE TABLE admissions (
admission_id INT PRIMARY KEY,
patient_id INT,
admission_date DATE,
discharge_date DATE,
diagnosis VARCHAR(100),
treatment_cost DECIMAL(12,2),
FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);
๐ Step 6: Insert Sample Patients INSERT INTO patients VALUES
(1,'Rahul Sharma','Male',34,'Mumbai','2024-01-05'),
(2,'Priya Verma','Female',29,'Delhi','2024-01-10'),
(3,'Amit Patel','Male',42,'Pune','2024-02-15'),
(4,'Sneha Joshi','Female',37,'Bangalore','2024-03-01'),
(5,'Rohan Gupta','Male',51,'Hyderabad','2024-03-20');
๐ Step 7: Insert Sample Doctors INSERT INTO doctors VALUES
(101,'Dr. Mehta','Cardiology','Heart Care'),
(102,'Dr. Singh','Orthopedics','Bone Care'),
(103,'Dr. Rao','Neurology','Neuro Care'),
(104,'Dr. Shah','General Medicine','General');
๐ Step 8: Insert Sample Appointments INSERT INTO appointments VALUES
(1001,1,101,'2025-01-05','Completed',800),
(1002,2,104,'2025-01-06','Completed',500),
(1003,3,102,'2025-01-08','Cancelled',700),
(1004,4,103,'2025-01-09','Completed',1000),
(1005,5,101,'2025-01-12','Completed',800);
๐ Step 9: Insert Sample Admissions INSERT INTO admissions VALUES
(201,1,'2025-01-05','2025-01-10','Heart Surgery',250000),
(202,2,'2025-01-08','2025-01-11','Fever',12000),
(203,3,'2025-01-15','2025-01-22','Fracture',85000),
(204,5,'2025-01-18','2025-01-21','Cardiac Checkup',45000);
๐ง SQL Concepts You'll Practice โ DDL & DML
โ Joins
โ Aggregate Functions
โ GROUP BY
โ HAVING
โ CASE WHEN
โ CTEs
โ Window Functions
โ Date Functions
โ Ranking Functions
๐ Business KPIs You Can Build ๐ Total Patients
๐ Total Admissions
๐ Total Appointments
๐ Appointment Completion Rate
๐ Appointment Cancellation Rate
๐ Doctor-wise Patient Count
๐ Department-wise Revenue
๐ Average Consultation Fee
๐ Average Treatment Cost
๐ Average Length of Stay
๐ Daily Patient Admissions
๐ Monthly Admission Trend
๐ Readmission Rate
๐ Bed Occupancy Rate
๐ Top Doctors by Patient Volume
๐ Revenue by Department
๐ Revenue by Doctor
๐ Most Common Diagnosis
๐ Patient Distribution by City
๐ Average Patient Age
๐ฏ This project reflects the type of SQL analysis performed by Healthcare Analysts, Hospital Operations teams, Business Intelligence Analysts, and Data Analysts working in hospitals and health-tech companies.
๐ก Double Tap โค๏ธ For More 
