SQL Theory Questions

My plan is to maintain all questions of SQL here. Everyone who is in the journey of new job hunt feel incomplete in the preperation and I'd like to help them at this place.

Most Asked SQL Questions in Interviews.

1. How would you optimize a slow-running query with multiple joins?
2. What is a recursive CTE, and can you provide an example of when to use it?
3. Explain the difference between clustered and non-clustered indexes and when to use each.
4. Write a query to find the second-highest salary in each department.
5. How would you detect and resolve deadlocks in SQL?
6. Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK.
7. Describe the ACID properties in database transactions and their significance.
8. Write a query to calculate a running total with partitions based on specific conditions.

1. What is the output of INNER JOIN, LEFT JOIN, and RIGHT JOIN, and how many rows will they produce?
2. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
3. Explain the use of window functions in SQL and how they were applied in your project?
4. How do you handle missing or corrupted data in large datasets?
5. How do you ensure data quality and consistency in a distributed system?
6. Explain how you would use a window function to calculate running totals or rank data in Spark?

Basic Window Function Questions

1. What are SQL window functions, and how are they different from aggregate functions?
2. Explain the syntax of a window function in SQL.
3. What is the purpose of the `OVER()` clause in window functions?
4. What is the difference between `PARTITION BY` and `GROUP BY`?
5. How does the `ORDER BY` clause affect window functions?
6. Write a query to calculate a running total of sales.
7. How do window functions handle NULL values?

Ranking Functions

1. What is the difference between `RANK()`, `DENSE_RANK()`, and `ROW_NUMBER()`?
2. Write a query to assign employee ranks based on their salaries within each department.
3. How would you use `NTILE()` to divide data into 4 equal groups?
4. What happens when there are ties in `RANK()` or `ROW_NUMBER()`?
5. Write a query to fetch each region's top 3 sales records using `ROW_NUMBER()`.

Aggregate Functions with Windows

1. How do you calculate the average salary of employees within each department using window functions?
2. Write a query to find the cumulative sum of sales for each product category.
3. How do you calculate a moving average using window functions?
4. Explain the difference between `SUM()` with `OVER()` and `SUM()` with `GROUP BY`.
5. Write a query to calculate the difference between a row’s value and the average value of its partition.

Lead and Lag Functions

1. What is the purpose of `LEAD()` and `LAG()` in SQL?
2. Write a query to calculate the difference between a row's value and the previous row’s value.
3. How would you retrieve the next and previous order date for each customer?
4. Explain how `LEAD()` and `LAG()` can be used to detect trends in time-series data.
5. Write a query to calculate the percentage change in sales from the previous day.

Frame-Specific Questions

1. What is the purpose of the `ROWS` and `RANGE` clauses in window functions?
2. Write a query to calculate the sum of sales over a 3-day sliding window.
3. How would you calculate a year-to-date total using window functions?
4. What is the difference between `ROWS BETWEEN` and `RANGE BETWEEN` in window functions?
5. Write a query to find the maximum value within a specific frame for each row.

Real-World Scenarios

1. Write a query to find the 2nd highest salary in each department.
2. How would you use window functions to calculate retention rates for customers?
3. Explain how window functions can be used to identify outliers in sales data.

1. How would you optimize a slow-running query with multiple joins?
2. What is a recursive CTE, and can you provide an example of when to use it?
3. Explain the difference between clustered and non-clustered indexes and when to use each.
4. How would you detect and resolve deadlocks in SQL?
5. Explain window functions and provide examples of ROW_NUMBER, RANK, and DENSE_RANK.
6. Describe the ACID properties in database transactions and their significance.

Source: LinkedIn Posts...