sql_healthcare.sql

-- ==========================================
-- 1. DROP TABLES IF EXIST (Clean Slate)
-- ==========================================
DROP TABLE IF EXISTS Billing;
DROP TABLE IF EXISTS Encounters;
DROP TABLE IF EXISTS Patients;

-- ==========================================
-- 2. CREATE TABLES
-- ==========================================

CREATE TABLE Patients (
    patient_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    dob DATE,
    gender VARCHAR(10),
    city VARCHAR(50)
);

CREATE TABLE Encounters (
    encounter_id INT PRIMARY KEY,
    patient_id INT,
    encounter_type VARCHAR(20), -- 'Inpatient', 'Outpatient', 'Emergency', 'Telehealth'
    admit_date TIMESTAMP,
    discharge_date TIMESTAMP, -- Can be NULL if currently admitted
    primary_diagnosis VARCHAR(100),
    hospital_department VARCHAR(50),
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);

CREATE TABLE Billing (
    bill_id INT PRIMARY KEY,
    encounter_id INT,
    total_amount DECIMAL(10, 2),
    insurance_covered DECIMAL(10, 2),
    patient_responsibility DECIMAL(10, 2),
    payment_status VARCHAR(20), -- 'Paid', 'Pending', 'Denied', 'Sent to Collections'
    FOREIGN KEY (encounter_id) REFERENCES Encounters(encounter_id)
);

-- ==========================================
-- 3. INSERT DATA (30 Rows Per Table)
-- ==========================================

-- --- PATIENTS (30 rows) ---
INSERT INTO Patients VALUES
(1, 'James', 'Smith', '1982-05-15', 'Male', 'New York'),
(2, 'Maria', 'Garcia', '1990-08-22', 'Female', 'Los Angeles'),
(3, 'Robert', 'Johnson', '1965-03-10', 'Male', 'Chicago'),
(4, 'Linda', 'Martinez', '1978-11-05', 'Female', 'Houston'),
(5, 'Michael', 'Brown', '2001-01-30', 'Male', 'Phoenix'),
(6, 'Sarah', 'Davis', '1955-12-12', 'Female', 'Philadelphia'),
(7, 'David', 'Rodriguez', '1988-07-19', 'Male', 'San Antonio'),
(8, 'Karen', 'Wilson', '1995-04-25', 'Female', 'San Diego'),
(9, 'William', 'Anderson', '1972-09-09', 'Male', 'Dallas'),
(10, 'Patricia', 'Taylor', '1985-02-14', 'Female', 'San Jose'),
(11, 'Christopher', 'Thomas', '1999-06-30', 'Male', 'Austin'),
(12, 'Jennifer', 'Moore', '1960-10-08', 'Female', 'Jacksonville'),
(13, 'Charles', 'Jackson', '1983-01-22', 'Male', 'Fort Worth'),
(14, 'Elizabeth', 'Martin', '1992-11-17', 'Female', 'Columbus'),
(15, 'Daniel', 'Lee', '1976-05-05', 'Male', 'Charlotte'),
(16, 'Susan', 'Perez', '1968-08-30', 'Female', 'San Francisco'),
(17, 'Matthew', 'Thompson', '1994-03-14', 'Male', 'Indianapolis'),
(18, 'Jessica', 'White', '1989-12-01', 'Female', 'Seattle'),
(19, 'Anthony', 'Harris', '1950-07-07', 'Male', 'Denver'),
(20, 'Barbara', 'Sanchez', '1970-09-28', 'Female', 'Washington'),
(21, 'Mark', 'Clark', '1981-04-18', 'Male', 'Boston'),
(22, 'Lisa', 'Ramirez', '1997-02-03', 'Female', 'El Paso'),
(23, 'Donald', 'Lewis', '1963-11-22', 'Male', 'Nashville'),
(24, 'Nancy', 'Robinson', '1986-06-12', 'Female', 'Detroit'),
(25, 'Paul', 'Walker', '1974-10-25', 'Male', 'Oklahoma City'),
(26, 'Sandra', 'Young', '1991-01-15', 'Female', 'Portland'),
(27, 'Steven', 'Allen', '1958-05-20', 'Male', 'Las Vegas'),
(28, 'Ashley', 'King', '1993-08-08', 'Female', 'Memphis'),
(29, 'Andrew', 'Wright', '1980-03-03', 'Male', 'Louisville'),
(30, 'Emily', 'Scott', '1998-12-25', 'Female', 'Baltimore');

-- --- ENCOUNTERS (30 rows - Linked to Patients) ---
-- Note: Some patients have multiple visits, some have none (for LEFT JOIN tests)
INSERT INTO Encounters VALUES
(101, 1, 'Emergency', '2023-01-10 08:00:00', '2023-01-10 14:00:00', 'Acute Bronchitis', 'ER'),
(102, 2, 'Outpatient', '2023-01-12 09:30:00', '2023-01-12 10:30:00', 'Hypertension', 'Cardiology'),
(103, 3, 'Inpatient', '2023-01-15 14:00:00', '2023-01-20 11:00:00', 'Pneumonia', 'Pulmonology'),
(104, 1, 'Outpatient', '2023-02-01 10:00:00', '2023-02-01 10:45:00', 'Follow-up', 'Family Medicine'),
(105, 4, 'Telehealth', '2023-02-05 15:00:00', '2023-02-05 15:20:00', 'Anxiety Disorder', 'Psychiatry'),
(106, 5, 'Emergency', '2023-02-10 22:15:00', '2023-02-11 02:00:00', 'Ankle Sprain', 'ER'),
(107, 6, 'Inpatient', '2023-02-14 07:00:00', '2023-02-18 16:00:00', 'Type 2 Diabetes', 'Endocrinology'),
(108, 7, 'Outpatient', '2023-02-20 11:30:00', '2023-02-20 12:00:00', 'Migraine', 'Neurology'),
(109, 8, 'Telehealth', '2023-03-01 09:00:00', '2023-03-01 09:15:00', 'Rash', 'Dermatology'),
(110, 9, 'Emergency', '2023-03-05 04:30:00', '2023-03-05 09:00:00', 'Kidney Stone', 'Urology'),
(111, 10, 'Outpatient', '2023-03-10 13:00:00', '2023-03-10 13:30:00', 'Pregnancy Checkup', 'OBGYN'),
(112, 11, 'Inpatient', '2023-03-15 08:00:00', '2023-03-25 12:00:00', 'Appendicitis', 'Surgery'),
(113, 12, 'Outpatient', '2023-03-20 10:15:00', '2023-03-20 10:45:00', 'Arthritis', 'Rheumatology'),
(114, 13, 'Emergency', '2023-04-01 19:00:00', '2023-04-01 23:00:00', 'Food Poisoning', 'ER'),
(115, 14, 'Telehealth', '2023-04-05 16:00:00', '2023-04-05 16:30:00', 'Insomnia', 'Psychiatry'),
(116, 15, 'Outpatient', '2023-04-10 09:45:00', '2023-04-10 10:30:00', 'High Cholesterol', 'Cardiology'),
(117, 16, 'Inpatient', '2023-04-15 11:00:00', '2023-04-19 14:00:00', 'COPD Flare-up', 'Pulmonology'),
(118, 17, 'Emergency', '2023-04-20 03:00:00', '2023-04-20 07:00:00', 'Concussion', 'ER'),
(119, 2, 'Telehealth', '2023-04-25 14:00:00', '2023-04-25 14:20:00', 'Medication Refill', 'Cardiology'),
(120, 19, 'Outpatient', '2023-05-01 15:30:00', '2023-05-01 16:00:00', 'Hearing Loss', 'ENT'),
(121, 20, 'Inpatient', '2023-05-05 06:00:00', '2023-05-10 18:00:00', 'Sepsis', 'ICU'),
(122, 21, 'Emergency', '2023-05-10 21:00:00', '2023-05-11 01:00:00', 'Allergic Reaction', 'ER'),
(123, 22, 'Outpatient', '2023-05-15 10:00:00', '2023-05-15 10:30:00', 'UTI', 'Urology'),
(124, 23, 'Telehealth', '2023-05-20 11:00:00', '2023-05-20 11:15:00', 'Flu Symptoms', 'Family Medicine'),
(125, 24, 'Inpatient', '2023-05-25 13:00:00', '2023-05-30 10:00:00', 'Hip Fracture', 'Orthopedics'),
(126, 25, 'Emergency', '2023-06-01 02:00:00', '2023-06-01 06:00:00', 'Chest Pain', 'ER'),
(127, 26, 'Outpatient', '2023-06-05 14:45:00', '2023-06-05 15:15:00', 'Asthma', 'Pulmonology'),
(128, 27, 'Inpatient', '2023-06-10 09:00:00', NULL, 'Stroke', 'Neurology'), -- NULL Discharge Date (Currently Admitted)
(129, 28, 'Telehealth', '2023-06-15 16:00:00', '2023-06-15 16:20:00', 'Depression', 'Psychiatry'),
(130, 29, 'Outpatient', '2023-06-20 08:30:00', '2023-06-20 09:00:00', 'Annual Physical', 'Family Medicine');

-- --- BILLING (30 rows - Linked to Encounters) ---
INSERT INTO Billing VALUES
(501, 101, 1500.00, 1200.00, 300.00, 'Paid'),
(502, 102, 200.00, 150.00, 50.00, 'Paid'),
(503, 103, 15000.00, 14000.00, 1000.00, 'Pending'),
(504, 104, 150.00, 100.00, 50.00, 'Paid'),
(505, 105, 100.00, 80.00, 20.00, 'Paid'),
(506, 106, 2500.00, 2000.00, 500.00, 'Denied'), -- Denied claim
(507, 107, 12000.00, 11000.00, 1000.00, 'Sent to Collections'), -- Bad debt
(508, 108, 300.00, 250.00, 50.00, 'Paid'),
(509, 109, 80.00, 0.00, 80.00, 'Paid'), -- Self-pay
(510, 110, 4500.00, 4000.00, 500.00, 'Pending'),
(511, 111, 250.00, 200.00, 50.00, 'Paid'),
(512, 112, 25000.00, 24000.00, 1000.00, 'Paid'),
(513, 113, 200.00, 180.00, 20.00, 'Paid'),
(514, 114, 1800.00, 1500.00, 300.00, 'Pending'),
(515, 115, 120.00, 100.00, 20.00, 'Paid'),
(516, 116, 220.00, 200.00, 20.00, 'Paid'),
(517, 117, 18000.00, 17000.00, 1000.00, 'Pending'),
(518, 118, 3500.00, 3000.00, 500.00, 'Denied'),
(519, 119, 90.00, 80.00, 10.00, 'Paid'),
(520, 120, 300.00, 250.00, 50.00, 'Paid'),
(521, 121, 45000.00, 44000.00, 1000.00, 'Pending'), -- High cost ICU
(522, 122, 2000.00, 1800.00, 200.00, 'Paid'),
(523, 123, 180.00, 150.00, 30.00, 'Paid'),
(524, 124, 100.00, 80.00, 20.00, 'Paid'),
(525, 125, 30000.00, 29000.00, 1000.00, 'Sent to Collections'),
(526, 126, 5000.00, 4500.00, 500.00, 'Denied'),
(527, 127, 250.00, 200.00, 50.00, 'Paid'),
(528, 128, 15000.00, 0.00, 15000.00, 'Pending'), -- No insurance info yet
(529, 129, 130.00, 100.00, 30.00, 'Paid'),
(530, 130, 200.00, 0.00, 200.00, 'Paid'); -- Self-pay