SQL

SELECT picks the columns, WHERE keeps only the rows that pass a condition, and ORDER BY sorts what is left; without ORDER BY the database promises no order at all.

A join combines rows from two tables by matching them on a condition; the join type decides what happens to rows that have no match.

GROUP BY collapses rows that share the same key into one row per group, aggregates like COUNT and SUM summarise each group, and HAVING filters the groups.

A subquery is a query inside another query, and a CTE (WITH ...) gives that inner query a name so you can build a complex query in readable steps, including recursive ones that walk hierarchies.

A window function computes a value for each row from a set of related rows (its window), such as a rank, a running total or the previous row's value, without collapsing rows the way GROUP BY does.

An index is a sorted lookup structure (usually a B-tree) that lets the database jump straight to matching rows in O(log n) instead of scanning the whole table, at the cost of extra storage and slower writes.

A transaction groups several statements into one all-or-nothing unit (ACID), and its isolation level decides how much concurrent transactions can see of each other's unfinished or newly committed work.