🏥 Hospital Patient Management System

Database Schema - RAW DATA (Messy!)

Table 1: patient_records - Sample data (25 rows total)

patient_id	full_name	date_of_birth	admission_date	discharge_date	diagnosis
P001	john smith	15/05/1985	2024-01-10 08:30:00	2024-01-15	Hypertension
p002	SARAH JONES	1990-08-22	10/01/2024	NULL	Diabetes
P003	Mike Wilson	1978-03-10	2024/01/12 14:45	2024-01-20	flu
P004	anna garcia	22-07-1995	2024-01-15	NULL	PNEUMONIA

...and 21 more messy rows! Download the full dataset above.

Table 2: medications - Drug reference data

med_id	medication_name	category	risk_level
MED001	Aspirin	Pain Relief	LOW
MED004	Warfarin	Blood Thinner	HIGH
MED005	Morphine	Pain Relief	HIGH

Table 3: prescriptions - Messy prescription data

prescription_id	patient_id	doctor_id	med_id	dosage	status
RX001	P001	D101	MED003	10mg	ACTIVE
RX002	p002	d102	med002	500 mg	active
RX003	P003	D101	MED001	NULL	Completed
RX004	P004	D103	med999	250mg	ACTIVE

Table 4: doctors - Doctor information

doctor_id	doctor_name	specialization	license_status
D101	dr. emily chen	Cardiology	ACTIVE
D104	james wilson	Orthopedics	SUSPENDED

📋 Problem Requirements

Build a hospital management system to track patient medications, validate prescriptions, detect risks, and generate compliance reports.

💡 SQL Skills Required: TRIM, UPPER, INITCAP, STR_TO_DATE, CASE, CAST, JOINs, TIMESTAMPDIFF, DATEDIFF, COALESCE, CTEs!

This mirrors real-world hospital systems used to track patient medications and ensure prescription safety!