SQL Projects For Beginners: Hospital Patient Database

Last update on October 23 2024 13:28:17 (UTC/GMT +8 hours)

Hospital Patient Database Management Syatem:

A Hospital Patient Database is designed to manage and track patient information, medical records, doctor details, appointments, treatments, and billing in an efficient way. This system ensures the easy retrieval and management of large volumes of data, automating many aspects of hospital administration. The database can store vital information such as patient history, doctor assignments, prescriptions, and more.

Creating the Database in MySQL or PostgreSQL:

Create the Database:


-- Create the database for the Patient Management System
CREATE DATABASE HospitalPatientDB;
USE HospitalPatientDB;

Create the Tables:

Patients Table:

This structure describes the table columns for storing Patients information in the Hospital Patient Database.

Structure:

Column Name	Data Type	Description
patient_id	INT(Primary Key)	Unique patient identifier (auto-incremented)
name	VARCHAR(255)	Patient’s full name
gender	ENUM('Male', 'Female')	Gender of the patient
dob	DATE	Date of birth
contact_number	VARCHAR(15)	Contact number
address	VARCHAR(255)	Patient's home address
medical_history	TEXT	Past medical history

Code:

CREATE TABLE Patients (
    patient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    dob DATE,
    contact_number VARCHAR(15),
    address VARCHAR(255),
    medical_history TEXT
);

Doctors Table:

The Doctors table is designed to store detailed information about medical professionals within a healthcare system.

Structure:

Column Name	Data Type	Description
doctor_id	INT(Primary Key)	Unique doctor identifier (auto-incremented)
name	VARCHAR(255)	Doctor's full name
specialization	VARCHAR(100)	Doctor’s specialization (e.g., Cardiologist)
contact_number	VARCHAR(15)	Contact number
email	VARCHAR(100)	Doctor’s email address

Code:

-- Create the Doctors table
CREATE TABLE Doctors (
    doctor_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    specialization VARCHAR(100),
    contact_number VARCHAR(15),
    email VARCHAR(100)
);

Appointments Table:

The Appointments table is designed to store information regarding patient appointments within a healthcare system.

Structure:

Column Name	Data Type	Description
appointment_id	INT	Unique appointment identifier (Primary Key)
patient_id	INT	Foreign key from Patients (which patient booked the appointment)
doctor_id	INT	Foreign key from Doctors (which doctor is assigned)
appointment_date	DATE	Appointment date
appointment_time	TIME	Appointment time
status	ENUM('Scheduled', 'Completed', 'Cancelled')	Appointment status

Code:

-- Create the Appointments table
CREATE TABLE Appointments (
    appointment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    appointment_date DATE,
    appointment_time TIME,
    status ENUM('Scheduled', 'Completed', 'Cancelled') DEFAULT 'Scheduled',
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id)
);

Billing Table:

The Billing table is designed to store billing information for medical services provided to patients.

Structure:

Column Name	Data Type	Description
bill_id	INT	Unique bill identifier (Primary Key)
patient_id	INT	Foreign key from Patients (patient being billed)
doctor_id	INT	Foreign key from Doctors (doctor providing service)
bill_date	DATE	Date of the bill
amount	DECIMAL(10, 2)	Total amount billed
status	ENUM('Paid', 'Pending', 'Cancelled')	Billing status

Code:

-- Create the Billing table
CREATE TABLE Billing (
    bill_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    bill_date DATE,
    amount DECIMAL(10, 2),
    status ENUM('Paid', 'Pending', 'Cancelled') DEFAULT 'Pending',
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id)
);

Inserting Data:

Add some sample data to the tables.

Inserting Data into Patients Table:

INSERT INTO Patients (name, gender, dob, contact_number, address, medical_history) 
VALUES 
('John Doe', 'Male', '1985-07-12', '1234567890', '123 Elm St', 'Diabetes, Hypertension'),
('Jane Smith', 'Female', '1990-09-25', '0987654321', '456 Oak St', 'Asthma'),
 ('Michael Scott', 'Male', '1964-03-15', '2223334444', '789 Birch St', 'Hypertension'),
('Pam Beesly', 'Female', '1980-09-25', '9876543210', '456 Cedar St', 'None'),
('Dwight Schrute', 'Male', '1978-01-20', '5556667777', '123 Farm Ln', 'None'),
('Jim Halpert', 'Male', '1979-10-01', '1112223333', '321 Maple St', 'Back Pain');

Inserting Data into Doctors Table:

INSERT INTO Doctors (name, specialization, contact_number, email) 
VALUES 
('Dr. Alice Johnson', 'Cardiologist', '5551234567', '[email protected]'),
('Dr. Robert Brown', 'Dermatologist', '5559876543', '[email protected]'),
('Dr. Meredith Palmer', 'Psychiatrist', '5553334444', '[email protected]'),
('Dr. Stanley Hudson', 'Endocrinologist', '5557778888', '[email protected]');

Inserting Data into Appointments Table:

INSERT INTO Appointments (patient_id, doctor_id, appointment_date, appointment_time, status) 
VALUES 
(1, 1, '2024-10-20', '10:00:00', 'Scheduled'),
(2, 2, '2024-10-21', '11:30:00', 'Scheduled'),
(3, 1, '2024-10-22', '09:00:00', 'Completed'),
(4, 3, '2024-10-23', '12:00:00', 'Scheduled'),
(5, 4, '2024-10-24', '14:00:00', 'Cancelled');

Inserting Data into Billing Table:

INSERT INTO Billing (patient_id, doctor_id, bill_date, amount, status) 
VALUES 
(1, 1, '2024-10-20', 200.00, 'Paid'),
(2, 2, '2024-10-21', 150.00, 'Pending'),
(3, 1, '2024-10-22', 300.00, 'Paid'),
(4, 3, '2024-10-23', 250.00, 'Pending'),
(5, 4, '2024-10-24', 400.00, 'Cancelled');

Basic Functionalities of Hospital Patient Database:

Add New Patient:

Allow users to add a new patient record.

Add New Doctor:

Allow users to add a new doctor to the system.

Schedule Appointments:

Users can schedule appointments with doctors.

Manage Billing:

Create bills, track payments, and generate reports for pending bills.

Update Patient Information:

Modify existing patient data such as contact info or medical history.

Track Attendance:

Monitor patient appointments, mark completed or cancelled.

View Patient History:

Retrieve medical history, appointments, and billing details for any patient.

Writing Queries for Functionality:

Query-1: View All Patients

SELECT * FROM Patients;
-- Retrieves all columns (*) and all records from the Patients table
-- This will display a list of all registered patients in the database

Explanation:

This query selects and retrieves all the data from the Patients table. The * symbol indicates that all columns should be included in the result, displaying every detail about the registered patients.

Output:

patient_id	name	gender	dob	contact_number	address	medical_history
1	John Doe	Male	1985-07-12	1234567890	123 Elm St	Diabetes, Hypertension
2	Jane Smith	Female	1990-09-25	0987654321	456 Oak St	Asthma
3	Michael Scott	Male	1964-03-15	2223334444	789 Birch St	Hypertension
4	Pam Beesly	Female	1980-09-25	9876543210	456 Cedar St	None
5	Dwight Schrute	Male	1978-01-20	5556667777	123 Farm Ln	None
6	Jim Halpert	Male	1979-10-01	1112223333	321 Maple St	Back Pain

Query-2: View Patient's Medical History

SELECT medical_history FROM Patients WHERE patient_id = 1;
-- Retrieves the medical history column for the patient with a specific ID (patient_id = 1)
-- Filters the Patients table to return only the medical history of the patient whose ID is 1

Explanation:

This query is designed to extract the medical history of a specific patient from the Patients table. By using the WHERE clause, it narrows down the result to the patient with patient_id = 1. Only the medical_history column is selected, which means the result will display just the medical history of that patient, without any other details.

Output:

medical_history
Diabetes, Hypertension

Query-3: List All Doctors

SELECT * FROM Doctors;
-- Displays all columns and all records from the Doctors table
-- The asterisk (*) indicates that all available fields (columns) will be selected for each doctor in the system

Explanation:

This query retrieves all the information stored in the Doctors table, displaying every column and record for each doctor. The * symbol ensures that all attributes of each doctor (like name, specialty, contact info, etc.) are included in the result.

Output:

doctor_id	name	specialization	contact_number	email
1	Dr. Alice Johnson	Cardiologist	5551234567	[email protected]
2	Dr. Robert Brown	Dermatologist	5559876543	[email protected]
3	Dr. Meredith Palmer	Psychiatrist	5553334444	[email protected]
4	Dr. Stanley Hudson	Endocrinologist	5557778888	[email protected]

Query-4: View Scheduled Appointments

SELECT * FROM Appointments WHERE status = 'Scheduled';
-- Shows all columns and records from the Appointments table where the appointment status is 'Scheduled'
-- Filters the records to only include appointments that are currently scheduled (excluding canceled, completed, etc.)

Explanation:

This query retrieves a list of all appointments from the Appointments table where the status column is set to 'Scheduled'. It fetches every available column for each appointment (e.g., appointment ID, date, patient, doctor, etc.), but only for those that are marked as scheduled. This is useful for checking upcoming appointments and managing the current schedule.

Output:

appointment_id	patient_id	doctor_id	appointment_date	appointment_time	status
1	1	1	2024-10-20	10:00:00	Scheduled
2	2	2	2024-10-21	11:30:00	Scheduled
4	4	3	2024-10-23	12:00:00	Scheduled

Query-5: Get Appointments for a Specific Patient

	SELECT * FROM Appointments WHERE patient_id = 1;
-- Retrieves all columns and records from the Appointments table where the patient_id is 1
-- Filters the records to show only appointments that belong to the patient with ID 1

Explanation:

This query fetches all appointments for the patient whose patient_id is 1. It returns every column related to that patient's appointments, such as appointment date, doctor, status, and any other details stored in the Appointments table.

Output:

appointment_id	patient_id	doctor_id	appointment_date	appointment_time	status
1	1	1	2024-10-20	10:00:00	Scheduled

Query-6: Get Doctor's Appointments

SELECT * FROM Appointments WHERE doctor_id = 1;
-- Retrieves all columns and records from the Appointments table where the doctor_id is 1
-- Filters the records to show only appointments that belong to the doctor with ID 1

Explanation:

This query retrieves all appointments associated with a specific doctor, identified by the doctor_id of 1. It displays all details of the appointments such as date, time, patient, and appointment status.

Output:

appointment_id	patient_id	doctor_id	appointment_date	appointment_time	status
1	1	1	2024-10-20	10:00:00	Scheduled
3	3	1	2024-10-22	09:00:00	Completed

Query-7: Update Appointment Status

UPDATE Appointments SET status = 'Completed' WHERE appointment_id = 1;
-- Updates the status of an appointment to 'Completed' where the appointment_id is 1
-- Specifically modifies the status of the appointment with ID 1 to indicate it has been completed

Explanation:

This query updates the status of an appointment in the Appointments table to 'Completed' for the appointment with appointment_id = 1. It is useful for marking an appointment as finished once the doctor has seen the patient. This ensures that records are kept up-to-date with the latest status of appointments.

Output:

Select * from Appointments;
appointment_id	patient_id	doctor_id	appointment_date	appointment_time	status
1	1	1	2024-10-20	10:00:00	Completed
2	2	2	2024-10-21	11:30:00	Scheduled
3	3	1	2024-10-22	09:00:00	Completed
4	4	3	2024-10-23	12:00:00	Scheduled
5	5	4	2024-10-24	14:00:00	Cancelled

Query-8: Insert New Billing Record

INSERT INTO Billing (patient_id, doctor_id, bill_date, amount, status)
VALUES (1, 1, '2024-10-18', 250.00, 'Pending');
-- Inserts a new billing record for a patient, specifying the patient ID, doctor ID, date, amount, and billing status

Explanation:

This query inserts a new record into the Billing table. It creates a bill for a patient (with patient_id = 1) who was treated by a doctor (doctor_id = 1) on the date '2024-10-18'. The amount of the bill is set at 250.00, and the status of the bill is 'Pending,' indicating that the payment has not been completed yet.

Output:

Select * from Billing;

bill_id	patient_id	doctor_id	bill_date	amount	status
1	1	1	2024-10-20	200.00	Paid
2	2	2	2024-10-21	150.00	Pending
3	3	1	2024-10-22	300.00	Paid
4	4	3	2024-10-23	250.00	Pending
5	5	4	2024-10-24	400.00	Cancelled
6	1	1	2024-10-18	250.00	Pending

Query-9: View Unpaid Bills

SELECT * FROM Billing WHERE status = 'Pending';
-- Retrieves all records from the Billing table where the bill status is 'Pending'

Explanation:

This query selects and displays all records from the Billing table where the status is marked as 'Pending'. It retrieves details of all bills that have not yet been paid or settled, which allows administrators to identify outstanding payments.

Output:

bill_id	patient_id	doctor_id	bill_date	amount	status
2	2	2	2024-10-21	150.00	Pending
4	4	3	2024-10-23	250.00	Pending

Query-10: Get Total Bills for a Specific Patient

SELECT SUM(amount) FROM Billing WHERE patient_id = 1;
-- Sums up the total amount of all bills for a specific patient with patient_id = 1

Explanation:

This query calculates the total billing amount for a specific patient by summing the values in the amount column for all records where the patient_id is 1. It helps in getting an overview of the total expenses incurred by the patient for the services received. This is useful for billing summaries or patient account statements.

Output:

SUM(amount)
200.00

Query-11: Mark Bill as Paid

UPDATE Billing SET status = 'Paid' WHERE bill_id = 1;
-- Updates the status of a bill to 'Paid' for the record where bill_id is 1

Explanation:

This query modifies the Billing table by changing the status of the bill with bill_id = 1 from its current value to 'Paid'. This is useful when a patient has settled their bill, and the system needs to reflect that the payment has been made. It ensures proper tracking of billing statuses.

Output:

Select * from Billing;
bill_id	patient_id	doctor_id	bill_date	amount	status
1	1	1	2024-10-20	200.00	Paid
2	2	2	2024-10-21	150.00	Pending
3	3	1	2024-10-22	300.00	Paid
4	4	3	2024-10-23	250.00	Pending
5	5	4	2024-10-24	400.00	Cancelled

Query-12: Count Total Patients

SELECT COUNT(*) FROM Patients;
-- Returns the total number of patients in the database

Explanation:

This query counts the total number of records in the Patients table, providing a simple way to determine how many patients are currently registered in the database. The COUNT(*) function counts all rows, which helps healthcare administrators assess patient load and manage resources accordingly.

Output:

COUNT(*)
6

Query-13: View All Patients With a Specific Doctor

SELECT Patients.name                           -- Selects the name of the patients
FROM Patients                                  -- From the Patients table
INNER JOIN Appointments ON Patients.patient_id = Appointments.patient_id  -- Joins with Appointments table on patient_id
WHERE Appointments.doctor_id = 1;             -- Filters results for appointments with a specific doctor

Explanation:

This SQL query retrieves the names of patients who have appointments with a particular doctor, specified by doctor_id = 1. It utilizes an INNER JOIN to connect the Patients and Appointments tables through the common field patient_id, ensuring that only records with matching patient IDs are included in the result set. The WHERE clause filters the data to show only those patients who are associated with the specified doctor.

Output:

name
John Doe
Michael Scott

Query-14: View Billing Report for a Specific Month

SELECT *                                    -- Selects all columns from the Billing table
FROM Billing                               -- From the Billing table
WHERE MONTH(bill_date) = 10               -- Filters results to include only records from October
AND YEAR(bill_date) = 2024;               -- Filters results to include only records from the year 2024

Explanation:

This SQL query retrieves all billing records from the Billing table specifically for the month of October in the year 2024. The use of the MONTH() and YEAR() functions allows for filtering based on the bill_date, ensuring that only relevant billing information is returned.

Output:

bill_id	patient_id	doctor_id	bill_date	amount	status
1	1	1	2024-10-20	200.00	Paid
2	2	2	2024-10-21	150.00	Pending
3	3	1	2024-10-22	300.00	Paid
4	4	3	2024-10-23	250.00	Pending
5	5	4	2024-10-24	400.00	Cancelled

Query-15: Find Patients Who Have Had Appointments With More Than One Doctor

SELECT p.name, COUNT(DISTINCT a.doctor_id) AS doctor_count  -- Selects patient name and counts the distinct number of doctors the patient has seen
FROM Patients p                                            -- From the Patients table (alias 'p')
JOIN Appointments a ON p.patient_id = a.patient_id         -- Joins the Appointments table on the patient_id to find corresponding appointments
GROUP BY p.patient_id                                      -- Groups the results by the patient_id (each patient)
HAVING doctor_count > 1;                                   -- Filters the results to include only patients who have seen more than one doctor

Explanation:

This query retrieves a list of patients who have had appointments with more than one doctor. It joins the Patients and Appointments tables based on patient_id. The COUNT(DISTINCT a.doctor_id) function counts the unique number of doctors each patient has seen. By grouping the results by patient_id and using the HAVING clause, it filters out patients who have been seen by only one doctor, displaying only those who have appointments with multiple doctors. This is useful for tracking patients who may have needed specialist consultations.

Output:

Output not generated for insufficient data

Query-16: Get a List of Doctors and Their Total Appointments in a Specific Month

SELECT d.name AS doctor_name, COUNT(a.appointment_id) AS total_appointments  -- Selects the doctor's name and counts the total number of appointments
FROM Doctors d                                                                -- From the Doctors table (alias 'd')
JOIN Appointments a ON d.doctor_id = a.doctor_id                               -- Joins the Appointments table on doctor_id to match each doctor with their appointments
WHERE MONTH(a.appointment_date) = 10 AND YEAR(a.appointment_date) = 2024       -- Filters the appointments to only include those from October 2024
GROUP BY d.doctor_id;                                                          -- Groups the results by doctor_id to get the count of appointments per doctor

Explanation:

This query retrieves the number of appointments each doctor had in October 2024. It joins the Doctors and Appointments tables based on the doctor_id, allowing the query to count the total appointments for each doctor. The WHERE clause restricts the results to only appointments that occurred in October 2024 by using the MONTH() and YEAR() functions. Finally, the query groups the results by doctor_id, giving the count of appointments for each doctor in that specific month. This helps track doctor activity within a specific period.

Output:

doctor_name	total_appointments
Dr. Alice Johnson	2
Dr. Robert Brown	1
Dr. Meredith Palmer	1
Dr. Stanley Hudson	1

Query-17: Retrieve Total Income Generated From Appointments for Each Doctor

SELECT d.name AS doctor_name, SUM(b.amount) AS total_income          -- Selects the doctor's name and sums up the total income from paid bills
FROM Doctors d                                                      -- From the Doctors table (alias 'd')
JOIN Billing b ON d.doctor_id = b.doctor_id                          -- Joins the Billing table on doctor_id to match each doctor with their billing records
WHERE b.status = 'Paid'                                              -- Filters the billing records to only include those with a 'Paid' status
GROUP BY d.doctor_id;                                                -- Groups the results by doctor_id to calculate the total income per doctor

Explanation:

This query calculates the total income generated by each doctor from paid bills. It joins the Doctors and Billing tables using doctor_id to link doctors with their associated billing records. The WHERE clause filters the records to include only those that have a status of 'Paid,' ensuring that only actual income is considered. The SUM() function then adds up the amount for each doctor, and the results are grouped by doctor_id to show the total income earned by each doctor.

Output:

doctor_name	total_income
Dr. Alice Johnson	500.00

Query-18: Find Patients Who Have Not Paid Their Bills (Pending Bills)

SELECT p.name, b.amount, b.bill_date                                   -- Selects the patient's name, bill amount, and bill date
FROM Patients p                                                        -- From the Patients table (alias 'p')
JOIN Billing b ON p.patient_id = b.patient_id                           -- Joins the Billing table on patient_id to match patients with their billing records
WHERE b.status = 'Pending';                                             -- Filters the billing records to only include those with a 'Pending' status

Explanation:

This query retrieves a list of patients who have unpaid bills, showing the name of the patient, the amount due, and the bill_date. The JOIN clause connects the Patients table with the Billing table using the patient_id, ensuring each patient's billing information is displayed. The WHERE clause filters the results to include only those bills that have a status of 'Pending,' meaning they are unpaid. This query is useful for identifying patients with outstanding balances.

Output:

name	amount	bill_date
Jane Smith	150.00	2024-10-21
Pam Beesly	250.00	2024-10-23

Query-19: Get a List of Patients Who Have Missed (Cancelled) Appointments

SELECT p.name, a.appointment_date                                        -- Selects the patient's name and the appointment date
FROM Patients p                                                         -- From the Patients table (alias 'p')
JOIN Appointments a ON p.patient_id = a.patient_id                       -- Joins the Appointments table on patient_id to match patients with their appointments
WHERE a.status = 'Cancelled';                                            -- Filters the results to include only appointments that have a status of 'Cancelled'

Explanation:

This query retrieves a list of patients who had their appointments canceled. It displays the name of the patient and the appointment_date. The JOIN connects the Patients and Appointments tables using the patient_id field, which ensures that each patient's appointment information is correctly linked. The WHERE clause filters the results to show only those appointments with a status of 'Cancelled.' This query is useful for tracking canceled appointments and notifying affected patients.

Output:

name	appointment_date
Dwight Schrute	2024-10-24

Query-20: Calculate the Average Bill Amount for All Patients

SELECT AVG(amount) AS average_bill_amount      -- Calculates the average of the 'amount' field and assigns it an alias 'average_bill_amount'
FROM Billing;                                  -- Retrieves data from the 'Billing' table

Explanation:

This query computes the average bill amount for all patients by applying the AVG() function on the amount field from the Billing table. The result, labeled as average_bill_amount, reflects the average of all recorded bill amounts in the system. This query is useful for understanding billing trends, such as the typical amount patients are charged for services.

Output:

average_bill_amount
260.000000

Query-21: List of Doctors Who Have Had No Appointments in the Last Month

SELECT d.name                                              -- Selects the name of doctors
FROM Doctors d                                             -- Retrieves data from the 'Doctors' table
LEFT JOIN Appointments a ON d.doctor_id = a.doctor_id      -- Left joins the 'Appointments' table on the doctor_id to include all doctors, even if they have no appointments
AND a.appointment_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)  -- Filters for appointments within the past month using the current date
WHERE a.appointment_id IS NULL;                            -- Filters the result to only include doctors with no appointments (appointment_id is null)

Explanation:

This query identifies doctors who have not had any appointments in the past month. It uses a LEFT JOIN to include all doctors, even if they have no associated appointments, and checks for appointments within the last month using DATE_SUB(CURDATE(), INTERVAL 1 MONTH). The condition WHERE a.appointment_id IS NULL ensures that only doctors without appointments in the specified timeframe are returned. This query helps track inactive doctors.

Output:

Output not generated for insufficient data

Query-22: Find the Most Frequent Patients (Top 3 Patients Based on Number of Appointments)

SELECT p.name, COUNT(a.appointment_id) AS total_appointments  -- Selects the patient's name and counts the total number of appointments for each patient
FROM Patients p                                               -- Retrieves data from the 'Patients' table
JOIN Appointments a ON p.patient_id = a.patient_id            -- Joins the 'Appointments' table on patient_id to match patients with their appointments
GROUP BY p.patient_id                                         -- Groups the results by patient_id to aggregate the count of appointments per patient
ORDER BY total_appointments DESC                              -- Orders the results in descending order based on the total number of appointments
LIMIT 3;                                                      -- Limits the results to the top 3 patients with the most appointments

Explanation:

This query retrieves the top 3 patients who have the highest number of appointments. It groups the patients by their patient_id and counts their appointments, then orders the results in descending order of the total appointment count. Finally, it limits the result to the top 3 patients. This query can help identify the most frequently scheduled patients.

Output:

name	total_appointments
John Doe	1
Jane Smith	1
Michael Scott	1

Query-23: Retrieve All Patients Who Have Been Assigned to a Specific Doctor

SELECT p.name                                             -- Selects the patient's name
FROM Patients p                                           -- Retrieves data from the 'Patients' table
JOIN Appointments a ON p.patient_id = a.patient_id        -- Joins the 'Appointments' table with 'Patients' based on matching patient_id
WHERE a.doctor_id = 1;                                    -- Filters the appointments where the doctor_id is 1

Explanation:

This query retrieves the names of all patients who have been assigned to a specific doctor, identified by doctor_id = 1. It performs a join between the Patients and Appointments tables based on patient_id to match patients with their appointments, then filters the results to show only the appointments assigned to the specified doctor.

Output:

name
John Doe
Michael Scott

Query-24: Get Total Appointments Per Day

SELECT appointment_date,                                      -- Selects the appointment date
       COUNT(appointment_id) AS total_appointments            -- Counts the total number of appointments for each date
FROM Appointments                                             -- Retrieves data from the 'Appointments' table
GROUP BY appointment_date;                                    -- Groups the result by each unique appointment date

Explanation:

This query displays the total number of appointments scheduled for each day by grouping the appointments based on their appointment_date. The COUNT(appointment_id) function counts the number of appointments on each specific date, and the result is shown alongside the corresponding date.

Output:

appointment_date	total_appointments
2024-10-20	1
2024-10-21	1
2024-10-22	1
2024-10-23	1
2024-10-24	1

Query-25: Find Patients Who Have Not Visited in the Last 6 Months

SELECT p.name                                                  -- Selects the name of the patients
FROM Patients p                                                -- Retrieves data from the 'Patients' table
LEFT JOIN Appointments a ON p.patient_id = a.patient_id         -- Performs a LEFT JOIN with 'Appointments' table on patient_id
AND a.appointment_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) -- Filters for appointments that occurred in the last 6 months
WHERE a.appointment_id IS NULL;                                -- Ensures only patients with no appointments in the last 6 months are selected

Explanation:

This query retrieves a list of patients who have not had any appointments in the last 6 months. It does this by performing a LEFT JOIN between the Patients and Appointments tables. The condition a.appointment_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) filters out appointments in the last 6 months. The WHERE a.appointment_id IS NULL ensures that only patients without any recent appointments are listed.

Output:

name
Jim Halpert

Query-26: Get All Paid Bills Along With the Responsible Doctor

SELECT p.name AS patient_name, d.name AS doctor_name, b.amount   -- Selects patient name, doctor name, and bill amount
FROM Billing b                                                  -- Retrieves data from the 'Billing' table
JOIN Patients p ON b.patient_id = p.patient_id                  -- Joins the 'Patients' table to get the patient name
JOIN Doctors d ON b.doctor_id = d.doctor_id                     -- Joins the 'Doctors' table to get the doctor name
WHERE b.status = 'Paid';                                        -- Filters the result to show only paid bills

Explanation:

This query retrieves a list of all paid bills, showing the corresponding patient’s name, the doctor's name responsible for the treatment, and the bill amount. It joins the Billing table with the Patients and Doctors tables to match the patient and doctor details using their respective IDs. Only records with a bill status of 'Paid' are included.

Output:

patient_name	doctor_name	amount
John Doe	Dr. Alice Johnson	200.00
Michael Scott	Dr. Alice Johnson	300.00

Query-27: Calculate Total Revenue by Month

SELECT MONTH(b.bill_date) AS month, YEAR(b.bill_date) AS year, SUM(b.amount) AS total_revenue  -- Selects the month and year from the bill date, and calculates the total revenue from the amount
FROM Billing b                                                                                   -- Retrieves data from the 'Billing' table
WHERE b.status = 'Paid'                                                                         -- Filters the records to include only those with a 'Paid' status
GROUP BY YEAR(b.bill_date), MONTH(b.bill_date);                                                -- Groups the results by year and month of the bill date

Explanation:

This query calculates the total revenue generated from paid bills on a monthly basis. It extracts the month and year from the bill_date and sums up the amount for each group of records that have a status of 'Paid'. The results are organized by year and month, allowing for an overview of revenue trends over time.

Output:

month	year	total_revenue
10	2024	500.00

Query-28: Find Doctors Specializing in More Than One Area

SELECT d.name, COUNT(d.specialization) AS specialization_count  -- Selects the doctor's name and counts their specializations
FROM Doctors d                                                  -- Retrieves data from the 'Doctors' table
GROUP BY d.doctor_id                                          -- Groups the results by doctor ID to aggregate specializations
HAVING specialization_count > 1;                               -- Filters doctors with more than one specialization

Explanation:

This query is designed to list the names of doctors who have multiple specializations. It counts the number of specializations for each doctor and groups the results by the doctor ID to ensure that the counts are correctly aggregated. The HAVING clause is then used to filter the results, displaying only those doctors who have more than one specialization. This can help identify highly qualified professionals within a healthcare system, which may be useful for patient referrals and treatment options.

Output:

Output not generated for insufficient data

Query-29: List All Patients Along With Their Appointment History and Billing Status

SELECT p.name, a.appointment_date, a.status AS appointment_status, b.status AS billing_status  -- Selects patient names, appointment dates, and the statuses of appointments and billing
FROM Patients p                                                                                   -- Retrieves data from the 'Patients' table
LEFT JOIN Appointments a ON p.patient_id = a.patient_id                                         -- Left joins the 'Appointments' table to include appointment data for each patient
LEFT JOIN Billing b ON p.patient_id = b.patient_id                                               -- Left joins the 'Billing' table to include billing status for each patient

Explanation:

This query retrieves a comprehensive list of all patients along with their corresponding appointment and billing statuses. By using left joins, it ensures that all patients are included in the results, even if they do not have any associated appointments or billing records. The selection of appointment and billing statuses allows for a quick overview of each patient's current engagement with the healthcare system, making it easier to identify any outstanding appointments or unpaid bills.

Output:

name	appointment_date	appointment_status	billing_status
John Doe	2024-10-20	Scheduled	Paid
Jane Smith	2024-10-21	Scheduled	Pending
Michael Scott	2024-10-22	Completed	Paid
Pam Beesly	2024-10-23	Scheduled	Pending
Dwight Schrute	2024-10-24	Cancelled	Cancelled
Jim Halpert	NULL	NULL	NULL

SQL Code Editor: