8 questions
SQL interview questions and answers
Junior questions
1. What types of JOIN are there, and how do they differ?
Answer
INNER JOINreturns only rows that have a match in both tables.LEFT JOINreturns every row of the left table, and where there is no match the right table's columns are filled withNULL.RIGHT JOINdoes the same for the right table, andFULL JOINreturns all rows from both sides.CROSS JOINproduces a Cartesian product with no matching condition.Follow-up question: How do you find customers who have no orders at all?
2. What are a primary key and a foreign key?
Answer
A primary key uniquely identifies a row in a table: its values are unique and cannot be
NULL, and a table has only one. A foreign key is a column that references the primary or a unique key of another table. It guarantees integrity: you cannot insert a reference to a row that does not exist. What happens when the parent row is deleted is set withON DELETE CASCADE,SET NULLorRESTRICT.Follow-up question: How does a natural key differ from a surrogate key, and which would you choose?
3. How does
GROUP BYwork together with aggregate functions?Answer
GROUP BYcombines rows with equal values in the given columns into one group. Aggregate functionsCOUNT,SUM,AVG,MIN,MAXare computed separately for each group. InSELECTyou may list only theGROUP BYcolumns or aggregates. For example,SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_idcounts each customer's orders.Follow-up question: How does
COUNT(*)differ fromCOUNT(column)?4. What is the difference between
WHEREandHAVING?Answer
WHEREfilters individual rows before grouping, so you cannot use aggregate functions in it.HAVINGfilters groups that are already formed afterGROUP BYand works with aggregates. For example,HAVING COUNT(*) > 5keeps only customers with more than five orders. Conditions on plain columns belong inWHERE, because then fewer rows are processed.Follow-up question: In what logical order are the parts of a
SELECTquery executed?5. How does
NULLwork in SQL, and how do you check for it correctly?Answer
NULLmeans a missing value, not zero and not an empty string. Any comparison withNULLyieldsUNKNOWN, socol = NULLnever matches, you have to writeIS NULLorIS NOT NULL. Aggregates such asSUMandAVGignoreNULL. You can substitute a default value withCOALESCE.Follow-up question: Why can
NOT INwith a subquery that returnsNULLproduce an empty result?
Middle questions
1. What is an index, how does it speed up queries, and what does it cost?
Answer
An index is a separate data structure, a B-tree by default in PostgreSQL, that lets the database find rows without a full table scan. It speeds up
WHERE,JOINandORDER BYon the indexed columns. The cost is that an index takes space and slows downINSERT,UPDATEandDELETE, because it has to be updated too. In a composite index the column order matters, and you check whether an index is used withEXPLAIN ANALYZE.Follow-up question: Why might a query with
WHERE lower(email) = ...not use a regular index onemail?2. What is a transaction, and what does ACID mean?
Answer
A transaction is a group of operations executed as one unit: either all changes are committed with
COMMIT, or all are undone withROLLBACK. Atomicity means exactly this all or nothing behaviour. Consistency guarantees the database moves from one valid state to another while respecting its constraints. Isolation defines how much concurrent transactions see each other's changes, and Durability guarantees that committed data survives a crash.Follow-up question: What isolation levels does PostgreSQL have, and which one is the default?
3. What is the N+1 query problem, and how do you fix it?
Answer
N+1 happens when code first fetches a list of N records with one query, and then runs another query for each of them to get related data. That is N+1 round trips to the database instead of one or two, and on large lists the delay becomes noticeable. You fix it with a
JOIN, a single query withWHERE id IN (...), or eager loading in the ORM. A SQL query log helps you spot the problem.Follow-up question: How does this problem show up in GraphQL, and what is DataLoader?
Train these questions
Study the cards, answer by text or voice and get an AI grade. Needs a signed in account.
Answer out loud and get graded
Here you get the questions with model answers. In the ShortLesson app you answer yourself, by text or voice, and AI grades the answer against the rubric and asks a follow-up, like a real interviewer.
The app is coming to the stores soon.
In the knowledge base
Reference pages for the technologies on this topic: who maintains them, when to use them and what interviews ask.
