SQL is asked in nearly every backend and data role, from basic joins to query optimization. These are the fundamentals interviewers check first.
INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the left table plus matched rows from the right (with NULLs where there's no match). FULL OUTER JOIN returns all rows from both tables, with NULLs filled in wherever a match doesn't exist on either side. Choosing the right join type is really about whether unmatched rows from one or both sides should be preserved.
WHERE filters individual rows before any grouping/aggregation happens, and can't reference aggregate functions like COUNT() or SUM(). HAVING filters groups after GROUP BY aggregation has been applied, and is specifically for conditions on aggregate results, e.g., HAVING COUNT(*) > 5.
A primary key uniquely identifies each row in a table and cannot be NULL or duplicated. A foreign key is a column (or set of columns) in one table that references the primary key (or another unique key) of another table, enforcing referential integrity — you can't insert a foreign key value that doesn't exist in the referenced table (unless it's nullable and left NULL).
Normalization is the process of organizing tables to reduce data redundancy and avoid update/insert/delete anomalies, typically by decomposing tables according to normal forms (1NF, 2NF, 3NF). For example, instead of repeating a customer's address on every order row, you'd store it once in a customers table and reference it via a foreign key — so updating an address requires changing one row, not many.
An index is a separate data structure (commonly a B-tree) that maintains a sorted reference to column values and their corresponding row locations, letting the database find matching rows via lookup or range scan instead of scanning the whole table. The tradeoff is that indexes speed up reads but add overhead to writes (INSERT/UPDATE/DELETE must also update the index), so they're applied selectively to columns that are frequently filtered or joined on.
DELETE removes rows matching a WHERE clause (or all rows if omitted), is logged row-by-row, fires triggers, and can be rolled back if issued inside a transaction. TRUNCATE removes all rows at once by deallocating data pages instead of logging individual row deletions, making it much faster, but it generally can't be selectively filtered with a WHERE clause. Whether TRUNCATE itself is rollback-safe depends on the engine: in PostgreSQL and SQL Server it runs inside a transaction and can be rolled back if not yet committed, whereas in MySQL (InnoDB) it causes an implicit commit and cannot be undone. DROP removes the entire table structure itself — columns, constraints, indexes, and data — not just the rows. Auto-increment/identity counter behavior on TRUNCATE also varies: MySQL and SQL Server reset the counter to its seed value by default, while PostgreSQL only resets the underlying sequence if you explicitly add RESTART IDENTITY (the default is CONTINUE IDENTITY).
A subquery nests one query inside another (in SELECT, WHERE, or FROM), often used to filter based on an aggregated or computed value from another table. A JOIN combines rows from multiple tables directly in one result set. Query optimizers often rewrite simple subqueries into equivalent joins internally, but JOINs are typically preferred for combining and returning columns from multiple tables, while subqueries (especially correlated ones) suit existence checks or per-row comparisons.
Atomicity means a transaction's operations either all succeed or all roll back together. Consistency means a transaction moves the database from one valid state to another, respecting constraints. Isolation means concurrent transactions don't see each other's uncommitted intermediate changes (enforced with varying strictness by isolation levels). Durability means once a transaction commits, its changes survive even a crash immediately after.
A window function performs a calculation across a set of rows related to the current row — defined by an OVER() clause — without collapsing those rows into a single output row the way GROUP BY aggregation does. Common examples are ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and running aggregates like SUM() OVER(...). PARTITION BY inside the OVER() clause resets the calculation for each group, similar to GROUP BY, while ORDER BY inside OVER() controls the row sequence used for ranking or running totals. Because each input row still produces one output row, window functions are ideal for something like ranking each employee's salary within their department while still showing every employee, which a plain GROUP BY can't do since it collapses rows per group.
GROUP BY collapses multiple rows sharing the same key into a single summarized row, so if you SELECT non-aggregated columns alongside a GROUP BY, most databases require those columns to appear in the GROUP BY list. PARTITION BY, used inside a window function's OVER() clause, divides rows into groups for the calculation but returns every original row unchanged, just with the computed window value attached alongside it. So GROUP BY answers something like the total per department as one row per department, whereas PARTITION BY answers the total per department shown next to each employee's own row. The two can even be combined: aggregate with GROUP BY first, then rank or compare those grouped results using a window function in an outer query.
UNION combines the result sets of two or more SELECT queries and removes duplicate rows from the combined output, which requires an implicit sort or hash-based deduplication step. UNION ALL combines the result sets without removing duplicates, simply concatenating them, which makes it significantly faster since it skips the deduplication work. Both require the queries to have the same number of columns with compatible data types in the same order. In practice, UNION ALL should be the default choice whenever you know there won't be duplicates or don't care about them, since paying the deduplication cost for no reason is a common performance mistake.
A CTE, defined with a WITH clause, lets you name a temporary result set and reference it one or more times later in the same query, which improves readability for multi-step logic compared to nesting subqueries several levels deep. A subquery is inline and typically scoped to a single use within the surrounding query. CTEs also support recursion (WITH RECURSIVE in PostgreSQL/MySQL 8+, or a recursive CTE in SQL Server), which is useful for hierarchical data like org charts or bill-of-materials trees — something a plain subquery can't do. Whether a CTE is materialized (computed once) or inlined and optimized like a subquery depends on the engine and version — PostgreSQL, for example, changed from always materializing CTEs to allowing inlining starting in version 12 — so it isn't safe to assume a CTE always avoids repeated computation.
A clustered index determines the physical order in which rows are stored on disk — the table data itself is organized as the index — so a table can have only one clustered index, typically on the primary key. A non-clustered index is a separate structure that stores the indexed column's values alongside a pointer (or, in some engines, the clustered key) back to the actual row, and a table can have many non-clustered indexes. Because the clustered index dictates physical row order, range queries and ORDER BY on that column tend to be very efficient, while lookups through a non-clustered index often require an extra step to fetch the full row (a bookmark or key lookup) unless the query is fully covered by the index. MySQL's InnoDB always clusters the table by primary key, whereas PostgreSQL doesn't maintain a persistently clustered heap at all — its CLUSTER command physically reorders rows once but doesn't keep them clustered on subsequent inserts.
Isolation levels control how much one transaction can see of another transaction's in-progress changes, trading off consistency for concurrency. Read Uncommitted allows dirty reads, where a transaction sees another's uncommitted changes that might later be rolled back. Read Committed only sees committed data but still allows non-repeatable reads, where the same query run twice in one transaction returns different values because another transaction committed a change in between. Repeatable Read prevents non-repeatable reads by locking or snapshotting the rows a transaction has already read, but can still allow phantom reads, where a second execution of a range query returns new rows inserted by another transaction. Serializable is the strictest level, preventing all three phenomena by making concurrent transactions behave as if they ran one after another, at the cost of more blocking and contention. Default isolation level differs by engine — PostgreSQL and Oracle default to Read Committed, while MySQL's InnoDB defaults to Repeatable Read.
A self join is a regular join where a table is joined to itself, using table aliases to distinguish the two references. It's used when rows in a table relate to other rows in the same table, like an employees table where each row has a manager_id pointing to another employee's id. You'd write something like SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id to pair each employee with their manager's name in a single row. Self joins can use any join type — a LEFT self join, for example, would let you keep employees who have no manager (like a CEO) with a NULL in the manager_name column instead of dropping that row.
A view is a stored, named SELECT query that acts like a virtual table — querying it re-runs the underlying query, so its data always reflects the current state of the base tables. Views are used to simplify complex or repeated queries, restrict which columns or rows certain users can see, and provide a stable interface even if underlying table structures change. A materialized view, supported by PostgreSQL and Oracle among others, differs in that it actually stores the query's result set on disk and must be explicitly refreshed, trading data freshness for much faster read performance since the underlying query doesn't need to re-execute on every access. Plain views generally can't be indexed directly (SQL Server's schema-bound indexed views are an exception), while materialized views can have their own indexes since they're physically stored data.
A stored procedure is a precompiled block of SQL (and often procedural logic) invoked with CALL or EXEC, can perform actions like INSERT/UPDATE/DELETE, manage transactions, and return zero, one, or multiple result sets or output parameters. A function is generally required to return a single value or table and is meant to be used within a query — e.g., in a SELECT list or WHERE clause — so most databases restrict functions from performing side-effecting operations like committing transactions. Functions can typically be composed inline inside SQL statements, while stored procedures are invoked as standalone statements. The exact restrictions vary by engine — PostgreSQL functions, for instance, can modify data and even manage transactions in some contexts, which is more permissive than the traditional procedure/function split in SQL Server or Oracle.
NULL represents an unknown or missing value, not zero or an empty string, and any direct comparison involving NULL using =, <>, <, or > evaluates to UNKNOWN rather than TRUE or FALSE — which is why you use IS NULL or IS NOT NULL instead of = NULL. This has real consequences in WHERE and HAVING clauses, since rows evaluating to UNKNOWN are excluded just like FALSE rows would be. Aggregate functions like COUNT(), SUM(), and AVG() generally ignore NULLs in their target column (though COUNT(*) counts rows regardless of NULLs), which can subtly skew an average if you expected NULLs to count as zero. In three-valued logic, combining conditions with AND/OR involving NULL follows specific rules — for instance, NULL OR TRUE evaluates to TRUE, but NULL AND FALSE evaluates to FALSE — which is a common source of subtle bugs in filtering logic.
A composite key (or composite primary key) is a primary key made up of two or more columns whose combination must be unique, used when no single column uniquely identifies a row on its own. A classic example is a junction table for a many-to-many relationship, like an order_items table keyed on (order_id, product_id), where neither column alone is unique but the pair is. Composite keys can also be referenced by composite foreign keys in other tables, and the database typically builds a single composite index to enforce uniqueness across all the key's columns together. One tradeoff is that composite keys make foreign key references and joins in child tables more verbose than a single column, which is why some schemas prefer a surrogate single-column key (like an auto-incrementing id) even when a natural composite key exists, adding a separate unique constraint on the composite columns instead.
Denormalization deliberately introduces redundancy — for example, storing a precalculated total or duplicating a customer's name on an orders table — to reduce the number of joins needed for common read queries, which can significantly improve read performance at scale. The tradeoff is that redundant data must be kept in sync across multiple places, increasing the risk of update anomalies and requiring extra application or trigger logic to maintain consistency. Denormalization is typically applied selectively, in reporting/analytics systems or specific hot-path tables, rather than across an entire schema, and is often layered on top of a normalized design rather than replacing it. It's also the underlying idea behind materialized views and read-optimized data warehouse schemas like star schemas, which trade some normalization for query simplicity and speed.