SQL (Structured Query Language) is used to query and manage data in relational database systems. For a fresher, effective SQL interview preparation means understanding what a query does, why a clause belongs in a particular place, and how to handle duplicates or missing data.
1. SQL fundamentals to revise first
Be able to explain tables, rows, columns, primary keys, foreign keys, and the difference between filtering rows and filtering grouped results.
| Clause | Purpose |
|---|---|
SELECT | Chooses columns or expressions. |
WHERE | Filters individual rows. |
ORDER BY | Sorts output rows. |
GROUP BY | Groups rows for aggregation. |
HAVING | Filters groups after aggregation. |
SELECT employee_name, salary
FROM employees
WHERE salary >= 30000
ORDER BY salary DESC;This returns employees whose salary is at least 30,000, sorted from highest to lowest.
2. SQL joins: a common fresher topic
Joins combine related rows from tables. Suppose employees contains employee details and departments contains department names.
| Join | What it returns |
|---|---|
INNER JOIN | Only rows with matches on both sides. |
LEFT JOIN | All left-side rows plus matching right-side data; unmatched columns are NULL. |
RIGHT JOIN | All right-side rows plus matching left-side data. |
SELECT e.employee_name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id;Practice: find employees with no matching department using a LEFT JOIN and a NULL check on a right-side key that is non-null for valid matches.
3. GROUP BY, aggregate functions and HAVING
Aggregate functions calculate values across multiple rows. Common examples are COUNT(), SUM(), AVG(), MIN(), and MAX().
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;The query groups employees by department, counts each group, and keeps groups with more than five rows. WHERE filters rows before grouping; HAVING filters grouped results.
4. Subqueries and common table expressions
A subquery is nested inside another query. A common table expression (CTE), introduced with WITH, names a query expression and can make complex logic easier to read.
WITH department_averages AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT department_id, avg_salary
FROM department_averages
WHERE avg_salary > 50000;5. Window functions: ranking rows
Window functions calculate values across related rows without collapsing each group into a single output row. They are useful for ranking, running totals, and comparisons.
SELECT employee_name, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employees;RANK() can leave gaps after ties; DENSE_RANK() does not. State how ties should be handled in your answer.
6. SQL interview practice questions for freshers
Find duplicate email addresses
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;Find the second-highest distinct salary
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);This returns the maximum salary below the overall maximum. It returns NULL if there is no lower non-NULL salary.
Count employees in each department
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;Return the top three distinct salary levels
WITH ranked AS (
SELECT employee_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
)
SELECT employee_name, salary
FROM ranked
WHERE salary_rank <= 3
ORDER BY salary DESC;7. A repeatable method for answering query questions
- Clarify the output: which columns and how many rows are expected?
- Identify the grain: employee, department, or another level?
- Choose operations: filtering, joining, grouping, ranking, or a combination.
- Write a readable query: use aliases and format clauses clearly.
- Test edge cases: duplicates, NULLs, ties, and empty results.
- Explain assumptions: describe why the query works.
8. A seven-day starter plan
| Day | Focus | Practice |
|---|---|---|
| 1 | SELECT and filtering | Write five WHERE queries |
| 2 | Sorting and NULLs | Practise ORDER BY and IS NULL |
| 3 | Joins | Write INNER and LEFT JOIN queries |
| 4 | Aggregation | Use GROUP BY and HAVING |
| 5 | Subqueries and CTEs | Rewrite a nested query as a CTE |
| 6 | Window functions | Compare RANK and DENSE_RANK |
| 7 | Mock interview | Solve four problems aloud |
9. Further learning resources
- MySQL Reference Manual — syntax and behaviour for MySQL.
- PostgreSQL Documentation — SQL features and database concepts.
- SQLBolt — interactive SQL lessons and exercises.
This is an educational starter guide, not a guarantee of interview questions or placement outcomes. Examples use generic table names; adapt them to your database engine.
Conclusion
Strong SQL interview preparation comes from understanding each clause, practising on data, and communicating your reasoning. Start with the basics, then add joins, aggregation, subqueries, and window functions to your routine.