Scenario-based SQL Questions

Question: You have a table named "Employees" with columns "EmployeeID," "FirstName," "LastName," and "Salary." You need to retrieve the top three highest-paid employees. How would you write an SQL query for this?

Answer: 

     

     SELECT EmployeeID, FirstName, LastName, Salary

     FROM Employees

     ORDER BY Salary DESC

     LIMIT 3;

     

 

Question: You need to find the total number of orders placed by each customer in the "Orders" table. How would you write an SQL query for this?

   Answer: 

     

     SELECT CustomerID, COUNT(OrderID) AS TotalOrders

     FROM Orders

     GROUP BY CustomerID;

     

 

Question: You have a table named "Products" with columns "ProductID," "ProductName," and "UnitPrice." You need to find the average unit price of products in each category. How would you write an SQL query for this?

   Answer: 

     

     SELECT CategoryID, AVG(UnitPrice) AS AveragePrice

     FROM Products

     GROUP BY CategoryID;

     

 

Question: You have two tables, "Employees" and "Departments," with columns "EmployeeID," "FirstName," "LastName," "DepartmentID," and "DepartmentName." You need to retrieve the names of employees along with their department names. How would you write an SQL query for this?

   Answer: 

     

     SELECT e.FirstName, e.LastName, d.DepartmentName

     FROM Employees e

     INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

     

 

Question: You need to find the customers who have not placed any orders in the "Customers" and "Orders" tables. How would you write an SQL query for this?

   Answer: 

     

     SELECT c.CustomerID, c.CustomerName

     FROM Customers c

     LEFT JOIN Orders o ON c.CustomerID = o.CustomerID

     WHERE o.CustomerID IS NULL;

     

 

Question: You need to calculate the total revenue generated by each product in the "OrderDetails" table. How would you write an SQL query for this?

   Answer: 

     

     SELECT ProductID, SUM(Quantity * UnitPrice) AS TotalRevenue

     FROM OrderDetails

     GROUP BY ProductID;

     

 

Question: You need to find the most recent order date for each customer in the "Orders" table. How would you write an SQL query for this?

   Answer: 

     

     SELECT CustomerID, MAX(OrderDate) AS MostRecentOrderDate

     FROM Orders

     GROUP BY CustomerID;

     

 

Question: You have a table named "Students" with columns "StudentID," "FirstName," "LastName," and "DateOfBirth." You need to retrieve the age of each student based on their date of birth. How would you write an SQL query for this?

   Answer:  *(Assuming current date is used for calculation)*

     

     SELECT StudentID, FirstName, LastName, 

            YEAR(CURRENT_DATE) - YEAR(DateOfBirth) - 

            CASE WHEN (MONTH(CURRENT_DATE) < MONTH(DateOfBirth)

OR 

                      (MONTH(CURRENT_DATE) = MONTH(DateOfBirth)

AND DAY(CURRENT_DATE) < DAY(DateOfBirth)))

                 THEN 1 ELSE 0 END AS Age

     FROM Students;

     

 

Question: You have a table named "Orders" with columns "OrderID," "OrderDate," and "TotalAmount." You need to find the total sales revenue for each month. How would you write an SQL query for this?

   Answer: 

     

     SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, 

            SUM(TotalAmount) AS TotalSalesRevenue

     FROM Orders

     GROUP BY YEAR(OrderDate), MONTH(OrderDate);

     

 

Question: You have a table named "Products" with columns "ProductID," "ProductName," and "UnitsInStock." You need to retrieve the products that are out of stock (i.e., UnitsInStock is 0). How would you write an SQL query for this?

    Answer: 

      

      SELECT ProductID, ProductName, UnitsInStock

      FROM Products

      WHERE UnitsInStock = 0;

      

 

Question: You have a table named "Employees" with columns "EmployeeID," "FirstName," "LastName," and "HireDate." You need to retrieve the names of employees who were hired in the year 2023. How would you write an SQL query for this?

    Answer: 

      

      SELECT FirstName, LastName

      FROM Employees

      WHERE YEAR(HireDate) = 2023;

      

 

Question: You have two tables, "Customers" and "Orders," with columns "CustomerID" and "OrderID." You need to find the customers who have placed more than three orders. How would you write an SQL query for this?

    Answer: 

      

      SELECT c.CustomerID, COUNT(o.OrderID) AS TotalOrders

      FROM Customers c

      INNER JOIN Orders o ON c.CustomerID = o.CustomerID

      GROUP BY c.CustomerID

      HAVING COUNT(o.OrderID) > 3;

      

 

Question: You need to find the average order amount for each month in the year 2022. The "Orders" table contains columns "OrderID," "OrderDate," and "OrderAmount." How would you write an SQL query for this?

    Answer: 

      

      SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, 

             AVG(OrderAmount) AS AverageOrderAmount

      FROM Orders

      WHERE YEAR(OrderDate) = 2022

      GROUP BY YEAR(OrderDate), MONTH(OrderDate);

      

 

Question: You have a table named "Students" with columns "StudentID," "FirstName," "LastName," and "Class." You need to find the number of students in each class. How would you write an SQL query for this?

    Answer: 

      

      SELECT Class, COUNT(StudentID) AS NumberOfStudents

      FROM Students

      GROUP BY Class;

      

 

Question: You have a table named "Products" with columns "ProductID," "ProductName," "CategoryID," and "UnitPrice." You need to find the top two most expensive products in each category. How would you write an SQL query for this?

    Answer: 

      

      SELECT ProductID, ProductName, CategoryID, UnitPrice

      FROM (

          SELECT ProductID, ProductName, CategoryID, UnitPrice,

                 ROW_NUMBER() OVER(PARTITION BY CategoryID ORDER BY UnitPrice DESC) AS Rank

          FROM Products

      ) AS RankedProducts

      WHERE Rank <= 2;

      

 

Question: You have a table named "Transactions" with columns "TransactionID," "TransactionDate," and "Amount." You need to find the total amount of transactions for each quarter in the year 2023. How would you write an SQL query for this?

    Answer: 

      

      SELECT YEAR(TransactionDate) AS Year, 

             QUARTER(TransactionDate) AS Quarter, 

             SUM(Amount) AS TotalAmount

      FROM Transactions

      WHERE YEAR(TransactionDate) = 2023

      GROUP BY YEAR(TransactionDate), QUARTER(TransactionDate);

      

 

Question: You have two tables, "Employees" and "Departments," with columns "EmployeeID," "FirstName," "LastName," "DepartmentID," and "DepartmentName." You need to find the department with the highest number of employees. How would you write an SQL query for this?

    Answer: 

      

      SELECT DepartmentID, DepartmentName, COUNT(EmployeeID) AS NumberOfEmployees

      FROM Employees e

      INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID

      GROUP BY DepartmentID, DepartmentName

      ORDER BY COUNT(EmployeeID) DESC

      LIMIT 1;

      

 

Question: You have a table named "Customers" with columns "CustomerID," "CustomerName," and "Country." You need to find the number of customers from each country. However, if a country has fewer than three customers, it should be grouped under "Other." How would you write an SQL query for this?

    Answer: 

      

      SELECT

          CASE 

              WHEN COUNT(CustomerID) >= 3 THEN Country 

              ELSE 'Other' 

          END AS CountryGroup, 

          COUNT(CustomerID) AS NumberOfCustomers

      FROM Customers

      GROUP BY Country

      ORDER BY NumberOfCustomers DESC;

      

 

Question: You have a table named "Employees" with columns "EmployeeID," "FirstName," "LastName," and "Salary." You need to retrieve the names of employees who have a salary greater than the average salary of all employees. How would you write an SQL query for this?

    Answer: 

      

      SELECT FirstName, LastName

      FROM Employees

      WHERE Salary > (SELECT AVG(Salary) FROM Employees);

      

 

Question: You have a table named "Orders" with columns "OrderID," "OrderDate," and "TotalAmount." You need to find the date of the oldest and newest orders in the table. How would you write an SQL query for this?

    Answer: 

      

      SELECT MIN(OrderDate) AS OldestOrderDate, 

             MAX(OrderDate) AS NewestOrderDate

      FROM Orders;

      

 

Question: You have a table named "Sales" with columns "TransactionID," "ProductID," "SaleDate," and "Quantity." You need to find the top-selling product for each month in the year 2023. How would you write an SQL query for this?

   Answer: 

     

     WITH MonthlySales AS (

         SELECT ProductID, MONTH(SaleDate) AS Month, SUM(Quantity) AS TotalQuantity

         FROM Sales

         WHERE YEAR(SaleDate) = 2023

         GROUP BY ProductID, MONTH(SaleDate)

     ),

     RankedSales AS (

         SELECT ProductID, Month, TotalQuantity,

                ROW_NUMBER() OVER(PARTITION BY Month ORDER BY TotalQuantity DESC) AS Rank

         FROM MonthlySales

     )

     SELECT ProductID, Month, TotalQuantity

     FROM RankedSales

     WHERE Rank = 1;

     

 

Question: You have two tables, "Customers" and "Orders," with columns "CustomerID" and "OrderDate." You need to find the customer who placed the first order ever in the database. How would you write an SQL query for this?

    Answer: 

      

      SELECT CustomerID, MIN(OrderDate) AS FirstOrderDate

      FROM Orders

      GROUP BY CustomerID

      ORDER BY FirstOrderDate

      LIMIT 1;

      

 

Question: You have a table named "Transactions" with columns "TransactionID," "TransactionDate," and "Amount." You need to find the average amount of transactions for each month in the year 2022, but excluding the highest transaction amount for each month. How would you write an SQL query for this?

    Answer: 

      

      SELECT YEAR(TransactionDate) AS Year, 

             MONTH(TransactionDate) AS Month, 

             AVG(Amount) AS AverageTransactionAmount

      FROM (

          SELECT TransactionDate, Amount,

                 ROW_NUMBER() OVER(PARTITION BY YEAR(TransactionDate), MONTH(TransactionDate) ORDER BY Amount DESC) AS Rank

          FROM Transactions

          WHERE YEAR(TransactionDate) = 2022

      ) AS RankedTransactions

      WHERE Rank > 1

      GROUP BY YEAR(TransactionDate), MONTH(TransactionDate);

      

 

Question: You have a table named "Products" with columns "ProductID," "ProductName," and "CategoryID." You need to find the top two products with the highest total sales revenue for each category. How would you write an SQL query for this?

    Answer: 

      

      WITH ProductSales AS (

          SELECT p.ProductID, p.ProductName, p.CategoryID, 

                 SUM(od.Quantity * od.UnitPrice) AS TotalRevenue

          FROM Products p

          INNER JOIN OrderDetails od ON p.ProductID = od.ProductID

          GROUP BY p.ProductID, p.ProductName, p.CategoryID

      ),

      RankedProducts AS (

          SELECT ProductID, ProductName, CategoryID, TotalRevenue,

                 ROW_NUMBER() OVER(PARTITION BY CategoryID ORDER BY TotalRevenue DESC) AS Rank

          FROM ProductSales

      )

      SELECT ProductID, ProductName, CategoryID, TotalRevenue

      FROM RankedProducts

      WHERE Rank <= 2;

      

 

Question: You have a table named "Students" with columns "StudentID," "FirstName," "LastName," and "EnrollmentDate." You need to find the retention rate of students for each month in the first year after enrollment. Retention rate is defined as the percentage of students who are still enrolled in the following month. How would you write an SQL query for this?

    Answer: 

      

      WITH StudentRetention AS (

          SELECT StudentID, MONTH(EnrollmentDate) AS EnrollmentMonth,

                 DATEDIFF(MONTH, EnrollmentDate, DATEADD(YEAR, 1, EnrollmentDate)) AS EnrollmentYearMonths,

                 DATEDIFF(MONTH, EnrollmentDate, GETDATE()) AS MonthsSinceEnrollment

          FROM Students

      )

      SELECT EnrollmentMonth, 

             SUM(CASE WHEN MonthsSinceEnrollment = EnrollmentYearMonths THEN 1 ELSE 0 END) AS RetainedStudents,

             COUNT(StudentID) AS TotalStudents,

             100.0 * SUM(CASE WHEN MonthsSinceEnrollment = EnrollmentYearMonths THEN 1 ELSE 0 END) / COUNT(StudentID) AS RetentionRate

      FROM StudentRetention

      GROUP BY EnrollmentMonth;

      

 

Question: You have a table named "Inventory" with columns "ProductID," "WarehouseID," "QuantityInStock," and "LastUpdated." You need to find the top-selling product for each warehouse based on the quantity sold in the last month. How would you write an SQL query for this?

    Answer: 

      

      WITH MonthlyInventory AS (

          SELECT ProductID, WarehouseID, SUM(QuantityInStock) AS TotalQuantitySold

          FROM Inventory

          WHERE DATEDIFF(MONTH, LastUpdated, GETDATE()) = 1

          GROUP BY ProductID, WarehouseID

      ),

      RankedInventory AS (

          SELECT ProductID, WarehouseID, TotalQuantitySold,

                 ROW_NUMBER() OVER(PARTITION BY WarehouseID ORDER BY TotalQuantitySold DESC) AS Rank

          FROM MonthlyInventory

      )

      SELECT ProductID, WarehouseID, TotalQuantitySold

      FROM RankedInventory

      WHERE Rank = 1;

      

 

Question: You have a table named "Orders" with columns "OrderID," "CustomerID," "OrderDate," and "TotalAmount." You need to find the top three customers with the highest average order amount. How would you write an SQL query for this?

    Answer: 

      

      WITH CustomerOrders AS (

          SELECT Customer

 

ID, AVG(TotalAmount) AS AverageOrderAmount

          FROM Orders

          GROUP BY CustomerID

      )

      SELECT CustomerID, AverageOrderAmount

      FROM CustomerOrders

      ORDER BY AverageOrderAmount DESC

      LIMIT 3;

      

 

Question: You have a table named "Employees" with columns "EmployeeID," "FirstName," "LastName," "ManagerID," and "Salary." You need to find the names of employees who are managed by the highest-paid manager in the company. How would you write an SQL query for this?

    Answer: 

      

      WITH ManagerSalaries AS (

          SELECT ManagerID, MAX(Salary) AS MaxManagerSalary

          FROM Employees

          GROUP BY ManagerID

      )

      SELECT e.FirstName, e.LastName

      FROM Employees e

      INNER JOIN ManagerSalaries ms ON e.ManagerID = ms.ManagerID

      WHERE e.Salary = ms.MaxManagerSalary;

      

 

Question: You have a table named "Orders" with columns "OrderID," "OrderDate," and "CustomerID." You need to find the date of the first order for each customer and the number of days between the first and second orders for those customers who have placed more than one order. How would you write an SQL query for this?

    Answer: 

      

      WITH CustomerFirstOrder AS (

          SELECT CustomerID, MIN(OrderDate) AS FirstOrderDate

          FROM Orders

          GROUP BY CustomerID

      ),

      CustomerSecondOrder AS (

          SELECT o.CustomerID, MIN(o.OrderDate) AS SecondOrderDate

          FROM Orders o

          INNER JOIN CustomerFirstOrder cfo ON o.CustomerID = cfo.CustomerID

          WHERE o.OrderDate > cfo.FirstOrderDate

          GROUP BY o.CustomerID

      )

      SELECT cfo.CustomerID, cfo.FirstOrderDate, 

             DATEDIFF(DAY, cfo.FirstOrderDate, cso.SecondOrderDate) AS DaysBetweenOrders

      FROM CustomerFirstOrder cfo

      INNER JOIN CustomerSecondOrder cso ON cfo.CustomerID = cso.CustomerID;

      

 

Question: You have a table named "Employees" with columns "EmployeeID," "FirstName," "LastName," "DepartmentID," and "StartDate." You need to find the names of employees who have worked in the same department for at least five years. How would you write an SQL query for this?

    Answer: 

      

      SELECT FirstName, LastName

      FROM Employees

      WHERE DATEDIFF(YEAR, StartDate, GETDATE()) >= 5;