All 75 SQL quiz questions below come with a short explanation, visible right on the page — no sign-in needed. They're grouped by beginner and intermediate levels and 19 subtopics, so you can revise one part at a time. Choose Try SQL quiz above to take a scored run and review the questions you got wrong.
What the SQL quiz covers
Index8
Aggregates7
JOIN7
NULL Semantics6
Constraints4
DML4
GROUP BY & HAVING4
Locking & MVCC4
Query Semantics4
Sorting & Pagination4
Query Planning3
Schema3
Set Operations3
Subquery & CTE3
Transactions & Isolation3
Window Functions3
Data Types2
Security2
Performance1
Beginner level — 20 SQL questions
Question 1·JOIN
How do INNER JOIN and LEFT JOIN differ?
A.INNER JOIN keeps only rows matching in both tables; LEFT JOIN keeps every row of the left table
B.LEFT JOIN ignores the ON condition and pairs every row with every row like a CROSS JOIN
C.INNER JOIN only works with two tables while LEFT JOIN works with many
D.LEFT JOIN is faster than INNER JOIN because it only has to read the left table
Answer: A
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table; if the right table has no matching row, the right table's columns get NULL. Choose LEFT JOIN when you need to keep records with no linked data, e.g. customers with no orders.
-- users: (id=1, status='active'), (id=2, status=NULL)SELECTcount(*) FROM users WHERE status =NULL;
A.Syntax error — writing = NULL is not allowed in SQL
B.2 — NULL matches every value so both rows are counted
C.0 — you must use IS NULL instead of = NULL
D.1 — the row with id=2 has status NULL so it matches the = NULL condition
Answer: C
The result is 0. NULL means "unknown / not present", so any comparison using = with NULL yields an unknown result — even for the row whose status is NULL. WHERE keeps only rows whose condition is true, so no rows are counted. To find NULL rows, use status IS NULL; for the opposite, use status IS NOT NULL.
In this query, at which stage do WHERE and HAVING filter data?
sql
SELECT department, count(*) AS cnt
FROM employees
WHERE salary >1000GROUPBY department
HAVINGcount(*) >=2;
A.WHERE and HAVING both run after SELECT so they can filter on aliased columns
B.The two clauses are equivalent, differing only in syntax — you could move count(*) >= 2 into WHERE with the same result
C.WHERE filters individual rows before grouping; HAVING filters the groups afterwards
D.WHERE filters after GROUP BY has finished grouping, while HAVING filters rows before grouping
Answer: C
WHERE filters individual rows before GROUP BY groups them — rows with salary <= 1000 are dropped and not counted. HAVING filters the groups after aggregation, so it is the only place you can put a condition containing an aggregate function like count(*) >= 2. The logical order is FROM → WHERE → GROUP BY → HAVING → SELECT. Thinking "WHERE filters after GROUP BY" is wrong.
Which of the following statements about primary keys, unique constraints and foreign keys are correct? (select all that apply)
A.A UNIQUE constraint automatically forbids NULL just like a primary key does
B.Foreign keys should be dropped from the design for code flexibility, since the application layer already validates the data
C.A primary key uniquely identifies each row, disallows NULL, and each table has exactly one primary key
D.A table can have several UNIQUE constraints on different columns
E.A foreign key guarantees that a value in the child table exists in the parent table, keeping linked data from being orphaned
Answer: C, D, E
Three distinct roles: the primary key is the row's identity — unique, NOT NULL, one PK per table. UNIQUE is a business rule against duplicates (email, code...) — a table can have several UNIQUE constraints, and UNIQUE does not itself forbid NULL. Foreign key is referential integrity — values in the child table must exist in the parent. Dropping FKs just to make the code more flexible is a bad idea when the data needs consistency.
After running this script, what value does the final SELECT return?
sql
-- accounts: (id=1, balance=100)BEGIN;
UPDATE accounts SET balance = balance -50WHERE id =1;
ROLLBACK;
SELECT balance FROM accounts WHERE id =1;
A.50 — the UPDATE executed successfully so the new value survives regardless of the ROLLBACK
B.50 — ROLLBACK only affects DELETE, an UPDATE cannot be undone
C.100 — ROLLBACK discards uncommitted changes in the transaction
D.Error — you cannot SELECT after a ROLLBACK because the transaction is dead, you must reopen the connection
Answer: C
The result is 100. A transaction groups commands into an atomic unit: COMMIT makes every change durable, ROLLBACK cancels everything as if it never ran — including an UPDATE that already executed successfully inside the block. After ROLLBACK, balance returns to its value before BEGIN. This is the Atomicity in ACID, the foundation for transfers, bookings, and any multi-step operation that must not stop halfway.
It returns 6 rows. CROSS JOIN builds a Cartesian product: each row of the left table is paired with every row of the right table, so the result count is 3 × 2 = 6. CROSS JOIN uses no ON clause; it needs no join condition because the result is every combination. It's appropriate when you need to generate all combinations (a size × color matrix); using it carelessly on two large tables produces an exploding row count.
How many rows does this query return, and what changes with UNION ALL?
sql
-- t1.city: 'Hanoi', 'Hue'-- t2.city: 'Hue', 'Danang'SELECT city FROM t1
UNIONSELECT city FROM t2;
A.Error — UNION requires both queries to read from the same table
B.1 row ('Hue') — UNION returns the intersection of the two results
C.4 rows — UNION merely concatenates the two results without removing duplicates; to deduplicate you need UNION DISTINCT ALL
D.3 rows ('Hanoi', 'Hue', 'Danang') — UNION dedupes; UNION ALL keeps all 4 rows
Answer: D
UNION returns 3 rows: 'Hanoi', 'Hue', 'Danang' — it deduplicates across the whole combined result ('Hue' appears in both tables but only once). UNION ALL returns all 4 rows because it skips the deduplication step. When you know the data has no duplicates, or duplicates are acceptable, use UNION ALL — it's faster because it avoids the sort/hash step used to compare for matches.
Which names does this query return, and in what order?
sql
-- products (name, price):-- ('A', 30), ('B', 10), ('C', 20), ('D', 40)SELECT name FROM products
ORDERBY price ASC
LIMIT 2OFFSET1;
A.'B' then 'C' — OFFSET 1 means start at row 1, that is the first row
B.Indeterminate — LIMIT and OFFSET run before ORDER BY so the two chosen rows are random
C.'C' then 'A' — sorted by price ascending gives B, C, A, D; OFFSET 1 skips the first row (B), LIMIT 2 takes the next two
D.'A' then 'C' — ORDER BY sorts descending by default so the order is D, A, C, B
Answer: C
It returns 'C' then 'A'. Processing order: ORDER BY price ASC sorts to B(10), C(20), A(30), D(40); OFFSET 1 skips the first row (B); LIMIT 2 takes the next two rows — C and A. Note: OFFSET is the number of rows skipped, not a starting position; and LIMIT/OFFSET without ORDER BY gives unstable results across runs.
COALESCE and NULLIF — what three values does this query return?
sql
SELECTCOALESCE(NULL, NULL, 'fallback') AS a,
NULLIF('same', 'same') AS b,
NULLIF(10, 20) AS c;
A.a = NULL, b = NULL, c = 10 — COALESCE returns NULL as soon as its first argument is NULL
B.a = 'fallback', b = 'same', c = NULL — NULLIF returns NULL when the two arguments differ
C.Syntax error — COALESCE accepts exactly two arguments
D.a = 'fallback', b = NULL, c = 10
Answer: D
Results: a = 'fallback', b = NULL, c = 10. COALESCE scans its arguments left to right and returns the first non-NULL value — the two leading NULLs are skipped. NULLIF(x, y) returns NULL if x equals y, otherwise x: 'same' = 'same' so b is NULL; 10 differs from 20 so c is 10. The two functions are opposites: COALESCE replaces NULL with a value, NULLIF turns a value into NULL.
A.The latter is faster because it reads only one column
B.The latter skips rows where that column is NULL
C.The former removes duplicate rows while the latter does not
D.The former only works on tables with a primary key
Answer: B
The form with a column name skips NULL rows in that column, while the star form counts every row. The two numbers differ by exactly the number of rows with a NULL value — the source of many reports that are off without anyone noticing.
B.The value that appears most often among the arguments
C.The first non-NULL argument, or NULL if they are all NULL
D.The last non-NULL argument in the list
Answer: C
The first non-NULL argument, scanning left to right. If all of them are NULL, it returns NULL. This is the standard way to set a default value for a column that may be empty.
A.Prepared statements with parameters, never string-concatenated queries
B.Allowing only queries pre-registered in an allow list
C.Filtering dangerous SQL keywords out of user input
D.Escaping single quotes in strings before concatenating them into the query
Answer: A
Use parameterised statements, not string concatenation. Parameters are sent separately from the statement, so user data is never parsed as syntax — no matter what it contains.
What ten rows does SELECT * FROM users LIMIT 10 return without ORDER BY?
A.The ten rows with the smallest primary key
B.The ten most recently updated rows
C.Undefined; it can differ between runs
D.The ten rows inserted earliest
Answer: C
Undefined — and it can differ between runs. Without an ordering clause the optimizer picks how to read, so the order depends on the execution plan and the state of the data at that moment.
No rows. In SQL, comparing to an unknown value yields unknown rather than true, and the filter clause keeps only rows that evaluate to true. You have to use the dedicated checking operator.
Why does SELECT city, name, COUNT(*) FROM users GROUP BY city error?
A.A count cannot be used alongside ordinary columns
B.The group clause must list every column in the table
C.The name column is neither grouped nor aggregated
D.The order of the columns in the select list is wrong
Answer: C
Because that column is neither in the group nor aggregated. Each group collapses many rows, so the column can hold several different values, and the database can't decide on its own which value is correct.
A.It matches a space in the corresponding position
B.It matches exactly one arbitrary character
C.It matches a literal underscore in the data
D.It marks where the matched portion begins
Answer: B
It matches exactly one arbitrary character, unlike the percent character which matches any number of characters including none. To search for this character literally in data, you must escape it.
Running UPDATE users SET status = "banned" without WHERE. Consequence?
A.Only the first row of the table is updated
B.The statement is rejected for lacking a required condition
C.The engine asks for confirmation before executing
D.Every row in the table is updated
Answer: D
Every row in the table gets updated. The syntax is perfectly valid and there is no confirmation, which makes this one of the most common data incidents when operating manually on production.
Inserting 1000 rows: one multi-row statement or 1000 separate ones?
A.One multi-row statement, many times faster
B.A thousand separate statements, since per-row errors are easier to handle
C.The two are equivalent since the same data is written
D.It depends on whether the table has indexes
Answer: A
One multi-row statement, usually many times faster. Each separate statement costs a network round trip and a parse of the statement, so for a thousand rows that overhead outweighs the actual data writes.
A.COUNT(1) is faster because it need not expand the star into a column list
B.Neither; the optimizer treats the two spellings identically
C.COUNT(*) is faster because the engine reads a row count stored in metadata
D.It depends on whether the table's first column allows NULL
Answer: B
No difference. COUNT(*) and COUNT(1) produce the same execution plan on every major DBMS — both count rows without reading values. The real difference is COUNT(cot): it skips rows where that column is NULL so it can return a smaller number.
-- customers: (id=1, name='An'), (id=2, name='Binh')-- orders: (id=10, customer_id=1, status='paid')SELECT c.name, o.status
FROM customers c
LEFTJOIN orders o ON o.customer_id = c.id
WHERE o.status ='paid';
A.Syntax error — a filter on the right table must live in the ON clause
B.Only 1 row ('An', 'paid')
C.0 rows — you cannot use WHERE on a right-table column after a LEFT JOIN, PostgreSQL discards the whole result
D.2 rows: ('An', 'paid') and ('Binh', NULL) — a LEFT JOIN always keeps every left-table row regardless of WHERE
Answer: B
It returns exactly 1 row ('An', 'paid'). Binh's row after the LEFT JOIN has o.status = NULL; the condition WHERE o.status = 'paid' compares with NULL, giving an unknown result, so the row is dropped — the LEFT JOIN effectively becomes an INNER JOIN. To keep Binh with status NULL, move the condition into the ON clause: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'.
-- customers.id: 1, 2, 3-- blocked.customer_id: 1, NULLSELECT id FROM customers
WHERE id NOTIN (SELECT customer_id FROM blocked);
A.Ids 2, 3 and even 1 — the NULL in the list disables the whole NOT IN so every row is kept
B.Ids 2 and 3 — they are not in the blocked list so they pass the NOT IN condition
C.No rows — NOT IN with a NULL in the list can never be true
D.Runtime error — a subquery inside NOT IN is not allowed to return NULL
Answer: C
It returns no rows. id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL; the id <> NULL part always yields unknown, so the whole condition is never true — even for id 2 and 3. This is the classic NOT IN trap. The safe way to exclude rows is NOT EXISTS, which is unaffected by NULLs in the data.
A.2, 2, 1 — count(*) also skips rows that have any NULL column
B.3, 2, 1 — each count variant counts a different set
C.3, 3, 2 — count(customer) counts the NULL row too, and DISTINCT treats NULL as its own value
D.3, 2, 2 — DISTINCT keeps NULL as a distinct value so it counts 2
Answer: B
Result: 3, 2, 1. count(*) counts rows regardless of NULL — 3. count(customer) counts only non-NULL values — 2. count(DISTINCT customer) counts distinct values and also ignores NULL — only 'An' remains, so 1. A common misconception is that count(column) counts NULLs; in fact every aggregate function except count(*) skips NULL.
Which of the following statements about indexes in a relational database are correct? (select all that apply)
A.Indexes are created automatically for every column that appears in the WHERE clause of frequently run queries
B.In a composite B-tree index, column order matters: an index on (a, b) serves queries filtering on a well, but is nearly useless when filtering on b alone
C.Indexes speed up reads but slow down writes, because every INSERT/UPDATE/DELETE must also maintain the index
D.A query using an index is always faster than a full table scan, so the more indexes a table has the faster reads get
E.The planner may ignore an index and scan the whole table if it estimates that to be cheaper, for example on a small table or when the condition returns most of the rows
Answer: B, C, E
Three correct points: (1) an index speeds up reads but makes writes slower and costs disk — every write must also update the index; (2) column order in a composite index matters — index (a, b) cannot serve a query that only filters on b; (3) the planner may skip the index and do a full table scan when that is cheaper. Misconceptions to avoid: "an index is always faster" and "the database creates indexes based on WHERE".
Question 5·Transactions & Isolation· select all that apply
Which statements about SQL standard isolation levels are correct? (select all that apply)
A.Read Committed prevents dirty reads, yet re-reading the same row can still return a different value
B.The default level is the same on every engine, so there is nothing to check when migrating
C.Repeatable Read prevents non-repeatable reads; the standard still permits phantom reads there
D.Higher levels cost more: longer lock waits, or a retry when the transaction is aborted
E.Raising the level to Serializable is enough to fix every concurrent-update bug
Answer: A, C, D
The SQL standard defines four levels by the phenomena they forbid: Read Uncommitted (allows dirty read), Read Committed (forbids dirty read), Repeatable Read (also forbids non-repeatable read), Serializable (also forbids phantom read). Defaults differ between systems, and a higher level always costs concurrency or requires retries.
What kind of subquery is used below, and what is its logical behavior?
sql
SELECT c.name FROM customers c
WHEREEXISTS (
SELECT1FROM orders o
WHERE o.customer_id = c.id
);
A.A non-correlated subquery — it runs independently once and the outer query reuses the result
B.A correlated subquery — it references c.id from the outer query
C.A scalar subquery — it must return exactly one SELECT 1 value, and several matching rows would cause a runtime error
D.Syntax error — a subquery inside EXISTS may not reference the outer query's table
Answer: B
This is a correlated subquery: it references the outer query's c.id column, so logically it must be evaluated once per row of customers. EXISTS returns true as soon as the first matching row is found (short-circuit), without reading everything. An uncorrelated subquery, by contrast, runs independently once. Optimizers usually rewrite a correlated subquery into a semi-join, so real-world performance is often fine.
A.Naming a subquery to split a complex query into readable steps
B.A CTE is always materialized into its own intermediate result, so it is always slower than writing a nested subquery
C.Creating a temporary table stored on disk that lives until the end of the session for later queries to reuse
D.A CTE is alternative syntax for a VIEW — each WITH automatically creates a view stored in the database schema
Answer: A
A CTE (the WITH clause) names a subquery so you can break a complex query into named, readable steps and reference the name multiple times in one statement. It exists only within the scope of a single statement — it is not a temp table or a view. On modern systems a CTE is typically inlined into the main query during optimization, so performance matches a subquery; WITH RECURSIVE handles hierarchical data.
B.2 rows: ('An', 'Eng'), ('Binh', NULL) — FULL OUTER JOIN keeps unmatched rows only from the first table
C.3 rows: ('An', 'Eng'), ('Binh', 'HR'), (NULL, NULL) — Binh's NULL dept_id is paired with the leftover department
D.1 row: ('An', 'Eng') — only rows matching in both tables are returned
Answer: A
It returns 3 rows: ('An', 'Eng') — the only matching pair; ('Binh', NULL) — Binh has dept_id NULL and matches no row because NULL can't be compared in ON; (NULL, 'HR') — the HR department has no employee pointing to it. FULL OUTER JOIN is the union of LEFT and RIGHT JOIN: it keeps unmatched rows from both sides, filling NULL on the missing side.
After the DELETE statement below, what is the state of the orders table?
sql
CREATE TABLE customers (id intPRIMARY KEY);
CREATE TABLE orders (
id intPRIMARY KEY,
customer_id intREFERENCES customers (id) ONDELETESETNULL
);
-- customers: (1)-- orders: (id=100, customer_id=1)DELETEFROM customers WHERE id =1;
A.The DELETE succeeds; orders keeps (100, 1) — the record becomes orphaned because an FK is only checked on INSERT
B.The DELETE succeeds and the row (100, 1) in orders is deleted along with it
C.The DELETE fails with a foreign key violation — a customer referenced by orders cannot be deleted
D.The DELETE succeeds; orders still holds the row (100, NULL) — ON DELETE SET NULL sets the referencing column to NULL when the parent row is deleted
Answer: D
The DELETE succeeds and orders keeps the row (100, NULL): ON DELETE SET NULL sets customer_id to NULL when the parent row is deleted — the order is kept but loses its link. For comparison: the default (NO ACTION/RESTRICT) blocks deleting the parent while children still reference it; CASCADE deletes the child rows in a chain. Choose behavior by business need: SET NULL when child data still has independent value, CASCADE when a child is meaningless without its parent.
Given the composite index below, which statements are correct? (select all that apply)
sql
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, created_at);
A.A query WHERE customer_id = 42 AND created_at >= '2026-01-01' uses this index efficiently — equality on the first column narrows the range, then the range on the second column scans contiguously
B.A query WHERE customer_id = 42 uses this index efficiently — the condition matches the leading column (leftmost prefix)
C.The index (customer_id, created_at) already covers an index on created_at alone, so a separate index on that column is never needed
D.A query WHERE created_at >= '2026-01-01' (no customer_id filter) also uses this index efficiently because created_at is part of it
E.A query WHERE customer_id = 42 ORDER BY created_at can take the rows in order straight from the index, skipping the sort step
Answer: A, B, E
The index (customer_id, created_at) sorts by customer_id first, then by created_at within each customer. It serves well: filtering by customer_id; filtering by customer_id combined with a created_at range; and ORDER BY created_at within a single customer (no sort needed). It does not help a query filtering only on created_at — those values are scattered across the tree (leftmost prefix rule) — and it can't replace a separate index on created_at.
Two transactions interleave as shown below. What happens?
sql
-- accounts: (id=1), (id=2)-- Transaction A: -- Transaction B:BEGIN; BEGIN;
UPDATE accounts SET balance =0UPDATE accounts SET balance =0WHERE id =1; WHERE id =2;
-- then: -- then:UPDATE accounts SET balance =0UPDATE accounts SET balance =0WHERE id =2; -- waits for B WHERE id = 1; -- waits for A
A.Nothing goes wrong — MVCC lets two transactions update the same row concurrently, and the later update wins
B.Deadlock — PostgreSQL aborts one transaction and lets the other proceed
C.Both transactions wait forever — you must restart the server or manually kill a connection to break it
D.PostgreSQL automatically merges the two transactions into one to avoid the conflict and commits them together
Answer: B
This is a classic deadlock: A holds the lock on row 1 and waits for row 2; B holds the lock on row 2 and waits for row 1 — a closed wait cycle where neither side can proceed. The DBMS detects it after a lock-wait period and aborts one transaction; the other side continues. Prevention: update rows in the same order everywhere in the code, e.g. always by ascending id.
Which statements about MVCC (multi-version concurrency control) are correct? (select all that apply)
A.Two transactions writing the same row must still queue on a lock
B.MVCC guarantees every SELECT sees the most recent state of the data
C.MVCC removes the need for locks in the engine entirely
D.A row exists in several versions; an old one is reclaimable only when no transaction still needs it
E.Each transaction reads from a snapshot, so reads do not block writes and writes do not block reads
Answer: A, D, E
MVCC keeps multiple versions per row: a writer creates a new version instead of overwriting, and a reader reads the version valid for its snapshot. That's why reads and writes don't block each other. The cost is that old versions pile up and need to be cleaned up — so a long-running transaction makes the table bloat.
What values does the diff column take (in ascending year order)?
sql
-- revenue (year, amount):-- (2024, 100), (2025, 150), (2026, 120)SELECTyear,
amount -LAG(amount) OVER (ORDERBYyear) AS diff
FROM revenue;
A.NULL, 50, -30
B.Error — LAG requires PARTITION BY in its OVER clause
C.100, 50, -30 — the first row has no predecessor so it keeps its own amount value
D.0, 50, -30 — for the first row LAG defaults to 0 so the arithmetic does not break
Answer: A
Result: NULL, 50, -30. LAG(amount) OVER (ORDER BY year) takes the amount of the preceding row in year order: 2024 has no preceding row, so LAG returns NULL and 100 - NULL = NULL; 2025 gives 150 - 100 = 50; 2026 gives 120 - 150 = -30. To make the first row output 0 instead of NULL, declare a default: LAG(amount, 1, 0). LEAD is the symmetric function — it looks at the following row.
A.It skips NULL rows in both the numerator and the denominator
B.It returns NULL if any row holds a NULL value
C.It treats NULL values as 0 when summing
D.It raises an error and requires NULLs to be handled first
Answer: A
It skips NULL rows in both the numerator and the denominator. The average of four values where two are NULL is the average of the remaining two. To treat NULL as 0 you must replace it explicitly first — and the two approaches give different results.
How does SELECT DISTINCT col differ from SELECT col ... GROUP BY col?
A.The former drops NULL while the latter keeps NULL as its own group
B.The former is faster because it does no grouping
C.They give the same result; the latter can additionally use aggregate functions
D.The latter preserves the original data order while the former does not
Answer: C
They produce the same result, and the optimizer usually generates the same plan. The difference is that the grouping form can also use aggregate functions — so when you need a count or sum per group, only it can do the job.
WHERE id NOT IN (SELECT parent_id FROM t) returns nothing despite data existing. Why?
A.Because the two columns have incompatible data types
B.Because the subquery runs first and cannot see newer data
C.Because the subquery returns too many rows and gets truncated
D.Because the subquery contains NULL, every comparison yields unknown
Answer: D
Because the subquery contains a NULL value. A comparison with NULL yields "unknown" rather than false, so the negated condition is never true and the result is empty — even when rows clearly do match.
How does EXISTS (SELECT 1 FROM ...) differ from IN (SELECT ...)?
A.The former cannot be used with a correlated subquery
B.The latter is faster because the optimizer can turn it into a join
C.The former only works with a subquery returning a single column
D.The former stops at the first matching row and handles NULL safely
Answer: D
EXISTS checks whether the subquery returns any matching row; IN checks whether a value belongs to the returned set of values. The difference over NULL is most notable in the negated form: NOT EXISTS is safer than NOT IN when the inner set contains NULL.
A.The smallest value of the column across the whole result set
B.The running total of the column up to the current row
C.The value of the column in the first row of the partition
D.The value of that column in the immediately preceding row per the specified order
Answer: D
The value of that column on the preceding row in the given order. This lets you compute the difference between two consecutive periods right within one query, instead of joining the table to itself offset by one row.
What does an upsert (insert if absent, update if present) rely on to decide a row "already exists"?
A.The insertion order of rows already in the table
B.A preceding SELECT that checks, after which the code decides to insert or update
C.A unique constraint or primary key on the columns that identify the row
D.A full table scan looking for a matching row before each write
Answer: C
It relies on a unique constraint or primary key over the set of columns identifying the row. The database detects the collision during the write and switches to an update, so the whole operation is atomic. Checking with a SELECT first and then writing leaves a gap between the two steps and breaks under concurrency.
What is the most important difference between DELETE FROM t and TRUNCATE t?
A.The latter cannot be rolled back inside a transaction
B.The latter does not scan row by row and does not fire per-row triggers
C.The latter only works on tables with no foreign key constraints
D.The former keeps the table structure while the latter drops the table too
Answer: B
The truncate form does not scan row by row but releases the data files outright, so it is far faster on large tables. In return it does not fire row-level triggers and cannot use a WHERE clause.
A.The built-in money type because it is purpose-built for this
B.numeric with an explicit precision, never float
C.float8 because it has the widest value range
D.text to sidestep all numeric precision problems
Answer: B
An exact decimal type with a specified precision. Floating-point types have representation errors, so monetary totals drift over repeated additions — unacceptable for financial data.
What is a partial index and when should it be used?
A.An index that applies only to one partition of a partitioned table
B.An index built incrementally over time instead of all at once
C.An index containing only some of the table's columns
D.An index containing only the rows satisfying a given condition
Answer: D
An index containing only rows that satisfy a condition. Use it when queries always carry that condition — e.g. finding only unprocessed orders in a table where 99% of orders are done. Such an index can be hundreds of times smaller.
WHERE LOWER(email) = $1 cannot use the index on email. What is the fix?
A.Add email to the ORDER BY clause as a hint to use the index
B.Move the condition into the group filter clause instead of the row filter
C.Create an index on the expression LOWER(email) itself
D.Cast the column to text before comparing
Answer: C
Create an index on that exact expression. A normal index stores the original value, so when the condition wraps the column in a function the DBMS must evaluate the function for every row — the index is useless. An expression index stores the precomputed result.
Does declaring a foreign key automatically create an index on the child column?
A.No index is needed because the constraint itself already provides fast lookups
B.It depends on the engine — some create one, some do not; check rather than assume
C.Never — constraints and indexes are entirely independent things
D.Always — a foreign key constraint mandates an accompanying index
Answer: B
It depends. MySQL/InnoDB requires an index on the foreign key column and creates one if missing; PostgreSQL and Oracle create nothing. A missing index on the child table is one of the most common causes of slowness — every delete or update of a parent key must scan the whole child table.
EXPLAIN shows a Seq Scan on an indexed table. Is that always a problem?
A.Yes; a sequential scan is always slower than an index scan
B.No; when the query returns most of the rows a sequential scan is cheaper
C.You cannot tell without running EXPLAIN ANALYZE
D.Yes; it means the index is corrupt and needs rebuilding
Answer: B
No. When the query fetches most of the table's rows, a sequential scan is cheaper because sequential reads are many times faster than random reads. Small tables are the same case — scanning a few pages costs less than walking the index levels.
Why does the optimizer need table statistics, and what happens when they go stale?
A.It estimates row counts from them to pick a plan; stale statistics lead to the wrong choice
B.Statistics decide the physical order in which rows are stored on disk
C.Statistics store results of frequent queries so the next run returns them immediately
D.Statistics only feed monitoring reports and never affect the execution plan
Answer: A
The optimizer picks a plan by estimating how many rows each step will return, and that estimate comes from data distribution statistics. When the data changes a lot and the statistics are not updated, the estimates drift — the optimizer picks the wrong join method or ignores an index, and a familiar query suddenly becomes slow.
A.Marking the rows that the next statement will modify
B.Locking the rows it reads so other transactions cannot modify them
C.Copying the rows into a temporary table before modifying them
D.Returning the newest version of the row regardless of isolation level
Answer: B
It locks the rows just read so other transactions cannot update or delete them until this transaction ends. It is a pessimistic lock — used when you need to read a value and write it back based on it, for example decrementing stock.
How does optimistic locking with a version column work?
A.A lock is taken on read and released once the write completes
B.The database increments the version column itself and rejects mismatched writes
C.The UPDATE carries the old version in its condition; 0 rows updated means a conflict
D.Each version of the row is stored as its own record
Answer: C
Read along with the version value, then issue the update with a condition that the version equals the value read, and increment it. If no row is updated, someone else wrote first — the application reports a conflict and retries.
What is the N+1 query problem in an ORM-based application?
A.The same query is sent repeatedly because of a missing cache
B.One query joins too many tables so the row count multiplies
C.One query fetches a list, then each element triggers one more query
D.The application opens more connections than the pool allows
Answer: C
One query fetches a list of n items, then the loop accessing each item's related data issues n more queries. The cost is not in reading but in hundreds of network round trips to the database.
What is the downside of a random UUID primary key versus an incrementing integer?
A.It does not guarantee uniqueness so an extra constraint is still needed
B.Random values scatter index writes, making them slower and more expensive
C.It cannot be used as a foreign key in other tables
D.It cannot be sorted so it cannot be used in ORDER BY
Answer: B
Random values scatter index writes across the tree instead of appending at the end, so inserts are slower and caching is less effective. It also takes 16 bytes instead of 8, making every index that contains it larger.
When should a schema be denormalised (deliberately duplicating data)?
A.When the number of tables in the schema exceeds a certain threshold
B.From the start, because joins are always the most expensive operation
C.Never; normalization is always the right choice
D.When you have measured it and joins are a real bottleneck, accepting the cost of keeping copies in sync
Answer: D
When you have measured that the join is truly the bottleneck, and you accept the cost of keeping things in sync. Duplicated data must be updated everywhere — miss one place and you get inconsistent data that is hard to detect and hard to fix.
What are the trade-offs of soft delete (a deleted_at column)?
A.It cannot be used with tables that are referenced by foreign keys
B.Deleted data cannot be restored
C.The table cannot be indexed because it contains invalid rows
D.Every query must remember to filter, and unique constraints are affected
Answer: D
Two main trade-offs: every query must remember to filter out rows marked as deleted, and unique constraints still count them — so you cannot reuse a value from a deleted record.
Why add CHECK constraints in the database when the application already validates?
A.Because the planner uses constraints to optimize queries
B.Because it completely replaces application-side validation
C.Because checking in the database is faster than checking in the application
D.Because data also arrives from scripts, migrations, and other applications
Answer: D
Because data also comes in through many other paths: manual fix scripts, migrations, background jobs, and other applications sharing the database. The database is the last layer that every write path must go through.
Which account should a web application use to connect to the database?
A.One shared account for every service to simplify management
B.The schema owner account so migrations can run at startup
C.A dedicated account with privileges only on the tables it needs
D.A superuser account to avoid permission errors at runtime
Answer: C
A dedicated account with privileges only on the tables it needs, and only the privileges it uses. If the application is exploited, the blast radius is limited to exactly what that account is allowed to do.
What does the FILTER (WHERE ...) clause after an aggregate do?
A.Removing groups that fail the condition from the result
B.Filtering rows before grouping, equivalent to the ordinary filter clause
C.Computing the aggregate only over the rows satisfying that condition
D.Sorting the rows before feeding them to the aggregate
Answer: C
It computes that aggregate only over the rows matching the condition. This lets one query compute several metrics with different conditions — counting successful and failed orders — in a single table scan.
Why can WHERE name LIKE '%abc%' not use a B-tree index?
A.Because the index is ordered by prefix, and the search pattern has no known prefix
B.Because the pattern-matching operator is not supported by indexes
C.Because the search pattern contains special characters that need escaping
D.Because a text column cannot have a B-tree index
Answer: A
Because the tree index is sorted by the prefix of the value. Knowing the start narrows the search range; a pattern starting with a wildcard gives no starting point, so it must scan everything.
Where does ORDER BY col (ascending) place NULL values?
A.Always first, because NULL means no value yet
B.It depends on the engine — the standard leaves it open, so state it explicitly when it matters
C.Always last, because NULL is treated as the largest value
D.They are dropped from the result because they cannot be compared
Answer: B
It depends on the DBMS: PostgreSQL and Oracle treat NULL as largest so they sort it last ascending, while MySQL and SQLite treat NULL as smallest so they sort it first. The SQL standard leaves this open. If the order matters to the UI, write NULLS FIRST / NULLS LAST explicitly instead of relying on the default.
A.Flushing the changes to disk before the transaction ends
B.Marking a point so you can roll back partially without aborting the whole transaction
C.Letting other transactions see the changes made up to that point
D.Splitting a long transaction into several smaller transactions
Answer: B
Marks a point to roll back partially without aborting the whole transaction — useful when a step may fail but earlier work is still worth keeping. Necessity varies by system: some systems let a failed statement corrupt the whole transaction, so there a savepoint is mandatory, not optional.
In EXPLAIN, what determines the choice of Nested Loop / Hash Join / Merge Join?
A.The join type written in the query
B.A global configuration parameter set by the administrator
C.The size of both sides, the indexes available, and whether the data is already sorted
D.The order in which the tables are written in the FROM clause
Answer: C
The planner estimates the cost of each algorithm and picks the cheapest, based on the sizes of both sides, available indexes, and whether the data is already sorted. Each algorithm has its own sweet spot.
What does SELECT * FROM a, b without a join condition produce?
A.A syntax error for lacking a required join condition
B.Every combination of the two tables' rows
C.The rows sharing a primary key in both tables
D.The first table's rows with empty columns from the second
Answer: B
Every row combination of the two tables — the row count equals the product of both row counts. With two tables of ten thousand rows each, the result is one hundred million rows and the query nearly hangs the server.
employees has a manager_id referencing itself. How to list employee and manager names?
A.Create a temporary table holding the list of managers first
B.Join the table with itself using two different aliases
C.Use a recursive query to walk up to the manager
D.Run two separate queries and combine the results in the application
Answer: B
Self-join the table with two different aliases — one alias for the employee, one for the manager. The aliases turn the same table into two separate data sources, so the join condition stays unambiguous.
Returning different labels depending on a column value in one query — what do you use?
A.A CASE WHEN conditional expression
B.A user-defined function in the database
C.Handling the label mapping in the application layer
D.Several separate queries combined with a union
Answer: A
Use a conditional expression. Being an expression, it can go in the select list, the filter clause, the order-by clause, and inside aggregate functions too — that last one is a handy way to do conditional counting.
Combining each order's product names into one comma-separated string — how?
A.Fetch every row and concatenate in the application
B.Use a loop in a stored procedure to build the string
C.Create a computed column storing the joined string
D.The string-aggregating function with a separator
Answer: D
Use a string aggregation function with a delimiter. It collapses many rows of a group into a single value right inside the database, so you don't have to ship every row to the application and concatenate there.
A.The same number of columns with compatible types
B.The same source table in both queries
C.The same column names in both queries
D.The same sort clause in both parts
Answer: A
The same number of columns with compatible data types. Combining relies on column position, not name, so different names are still fine — which is also a bug source when someone reorders the columns in one branch.
Indexing an is_active column with only two values — is it useful?
A.Useful because the index is small so it stays in memory
B.Rarely useful unless one value is very rare
C.Very useful, since every column should have an index
D.Never useful on a boolean column
Answer: B
Rarely useful unless one value is very rare. An index only pays off when it eliminates most rows; a predicate matching half the table is often faster with a sequential scan because sequential reads are cheaper than random index reads.
What does a foreign key with ON DELETE CASCADE do, and what is the risk?
A.It blocks deleting a parent row while child rows remain
B.It deletes the child rows too, which easily loses data unintentionally
C.It sets the child rows' foreign key to null
D.It moves the child rows to a default parent row
Answer: B
It deletes the child rows too when the parent row is deleted. The risk is that deletion propagates through several relationship levels, so a seemingly small delete can wipe out an entire branch of data with no warning.
You need the latest order per customer. Which approach runs on most modern engines?
A.Sort the whole table by timestamp descending and limit to one row
B.Number the orders within each customer using a window function, then keep number 1
C.Group by customer and also select the order's other columns in the same statement
D.Fetch every order into the application and filter with a loop
Answer: B
Use a window function: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC), then wrap it and filter WHERE rn = 1. This is standard syntax, so it runs on PostgreSQL, MySQL 8+, SQL Server, Oracle, and SQLite 3.25+ — the classic "top-N per group" problem.
A user types "ha noi" while the data stores "Hà Nội". How to match?
A.Use pattern matching with a wildcard in the middle
B.Match against an accent-stripped column that is indexed
C.Lowercase both sides before comparing
D.Apply the accent-stripping function to the column in the filter
Answer: B
Match on an unaccented, indexed column. Applying the unaccent function directly in the filter clause works but forces a full table scan, because the index on the original column can no longer be used.
The query below fails with "unknown column total". Why?
sql
SELECT price * qty AS total
FROM order_items
WHERE total >1000;
A.You cannot alias a computed expression, only an existing column
B.The filter is processed before SELECT, so the alias does not exist yet
C.The alias needs a declared type before the engine can compare it
D.An alias must be double-quoted before it can be reused elsewhere
Answer: B
Because the filter clause runs before SELECT. The logical processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, so at filter time the alias total doesn't exist yet. Fix: repeat the expression in the filter clause, or wrap the query in a subquery or CTE and filter in the outer layer.
-- a (id): 1, 2, 2, 3-- b (id): 2, 3, 4SELECT id FROM a INTERSECTSELECT id FROM b; -- (1)SELECT id FROM a EXCEPTSELECT id FROM b; -- (2)
A.(1) 2, 3 and (2) 1, 4 — EXCEPT returns what differs on either side
B.(1) 2, 2, 3 and (2) 1 — only EXCEPT removes duplicates
C.(1) 1, 2, 3, 4 and (2) empty — INTERSECT merges the two sets
D.(1) 2, 3 and (2) 1 — both operations remove duplicates
Answer: D
(1) returns 2 and 3 — the common part. (2) returns 1 — the part only in the left side. All three set operations (UNION, INTERSECT, EXCEPT) remove duplicates by default; add ALL to keep them. EXCEPT isn't symmetric, so id 4 (only in the right side) doesn't appear anywhere.
How do JOIN ... USING (id), JOIN ... ON a.id = b.id and NATURAL JOIN differ?
A.USING names the join column and merges it into one; NATURAL guesses from every shared column name
B.All three are equivalent and differ only in verbosity
C.ON only works for INNER JOIN while USING covers the outer joins
D.USING can join on exactly one column while ON handles several
Answer: A
ON spells out the full condition and keeps the join column in both tables. USING (id) is shorter, requires the same column name on both sides, and merges the join column into one in the result. NATURAL JOIN joins on every same-named column automatically — convenient to type but dangerous, since adding a same-named column silently changes the join condition.
Orders must sort by status in a business-defined order (pending, then paid, then cancelled). Which approach is right?
A.Sort in the application after fetching all the data
B.Sort by the status name and choose names whose alphabetical order happens to match
C.Run three separate queries, one per status, then concatenate the results in order
D.Use a conditional expression in the sort clause that assigns each status a rank
Answer: D
Use a CASE expression right in the sort clause: ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 ELSE 3 END, created_at DESC. This is standard syntax, runs on every DBMS, and keeps both sorting and pagination on the database side.
The query below runs against a table where no row matches the filter. What is the result?
sql
SELECTcount(*), sum(amount), avg(amount), max(amount)
FROM payments
WHERE status ='refunded'; -- no rows match
A.One row: 0, NULL, NULL, NULL
B.One row: 0, 0, 0, 0
C.An error, because an average cannot be computed with no values
D.No rows — the filter removed everything, so the result is empty
Answer: A
A single row containing 0, NULL, NULL, NULL. An aggregate function without GROUP BY always returns exactly one row even when the input is empty. Only count gives 0; sum, avg, max, min all give NULL, because the sum or max of an empty set is undefined, not zero.
A table has four rows whose city column reads Hà Nội, Hà Nội, NULL, NULL. How many groups does GROUP BY city produce?
A.Three groups — each NULL forms its own group because NULL does not equal NULL
B.One group — NULL rows are dropped before grouping
C.Four groups — one per row whenever the grouping column contains NULL
D.Two groups — the NULL rows are collected into a single group
Answer: D
Two groups: one Hà Nội group (two rows) and one NULL group (two rows). Grouping does not use equality — if it did, the two NULLs would never meet — but the notion of "not distinct", under which every NULL lands in the same group.
A column declared CHAR(10) stores the string "abc". What comes back when reading it?
A.Exactly "abc", because the padding exists only in storage
B.The string padded with spaces out to ten characters
C.An error, because the string is shorter than the declared length
D.The string padded with null characters up to the declared length
Answer: B
The string is padded with spaces to ten characters, and that padding travels with the value out to the application. This is the real difference between CHAR(n) and VARCHAR(n): VARCHAR stores exactly what you wrote. So CHAR is almost always the wrong choice unless the data really is fixed-length.
