In the competitive world of data management and analysis, SQL remains one of the most vital skills for database professionals. During interviews, employers often test candidates with scenario-based questions to assess their problem-solving abilities and practical knowledge of SQL. In this blog, we’ll explore some of the top SQL scenario-based interview questions along with detailed answers to help you prepare effectively.
1. Scenario: Extracting Data with Conditions
Question:
You have a table named Employees with columns EmployeeID, Name, Department, and Salary. Write an SQL query to find the names of employees who work in the ‘HR’ department and have a salary greater than $50,000.
Answer:
sqlCopy codeSELECT Name
FROM Employees
WHERE Department = 'HR' AND Salary > 50000;
Explanation:
This query selects the Name column from the Employees table where the Department is ‘HR’ and the Salary is greater than $50,000. The AND operator ensures that both conditions must be true for a record to be included in the result set.
2. Scenario: Finding Duplicate Records
Question:
You have a table called Orders with columns OrderID, CustomerID, OrderDate, and OrderAmount. Write a query to find duplicate CustomerID values.
Answer:
sqlCopy codeSELECT CustomerID, COUNT(*)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 1;
Explanation:
The query groups the records by CustomerID and counts the number of occurrences for each CustomerID. The HAVING clause filters the results to include only those CustomerIDs that appear more than once, indicating duplicates.
3. Scenario: Aggregating Data
Question:
Given a table Sales with columns SaleID, ProductID, Quantity, and SaleDate, write a query to find the total quantity sold for each product.
Answer:
sqlCopy codeSELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID;
Explanation:
This query aggregates the Quantity column for each ProductID using the SUM function. The GROUP BY clause groups the rows by ProductID, allowing the calculation of the total quantity sold for each product.
4. Scenario: Date Range Queries
Question:
You have a table Transactions with columns TransactionID, AccountID, TransactionDate, and Amount. Write a query to find all transactions that occurred in the last 30 days.
Answer:
sqlCopy codeSELECT *
FROM Transactions
WHERE TransactionDate >= DATEADD(DAY, -30, GETDATE());
Explanation:
The DATEADD function subtracts 30 days from the current date (GETDATE()). The WHERE clause filters the transactions to include only those that occurred within the last 30 days. This query is useful for retrieving recent transactions.
5. Scenario: Updating Records
Question:
You have a table Products with columns ProductID, ProductName, Price, and StockQuantity. Write a query to increase the price of all products by 10% that have less than 100 units in stock.
Answer:
sqlCopy codeUPDATE Products
SET Price = Price * 1.10
WHERE StockQuantity < 100;
Explanation:
The UPDATE statement modifies the Price of products. The SET clause specifies the new price as 10% higher than the current price. The WHERE clause restricts the update to products with a StockQuantity of less than 100 units.
6. Scenario: Deleting Specific Records
Question:
You have a table Users with columns UserID, Username, Email, and Status. Write a query to delete all users whose status is ‘inactive’ and have not logged in for the past year.
Answer:
sqlCopy codeDELETE FROM Users
WHERE Status = 'inactive' AND LastLoginDate < DATEADD(YEAR, -1, GETDATE());
Explanation:
The DELETE statement removes records from the Users table. The WHERE clause ensures that only users with a status of ‘inactive’ and a LastLoginDate older than one year are deleted. The DATEADD function calculates the date one year ago.
7. Scenario: Joining Multiple Tables
Question:
You have two tables, Customers and Orders. The Customers table has columns CustomerID, Name, and City, while the Orders table has columns OrderID, CustomerID, OrderDate, and TotalAmount. Write a query to find the total amount spent by each customer.
Answer:
sqlCopy codeSELECT c.Name, SUM(o.TotalAmount) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name;
Explanation:
The JOIN clause combines the Customers and Orders tables based on the matching CustomerID. The SUM function calculates the total amount spent by each customer. The results are grouped by the customer’s name (c.Name).
8. Scenario: Subqueries
Question:
You have a table Employees with columns EmployeeID, Name, Department, and Salary. Write a query to find the names of employees who earn more than the average salary in their department.
Answer:
sqlCopy codeSELECT Name
FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department);
Explanation:
The subquery (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department) calculates the average salary for each department. The main query retrieves the names of employees whose salary is greater than this average. The correlation between the main query and the subquery is established by the alias e.
9. Scenario: Handling NULL Values
Question:
You have a table Products with columns ProductID, ProductName, Category, and Discount. Write a query to display the ProductName and Discount, replacing NULL discounts with ‘No Discount’.
Answer:
sqlCopy codeSELECT ProductName, ISNULL(CAST(Discount AS VARCHAR), 'No Discount') AS Discount
FROM Products;
Explanation:
The ISNULL function replaces NULL values in the Discount column with the string ‘No Discount’. The CAST function converts the Discount column to a string format, allowing the use of the ‘No Discount’ label.
10. Scenario: Ranking and Window Functions
Question:
You have a table Sales with columns SaleID, ProductID, SaleAmount, and SaleDate. Write a query to rank the sales amounts for each product and display the rank along with the sale details.
Answer:
sqlCopy codeSELECT SaleID, ProductID, SaleAmount,
RANK() OVER (PARTITION BY ProductID ORDER BY SaleAmount DESC) AS SaleRank
FROM Sales;
Explanation:
The RANK() function assigns a rank to each sale amount within the partition of ProductID, ordered by SaleAmount in descending order. The PARTITION BY clause ensures that the ranking is reset for each product.
Recommended Post: QA Testing Training
Conclusion
Scenario-based SQL interview questions are a common way to evaluate a candidate’s practical knowledge and problem-solving abilities. By understanding these scenarios and practicing the associated queries, you can enhance your SQL skills and be better prepared for your next interview. These questions cover a range of topics, including data extraction, aggregation, updates, deletions, and more, providing a comprehensive overview of the skills required for a successful career in database management and analysis.
