{"id":17448,"date":"2024-08-05T11:49:53","date_gmt":"2024-08-05T06:19:53","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=17448"},"modified":"2024-12-12T09:17:58","modified_gmt":"2024-12-12T14:17:58","slug":"top-sql-scenario-based-interview-questions-with-answers","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/top-sql-scenario-based-interview-questions-with-answers\/","title":{"rendered":"Top SQL Scenario Based Interview Questions with Answers"},"content":{"rendered":"\n

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<\/a>. In this blog, we’ll explore some of the top SQL scenario-based interview questions<\/a> along with detailed answers to help you prepare effectively.<\/p>\n\n\n\n

1. Scenario: Extracting Data with Conditions<\/strong><\/h2>\n\n\n\n

Question:<\/strong>
You have a table named Employees<\/code> with columns EmployeeID<\/code>, Name<\/code>, Department<\/code>, and Salary<\/code>. Write an SQL query to find the names of employees who work in the ‘HR’ department and have a salary greater than $50,000.<\/p>\n\n\n\n

Answer:<\/strong><\/p>\n\n\n\n

sqlCopy codeSELECT Name\nFROM Employees\nWHERE Department = 'HR' AND Salary > 50000;\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
This query selects the Name<\/code> column from the Employees<\/code> table where the Department<\/code> is ‘HR’ and the Salary<\/code> is greater than $50,000. The AND<\/code> operator ensures that both conditions must be true for a record to be included in the result set.<\/p>\n\n\n\n
2. Scenario: Finding Duplicate Records<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have a table called Orders<\/code> with columns OrderID<\/code>, CustomerID<\/code>, OrderDate<\/code>, and OrderAmount<\/code>. Write a query to find duplicate CustomerID<\/code> values.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeSELECT CustomerID, COUNT(*)\nFROM Orders\nGROUP BY CustomerID\nHAVING COUNT(*) > 1;\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The query groups the records by CustomerID<\/code> and counts the number of occurrences for each CustomerID<\/code>. The HAVING<\/code> clause filters the results to include only those CustomerID<\/code>s that appear more than once, indicating duplicates.<\/p>\n\n\n\n
3. Scenario: Aggregating Data<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
Given a table Sales<\/code> with columns SaleID<\/code>, ProductID<\/code>, Quantity<\/code>, and SaleDate<\/code>, write a query to find the total quantity sold for each product.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeSELECT ProductID, SUM(Quantity) AS TotalQuantity\nFROM Sales\nGROUP BY ProductID;\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
This query aggregates the Quantity<\/code> column for each ProductID<\/code> using the SUM<\/code> function. The GROUP BY<\/code> clause groups the rows by ProductID<\/code>, allowing the calculation of the total quantity sold for each product.<\/p>\n\n\n\n
4. Scenario: Date Range Queries<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have a table Transactions<\/code> with columns TransactionID<\/code>, AccountID<\/code>, TransactionDate<\/code>, and Amount<\/code>. Write a query to find all transactions that occurred in the last 30 days.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeSELECT *\nFROM Transactions\nWHERE TransactionDate >= DATEADD(DAY, -30, GETDATE());\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The DATEADD<\/code> function subtracts 30 days from the current date (GETDATE()<\/code>). The WHERE<\/code> clause filters the transactions to include only those that occurred within the last 30 days. This query is useful for retrieving recent transactions.<\/p>\n\n\n\n
5. Scenario: Updating Records<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have a table Products<\/code> with columns ProductID<\/code>, ProductName<\/code>, Price<\/code>, and StockQuantity<\/code>. Write a query to increase the price of all products by 10% that have less than 100 units in stock.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeUPDATE Products\nSET Price = Price * 1.10\nWHERE StockQuantity < 100;\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The UPDATE<\/code> statement modifies the Price<\/code> of products. The SET<\/code> clause specifies the new price as 10% higher than the current price. The WHERE<\/code> clause restricts the update to products with a StockQuantity<\/code> of less than 100 units.<\/p>\n\n\n\n
6. Scenario: Deleting Specific Records<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have a table Users<\/code> with columns UserID<\/code>, Username<\/code>, Email<\/code>, and Status<\/code>. Write a query to delete all users whose status is ‘inactive’ and have not logged in for the past year.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeDELETE FROM Users\nWHERE Status = 'inactive' AND LastLoginDate < DATEADD(YEAR, -1, GETDATE());\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The DELETE<\/code> statement removes records from the Users<\/code> table. The WHERE<\/code> clause ensures that only users with a status of ‘inactive’ and a LastLoginDate<\/code> older than one year are deleted. The DATEADD<\/code> function calculates the date one year ago.<\/p>\n\n\n\n
7. Scenario: Joining Multiple Tables<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have two tables, Customers<\/code> and Orders<\/code>. The Customers<\/code> table has columns CustomerID<\/code>, Name<\/code>, and City<\/code>, while the Orders<\/code> table has columns OrderID<\/code>, CustomerID<\/code>, OrderDate<\/code>, and TotalAmount<\/code>. Write a query to find the total amount spent by each customer.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeSELECT c.Name, SUM(o.TotalAmount) AS TotalSpent\nFROM Customers c\nJOIN Orders o ON c.CustomerID = o.CustomerID\nGROUP BY c.Name;\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The JOIN<\/code> clause combines the Customers<\/code> and Orders<\/code> tables based on the matching CustomerID<\/code>. The SUM<\/code> function calculates the total amount spent by each customer. The results are grouped by the customer’s name (c.Name<\/code>).<\/p>\n\n\n\n
8. Scenario: Subqueries<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have a table Employees<\/code> with columns EmployeeID<\/code>, Name<\/code>, Department<\/code>, and Salary<\/code>. Write a query to find the names of employees who earn more than the average salary in their department.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeSELECT Name\nFROM Employees e\nWHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department);\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The subquery (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department)<\/code> 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<\/code>.<\/p>\n\n\n\n
9. Scenario: Handling NULL Values<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have a table Products<\/code> with columns ProductID<\/code>, ProductName<\/code>, Category<\/code>, and Discount<\/code>. Write a query to display the ProductName<\/code> and Discount<\/code>, replacing NULL discounts with ‘No Discount’.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeSELECT ProductName, ISNULL(CAST(Discount AS VARCHAR), 'No Discount') AS Discount\nFROM Products;\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The ISNULL<\/code> function replaces NULL values in the Discount<\/code> column with the string ‘No Discount’. The CAST<\/code> function converts the Discount<\/code> column to a string format, allowing the use of the ‘No Discount’ label.<\/p>\n\n\n\n
10. Scenario: Ranking and Window Functions<\/strong><\/h2>\n\n\n\n
Question:<\/strong>
You have a table Sales<\/code> with columns SaleID<\/code>, ProductID<\/code>, SaleAmount<\/code>, and SaleDate<\/code>. Write a query to rank the sales amounts for each product and display the rank along with the sale details.<\/p>\n\n\n\n
Answer:<\/strong><\/p>\n\n\n\n
sqlCopy codeSELECT SaleID, ProductID, SaleAmount, \n       RANK() OVER (PARTITION BY ProductID ORDER BY SaleAmount DESC) AS SaleRank\nFROM Sales;\n<\/code><\/pre>\n\n\n\n
Explanation:<\/strong>
The RANK()<\/code> function assigns a rank to each sale amount within the partition of ProductID<\/code>, ordered by SaleAmount<\/code> in descending order. The PARTITION BY<\/code> clause ensures that the ranking is reset for each product.<\/p>\n\n\n\n
Recommended Post: QA Testing Training<\/a><\/p>\n\n\n\n
Conclusion<\/strong><\/h2>\n\n\n\n
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<\/a> and analysis.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":17454,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-17448","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/17448","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=17448"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/17448\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/17454"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=17448"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=17448"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=17448"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}