Interview Questions: SQL Fundamentals

Fundamentals questions are not easy questions. They are the ones where an interviewer learns whether you understand the language or have only memorized patterns. A confident one-line answer followed by the reason behind it is what separates the two.

For each question below, say the short answer first, then explain. That order shows you can summarize, and gives the interviewer a place to stop you or push deeper.

1. What is the difference between SQL, a database and a DBMS?#

Short answer. SQL is the language. A database is the organized data. A DBMS (such as PostgreSQL or MySQL) is the software that stores it, enforces rules on it and executes SQL against it.

Why it matters. The distinction shows up in practice: SQL is standardized but every DBMS adds its own dialect, which is why LIMIT, TOP and FETCH FIRST all exist. An RDBMS organizes data into related tables.

Likely follow-up. "What is the difference between SQL and NoSQL?" Answer in terms of trade-offs. Relational systems enforce a schema, support joins and multi-row transactions; many NoSQL systems favor flexible schemas and horizontal scale, and make you model around your queries.

2. Name the categories of SQL commands.#

Short answer. DDL defines structure (CREATE, ALTER, DROP, TRUNCATE). DML changes data (INSERT, UPDATE, DELETE). DQL reads it (SELECT). DCL manages permissions (GRANT, REVOKE). TCL controls transactions (BEGIN, COMMIT, ROLLBACK, SAVEPOINT).

Add. Some sources count SELECT under DML, and TRUNCATE is classed as DDL even though it removes rows. In PostgreSQL, DDL is transactional, which is not true in MySQL or Oracle.

3. What is the difference between DELETE, TRUNCATE and DROP?#

Short answer. DELETE removes chosen rows and can use WHERE. TRUNCATE removes all rows quickly and keeps the table. DROP removes the table itself.

Reasoning. DELETE processes each row, fires row triggers and leaves dead tuples for vacuum. TRUNCATE discards the table's storage in one step, takes a strong lock and does not fire row-level delete triggers. It can reset identity counters with RESTART IDENTITY, and it refuses to run on a table referenced by a foreign key unless you add CASCADE.

Follow-up. "Can you roll back a TRUNCATE?" In PostgreSQL, yes, inside a transaction. In MySQL, no.

4. Primary key, unique key, foreign key: what is the difference?#

Short answer. A primary key uniquely identifies each row and cannot be NULL, with one per table. A unique constraint also prevents duplicates, but a table can have many and they allow NULL. A foreign key requires a value to exist in another table's key.

Add. The unique values in a unique constraint can include several NULLs, because NULL is not equal to anything, even another NULL. A primary key can span several columns (a composite key). Primary and unique constraints create an index automatically. Foreign keys do not, in PostgreSQL, so index them yourself.

5. What is NULL, and how does it behave in comparisons?#

Short answer. NULL means unknown or missing, not zero or an empty string. Any comparison with it, including NULL = NULL, yields unknown rather than true or false, and WHERE keeps only rows where the condition is true.

Show it. WHERE phone = NULL returns nothing. Use IS NULL. COUNT(*) counts rows, while COUNT(column) skips NULLs. NULL plus a number is NULL. NULL OR TRUE is TRUE, and NULL AND FALSE is FALSE.

Follow-up. "How do you treat NULL as a value?" COALESCE(x, default), or IS DISTINCT FROM for comparison.

6. What is the difference between WHERE and HAVING?#

Short answer. WHERE filters rows before grouping. HAVING filters groups after aggregation.

Test. If the condition uses an aggregate (SUM(x) > 100), it must be in HAVING. If it uses a plain column, put it in WHERE, which is faster because it shrinks the data before grouping. HAVING without GROUP BY treats the whole table as one group.

7. In what order does a query execute?#

Short answer. FROM (and joins), WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

Why interviewers love this. It explains behavior that otherwise looks arbitrary. You cannot use a SELECT alias in WHERE, because the alias is created later. You can use it in ORDER BY. Window functions run after HAVING and before ORDER BY, so you cannot filter on them in WHERE.

Add a caveat. This is the logical order. The optimizer physically reorders work as long as the result is the same.

8. CHAR, VARCHAR and TEXT?#

Short answer. CHAR(n) is fixed length and padded. VARCHAR(n) is variable with a limit. TEXT is variable with no limit.

Detail. In PostgreSQL there is no performance difference between VARCHAR and TEXT, so use VARCHAR(n) only when the limit is a business rule. CHAR(n) pads with spaces, which causes comparison surprises, so avoid it. Other databases differ, so mention that.

9. List the constraints and what each enforces.#

Short answer. NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT (the last is a default value rather than a restriction).

Strong addition. Constraints are enforced for every client, so they are the most reliable place for integrity rules. A CHECK passes when its expression is NULL, so combine it with NOT NULL when a value must be present.

10. UNION versus UNION ALL?#

Short answer. UNION removes duplicate rows from the combined result. UNION ALL keeps everything.

Reasoning. Deduplication requires a sort or a hash over the whole result, so UNION ALL is cheaper. Default to UNION ALL and use UNION only when duplicates are possible and unwanted. Both queries need the same number of columns with compatible types.

11. DISTINCT versus GROUP BY?#

Short answer. Both can remove duplicates. GROUP BY lets you aggregate over each group, while DISTINCT only deduplicates the output.

Add. For plain deduplication the planner usually produces the same plan. DISTINCT is a smell when it appears after a join, because it often masks a join that multiplies rows. Find the cause rather than hiding it.

12. What is a surrogate key, and why use one?#

Short answer. A surrogate key is a meaningless generated identifier, like an auto-incremented ID. It is used instead of a natural key (email, national ID) because natural keys change, vary in format and get reused.

Complete the answer. Keep a UNIQUE constraint on the natural identifier so uniqueness is still enforced.

13. How does LIKE work, and when is it slow?#

Short answer. LIKE matches patterns with % (any run of characters) and _ (one character). PostgreSQL's ILIKE ignores case.

Performance. A pattern with a leading wildcard ('%term') cannot use an ordinary B-tree index because there is no known starting point. Use a trigram index, full-text search, or an expression index on LOWER(column) for prefix matches.

14. What is normalization, in one minute?#

Short answer. Organizing tables so each fact is stored once, to prevent update, insert and delete anomalies. 1NF means atomic values. 2NF means no dependence on part of a composite key. 3NF means no dependence between non-key columns.

Mnemonic. Every non-key column depends on the key, the whole key, and nothing but the key.

Balance it. Denormalization is legitimate for read-heavy reporting and for history, such as keeping the price paid on an order line, but each copy needs a mechanism that keeps it consistent.

15. What is an index, and what does it cost?#

Short answer. A separate sorted structure (usually a B-tree) that lets the database find rows without scanning the whole table.

Cost. Every index slows INSERT, UPDATE and DELETE, and consumes space. Index the columns you filter, join and sort on, not every column. Mention that the planner may still ignore an index when a query returns a large share of the table.

16. What is a view, and what is a materialized view?#

Short answer. A view is a stored query that behaves like a table and runs each time it is read. A materialized view stores the result physically and must be refreshed.

Trade-off. A view is always current but no faster than its query. A materialized view is fast but can be stale. Views are also used to hide columns and give reports a stable interface.

17. Why does NOT IN sometimes return no rows?#

Short answer. If the subquery returns even one NULL, x NOT IN (...) evaluates to unknown for every row, so nothing is returned.

Fix. Filter NULLs out of the subquery, or use NOT EXISTS, which is not affected. This question tests whether you understand three-valued logic better than almost any other.

Practice#

1. Pick five questions from this list and answer each aloud in under 45 seconds: short answer first, then one reason.
2. For question 7, write three queries that fail because of evaluation order (alias in WHERE, window function in WHERE, aggregate in WHERE) and explain each error.
3. For question 17, build the failing query with employees.manager_id and fix it two ways.
4. Pick the three questions you answered worst. Write a fuller answer for each and test it against the course database.