SQL shows up in nearly every fresher interview — developer, analyst, support, testing — because it's the fastest way to tell whether a candidate can think about data. The question list is short and stable. What separates candidates is whether the answer survives the follow-up.

The definition questions

WHERE vs HAVING

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. So you can write HAVING COUNT(*) > 5 but never WHERE COUNT(*) > 5. Follow-up: "Can a query use both?" Yes — and it's common: WHERE to cut rows down first, HAVING to filter the resulting groups.

DELETE vs TRUNCATE vs DROP

DELETE removes rows one at a time, can carry a WHERE, fires triggers, and can be rolled back. TRUNCATE empties the whole table in one operation — faster, no WHERE, usually resets auto-increment. DROP removes the table itself, structure included. Follow-up: "Which is fastest and why?" TRUNCATE, because it deallocates data pages instead of logging every row.

Primary key vs unique key

Both enforce uniqueness. A primary key is one per table and cannot be NULL; a unique key can be multiple per table and (in most engines) allows one NULL. Follow-up: "What's a composite key?" A key made of two or more columns, unique in combination — think (student_id, course_id) on an enrolment table.

UNION vs UNION ALL

UNION removes duplicates, which costs a sort; UNION ALL keeps everything and is faster. If you know the sets are disjoint, use UNION ALL — and say so, because it shows you think about cost.

The write-a-query questions

Second-highest salary

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Clean and works everywhere. The modern answer uses a window function:

SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 2;

Follow-up: "What if two people share the top salary?" That's exactly why DENSE_RANK rather than ROW_NUMBER — and why LIMIT 1 OFFSET 1 is the fragile answer. Mention ties before they ask and you're ahead.

Find duplicate rows

SELECT email, COUNT(*) AS c
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Employees with no department

SELECT e.name
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
WHERE d.id IS NULL;

Follow-up: "Why LEFT JOIN and not NOT IN?" Because NOT IN against a subquery containing a single NULL returns no rows at all — a classic production bug. NOT EXISTS is the safe alternative.

Nth record / per-group top-N

"The highest-paid employee in each department" is the most-asked hard one. Window functions:

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY department_id ORDER BY salary DESC
  ) AS rn
  FROM employees e
) t WHERE rn = 1;

The DBMS theory questions

Normalization — be able to walk 1NF → 2NF → 3NF with one example, and say when you'd denormalise on purpose. Details in normalization explained.
ACID — Atomicity, Consistency, Isolation, Durability. Have a one-line example ready for each; the bank-transfer example covers atomicity and durability well.
Indexes — what they speed up, what they cost on writes, why an index on a low-cardinality column (like a yes/no flag) rarely helps. See indexes and query optimization.
Clustered vs non-clustered index — clustered determines the physical row order (one per table); non-clustered is a separate structure pointing back at rows.

How to prepare in the last week

Write every query above from scratch, against real tables, without looking. Reading them is not preparation.
For each answer, prepare the one follow-up you'd ask if you were the interviewer.
Practise saying answers out loud. "It filters after grouping" beats a memorised paragraph.
Do a timed mixed set the day before — no notes, no autocomplete.

Interviewers aren't checking whether you memorised HAVING. They're checking whether you can be handed an unfamiliar schema and reason about it out loud.

If you want the reps, run them against a live database in your browser — no install needed — and check your answers against hidden data rather than your own eyeballs.

SQL Interview Questions for Freshers (and How to Answer Them)