SQL Interview Questions and Answers (Freshers to Advanced)

These sql interview questions and answers span freshers to advanced developers — covering joins, group by, subqueries, indexes, normalization, window functions and query tuning — with concise, correct model answers and short queries you can adapt during the interview.

Beginner sql interview questions

Q: What is the difference between DELETE, TRUNCATE and DROP?

A: DELETE removes rows one at a time and can use a WHERE clause, and it is logged and can be rolled back. TRUNCATE removes all rows quickly with minimal logging and usually cannot be filtered. DROP removes the entire table structure and its data. DELETE and TRUNCATE keep the table; DROP does not.

Q: What is the difference between WHERE and HAVING?

A: 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. In short, WHERE runs first on raw rows and HAVING runs later on grouped results.

Q: What are the main types of JOIN?

A: INNER JOIN returns rows matching in both tables. LEFT JOIN returns all left rows plus matches from the right, with NULLs where none exist, and RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns the Cartesian product of the two tables.

Q: What is the difference between UNION and UNION ALL?

A: UNION combines the result sets of two queries and removes duplicate rows, which requires a sort or hash and costs performance. UNION ALL keeps all rows including duplicates and is faster. Use UNION ALL when you know there are no duplicates or duplicates are acceptable.

Q: What is a primary key versus a foreign key?

A: A primary key uniquely identifies each row in a table and cannot be NULL. A foreign key is a column that references the primary key of another table, enforcing referential integrity so you cannot insert a child row pointing to a non-existent parent.

Q: How do you find distinct values?

A: Use the DISTINCT keyword to remove duplicate rows from the selected columns.

SELECT DISTINCT department
FROM employees;

Q: What does GROUP BY do?

A: GROUP BY collapses rows that share values in the listed columns into summary rows, so aggregate functions like COUNT, SUM, AVG, MIN and MAX are computed per group. Every non-aggregated column in the SELECT must appear in the GROUP BY clause.

Intermediate sql interview questions

Q: What is an index and how does it help?

A: An index is a data structure, usually a B-tree, that lets the database find rows without scanning the whole table, speeding up lookups, joins and sorts on the indexed columns. The trade-off is extra storage and slower inserts and updates because indexes must be maintained.

Q: What is the difference between a clustered and a non-clustered index?

A: A clustered index determines the physical order of rows in the table, so there can be only one per table, and the leaf level is the data itself. A non-clustered index is a separate structure that stores a pointer to the row, and a table can have many of them.

Q: How do you find the second-highest salary?

A: A reliable, portable approach uses a subquery to exclude the maximum.

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

Q: What is a correlated subquery?

A: A correlated subquery references a column from the outer query, so it is evaluated once per outer row rather than once overall. This makes it flexible but potentially slow on large tables. It can often be rewritten as a join or a window function for better performance.

Q: What is the difference between CHAR and VARCHAR?

A: CHAR is a fixed-length type that pads values with spaces to the declared length, so it suits codes of constant size. VARCHAR is variable length and stores only the actual characters plus a small length prefix, which saves space for values whose length varies.

Q: What are aggregate functions and can they be nested?

A: Aggregate functions such as COUNT, SUM, AVG, MIN and MAX compute a single value over a set of rows. You cannot nest aggregates directly, for example MAX of AVG, in one level; you achieve that with a subquery or a common table expression that computes the inner aggregate first.

Q: What is the difference between IN and EXISTS?

A: IN checks whether a value matches any in a list or subquery result and can struggle with large result sets or NULLs. EXISTS returns true as soon as the subquery yields one row, so it can stop early and often performs better for correlated checks. For matching against a small static list, IN is clearer.

Advanced sql interview questions

Q: What is a window function?

A: A window function performs a calculation across a set of rows related to the current row without collapsing them, unlike GROUP BY. Functions like ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD run over a window defined by PARTITION BY and ORDER BY, keeping each row in the output.

SELECT name, department,
       RANK() OVER (PARTITION BY department
                    ORDER BY salary DESC) AS r
FROM employees;

Q: What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

A: ROW_NUMBER gives every row a unique sequential number. RANK gives tied rows the same rank but leaves gaps afterward, so two firsts are followed by rank three. DENSE_RANK also ties equal rows but leaves no gaps, so the next rank is two.

Q: What is normalization and why is it used?

A: Normalization organises tables to reduce redundancy and prevent update anomalies by splitting data into related tables. First normal form removes repeating groups, second removes partial dependencies on part of a composite key, and third removes transitive dependencies. It improves integrity but may require joins that denormalization sometimes trades away for speed.

Q: What is a common table expression (CTE)?

A: A CTE is a named temporary result set defined with the WITH keyword that exists only for the duration of one statement. It improves readability, avoids repeating subqueries and enables recursion for hierarchical data such as organisation charts or category trees.

Q: How do you approach tuning a slow query?

A: Start by reading the execution plan to find full scans, expensive sorts or nested loops on large tables. Add or adjust indexes on filtered and joined columns, avoid functions on indexed columns in the WHERE clause, select only needed columns, and check that statistics are current. Measure before and after each change.

Q: What is a transaction and what are ACID properties?

A: A transaction is a unit of work that must complete fully or not at all. ACID stands for Atomicity, meaning all or nothing; Consistency, keeping data valid; Isolation, so concurrent transactions do not interfere; and Durability, so committed changes survive failures. These guarantees keep multi-step operations reliable.

Q: What is the difference between a stored procedure and a view?

A: A view is a saved query that behaves like a virtual table and is used for reading, simplifying complex joins and controlling access. A stored procedure is precompiled code that can accept parameters, contain logic and modify data. Use views for reusable read queries and procedures for reusable operations.

How to prepare for an SQL interview

Write queries by hand rather than relying on autocomplete, and practise turning plain-English questions into SQL under time pressure. Drill joins, group by with having, window functions and index reasoning, and be able to explain an execution plan. Many data and backend roles test both, so pair this with our Python interview questions. Freshers should also read our apprenticeship and Skill India guide and browse live roles on the GetJobsNews homepage.

Frequently Asked Questions