SQL

Why learn SQL?

SQL is one of the most valuable skills in tech: easy to start, used by developers, analysts and data scientists alike, and essential for feeding AI systems with real data.

• Everywhere: almost every company stores its data in SQL databases.
• Declarative: you describe what you want; the database figures out how.
• AI-ready: pgvector turns PostgreSQL into a vector database for RAG.

Set up and run your first program

The quickest start is SQLite, which needs no server. For production and vector search, install PostgreSQL.

sqlite3 shop.db
# or: psql -U postgres

SELECT 'Hello, AI! Welcome to SQL.' AS greeting;

Creating tables and inserting data

A table has named, typed columns. A primary key uniquely identifies each row.

CREATE TABLE gpus (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  vendor  TEXT,
  vram_gb INTEGER,
  year    INTEGER
);
INSERT INTO gpus (name, vendor, vram_gb, year) VALUES
  ('H100', 'NVIDIA', 80, 2022),
  ('H200', 'NVIDIA', 141, 2024),
  ('MI355X', 'AMD', 288, 2025),
  ('RTX 5090', 'NVIDIA', 32, 2025);

Querying with SELECT

SELECT picks columns, WHERE filters rows, ORDER BY sorts and LIMIT caps the number of results.

SELECT name, vram_gb
FROM gpus
WHERE vendor = 'NVIDIA' AND vram_gb >= 80
ORDER BY vram_gb DESC
LIMIT 5;

Grouping and aggregates

Aggregate functions summarise rows. GROUP BY makes one summary row per group; HAVING filters groups.

SELECT vendor,
       COUNT(*)     AS models,
       AVG(vram_gb) AS avg_vram,
       MAX(vram_gb) AS max_vram
FROM gpus
GROUP BY vendor
HAVING COUNT(*) >= 1
ORDER BY max_vram DESC;

Joins

Real data is split across tables. A JOIN combines rows that share a key. LEFT JOIN keeps rows even when there is no match.

CREATE TABLE benchmarks (gpu_id INTEGER REFERENCES gpus(id), model TEXT, tokens_per_s REAL);
INSERT INTO benchmarks VALUES (1, 'llama-70b', 2400), (2, 'llama-70b', 3700);

SELECT g.name, b.model, b.tokens_per_s
FROM gpus g
LEFT JOIN benchmarks b ON b.gpu_id = g.id
ORDER BY b.tokens_per_s DESC;

Updates, CTEs and window functions

UPDATE and DELETE change data (always use a WHERE). A CTE (WITH) names a subquery; window functions compute rankings and running totals.

UPDATE gpus SET vram_gb = 192 WHERE name = 'B200';

WITH ranked AS (
  SELECT name, vendor, vram_gb,
         RANK() OVER (PARTITION BY vendor ORDER BY vram_gb DESC) AS r
  FROM gpus
)
SELECT * FROM ranked WHERE r = 1;

AI with SQL

Modern AI apps need memory and facts, and SQL databases now provide both. With the pgvector extension, PostgreSQL stores embeddings and finds the most similar text with a single query, which is the retrieval step in RAG (retrieval-augmented generation).

The essential libraries

Vector search with pgvector

Store documents with their embeddings, then find the closest matches to a question's embedding. <=> is cosine distance.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (
  id        BIGSERIAL PRIMARY KEY,
  content   TEXT,
  embedding vector(768)          -- size depends on your embedding model
);
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

-- $1 is the question's embedding, computed by your app
SELECT content, 1 - (embedding <=> $1) AS similarity
FROM docs
ORDER BY embedding <=> $1
LIMIT 5;

SQL agents: let an LLM query your data safely

A text-to-SQL agent gives the model your schema, lets it write a query, runs it and passes the rows back for a final answer. The SQL side is about safety: give the agent a read-only role, limit what it can see and cap query time.

1. Send the model the table definitions and the user's question.
2. The model calls a run_sql tool with a SELECT statement.
3. Your app runs it as a read-only user with a timeout and returns the rows.
4. The model explains the result in plain language.

-- A locked-down role for the agent (PostgreSQL)
CREATE ROLE ai_agent LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE shop TO ai_agent;
GRANT USAGE ON SCHEMA public TO ai_agent;
GRANT SELECT ON gpus, benchmarks TO ai_agent;   -- read-only, chosen tables
ALTER ROLE ai_agent SET statement_timeout = '5s';
ALTER ROLE ai_agent SET default_transaction_read_only = on;

-- A view that hides sensitive columns from the agent
CREATE VIEW public_gpus AS SELECT name, vendor, vram_gb, year FROM gpus;

The loop itself runs in Python, TypeScript or another language; see those volumes for the tool-calling code.

SQL learning roadmap

Tick items off as you go. Your progress is saved in this browser.

Projects to build

SQL cheat sheet

Common mistakes

• UPDATE or DELETE without WHERE changes every row.
• Building queries by string concatenation invites SQL injection; use parameters.
• NULL is not equal to anything, even NULL: use IS NULL.

Where to go next

• SQLBolt — interactive lessons
• PostgreSQL tutorial — official
• pgvector — vector search

On TensorCore.si: Python volume, What is an AI data center?.