Lizzy McIMF

Relational Algebra

a formal, mathematical query language that operates on relations (tables) and produces new relations as results.

Closure

every operation takes one or more relations in and returns a single relation out, so the output of one operation can feed straight into the next.

Why Relational Algebra matters?

it’s the theoretical foundation SQL is built on; every SQL query can be expressed as a sequence of relational algebra operations. Understanding it helps you reason about what a query does and why some queries perform better than others.

Set Operations

Union, Intersection, Difference, Cartesian Product.

Relational Operations

Selection, Projection, Join.

Union (∪)

returns every row that appears in either set, duplicates removed.

Intersection (∩)

returns only the rows that appear in both sets.

Difference (−)

keeps rows from A that do not appear in B.

Cartesian Product (×)

pairs every row of A with every row of B, producing all possible combinations.

Selection (σ)

filters rows based on a condition, like SQL’s WHERE clause columns stay the same, only matching rows remain.

Projection (π)

picks specific columns and drops the rest; duplicate resulting rows are automatically removed.

DML (Data Manipulation Language)

the part of SQL that manages and manipulates the data stored inside tables, as opposed to DDL, which creates/modifies the structure of the database itself.

INSERT

adds a new data/record into the table.

UPDATE

modifies an existing data/record in the table.

DELETE

removes a data/record from the table.

SELECT

reads data/records from the table.

INT

whole numbers, no quotes needed (e.g. 100).

DECIMAL(10,2)

fixed-point number with set decimal places, no quotes (e.g. 1500.75).

FLOAT

floating-point number, no quotes (e.g. 89.5).

VARCHAR(50)

variable-length text, wrapped in single quotes (e.g. ‘Elmar Marquez’).

ENUM

only accepts values defined in its predefined list (e.g. ‘Male’).

DATE format

written as ‘YYYY-MM-DD’ (e.g. ‘2026-10-02’).

TIME format

written as ‘HH:MM:SS’ (e.g. ‘09:30:00’).

DATETIME format

written as ‘YYYY-MM-DD HH:MM:SS’ (e.g. ‘2026-10-02 09:30:00’).

Numeric values

no quotation marks needed (e.g. 100).

String values

wrapped in single quotation marks (e.g. ‘Elmar Marquez’).

Boolean values

TRUE or FALSE, equivalent to 1 or 0 in MySQL.

=

exact match.

!= or <>

not equal to.

>

Greater than (for numeric values).

<

less than (for numeric values).

>=

greater than or equal to.

<=

less than or equal to.

AND

both conditions must be true.

OR

at least one condition must be true.

NOT

excludes matching conditions.

IN

matches any value in a list.

NOT IN

excludes values in a list.

BETWEEN

matches a range of values.

NOT BETWEEN

excludes a range of values.

LIKE

matches a text pattern.

NOT LIKE

excludes a text pattern.

IS NULL

finds missing values.

IS NOT NULL

finds non-missing values.

%

Matches zero or more characters

_

Matches exactly one character

%text%

Finds text anywhere in a value

text%

Finds values starting with text

%text

Finds values ending with text

Aggregate Functions

combine or summarize data from multiple rows into one summary value; commonly used with SELECT and GROUP BY.

COUNT(*)

counts the total number of rows; works on all data types and counts non-NULL values (or all rows with COUNT()).

SUM(Amount)

adds up numeric values into a total; numeric only.

AVG(Grade)

calculates the average of numeric values; numeric only.

MAX(Price)

finds the highest value; works on numeric, text, or date/time — for text it uses collation rules, for dates it finds the latest.

MIN(Price)

finds the lowest value; works on numeric, text, or date/time — for text it uses collation rules, for dates it finds the earliest.

Note on NULLs

SUM() and AVG() are meant for numeric expressions; most aggregate functions skip NULL values, but COUNT(*) still counts every row.

GROUP BY

groups rows by a shared value so you can summarize each group separately (e.g. average grade per year level).

HAVING

filters grouped data based on a condition, usually applied after an aggregate function like COUNT(), SUM(), or AVG().

WHERE

filters individual rows, applied before GROUP BY; can’t use aggregate functions in its condition.

HAVING vs. WHERE

HAVING filters groups of rows instead of individual ones, is applied after grouping, and CAN use aggregate functions in its condition.

ORDER BY

sorts results ascending (ASC, the default) or descending (DESC).

LIMIT

caps the maximum number of rows returned.

Combining clauses

WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT can all be combined in a single query, in that order.

JOIN

combines related data from two or more tables using a matching column, usually a primary key paired with a foreign key.

Why JOINs exist

normalization splits data into related tables to cut redundancy, so JOINs are what let you pull that related data back together in a single query.

INNER JOIN

returns only rows with matching values in both tables.

LEFT (OUTER) JOIN

keeps all rows from the left table, even unmatched ones; the unmatched right-table columns come back NULL.

RIGHT (OUTER) JOIN

keeps all rows from the right table, even unmatched ones; the unmatched left-table columns come back NULL.

Joining 3+ tables

SQL lets you chain three or more tables together in one query when the needed info is spread across them; each JOIN needs its own condition explaining how the tables relate.

Subquery

a query nested inside another query, often in parentheses, used to feed information the outer query needs; can appear in WHERE, FROM, or SELECT.

Correlated Subquery

a subquery that references a column from the outer query, so it re-evaluates for every row the outer query is currently checking.

EXISTS

returns true as soon as the subquery finds at least one matching row.