Post 🚀 SQL Project Series #7

🚀 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