AI & ML Quick Path

Part 2: Data

Collecting and labelling data, with SQL

Learning goal. Pull training data from a database with SQL, plan where data comes from and whether you may use it, and measure how consistently people label it.

In plain words

Most company data lives in databases, and SQL is the language for asking them questions. SELECT picks columns, WHERE filters rows, JOIN connects tables (customers with their orders), and GROUP BY summarises (orders per customer). Data also comes from logs, files, sensors, forms and public datasets. Before using any data, check that you are allowed to (owner, licence, customer consent, privacy rules) and that it represents the people and situations the model will meet. Labelling means adding the right answer to examples, usually by people following a written guide. People disagree, so you measure agreement: if two careful labellers often disagree, the task or the guide is unclear, and no model will learn it well.

Diagram What the main SQL words do

Pick and filter

  • SELECT Picks columns
  • WHERE Filters rows

Connect and summarise

  • JOIN Connects tables, such as customers with their orders
  • GROUP BY Summarises, such as orders per customer
Diagram Where data comes from
InputYour questionCheck you are allowed to use the data and that it represents the people it will meet
  • Data storeDatabasesQueried with SQL
  • Data storeLogsEvent records
  • Data storeFilesExports and documents
  • Data storeSensorsMeasurements
  • Data storeForms and public datasetsOther sources

Real-world example

A support team wants to auto-sort tickets into "bug", "billing" and "other". Two agents label the same 200 tickets. They agree 82.5% of the time, but mostly disagree on billing questions about bugs. They add three examples of those to the guide, re-label, and agreement rises. Better labels improved the final model more than any algorithm change.

Diagram The labelling improvement in the support team
  1. InputTwo agents label 200 ticketsThe same tickets
  2. ProcessCompareThey agree 82.5% of the time
  3. RiskFind the disagreementsMostly billing questions about bugs
  4. OutputAdd three examples to the guideThen re-label

Re-label and measure agreement again

How it works

  1. Write the question and list the data you need.
  2. Find the source; record owner, permission and date range.
  3. Pull data with SQL into a table with one row per example.
  4. Write a labelling guide with definitions and tricky examples.
  5. Have two people label a sample; compute agreement; fix the guide.
  6. Label the rest; spot-check quality.
Diagram From question to labelled data
  1. InputQuestion and data listWhat you need
  2. RiskSource and permissionOwner, permission, date range
  3. Data storeSQL pullOne row per example
  4. ProcessLabelling guideDefinitions and tricky examples
  5. ProcessTwo people label a sampleCompute agreement, fix the guide
  6. OutputLabel the restSpot-check quality
  • Active learning: let the current model pick the examples it is least sure about and send only those to human labellers. You reach good accuracy with far fewer labels.
  • Weak supervision: create approximate labels with rules, keyword lists or existing systems, then train on them; useful when hand-labelling is too slow. Check quality on a small hand-labelled set.
  • Synthetic data: generated data (by scripts, simulators or LLMs) used for practice, testing or to cover rare cases. It is how every example in this guide avoids real personal data. It can miss real-world quirks, so always validate on real data before production.
  • Data augmentation: creating extra training examples by changing existing ones (flip or crop an image, reword a sentence).
Diagram Four ways to get more or cheaper labelled data

Fewer labels needed

  • Active learning The model picks the examples it is least sure about for humans to label
  • Data augmentation Change existing examples: flip or crop an image, reword a sentence

Labels or data made by machines

  • Weak supervision Approximate labels from rules, keyword lists or existing systems
  • Synthetic data Generated by scripts, simulators or LLMs; validate on real data

Python example

SQL from Python (SQLite is built in). The LEFT JOIN keeps customers with no orders:

Python — gap_topics.py sql (excerpt)
q2 = """SELECT c.id, COUNT(o.id) AS n_orders, COALESCE(AVG(o.amount),0) AS avg_amount, MAX(o.ordered_on) AS last_order
        FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id"""
print(pd.read_sql(q2, con))
rows = con.execute("SELECT id FROM customers WHERE name = ?", (name,)).fetchall()   # safe parameter
Output — Output (executed)
 id  n_orders  avg_amount last_order
  1         2        50.0 2026-10-02
  2         2        20.0 2026-10-21
  3         2       100.0 2026-10-20
  4         0         0.0        NaN
parameterised lookup for "Ben' OR '1'='1" returns [] (injection attempt treated as plain text)

Agreement between two simulated labellers (one makes 10% mistakes, the other 25%):

Output — Output of gap_topics.py viz (executed)
raw agreement 0.825, Cohen's kappa 0.730

Cohen's kappa corrects for agreement that would happen by luck; 0.73 is good but shows room to improve the guide.

.NET (C#) example

Query SQL Server (or any database) with Microsoft.Data.SqlClient and parameters, and build the feature rows:

C# — Program.cs
using Microsoft.Data.SqlClient;

const string sql = """
    SELECT c.Id, COUNT(o.Id) AS NOrders, COALESCE(AVG(o.Amount), 0) AS AvgAmount
    FROM Customers c LEFT JOIN Orders o ON o.CustomerId = c.Id
    WHERE c.Country = @country
    GROUP BY c.Id
    """;
using var connection = new SqlConnection(Environment.GetEnvironmentVariable("SALES_DB"));
await connection.OpenAsync();
using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@country", "UK");          // never paste user input into SQL text
using var reader = await command.ExecuteReaderAsync();
var rows = new List<CustomerFeatures>();
while (await reader.ReadAsync())
    rows.Add(new CustomerFeatures(reader.GetInt32(0), reader.GetInt32(1), Convert.ToSingle(reader.GetValue(2))));
Console.WriteLine($"{rows.Count} customers loaded");

public record CustomerFeatures(int Id, int NOrders, float AvgAmount);

Applied Maths - Statistics

Short, optional reading. Open a topic to see the formula, what it means and worked examples. The numbers are small, made-up values chosen so you can check them by hand.

Average and count per group (what GROUP BY computes)

Where it is used: the SQL query that gives each customer's number of orders and average order amount.

Count and average

count   = number of rows in the group
average = sum of the values ÷ count

In plain words: GROUP BY makes one small pile per customer. COUNT counts the pile and AVG adds it up and divides by the count.

Example 1: customer with orders. Customer 3 has two orders, 80 and 120. Count = 2. Sum = 80 + 120 = 200. Average = 200 ÷ 2 = 100.

Example 2: customer with no orders. Customer 4 has no orders. Count = 0, and the average would be 0 ÷ 0, which is undefined, so SQL returns NULL. The LEFT JOIN keeps this customer, and COALESCE(AVG(...), 0) turns the NULL into 0. Without the LEFT JOIN the customer would be missing from the result.

Cohen's kappa (agreement beyond luck)

Where it is used: checking whether two labellers follow the labelling guide the same way.

Cohen's kappa

kappa = (po - pe) ÷ (1 - pe)
po = share of items where both labellers agree
pe = share of agreement expected by luck
     = sum over labels of (share A gives that label × share B gives that label)

In plain words: raw agreement can look high just by luck. Kappa removes the lucky part. 1 is perfect agreement and 0 is no better than luck.

Example 1: balanced labels. Two labellers agree on 85 of 100 items, so po = 0.85. Both say "spam" half the time and "ok" half the time. pe = 0.5 × 0.5 + 0.5 × 0.5 = 0.50. kappa = (0.85 − 0.50) ÷ (1 − 0.50) = 0.35 ÷ 0.50 = 0.70.

Example 2: rare label. Both say "ok" 90% of the time and "spam" 10%, and they agree on 90 of 100 items, so po = 0.90. pe = 0.9 × 0.9 + 0.1 × 0.1 = 0.81 + 0.01 = 0.82. kappa = (0.90 − 0.82) ÷ (1 − 0.82) = 0.08 ÷ 0.18 = 0.44. The same 90% raw agreement is only moderate once luck is removed.

Maths behind it

  • Sampling bias and representativeness (statistics)
  • Inter-rater agreement: Cohen's kappa (statistics)
  • Relational algebra (the theory behind SQL)

Common mistakes

  • Using an inner JOIN and silently losing customers with no orders.
Diagram Inner JOIN and LEFT JOIN

Inner JOIN

  • Customers with orders Kept
  • Customers with no orders Silently lost

LEFT JOIN

  • Customers with orders Kept
  • Customers with no orders Kept, with a count of 0

Same customers and orders tables, different result.

  • Building SQL by pasting text from users, which allows SQL injection. Use parameters.
Diagram Pasted text or a parameter

Pasting user text into SQL

  • Result The text can run as SQL code: SQL injection

Using a parameter

  • Result The database treats the value as data, never as SQL code
  • Labelling without a written guide, so each person uses different rules.
  • Collecting personal data you are not allowed to use for this purpose.

Quick check

  1. Why use a LEFT JOIN to build a customer feature table?

    Show answer

    It keeps every customer, including those with no orders, instead of dropping them.

  2. What does a low agreement score between labellers tell you?

    Show answer

    The task or the labelling guide is unclear, so labels are noisy and a model will struggle to learn them.

  3. Why use query parameters?

    Show answer

    The database treats parameter values as data, never as SQL code, which prevents SQL injection.

Completion

Evidence of completion. Three SQL queries that build a one-row-per-customer feature table, and a labelling guide tested by two people with an agreement score.

Saved in this browser only. See all progress.