Glossary

96 terms from SQL Basics, in plain words, each with the chapter that teaches it.

Search the glossary

A

Aggregate function: A function that works on many rows at once and boils them down to a single value, such as counting Kettle & Crane's orders or adding up their revenue.
Alias: A temporary name given with AS to a column or table for the length of one query, such as `price * stock AS stock_value` or `orders AS o`.
Ambiguous: Could mean more than one thing. In a join, a column name such as customer_id that exists in both tables must be qualified, or SQLite reports an error.
AND / OR / NOT: Keywords that combine or reverse conditions in WHERE: AND needs both to be true, OR needs at least one, NOT flips true and false. AND is worked out before OR, so use parentheses when mixing them.
Argument: A value you give a function inside its parentheses, such as the 2 in `ROUND(price, 2)`. Several arguments are separated by commas.
Arithmetic operators: The symbols `+` (add), `-` (subtract), `*` (multiply) and `/` (divide). `*` and `/` are worked out before `+` and `-`, and parentheses change the order.
AS: The keyword that gives a column or table an alias for one query. It is optional, which is why a missing comma in `SELECT name price` quietly renames a column.
Ascending, descending (ASC, DESC): Sort directions for ORDER BY. Ascending (the default) puts the smallest, earliest or A-first values on top; descending puts the largest or latest first.
Average order value: A sales measure: total revenue divided by the number of orders. Sam uses it to see whether orders are getting bigger or smaller each month.
AVG: An aggregate function that gives the mean of a column's values, skipping NULLs. Averages are usually wrapped in ROUND.

B

BETWEEN: A condition that tests whether a value lies in a range, including both ends. `price BETWEEN 15 AND 18` means `price >= 15 AND price <= 18`.
Browse Data tab: The DB Browser for SQLite tab that shows a table's rows as a spreadsheet-like grid, with missing values shown as NULL. It is meant for looking at data, while changes are made with SQL.

C

Calculated column: A calculation written in the SELECT list, such as `price * stock`, that SQLite works out separately for each row. It exists only in the result and doesn't change the table.
CASE: An expression, like a spreadsheet IF, that gives a value from the first WHEN condition that is true, or the ELSE value if none are. Without ELSE, unmatched rows get NULL.
Case sensitive: Treating capital and small letters as different. In SQLite, `=` on text is case sensitive, so `'portland'` doesn't match Portland, while LIKE ignores case for A to Z.
changes(): A SQLite function that tells you how many rows the last INSERT, UPDATE or DELETE changed. It should match the count from your safety-check SELECT.
Clause: One part of a query that starts with its own keyword, such as `FROM products` or `WHERE price < 20`. Clauses must appear in a fixed order.
COALESCE: A function that returns the first of its arguments that isn't NULL. `COALESCE(SUM(i.quantity), 0)` gives 0 instead of NULL for a customer with no orders.
Column: One fact about every item in a table, such as a product's name or price.
Comment: A note for humans written after two dashes `--`. SQLite ignores it, so you can explain what a query does.
Comparison operators: Symbols that compare two values in a condition: `=` equal, `<>` or `!=` not equal, `<`, `<=`, `>`, `>=`. SQL uses a single `=` for equality.
Concatenation (||): Joining text end to end with the `||` operator, for example `'Hi ' || 'Dana'`. Joining anything to NULL gives NULL; MySQL and SQL Server use CONCAT instead.
Condition: A test that is true or false for each row, such as `price < 20`. WHERE keeps only the rows where it is true.
Constraint: A rule the database enforces on a table, such as NOT NULL or PRIMARY KEY. Breaking one gives an error such as `UNIQUE constraint failed` (a primary key value reused) or `NOT NULL constraint failed` (a required column left empty).
COUNT: An aggregate function. `COUNT(*)` counts rows, `COUNT(column)` counts rows where that column isn't NULL, and `COUNT(DISTINCT column)` counts different values.
CREATE TABLE: A statement that makes a new, empty table by giving its name and listing its columns with their data types and constraints.
CSV (comma-separated values): A plain text file holding a table, with one row per line and commas between values. Priya exported the website reviews as reviews.csv.

D

Data type: The kind of value a column holds: INTEGER for whole numbers, REAL for decimals and TEXT for text (including ISO dates).
Database: An organized store of data, kept in tables, that a computer can search quickly and that you query with SQL.
date: A SQLite function that gives a date: `date('now')` is today's date (in UTC), and extra modifier arguments move it, as in `date('2026-10-21', '-30 days')`.
DB Browser for SQLite (DB4S): A free program with windows and buttons for working with SQLite databases, where you type queries in the Execute SQL tab and see results as a grid.
DELETE: A statement that removes the rows matching its WHERE from a table. Without WHERE it removes every row.
DISTINCT: A keyword placed after SELECT that removes repeated rows from the result, so each value or combination appears once. Inside COUNT it counts different values.
DROP TABLE: A statement that removes a whole table. With IF EXISTS it does nothing, instead of giving an error, when the table isn't there.

E

Execute SQL tab: The DB Browser for SQLite tab where you type SQL in the editor and run it with the Execute all/selected SQL button (Ctrl+Return, or Cmd+Return on a Mac). Results appear in the grid below.

F

Foreign key: A column that holds another table's primary key, linking a row to a row there. `orders.customer_id` points at `customers.customer_id`.
FROM: The clause that names the table (or joined tables) a query reads from.
Function: A named operation that takes values (arguments) and gives back a result, called by writing its name with parentheses, like `ROUND(price, 2)`.

G

GROUP BY: A clause that splits rows into groups sharing a value and runs aggregates once per group, such as one summary row per category or per month. Every other SELECT column must be grouped or inside an aggregate.

H

HAVING: A clause that filters groups after GROUP BY, so it can test aggregates such as `COUNT(*) >= 3`. WHERE filters rows before grouping instead.
Header: The first line of a CSV file, holding the column names rather than data.

I

IN: A condition true when a value matches any item in a list, such as `state IN ('OR', 'WA')`; a shorter form of several ORs. NOT IN does the opposite.
Inner join: A join that keeps only the pairs of rows that match in both tables. Plain JOIN means the same; unmatched rows, like Tom Reyes with no orders, disappear.
INSERT INTO: A statement that adds rows to a table, giving the values in the order of the listed columns. Columns you don't list become NULL.
Integer: A whole number. Dividing two integers in SQLite drops the remainder, so 15 / 12 gives 1.
Integer division: The behavior where dividing two whole numbers gives a whole number, discarding the remainder. Use `12.0` or multiply by `1.0` to get decimals.
IS NULL / IS NOT NULL: The only correct way to test for missing values, because `= NULL` never gives true. `email IS NULL` finds customers with no email.
ISO format: Dates written year-month-day, such as `2026-10-21`. Stored as text, they sort and compare correctly because the year comes first.

J

Join: Combining rows from two or more tables that share a key, such as matching each order to its customer, so a query can show names beside numbers.
Join condition: The ON part of a join, saying which rows belong together, such as `ON o.customer_id = c.customer_id`. Without it, every row pairs with every other row.
julianday: A SQLite function that turns a date into a day number, so subtracting two julianday values gives the number of days between the dates.

K

Keyword: A word with a fixed meaning in SQL, such as `SELECT` or `WHERE`. Keywords aren't case sensitive, but they are commonly written in capitals so they stand out from table names and data.

L

Left join: A join that keeps every row of the first (left) table, filling the right table's columns with NULL where there's no match. Adding `WHERE right.key IS NULL` finds rows with no match.
LIKE: A condition that matches text against a pattern with wildcards. In SQLite it ignores case for A to Z.
LIMIT: A clause, always last, that keeps only the first so many rows of a result. Use it with ORDER BY for 'top 3' questions. SQL Server uses TOP instead.

M

Mean: The average: the total divided by the number of values.
MIN / MAX: Aggregate functions giving the smallest and largest value. They also work on text (alphabetical) and ISO dates, so `MAX(order_date)` is the latest order.
Modifier: An extra argument to `date`, such as `'-30 days'` or `'+1 month'`, that moves the date.

N

Nested functions: A function call used inside another, like `ROUND(AVG(price), 2)`. The inner one is worked out first and its result becomes an argument of the outer one.
NOT NULL: A constraint meaning a column must always have a value; for example, every supplier must have a name.
NULL: A marker for a missing or unknown value, such as Northside Coffee's email. It isn't zero or empty text, and any comparison or arithmetic with it gives NULL.

O

OFFSET: Used with LIMIT to skip a number of rows first, so `LIMIT 3 OFFSET 3` gives the fourth to sixth rows. Websites use it to show results page by page.
Operator: A symbol that does something with values, such as `+`, `=` or `||`.
ORDER BY: A clause that sorts the result by one or more columns, ascending by default. Without it, the row order isn't guaranteed.

P

Pattern: Text containing wildcards, used with LIKE to match many values, such as `'Decaf%'`.
PostgreSQL / MySQL / SQL Server: Large database programs that run as servers, which many people can connect to at once, and that also understand SQL. Most SQL works the same in them as in SQLite, with some differences in features such as `CONCAT` and `TOP`.
Primary key: A column whose value is different on every row, so it identifies exactly one row, such as `customer_id` in customers.
printf: A SQLite function that formats a number as text, such as `printf('$%.2f', 59.4)` giving `$59.40`. Use it only for final display, since the output is text.

Q

Qualified name: A column name with its table in front, joined by a dot, such as `orders.order_id`. It says exactly which table a column comes from.
Query: A request to the database written in SQL, usually asking it for data.

R

Revenue: The money from sales: quantity times price, added up. At Kettle & Crane no single table holds it, so it takes a join of orders, order_items and products.
ROUND: A function that rounds a number to a given number of decimal places. Round money as the last step of a calculation to avoid tiny binary decimal errors.
Rounding error: Computers store most decimals in binary, so they can't be held exactly, and a calculation can show results like `5.76000000000001`. Wrapping the whole calculation in ROUND fixes the display.
Row: One item in a table, such as one product or one order.

S

Script: A file of SQL statements that run one after another, like kettle.sql, which builds the four Kettle & Crane tables.
SELECT: The keyword that starts every query that reads data. It lists the columns or calculations to show, and `*` means every column.
Semicolon: The `;` that ends each SQL statement, telling SQLite where one stops when several are in the editor.
Server: A database program, often on another computer, that many people connect to at once. SQLite needs no server.
Single and double quotes: Single quotes wrap text values, such as `'Portland'`. Double quotes are for names, such as an alias with spaces: `AS "Price ($)"`.
SQL (Structured Query Language): The language you use to talk to a database. It is pronounced either 'S-Q-L' or 'sequel'.
SQLite: A small, free database that keeps a whole database in one ordinary file, with no server to set up. It is built into many phones and web browsers and is a convenient database for learning SQL.
sqlite3 shell: SQLite's own program for typing SQL in a terminal. Its dot commands (`.tables`, `.mode box`, `.import`, `.quit`) belong to the shell, not to SQL.
Statement: One complete instruction to the database, such as a query, ended with a semicolon.
strftime: A SQLite function that picks parts out of a date using a format such as `'%Y-%m'` for year and month. Its result is text.
String: A piece of text, written in single quotes in SQL, like `'Hello from Kettle & Crane'`.
SUM: An aggregate function that adds up a column or a calculation such as `price * stock`, skipping NULLs. It gives NULL when no rows match.
Syntax error: An error meaning a statement breaks SQL's rules of writing, such as a misspelled keyword. The word after `near` shows where SQLite got confused.

T

Table: A grid of data about one kind of thing, such as products, customers or orders, made of rows and columns.
Terminal: A window where you type commands instead of clicking, such as Terminal on macOS or PowerShell on Windows.
Text functions (UPPER, LOWER, LENGTH): `UPPER(x)` and `LOWER(x)` change text to capitals or small letters (in SQLite only A to Z), and `LENGTH(x)` counts its characters.
typeof: A SQLite function that tells you the type of the value actually stored, used to check that imported numbers are `integer` and not `text`.

U

UPDATE: A statement that changes values in existing rows: SET gives the new values and WHERE picks the rows. Without WHERE it changes every row.
UTC (Coordinated Universal Time): The world's reference clock, hours ahead of US time. SQLite's `date('now')` uses it, so on a US evening it may show tomorrow's date.

W

WHERE: A clause that keeps only the rows for which a condition is true. It runs before grouping, so it can't test aggregates.
Wildcard: A character standing for 'anything' in a LIKE pattern: `%` matches any run of characters (including none) and `_` matches exactly one.
Write Changes / Revert Changes: DB Browser for SQLite buttons: Write Changes saves your changes to the database file, and Revert Changes throws away everything since the last save.