SQL Interview Questions for Freshers

SQL is the one skill that shows up in almost every fresher interview, whether you apply for backend, data analyst or testing roles. This set covers the questions you will actually hear in your first job hunt — filtering with WHERE, joins, GROUP BY, keys and the classic DELETE vs TRUNCATE trap — each with a short spoken answer and a query you can write on the spot.

Why interviewers ask basic SQL questions to freshers

For a fresher, SQL is a fair test: it is small enough to learn well and impossible to fake. An interviewer can hand you two tables and know within five minutes whether you have written queries or only watched tutorials.

The questions stay simple on purpose. Anyone can define a join; far fewer can write a correct LEFT JOIN that keeps unmatched rows, or explain why WHERE cannot use COUNT(). That gap is exactly what the interviewer is measuring.

If your fundamentals are shaky, spend an evening on what SQL is and how databases store data before you rehearse these answers — the concepts below assume you know tables, rows and columns.

How to answer SQL questions as a fresher

For every question, say the one-line rule first, then write a tiny query. Interviewers trust a candidate who reaches for the keyboard. Even on a whiteboard, sketch the table and a two-row result.

Use the same two example tables throughout your prep — say an employees table and a departments table — so you can answer any join or grouping question without inventing new schema each time. Speaking while you write ("I filter rows first, then group") shows structured thinking.

Q1. What is the difference between SELECT and SELECT DISTINCT?

SELECT returns every matching row, including duplicates. SELECT DISTINCT removes duplicate rows from the result so each combination of the selected columns appears once.

DISTINCT applies to the whole selected row, not a single column. If you select two columns, a row is a duplicate only when both values match.

SELECT DISTINCT department_id
FROM employees;

This returns each department id once, even if fifty employees share it. DISTINCT is the simplest way to collapse repeated values in a result.

Interview note: Follow-up: "does DISTINCT work on one column when you select three?" No — it deduplicates the entire selected row, so all three columns count.

Q2. How do WHERE and ORDER BY work together?

WHERE filters which rows come back; ORDER BY sorts the rows that survive the filter. WHERE runs first, then ORDER BY arranges the result, ascending by default or descending with DESC.

SELECT name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

You can order by multiple columns for tie-breaking: ORDER BY department_id, salary DESC.

Interview note: Trap: "can you use a column alias in WHERE?" No — WHERE runs before the SELECT aliases exist. You can, however, use an alias in ORDER BY.

Q3. What are the different types of SQL joins?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table plus matches from the right. RIGHT JOIN does the reverse, and FULL OUTER JOIN returns all rows from both tables, matched or not.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

The LEFT JOIN above keeps employees who have no department — their dept_name comes back NULL. Joins are the single most tested SQL topic; drill them in the dedicated SQL joins interview set.

Interview note: Follow-up: "what does INNER JOIN drop that LEFT JOIN keeps?" Rows from the left table with no match on the right.

Q4. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates like COUNT or SUM.

SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE salary > 30000
GROUP BY department_id
HAVING COUNT(*) > 5;

Here WHERE drops low-salary rows first, then HAVING keeps only departments with more than five remaining employees.

Interview note: Trap: "can you replace HAVING with WHERE here?" No — WHERE COUNT(*) > 5 is invalid because aggregates do not exist until after grouping.

Q5. What do aggregate functions and GROUP BY do?

Aggregate functions — COUNT, SUM, AVG, MIN, MAX — collapse many rows into one summary value. GROUP BY splits rows into groups so the aggregate runs per group instead of over the whole table.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Every column in the SELECT that is not inside an aggregate must appear in GROUP BY. Practice this pattern in the GROUP BY tutorial.

Interview note: Follow-up: "does COUNT(*) count NULLs?" COUNT(*) counts all rows; COUNT(column) skips NULLs in that column.

Q6. What is a primary key and a foreign key?

A primary key uniquely identifies each row in a table and cannot be NULL or duplicated. A foreign key is a column that references the primary key of another table, enforcing a valid relationship between them.

A table has exactly one primary key (which may span multiple columns). Foreign keys prevent orphan rows — you cannot insert an employee with a department_id that does not exist in departments.

Interview note: Trap: "can a foreign key be NULL?" Yes — a NULL foreign key means "no relationship yet", which is often valid.

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

DELETE removes selected rows and can be filtered with WHERE and rolled back. TRUNCATE removes all rows quickly but keeps the table structure. DROP removes the entire table, structure included.

DELETE FROM employees WHERE department_id = 4; -- rows, filterable
TRUNCATE TABLE employees;                       -- all rows, keeps table
DROP TABLE employees;                           -- table is gone

DELETE is a DML operation and is logged row by row; TRUNCATE and DROP are DDL and are much faster but generally cannot be rolled back in the same way.

Interview note: Follow-up: "which resets an auto-increment counter?" TRUNCATE typically resets it; DELETE does not.

Q8. What is the difference between UNION and UNION ALL?

UNION combines the results of two queries and removes duplicate rows. UNION ALL combines them and keeps every row, including duplicates, which makes it faster.

Both require the two queries to return the same number of columns with compatible types. Use UNION ALL when you know there are no duplicates or you want them — it skips the expensive de-duplication step.

SELECT name FROM current_employees
UNION ALL
SELECT name FROM former_employees;

Interview note: Trap: "why is UNION slower?" It sorts and removes duplicates; UNION ALL just concatenates.

Q9. How does NULL behave in SQL?

NULL means "unknown", not zero or empty string. Any comparison with NULL using = returns unknown, so you must test it with IS NULL or IS NOT NULL.

SELECT name FROM employees WHERE manager_id IS NULL;

NULL also spreads through arithmetic — salary + NULL is NULL — and aggregate functions except COUNT(*) ignore it.

Interview note: Follow-up: "what does WHERE manager_id = NULL return?" No rows — because = NULL is never true. This is a classic mistake.

Q10. What is the difference between CHAR and VARCHAR?

CHAR(n) is a fixed-length string padded with spaces to length n. VARCHAR(n) is variable-length and stores only the characters you put in, up to n.

Use CHAR for fixed-width codes like country codes or a fixed status flag; use VARCHAR for names, emails and anything of varying length, which is most columns. VARCHAR saves space when values differ in length.

Interview note: Trap: "does CHAR(10) storing 'abc' use 3 bytes?" No — it pads to 10 characters.

Q11. What is the order of execution of a SELECT query?

SQL runs a query in this logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then LIMIT — even though you write SELECT first.

Knowing this order explains many rules at once: why WHERE cannot see aliases (they are created in SELECT, which runs later), and why HAVING can use aggregates (it runs after GROUP BY).

Interview note: Follow-up: "why can ORDER BY use an alias but WHERE cannot?" Because ORDER BY runs after SELECT, so the alias already exists.

Q12. What is a subquery, and when do you use one?

A subquery is a query nested inside another query. You use it to filter or compute against a value that itself comes from a query — for example finding employees who earn above the company average.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subqueries can appear in WHERE, SELECT or FROM. This idea powers the very common "second highest salary" question — see how to find the second highest salary.

Interview note: Trap: "when does a correlated subquery run?" Once per outer row, because it references the outer query — which can be slow.

Q13. What is an index and why does it speed up queries?

An index is a separate sorted structure that lets the database find rows without scanning the whole table, much like the index at the back of a book. It speeds up reads on the indexed columns but slightly slows inserts and updates.

You index columns you frequently filter or join on, such as a foreign key or an email used for lookups. Indexes are not free — each one takes storage and must be maintained on writes.

Interview note: Follow-up: "does adding indexes always help?" No — too many indexes slow down INSERT/UPDATE and waste space; index what you actually query.

How to prepare

Set up a free local database, create two small tables, and write every query above yourself. Freshers who have typed GROUP BY ... HAVING a dozen times never freeze on it in an interview.

Rehearse the tricky comparisons out loud — WHERE vs HAVING, DELETE vs TRUNCATE, UNION vs UNION ALL — because those are the questions designed to catch memorizers. Then work through the SQL interview queries collection for mixed rounds. Strong SQL plus one main language is exactly the foundation the Java Full Stack with AI course builds on.