Ruankao Database Systems Engineer (Intermediate) common mistakes
Ruankao Database Systems Engineer (Intermediate) · Understand the traps and how to avoid them.
Filtering grouped results in a WHERE clause (for example WHERE COUNT(*) > 5), comparing a column to NULL with = instead of IS NULL, or assuming UNION and JOIN do the same thing when combining two tables.
WHERE is encountered first in every tutorial and gets generalized to all filtering, and NULL is treated as an ordinary value rather than as unknown, which produces wrong intuitions about three-valued logic.
What to do instead
Classify every filter before writing it: row-level predicates go in WHERE, group-level predicates go in HAVING, and any NULL test uses IS NULL or IS NOT NULL. Sketch the result table for JOIN (horizontal, matched columns) versus UNION (vertical, matching schemas) to fix the distinction.
Declaring a table well designed because it 'looks organized', while leaving a transitive dependency such as storing instructor name and office in a course-enrollment table, or designating a foreign key as the primary key without checking uniqueness.
Spreadsheet intuition treats any tidy grid as normalized, and the candidate checks only that a key exists rather than testing candidate keys against every functional dependency the scenario implies.
What to do instead
List the functional dependencies in plain sentences ('each course has one instructor, and each instructor has one office') before any table is drawn. Place every determinant on the left, split off any non-key dependency into its own relation, and verify that each relation's chosen key uniquely determines all its other attributes.
Converts between denary and two's complement or between binary and hexadecimal but drops the sign bit, forgets to state the number of bits, or performs subtraction by ordinary binary subtraction and reports a positive result for a negative answer.
Candidates learn the flip-and-add-one procedure as a ritual without understanding the fixed width, so leading zeros and the sign bit get lost and range questions become guesswork.
What to do instead
Always state the word length first, for example '8-bit two's complement'. Write out all bits including leading zeros and leading sign bits. Check by converting back. Learn the range formula for n bits, minus two to the power n minus one up to two to the power n minus one minus one, and use it to sanity-check every answer. For subtraction, convert to adding the two's complement and discard the final carry with a note saying so.
Compares data structures by naming them, for example 'a linked list is better than an array', without linking the choice to the operations required: insertion, deletion, search, and access by index, or to memory layout and static versus dynamic sizing.
Structures are memorised as definitions with a vague advantage attached, and candidates do not rehearse justifying a choice against a stated scenario.
What to do instead
For each structure (array, linked list, stack, queue, binary search tree, hash table) write its memory layout, the cost of access by index, search, insertion and deletion, and one scenario where it is the right choice. When answering, quote the scenario's dominant operation and justify with that cost, for example frequent insertion at the front favours a linked list because no elements need shifting. Use the same reasoning to explain why a stack suits recursion or expression evaluation.
Choosing the wrong data type for a column, such as storing dates or numbers in VARCHAR, or using CHAR for variable-length text, and then being unable to sort, compare, or index correctly.
Data types are often skimmed as syntax rather than design decisions. String storage 'works' for insertion, so the consequences only appear later as lexicographic sorting ('10' before '9') and failed range comparisons.
What to do instead
Match the type to the domain: DATE/TIMESTAMP for dates, DECIMAL/NUMERIC for exact money, integer types for identifiers, VARCHAR for variable text, CHAR only for fixed-length codes. Check that comparisons and ORDER BY behave as expected on sample data.
Writing DDL that omits or misplaces constraints: no PRIMARY KEY, a FOREIGN KEY referencing a non-unique column, or expecting a CHECK/NOT NULL constraint to be enforced when it was never declared.
Candidates focus on getting the CREATE TABLE to execute rather than on the integrity rules. Constraint syntax varies by product, and some constraints are only enforced if declared at the right level (column vs table).
What to do instead
Declare PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY and CHECK explicitly and verify them by attempting violating inserts in a scratch database. Know that a foreign key must reference a primary or unique key, and that constraint naming helps later ALTER TABLE operations.
Writing NULL comparisons as `WHERE column = NULL` or `WHERE column != NULL`, then assuming the query proved the value is or is not empty.
Candidates treat NULL as an ordinary literal value like 0 or an empty string. Because SQL uses three-valued logic, any comparison with NULL evaluates to UNKNOWN, never TRUE, so the row is silently filtered out and the result set looks deceptively 'correct'.
What to do instead
Always test for the null state with IS NULL / IS NOT NULL. When NULL should be treated as a real value (e.g. in COALESCE or a default), wrap it explicitly and state the intended semantics in the query. Remember NOT IN with a NULL in the list returns no rows.
Confusing WHERE and HAVING, for example filtering on an aggregate with `WHERE COUNT(*) > 5` or putting a row-level condition in HAVING.
Both clauses filter, so candidates reach for whichever they learned first. The logical order of evaluation (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) is not the order clauses are written, and this is the usual gap.
What to do instead
Filter individual rows before grouping in WHERE; filter groups after aggregation in HAVING. Practice by writing the logical evaluation order at the top of the query and checking each predicate against it. Note that SELECT aliases are generally not usable in WHERE but often are in HAVING/ORDER BY depending on the product.
Using INNER JOIN when rows from one side with no match must be preserved, or adding a WHERE condition on the outer side that silently turns a LEFT JOIN back into an INNER JOIN.
Candidates equate 'join' with 'inner join' and do not think about unmatched rows. The second error is subtler: a predicate like WHERE b.status = 'active' in the WHERE clause rejects the NULL-extended rows produced by the outer join.
What to do instead
Choose the join type by asking which rows must survive. Put conditions that restrict the optional table in the ON clause of the outer join, not in WHERE. Verify with a small dataset that unmatched rows appear with NULLs.
Writing aggregate queries that select non-aggregated columns not listed in GROUP BY, then trusting the arbitrary value returned.
Some engines (notably older MySQL configurations) permit this, so the query runs without error and the candidate assumes it is valid. The result is non-deterministic and often wrong.
What to do instead
Every selected column must either be inside an aggregate function or appear in GROUP BY. Check by listing the SELECT columns and GROUP BY columns side by side. Use ONLY_FULL_GROUP_BY-style strictness as a mental rule even where the product is lenient.
Using DISTINCT to remove duplicate rows when the duplication actually comes from a join fan-out, and not noticing that DISTINCT can also hide genuinely different rows that differ only in one selected column.
DISTINCT is taught as a quick de-duplication tool. Candidates apply it as a band-aid without diagnosing why duplicates appeared, and they forget DISTINCT applies to the whole selected row, not to a single column.
What to do instead
Diagnose the cause first: check whether the join key is unique on the joined side. If you need one row per entity, aggregate or use a subquery/EXISTS instead. When you need distinct values of one column, select only that column or use GROUP BY.
Assuming a subquery in the SELECT or WHERE list can be treated like a join, or writing a scalar subquery that returns multiple rows and expecting it to work.
Candidates learn subqueries as 'queries inside queries' without distinguishing scalar, row, table, and correlated forms. A scalar subquery must return at most one row and one column, and that constraint is often overlooked.
What to do instead
Classify the subquery before writing it: scalar (one value), multi-row (use IN/ANY/ALL/EXISTS), or table (use in FROM). For correlated subqueries, confirm the outer reference is intentional. Test with data that produces multiple matches to see the error.