Querying Data with TransactSQL Free Practice Test — 30 Questions

Question 1

A database developer is troubleshooting a slow-performing T-SQL query that aggregates customer order data. The query frequently filters and sorts results by the date an order was placed. The execution plan reveals significant time spent on table scans and inefficient data retrieval. Considering the typical access patterns for order-related data, which indexing strategy on the `Orders` table, specifically targeting the `OrderDate` column, would most effectively address the performance degradation for queries involving date-based filtering and sorting?

Accepted Answer

Implement a clustered index on the `OrderDate` column of the `Orders` table.

Question 2

Anya, a junior database administrator, is tasked with improving the performance of a T-SQL query that retrieves order details for customers residing in the United States. The current query utilizes an `INNER JOIN` between the `Customers` and `Orders` tables, with a `WHERE` clause applied to filter by country. Given that the `Customers` table contains millions of records, with only a small percentage residing in the USA, Anya needs to implement a strategy that ensures the filtering occurs as early as possible in the execution plan to minimize the data processed during the join operation. Which T-SQL construct modification would be the most effective for achieving this optimization goal, demonstrating an understanding of predicate pushdown and efficient query execution?

Accepted Answer

Using an INNER JOIN and moving the WHERE c.Country = 'USA' condition to the ON clause

Question 3

Anya, a junior database administrator, is tasked with optimizing a T-SQL query that retrieves customer order details. The existing query uses a `LEFT JOIN` to connect the `Customers` table to the `OrderHistory` table. Users have reported significant slowdowns, particularly during peak business hours. Anya suspects the `LEFT JOIN` might be contributing to the performance bottleneck, especially if the majority of customers have placed orders and the system is processing many rows with NULL order details. She needs to adjust the query to improve response times while ensuring that only customers with actual order data are returned.

Accepted Answer

Replace the `LEFT JOIN` with an `INNER JOIN` to ensure only customers with matching order records are included in the result set, thereby reducing the number of rows processed.

Question 4

Anya, a database developer working with a large e-commerce platform, is tasked with optimizing a T-SQL query that retrieves customer order details. The query joins the `Customers` table with the `Orders` table and then with the `Products` table to display customer names, order dates, product names, and quantities. She observes that the query performs poorly, particularly when dealing with large datasets, and suspects that the current indexing strategy on the `Orders` table is not optimal for this specific query pattern. Anya wants to implement a covering index on the `Orders` table to improve performance by minimizing the need for bookmark lookups.

Considering the query\'s requirements to join on `CustomerID` and `ProductID`, and to select `OrderDate`, which of the following index definitions on the `Orders` table would be the most effective for achieving a covering index and improving query performance?

Accepted Answer

CREATE INDEX IX_Orders_Covering ON Orders (CustomerID, ProductID) INCLUDE (OrderDate);

Question 5

A database administrator observes that a critical Transact-SQL query responsible for generating daily sales reports is experiencing significant performance degradation. The query joins the `Sales` and `Products` tables, filters records based on a date range in the `Sales` table, and calculates a derived metric using a user-defined scalar-valued function within its select list. Analysis of the query execution plan reveals a high cost associated with scanning the `Sales` table and repeated execution of the scalar-valued function for each row processed. To mitigate these issues and adhere to best practices for query optimization in SQL Server, what is the most effective two-pronged approach?

Accepted Answer

Implement a non-clustered index on the `OrderDate` column of the `Sales` table and rewrite the scalar-valued function's logic directly into the main query.

Question 6

A T-SQL developer is tasked with optimizing a query that retrieves customer names and their most recent order date. The existing query uses a correlated subquery in the `SELECT` list to find the latest order date for each customer. As the customer and order tables grow, this query\'s execution time has become unacceptable. Which of the following strategies would most effectively address this performance degradation, considering the need to display all customers, even those without orders?

Accepted Answer

Rewrite the query to use a `LEFT JOIN` with a window function, such as `ROW_NUMBER()`, partitioned by customer and ordered by order date in descending order, to identify and select the most recent order for each customer.

Question 7

A database administrator is investigating a performance degradation issue with a T-SQL query that retrieves historical customer order data. The query frequently employs a `WHERE` clause filtering on the `OrderDate` column, which is defined as `DATETIME2`. Initial analysis indicates that the query\'s execution plan involves a significant number of logical reads, particularly when a broad date range is specified. To enhance the efficiency of this query and similar date-based range filters, which indexing strategy would provide the most substantial performance improvement by directly optimizing the physical data retrieval for sequential date lookups?

Accepted Answer

Implement a clustered index on the `OrderDate` column.

Question 8

Anya, a database developer, is tasked with optimizing a T-SQL query that retrieves customer order summaries. The existing query, which utilizes a correlated subquery to count recent orders for each customer, is experiencing significant performance degradation on a production database with millions of records. The application relying on this query is becoming unresponsive. Anya recognizes the need to adapt her strategy to address the performance bottleneck and maintain application stability. She considers refactoring the query to improve its execution plan.

Which of the following T-SQL query refactoring approaches would most effectively address the performance issues associated with a correlated subquery that repeatedly executes for each row in the outer query, especially when dealing with large datasets and aiming for a more efficient data retrieval mechanism?

Accepted Answer

Replacing the correlated subquery with a LEFT JOIN operation combined with a GROUP BY clause and a HAVING clause to filter the results, thereby materializing the related data once.

Question 9

Anya, a junior database administrator, is reviewing a poorly performing T-SQL query intended to retrieve all orders placed within the last quarter for a specific product line, \"AquaGlide Water Sports,\" from a database containing millions of customer and order records. The current query utilizes a subquery in the `WHERE` clause to filter products by name. Analysis of the execution plan reveals significant I/O costs and high CPU usage due to the subquery\'s repeated evaluation. Anya needs to implement a T-SQL modification that will most effectively improve query performance by ensuring that filtering occurs at the earliest possible stage of data retrieval and processing, thereby reducing the number of rows processed by subsequent operations.

Accepted Answer

Rewrite the query to include the product name filter directly in the `WHERE` clause, applied to the `Products` table before or during the join with `Orders`.

Question 10

A data analyst at a global e-commerce firm needs to extract a dataset containing all customer orders placed during January 2023, specifically from customers located in either the \'Northwest\' or \'Southwest\' territories. The existing database schema includes tables for `Orders` (with `OrderID`, `CustomerID`, `OrderDate`) and `Customers` (with `CustomerID`, `CustomerName`, `CustomerRegion`). Which T-SQL `SELECT` statement correctly retrieves this specific subset of data?

Accepted Answer

SELECT o.OrderID, o.CustomerID, o.OrderDate, c.CustomerRegion FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID WHERE o.OrderDate BETWEEN '2023-01-01' AND '2023-01-31' AND c.CustomerRegion IN ('Northwest', 'Southwest')

Question 11

Elara, a data analyst for a retail analytics firm, is tasked with identifying high-volume customers from the past fiscal year. She needs to retrieve a list of customer IDs and the total quantity of items they ordered during that period. The criteria are specific: only customers marked as \"active\" in the `Customers` table should be considered, and the total quantity of items ordered by each customer must exceed 50 units. The `Orders` table contains `OrderID`, `CustomerID`, `OrderDate`, and `Quantity` columns, while the `Customers` table has `CustomerID` and `IsActive` (a BIT datatype where 1 signifies active). The fiscal year in question spans from January 1, 2023, to December 31, 2023. Which T-SQL query would accurately fulfill Elara\'s requirements?

Accepted Answer

SELECT CustomerID, SUM(Quantity) AS TotalQuantity FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' AND CustomerID IN (SELECT CustomerID FROM Customers WHERE IsActive = 1) GROUP BY CustomerID HAVING SUM(Quantity) > 50

Question 12

A business analyst needs to extract a list of customer names and their primary email addresses for an upcoming outreach initiative. The data resides in two tables: `Clientele` (containing `ClientID`, `GivenName`, `FamilyName`) and `CommunicationLog` (containing `LogID`, `ClientID`, `CommunicationType`, `ContactDetail`). The business analyst has specified that only customers with a `CommunicationType` of \'Email\' and a corresponding `ContactDetail` that is not null should be considered. Additionally, they require that the `ClientID` must exist in both tables to ensure data integrity. Which Transact-SQL statement accurately retrieves the required data?

Accepted Answer

SELECT cl.GivenName, cl.FamilyName, cl.ContactDetail FROM Clientele cl INNER JOIN CommunicationLog clg ON cl.ClientID = clg.ClientID WHERE clg.CommunicationType = 'Email' AND clg.ContactDetail IS NOT NULL

Question 13

A database administrator observes that a T-SQL query designed to fetch a customer\'s historical transactions, involving filtering by a date range and aggregating spending by product category, is becoming increasingly sluggish. The query’s execution plan shows significant time spent on table scans of the `Transactions` table, which has grown substantially in size. The `Transactions` table has columns such as `TransactionID`, `CustomerID`, `TransactionDate`, `ProductID`, and `Amount`. The query frequently filters records based on `TransactionDate` to retrieve data for specific periods. What strategic modification to the database schema would most effectively address this performance degradation for date-based range queries?

Accepted Answer

Implement a clustered index on the `TransactionDate` column of the `Transactions` table.

Question 14

Anya, a data analyst for a global e-commerce platform, is investigating a significant performance degradation in a critical Transact-SQL query responsible for generating daily sales reports. The query joins the `Customers`, `Orders`, and `OrderItems` tables. Analysis of the execution plan reveals a high cost attributed to a nested loop join between `Orders` and `OrderItems`, where a large number of rows from `OrderItems` are being scanned for each row in `Orders` based on a filter condition on `OrderItems.ProductID` and projection of `OrderItems.Quantity`. The `Orders` table has a clustered index on `OrderID` and a non-clustered index on `CustomerID`. The `OrderItems` table has a clustered index on `OrderItemID` and a non-clustered index on `OrderID`. Given these circumstances, what is the most appropriate T-SQL optimization strategy to directly mitigate the inefficiency identified in the nested loop join\'s data retrieval process?

Accepted Answer

Create a non-clustered index on `OrderItems` that includes `ProductID` and `Quantity`, and potentially `OrderID` to support the join.

Question 15

A database administrator is tasked with generating a report that lists each unique product name along with the most recent date it was ordered. The available tables are `Products` (containing `ProductID`, `ProductName`, `Category`) and `Orders` (containing `OrderID`, `ProductID`, `OrderDate`). The requirement is to ensure that if a product has multiple orders, only the single, latest order date is displayed for that product, and each product name appears only once in the final result set. Which Transact-SQL construct would most effectively achieve this outcome by assigning a rank to each order for a product based on its date and then selecting the top-ranked order?

Accepted Answer

Utilizing a window function like `ROW_NUMBER()` partitioned by product name and ordered by order date descending, then filtering for the first row within each partition.

Question 16

A data analyst at \"Global Gadgets Inc.\" is tasked with generating a report on the most recent order placed by each distinct customer. The initial T-SQL query, which employs a correlated subquery within the `WHERE` clause to identify the maximum `OrderDate` for each `CustomerID`, is causing significant performance degradation on a large `Orders` table. The analyst needs to refactor this query to improve efficiency and reduce execution time, adhering to best practices for querying large datasets. Which of the following T-SQL query structures would most effectively address this performance bottleneck?

Accepted Answer

Utilize a Common Table Expression (CTE) with the `ROW_NUMBER()` window function, partitioning by `CustomerID` and ordering by `OrderDate` DESC, then selecting rows where the row number is 1.

Question 17

A business analyst is tasked with identifying the top three highest distinct sales figures for each geographical region within the company\'s sales database. The critical requirement is that if multiple products within a region achieve the same sales figure, and that figure qualifies as one of the top three distinct values, all products associated with that sales figure must be included in the result set. For instance, if the top three distinct sales figures in a region are $50,000, $45,000, and $40,000, and there are five products that each sold $40,000, all five of those products must be returned. Which Transact-SQL window function, when applied with appropriate partitioning and ordering, will fulfill this specific requirement for identifying and ranking these sales figures?

Accepted Answer

DENSE_RANK()

Question 18

Anya, a junior DBA, is investigating a critical stored procedure, `usp_ProcessCustomerOrders`, that exhibits unpredictable performance dips. The procedure handles customer order processing and has become a bottleneck since the company’s recent expansion into a new market, which has dramatically increased data volume and introduced new data patterns. Anya suspects that the procedure\'s execution plan is not adapting well to these changes, possibly due to stale statistics or inefficient query constructs like dynamic cursors. To diagnose and resolve this, she needs to systematically analyze the procedure\'s behavior. Which of the following diagnostic and remediation strategies, when applied in sequence, best addresses Anya\'s situation by focusing on identifying the root cause and implementing effective solutions within the context of Transact-SQL query optimization principles?

Accepted Answer

Capture the execution plan and I/O statistics, update table statistics using `FULLSCAN`, and refactor cursor usage to set-based operations.

Question 19

Anya, a data analyst working for a global e-commerce platform, is tasked with reviewing customer order data for a specific geographic region to identify trends for an upcoming marketing campaign. The current system utilizes a stored procedure, `usp_GetCustomerOrders`, which retrieves an extensive dataset of all customer orders, regardless of location. Anya has identified that this procedure is a significant bottleneck, leading to slow report generation times and consuming excessive network bandwidth. Additionally, stricter data privacy regulations are being implemented, requiring the minimization of data processed and transmitted. Anya needs to propose an immediate, effective modification to the existing stored procedure to enhance performance and ensure compliance. Which of the following modifications would best address both Anya\'s performance concerns and the new regulatory requirements?

Accepted Answer

Modify the stored procedure to accept a regional parameter and explicitly select only the necessary columns for order history retrieval.

Question 20

A database administrator is tasked with joining a `ProductInventory` table, which stores `SKU` as a `BIGINT`, to a `ShipmentTracking` table where `SKU` is defined as `VARCHAR(50)`. The administrator needs to retrieve all shipment records that correspond to products present in the inventory. Which of the following join strategies would most effectively mitigate potential data integrity issues arising from data type mismatches and ensure accurate retrieval of matching records, considering the inherent differences in storage and representation between `BIGINT` and `VARCHAR`?

Accepted Answer

Explicitly convert the `BIGINT` `SKU` from `ProductInventory` to `VARCHAR(50)` using `CAST` or `CONVERT` within the join predicate.

Question 21

A data analyst at a financial services firm is tasked with identifying high-value transactions for a quarterly compliance audit. The audit requires a list of all customer transactions that exceeded \$1000.00 in value and occurred specifically during the month of October 2023. The transaction data is stored in a table named `FinancialRecords`, which contains columns such as `AccountID`, `TransactionTimestamp`, and `TransactionValue`. Which Transact-SQL query would most accurately fulfill this requirement, adhering to best practices for date range filtering?

Accepted Answer

SELECT AccountID, TransactionTimestamp, TransactionValue FROM FinancialRecords WHERE TransactionValue > 1000.00 AND TransactionTimestamp >= '2023-10-01' AND TransactionTimestamp < '2023-11-01'

Question 22

A database administrator, Elara Vance, is reviewing a T-SQL query designed to aggregate sales data across multiple product categories for a new fiscal reporting dashboard. The current query utilizes a cursor to iterate through each product category, calculate the total revenue, and then insert this into a summary table. While functional, performance testing indicates this approach is exceptionally slow, especially as the dataset grows. Elara needs to identify the most appropriate T-SQL technique to replace the cursor-based logic, adhering to best practices for performance and scalability, while also demonstrating adaptability in her approach to problem-solving.

Accepted Answer

Rewrite the query to use a Common Table Expression (CTE) to pre-aggregate revenue by category and then join this CTE to the main sales table for reporting.

Question 23

Anya, a junior database administrator, is troubleshooting a critical stored procedure that retrieves historical sales data. Users have reported that the procedure sometimes returns incomplete or erroneous order details depending on the specific date range and customer segment provided as input parameters. The procedure’s execution time is within acceptable limits, but the accuracy of the returned data is compromised in certain scenarios. Which of Anya\'s diagnostic actions would most effectively address the root cause of these data inconsistencies?

Accepted Answer

Investigating parameter sniffing issues and potential implicit conversions by examining the execution plan for various parameter combinations and analyzing data types of joined columns.

Question 24

Elara, a data analyst tasked with identifying high-performing product categories for an upcoming promotional campaign, is working with a large dataset of customer transactions. She needs to determine the total revenue generated by each product category for all orders placed within the last fiscal quarter. Given the database schema and the need for efficient data retrieval, which T-SQL query construction strategy would best balance performance, readability, and maintainability for this task?

Accepted Answer

Utilize a Common Table Expression (CTE) to first calculate the revenue per category for the specified period, then select from the CTE, ordering the results by revenue in descending order.

Question 25

A database administrator is tasked with querying a `Products` table where `ProductCode` is a `VARCHAR(10)` and `ListPrice` is a `DECIMAL(10,2)`. The `ProductCode` column contains values such as \'SKU-987-A\', \'ITEM-456-B\', and \'PROD-123-C\'. The administrator needs to identify products where the `ProductCode` contains the alphanumeric sequence \'123\' without causing a conversion error, as a direct numeric comparison of `ProductCode` to a numeric literal would fail due to the non-numeric characters. Which of the following T-SQL query fragments would successfully achieve this objective while adhering to the existing table structure?

Accepted Answer

WHERE ProductCode LIKE '3%'

Question 26

Anya, a junior database administrator, is tasked with extracting all sales records for customers with IDs 101, 105, 112, and 120, specifically for orders placed between January 15, 2023, and February 28, 2023. She needs to ensure that only records meeting both the customer ID criteria and the date range criteria are returned. Which T-SQL statement would most accurately and efficiently fulfill this requirement?

Accepted Answer

SELECT OrderID, CustomerID, OrderDate, TotalAmount FROM Orders WHERE CustomerID IN (101, 105, 112, 120) AND OrderDate BETWEEN '2023-01-15' AND '2023-02-28';

Question 27

Anya, a junior database developer, is tasked with enhancing the performance of a T-SQL query responsible for generating monthly sales reports. The current query, which joins customer and order tables, is experiencing significant slowdowns, impacting the business intelligence dashboard. Analysis of the execution plan reveals a high cost associated with table scans and inefficient join operations. Anya considers several approaches to mitigate these issues. Which of the following strategies would best address the performance bottlenecks by improving query structure and underlying data access efficiency?

Accepted Answer

Refactor the query using Common Table Expressions (CTEs) to logically separate data retrieval and aggregation steps, and advocate for the creation of appropriate non-clustered indexes on join columns and frequently filtered columns to optimize data retrieval.

Question 28

A data analyst is tasked with retrieving a dataset of customer interactions from the `CustomerInteractions` table, which includes columns like `InteractionID`, `CustomerID`, `InteractionType`, `InteractionTimestamp`, and `Notes`. The requirement is to fetch all interactions that have occurred within the past 48 hours, but the precise timestamp of the last successful data extraction is not readily available in a variable. The analyst needs a method to query these recent interactions based on the current system time. Which Transact-SQL approach would most effectively identify records within this dynamic, recent timeframe without relying on a pre-stored \"last processed\" timestamp?

Accepted Answer

SELECT InteractionID, CustomerID, InteractionType, InteractionTimestamp, Notes FROM CustomerInteractions WHERE InteractionTimestamp >= DATEADD(hour, -48, GETDATE());

Question 29

A company maintains its employee reporting structure in a SQL Server database table named `Employees`, with columns `EmployeeID`, `EmployeeName`, `ManagerID`, and `Level` (where `Level` indicates the depth in the hierarchy, with 0 being the top). A Transact-SQL query is designed to retrieve the reporting line for any employee up to three levels above them. The query utilizes a recursive Common Table Expression (CTE) named `EmployeeHierarchy` to traverse this structure. The base case of the CTE selects employees at `Level = 0`, and the recursive member joins `Employees` to `EmployeeHierarchy` on `e.ManagerID = eh.EmployeeID`, incrementing the `Level` by one in each recursive step. The final query filters the results to include only records where the `Level` is less than or equal to 3, and the `EmployeeName` is \'Elara Vance\'. If Elara Vance is confirmed to be at `Level = 3` in this organizational hierarchy, how many rows will the executed query return that directly pertain to Elara Vance\'s reporting line, including herself?

Accepted Answer

4

Question 30

A data analytics team is experiencing significant performance degradation with a T-SQL query designed to report on active clients who have made purchases within the last fiscal quarter. The current implementation relies on a `WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate >= DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0))`. The lead developer needs to pivot to a more efficient strategy to reduce execution time, considering that the `Orders` table is substantial and indexed on `OrderDate`. Which alternative query structure would most effectively address this performance bottleneck while adhering to best practices for data retrieval in T-SQL?

Accepted Answer

An `INNER JOIN` between the `Customers` table and the `Orders` table, filtering `Orders.OrderDate` to be within the last fiscal quarter.

Querying Data with TransactSQL Free Practice Test — 30 Questions

A lot more is already mapped out

About the Querying Data with TransactSQL Certification