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, summaries, joins, common table expressions, window functions, 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.

SELECT
    hospital_id,
    COUNT(*) AS patients,
    AVG(age) AS mean_age
FROM patients
GROUP BY hospital_id
ORDER BY patients DESC;

19sections

60+examples

Searchbuilt in

Beginner to intermediateAbout 9 minutesLast updated August 2026

A note from the author

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

I put this reference together to give beginners a clear route from their first SELECT statement to practical analytical queries.

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;

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

Subqueries place one query inside another. Common table expressions, written with WITH, often make complex queries easier to read.

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 values across related rows without collapsing the result into one row per group.

Row numbers

SELECT
    patient_name,
    hospital_id,
    age,
    ROW_NUMBER() OVER (
        PARTITION BY hospital_id
        ORDER BY age DESC
    ) AS age_rank
FROM patients;

Group average beside each row

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

Running total

SELECT
    activity_date,
    daily_responses,
    SUM(daily_responses) OVER (
        ORDER BY activity_date
    ) AS cumulative_responses
FROM response_activity;

Working with dates

Date syntax differs slightly across database systems. These examples use PostgreSQL-style functions.

Extract parts of a date

SELECT
    appointment_date,
    EXTRACT(YEAR FROM appointment_date) AS year,
    EXTRACT(MONTH FROM appointment_date) AS month
FROM appointments;

Calculate a duration

SELECT
    patient_id,
    appointment_date - referral_date AS waiting_days
FROM appointments;

Group by month

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

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 and indexes

Views save reusable queries. Indexes can speed up searches and joins, although they also add storage and maintenance costs.

Create a view

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

Query the view

SELECT *
FROM patient_hospital_view
WHERE region = 'Wales';

Create an index

CREATE INDEX idx_patients_hospital_id
ON patients (hospital_id);

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;

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

Aggregate functions, GROUP BY, HAVING, CASE, text functions and dates.

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, views, indexes, transactions and connecting SQL to Python or R.

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