🚀 SQL Project Series #7

Hospital 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