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.

Beginner to intermediateAbout 9 minutesLast updated September 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;

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 values across related rows without collapsing the result into one row per group. For ranking questions, remember the difference: ROW_NUMBER always gives a unique sequence, RANK leaves gaps after ties, and DENSE_RANK does not leave gaps.

Concept first: ROW_NUMBER vs RANK vs DENSE_RANK

ROW_NUMBER: 1, 2, 3, 4 even when values are tied.

RANK: tied values can produce 1, 2, 2, 4.

DENSE_RANK: tied values can produce 1, 2, 2, 3.

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, 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.

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

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.