Top 50 SQL Interview Questions Every Data Analyst Should Know

1. What is SQL, and why is it important in data analytics?

SQL is a standard programming language specifically designed for managing and manipulating relational databases. It is important because it allows analysts to extract, filter, and transform massive volumes of raw data—often millions of rows—far faster and more reliably than spreadsheet software like Excel.

2. What is the difference between SQL and NoSQL?

SQL databases are relational and use structured, tabular schemas (e.g., rows and columns). NoSQL databases are non-relational, flexible, and store data in document, key-value, or graph formats (e.g., JSON files), making them better suited for unstructured or rapidly changing data.

3. What are the different subsets of SQL commands?

• DDL (Data Definition Language): Defines structure (CREATE, ALTER, DROP).
• DML (Data Manipulation Language): Manipulates data (INSERT, UPDATE, DELETE).
• DQL (Data Query Language): Retrieves data (SELECT).
• DCL (Data Control Language): Manages permissions (GRANT, REVOKE).

4. What is the difference between DELETE and TRUNCATE?

DELETE is a DML command that removes specific rows based on a WHERE clause and can be rolled back. TRUNCATE is a DDL command that instantly removes all rows from a table, resets the identity counter, and cannot easily be rolled back.

5. How do you select all unique values from a column?

Use the DISTINCT keyword.

6. What is the IN operator used for?

It allows you to specify multiple exact values in a WHERE clause, replacing the need for multiple OR conditions.

7. How do you search for a specific pattern in a string?

Use the LIKE operator combined with wildcard characters: % (represents zero or more characters) and _ (represents exactly one character).

8. What is the difference between ORDER BY and GROUP BY?

ORDER BY simply sorts the final output rows alphabetically or numerically. GROUP BY mathematically collapses rows that share the same values into summary rows (e.g., calculating the total sales per city).

9. How do you limit the number of rows returned by a query?

Use the LIMIT clause (in PostgreSQL/MySQL) or the TOP clause (in SQL Server).

10. What does the COALESCE() function do?

It evaluates a list of arguments and returns the first non-null value. It is critical for handling missing data to prevent mathematical errors.

11. What is a primary key?

A primary key is a column (or combination of columns) that uniquely identifies every row in a table. It cannot contain NULL values and must be entirely unique (e.g., an Employee ID).

12. What is a foreign key?

A foreign key is a column in one table that links directly to the primary key of another table, establishing a relationship and enforcing referential integrity between the datasets.

13. Explain the main types of SQL Joins.

• INNER JOIN: Returns only rows where there is a match in both tables.
• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table (unmatched right rows return NULL).
• RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left.
• FULL OUTER JOIN: Returns all rows when there is a match in either the left or the right table.

14. What happens if you join two tables without specifying a join condition?

You create a Cartesian product (a Cross Join). Every row from the first table is paired with every row from the second table, resulting in a massive, often system-crashing explosion of data.

15. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts every single row in the result set, including rows with NULL values. COUNT(column_name) counts only the rows where that specific column has a non-null value.

16. What is the difference between the WHERE clause and the HAVING clause?

WHERE filters individual rows before any grouping or aggregation takes place. HAVING filters aggregated data after the GROUP BY clause has been applied.

17. Write a query to find departments where the average salary is greater than $60,000.

18. What is a Self Join?

A self join is a regular join where a table is joined to itself. It is commonly used to query hierarchical data within the same table, such as finding the name of an employee's manager when both are listed in the same employees table.

19. How do you combine the results of two different queries into a single output?

Use the UNION operator. The queries must have the same number of columns and compatible data types. UNION removes duplicate rows, while UNION ALL keeps all duplicates.

20. Why might a LEFT JOIN artificially inflate your row count?

If the table on the right side of the join contains duplicate keys for a single record on the left side, the left record will be duplicated for every match found on the right, inflating aggregations like SUM().

21. What is a Subquery?

A subquery (or inner query) is a query nested inside another query (like SELECT, INSERT, UPDATE, or DELETE). It is executed first, and its result is passed to the outer query.

22. What is a Correlated Subquery?

Unlike a standard subquery that runs once, a correlated subquery depends on the outer query for its values and must be evaluated repeatedly, once for every single row processed by the outer query. This makes it notoriously slow.

23. What is a CTE (Common Table Expression)?

A CTE is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It acts like a temporary view that only exists for the duration of that specific query.

24. Why are CTEs generally preferred over Subqueries?

CTEs significantly improve code readability. They allow you to break massive, nested logical steps into chronological blocks that can be read top-to-bottom, making the code much easier for other analysts to debug.

25. How do you implement conditional logic (If/Then) in SQL?

Use the CASE WHEN statement.

26. Write a query using a CTE to find the second highest salary.

27. What is conditional aggregation?

It is the process of using CASE WHEN statements inside an aggregate function (like SUM or COUNT) to pivot data or calculate multiple distinct metrics in a single pass over a table.

28. Give an example of conditional aggregation.

29. What does the EXISTS operator do?

The EXISTS operator is used to test for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more records, and it is highly optimized for performance compared to IN.

30. How do you handle string manipulation in SQL?

Using built-in string functions such as CONCAT() (to join strings), SUBSTRING() (to extract parts of a string), TRIM() (to remove spaces), and LOWER()/UPPER() (to change case).

31. What is the fundamental difference between GROUP BY and a Window Function?

GROUP BY collapses the individual rows into a single aggregated summary row. A window function performs the aggregation but leaves the original rows intact, appending the calculation as a new column next to the existing data.

32. What does the OVER() clause do?

The OVER() clause defines the specific "window" or set of rows that the window function operates on. It dictates how the data is partitioned (grouped) and ordered before the calculation is applied.

33. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

• ROW_NUMBER(): Assigns a unique sequential integer to rows. If there is a tie, it breaks the tie arbitrarily (1, 2, 3, 4).
• RANK(): Assigns the same rank to tied values, but leaves a gap in the sequence afterward (1, 2, 2, 4).
• DENSE_RANK(): Assigns the same rank to tied values without leaving any gaps in the sequence (1, 2, 2, 3).

34. When would you use LEAD() and LAG()?

These functions are used to compare values in the current row with values in a subsequent row (LEAD) or a previous row (LAG). They are essential for calculating month-over-month growth, time between purchases, or tracking sequential status changes.

35. Write a query to calculate the Year-Over-Year (YoY) revenue difference using LAG().

36. What is a running total, and how do you calculate it?

A running total calculates the cumulative sum of a metric over time.

37. How do you calculate a moving average (e.g., a 3-day moving average)?

You use the ROWS BETWEEN framing clause within a window function.

38. What does NTILE(n) do?

It distributes the rows in an ordered partition into a specified number of approximately equal groups (e.g., NTILE(4) creates quartiles, NTILE(100) creates percentiles).

39. How do you find the first or last record in a dataset using window functions?

Use FIRST_VALUE(column) or LAST_VALUE(column) combined with the OVER() clause to pull the specific value from the top or bottom of the defined window partition.

40. Why might LAST_VALUE() not work as expected without specific framing?

By default, the window frame for ORDER BY stops at the CURRENT ROW. Therefore, LAST_VALUE() will just return the current row's value unless you explicitly define the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

41. What is an index, and why is it used?

An index is a database structure that improves the speed of data retrieval operations on a table, much like the index at the back of a book. Without an index, the database must perform a slow "full table scan" to find a specific row.

42. Why wouldn't you just put an index on every column?

While indexes speed up SELECT retrieval queries, they significantly slow down INSERT, UPDATE, and DELETE commands because the database must update the index structure every time the raw data changes. They also consume heavy disk space.

43. What is query optimization, and how do you approach it?

It is the process of writing SQL that minimizes execution time and server load. I optimize by looking at the execution plan (EXPLAIN), filtering data early using WHERE before joining, avoiding SELECT *, replacing slow subqueries with CTEs, and ensuring joins operate on indexed keys.

44. What does the EXPLAIN command do?

It returns the execution plan that the database engine will use to run your query, detailing how it accesses tables, the order of operations, and the estimated computational cost, allowing analysts to identify bottlenecks like full table scans.

45. How do you handle date and time extraction?

Using functions like EXTRACT(MONTH FROM date_column) or DATE_PART(), and DATE_TRUNC() to round timestamps down to the nearest day, month, or quarter for aggregation.

46. What is string casting, and when do you use it?

Casting changes a data type from one form to another (e.g., CAST(column AS VARCHAR)). It is essential when joining tables where a numeric ID is stored as an integer in one table and as a text string in the other.

47. How do you identify duplicate records without deleting them?

By grouping by all relevant columns and filtering for a count greater than 1.

48. How do you calculate a retention rate?

Retention calculations require identifying a specific cohort (e.g., users who signed up in January), tracking their unique user IDs, and joining that cohort back to subsequent activity tables in later months to measure the percentage of users who returned.

49. What is a View in SQL?

A View is a virtual table based on the result-set of an SQL statement. It contains rows and columns just like a real table, but it does not store the data physically. It is used to simplify complex queries and restrict access to sensitive underlying data.

50. What is a Stored Procedure?

A stored procedure is a prepared, pre-compiled SQL code segment that you can save and reuse over and over again. Unlike a View, it can accept parameters and execute complex procedural logic (like loops and variable assignments) for automated tasks.