SQL learning notes

SQL for Data Analysis and Reporting

By Imonikhe Ayeni

This guide starts with installing a database tool and writing simple queries, then moves through filtering, NULL handling, summaries, joins, common table expressions, advanced window functions, date and time analysis, data-quality checks and reporting. Work through the sections in order if you are new to SQL, or use the contents panel to find a specific topic.

Beginner to intermediateAbout 15 minutesLast updated October 2026Includes interview refresher

A note from the author

I’m Imonikhe Ayeni. I use SQL to extract, check and summarise data for analysis and reporting.

SQL is easiest to learn by writing small queries. Start with SELECT and WHERE, then add summaries and joins once those feel natural.

No matching sections were found. Try a broader search term.

Installing SQL tools

SQL is a language rather than a single application. To practise it, you need a database system and a tool for writing queries. SQLite is the simplest place to start, while PostgreSQL is a strong next step for real projects.

Beginner option: SQLite

1. Install DB Browser for SQLite, or use SQLite through Python.
2. Create a new database file.
3. Create a table or import a CSV file.
4. Open the SQL editor.
5. Run your first SELECT query.

PostgreSQL option

1. Download PostgreSQL from the official PostgreSQL website.
2. Install PostgreSQL and pgAdmin.
3. Set a password for the postgres administrator account.
4. Open pgAdmin.
5. Create a database named sql_learning.
6. Open the Query Tool.

Test the connection

SELECT version();

Database basics

A relational database stores information in tables. Each table contains rows and columns, and related tables are connected through keys.

Create a table

CREATE TABLE patients (
    patient_id INTEGER PRIMARY KEY,
    patient_name VARCHAR(100) NOT NULL,
    age INTEGER,
    gender VARCHAR(30),
    hospital_id INTEGER
);

Insert rows

INSERT INTO patients (
    patient_id,
    patient_name,
    age,
    gender,
    hospital_id
)
VALUES
    (1, 'Alice Brown', 45, 'Female', 101),
    (2, 'Ben Smith', 62, 'Male', 102),
    (3, 'Chidi Okafor', 38, 'Male', 101);

View the table

SELECT *
FROM patients;

Selecting data

SELECT is the command you will use most often. It retrieves columns from one or more tables.

Select every column

SELECT *
FROM patients;

Select specific columns

SELECT
    patient_name,
    age,
    gender
FROM patients;

Rename output columns

SELECT
    patient_name AS name,
    age AS patient_age
FROM patients;

Return unique values

SELECT DISTINCT gender
FROM patients;

Filtering rows

WHERE limits the rows returned by a query. Conditions can be combined with AND, OR and NOT.

Basic conditions

SELECT *
FROM patients
WHERE age >= 50;

Multiple conditions

SELECT *
FROM patients
WHERE age >= 40
  AND gender = 'Female';

IN, BETWEEN and LIKE

SELECT *
FROM patients
WHERE hospital_id IN (101, 102);

SELECT *
FROM patients
WHERE age BETWEEN 40 AND 65;

SELECT *
FROM patients
WHERE patient_name LIKE 'A%';

Missing values

SELECT *
FROM patients
WHERE age IS NULL;

SELECT *
FROM patients
WHERE age IS NOT NULL;

Sorting and limiting results

ORDER BY controls the order of results. LIMIT is useful while exploring large tables.

Sort ascending and descending

SELECT
    patient_name,
    age
FROM patients
ORDER BY age DESC;

Sort by more than one column

SELECT *
FROM patients
ORDER BY hospital_id ASC, age DESC;

Limit the output

SELECT *
FROM patients
ORDER BY age DESC
LIMIT 10;

Calculated columns and CASE

SQL can create new values while the query runs. CASE is used to apply conditional logic.

Arithmetic

SELECT
    patient_name,
    age,
    age + 5 AS age_in_five_years
FROM patients;

CASE expression

SELECT
    patient_name,
    age,
    CASE
        WHEN age < 18 THEN 'Child'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Older adult'
    END AS age_group
FROM patients;

Handle nulls

SELECT
    patient_name,
    COALESCE(gender, 'Not recorded') AS gender
FROM patients;

NULL and missing values

NULL represents an unknown, missing or not-applicable value. It is not zero, an empty string or FALSE. NULL needs its own comparison rules, and it can change the result of arithmetic, aggregation, joins and filters.

Concept first: what NULL changes

Comparisons: use IS NULL or IS NOT NULL. Expressions such as = NULL do not work as normal equality tests.

Arithmetic: a calculation involving NULL normally returns NULL, so replace NULL first when a business rule justifies a default.

Aggregates: COUNT(column), SUM, AVG, MIN and MAX generally ignore NULL inputs, while COUNT(*) counts rows.

Joins: an unmatched row from the preserved side of a LEFT JOIN produces NULLs for columns from the other table.

Need	Portable / common approach	Dialect notes
Test for missing values	`IS NULL`, `IS NOT NULL`	Standard SQL pattern
Return the first available value	`COALESCE(a, b, c)`	Portable across major systems
Return NULL when two expressions are equal	`NULLIF(a, b)`	Useful for divide-by-zero protection
Replace NULL with one fallback	`COALESCE(value, fallback)`	SQL Server also has `ISNULL(value, fallback)`; MySQL/SQLite have `IFNULL`
Null-safe equality	Dialect-specific	PostgreSQL: `IS NOT DISTINCT FROM`; MySQL: `<=>`

IS NULL and IS NOT NULL

-- Correct
SELECT *
FROM patients
WHERE age IS NULL;

SELECT *
FROM patients
WHERE age IS NOT NULL;

-- Do not write:
-- WHERE age = NULL;

COALESCE: return the first non-NULL value

SELECT
    patient_name,
    COALESCE(phone_mobile, phone_home, 'No phone recorded') AS contact_number
FROM patients;

SQL Server ISNULL vs COALESCE

-- SQL Server: two arguments
SELECT ISNULL(gender, 'Not recorded') AS gender
FROM patients;

-- Standard SQL: two or more arguments
SELECT COALESCE(gender, ethnicity, 'Not recorded') AS first_available_value
FROM patients;

NULLIF: protect a division

SELECT
    practice_id,
    responses,
    invitations,
    100.0 * responses / NULLIF(invitations, 0) AS response_rate
FROM practice_summary;

COUNT(*) vs COUNT(column)

SELECT
    COUNT(*) AS all_rows,
    COUNT(age) AS rows_with_age,
    COUNT(*) - COUNT(age) AS missing_age
FROM patients;

NULL in arithmetic

SELECT
    order_id,
    subtotal,
    discount,
    subtotal - discount AS may_be_null,
    subtotal - COALESCE(discount, 0) AS final_amount
FROM orders;

Find rows with no matching record: anti join

-- LEFT anti join
SELECT c.*
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

-- Often clearer:
SELECT c.*
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

Be careful with NOT IN when NULL is possible

-- This can behave unexpectedly if the subquery returns NULL:
SELECT *
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

-- Prefer NOT EXISTS when the subquery key may contain NULLs.

Summary statistics

Aggregate functions reduce many rows to summary values such as counts, totals and averages.

Common aggregate functions

SELECT
    COUNT(*) AS patients,
    AVG(age) AS mean_age,
    MIN(age) AS youngest_age,
    MAX(age) AS oldest_age
FROM patients;

Group summaries

SELECT
    gender,
    COUNT(*) AS patients,
    AVG(age) AS mean_age
FROM patients
GROUP BY gender;

Filter grouped results

SELECT
    hospital_id,
    COUNT(*) AS patients
FROM patients
GROUP BY hospital_id
HAVING COUNT(*) >= 10;

Joining tables

Joins combine related tables. The join condition should identify how rows in one table correspond to rows in another.

Create a second table

CREATE TABLE hospitals (
    hospital_id INTEGER PRIMARY KEY,
    hospital_name VARCHAR(150),
    region VARCHAR(100)
);

INNER JOIN

SELECT
    p.patient_name,
    p.age,
    h.hospital_name
FROM patients AS p
INNER JOIN hospitals AS h
    ON p.hospital_id = h.hospital_id;

LEFT JOIN

SELECT
    p.patient_name,
    h.hospital_name
FROM patients AS p
LEFT JOIN hospitals AS h
    ON p.hospital_id = h.hospital_id;

Find unmatched rows

SELECT p.*
FROM patients AS p
LEFT JOIN hospitals AS h
    ON p.hospital_id = h.hospital_id
WHERE h.hospital_id IS NULL;

Subqueries and CTEs

A subquery is a query nested inside another query. A Common Table Expression (CTE) is a named result set defined with WITH and available only to the statement that follows. Unlike a temporary table, a CTE is not normally stored as a separate table.

Concept first: CTE vs temporary table

CTE: improves readability inside one statement and can support recursion.

Temporary table: is materialised as a temporary database object and can usually be referenced by several statements during the session.

Subquery

SELECT *
FROM patients
WHERE age > (
    SELECT AVG(age)
    FROM patients
);

Common table expression

WITH hospital_summary AS (
    SELECT
        hospital_id,
        COUNT(*) AS patients,
        AVG(age) AS mean_age
    FROM patients
    GROUP BY hospital_id
)
SELECT *
FROM hospital_summary
WHERE patients >= 10;

Window functions

Window functions calculate across related rows while preserving row-level detail. Unlike GROUP BY, they do not normally collapse a group into one output row. They are central to ranking, running totals, rolling calculations, comparisons with previous or next rows and percent-of-total analysis.

Anatomy of a window expression

Function: for example SUM, AVG, ROW_NUMBER, RANK, LAG or LEAD.

OVER: turns a supported function into a window calculation.

PARTITION BY: divides the result into independent groups without collapsing them.

ORDER BY: defines row order inside each partition.

Frame: optionally limits which rows around the current row are included, such as all previous rows or the previous two rows plus the current row.

General pattern

function_name(expression) OVER (
    PARTITION BY grouping_column
    ORDER BY sort_column
    ROWS BETWEEN frame_start AND frame_end
)

Family	Common functions	Typical analytical use
Aggregate windows	`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`	Running totals, group averages, percent of total
Ranking	`ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`	Top-N, ordering, quartiles/deciles
Offset / value	`LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`	Previous/next values, first/last within a window
Distribution	`PERCENT_RANK`, `CUME_DIST`	Relative position within an ordered group

ROW_NUMBER vs RANK vs DENSE_RANK

ROW_NUMBER: always gives a unique sequence: 1, 2, 3, 4.

RANK: ties share a rank and leave gaps: 1, 2, 2, 4.

DENSE_RANK: ties share a rank with no gaps: 1, 2, 2, 3.

Aggregate window: group average beside every row

SELECT
    patient_name,
    hospital_id,
    age,
    AVG(age) OVER (
        PARTITION BY hospital_id
    ) AS hospital_mean_age
FROM patients;

ROW_NUMBER: most recent row per customer

WITH ranked AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;

RANK and DENSE_RANK

SELECT
    region,
    product_id,
    total_sales,
    RANK() OVER (
        PARTITION BY region
        ORDER BY total_sales DESC
    ) AS sales_rank_with_gaps,
    DENSE_RANK() OVER (
        PARTITION BY region
        ORDER BY total_sales DESC
    ) AS sales_rank_no_gaps
FROM product_sales;

NTILE: split rows into quartiles

SELECT
    customer_id,
    annual_spend,
    NTILE(4) OVER (
        ORDER BY annual_spend DESC
    ) AS spend_quartile
FROM customer_summary;

Running total with an explicit ROWS frame

SELECT
    activity_date,
    daily_responses,
    SUM(daily_responses) OVER (
        ORDER BY activity_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_responses
FROM response_activity;

Rolling three-row average

SELECT
    activity_date,
    daily_responses,
    AVG(daily_responses) OVER (
        ORDER BY activity_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3_row_average
FROM response_activity;

LAG and LEAD: compare neighbouring rows

SELECT
    month,
    responses,
    LAG(responses) OVER (ORDER BY month) AS previous_month,
    LEAD(responses) OVER (ORDER BY month) AS next_month,
    responses - LAG(responses) OVER (ORDER BY month) AS change_from_previous
FROM monthly_responses;

Percent of total

SELECT
    region,
    sales_amount,
    100.0 * sales_amount
        / NULLIF(SUM(sales_amount) OVER (), 0) AS percent_of_total
FROM sales;

Top three products per region

WITH ranked_products AS (
    SELECT
        region,
        product_id,
        total_sales,
        DENSE_RANK() OVER (
            PARTITION BY region
            ORDER BY total_sales DESC
        ) AS sales_rank
    FROM product_sales
)
SELECT *
FROM ranked_products
WHERE sales_rank <= 3;

FIRST_VALUE and LAST_VALUE with a deliberate frame

SELECT
    customer_id,
    order_date,
    order_amount,
    FIRST_VALUE(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS first_order_amount,
    LAST_VALUE(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders;

Window-function habits that prevent mistakes

Use a deterministic tie-breaker: if two rows can share the same date or score, add another stable column to the window ORDER BY.

Be explicit about frames: when an ordered aggregate needs a running or rolling calculation, write the ROWS BETWEEN ... frame rather than relying on a database default.

Aggregate first when necessary: if the business question is “running total of daily sales”, create daily totals first, then window over those totals.

Remember execution order: a window result usually cannot be referenced directly in WHERE in the same SELECT level; place it in a CTE/subquery and filter outside.

Date and time functions

Date functions are highly dialect-specific. This section covers the SQL Server functions commonly used for extraction, formatting, calculations and validation, then shows PostgreSQL equivalents so you can recognise the same analytical ideas across systems.

Four date-and-time jobs to recognise

Part extraction: pull out or label a year, month, day, week or other component.

Format and casting: convert between date/time values, text and other compatible types.

Calculations: add intervals, calculate elapsed time or create reporting periods.

Validation: check or safely attempt conversion before relying on date-like text.

Task	SQL Server	PostgreSQL
Year / month / day	`YEAR`, `MONTH`, `DAY`	`EXTRACT`, `DATE_PART`
Generic date part	`DATEPART`	`EXTRACT`, `DATE_PART`
Named date part	`DATENAME`	`TO_CHAR`
Truncate to month / week / year	`DATETRUNC` (SQL Server 2022+)	`DATE_TRUNC`
End of month	`EOMONTH`	date arithmetic / `DATE_TRUNC`
Add time	`DATEADD`	`+ INTERVAL`
Difference	`DATEDIFF`, `DATEDIFF_BIG`	date/timestamp subtraction, `AGE`
Format for display	`FORMAT`, `CONVERT`	`TO_CHAR`
Cast types	`CAST`, `CONVERT`, `TRY_CAST`, `TRY_CONVERT`	`CAST`, `::`
Validate date-like text	`ISDATE`; often safer: `TRY_CAST`/`TRY_CONVERT`	No direct `ISDATE`; validate ingestion or use controlled parsing

SQL Server: DAY, MONTH and YEAR

SELECT
    appointment_date,
    DAY(appointment_date) AS day_number,
    MONTH(appointment_date) AS month_number,
    YEAR(appointment_date) AS year_number
FROM appointments;

SQL Server: DATEPART and DATENAME

SELECT
    appointment_date,
    DATEPART(quarter, appointment_date) AS quarter_number,
    DATEPART(weekday, appointment_date) AS weekday_number,
    DATENAME(month, appointment_date) AS month_name,
    DATENAME(weekday, appointment_date) AS weekday_name
FROM appointments;

SQL Server: DATETRUNC

-- SQL Server 2022+
SELECT
    appointment_date,
    DATETRUNC(month, appointment_date) AS month_start,
    DATETRUNC(year, appointment_date) AS year_start
FROM appointments;

SQL Server: EOMONTH

SELECT
    appointment_date,
    EOMONTH(appointment_date) AS month_end,
    EOMONTH(appointment_date, 1) AS next_month_end
FROM appointments;

SQL Server: DATEADD

SELECT
    appointment_date,
    DATEADD(day, 14, appointment_date) AS follow_up_due,
    DATEADD(month, -1, appointment_date) AS one_month_before
FROM appointments;

SQL Server: DATEDIFF

SELECT
    patient_id,
    referral_date,
    appointment_date,
    DATEDIFF(day, referral_date, appointment_date) AS waiting_days
FROM appointments;

SQL Server: CAST, CONVERT and FORMAT

SELECT
    appointment_datetime,
    CAST(appointment_datetime AS date) AS appointment_date,
    CONVERT(date, appointment_datetime) AS converted_date,
    CONVERT(varchar(10), appointment_datetime, 23) AS iso_date_text,
    FORMAT(appointment_datetime, 'yyyy-MM') AS reporting_month
FROM appointments;

SQL Server: validate or safely convert date-like text

SELECT
    raw_date,
    ISDATE(raw_date) AS is_date,
    TRY_CONVERT(date, raw_date) AS parsed_date
FROM staging_import;

Current date and timestamp

-- SQL Server
SELECT
    GETDATE() AS local_datetime,
    SYSDATETIME() AS higher_precision_datetime,
    SYSUTCDATETIME() AS utc_datetime,
    CURRENT_TIMESTAMP AS standard_style_current_timestamp;

-- PostgreSQL
SELECT
    CURRENT_DATE,
    CURRENT_TIMESTAMP;

PostgreSQL: EXTRACT and DATE_TRUNC

SELECT
    appointment_date,
    EXTRACT(YEAR FROM appointment_date) AS year_number,
    EXTRACT(MONTH FROM appointment_date) AS month_number,
    DATE_TRUNC('month', appointment_date) AS month_start
FROM appointments;

PostgreSQL: interval arithmetic and date difference

SELECT
    patient_id,
    appointment_date + INTERVAL '14 days' AS follow_up_due,
    appointment_date::date - referral_date::date AS waiting_days,
    AGE(appointment_date, referral_date) AS elapsed_interval
FROM appointments;

Group by month without mixing years

-- PostgreSQL
SELECT
    DATE_TRUNC('month', appointment_date) AS month_start,
    COUNT(*) AS appointments
FROM appointments
GROUP BY DATE_TRUNC('month', appointment_date)
ORDER BY month_start;

-- Avoid grouping only by EXTRACT(MONTH ...)
-- when the data spans multiple years.

Use half-open date ranges for timestamps

SELECT *
FROM appointments
WHERE appointment_datetime >= '2026-10-01'
  AND appointment_datetime <  '2026-11-01';

SQL Server: convert between UTC and a named time zone

SELECT
    event_time_utc,
    event_time_utc AT TIME ZONE 'UTC'
                   AT TIME ZONE 'GMT Standard Time' AS uk_local_time
FROM events;

Date-and-time habits that matter in real analysis

Store dates as date/time types: do not store them as display-formatted text unless you are in a staging layer.

Separate storage from presentation: use date values for filtering and calculations; format as text only near the reporting/output layer.

Use unambiguous literals: ISO-style YYYY-MM-DD is easier to read and reduces locale ambiguity.

Prefer range filters: filtering a timestamp with >= start AND < next_period handles times during the day and often gives the optimiser a better chance to use an index than wrapping the column in a function.

Do not use DATEDIFF(year, birth_date, today) as an exact age: it counts year boundaries and needs a birthday adjustment.

Working with text

Text functions help clean, combine and inspect character values.

Clean and combine text

SELECT
    TRIM(patient_name) AS clean_name,
    UPPER(gender) AS gender_upper,
    patient_name || ' - ' || gender AS patient_label
FROM patients;

Replace text

SELECT
    REPLACE(hospital_name, 'NHS Foundation Trust', 'FT') AS short_name
FROM hospitals;

String length

SELECT
    patient_name,
    LENGTH(patient_name) AS name_length
FROM patients;

Data-quality checks

SQL is useful for checking completeness, duplicates, invalid values and broken relationships before analysis.

Count missing values

SELECT
    COUNT(*) AS total_rows,
    COUNT(age) AS rows_with_age,
    COUNT(*) - COUNT(age) AS missing_age
FROM patients;

Find duplicate identifiers

SELECT
    patient_id,
    COUNT(*) AS occurrences
FROM patients
GROUP BY patient_id
HAVING COUNT(*) > 1;

Find invalid values

SELECT *
FROM patients
WHERE age < 0
   OR age > 120;

Check join coverage

SELECT
    COUNT(*) AS patient_rows,
    COUNT(h.hospital_id) AS matched_hospital_rows
FROM patients AS p
LEFT JOIN hospitals AS h
    ON p.hospital_id = h.hospital_id;

Updating and deleting data

INSERT, UPDATE and DELETE change stored data. Always test the WHERE condition with SELECT before running an update or deletion.

Update rows

UPDATE patients
SET gender = 'Not recorded'
WHERE gender IS NULL;

Delete rows carefully

-- Check first
SELECT *
FROM patients
WHERE patient_id = 999;

-- Delete only after checking
DELETE FROM patients
WHERE patient_id = 999;

Use a transaction

BEGIN;

UPDATE patients
SET hospital_id = 105
WHERE hospital_id = 104;

-- Review the result before saving
SELECT *
FROM patients
WHERE hospital_id IN (104, 105);

COMMIT;

-- Use ROLLBACK instead of COMMIT to undo the transaction.

Views, stored procedures and indexes

Views, stored procedures and indexes solve different database problems. A view is a saved query that behaves like a virtual table. A stored procedure is executable database logic that can accept parameters and run one or more statements. An index is a separate structure that can speed up searches, joins and sorting, with storage and write-performance trade-offs. The procedure examples below use SQL Server T-SQL; syntax and features differ across database systems.

Concept first: view vs stored procedure

View: queried with SELECT and mainly used to present reusable data logic.

Stored procedure: executed as a database program and may contain parameters, multiple statements and data-changing logic.

Not every database implements stored procedures in the same way.

Interview answer: view vs stored procedure vs index

View: a named SELECT query that you can query like a table. A normal view stores the query definition rather than a separate copy of the rows.

Stored procedure: a named executable routine in the database. It can accept parameters and contain multiple SQL statements and control-flow logic.

Index: a data structure that helps the database locate rows efficiently for suitable query patterns. It improves many reads but adds storage and maintenance work when data changes.

Create and query a view

CREATE VIEW dbo.patient_hospital_view
AS
SELECT
    p.patient_id,
    p.patient_name,
    p.age,
    h.hospital_name,
    h.region
FROM dbo.patients AS p
LEFT JOIN dbo.hospitals AS h
    ON p.hospital_id = h.hospital_id;
GO

SELECT *
FROM dbo.patient_hospital_view
WHERE region = 'Wales';

Create a stored procedure with a parameter

CREATE OR ALTER PROCEDURE dbo.usp_GetPatientsByHospital
    @HospitalID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        patient_id,
        patient_name,
        age
    FROM dbo.patients
    WHERE hospital_id = @HospitalID
    ORDER BY patient_name;
END;
GO

Execute the stored procedure

EXEC dbo.usp_GetPatientsByHospital
    @HospitalID = 101;

Create an index

CREATE INDEX idx_patients_hospital_id
ON dbo.patients (hospital_id);

When would you choose each one?

View: when users or reports need a reusable table-like representation of a query.

Stored procedure: when you need parameterised executable logic, several coordinated statements, or controlled database operations.

Index: when frequent query patterns justify faster lookups and you have considered the cost to storage and writes.

Do not say a view and a stored procedure are interchangeable: a view is queried; a stored procedure is executed.

A complete analysis query

This example combines filtering, joining, grouping and calculated fields in one reporting query.

Hospital performance summary

WITH eligible_patients AS (
    SELECT
        patient_id,
        hospital_id,
        age,
        satisfaction_score
    FROM patient_experience
    WHERE survey_year = 2026
      AND satisfaction_score IS NOT NULL
),
hospital_results AS (
    SELECT
        hospital_id,
        COUNT(*) AS responses,
        AVG(satisfaction_score) AS mean_score,
        AVG(age) AS mean_age
    FROM eligible_patients
    GROUP BY hospital_id
)
SELECT
    h.hospital_name,
    h.region,
    r.responses,
    ROUND(r.mean_score, 2) AS mean_score,
    ROUND(r.mean_age, 1) AS mean_age,
    CASE
        WHEN r.mean_score >= 90 THEN 'High'
        WHEN r.mean_score >= 75 THEN 'Moderate'
        ELSE 'Review'
    END AS performance_band
FROM hospital_results AS r
INNER JOIN hospitals AS h
    ON r.hospital_id = h.hospital_id
ORDER BY r.mean_score DESC;

Using SQL with Python and R

SQL often handles extraction and aggregation, while Python or R performs further analysis, visualisation or modelling.

Python with SQLite

import sqlite3
import pandas as pd

connection = sqlite3.connect("hospital.db")

query = '''
SELECT
    hospital_id,
    COUNT(*) AS patients,
    AVG(age) AS mean_age
FROM patients
GROUP BY hospital_id
'''

summary = pd.read_sql_query(query, connection)
connection.close()

print(summary)

R with DBI

library(DBI)
library(RSQLite)

connection <- dbConnect(
  SQLite(),
  "hospital.db"
)

summary <- dbGetQuery(
  connection,
  "
  SELECT
      hospital_id,
      COUNT(*) AS patients,
      AVG(age) AS mean_age
  FROM patients
  GROUP BY hospital_id
  "
)

dbDisconnect(connection)

Good SQL habits

Readable SQL is easier to review, debug and maintain.

Formatting checklist

• Put major clauses such as SELECT, FROM and WHERE on separate lines.
• Use clear aliases such as patients AS p and hospitals AS h.
• Select only the columns you need.
• Test complex work in small steps.
• Check row counts before and after joins.
• Avoid UPDATE or DELETE without a reviewed WHERE clause.
• Comment unusual business rules.
• Keep production credentials out of scripts.

Avoid SELECT * in final reporting queries

SELECT
    patient_id,
    age,
    hospital_id
FROM patients;

SQL key terms and interview refresher

Use this section as a quick revision page before an interview or when a term comes up at work. The aim is to understand the idea well enough to explain it in plain language before memorising syntax.

NULLAn unknown, missing or not-applicable value. Test it with IS NULL / IS NOT NULL rather than = NULL.

COALESCEReturns the first non-NULL expression in its argument list. Useful for fallbacks and for controlling how NULL affects arithmetic or display fields.

NULLIFReturns NULL when two expressions are equal; otherwise returns the first expression. A common use is preventing division by zero.

Window frameThe subset of rows around the current row used by an ordered window calculation, for example all rows from the start of the partition through the current row.

LAG / LEADReturn a value from a previous or following row within a defined window order, useful for period-on-period comparisons.

DATETRUNC / DATE_TRUNCReduce a date or timestamp to the start of a reporting period such as month or year. SQL Server uses DATETRUNC; PostgreSQL uses DATE_TRUNC.

ViewA named SELECT query that can be queried like a virtual table. A normal view stores the query definition rather than a separate copy of the rows; some systems also support materialised or indexed views.

Stored procedureA named executable routine stored in the database. It can accept parameters and contain multiple SQL statements and control-flow logic. Exact syntax and capabilities depend on the database system.

CTEA Common Table Expression is a named result set defined with WITH and used within the scope of the statement that follows. In SQL Server it is not materialised as a separate table by default.

Temporal tableA table designed to preserve row history over time. In SQL Server, a system-versioned temporal table keeps current data plus historical row versions so you can query an earlier point in time.

NormalisationA relational database design process that reduces unnecessary duplication and update anomalies by putting facts into appropriate related tables.

DenormalisationAn intentional design choice that combines or repeats data to reduce joins or improve some read-heavy workloads, accepting additional duplication and maintenance risk.

ROW_NUMBERAssigns a unique sequential number to each row within a partition according to the ORDER BY in the OVER clause.

RANKGives tied rows the same rank and leaves gaps afterwards: 1, 2, 2, 4.

DENSE_RANKGives tied rows the same rank but does not leave gaps: 1, 2, 2, 3.

Primary keyA column or combination of columns chosen to uniquely identify each row in a table.

Foreign keyA column or combination of columns that references a key in another or the same table and can enforce referential integrity.

IndexA database structure that can speed up retrieval for suitable query patterns. It uses storage and adds maintenance work when rows are inserted, updated or deleted.

Normalisation in interview language: 1NF, 2NF and 3NF

1NF: each field holds a single value and repeating groups are removed.

2NF: the table is in 1NF and every non-key attribute depends on the whole key, which matters especially when the key is composite.

3NF: the table is in 2NF and non-key attributes do not depend on other non-key attributes; they depend on the key.

A useful summary is: normalisation aims to store each fact in the right place so changes do not have to be repeated across many rows.

Common interview questions

What is NULL, and why does = NULL not work?

NULL means the value is unknown or missing. SQL uses three-valued logic, so ordinary equality does not treat NULL like a normal value. Use IS NULL or IS NOT NULL.

COALESCE vs ISNULL: what is the difference?

COALESCE is standard SQL and can evaluate several arguments, returning the first non-NULL one. SQL Server ISNULL accepts two arguments and is SQL Server-specific. Their type-resolution behaviour can also differ, so they should not be treated as exact synonyms.

How are window functions different from GROUP BY?

GROUP BY normally reduces a group to one row. A window function calculates across related rows while preserving each row, which makes it useful for ranking, running totals and comparisons.

What do LAG and LEAD do?

LAG returns a value from a previous row and LEAD returns a value from a following row according to the ORDER BY inside the window. They are useful for month-on-month or row-to-row comparisons.

What is the difference between extracting a month and truncating to a month?

Extracting the month usually returns a month number such as 1 to 12. Truncating to month returns the date or timestamp representing the start of that month, preserving the year and therefore working better as a reporting-period key across several years.

What is the difference between a view and a stored procedure?

A view behaves like a reusable virtual table based on a SELECT query. A stored procedure is executable database logic that may accept parameters and perform multiple operations. A view is queried; a procedure is executed.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

ROW_NUMBER always gives unique sequential numbers. RANK gives tied rows the same rank and leaves gaps afterwards. DENSE_RANK gives tied rows the same rank but does not leave gaps.

What is a CTE and when would you use one?

A CTE is a named result set available to one statement. It is useful for making complex queries easier to read, breaking logic into steps, and writing recursive queries where supported.

What is a temporal table?

A temporal table keeps historical row versions so you can query how data looked at an earlier point in time. SQL Server has built-in system-versioned temporal tables; other database systems use different implementations.

What is normalisation?

Normalisation separates data into related tables to minimise duplication and update anomalies. Common forms include 1NF, 2NF and 3NF.

Why might you denormalise?

For reporting or high-read workloads, denormalisation can reduce joins and improve query performance, but it increases duplication and the risk of inconsistent data.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after GROUP BY.

What is the difference between UNION and UNION ALL?

UNION combines results and removes duplicates. UNION ALL keeps all rows and is usually faster because it does not deduplicate.

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes selected rows and can usually use WHERE. TRUNCATE removes all rows more directly. DROP removes the table object itself. Exact transactional behaviour varies by database.

Practical interview tests

These short tasks test whether you can apply the tool, explain your reasoning and validate the result. In a live exercise, say your assumptions aloud and check the output rather than rushing straight to syntax.

Return the most recent record for each customer.What it tests: window functions and partitioning

Answer: Use ROW_NUMBER() partitioned by customer and ordered by date descending, then keep row number 1.

Why: Add a deterministic tie-breaker when dates can be identical.

WITH ranked AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders
)
SELECT
    customer_id,
    order_id,
    order_date
FROM ranked
WHERE rn = 1;

Rank products by sales within each region, with no gaps after ties.What it tests: DENSE_RANK and window partitions

Answer: Aggregate product sales, then use DENSE_RANK() partitioned by region and ordered by total sales descending.

Why: DENSE_RANK gives tied values the same rank without skipping the next rank.

WITH product_sales AS (
    SELECT
        region,
        product_id,
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region, product_id
)
SELECT
    region,
    product_id,
    total_sales,
    DENSE_RANK() OVER (
        PARTITION BY region
        ORDER BY total_sales DESC
    ) AS sales_rank
FROM product_sales;

Find customers who have never placed an order.What it tests: anti-join logic

Answer: Use NOT EXISTS, or a LEFT JOIN followed by a NULL check.

Why: NOT EXISTS expresses the business question clearly and avoids accidental row multiplication.

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

Calculate a running total of daily sales.What it tests: window aggregates

Answer: Aggregate by date first, then apply SUM() as a window function ordered by date.

WITH daily_sales AS (
    SELECT
        order_date,
        SUM(sales_amount) AS daily_total
    FROM sales
    GROUP BY order_date
)
SELECT
    order_date,
    daily_total,
    SUM(daily_total) OVER (
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) AS running_total
FROM daily_sales
ORDER BY order_date;

How would you find duplicate business keys?What it tests: data-quality SQL

Answer: Group by the key and keep groups with COUNT(*) greater than 1.

SELECT
    patient_id,
    COUNT(*) AS occurrences
FROM patients
GROUP BY patient_id
HAVING COUNT(*) > 1;

A query became slow after a table grew substantially. What would you investigate?What it tests: performance reasoning

Answer: Start with the execution plan, filters, join predicates, indexes, row counts, unnecessary columns, data types and whether expressions prevent useful index access.

Why: A strong answer does not jump straight to 'add an index'; indexes help some read patterns but also have storage and write costs.

Four-week SQL learning plan

Practise with a small database and write queries every day. SQL becomes familiar through repetition.

Week 1

Tables, rows, columns, SELECT, aliases, WHERE, DISTINCT, ORDER BY and LIMIT.

Practice:
• Create a small patient or sales database.
• Write at least 20 filtering queries.

Week 2

NULL handling, aggregate functions, GROUP BY, HAVING, CASE, text functions and date/time analysis.

Practice:
• Produce a weekly or monthly summary report.

Week 3

INNER JOIN, LEFT JOIN, subqueries, CTEs and data-quality checks.

Practice:
• Combine three related tables and validate the joins.

Week 4

Window functions including ranking, frames, LAG/LEAD and running totals; then views, indexes, transactions and connecting SQL to Python or R.

Practice:
• Build one complete reporting project from raw tables to final output.