Introduction
Data is information stored in a computer and a Database is a place we store data in an organized way.
Types of databases
SQL databases also knowns as relational databases store data in tables with rows and columns.They use structured query language for defininng and manipulating data.They use a schema(predefined structure)eg Postgresql,Mysql,Oracle,Microsoft sql server and sqlite.
No SQL(Non Relational Databases)-are designed to store data without contraint of a table form structure ,they can handle unstructured or demi structured data like Json and text data eg Mongodb,Apache CouchdB
SQL Databases
Data is organized in tables
The tables have relations eg Common ID and are connected using keys
We talk to the tables using the structured query language.
Sql databases require pre-defined schemas
A schema is like a blue print.
No SQL
No sql databases have dynamic schemas meaning they have no predefined schemas eg Mongo Db(data is stored like a dictionary meaning its flexible
Types of Sql commands
Data Definition Language(DDL)-manages the database blueprint.
Create-Used to build new tables, databases, or schemas from scratch.
CREATE TABLE table_name (
column1_name DATA_TYPE CONSTRAINTS,
column2_name DATA_TYPE CONSTRAINTS
);
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
enrollment_date DATE DEFAULT CURRENT_DATE
);
Alter- Used to modify the structure of an existing table (adding, dropping, or renaming columns/constraints).
Adding a column
ALTER TABLE students
ADD COLUMN phone_number VARCHAR(15);
Dropping a Column:ALTER TABLE students `
DROP COLUMN phone_number;
Renaming a Column:
ALTER TABLE students
RENAME COLUMN email TO student_email;
Drop- Deletes a table or database permanently along with all its data, structural definition, constraints, and indexes.
Syntax DROP TABLE table_name;
Eg DROP TABLE students;
Truncate-Removes all rows from a table instantly while keeping the table structure intact for future data. It is faster than DELETE because it bypasses individual row scanning.
Syntax TRUNCATE TABLE table_name;
Example TRUNCATE TABLE students;
DML(Data Manipulation Language)-is a subset of sql used to manage,retrieve,modify and update data stored within a database tables.Dml focuses purely on the records and data rows inside those tables
Select-used to query and view data from a database
eg SELECT * FROM Customers;
Insert-used to insert new records into a table
eg Insert Into (first_name)
Values ('Esther');
Update-used to change existing orders
We always use a Where clause with an update statement otherwise every single row on the table will be updated.
eg UPDATE employees
SET department='product manager'
Where emp_id =1;
Delete-Used to remove rows from a table.
eg DELETE FROM employees
WHERE emp_id =20;
For better understanding we will use a Hospital schema overview.
Our Hospital Database Schema Overview
patients: patient_id, name, age, gender
doctors: doctor_id, name, department_id, supervisor_id
departments: department_id, dept_name
admissions: admission_id, patient_id, doctor_id, admission_date, discharge_date, room_cost
treatments: treatment_id, admission_id, treatment_name, cost
SQL Window Functions
Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike regular aggregate functions, window functions do not collapse rows into a single summary row; each row retains its individual identity in the output.
Understanding the Architecture: OVER(), PARTITION BY, ORDER BY, and Window Frames
OVER(): Defines the window or set of rows the function operates on.
PARTITION BY: Divides the rows into logical groups (similar to GROUP BY, but without collapsing rows).
ORDER BY: Determines the logical order of rows within each partition.
Window Frames (ROWS BETWEEN ...): Narrows down the operating window relative to the current row (e.g., moving averages).
Types of Window Functions & Hospital Examples
Ranking Functions
ROW_NUMBER(): Assigns a unique sequential integer to rows starting from 1 within a partition.
RANK(): Assigns a rank to each row with gaps for ties.
DENSE_RANK(): Assigns a rank without gaps for ties.
NTILE(n): Distributes rows into a specified number of roughly equal groups (buckets).
Hospital Scenario: Rank patient room costs within each department to find the most expensive stays.
SELECT
department_id,
patient_id,
room_cost,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY room_cost DESC) AS row_num,
RANK() OVER (PARTITION BY department_id ORDER BY room_cost DESC) AS rank_val,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY room_cost DESC) AS dense_rank_val,
NTILE(2) OVER (PARTITION BY department_id ORDER BY room_cost DESC) AS cost_bucket
FROM admissions a
JOIN doctors d ON a.doctor_id = d.doctor_id;
Explanation: ROW_NUMBER gives every row a unique number even if costs tie. RANK and DENSE_RANK handle ties differently (RANK leaves gaps, DENSE_RANK does not). NTILE(2) splits the department admissions into two equal halves (e.g., high-cost vs. low-cost buckets).
Value Functions
LAG() & LEAD(): Access data from a previous or subsequent row within the same result set.
FIRST_VALUE() & LAST_VALUE(): Retrieve the first or last value in an ordered window frame.
Hospital Scenario: Compare a patient's current admission room cost with their previous admission cost.
SELECT
patient_id,
admission_date,
room_cost,
LAG(room_cost, 1) OVER (PARTITION BY patient_id ORDER BY admission_date) AS prev_room_cost,
LEAD(room_cost, 1) OVER (PARTITION BY patient_id ORDER BY admission_date) AS next_room_cost,
FIRST_VALUE(room_cost) OVER (PARTITION BY patient_id ORDER BY admission_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_cost
FROM admissions;
Explanation: LAG fetches the cost of the patient's prior visit, helping doctors spot escalating medical expenses over time. FIRST_VALUE looks across the entire partition to retrieve the patient's initial recorded admission cost.
Aggregate Window Functions (SUM, AVG, COUNT, MIN, MAX)
These functions compute cumulative or moving summaries.
Hospital Scenario: Calculate a running total of room costs for each patient as they accumulate multiple admissions.
SELECT
patient_id,
admission_date,
room_cost,
SUM(room_cost) OVER (PARTITION BY patient_id ORDER BY admission_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_cost,
AVG(room_cost) OVER (PARTITION BY patient_id) AS avg_patient_cost,
COUNT(admission_id) OVER (PARTITION BY patient_id) AS total_admissions,
MIN(room_cost) OVER (PARTITION BY patient_id) AS lowest_cost,
MAX(room_cost) OVER (PARTITION BY patient_id) AS highest_cost
FROM admissions;
Explanation: The SUM with a window frame (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) creates a running total of expenses over time for each patient.
SQL Joins
SQL Joins combine columns from two or more tables based on a related column between them.
We use Joins when we want to re-combine data, for data enrichment and filtering data by checking the existence of records in a table.
INNER JOIN Returns records that have matching values in both tables.Finding patients who currently have active admissions.
LEFT JOIN Returns all records from the left table, and matched records from the right.Listing all patients, including those who have never been admitted.
RIGHT JOIN returns all records from the right table, and matched records from the left. Listing all admissions ensures every admission record shows up even if patient info is missing.
FULL OUTER JOIN Returns all records when there is a match in either left or right table.Auditing all patients and all admissions to see complete mismatches.
CROSS JOIN Produces the Cartesian product of the two tables.Generating every possible combination of doctors and department shifts for scheduling.
SELF JOIN A table is joined with itself.Finding supervisor-subordinate relationships among hospital doctors.
INNER JOIN vs LEFT JOIN
-- Inner Join: Only patients with admissions
SELECT p.name, a.admission_date, a.room_cost
FROM patients p
INNER JOIN admissions a ON p.patient_id = a.patient_id;
-- Left Join: All patients, including healthy ones without admissions
SELECT p.name, a.admission_date, a.room_cost
FROM patients p
LEFT JOIN admissions a ON p.patient_id = a.patient_id;
SELF JOIN (Doctor Supervision)
-- Finding doctors and their respective supervisors
SELECT
doc.name AS doctor_name,
sup.name AS supervisor_name
FROM doctors doc
LEFT JOIN doctors sup ON doc.supervisor_id = sup.doctor_id;
Explanation: The SELF JOIN connects the doctors table to itself using doctor_id and supervisor_id to map reporting hierarchies.
Common Table Expressions (CTEs)
A CTE is a temporary result **set defined within the execution scope of a single SELECT, INSERT, UPDATE or DELETE statement.
**Basic and Multiple CTEs
CTEs improve readability by breaking complex queries into logical blocks.
WITH HighCostAdmissions AS (
SELECT patient_id, room_cost, admission_date
FROM admissions
WHERE room_cost > 5000
),
PatientDetails AS (
SELECT patient_id, name, gender
FROM patients
WHERE age > 60
)
SELECT pd.name, hca.room_cost, hca.admission_date
FROM PatientDetails pd
JOIN HighCostAdmissions hca ON pd.patient_id = hca.patient_id;
Recursive CTEs
A recursive CTE references itself making it ideal for hierarchical data (like organizational structures or multi-step treatment pathways).
WITH RECURSIVE DoctorHierarchy AS (
-- Anchor member: Top-level supervisor (no supervisor)
` SELECT doctor_id, name, supervisor_id, 1 AS level
FROM doctors
WHERE supervisor_id IS NULL
UNION ALL
-- Recursive member: Find subordinates
SELECT d.doctor_id, d.name, d.supervisor_id, dh.level + 1
FROM doctors d
JOIN DoctorHierarchy dh ON d.supervisor_id = dh.doctor_id
)
SELECT * FROM DoctorHierarchy;`
CTEs vs. Subqueries
Readability: CTEs use a clean top-down structure (WITH ... SELECT ...), whereas nested subqueries can become hard to read.
Reusability: A CTE can be referenced multiple times within the main query body, whereas a subquery must be repeated or rewritten.
Recursion: CTEs support recursion (WITH RECURSIVE), which standard subqueries cannot do.
** SQL Subqueries**
A subquery is a query nested inside another SQL statement (like SELECT, FROM, or WHERE).
Types of Subqueries
Scalar vs. Correlated vs. Non-Correlated
Scalar Subquery: Returns a single value (one row, one column).
Non-Correlated Subquery: Can be run independently of the outer query.
Correlated Subquery: Depends on values from the outer query and executes row-by-row.
-- Non-Correlated Subquery (Find patients with room cost above average)
SELECT name
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM admissions
WHERE room_cost > (SELECT AVG(room_cost) FROM admissions)
);
-- Correlated Subquery (Find admissions with a room cost higher than the average cost of their specific department)
SELECT a1.admission_id, a1.room_cost, a1.department_id
FROM admissions a1
WHERE a1.room_cost > (
SELECT AVG(a2.room_cost)
FROM admissions a2
WHERE a2.doctor_id IN (SELECT doctor_id FROM doctors WHERE department_id = a1.department_id)
);
Subqueries with IN, EXISTS, ANY, and ALL
IN: Checks if a value matches any value in a subquery list.
EXISTS: Returns true if the subquery returns any rows (highly efficient for checking existence).
ANY: Compares a value to any value returned by the subquery.
ALL: Compares a value to all values returned by the subquery
-- Using EXISTS to find patients who have had at least one admission
SELECT name
FROM patients p
WHERE EXISTS (
SELECT 1
FROM admissions a
WHERE a.patient_id = p.patient_id
);
Subqueries in SELECT, FROM, and WHERE Clauses
SELECT Clause: To calculate contextual metrics per row.
FROM Clause (Derived Table): Acts as a temporary table.
WHERE Clause: For filtering
-- Subquery in SELECT clause
SELECT
p.name,
(SELECT MAX(room_cost) FROM admissions a WHERE a.patient_id = p.patient_id) AS max_cost
FROM patients p;
Conclusion
By mastering Window Functions, Joins, CTEs and Subqueries i can handle complex healthcare analytics queries; from tracking patient cost trajectories, to managing doctor hierarchies and departmental performance metrics.
Top comments (0)