SQL Scenario Based Interview Questions and Answers

1. Employee Department Retrieval:

• Question: You have Employees and Departments tables. Write a query to list all employees working in the ‘Marketing’ department.
• Answer: Combine Employees with Departments using a JOIN on department ID, filtering the results to only include the ‘Marketing’ department.

2. Retrieving Orders with Optional Customer Data:

• Question: How can you fetch all orders, including those with no corresponding customer details?
• Answer: Use a LEFT JOIN between Orders and Customers to ensure all orders are displayed, even when customer data is missing.

3. Top-Selling Products:

• Question: Write a query to identify the top 5 products with the highest sales in the past 6 months.
• Answer: Aggregate sales data by product and sort it in descending order of total sales, then limit the results to the top 5.

4. Employees Above Average Salary:

• Question: How can you find employees whose salaries exceed the average salary in their department?
• Answer: Use a subquery to calculate the average salary for each department, then filter employees whose salary is above this average.

5. Calculating Cumulative Sales:

• Question: How would you compute the cumulative sales for each product over time?
• Answer: Apply a window function to sum the sales, ordered by date, partitioned by product.

6. Replacing NULL Values:

• Question: How can you substitute NULL values in a column with ‘Unknown’?
• Answer: Utilize the COALESCE function to replace any NULL values with ‘Unknown’.

7. Identifying Recent Customers:

• Question: How do you identify customers who placed orders within the last 30 days?
• Answer: Filter the orders by the order date, ensuring it falls within the last 30 days, and join with the Customers table.

8. Counting Customer Orders:

• Question: Write a query to count how many orders each customer has placed.
• Answer: Group the data by customer and use the COUNT function to tally their orders.

9. Filtering Groups by Total Salary:

• Question: How do you find departments where the combined salary exceeds $200,000?
• Answer: Group by department and use the HAVING clause to filter those with a total salary over $200,000.

10. String Date to Date Conversion:

• Question: How can you convert a text-based date ‘YYYYMMDD’ to a date format and filter records accordingly?
• Answer: Use conversion functions to transform the string into a date type, then apply your filtering criteria.

11. Extracting Substrings:

• Question: How do you extract the first 5 characters of each employee’s first name?
• Answer: Use string functions like LEFT or SUBSTRING to capture the first 5 characters.

12. Calculating Date Differences:

• Question: How can you determine the number of days between a purchase date and a shipping date?
• Answer: Use date difference functions to subtract the purchase date from the shipping date.

13. Finding Employees with the Same Manager:

• Question: How do you identify employees who share the same manager?
• Answer: Perform a self-join on the Employees table where the manager ID matches.

14. Finding the Second-Highest Salary:

• Question: How can you determine the second-highest salary in a company?
• Answer: Use a subquery to exclude the highest salary and find the maximum of the remaining salaries.

15. Difference Between UNION and UNION ALL:

• Question: Explain the difference between UNION and UNION ALL. When would you use each?
• Answer: UNION removes duplicates while UNION ALL includes all records. Use UNION when uniqueness is required, and UNION ALL when all results, including duplicates, are needed.

16. Simplifying Queries with CTEs:

• Question: How can you simplify a complex query that includes multiple subqueries?
• Answer: Use a Common Table Expression (CTE) to define the subquery and reference it within the main query.

17. Using Recursive CTEs:

• Question: How do you create a hierarchy of employees and their reporting managers using a recursive CTE?
• Answer: Start with the base case for top-level managers and recursively join to build the employee hierarchy.

18. Ranking Products by Sales:

• Question: How can you rank products by sales volume within each category?
• Answer: Use the RANK() function with partitioning by category and ordering by sales to assign ranks.

19. Categorizing Employees by Salary:

• Question: How would you categorize employees as ‘Low’, ‘Medium’, or ‘High’ earners based on their salary?
• Answer: Use a CASE statement to define ranges for each salary category.

20. Preventing Deletions with Constraints:

• Question: How can you prevent deletion of a product if it has associated orders?
• Answer: Implement a foreign key constraint with ON DELETE RESTRICT to enforce this rule.

21. Optimizing Query Performance with Indexes:

• Question: What strategies would you use to speed up a query that is performing slowly due to a full table scan?
• Answer: Create indexes on frequently used columns in WHERE clauses or JOIN conditions to improve query performance.

22. Using Temporary Tables:

• Question: When should you use a temporary table, and how would you create one?
• Answer: Temporary tables are useful for storing intermediate results in complex queries. They are created with a specific command and exist only for the duration of the session.

23. Ensuring Transactional Integrity:

• Question: How do you ensure that a set of SQL operations either all succeed or fail together?
• Answer: Use transaction control commands to begin, commit, or roll back transactions, ensuring atomicity.

24. Handling Deadlocks in Transactions:

• Question: What should you do if a deadlock occurs during a transaction?
• Answer: Identify the cause of the deadlock, optimize the transactions, and implement error handling to retry or roll back transactions.

25. Automatically Updating Timestamps:

• Question: How can you set up a system where a timestamp is automatically updated whenever a row is modified?
• Answer: Use a trigger that activates after an update, setting the timestamp column to the current time.

26. Inserting Data with Stored Procedures:

• Question: Write a stored procedure to insert a new record into the Employees table and return the new employee ID.
• Answer: Create a stored procedure with input parameters for the employee details and an output parameter for the new ID. Insert the data and return the ID.

27. Normalizing a Database:

• Question: How would you normalize a database to minimize redundancy and maintain data integrity?
• Answer: Break down tables to eliminate repeating groups, creating separate tables for distinct entities, and enforce relationships with foreign keys.

28. Deciding When to Denormalize:

• Question: In what situations might you denormalize a database, and how would you do it?
• Answer: Denormalization is useful in read-heavy systems where query performance is critical. It involves combining tables or adding redundant data to reduce the need for JOINs.

29. Using PIVOT to Transform Data:

• Question: How would you convert rows into columns, such as turning sales data by month into separate columns for each month?
• Answer: Use the PIVOT function to transform row data into columns, specifying the values to pivot on.

30. Handling Complex Filtering Logic:

• Question: How do you write a query to filter records based on multiple complex conditions, such as combining AND/OR logic with specific value checks?
• Answer: Use a combination of WHERE clauses and parentheses to correctly structure the logical conditions, ensuring the intended filtering is applied.