What you'll learn
Quick Answer
SQL interviews for freshers concentrate on joins and knowing which one to use, GROUP BY with HAVING versus WHERE, aggregate functions, indexes and why they help, normalisation up to third normal form, and the difference between DELETE, TRUNCATE and DROP. Expect at least one written query, most often finding the second-highest salary or the duplicate rows in a table.
Joins — The Most Asked Topic
Explain the types of joins.
- INNER JOIN — only rows matching in both tables.
- LEFT JOIN — all rows from the left table, with NULLs where the right has no match.
- RIGHT JOIN — the mirror image.
- FULL OUTER JOIN — all rows from both, NULLs where either side is missing.
- CROSS JOIN — every combination, the Cartesian product.
- SELF JOIN — a table joined to itself, typically for hierarchies like employee and manager.
Find employees with no department. This is the standard test of whether you really understand LEFT JOIN:
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;The IS NULL after a LEFT JOIN is the idiom for "exists on the left but not the right".
A trap worth knowing: putting a condition on the right table in the WHERE clause silently converts a LEFT JOIN into an INNER JOIN, because NULL fails the comparison. If the condition should apply only to the join, put it in the ON clause instead.
GROUP BY, HAVING and Aggregates
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after. So aggregate functions can appear in HAVING but not in WHERE.
SELECT dept_id, COUNT(*) AS staff
FROM employees
WHERE active = 1 -- filters rows first
GROUP BY dept_id
HAVING COUNT(*) > 5; -- filters groups afterWhat is the logical order of execution? A strong answer, because it explains several other rules:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMITThis is why you cannot use a SELECT alias in WHERE (the alias does not exist yet) but can use it in ORDER BY.
Difference between COUNT(*) and COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts distinct non-NULL values. A frequent follow-up.
How do NULLs behave in aggregates?
They are ignored by SUM, AVG and COUNT(column). This matters: AVG(salary) over ten rows where two are NULL divides by eight, not ten.
The Queries You Will Be Asked to Write
Find the second-highest salary. Almost guaranteed. Know two approaches.
-- Approach 1: works everywhere
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Approach 2: window function, and handles Nth easily
SELECT DISTINCT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk = 2;Mention the edge case before being asked: if everyone earns the same, the first returns NULL. Using LIMIT 1 OFFSET 1 is a third answer but fails when there are ties, since it ranks rows rather than values.
Find duplicate rows.
SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;Find employees earning more than their department average. Tests correlated subqueries:
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) FROM employees
WHERE dept_id = e.dept_id
);Get the top earner per department using a window function, which is increasingly expected:
SELECT * FROM (
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
) t WHERE rn = 1;Keys, Constraints and Normalisation
Primary key vs unique key?
Both enforce uniqueness. A table has one primary key, which cannot be NULL. Unique keys can be several per table and typically allow one NULL, though this varies by database.
What is a foreign key?
A column referencing a primary key in another table, enforcing referential integrity — you cannot insert a row pointing at a parent that does not exist, and deletes can cascade or be blocked.
Explain normalisation. Keep it concrete rather than reciting definitions:
- 1NF — atomic values, no repeating groups. A column holding "maths,science" breaks it.
- 2NF — 1NF plus no partial dependency on part of a composite key.
- 3NF — 2NF plus no transitive dependency; non-key columns depend only on the key.
Then add the practical point: normalisation removes duplication and update anomalies, while denormalisation deliberately reintroduces some duplication to avoid expensive joins in read-heavy systems. Knowing when to break the rule is a better answer than reciting it.
DELETE vs TRUNCATE vs DROP?
- DELETE — removes rows, can have a WHERE clause, logged row by row, can be rolled back, keeps the table.
- TRUNCATE — removes all rows quickly, no WHERE, usually resets auto-increment, keeps the structure.
- DROP — removes the table entirely.
Indexes and Transactions
What is an index and why use one?
A sorted structure, usually a B-tree, letting the database find rows without scanning the whole table. It turns a full scan into a logarithmic lookup.
Always add the cost, because the follow-up is coming: every insert, update and delete must maintain every index, and indexes take disk space. So index the columns you filter, join and sort on, not every column.
Why might an index not be used?
A function applied to the column, a LIKE pattern with a leading wildcard, low selectivity, or a type mismatch. Mentioning EXPLAIN as the way to check is a strong signal.
What are ACID properties?
- Atomicity — all of a transaction happens, or none of it.
- Consistency — the database moves between valid states.
- Isolation — concurrent transactions do not interfere.
- Durability — committed data survives a crash.
The bank transfer example is the standard illustration for atomicity: debiting one account and crediting another must both happen or neither.
What is a stored procedure? Precompiled SQL stored in the database, called by name. Reduces round trips and centralises logic; the trade is that business logic in the database is harder to version and test.