Joins, window functions, indexing, query plans and transactions. Open a question to read the answer.
Both store character strings, but CHAR is fixed-length and VARCHAR2 is variable-length. (VARCHAR2 is Oracle-specific; SQL Server, PostgreSQL and MySQL use VARCHAR.)
| Aspect | CHAR(n) | VARCHAR2(n) |
|---|---|---|
| Storage | Always n bytes/chars; right-padded with blanks | Actual length plus a 1–2 byte length prefix |
| Comparison | Blank-padded semantics; trailing spaces ignored | Trailing spaces are significant |
| Maximum size | 2000 bytes in Oracle | 4000 bytes (32767 with extended strings) |
| Typical use | Fixed codes: ISO country, status flags | Names, addresses, free text |
CREATE TABLE customers (
country_code CHAR(2), -- 'IN', 'US': always 2 bytes
full_name VARCHAR2(100) -- 'Priya Sharma' occupies 12 bytes
);A practical trap: 'AB ' stored in CHAR(5) compares equal to 'AB', while the same value in VARCHAR2 does not. Oracle’s VARCHAR is reserved and currently behaves like VARCHAR2, which is why Oracle code always uses the 2 form.
A view is a named, stored SELECT statement that behaves like a virtual table. It holds no rows itself; each query against the view is rewritten by the optimizer to run against the underlying base tables, so it always reflects current data.
Typical reasons to create one:
SELECT on the view while denying it on the base tables, exposing only permitted columns or rows.CREATE VIEW active_orders AS
SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'ACTIVE';The request path shows that the view is only a query layer over the base tables.

Views that reference a single table without aggregates or DISTINCT are generally updatable; WITH CHECK OPTION prevents inserts or updates that would fall outside the view’s WHERE filter. Because a view adds no storage, it adds no performance on its own; a materialized view (Q43) is the variant that physically stores results.
A UNIQUE constraint guarantees that no two rows hold the same value (or the same combination of values) in the constrained column(s). The database enforces it on every INSERT and UPDATE by maintaining a unique index behind the scenes, so the rule cannot be bypassed by application code.
It differs from a primary key in two ways: a table may have many UNIQUE constraints but only one primary key, and UNIQUE columns normally accept NULL. The NULL handling is dialect-dependent: Oracle, PostgreSQL and MySQL allow multiple NULLs because NULL <> NULL, whereas SQL Server treats NULL as a value and permits only one (PostgreSQL 15 added NULLS NOT DISTINCT to opt into that behaviour).
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);
ALTER TABLE enrolments
ADD CONSTRAINT uq_enrol UNIQUE (student_id, course_id); -- compositeTypical uses are natural identifiers such as e-mail addresses, SKU codes or passport numbers when a surrogate key is the primary key.
A subquery is a SELECT nested inside another SQL statement. It can appear in the WHERE or HAVING clause as a filter, in the FROM clause as a derived table, or in the SELECT list as a scalar expression, and it can feed INSERT, UPDATE and DELETE as well.
Three shapes matter in interviews:
WHERE salary > (SELECT AVG(salary) FROM employees).IN, ANY, ALL or EXISTS.-- Employees earning above their own department's average
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(d.salary)
FROM employees d
WHERE d.dept_id = e.dept_id);Modern optimizers often rewrite subqueries into joins or semi-joins, so the choice between a subquery and a join is usually about readability rather than speed; the exception is a correlated subquery the planner cannot unnest, which can execute once per outer row.
A composite (compound) primary key is a primary key made of two or more columns whose combined value uniquely identifies each row; no single column needs to be unique on its own. All member columns are implicitly NOT NULL, and the database builds one index over the column combination.
The classic case is a junction table for a many-to-many relationship:
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);Column order inside the key matters for index use: the index above serves lookups by order_id alone but not by product_id alone, so a second index on product_id is usually added. Trade-offs against a surrogate key: the composite key enforces the natural business rule directly and avoids an extra column, but every child table must carry all key columns as its foreign key, and joins become wider. Many teams add a surrogate key plus a UNIQUE constraint on the natural combination to get both benefits.
WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and aggregation have run. Because WHERE executes first, it cannot reference an aggregate such as SUM(); because HAVING executes after grouping, it can.
| Aspect | WHERE | HAVING |
|---|---|---|
| Operates on | Single rows from the FROM/JOIN result | Groups produced by GROUP BY |
| Aggregates allowed | No | Yes |
| Evaluated | Before GROUP BY | After GROUP BY |
| Performance | Reduces rows early, enables index use | Runs on already-aggregated data |
SELECT region, SUM(amount) AS revenue
FROM sales
WHERE sale_date >= DATE '2025-01-01' -- row filter
GROUP BY region
HAVING SUM(amount) > 100000; -- group filterAny condition that does not involve an aggregate belongs in WHERE, even though most databases accept it in HAVING, because filtering early shrinks the data the grouping step must process.
A CTE is a named temporary result set defined with WITH at the top of a statement and referenced like a table in the query that follows. It exists only for the duration of that single statement, is supported in Oracle, SQL Server, PostgreSQL, MySQL 8+ and SQLite, and can be chained so later CTEs build on earlier ones.
Reasons to reach for one:
WITH RECURSIVE, Q21).UPDATE, DELETE or INSERT.WITH monthly AS (
SELECT customer_id, DATE_TRUNC('month', ordered_at) AS m,
SUM(total) AS spend
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', ordered_at)
)
SELECT customer_id, AVG(spend) AS avg_monthly_spend
FROM monthly
GROUP BY customer_id;A CTE is not automatically a performance tool: SQL Server and Oracle usually inline it; PostgreSQL inlined it only from version 12 onward (earlier versions always materialized it, and MATERIALIZED / NOT MATERIALIZED hints still control this).
Both combine the result sets of two or more SELECT statements with compatible column lists into one result. UNION removes duplicate rows across the combined output; UNION ALL keeps every row, including duplicates that appear within or across the inputs.
| Aspect | UNION | UNION ALL |
|---|---|---|
| Duplicates | Eliminated (entire row compared) | Retained |
| Cost | Sort or hash to de-duplicate | Simple concatenation |
| Row count | ≤ sum of inputs | Exactly the sum of inputs |
| Default choice | Only when duplicates must go | Whenever inputs are already disjoint |
SELECT email FROM customers
UNION ALL
SELECT email FROM newsletter_subscribers; -- same address may appear twiceRules shared by both: each SELECT must return the same number of columns with compatible types, column names come from the first SELECT, and ORDER BY applies once to the whole result. Choosing UNION out of habit on large partitioned data is a common hidden cost because the de-duplication step forces a sort or hash of the entire output.
Measurement comes first: capture the actual execution plan (EXPLAIN ANALYZE in PostgreSQL, SET STATISTICS IO, TIME ON or the actual plan in SQL Server, DBMS_XPLAN.DISPLAY_CURSOR in Oracle) and identify the operator consuming the most time or rows. Then work through the usual causes in order of payoff:
WHERE UPPER(email) = ...) or implicit type conversion. Add the index, rewrite the predicate, or use a function-based index.SELECT *, unnecessary DISTINCT or ORDER BY, UNION instead of UNION ALL, fetching 100 000 rows to show 20 (add keyset pagination, Q45).Verify after each change by comparing logical reads and elapsed time, not just one lucky run. Structural fixes (covering index, partitioning, materialized view, denormalized summary table) come only when the query itself is already tight, and every added index must be weighed against its write cost.
Partitioning splits one large logical table into smaller physical pieces (partitions) according to a column value, while applications still query a single table name. The optimizer uses the partition key to skip partitions that cannot contain matching rows (partition pruning), and maintenance such as archiving becomes a metadata operation on one partition instead of a mass DELETE.
Common strategies:
order_date.region IN ('EU','US').-- PostgreSQL declarative range partitioning
CREATE TABLE events (
event_id BIGINT, occurred_at TIMESTAMP NOT NULL, payload JSONB
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2025_11 PARTITION OF events
FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');The diagram shows a date filter touching only the matching partition.

Partitioning only helps queries that filter on the partition key; queries that do not touch every partition and can get slower. Each engine differs in detail: Oracle and SQL Server support global indexes across partitions, while PostgreSQL unique constraints must include the partition key. This is horizontal partitioning within one server; sharding applies the same idea across multiple servers.
A self join joins a table to itself, using two different aliases so each side can be treated as a separate row source. There is no special keyword; it is an ordinary inner or outer join in which both sides name the same table.
It is used whenever rows in one table relate to other rows in the same table:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;The LEFT JOIN keeps the CEO, whose manager_id is NULL. For pairwise comparison, add e1.id < e2.id rather than <> so each pair appears once and a row never pairs with itself. A self join resolves one level only; walking an entire hierarchy of unknown depth needs a recursive CTE (Q21) or Oracle’s CONNECT BY.
Pattern matching tests whether a string conforms to a pattern rather than equals a fixed value. Standard SQL provides LIKE with two wildcards: % matches any sequence of zero or more characters and _ matches exactly one character. An ESCAPE clause lets a literal % or _ be matched.
SELECT name FROM products
WHERE name LIKE 'Blue%' -- starts with Blue
OR sku LIKE 'AB-___' -- AB- followed by exactly 3 chars
OR note LIKE '100\% off' ESCAPE '\';Beyond LIKE, every major engine offers regular expressions with its own syntax: REGEXP_LIKE in Oracle, ~ and SIMILAR TO in PostgreSQL, REGEXP / RLIKE in MySQL, and T-SQL’s limited character classes inside LIKE ([A-Z]), with full regex arriving in SQL Server 2025. Case sensitivity follows the column collation: PostgreSQL LIKE is case-sensitive (ILIKE is not), while default SQL Server and MySQL collations are case-insensitive.
Performance matters: a pattern anchored at the start ('Blue%') can use a B-tree index as a range scan, but a leading wildcard ('%blue%') forces a full scan, which is why text search columns use full-text indexes or trigram indexes (PostgreSQL pg_trgm) instead. Oracle 12c and later also offer MATCH_RECOGNIZE for row-pattern matching across sequences of rows.
The distinction is one of timing in the logical order of evaluation: FROM and JOIN produce rows, WHERE discards rows, GROUP BY collapses survivors into groups, aggregates are computed, HAVING discards groups, then SELECT, ORDER BY and LIMIT run. So WHERE can only see column values, while HAVING sees group keys and aggregate results.
The pipeline below makes the two filter points explicit.
Consequences that interviewers probe:
HAVING without GROUP BY treats the whole table as one group: SELECT COUNT(*) FROM t HAVING COUNT(*) > 10 returns either one row or none.SELECT list cannot be used in WHERE in standard SQL (MySQL allows it in HAVING); repeat the expression or use a CTE.HAVING is legal but wasteful because the rows are grouped before being thrown away.JOIN, a filter on the outer side of a LEFT JOIN placed in WHERE turns it into an inner join; the same filter in HAVING on an aggregate does not.Writing the row condition in WHERE is also what lets the optimizer use an index and shrink the hash or sort input before grouping.
Apply. In a reporting warehouse for an e-commerce platform, the daily “high-value customers” job restricts rows with WHERE order_date >= CURRENT_DATE - 30 AND status = 'PAID', groups by customer_id, and keeps only HAVING SUM(total) > 500. The date and status predicates live in WHERE so the partitioned, indexed orders table is pruned to a month of data before any grouping happens. When a product owner asks for “customers with more than three distinct categories”, that becomes HAVING COUNT(DISTINCT category_id) > 3, which is impossible to express in WHERE.
Test. A fixture set with known totals sits in the integration database: two customers straddling the 500 threshold (499.99 and 500.01) and one with an unpaid order that must not count. Assertions check both the row set and the aggregate values. A second test moves a predicate from WHERE to HAVING deliberately and confirms the result is identical, which protects against a later refactor changing semantics on LEFT JOIN queries.
Troubleshoot. Symptom: the job slowed from 4 s to 90 s after a developer “simplified” the query by moving status = 'PAID' into HAVING. The plan showed a full scan and a hash aggregate over the whole year because partition pruning no longer applied. Moving the predicate back to WHERE restored the plan. A second recurring bug is HAVING COUNT(*) > 1 used to find duplicates while the SELECT list contains columns not in GROUP BY; PostgreSQL rejects it, MySQL with ONLY_FULL_GROUP_BY disabled silently returns arbitrary values.
An important consideration: a condition on a non-aggregated column almost never belongs in HAVING, and code review should flag it even when the database accepts it.
GROUP BY partitions the rows that survive WHERE into groups sharing the same values of the listed expressions, and collapses each group into one output row. Aggregate functions (COUNT, SUM, AVG, MIN, MAX, plus STRING_AGG / LISTAGG / GROUP_CONCAT, ARRAY_AGG, statistical functions) compute one value per group from the rows inside it.
Rules worth stating precisely:
SELECT list must appear in GROUP BY (or be functionally dependent on it, which PostgreSQL and MySQL 5.7+ recognise for primary keys).NULL, except COUNT(*), which counts rows. COUNT(col) counts non-null values; COUNT(DISTINCT col) counts distinct non-null values.AVG of an integer column is integer division in SQL Server and PostgreSQL; cast first.NULL group keys form a single group.SELECT department_id,
COUNT(*) AS headcount,
COUNT(termination_date) AS leavers,
ROUND(AVG(salary * 1.0), 2) AS avg_salary,
MAX(hire_date) AS latest_hire
FROM employees
GROUP BY department_id
ORDER BY headcount DESC;Extensions GROUPING SETS, ROLLUP and CUBE produce several grouping levels in one pass (subtotals and grand totals), with GROUPING() distinguishing a real NULL key from a subtotal row. FILTER (WHERE ...) in PostgreSQL, or SUM(CASE WHEN ... END) elsewhere, gives conditional aggregates for pivot-style reports. Physically the engine uses a hash aggregate or a sort followed by streaming aggregation; an index ordered on the group keys can supply the sorted input for free.
Apply. A payments service produces a daily settlement report: per merchant and currency, the count of captured transactions, gross amount, refunds (SUM(CASE WHEN type='REFUND' THEN amount END)) and net. A ROLLUP (merchant_id, currency) adds per-merchant and grand totals in one query instead of three. The same aggregate also feeds a nightly materialized summary table that dashboards read, so the heavy grouping over millions of rows runs once rather than per user.
Test. The report query is wrapped in a view, and tests seed a small, deliberately awkward dataset: a merchant with zero transactions (must appear via LEFT JOIN with COUNT(t.id) = 0, not vanish), a transaction with NULL amount, and a refund larger than the capture. Expected values are computed independently in the test code. A reconciliation test asserts that the sum of per-merchant nets equals the grand-total row, which catches rows silently dropped by a join or filter.
Troubleshoot. Classic symptom: totals double after a new LEFT JOIN to a one-to-many table (each payment now appears once per fee line). Diagnosis is COUNT(*) versus COUNT(DISTINCT payment_id) per group; the fix is to aggregate the child table in a CTE before joining. The second common issue is the “column must appear in GROUP BY” error after someone adds a display column; the resolution is to join the descriptive attribute after grouping, or group by the key and pick the attribute with MAX() when it is functionally dependent. For slowness, the plan reveals a sort-based aggregate spilling to disk; raising work memory or adding an index on the grouping columns changes it to a streaming aggregate.
An important consideration: AVG over a column containing NULL divides by the count of non-null values, which is rarely what the business means by “average per transaction”.
Both are set operators that stack the rows of multiple SELECTs vertically; the difference is that UNION applies DISTINCT to the combined result and UNION ALL does not. Three details follow from that.
Semantics. UNION compares whole rows, treating two NULLs as equal for de-duplication purposes, so (1, NULL) from the first query and (1, NULL) from the second collapse into one row. UNION ALL is order-preserving in practice but the standard does not guarantee row order without an outer ORDER BY.
Cost. De-duplication requires a hash or sort over the full combined result, which can dominate run time on wide rows. UNION ALL streams rows through with no blocking step and is therefore the default in ETL and in queries over partitioned or sharded tables where the inputs are disjoint by construction.
Types and names. Columns are matched by position, not name. The result takes names from the first branch and types by type precedence; UNION between INT and VARCHAR fails in strict engines and silently converts in lenient ones.
SELECT 'current' AS src, order_id, total FROM orders
UNION ALL
SELECT 'archive', order_id, total FROM orders_archive
ORDER BY order_id; -- applies to the whole resultINTERSECT and EXCEPT (MINUS in Oracle) are the sibling operators; MySQL added them in 8.0.31. Choose UNION when the business rule requires a distinct set, otherwise UNION ALL, and when both duplicates and a tag are needed, add a literal column as above so the branches never collide.
Apply. An order service keeps hot orders in orders and moves rows older than 18 months into orders_archive. A reporting view orders_all is defined as UNION ALL of the two tables, since an order lives in exactly one of them. The customer-search endpoint, by contrast, merges hits from customers and leads tables with UNION, because the same email can exist in both and the UI must show one row per email.
Test. Unit tests on the view insert the same order_id into both tables and assert that orders_all returns two rows, documenting that the view relies on the archive job for disjointness. A schema test compares information_schema.columns of both tables so a column added to one but not the other fails CI before the view breaks in production. For the search query, a test with duplicated email in differing letter case verifies whether the collation makes UNION treat them as one row.
Troubleshoot. A reporting dashboard became slow after a refactor; the plan showed a hash-distinct over 40 million rows because the developer used UNION. Replacing it with UNION ALL removed the step entirely. The opposite bug also appears: a finance total was undercounted because UNION collapsed two legitimately identical refund rows (same amount, same day, no key column selected). The fix was to include the primary key in the select list or switch to UNION ALL. Type mismatches surface as “conversion failed” errors only when the second branch first returns data, so the conversion is made explicit with CAST in every branch.
An important consideration: UNION is not a substitute for a missing primary key; de-duplication should be a conscious decision expressed by selecting a key, not an accident of the operator.
A correlated subquery is a subquery that references a column from the enclosing query, so its result depends on the current outer row. Logically it is re-evaluated for each outer row, like a loop; the optimizer may transform it into a join (semi-join for EXISTS, anti-join for NOT EXISTS) when it can prove equivalence, but it cannot always do so.
-- Customers with at least one order in the last 30 days
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id -- correlation
AND o.ordered_at >= CURRENT_DATE - 30);Where it appears and what it means:
WHERE with EXISTS / NOT EXISTS: a membership test that stops at the first matching row and handles NULLs cleanly, unlike NOT IN, which returns no rows if the subquery yields any NULL.SELECT list as a scalar: “latest order date per customer”; must return at most one row or the statement fails.UPDATE ... SET col = (SELECT ... WHERE inner.id = outer.id): row-by-row derived updates.LATERAL (PostgreSQL, MySQL 8.0.14+) or CROSS APPLY (SQL Server, Oracle 12c+) is a correlated subquery in the FROM clause and the idiomatic way to fetch top-N rows per parent.Performance hinges on an index on the correlated column of the inner table; without one the engine performs a scan per outer row and cost grows as rows × inner size. Many correlated scalar subqueries in one SELECT list can often be replaced by one window function pass.
Apply. In a subscription billing system, the invoice generator selects accounts with an active plan and no invoice for the current period using NOT EXISTS (SELECT 1 FROM invoices i WHERE i.account_id = a.account_id AND i.period = :period). NOT EXISTS is chosen over NOT IN because invoices.account_id is nullable for legacy manual invoices. A LATERAL join fetches each account’s most recent payment method, replacing three scalar subqueries that previously appeared in the select list.
Test. The test database contains an account with a NULL account_id invoice to prove the NOT IN trap would have excluded everyone; the test asserts the correct count. The LATERAL top-1 query is tested against an account with two payment methods updated in the same second to pin down the tie-breaker (ORDER BY updated_at DESC, id DESC). A performance guard runs EXPLAIN in CI and fails if the plan contains a nested loop with the inner side being a sequential scan on invoices.
Troubleshoot. Production symptom: the generator took 40 minutes for 2 million accounts. EXPLAIN ANALYZE showed the subquery executed 2 million times (loops=2000000) with a full scan each time because the composite index was on (period, account_id) and the query filtered account_id first with a function applied. Adding an index on (account_id, period) turned it into an index-only probe and the job finished in 90 seconds. Scalar subqueries that suddenly fail with “more than one row returned” usually mean a uniqueness assumption was broken by a data change; the fix is LIMIT 1 with a deterministic ORDER BY or a corrected join key.
An important consideration: a correlated subquery that the planner reports with a high loop count is the single most common cause of queries that are fine in test and unusable at production volume.
A CTE names a subquery for the duration of one statement (Q7); a recursive CTE additionally references its own name, letting a query iterate until no new rows are produced. That is the only standard way in SQL to walk a tree or graph of unknown depth, generate a series, or process a dependency chain.
A recursive CTE has a fixed shape:
UNION ALL (or UNION to stop on cycles by discarding repeated rows).WITH RECURSIVE org AS (
SELECT employee_id, manager_id, name, 1 AS depth,
name::text AS path
FROM employees WHERE manager_id IS NULL -- anchor
UNION ALL
SELECT e.employee_id, e.manager_id, e.name, o.depth + 1,
o.path || ' > ' || e.name
FROM employees e
JOIN org o ON e.manager_id = o.employee_id -- recursive
)
SELECT * FROM org ORDER BY path;The iteration loop below is what the engine runs for the query above.

Each iteration sees only the rows produced by the previous one (not the accumulated set), and the engine stops when an iteration returns zero rows. RECURSIVE is required in PostgreSQL, MySQL 8 and SQLite, optional in SQL Server and Oracle. Guard rails differ: SQL Server defaults to MAXRECURSION 100; PostgreSQL and Oracle have no limit, so a cycle in the data loops forever unless a depth < N predicate, a path check, or CYCLE clause (Oracle, PostgreSQL 14+) is added. Oracle also offers the older CONNECT BY PRIOR syntax for the same job.
Apply. A product catalogue stores categories as an adjacency list (category_id, parent_id). The search service needs “all products under Electronics including sub-categories”, which a recursive CTE resolves in one round trip and feeds into WHERE category_id IN (SELECT category_id FROM subtree). The same pattern computes bill-of-materials explosions in a manufacturing module and the chain of depends_on jobs in the scheduler. Non-recursive CTEs structure the monthly churn report into named stages (active_last_month, active_this_month, churned) that reviewers can read top-down.
Test. Fixtures include a five-level tree, a node with two children, and a deliberate cycle (A → B → A) in a dedicated test; the assertion is that the query terminates and returns each node once, which validates the depth guard or CYCLE clause. Results are compared with a materialized path column computed in application code. For non-recursive CTEs, each stage is tested in isolation by selecting from the CTE alone.
Troubleshoot. A nightly job hung at 100 % CPU: a data-entry error had set a category’s parent to one of its own descendants and the recursion never terminated. The immediate fix was a WHERE depth < 20 predicate; the permanent fix was a trigger rejecting cyclic parent assignments. On PostgreSQL 11, a CTE used three times was materialized once and then scanned without indexes, making a two-second query take a minute; upgrading to 12 (or restructuring as a subquery) let the planner inline and push predicates down. The SQL Server counterpart error is “maximum recursion 100 has been exhausted”, resolved with OPTION (MAXRECURSION 0) only after proving the data is acyclic.
An important consideration: the engine evaluates the recursive member breadth-first and never short-circuits, so filters that can be applied inside the recursive member should be placed there, not on the final SELECT.
A window function computes a value for each row using a set of related rows (the window) without collapsing them the way GROUP BY does. The row count stays the same; a new column is added. The window is described by an OVER clause with three optional parts:
PARTITION BY – splits rows into independent groups; the function restarts in each.ORDER BY – defines the ordering inside the partition, needed for ranking and running totals.ROWS / RANGE / GROUPS BETWEEN ... AND ...) – the subset of the partition visible to aggregate windows; default with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.SELECT order_id, customer_id, ordered_at, total,
SUM(total) OVER (PARTITION BY customer_id
ORDER BY ordered_at) AS running_total,
AVG(total) OVER (PARTITION BY customer_id
ORDER BY ordered_at
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
AS moving_avg_3,
LAG(total) OVER (PARTITION BY customer_id
ORDER BY ordered_at) AS prev_total
FROM orders;Families: ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), offset (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), aggregate windows (SUM, AVG, COUNT over a frame), and distribution (PERCENT_RANK, CUME_DIST). Window functions are evaluated after WHERE, GROUP BY and HAVING, so filtering on their result requires wrapping the query in a CTE or derived table. Supported in Oracle, SQL Server 2012+, PostgreSQL, MySQL 8.0+ and SQLite 3.25+. A WINDOW w AS (...) clause names a specification once for reuse; LAST_VALUE with the default frame returns the current row, a well-known surprise fixed by ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Apply. In a fintech ledger, the account statement endpoint returns each transaction with a running balance computed as SUM(amount) OVER (PARTITION BY account_id ORDER BY posted_at, txn_id) instead of storing a balance column that drifts. A fraud rule compares each card swipe with the previous one via LAG(location) and LAG(occurred_at) to flag impossible travel. Month-over-month growth in the KPI dashboard uses LAG(revenue) OVER (PARTITION BY region ORDER BY month). A session-ization query marks a new session whenever occurred_at - LAG(occurred_at) > INTERVAL '30 minutes' and then uses a cumulative SUM of those flags as the session id.
Test. Golden tests feed a fixed sequence of transactions, including two with the same timestamp, and assert the running balance at every row; the tie-breaker txn_id in ORDER BY is what makes the output deterministic, and the test would catch its removal. Frame behaviour is pinned by a test that compares LAST_VALUE with and without an explicit frame. The statement endpoint has a latency budget test at 50 000 transactions per account.
Troubleshoot. Users saw balances that changed between page loads: the ORDER BY lacked a tie-breaker and the engine returned ties in arbitrary order, so running sums differed. Adding txn_id fixed it. A slow report showed three WindowAgg nodes with separate sorts in the plan because each OVER clause used a different ORDER BY; aligning the partitions let the planner compute all windows in one sort. Memory spills in the sort step were addressed with an index on (account_id, posted_at), which supplies pre-sorted input.
An important consideration: windows cannot be filtered in the same query level, so WHERE row_number = 1 always requires a wrapping CTE, and forgetting this produces a syntax error that confuses newcomers.
All three are ranking window functions that assign an integer position to each row within a partition according to ORDER BY. They differ only in how they treat ties.
| Function | Ties | Gaps after ties | Example for scores 90, 85, 85, 70 |
|---|---|---|---|
ROW_NUMBER() | Broken arbitrarily; always unique | None | 1, 2, 3, 4 |
RANK() | Same rank for equal values | Yes: skips | 1, 2, 2, 4 |
DENSE_RANK() | Same rank for equal values | No | 1, 2, 2, 3 |
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER w AS rn,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS drnk
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);Which one to use follows from the question being asked:
ROW_NUMBER with a full tie-breaker in ORDER BY, then WHERE rn = 1 in an outer query.RANK.DENSE_RANK with WHERE drnk <= 3.NTILE(n) divides rows into n buckets, and PERCENT_RANK gives a relative rank between 0 and 1. ROW_NUMBER without an ORDER BY is permitted in some engines but produces non-deterministic numbering; Oracle requires an ORDER BY. All three are available in Oracle, SQL Server, PostgreSQL, MySQL 8+ and SQLite 3.25+.
Apply. A CRM syncs contacts from several sources and keeps duplicates; the “golden record” view picks one row per email with ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY source_priority, updated_at DESC, id) and filters rn = 1. The sales leaderboard uses RANK() so two reps with equal revenue share position 1 and the next is 3, matching the commission plan’s wording. Product analytics wants “customers in the top 3 spending tiers per region” and uses DENSE_RANK() on distinct spend levels. Deleting duplicate rows while keeping one uses the same ROW_NUMBER CTE inside a DELETE.
Test. Tests construct ties on purpose: two contacts with identical priority and timestamp must still yield one deterministic winner, which only holds because id is the final tie-breaker. The leaderboard test asserts 1, 1, 3 for a tie and documents why DENSE_RANK would be wrong under that commission plan. A property-style test checks COUNT(*) of the golden view equals COUNT(DISTINCT lower(email)) of the source.
Troubleshoot. Support reported a contact’s phone number “flipping” daily; the view lacked the id tie-breaker, so each run chose a different duplicate. A reporting bug showed rank 1, 2, 2, 3 where the spec said 1, 2, 2, 4; the author had used DENSE_RANK where RANK was intended, revealed by a query of the tie rows. Performance-wise, a de-duplication over 200 million rows spilled to disk; partitioning the job by the first character of the email, and an index on (lower(email), source_priority, updated_at DESC), produced sorted input and removed the spill.
An important consideration: a ranking ORDER BY without a unique final column is non-deterministic, and every bug involving “random” results from these functions traces back to that omission.
An index is a separate ordered or hashed structure that maps column values to row locations so the engine can find rows without scanning the table. The main types are:
<, BETWEEN), prefix LIKE and ORDER BY on the leading columns. Composite B-trees are usable only from the leftmost column onward.WHERE clause, and function-based or expression indexes.A covering index contains every column a query needs, so the engine answers it from the index alone and never touches the table (an “index-only scan” or eliminating the “key lookup”/”bookmark lookup”). Columns needed only for output can be appended without affecting ordering using INCLUDE (SQL Server, PostgreSQL 11+); in Oracle and MySQL they are added as trailing key columns.
-- Query: SELECT customer_id, status, total FROM orders
-- WHERE customer_id = ? AND ordered_at >= ?
CREATE INDEX ix_orders_cust_date
ON orders (customer_id, ordered_at)
INCLUDE (status, total); -- covering: no table accessEvery index costs write amplification and storage, so the goal is a small number of indexes that each cover several hot queries. In PostgreSQL, index-only scans also depend on the visibility map being current, which is why vacuum matters for read performance.
Apply. For an order service, the two hottest queries are “orders for a customer, newest first” and “open orders by status for the warehouse”. The first gets a composite B-tree on (customer_id, ordered_at DESC) INCLUDE (status, total) so the list page is an index-only scan with no sort. The second uses a partial index ON orders (status, ordered_at) WHERE status IN ('NEW','PICKING'), which stays tiny because 98 % of rows are closed. Product search over name uses a trigram GIN index in PostgreSQL rather than a B-tree because queries contain leading wildcards. Indexes are created CONCURRENTLY (PostgreSQL) or ONLINE (Oracle, SQL Server Enterprise) in migrations so deploys do not lock the table.
Test. CI runs EXPLAIN for each tagged query against a staging copy with production-like statistics and fails if the plan contains Seq Scan on orders or a Heap Fetches count above a threshold. Load tests compare p95 latency before and after the index, and a write-throughput benchmark confirms inserts are still within budget after adding it.
Troubleshoot. A query stopped using its index after the team changed customer_id from INT to BIGINT in the application while the bind parameter stayed INT in one driver; the implicit cast disabled the index in SQL Server. Typed parameters fixed it. On PostgreSQL, “Index Only Scan” showed Heap Fetches: 1200000 because a bulk update had made the visibility map stale; a VACUUM restored true index-only behaviour. Unused indexes are found with pg_stat_user_indexes or sys.dm_db_index_usage_stats and dropped to reclaim write performance; duplicated leading columns across indexes are the usual culprit.
An important consideration: a covering index that includes wide columns becomes almost as large as the table, so covering is reserved for a handful of latency-critical queries.
An execution plan is the tree of physical operators the optimizer chose to run a statement: how each table is accessed (sequential/full scan, index seek, index-only scan), how tables are joined (nested loops, hash join, merge join), and where sorting, aggregation and filtering happen. Reading it is how an engineer finds out why a query is slow rather than guessing.
Two kinds matter:
EXPLAIN, “Display Estimated Plan”, EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY).EXPLAIN (ANALYZE, BUFFERS), “Include Actual Execution Plan”, DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST, MySQL 8 EXPLAIN ANALYZE).The flow from text to operators looks like this.

What to look for when reading one:
loops or “executions” on inner nodes, which multiply the per-iteration cost.Plans are cached by statement text in SQL Server and Oracle, so a plan built for an unrepresentative first parameter value (parameter sniffing / bind peeking) can be reused badly; adaptive features in recent versions mitigate this.
Apply. In a payments reporting service every query that runs more than once a minute has its plan reviewed during code review: the pull request includes the EXPLAIN (ANALYZE, BUFFERS) output from a staging database with production-scale data. Slow-query logging (log_min_duration_statement, Query Store in SQL Server, AWR/ASH in Oracle) captures regressions after deploy. Developers use pg_stat_statements or Query Store to rank statements by total time rather than by single-execution latency, since a 20 ms query run 50 000 times a minute matters more than a 3 s nightly job.
Test. A plan-regression test suite stores the expected shape (operator types, index names) for a dozen critical queries and compares it after each migration; it tolerates cost changes but fails on a new sequential scan or a changed join algorithm. Load tests run with realistic parameter distributions, not a single customer id, to expose parameter-sensitive plans.
Troubleshoot. A query that was fast all month became slow on the 1st: the estimated rows for WHERE invoice_month = :m were 0 for the new month because statistics had not been refreshed, so the planner chose nested loops and ran 8 million inner probes. ANALYZE on the table (or auto-stats threshold tuning) fixed it. In SQL Server, an endpoint was slow only for a few large tenants; Query Store showed a plan compiled for a small tenant being reused, and OPTION (RECOMPILE) on that one statement, later replaced by a tenant-size-aware query split, removed the problem. Oracle plan flips after upgrades were pinned with SQL Plan Baselines until the root cause (a dropped histogram) was fixed.
An important consideration: comparing plans by estimated cost between different statements is meaningless; only actual time, rows and buffer reads should drive decisions.
ACID names the four guarantees a relational database makes about a transaction, a unit of work delimited by BEGIN and COMMIT or ROLLBACK:
ROLLBACK reverses partial changes.COMMIT returns, the change survives a crash. Achieved by write-ahead logging flushed to stable storage (fsync) before acknowledging the commit; replication adds durability across machines.BEGIN;
UPDATE accounts SET balance = balance - 250 WHERE id = 1;
UPDATE accounts SET balance = balance + 250 WHERE id = 2;
-- a CHECK (balance >= 0) constraint fails here if id 1 is overdrawn
COMMIT; -- both rows change, or the failed statement rolls everything backThe commit path shows where durability is secured before the client is told the transaction succeeded.

Dialect notes: Oracle and PostgreSQL run every statement in an implicit transaction; SQL Server does too by default but offers SET IMPLICIT_TRANSACTIONS ON; MySQL’s autocommit setting controls it and only InnoDB tables are transactional. DDL is transactional in PostgreSQL and SQL Server but commits implicitly in Oracle and MySQL. Durability is tunable: synchronous_commit = off in PostgreSQL or delayed durability in SQL Server trades a few milliseconds of possible loss for throughput.
Apply. In an order service, placing an order inserts the order, inserts its items, decrements stock and writes an outbox event, all in one transaction so a partial order can never exist and the event is published only if the order committed (transactional outbox pattern). Transactions are kept short: no HTTP calls or user waits inside BEGIN ... COMMIT, and the stock update runs last to minimise the time its row lock is held. Retries wrap the whole unit of work because a serialization failure or deadlock error (40001, 40P01, SQL Server 1205) aborts the transaction.
Test. Integration tests run against a real database (Testcontainers), not a mock, and inject a failure between the item insert and the stock update to assert that no order row survives. A durability test kills the database process immediately after a committed transaction and verifies the row exists on restart; this is where synchronous_commit = off would be caught. Concurrency tests run two threads buying the last unit and assert exactly one succeeds.
Troubleshoot. Symptom: stock went negative under load. The code read stock, checked it in Java, then updated; two transactions both read 1. The fix was a single UPDATE ... SET qty = qty - 1 WHERE id = ? AND qty >= 1 and checking the affected-row count, or SELECT ... FOR UPDATE. A second incident: orders appeared without events because the outbox write happened in a separate connection with its own transaction; routing both through the same connection resolved it. Long-running “idle in transaction” sessions (visible in pg_stat_activity) held locks for minutes because an ORM opened a transaction and the request stalled on an external API; a statement timeout and moving the call outside the transaction fixed it.
An important consideration: ACID guarantees apply within one database; spanning two services requires sagas or outbox patterns, not distributed transactions, in most modern systems.
An isolation level defines which effects of concurrent transactions a transaction may observe. The SQL standard defines four levels in terms of three anomalies: a dirty read (seeing uncommitted data), a non-repeatable read (re-reading a row and getting a different value because another transaction committed) and a phantom read (re-running a range query and seeing new rows).
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Prevented | Possible | Possible |
| REPEATABLE READ | Prevented | Prevented | Possible (standard) |
| SERIALIZABLE | Prevented | Prevented | Prevented |
Behaviour varies by engine more than the table suggests:
40001. Read Uncommitted behaves as Read Committed.READ_COMMITTED_SNAPSHOT switches it to row versioning; SNAPSHOT isolation adds a fourth non-standard level; Serializable uses range locks.Higher levels cost either more blocking (lock-based engines) or more aborted transactions (MVCC engines). Levels are set per session or per transaction, for example SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. Write skew, where two transactions each read a condition and both write based on it, is the anomaly snapshot isolation does not prevent and the reason true Serializable exists.
Apply. A hotel booking service runs at Read Committed for the general workload and promotes the “book room” transaction to Serializable in PostgreSQL, with an application-level retry on 40001, because two guests must not both get the last room on the same night (a write-skew case Repeatable Read would allow). Monthly financial reports run under Repeatable Read so every query in the report sees the same snapshot and totals reconcile. In SQL Server, READ_COMMITTED_SNAPSHOT ON is enabled database-wide so readers stop blocking writers on the busy orders tables.
Test. Concurrency tests open two connections and script an interleaving: T1 reads availability, T2 reads availability, both insert a booking, both commit. At Read Committed the test expects two bookings (documenting the hazard), at Serializable it expects one commit and one 40001. The retry wrapper is tested to give up after N attempts with a clear error. The report’s snapshot behaviour is tested by inserting a row from a second connection mid-report and asserting the totals ignore it.
Troubleshoot. A dashboard showed a customer count that disagreed with the detail page by one; the two queries ran in separate Read Committed statements and a signup landed between them. Wrapping the report in one Repeatable Read transaction fixed it. After enabling Serializable, the error rate spiked at peak; the long transactions were widened by an unrelated SELECT on a hot table, inflating predicate locks. Narrowing the transaction and adding an index to reduce the lock footprint brought aborts under 0.1 %. In MySQL, “lock wait timeout exceeded” on inserts traced back to gap locks under Repeatable Read; switching that session to Read Committed removed the gap locks.
An important consideration: isolation levels protect against anomalies in the database, not in application caches, so a Serializable transaction that writes a cached value back still races.
Locks are how the engine serialises conflicting access: a row or page being modified holds an exclusive lock until the transaction ends, so other writers wait, and in lock-based readers (SQL Server default, DB2) readers hold shared locks too. MVCC engines (PostgreSQL, Oracle, InnoDB, SQL Server with snapshot isolation) let readers see an older version instead of waiting, so locking conflicts are mostly writer-versus-writer. Explicit locks are requested with SELECT ... FOR UPDATE (WITH (UPDLOCK) in T-SQL), FOR UPDATE SKIP LOCKED / NOWAIT, and table-level LOCK TABLE.
A deadlock occurs when two transactions each hold a lock the other needs, forming a cycle neither can leave on its own. The diagram shows the classic case.

The engine detects the cycle (immediately in SQL Server and InnoDB, after deadlock_timeout, default 1 s, in PostgreSQL) and kills one participant, the victim, with an error: PostgreSQL 40P01, SQL Server 1205, Oracle ORA-00060, MySQL 1213. The surviving transaction proceeds. Prevention is mostly ordering: always update rows in the same order (sort ids before a batch update), keep transactions short, index foreign keys so child updates do not escalate to scans, and avoid user interaction inside a transaction. Lock escalation (SQL Server converting thousands of row locks into a table lock) and foreign-key checks taking shared locks on parent rows are common hidden sources.
Apply. A job-queue table is consumed by several workers with SELECT ... FROM jobs WHERE status = 'READY' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED, so workers never block each other and never process the same row. The inventory service updates stock rows in ascending product_id order within every transaction, including bulk restocks, which removes the AB/BA pattern by construction. Pessimistic locking (FOR UPDATE) is used only on the few hot rows such as account balances; everything else relies on optimistic concurrency with a version column and UPDATE ... WHERE version = :expected. Every transaction has a lock_timeout so a stuck lock produces a fast error instead of a pile-up.
Test. A dedicated test reproduces the deadlock: two threads update rows (1, 2) and (2, 1) with a barrier between the statements, and asserts that exactly one receives the deadlock error and the retry wrapper succeeds on the second attempt. The ordered-update helper is unit-tested to sort ids. Load tests track lock-wait time as a first-class metric (pg_stat_activity wait events, sys.dm_os_wait_stats).
Troubleshoot. Production deadlocks between the order update and a nightly archive job were diagnosed from the PostgreSQL log (log_lock_waits = on) and the deadlock graph in SQL Server’s Extended Events, which named both statements and the resources. The archive deleted children before parents while the application inserted parents then children; aligning the order fixed it. A different incident showed hundreds of sessions waiting on one UPDATE: a missing index on order_items.order_id made the foreign-key cascade take a full-table lock scan on every parent delete. Adding the index cut the lock hold time from seconds to microseconds.
An important consideration: deadlocks cannot be eliminated entirely in a busy system, so every write path needs an idempotent retry, and a deadlock rate above a few per hour should be treated as a design bug.
Normalization is the process of structuring tables so each fact is stored exactly once, which eliminates update, insert and delete anomalies. The normal forms stack up:
phone1, phone2).zip_code, not also city derived from it).Denormalization deliberately reintroduces redundancy, such as storing customer_name on orders, keeping a comment_count on posts, or a star-schema fact table with wide dimension tables, to avoid joins and aggregation at read time.
| Aspect | Normalized (3NF) | Denormalized |
|---|---|---|
| Write cost | Low: one place to update | Higher: redundant copies must sync |
| Read cost | Joins required | Few joins, pre-computed values |
| Integrity | Enforced by schema | Enforced by code, triggers or jobs |
| Fits | OLTP systems of record | Reporting, read-heavy APIs, caches |
The operational design rule is to normalize the transactional model first and denormalize selectively where measurements show join cost, using mechanisms that keep the copy honest: triggers, materialized views, generated columns, or an event-driven projection with a reconciliation job. A denormalized value whose source changes (product price on an order line) is not redundancy at all but a correct historical snapshot, and belongs in the model on purpose.
Apply. The order service’s write model is 3NF: customers, addresses, orders, order_items, products. Two deliberate denormalizations exist. order_items copies unit_price and product_name at purchase time because invoices must not change when the catalogue does. customers.lifetime_value is a maintained aggregate updated by the same transaction that inserts an order, because the account page shows it on every load and summing a million order rows per request is not acceptable. For analytics, a nightly ETL loads a star schema (fact_order_line with dim_customer, dim_product, dim_date) where redundancy is the point.
Test. Schema tests assert the normalized invariants: a UNIQUE on the natural key of every reference table and a foreign key on every _id column, checked by querying information_schema. For each denormalized column there is a reconciliation query, for example SUM(orders.total) grouped by customer compared with customers.lifetime_value, run as a nightly job that alerts on any mismatch and as an integration test after the order-placement code path.
Troubleshoot. Reconciliation flagged 312 customers whose lifetime_value drifted; the cause was a refund path that updated orders.total through a separate admin tool without touching the aggregate. The fix moved the aggregate update into a trigger so no code path can bypass it, with a one-off repair script. A reporting query joining seven normalized tables took 40 s; rather than denormalizing the OLTP schema, a materialized view was introduced and refreshed every 10 minutes. A legacy table with tags VARCHAR holding comma-separated values (1NF violation) caused LIKE '%, 5,%' bugs; it was migrated to a post_tags junction table with a backfill script.
An important consideration: every denormalized column needs a named owner process that keeps it correct and a query that proves it, otherwise it silently becomes wrong.
All three remove data, but at different granularities and with different transactional behaviour.
| Aspect | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Category | DML, row by row | DDL, deallocates pages | DDL, removes object |
| WHERE clause | Yes | No: all rows | Not applicable |
| Triggers | Fires row/statement triggers | No DML triggers | No |
| Logging / speed | Logs each row; slow on big tables | Minimal logging; fast | Fast |
| Rollback | Yes | Oracle/MySQL: no; PG/SQL Server: yes in txn | Same as TRUNCATE |
DELETE FROM sessions WHERE expires_at < NOW(); -- selective, logged
TRUNCATE TABLE staging_imports; -- empty the table, keep it
DROP TABLE staging_imports_2019; -- remove table and indexesPoints interviewers push on:
DELETE without WHERE and TRUNCATE both leave an empty table, but TRUNCATE is far faster because it releases the storage instead of marking every row deleted, and it does not bloat the undo/transaction log.TRUNCATE is refused when a foreign key from another table references the target (PostgreSQL allows TRUNCATE ... CASCADE); DELETE respects the foreign keys row by row.TRUNCATE and DROP commit implicitly, so they also commit any preceding DML in the session. In PostgreSQL and SQL Server they are transactional and can be rolled back.DROP removes the table definition, indexes, constraints and privileges; dependent views and foreign keys block it or go with CASCADE. Oracle keeps a dropped table in the recycle bin for FLASHBACK TABLE.DELETE is a table-level DML grant, whereas TRUNCATE and DROP require ownership or ALTER / DROP privileges.Deleting most of a large table is usually done in batches (DELETE ... LIMIT 10000 in a loop, or DELETE TOP (n) in T-SQL) or by partition drop, never as one giant statement.
Apply. In a data pipeline, each nightly load TRUNCATEs the staging tables before COPY / BULK INSERT, because the tables are fully replaced and the operation must not fill the transaction log. Expired sessions are purged with a batched DELETE ... WHERE expires_at < NOW() of 5 000 rows per iteration with a short sleep, so the purge never holds long locks against the login path. Old monthly partitions of the events table are removed with ALTER TABLE ... DROP PARTITION, which is a metadata operation, instead of deleting 200 million rows. Soft deletes (deleted_at column) replace hard DELETE on customer-facing entities that must be recoverable or audited.
Test. Migration tests run DROP TABLE scripts against a copy of the schema and assert that dependent views and foreign keys are listed and handled, since a blocked DROP in production halts a deploy. The purge job has a test with 20 000 expired rows asserting all are gone, the loop terminated, and no statement exceeded the lock timeout. For TRUNCATE, a test confirms identity values restart (or do not) as the downstream importer expects, because SQL Server and PostgreSQL differ by default.
Troubleshoot. A one-shot DELETE FROM audit_log WHERE created_at < '2023-01-01' of 400 million rows ran for six hours, filled the transaction log and blocked inserts; it was cancelled, rolled back for another hour, then replaced by batched deletes and a long-term switch to monthly partitions. A TRUNCATE in an Oracle deployment script silently committed the preceding half-finished data migration; the script was restructured so DDL ran in its own step. TRUNCATE failing with “cannot truncate a table referenced in a foreign key constraint” was resolved by truncating child and parent together in one statement.
An important consideration: DROP and TRUNCATE cannot be undone in Oracle or MySQL without backups or flashback, so production scripts run them behind an explicit confirmation step and a verified backup.
Both are named, server-side blocks of SQL (and procedural code: PL/SQL, T-SQL, PL/pgSQL) stored in the database, but they serve different roles and the engine imposes different rules.
| Aspect | Stored procedure | Function |
|---|---|---|
| Invocation | CALL / EXEC; standalone statement | Inside expressions: SELECT f(x), WHERE f(x) > 0 |
| Return | Optional status; OUT parameters; result sets | Exactly one value or one table (table-valued) |
| Side effects | May modify data, manage transactions | Usually read-only; DML restricted or forbidden |
| Transaction control | Can COMMIT / ROLLBACK (PG 11+, Oracle, T-SQL) | Cannot |
| Typical use | Batch jobs, multi-step business operations | Reusable calculations, computed columns, filters |
-- PostgreSQL: function usable in a query
CREATE FUNCTION order_total(p_order_id INT) RETURNS NUMERIC AS $$
SELECT COALESCE(SUM(quantity * unit_price), 0)
FROM order_items WHERE order_id = p_order_id;
$$ LANGUAGE sql STABLE;
-- Procedure performing a multi-step change
CREATE PROCEDURE close_order(p_order_id INT) AS $$
BEGIN
UPDATE orders SET status = 'CLOSED' WHERE order_id = p_order_id;
INSERT INTO order_events(order_id, kind) VALUES (p_order_id, 'CLOSED');
END; $$ LANGUAGE plpgsql;Dialect differences: SQL Server functions cannot execute DML or non-deterministic calls, and scalar UDFs in a WHERE clause run row by row (inlined only from SQL Server 2019 for eligible functions). Oracle functions can be called from SQL only if they do not write. PostgreSQL functions may perform DML and are the historic workhorse; procedures arrived in version 11 specifically to allow transaction control. MySQL functions must be declared DETERMINISTIC or NO SQL / READS SQL DATA when binary logging is on. Volatility labels (IMMUTABLE, STABLE, VOLATILE) and SCHEMABINDING tell the optimizer how aggressively it may cache or inline a function.
Apply. A banking back office keeps the end-of-day interest posting as a stored procedure because it touches four tables, needs intermediate commits every 10 000 accounts, and is scheduled from the database job runner rather than an application. Pure business calculations such as business_days_between(d1, d2) and tax_rate(country, product_type) are functions, declared IMMUTABLE / STABLE so they can appear in indexes, generated columns and WHERE clauses. Application services otherwise use plain parameterised SQL through the ORM, keeping procedural logic in the database only where it saves large data round trips.
Test. Database code is versioned in migrations and tested with a database unit-testing framework (pgTAP, tSQLt, utPLSQL): each function has table-driven tests with edge inputs (NULL, leap days, zero quantities), and the procedure test seeds accounts, runs it, and asserts the ledger balances and event rows. Tests run inside a transaction that is rolled back, except for procedures that commit internally, which get a disposable schema instead. Performance tests check that a function used in a predicate does not prevent index use.
Troubleshoot. A report using WHERE tax_rate(country, type) > 0.2 took minutes on SQL Server; the scalar UDF executed once per row and blocked parallelism, as the plan’s “UDF” warning showed. Rewriting it as an inline table-valued function restored a set-based plan. In PostgreSQL a function marked IMMUTABLE read a configuration table, so a cached result went stale after a config change; relabelling it STABLE fixed the staleness. A procedure that “half ran” turned out to swallow exceptions in a WHEN OTHERS THEN NULL handler; replacing it with logging and re-raise exposed the real constraint violation.
An important consideration: logic hidden in procedures is invisible to application code reviews and profilers unless the team treats database code as first-class code with tests and version control.
A view is a stored query evaluated on every access; a materialized view stores the query’s result as a physical table and serves reads from that copy, trading freshness for speed. Both present a table-like interface to applications.
| Aspect | View | Materialized view |
|---|---|---|
| Storage | None; query rewritten at run time | Rows and optional indexes stored on disk |
| Freshness | Always current | As of last refresh |
| Read cost | Cost of the underlying query | Cost of reading a table |
| Refresh | Not applicable | Manual, scheduled, on commit, or incremental |
| Support | Universal | Oracle, PostgreSQL, SQL Server (indexed views); not MySQL |
CREATE MATERIALIZED VIEW daily_sales AS
SELECT sale_date, region, SUM(amount) AS revenue, COUNT(*) AS orders
FROM sales
GROUP BY sale_date, region;
CREATE UNIQUE INDEX ux_daily_sales ON daily_sales (sale_date, region);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales; -- PostgreSQLThe diagram contrasts the two read paths.

Refresh mechanics are where engines diverge. PostgreSQL offers only full refresh (CONCURRENTLY avoids blocking readers but needs a unique index); incremental refresh is not built in. Oracle supports REFRESH FAST ON COMMIT using materialized view logs and query rewrite, where the optimizer transparently answers a query against base tables from the materialized view. SQL Server’s indexed views are maintained synchronously on every write and are used automatically by the optimizer in Enterprise edition (or with NOEXPAND). MySQL has no materialized views; teams emulate them with summary tables maintained by triggers or jobs.
Regular views also carry options worth knowing: WITH CHECK OPTION for updatable views, SCHEMABINDING (SQL Server) to prevent base-table changes that would break the view, and security barrier views for row-level filtering. Materialized views are the standard tool for dashboards, aggregations over large fact tables, and pre-joined read models; they are the wrong tool when the data must reflect the last second.
Apply. An analytics API exposes vw_customer_orders, a plain view joining six tables, so three teams query a stable contract while the underlying schema evolves; column renames are absorbed in the view definition. The “sales by region per day” dashboard reads a materialized view refreshed every 15 minutes by a scheduler, with a unique index so PostgreSQL’s REFRESH ... CONCURRENTLY keeps the dashboard available during refresh. Row-level security for tenants is implemented through views that filter on tenant_id = current_setting('app.tenant'), with grants on the views only.
Test. View definitions live in migrations and have contract tests: column names and types are compared against a stored schema snapshot so a change that breaks consumers fails CI. The materialized view has a reconciliation test comparing its totals with a direct aggregate over the base table immediately after refresh, and a staleness monitor that alerts if the last successful refresh is older than 30 minutes. A performance test asserts the dashboard query reads from the materialized view (the plan names it) rather than the base tables.
Troubleshoot. Users reported yesterday’s totals missing at 09:00; the refresh job had failed overnight on a disk-full error and nobody was alerted, which is what led to the staleness monitor. A REFRESH MATERIALIZED VIEW without CONCURRENTLY took an exclusive lock for four minutes and timed out API requests; adding the unique index and CONCURRENTLY fixed it. A nested view (view over view over view) produced a 90-second plan because the optimizer could not push the tenant predicate through a DISTINCT in the middle layer; flattening the view stack restored a sub-second index seek. In SQL Server, an indexed view blocked a bulk load because every insert had to maintain it; the view was dropped before the load and recreated after.
An important consideration: a materialized view is a cache, and every cache needs an owner, a refresh SLA and an alert when it is stale.
Pagination returns a result set one page at a time. The two mainstream techniques are offset pagination and keyset (seek) pagination.
Offset pagination uses ORDER BY ... LIMIT n OFFSET m (OFFSET ... FETCH NEXT in SQL Server 2012+ and Oracle 12c+, ROWNUM tricks in older Oracle). It is simple and supports jumping to page 500, but the engine must produce and discard the first m rows, so cost grows linearly with page depth, and rows inserted or deleted between requests cause items to be skipped or repeated.
Keyset pagination remembers the sort-key values of the last row of the current page and asks for rows after it, so every page is an index range scan of constant cost and insertions do not shift results.
-- Page 1
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Next page: client sends last_created_at and last_id from the previous page
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id) -- row-value compare
ORDER BY created_at DESC, id DESC
LIMIT 20;The cursor round trip between client and database looks like this.

Requirements for keyset: the ORDER BY must be deterministic, so a unique column is always the final key; an index on exactly those columns in that order makes each page a seek; the API returns an opaque cursor (an encoded tuple) rather than a page number. Row-value comparison is supported in PostgreSQL, MySQL and SQLite; SQL Server and Oracle need the expanded form created_at < :a OR (created_at = :a AND id < :b). Limitations: no direct jump to an arbitrary page, and backward paging requires reversing the comparison and the sort then re-reversing in the application. Offset remains acceptable for small admin tables and for UIs that genuinely need numbered pages; infinite scroll and API cursors should use keyset.
Apply. The public orders API of an e-commerce platform returns next_cursor, a base64 tuple of (created_at, id) from the last row; the backend decodes it, validates both parts, and runs a keyset query with a covering index on (customer_id, created_at DESC, id DESC). The internal admin UI, where staff want “page 7 of 12” on a few thousand rows, keeps offset pagination with a hard cap of 1 000 on the offset. Feed endpoints also cap page size at 100 to prevent a client from requesting a million rows.
Test. A pagination test inserts 250 rows, including a cluster of 30 rows sharing the same created_at, walks the cursor until it is null, and asserts that every id appears exactly once; this is the test that fails when the tie-breaker is missing. A second test inserts new rows while paging and asserts no duplicates or skips across the boundary. Cursor tampering tests send malformed and out-of-order values and expect a 400 response rather than a database error. A performance test measures page 1 and page 5 000 and requires near-identical latency.
Troubleshoot. Page 1 000 of a search endpoint took 12 seconds while page 1 took 15 ms; EXPLAIN showed LIMIT 20 OFFSET 20000 reading 20 020 rows from a sort. Migrating to keyset fixed the depth cost. A mobile app showed duplicated items during fast scrolling because the sort key was updated_at alone and items with equal timestamps straddled the page boundary; adding id to both the ORDER BY and the cursor removed it. On SQL Server, the row-value comparison from the PostgreSQL version failed to parse and was rewritten as the OR form, with a test added to confirm the index was still seeked, since the OR version sometimes degrades to a scan unless written as two unioned range predicates.
An important consideration: a cursor must encode exactly the ORDER BY columns, so changing the sort order of an endpoint invalidates cursors in flight and needs versioning.
SQL injection is an attack in which user-controlled input is concatenated into a SQL string so that the input is interpreted as SQL syntax rather than as data. A login query built as "... WHERE email = '" + email + "'" with the input ' OR 1=1 -- becomes a query that matches every row; variants read other tables through UNION SELECT, modify data, or exfiltrate through timing or error messages (blind injection).
Parameterized queries (prepared statements, bind variables) eliminate the problem by separating the statement from its values: the SQL text containing placeholders is sent to the database and compiled first, then the values are supplied through a separate channel and can only ever be treated as data.
query = f"SELECT * FROM users WHERE email = '{email}'"
cur.execute(query) # vulnerable: input joins the grammar
query = "SELECT * FROM users WHERE email = %s"
cur.execute(query, (email,)) # safe: value sent as a bound parameterPlaceholder syntax varies by driver (?, %s, $1, :name, @name) but the mechanism is the same, and ORMs and query builders use it underneath. Secondary benefits: the plan cache in Oracle and SQL Server is reused across calls instead of compiling one plan per literal, and type conversion is handled by the driver.
What parameters cannot do: identifiers (table or column names) and keywords such as ASC / DESC cannot be bound, so dynamic ordering must be validated against an allow-list; IN (...) lists need one placeholder per element or an array parameter; LIKE patterns still need % and _ escaped if the user should not control wildcards; and stored procedures that build SQL strings internally (EXECUTE IMMEDIATE, sp_executesql without parameters) reintroduce the hole. Defence in depth adds least-privilege database accounts, no raw error messages to clients, and input validation, but parameterization is the control that actually prevents injection.
Apply. In a customer-portal backend every data access goes through the ORM or a thin repository layer that only accepts parameterised statements; raw string formatting into SQL is banned by a lint rule (Semgrep/CodeQL rule for string concatenation into execute). Dynamic sorting maps sort=created to a column name from a hard-coded dictionary. The application connects with a role that has SELECT/INSERT/UPDATE on its own schema and nothing else, so a successful injection cannot read pg_shadow or drop tables. Database errors are logged server-side and returned to the client as a generic 500.
Test. The CI pipeline runs static analysis on every pull request and a dynamic scan (OWASP ZAP or sqlmap in safe mode) against the staging environment weekly. Unit tests pass classic payloads (' OR '1'='1, '; DROP TABLE users; --, 1 UNION SELECT ...) into every string parameter of the repository layer and assert the query returns zero rows or a validation error, never an exception from the database. A test for the sort allow-list sends sort=id;-- and expects a 400.
Troubleshoot. Security monitoring flagged UNION SELECT strings in the access log hitting a legacy report endpoint. The endpoint built a WHERE clause with string formatting for an optional filter; the fix was a parameterised dynamic query where only the structure varies and every value is bound, plus a review of the audit log for data exposure. A second finding: a PL/SQL procedure used EXECUTE IMMEDIATE 'SELECT ... WHERE name = ''' || p_name || ''''; it was rewritten with USING p_name. A false alarm occurred when the WAF blocked legitimate users whose surname was “O’Brien”, which confirmed that the WAF was a secondary control and the parameterised code already handled the apostrophe correctly.
An important consideration: parameterization must be total; a single concatenated string anywhere in the path, including inside stored procedures, undoes the protection of every other query.
Query performance optimization is the practice of reducing the work a statement does: fewer rows read, fewer rows moved between operators, fewer sorts and lookups, and fewer round trips. It is driven by measurement of the execution plan (Q29) and by knowledge of how the optimizer uses indexes and statistics, not by rewriting SQL on instinct.
The levers, roughly in order of how often they matter:
WHERE created_at >= :d rather than WHERE DATE(created_at) = :d; avoid implicit casts and leading wildcards.EXISTS rather than COUNT(*) > 0, prefer UNION ALL, paginate with keyset.Diminishing returns follow: hints and plan guides to pin a plan, configuration such as work memory and parallelism, and finally hardware. Caching outside the database (application cache, read replicas) is a scaling strategy rather than query optimization and does not fix a query that is wrong.
-- Non-sargable: index on created_at cannot be used
WHERE YEAR(created_at) = 2025 AND MONTH(created_at) = 11
-- Sargable: index range scan
WHERE created_at >= DATE '2025-11-01' AND created_at < DATE '2025-12-01'Optimization decisions are validated by comparing actual plans and logical reads before and after, under production-like data volumes, because an index that speeds one query may slow writes and change plans elsewhere.
Apply. A reporting service on PostgreSQL has a performance budget per endpoint (p95 under 300 ms). During development each new query is run with EXPLAIN (ANALYZE, BUFFERS) against a staging database restored from an anonymised production snapshot, and the engineer records the plan in the pull request. pg_stat_statements ranks statements by total time in production, and the top ten are reviewed every sprint. Standard fixes follow: composite indexes matching predicate order, keyset pagination on list endpoints, a materialized view for the executive dashboard, and batching of ORM-generated N+1 patterns.
Test. A nightly performance suite replays the 30 most frequent queries with realistic parameters against the staging snapshot and fails if latency regresses by more than 20 % or if the plan gains a sequential scan on a large table. Load tests run at twice the expected peak before releases. Migrations that add indexes include a measurement of write throughput so the trade-off is explicit.
Troubleshoot. An endpoint degraded gradually over months; pg_stat_statements showed its mean time growing with table size, and the plan showed a Seq Scan with a filter on tenant_id followed by a sort: the index on (created_at) did not match the query’s WHERE tenant_id = ? ORDER BY created_at. A new index on (tenant_id, created_at) turned it into an ordered index scan with no sort. A separate incident showed sudden slowness without code changes; auto_explain logged a plan with estimated rows 1 versus actual 400 000 after a bulk import, and ANALYZE plus a lower autovacuum analyze threshold on that table fixed it. Application-side, a dashboard made 1 200 queries per page load (N+1); eager loading cut it to 4.
An important consideration: tuning a query that runs once a day while ignoring one that runs 10 000 times a minute is the most common misallocation of effort, so tuning starts from aggregate time, not the slowest single statement.
Add your resume and the job description in CrackItX's Settings. When the interviewer asks a SQL question, Claude suggests an answer using your real projects and the role's requirements, in real time.
See pricing →