Scenario-Based Questions

1. Scenario: You have two tables: orders and customers. How would you retrieve all orders made by a customer named “John Doe”?

Answer: You would use a JOIN between the orders and customers tables, ensuring that you filter the customers by the name “John Doe.” The query would involve matching the customer_id from the orders table with the id of the customers table.

2. Scenario: You need to calculate the total sales for each product category. How would you do that?

Answer: To calculate the total sales, you would aggregate the data by product category. Using the GROUP BY clause allows you to group the data by the category, and then you can sum the sales column to get the total sales for each category.

3. Scenario: You need to find employees who have worked for more than 5 years. How do you write the query?

Answer: To find employees with more than 5 years of work experience, you would calculate the difference between the hire_date and the current date, and filter records where this difference exceeds 5 years.

4. Scenario: You want to find all customers who have made at least one purchase in the last month. How would you approach this?

Answer: You would join the customers table with the purchases table and filter the purchase_date to be within the last month. This would give you the customers who have made purchases during that period.

5. Scenario: You need to find all products that are not sold in any order. How would you do that?

Answer: A LEFT JOIN between the products and orders tables would be used to find products that don’t have matching rows in the orders table. By checking for NULL values in the join condition, you can identify products that have never been sold.

6. Scenario: How would you list the top 5 highest-paid employees in a company?

Answer: You would sort employees by salary in descending order and use a LIMIT to restrict the result set to the top 5 highest-paid employees.

7. Scenario: You need to display all orders placed by a customer, including orders that may not have any products associated with them.

Answer: To include orders with no associated products, you would use a LEFT JOIN to join the orders and products tables, ensuring that all orders are returned even if there is no matching product.

8. Scenario: How would you remove duplicate records from a table where the name and address columns are identical?

Answer: You could use a DELETE statement with a JOIN to the same table, targeting rows where the name and address are the same, but ensuring that only one of them remains.

9. Scenario: You want to count the number of orders for each customer. How would you do that?

Answer: You would use the GROUP BY clause to group the data by customer_id and then use the COUNT() function to count the orders for each customer.

10. Scenario: You need to calculate the average salary of employees in each department. How would you do that?

Answer: Use GROUP BY to group the data by department_id and the AVG() function to calculate the average salary for each department.

11. Scenario: How would you find employees who have a salary higher than the average salary?

Answer: To find employees with a salary higher than the average, you would use a subquery to calculate the average salary and filter employees whose salary is greater than that value.

12. Scenario: You need to find the department with the highest total salary expenditure. How do you write the query?

Answer: You would group the data by department, use the SUM() function to calculate the total salary per department, and then order the results to get the department with the highest total salary.

13. Scenario: How would you find customers who have made purchases in every product category?

Answer: This would require a GROUP BY on the customer_id and ensuring that the customer has purchases in each product category. You could use HAVING to ensure every category is represented.

14. Scenario: You need to find employees who have worked in more than one department. How would you approach this?

Answer: You would use a GROUP BY clause on the employee_id and COUNT() the number of unique department_id values associated with each employee. Filter those where the count is greater than 1.

15. Scenario: How would you find employees who do not belong to any department?

Answer: You can use a LEFT JOIN between the employees and departments tables and filter the results where the department_id is NULL, indicating that no department is assigned.

16. Scenario: How would you retrieve the most recent order for each customer?

Answer: You would use the GROUP BY clause to group by customer_id and use the MAX() function on the order_date to find the most recent order for each customer.

17. Scenario: How would you update the salary of all employees in a specific department by 10%?

Answer: You would use an UPDATE statement with a WHERE clause to target only employees in the specified department, then increase their salary by multiplying it by 1.1.

18. Scenario: How would you delete all records from a table where a column value is NULL?

Answer: Use a DELETE statement with a WHERE clause to filter rows where the column value is NULL and delete those rows.

19. Scenario: You need to combine data from two tables with similar columns but in different formats (e.g., varchar and int). How do you ensure compatibility?

Answer: You would ensure that both columns are in the same format by using CAST() or CONVERT() functions to match the data types before combining them.

20. Scenario: How would you find records where a value in a column matches one of several options (e.g., 1, 2, 3)?

Answer: You would use the IN operator to filter rows where the column value matches any of the values in the provided list.

21. Scenario: You need to find employees whose names start with “J”. How would you approach this?

Answer: You would use the LIKE operator with a wildcard (%) to match any employee names starting with the letter “J.”

22. Scenario: You want to ensure that only unique records are selected in your query. How would you do that?

Answer: You would use the DISTINCT keyword to eliminate duplicate rows from the result set.

23. Scenario: How would you find all customers who have made purchases greater than $500 in total?

Answer: You would group the data by customer and use the SUM() function to calculate the total purchase amount per customer. Then, use the HAVING clause to filter for customers with total purchases greater than $500.

24. Scenario: You want to retrieve data in a specific order, say by the latest date first. How would you do that?

Answer: Use the ORDER BY clause to sort the data by the date column in descending order.

25. Scenario: You need to retrieve a list of products that were either never sold or sold in only one order. How would you query this?

Answer: Use a LEFT JOIN between the products and orders tables, and filter for products that either have no corresponding records in the orders table or have only one associated order.

26. Scenario: How would you find the second-highest salary in a company?

Answer: Use a subquery to find the highest salary, then another query to find the highest salary below that value.

27. Scenario: You need to calculate the number of distinct products ordered in each month. How would you approach this?

Answer: Use the GROUP BY clause on the order date (by month) and the COUNT(DISTINCT product_id) to calculate the number of distinct products.

28. Scenario: How would you handle retrieving data from multiple tables where no matches exist in one of the tables?

Answer: Use a LEFT JOIN to include all records from one table, even if there are no matching records in the other table.

29. Scenario: How do you retrieve records from a table that have no matching rows in another table?

Answer: Use a LEFT JOIN and filter for NULL values in the columns from the second table, indicating no match.

30. Scenario: You need to find all records where a value is within a range (e.g., between 10 and 100). How would you do that?

Answer: Use the BETWEEN operator to filter rows where the column value is between two specified values.

31. Scenario: You need to categorize employees based on their age groups. How would you do that?

Answer: Use a CASE statement to assign age categories based on the value of the age column.

32. Scenario: How would you retrieve all rows from a table except for a certain subset of records?

Answer: Use a WHERE clause to exclude the subset of records you don’t want by specifying conditions that filter them out.

33. Scenario: You need to return only the top 3 records based on a specific column. How do you do that?

Answer: Use a LIMIT clause (or equivalent in some databases) to restrict the number of rows returned to the top 3.

34. Scenario: How would you update a column in one table based on values from another table?

Answer: You would use a JOIN in an UPDATE statement to update the values in the target table using data from the source table.

35. Scenario: You need to find records that match a pattern. How would you do that?

Answer: Use the LIKE operator with wildcards to match a pattern within a column.

36. Scenario: How would you check if a particular record exists in a table?

Answer: Use the EXISTS keyword in a subquery to check for the existence of a record.

37. Scenario: You need to retrieve data in a specific order, but the data needs to be sorted by multiple columns. How do you do that?

Answer: Use the ORDER BY clause with multiple column names, specifying the order (ascending or descending) for each column.

38. Scenario: You need to get a list of employees and their salaries, but you also want to include employees with no salary data. How do you approach this?

Answer: Use a LEFT JOIN between the employees and salary tables to ensure that employees without a salary are still included in the result set.

39. Scenario: You need to create a report that lists departments and the number of employees in each. How would you do that?

Answer: Use a GROUP BY clause on the department_id and COUNT() to get the number of employees in each department.

40. Scenario: How would you find records where a value is not equal to a specific value in a column?

Answer: Use the <> (or != depending on the database) operator in a WHERE clause to filter out rows where the column value is not equal to the specified value.

41. Scenario: You need to retrieve all products where the price is greater than 50, but only for products that have been sold at least once. How would you approach this?

Answer: Use a JOIN between the products and orders tables, filtering for products with a price greater than 50 and ensuring they have been sold.

42. Scenario: How would you find the average order value for each customer?

Answer: You would group the orders by customer_id and use the AVG() function to calculate the average order value for each customer.

43. Scenario: How would you retrieve all employees whose salary is between 40000 and 70000?

Answer: Use the BETWEEN operator to filter for salaries in the specified range.

44. Scenario: You need to calculate the percentage of total sales made by each salesperson. How would you do that?

Answer: You would calculate the total sales for each salesperson, then divide each salesperson’s sales by the total sales of all salespeople to get the percentage.

45. Scenario: How do you retrieve records from a table, sorting them by the most recent date first?

Answer: Use the ORDER BY clause to sort by the date column in descending order.

46. Scenario: You need to find the third-highest salary among all employees. How do you write the query?

Answer: You would use a subquery to rank the salaries in descending order and retrieve the third-highest salary using a LIMIT or ROW_NUMBER().

47. Scenario: You need to return only the distinct values of a specific column. How would you do that?

Answer: Use the DISTINCT keyword to retrieve unique values from the column.

48. Scenario: How would you find employees whose names contain the substring “Smith”?

Answer: Use the LIKE operator with the % wildcard to search for “Smith” anywhere in the employee names.

49. Scenario: You need to calculate the total number of orders placed by each customer, but only for customers who have placed more than 5 orders. How do you do that?

Answer: Use a GROUP BY clause on the customer and a HAVING clause to filter for customers who have placed more than 5 orders.

50. Scenario: You need to retrieve a list of products, including their total sales, but exclude products with no sales. How do you approach this?

Answer: Use an INNER JOIN between the products and orders tables, summing the sales for each product and filtering out those with no sales.