Important Scenario-Based SQL Interview Questions Every Developer Should Know

By /

Preparing SQL is not just about writing queries but solving real-world business problems. Below are 30 scenario-based SQL interview questions categorized by difficulty:

Basic-Level Questions:

  1. Find the second highest salary in an employee table.

2. Detect duplicate records and remove them efficiently.

  1. Retrieve employees who joined in the last 6 months.
  2. Find customers who placed more than three orders in a month.

5. Identify the top-selling product in each category.

  1. Write a query to calculate the running total of sales per day.
  2. Get the department with the highest average salary.
  3. Find employees who haven’t logged in for the last 30 days.
  4. Display the first and last order of every customer.
  5. Find gaps in a sequence of invoice numbers.

Intermediate-Level Questions:

11. Retrieve products that have never been ordered.

  1. Get the manager with the most direct reports.
  2. Identify overlapping booking times for a hotel room.
  3. Find employees who share the same salary but work in different departments.
  4. Write a query to pivot sales data by month.
  5. Retrieve customers who have not made any purchases in the last year.
  6. Find the longest streak of consecutive order days for a customer.
  7. Identify orders where the total amount is higher than the average order value.
  8. Find products that were never restocked after being sold out.
  9. Retrieve the top three customers based on total spending.

Expert-Level Questions:

  1. Write a recursive query to find all employees reporting to a specific manager (hierarchical data).
  2. Detect and fix data integrity issues caused by missing foreign key relationships.
  3. Optimize a slow query using indexing and query execution plans.
  4. Identify and remove orphaned records from a database.
  5. Design a query to handle multi-tenant database partitioning efficiently.
  6. Write a query to implement a sliding window aggregation over time-series data.
  7. Generate a cohort analysis report based on customer retention.
  8. Create a stored procedure to dynamically generate a pivot table.
  9. Identify and fix performance bottlenecks in a large dataset query.
  10. Write a query to split a comma-separated string into rows in SQL.

For detailed solutions and preparation, check out the SQL Interview Preparation Kit: SQL Interview Preparation Kit

Practice Verified Codes and Commands:

1. Find the Second Highest Salary:

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

2. Remove Duplicate Records:

DELETE FROM employees 
WHERE id NOT IN (
SELECT MIN(id) 
FROM employees 
GROUP BY name, salary
);
  1. Employees Who Joined in the Last 6 Months:
    SELECT * 
FROM employees 
WHERE join_date >= DATEADD(MONTH, -6, GETDATE());

4. Top-Selling Product in Each Category:

SELECT category, product, MAX(sales) 
FROM sales 
GROUP BY category;

5. Running Total of Sales Per Day:

SELECT sale_date, amount, 
SUM(amount) OVER (ORDER BY sale_date) AS running_total 
FROM sales;

6. Recursive Query for Hierarchical Data:

WITH RECURSIVE EmployeeHierarchy AS (
SELECT id, name, manager_id 
FROM employees 
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id 
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

What Undercode Say:

SQL is a powerful tool for managing and manipulating data, and mastering it is essential for any developer working with databases. The questions listed above cover a wide range of scenarios, from basic to expert-level, ensuring that you are well-prepared for real-world challenges.

In addition to SQL, it’s important to be familiar with related technologies and commands. For instance, in Linux, you can use commands like grep, awk, and `sed` to manipulate text files, which can be useful when working with database exports. For example:

  • Search for a pattern in a file:
    grep "pattern" filename

  • Extract specific columns from a CSV file:

    awk -F, '{print $1, $3}' filename.csv

  • Replace text in a file:

    sed 's/old-text/new-text/g' filename

For Windows users, PowerShell commands can be equally powerful:

  • Search for a string in files:
    Select-String -Path "*.log" -Pattern "error"

  • Export data to a CSV file:

    Get-Process | Export-Csv -Path processes.csv

  • Filter and sort processes:

    Get-Process | Sort-Object CPU -Descending | Select-Object -First 10

To further enhance your SQL skills, consider exploring advanced topics like indexing, query optimization, and database partitioning. Resources like SQL Performance Explained and Database Administrators Stack Exchange can be invaluable.

In conclusion, SQL is not just about writing queries; it’s about understanding data and solving problems efficiently. By practicing the questions and commands provided, you’ll be well-equipped to tackle any SQL-related challenge in your career. Keep learning, keep practicing, and always strive to improve your skills.

For more resources, visit:

References:

Hackers Feeds, Undercode AIFeatured Image

Related Posts:

Scroll to Top