Data, Databases and SQL: Why AI Engineers Need Them

Almost every AI system you will build sits on top of a database. The training data for a churn model comes out of an orders table. The chatbot you deploy logs every prompt, answer, token count and cost into a table so someone can check whether it is getting better or worse. A RAG application keeps its document chunks, their sources and who may see them in tables, and often the embeddings too. The language all of these speak is SQL.

This tutorial teaches SQL from zero, on a small online shop called BitByte Shop. This first page explains what a database is, why SQL is worth learning for AI work, how relational databases compare with the other kinds you will hear about, and which flavour of SQL we use.

What you will learn

• What data, a database, a DBMS and an RDBMS are, and what SQL is
• Why SQL is a daily tool in AI, ML and data science
• How relational databases differ from document, key-value, graph and vector databases
• The main SQL dialects (SQLite, MySQL, PostgreSQL, SQL Server) and which ones this course uses

Data, databases and the DBMS

Five words come up constantly. It is worth getting them straight once:

Why not just keep everything in CSV or Excel files, as you did in Python for AI? Files are fine for a one-off analysis. A DBMS adds what files cannot give you: many users and programs reading and writing at the same moment without corrupting anything, rules that reject bad data (no order for a customer who does not exist, no negative price), indexes that find one row among millions quickly, and transactions so that a payment is either fully recorded or not at all.

SQL in one look

SQL is declarative: you describe the result you want, and the database works out how to get it. Here is a question the shop owner might ask — "which products have sold the most units?" Do not worry about the syntax yet; by the time you reach Joins: INNER JOIN and Table Aliases you will write queries like this yourself:

SELECT p.name, SUM(oi.quantity) AS units_sold
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY units_sold DESC, p.name
LIMIT 5;

+-------------------------+------------+
| name                    | units_sold |
+-------------------------+------------+
| Wireless Mouse          |          5 |
| Laptop Stand            |          3 |
| Python Crash Course     |          3 |
| 27-inch Monitor         |          2 |
| AI Engineering Bootcamp |          2 |
+-------------------------+------------+

Read it almost as English: take the order items, attach each product's name, group the rows by product, add up the quantities, sort from most to least, keep five. You never said how to loop, sort or count. Compare the same answer in plain Python:

import sqlite3

con = sqlite3.connect("shop.db")
names = dict(con.execute("SELECT product_id, name FROM products"))

units = {}
for product_id, quantity in con.execute("SELECT product_id, quantity FROM order_items"):
    name = names[product_id]
    units[name] = units.get(name, 0) + quantity

top = sorted(units.items(), key=lambda item: (-item[1], item[0]))[:5]
for name, total in top:
    print(f"{name:26} {total}")

Wireless Mouse             5
Laptop Stand               3
Python Crash Course        3
27-inch Monitor            2
AI Engineering Bootcamp    2

Same answer, but you had to pull every row into Python, write the loop and the sort yourself, and on a table of fifty million rows you would also run out of memory. The SQL version runs inside the database, next to the data, and only five rows travel back. That is the habit to build: let the database do the filtering, joining and counting; bring only the result into Python.

Why AI engineers need SQL

Here is that last-but-one row for real: a tiny table of product help texts with 3-number "embeddings" (real ones have hundreds or thousands of numbers), searched for the text closest to a question — first on similarity alone, then only among products that are in stock. The first line, -- PostgreSQL, means this block ran on a PostgreSQL server, not on SQLite:

-- PostgreSQL
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE help_chunks (
    chunk_id   INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    body       TEXT NOT NULL,
    embedding  vector(3) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

INSERT INTO help_chunks VALUES
    (1, 3, 'Keyboard: how to change the keycaps',      '[0.9, 0.1, 0.0]'),
    (2, 4, 'Mouse: pairing over Bluetooth',            '[0.2, 0.9, 0.1]'),
    (3, 9, 'USB-C hub: connecting two monitors',       '[0.1, 0.3, 0.9]'),
    (4, 5, 'Monitor: choosing the right refresh rate', '[0.3, 0.1, 0.8]');

-- "How do I use two screens?" embedded as [0.1, 0.3, 0.9]
-- 1) similarity alone
SELECT c.body,
       p.stock,
       ROUND((c.embedding <=> '[0.1, 0.3, 0.9]')::numeric, 3) AS distance
FROM help_chunks AS c
JOIN products AS p ON p.product_id = c.product_id
ORDER BY distance
LIMIT 2;

-- 2) similarity plus a business rule: only products in stock
SELECT c.body,
       p.stock,
       ROUND((c.embedding <=> '[0.1, 0.3, 0.9]')::numeric, 3) AS distance
FROM help_chunks AS c
JOIN products AS p ON p.product_id = c.product_id
WHERE p.stock > 0
ORDER BY distance
LIMIT 2;

+------------------------------------------+-------+----------+
| body                                     | stock | distance |
+------------------------------------------+-------+----------+
| USB-C hub: connecting two monitors       |     0 |    0.000 |
| Monitor: choosing the right refresh rate |     5 |    0.049 |
+------------------------------------------+-------+----------+
+------------------------------------------+-------+----------+
| body                                     | stock | distance |
+------------------------------------------+-------+----------+
| Monitor: choosing the right refresh rate |     5 |    0.049 |
| Mouse: pairing over Bluetooth            |    60 |    0.570 |
+------------------------------------------+-------+----------+

<=> is pgvector's cosine distance (0 means "pointing the same way"; cosine similarity is in Maths for AI). On similarity alone the USB-C hub chunk wins, but product 9 has stock = 0, so in the second query the WHERE filter removed it and the monitor chunk came first. A business rule and a similarity search, in one query. You will build exactly this in the advanced tutorial.

Relational and non-relational databases

Relational databases are not the only kind. "NoSQL" is the umbrella name for the others; each is built for one shape of data:

The honest summary: most real AI products keep their core data in a relational database and add a specialised store only when they need it. Many teams start with PostgreSQL plus pgvector and never need a separate vector database. And the line keeps blurring — PostgreSQL and MySQL store JSON documents, and SQLite does too.

SQL dialects, and the ones this course uses

SQL is an ISO standard, but every database speaks its own dialect: the core (SELECT, WHERE, JOIN, GROUP BY) is the same everywhere, while details such as text functions, date handling and limiting rows differ.

This course practises on SQLite, because it needs nothing beyond Python and the whole database is a file you can delete and rebuild in a second. Wherever MySQL or PostgreSQL behave differently, the page shows it on a real server, in a block whose first line is -- MySQL or -- PostgreSQL. A first taste of why that matters — the same question on three databases:

SELECT 7 / 2 AS result;

+--------+
| result |
+--------+
|      3 |
+--------+

-- MySQL
SELECT 7 / 2 AS result;

+--------+
| result |
+--------+
| 3.5000 |
+--------+

-- PostgreSQL
SELECT 7 / 2 AS result;

+--------+
| result |
+--------+
|      3 |
+--------+

SQLite and PostgreSQL divide two whole numbers and throw away the remainder; MySQL gives a decimal. Imagine computing a "share of orders cancelled" feature this way and getting 0 for every customer on one database and the right number on another. Such differences are few, but they are exactly the ones that cause silent bugs, so this course points them out.

Common mistakes

• Loading a whole table into Python to count or filter it. It is slow, wastes memory and breaks on big tables. Ask the database instead and bring back only the answer:
  SQLCopy

  SELECT COUNT(*) AS delivered FROM orders WHERE status = 'delivered';

  OutputCopy

  +-----------+
  | delivered |
  +-----------+
  |        11 |
  +-----------+

• Assuming every database gives the same answer. As you saw, 7 / 2 is 3 in SQLite and PostgreSQL. When you want a fraction, make one of the numbers a decimal:
  SQLCopy

  SELECT 7 / 2.0 AS result;

  OutputCopy

  +--------+
  | result |
  +--------+
  |    3.5 |
  +--------+

• Expecting rows to come back in a particular order. A table has no built-in order; without ORDER BY the database may return rows in any order, and that order can change. Every query in this course that returns several rows says how to sort them:
  SQLCopy

  SELECT name FROM customers ORDER BY name LIMIT 3;

  OutputCopy

  +---------------+
  | name          |
  +---------------+
  | Farhana Akter |
  | Imran Hossain |
  | Karim Uddin   |
  +---------------+

• Picking a database because it is fashionable. A vector database alone cannot enforce "every chunk belongs to a real document" or join chunks to users and permissions. Start from the shape of your data and your questions; for most projects that means a relational database first, as in the pgvector example above.

Try it yourself

1. Easy: Which kind of database fits each need best? (a) the shop's orders and payments, (b) caching the answer to a prompt for ten minutes, (c) finding help articles similar in meaning to a question, (d) storing raw JSON responses from an API whose fields change often.
2. Medium: Change the first query on this page so it shows only the top 3 products. Then predict, before running it, what SELECT 9 / 4, 9 / 4.0; returns in SQLite.
3. Hard: In Python, without GROUP BY, count how many orders have each status (loop over SELECT status FROM orders). Then get the same counts with one SQL query and compare.

Answers

1. (a) relational, (b) key-value (for example Redis), (c) vector search (a vector database, or pgvector in PostgreSQL), (d) document — or a JSON column in a relational table.

2. Replace LIMIT 5 with LIMIT 3. 9 / 4 is 2 (whole-number division) and 9 / 4.0 is 2.25:

SELECT p.name, SUM(oi.quantity) AS units_sold
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY units_sold DESC, p.name
LIMIT 3;

SELECT 9 / 4, 9 / 4.0;

3. The Python loop and the SQL query give the same counts:

import sqlite3

con = sqlite3.connect("shop.db")
counts = {}
for (status,) in con.execute("SELECT status FROM orders"):
    counts[status] = counts.get(status, 0) + 1
for status in sorted(counts):
    print(status, counts[status])

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;

Summary

• A database is an organised, persistent collection of data; a DBMS is the software that manages it; an RDBMS keeps it in linked tables.
• SQL is declarative: you describe the result and the database finds the way. Let it filter and count, and bring only results into Python.
• In AI work SQL builds training data and features, analyses LLM logs and evals, filters RAG metadata and — with pgvector — runs vector search.
• Document, key-value, graph and vector databases each fit one shape of data; most systems keep a relational database at the core.
• This course practises on SQLite and shows MySQL and PostgreSQL wherever they differ (7 / 2 already does).

Next: Tables, Keys and Relationships opens the BitByte Shop database and shows how its tables are built and linked by primary and foreign keys — the idea that makes queries like the first one on this page possible.