SQL scenario-based questions ~ Interview Questions and Answers

Here are the answers to each of the SQL scenario-based questions provided earlier, along with explanations for clarity.

Scenario 1: Employee Database

Question: Find the top 3 highest-paid employees in the company.


SELECT TOP 3 * 
FROM Employees 
ORDER BY Salary DESC;

Explanation: This query selects all columns from the Employees table and sorts the results by Salary in descending order, limiting the results to the top 3 entries.

Scenario 2: Sales Data Analysis

Question: Calculate the total sales quantity for each product for the month of January 2024.


SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
WHERE SaleDate >= '2024-01-01' AND SaleDate < '2024-02-01'
GROUP BY ProductID;

Explanation: This query filters sales within January 2024 and groups the results by ProductID to calculate the total quantity sold for each product.

Scenario 3: Product Inventory

Question: Find the products that are low in stock (less than 10 units) and sort them by Category.


SELECT * 
FROM Products 
WHERE StockQuantity < 10 
ORDER BY Category;

Explanation: This query retrieves all products with a stock quantity of less than 10 and sorts the results by Category.

Scenario 4: Customer Orders

Question: List all customers who have placed more than 5 orders, along with the number of orders they have placed.


SELECT c.CustomerID, c.CustomerName, COUNT(o.OrderID) AS OrderCount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
HAVING COUNT(o.OrderID) > 5;

Explanation: This query joins the Customers and Orders tables, counts the number of orders per customer, and filters to show only those with more than 5 orders.

Scenario 5: Student Grades

Question: Find the average grade per subject.


SELECT Subject, AVG(Grade) AS AverageGrade
FROM Students
GROUP BY Subject;

Explanation: This query groups the students by Subject and calculates the average grade for each subject.

Scenario 6: Event Attendance

Question: Find the total number of attendees for each event.


SELECT EventID, COUNT(AttendeeID) AS TotalAttendees
FROM EventAttendance
GROUP BY EventID;

Explanation: This query counts the number of attendees for each event by grouping the results by EventID.

Scenario 7: Purchase History

Question: Find the total purchase amount for each customer for the year 2023.


SELECT CustomerID, SUM(TotalAmount) AS TotalPurchase
FROM Purchases
WHERE YEAR(PurchaseDate) = 2023
GROUP BY CustomerID;

Explanation: This query sums the purchase amounts for each customer, filtering the results to only include purchases made in 2023.

Scenario 8: Department Budgets

Question: Find departments whose budget is higher than the average budget of all departments.


SELECT * 
FROM Departments 
WHERE Budget > (SELECT AVG(Budget) FROM Departments);

Explanation: This query selects departments where the budget exceeds the average budget of all departments using a subquery.

Scenario 9: Social Media Followers

Question: Find users who have more than 100 followers.


SELECT UserID, COUNT(FollowerID) AS FollowerCount
FROM Followers
GROUP BY UserID
HAVING COUNT(FollowerID) > 100;

Explanation: This query counts the number of followers for each user and filters to show only those with more than 100 followers.

Scenario 10: Order History

Question: Find the orders that have been shipped (Status = 'Shipped') in the last 30 days.


SELECT * 
FROM Orders 
WHERE Status = 'Shipped' AND OrderDate >= DATEADD(DAY, -30, GETDATE());

Explanation: This query filters the Orders table for shipped orders within the last 30 days.

Scenario 11: Sales Performance

Question: Determine which sales representative has the highest total sales amount for the current year.


SELECT SalesRepID, SUM(Amount) AS TotalSales
FROM Sales
WHERE YEAR(SalesDate) = YEAR(GETDATE())
GROUP BY SalesRepID
ORDER BY TotalSales DESC
LIMIT 1; -- Use TOP 1 in SQL Server

Explanation: This query sums sales amounts for each sales representative for the current year and sorts the results to find the top representative.

Scenario 12: Employee Hierarchy

Question: Find all employees who report directly to a specific manager (e.g., ManagerID = 1).


SELECT * 
FROM Employees 
WHERE ManagerID = 1;

Explanation: This query selects all employees with a ManagerID of 1, indicating they report directly to that manager.

Scenario 13: Inventory Management

Question: List items that need to be reordered (Quantity < ReorderLevel).


SELECT * 
FROM Inventory 
WHERE Quantity < ReorderLevel;

Explanation: This query retrieves items from the Inventory table that have a quantity below their reorder level.

Scenario 14: Customer Feedback

Question: Find the average rating given by customers.


SELECT AVG(Rating) AS AverageRating
FROM Feedback;

Explanation: This query calculates the average rating from the Feedback table.

Scenario 15: Duplicate Records

Question: Find duplicate email addresses in the Users table.


SELECT Email, COUNT(*) AS DuplicateCount
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;

Explanation: This query groups the users by email and counts occurrences, filtering to show only those with duplicates.

Scenario 16: Blog Posts

Question: Retrieve the most recent blog post for each author.


WITH RankedPosts AS (
    SELECT PostID, AuthorID, Title, PublishDate,
           ROW_NUMBER() OVER (PARTITION BY AuthorID ORDER BY PublishDate DESC) AS PostRank
    FROM BlogPosts
)
SELECT PostID, AuthorID, Title, PublishDate
FROM RankedPosts
WHERE PostRank = 1;

Explanation: This query uses a Common Table Expression (CTE) to rank blog posts for each author and retrieves the most recent post.

Scenario 17: Seasonal Sales

Question: Find the month with the highest total sales in the last year.


SELECT MONTH(SaleDate) AS SaleMonth, SUM(Amount) AS TotalSales
FROM Sales
WHERE SaleDate >= DATEADD(YEAR, -1, GETDATE())
GROUP BY MONTH(SaleDate)
ORDER BY TotalSales DESC
LIMIT 1; -- Use TOP 1 in SQL Server

Explanation: This query sums sales amounts grouped by month for the last year and retrieves the month with the highest total sales.

Scenario 18: User Roles

Question: Count the number of users for each role.


SELECT Role, COUNT(*) AS UserCount
FROM Users
GROUP BY Role;

Explanation: This query groups users by their role and counts the number of users in each role.

Scenario 19: Loan Management

Question: Find the total loan amount for loans that are currently active.


SELECT SUM(LoanAmount) AS TotalActiveLoans
FROM Loans
WHERE LoanStatus = 'Active';

Explanation: This query sums the loan amounts for loans that have an active status.

Scenario 20: Price Changes

Question: Find products whose prices have increased by more than 20% since last month.


SELECT p.*
FROM Products p
JOIN ProductsHistory ph ON p.ProductID = ph.ProductID
WHERE p.Price > ph.PreviousPrice * 1.2
AND ph.RecordedDate = DATEADD(MONTH, -1, GETDATE());

Explanation: This query joins the current Products table with a hypothetical ProductsHistory table to find products with price increases greater than 20% compared to the previous month.

These answers and explanations should help you understand how to approach each SQL scenario effectively!

Interview Questions and Answers

Friday, 1 November 2024

SQL scenario-based questions