Question 1

Filter to one department

Accepted Answer

WHERE dept = 'Sales' keeps only rows where dept equals the string 'Sales'. Alice (row 1) and Carol (row 3) both match. Bob's dept is 'Eng', so he is excluded.

Question 2

Exclude a category

Accepted Answer

!= excludes rows where the column equals the given value. Only Gadget has category 'software'. You can also write <> instead of != - both mean not equal in SQL.

Question 3

When both conditions must pass

Accepted Answer

AND requires both conditions to be true. Alice is in Sales but her salary (70000) fails the second condition. Carol earns over 80000 but is not in Sales. Only Bob satisfies both.

Question 4

Two statuses, one filter

Accepted Answer

IN checks whether the column value matches any value in the list. IN ('shipped', 'delivered') is shorthand for status = 'shipped' OR status = 'delivered'. Rows 2 and 3 match; rows 1 and 4 do not.

Question 5

Widening the net with OR

Accepted Answer

OR returns a row if at least one condition is true. Alice matches the first condition, Bob matches the second. Carol matches neither and is excluded.

Question 6

What is a PRIMARY KEY?

Accepted Answer

A PRIMARY KEY enforces two rules at once: every value must be unique across all rows, and no value may be NULL. You can only define one primary key per table, though it can span multiple columns (a composite key). Most databases automatically create an index on the primary key column, but the key itself is a constraint, not an index.

Question 7

What is a full table scan?

Accepted Answer

A full table scan means the query planner has no index it can use, so it reads the table from start to finish. This is fine for small tables or when you are fetching a large portion of the rows anyway, but it becomes expensive on large tables with selective WHERE clauses. Adding an index on the filtered column typically eliminates the scan.

Question 8

What does an INDEX do?

Accepted Answer

An index is a separate data structure (usually a B-tree) that lets the database jump directly to rows matching a condition instead of reading every row in the table. The trade-off is that indexes consume extra disk space and must be updated on every INSERT, UPDATE, and DELETE. A common mistake is adding an index to every column - over-indexing slows down writes without helping reads on low-cardinality columns.

Question 9

What is a SQL transaction?

Accepted Answer

Transactions give you all-or-nothing guarantees. If you transfer money between accounts - debiting one and crediting the other - a transaction ensures that a crash or error mid-way does not leave the database in a half-updated state. You start a transaction with BEGIN (or START TRANSACTION), confirm it with COMMIT, or cancel it with ROLLBACK. Many databases run each individual SQL statement in its own auto-commit transaction by default.

Question 10

COMMIT vs ROLLBACK

Accepted Answer

COMMIT makes every change within the transaction durable - other sessions can see the updates and they survive a crash. ROLLBACK discards all changes, leaving the database exactly as it was when the transaction began. If your application crashes without reaching COMMIT, the database automatically rolls back the transaction on reconnect.

Question 11

Does 80 make the cut?

Accepted Answer

BETWEEN x AND y is exactly equivalent to col >= x AND col <= y - both boundary values are included. Bob (80) hits the lower boundary; Dave (100) hits the upper boundary; both qualify. A common mistake is assuming BETWEEN is exclusive, which would wrongly drop Bob and Dave.

Question 12

Mixed AND and OR - who wins?

Accepted Answer

AND has higher precedence than OR, so this evaluates as dept = 'Sales' OR (dept = 'Eng' AND salary > 92000). Alice and Bob satisfy dept = 'Sales'. Carol is in Eng but salary 90000 is not > 92000. Always use parentheses when mixing AND and OR to make intent explicit.

Question 13

How far does % reach?

Accepted Answer

% matches zero or more characters. 'Al%' matches any string starting with 'Al' - Alice, Alexander, and Alicia all qualify. Bob does not start with 'Al' and is excluded.

Question 14

The boundary price question

Accepted Answer

BETWEEN x AND y is equivalent to >= x AND <= y - both boundary values are included. Beta (20.00) and Gamma (30.00) both qualify. Alpha is below the range; Delta is above.

Question 15

Blocking a list of departments

Accepted Answer

NOT IN excludes rows whose column value appears in the list. Alice (Sales) and Carol (HR) are filtered out. Bob (Eng) is the only row that remains. NOT IN ('Sales', 'HR') is equivalent to dept != 'Sales' AND dept != 'HR'.

Question 16

What does a single underscore lock in?

Accepted Answer

_ matches exactly one character. 'A_' matches any two-character string starting with A. A1 and AB each have exactly two characters starting with A. A12 has three characters so it does not match. B1 starts with B.

Question 17

The boundary flip with NOT BETWEEN

Accepted Answer

NOT BETWEEN x AND y is equivalent to < x OR > y. Because BETWEEN is inclusive on both ends, NOT BETWEEN excludes both boundary values too. Prices 20 and 30 fall inside the range and are excluded. Only 10 and 40 remain, giving 2 rows. This symmetry - BETWEEN includes boundaries, NOT BETWEEN excludes them - is where people most often go wrong.

Question 18

What does the UNIQUE constraint do?

Accepted Answer

UNIQUE enforces that every value in the column appears at most once - but NULL is treated specially. Most databases allow multiple NULL values in a UNIQUE column because NULL is unknown, so two NULLs are not considered equal. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns are allowed to be NULL.

Question 19

What does a CHECK constraint do?

Accepted Answer

A CHECK constraint lets you write an arbitrary condition that every inserted or updated value must pass - for example CHECK (age >= 0) or CHECK (status IN ('active', 'inactive')). If the condition evaluates to FALSE, the write is rejected. NULL is the subtle edge case: if the expression evaluates to NULL (unknown), most databases let the row through rather than rejecting it. Use NOT NULL alongside CHECK when you want to block NULLs too.

Question 20

Which operator binds first?

Accepted Answer

AND has higher precedence than OR, so it binds its operands first - just like * binds before + in arithmetic. The expression groups as (a AND b) OR (c AND d): both AND pairs are evaluated first, then OR combines the two results. Always add parentheses when mixing AND and OR; silent precedence bugs are hard to find in production queries.

Question 21

Does '%sale%' match the empty row?

Accepted Answer

% matches zero or more characters, so '%sale%' requires the substring 'sale' to appear anywhere in the value. Row 2 ('sale') matches because 'sale' is present. Row 1 is an empty string - there is no 'sale' substring in it, so it does not match. The 'zero or more' part means % can match nothing at all on either side, not that the entire pattern is optional.

Question 22

Which rows slip through the missing parens?

Accepted Answer

AND binds before OR, so this reads as dept = 'Sales' OR (dept = 'HR' AND salary > 80000). Alice and Carol satisfy dept = 'Sales'. Bob satisfies the HR + high-salary branch. Dave is in HR but earns only 55000, so the AND branch is false for him. If you intended all Sales or HR employees, you would need (dept = 'Sales' OR dept = 'HR') AND salary > 80000.

Question 23

What expression mirrors NOT BETWEEN?

Accepted Answer

NOT BETWEEN x AND y negates the inclusive range, producing col < x OR col > y. Because BETWEEN includes boundaries, NOT BETWEEN excludes them - a value of exactly 10 or exactly 20 does not pass. The AND option (col < 10 AND col > 20) is a logical impossibility; no single value can be simultaneously below 10 and above 20.

Question 24

Which codes does [ABC]% catch?

Accepted Answer

[ABC]% matches any string whose first character is A, B, or C. Apple starts with A, Banana with B, Cherry with C - all three match. Durian starts with D, which is not in the bracket set, so it is excluded. Note that bracket patterns are a T-SQL extension and are not standard SQL; PostgreSQL uses similar syntax in some contexts but standard SQL does not support them.

Question 25

Which values fit the two-character pattern?

Accepted Answer

The underscore _ matches exactly one character - not zero, not two, exactly one. '_b' therefore requires a two-character string where the second character is 'b'. 'ab' is the only match: 'a' is too short (no character before b), 'abc' is too long (two characters before b), and '' is empty. People often confuse _ with % and expect it to match any number of characters.

Question 26

Finding the boss with no manager

Accepted Answer

IS NULL is the only correct way to test for a NULL value. manager_id = NULL is UNKNOWN for every row - even the row where manager_id is actually NULL - because NULL is not equal to anything, including itself. IS NULL bypasses this and correctly identifies the NULL row.

Question 27

The invisible NULL that kills the query

Accepted Answer

NOT IN ('Sales', NULL) expands to dept != 'Sales' AND dept != NULL. dept != NULL is UNKNOWN for every row because nothing can be compared to NULL with = or !=. WHERE only keeps rows evaluating to TRUE - UNKNOWN rows are dropped. Fix this with WHERE dept IS NOT NULL or use NOT EXISTS.

Question 28

Filtering out the top of the org chart

Accepted Answer

IS NOT NULL keeps only rows where the column has an actual value. Alice and Carol both have NULL manager_id, so they are excluded. Bob (manager_id = 1) and Dave (manager_id = 2) pass the filter. Just as with IS NULL, you cannot use != NULL here - that comparison is always UNKNOWN.

Question 29

What do the ACID properties mean?

Accepted Answer

ACID is the set of properties that make database transactions reliable. Atomicity means partial completion is impossible - the transaction either finishes fully or not at all. Consistency means every transaction moves the database from one valid state to another, respecting all constraints. Isolation means transactions running in parallel do not see each other's uncommitted changes. Durability means once you COMMIT, the data is safe even if the server crashes immediately after. Different isolation levels trade some isolation for higher concurrency - read committed, repeatable read, and serializable are the common options.

Question 30

What does 'sargable' mean?

Accepted Answer

Sargable stands for 'Search ARGument ABLE' - a condition that the query optimizer can match against an index. Wrapping a column in a function usually breaks sargability: WHERE YEAR(created_at) = 2024 forces a full scan, but WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' can use an index on created_at. The same applies to leading wildcards in LIKE - WHERE name LIKE '%smith' cannot use a standard B-tree index, but WHERE name LIKE 'smith%' can.

Question 31

Why did all 10 rows vanish?

Accepted Answer

NOT IN (v1, v2, NULL) expands to col != v1 AND col != v2 AND col != NULL. col != NULL is UNKNOWN for every possible col value because NULL is never equal or unequal to anything. WHERE discards rows that are not TRUE, so all rows disappear. The safe fix is WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL), or use NOT EXISTS.

Question 32

What does the caret do inside brackets?

Accepted Answer

The caret ^ inside brackets negates the set, so [^A-E] means 'any single character that is NOT A, B, C, D, or E'. The trailing % then allows any remaining characters. Only the first character is constrained - 'Frank' would match because F is not in A-E, even though no other character is checked by the bracket. This is T-SQL bracket syntax; in standard SQL you would achieve this with SUBSTRING and NOT BETWEEN or a CASE expression.

Question 33

Is this divide-by-zero guard safe?

Accepted Answer

Unlike most programming languages, SQL does not promise that the left side of AND is fully evaluated before the right side. The query optimizer can reorder predicate evaluation for performance, which means col2 / col1 may run on rows where col1 = 0, causing a divide-by-zero error at runtime. The safe fix is NULLIF: col2 / NULLIF(col1, 0) > 2, which converts zero to NULL so the division yields NULL instead of an error.

Question 34

NULL inside an IN list

Accepted Answer

IN (5, NULL) expands to manager_id = 5 OR manager_id = NULL. The comparison manager_id = NULL is UNKNOWN for every row - even for Alice and Carol whose manager_id is NULL - because equality with NULL is never TRUE. Only Bob matches manager_id = 5. To find NULL values you must use IS NULL explicitly.

Question 35

5 is clearly not 1 or 2 - so why no rows?

Accepted Answer

NOT IN (1, 2, NULL) expands to n != 1 AND n != 2 AND n != NULL. The last condition, n != NULL, is UNKNOWN for every row regardless of what n contains - 5, 10, and 3 all become UNKNOWN. AND with UNKNOWN is UNKNOWN, so the entire predicate is UNKNOWN for every row, and WHERE keeps nothing. Even though 5 is clearly not 1 or 2, the NULL in the list silently kills all results - always filter NULLs from NOT IN lists.