Top 15 SQL Interview Questions for Data Analysts in India

Basic SQL Concepts: Laying the Foundation

These questions test your understanding of core SQL operations. Expect these in the initial screening or technical round.

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

This is a classic warm-up question.

DELETE: A Data Manipulation Language (DML) command. It removes specific rows based on a WHERE clause. It is slower because it logs individual row deletions and can be rolled back.
TRUNCATE: A Data Definition Language (DDL) command. It removes all rows from a table by deallocating the data pages. It is faster than DELETE and cannot be rolled back in most databases.
DROP: A DDL command. It removes the entire table structure from the database, including all data, indexes, and privileges.

2. Explain the different types of JOINS in SQL.

You must be able to explain these clearly, ideally drawing a mental Venn diagram for the interviewer.

INNER JOIN: Returns records that have matching values in both tables.
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. The result is NULL from the right side if there is no match.
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.
CROSS JOIN: Returns the Cartesian product of the sets of records from the two joined tables.

3. What is the difference between `HAVING` and `WHERE`?

WHERE: Used to filter rows before grouping occurs. It cannot be used with aggregate functions.
HAVING: Used to filter records after the GROUP BY clause has grouped the rows. It is specifically designed to work with aggregate functions like SUM(), COUNT(), etc.

4. How do you find duplicate records in a table?

Interviewers love this because it requires combining GROUP BY and HAVING.

SELECT employee_id, COUNT(*)
FROM employees
GROUP BY employee_id
HAVING COUNT(*) > 1;

5. What are Primary Keys and Foreign Keys?

Primary Key: A column (or set of columns) that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
Foreign Key: A column in one table that links to the Primary Key of another table. It is used to enforce referential integrity between the data in two tables.

Intermediate SQL: Query Formulation

These questions require you to write actual queries to solve business logic problems.

6. Write a query to find the second highest salary of an employee.

This is perhaps the most famous SQL interview question in India. There are multiple ways to solve it, but using OFFSET or a subquery is standard. Using Subquery:

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

7. Find employees who earn more than their managers.

Assuming an employees table with employee_id, salary, and manager_id (which refers to employee_id in the same table). You need a self-join.

SELECT e.employee_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

8. Explain the difference between `UNION` and `UNION ALL`.

UNION: Combines the result sets of two or more SELECT statements, but removes duplicate rows. It requires a distinct sort operation, making it slightly slower.
UNION ALL: Also combines result sets, but keeps all duplicates. It is faster because it does not attempt to remove duplicates.

9. Write a query to fetch the first 5 characters of a string.

This tests your knowledge of string manipulation functions, which varies slightly by SQL dialect. Standard / MySQL / PostgreSQL:

SELECT SUBSTRING(employee_name, 1, 5) FROM employees;

10. How would you handle NULL values in a query?

You can handle NULLs using the COALESCE() or ISNULL() functions. COALESCE(column_name, 'Replacement Value') returns the first non-null value in a list of arguments. It is widely used to prevent errors in mathematical operations or concatenation involving NULLs.

Advanced SQL: Analytical and Window Functions

If you are interviewing for mid-level roles or at companies with mature data stacks (like Swiggy, Zomato, or Cred), expect heavy emphasis on Window Functions.

11. What are Window Functions? Give an example.

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not group rows into a single output row; they retain the individual rows. Example: Assigning a row number to employees based on salary within their department.

SELECT employee_name, department, salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

12. Differentiate between `RANK()`, `DENSE_RANK()`, and `ROW_NUMBER()`.

ROW_NUMBER(): Assigns a unique sequential integer to rows, regardless of ties. (e.g., 1, 2, 3, 4).
RANK(): Assigns the same rank to ties, but skips the next numbers. (e.g., 1, 2, 2, 4).
DENSE_RANK(): Assigns the same rank to ties, but does not skip the next numbers. (e.g., 1, 2, 2, 3).

13. How do you calculate a moving average or running total?

This requires using window functions with rows framing. Running Total Example:

SELECT order_date, sales_amount,
SUM(sales_amount) OVER(ORDER BY order_date) as running_total
FROM daily_sales;

14. Explain Common Table Expressions (CTEs). When would you use them?

A CTE (WITH clause) provides a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. Why use them? They make complex, nested queries much more readable and easier to debug than subqueries. Recursive CTEs are also essential for querying hierarchical data (like organizational charts).

15. What is a Stored Procedure? How is it different from a Function?

Stored Procedure: A prepared SQL code that you can save and reuse. It can perform complex business logic, use DML statements (insert/update/delete), and does not necessarily need to return a value.
Function: Must return a single value (or a table). It is generally used for computations and cannot modify database state (no inserts/updates). Functions can be called from within a SELECT statement, while procedures are executed using CALL or EXEC.

Bonus Tips for SQL Interviews in India

Clarify the Dialect: Always ask the interviewer which SQL dialect they prefer (MySQL, PostgreSQL, MS SQL Server, etc.). While core concepts are identical, specific syntax (like date manipulation) varies.
Think Out Loud: When given a complex query problem, do not just stare at the screen. Explain your logic. "First, I need to join these two tables. Then, I'll filter by this date range, and finally, apply a window function..." Even if your syntax has a minor error, solid logic will earn you high marks.
Format Your Code: Write clean, readable SQL. Use uppercase for keywords (SELECT, FROM, WHERE) and proper indentation.
Consider Edge Cases: Mention how your query handles NULL values or duplicate records. This shows maturity as an analyst.

By mastering these 15 questions and practicing on platforms like LeetCode or HackerRank, you will be well-prepared to tackle the SQL round of any Data Analyst interview in India.