Hospital Management System

Hospital Management System

(Multi-Table Join Project)

STEP 1: Create Database

				
					CREATE DATABASE hospital_db;

-- Verify database
SHOW DATABASES;

STEP 2: Use Database

				
					USE hospital_db;

-- Check current database
SELECT DATABASE();

STEP 3: Create Patients Table

Stores patient details.

Fields:

  • patient_id
  • patient_name
  • age
  • gender
  • phone
				
					CREATE TABLE patients (
    patient_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_name VARCHAR(100) NOT NULL,
    age INT,
    gender VARCHAR(10),
    phone VARCHAR(15)
);

-- Check structure
DESC patients;

STEP 4: Insert Patients

				
					INSERT INTO patients (patient_name, age, gender, phone) VALUES
('Aman', 25, 'Male', '9876543210'),
('Priya', 30, 'Female', '9876501234'),
('Rahul', 40, 'Male', '9876512345');

-- View patients
SELECT * FROM patients;

STEP 5: Create Doctors Table

Stores doctor details.

Fields:

  • doctor_id
  • doctor_name
  • specialization
  • consultation_fee
				
					CREATE TABLE doctors (
    doctor_id INT PRIMARY KEY AUTO_INCREMENT,
    doctor_name VARCHAR(100),
    specialization VARCHAR(100),
    consultation_fee DECIMAL(10,2)
);

-- Check structure
DESC doctors;

STEP 6: Insert Doctors

				
					INSERT INTO doctors (doctor_name, specialization, consultation_fee) VALUES
('Dr. Sharma', 'Cardiologist', 800),
('Dr. Mehta', 'Dermatologist', 500),
('Dr. Singh', 'Orthopedic', 700);

-- View doctors
SELECT * FROM doctors;

STEP 7: Create Appointments Table

Stores appointment booking details.

Fields:

  • appointment_id
  • patient_id (FK)
  • doctor_id (FK)
  • appointment_date
  • status
				
					CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT,
    doctor_id INT,
    appointment_date DATE,
    status VARCHAR(20),

    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- Check structure
DESC appointments;

STEP 8: Book Appointments

When a patient books appointment, insert record here.

				
					INSERT INTO appointments (patient_id, doctor_id, appointment_date, status) VALUES
(1, 1, '2026-03-05', 'Completed'),
(2, 2, '2026-03-06', 'Completed'),
(3, 1, '2026-03-07', 'Completed'),
(1, 3, '2026-03-08', 'Pending');

-- View appointments
SELECT * FROM appointments;

STEP 9: Create Bills Table

Each completed appointment generates a bill.

Fields:

  • bill_id
  • appointment_id (FK)
  • consultation_fee
  • medicine_charges
  • total_amount (calculated)
				
					CREATE TABLE bills (
    bill_id INT PRIMARY KEY AUTO_INCREMENT,
    appointment_id INT,
    consultation_fee DECIMAL(10,2),
    medicine_charges DECIMAL(10,2),

    total_amount DECIMAL(10,2) GENERATED ALWAYS AS
    (consultation_fee + medicine_charges) STORED,

    FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

-- Check structure
DESC bills;

STEP 10: Generate Bills

Bills are generated for completed appointments.

				
					INSERT INTO bills (appointment_id, consultation_fee, medicine_charges) VALUES
(1, 800, 300),
(2, 500, 200),
(3, 800, 400);

-- View bills
SELECT * FROM bills;
--Students will see total_amount calculated automatically.

STEP 11: View Complete Hospital Report (Multi-Table Join)

Combine patients, doctors, appointments, and bills.

				
					SELECT patients.patient_name,
       doctors.doctor_name,
       doctors.specialization,
       appointments.appointment_date,
       bills.total_amount
FROM bills
JOIN appointments ON bills.appointment_id = appointments.appointment_id
JOIN patients ON appointments.patient_id = patients.patient_id
JOIN doctors ON appointments.doctor_id = doctors.doctor_id;

-- Complete hospital billing report displayed

STEP 12: Doctor-wise Patient Count

Count how many patients each doctor handled.

				
					SELECT doctors.doctor_name,
       COUNT(appointments.patient_id) AS total_patients
FROM appointments
JOIN doctors ON appointments.doctor_id = doctors.doctor_id
GROUP BY doctors.doctor_name;

-- Doctor-wise patient count displayed

🎯 What Students Learn From This Project

  • Multiple tables with relationships
  • Foreign keys
  • Appointment booking system
  • Bill generation
  • Generated columns
  • Multi-table JOIN (4 tables)
  • GROUP BY
  • COUNT
  • Real-world database structure