DS0-001 Data Querying And Analysis Practice Test 2 — 15 Questions

Question 1

A data analyst queries a sales table containing 40 million rows to calculate month-to-date revenue. The query filters on order_date and groups by region_code. The table has a clustered index on order_id and a nonclustered index on order_date, yet the query performs a full table scan and takes several minutes. The analyst needs to reduce execution time without changing the result set. Which of the following actions is the MOST appropriate first step?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a covering nonclustered index on (order_date, region_code) that includes the revenue column.. The scan persists because no index satisfies both the order_date range filter and the grouping/aggregation on region_code and revenue. A covering index with order_date and region_code as keys and revenue included allows an index seek plus direct reads of the aggregated column, removing the scan and key lookups. The other actions either degrade the existing index, add recompilation overhead, or improve estimates without creating a usable access path.

Answer

Update statistics on the sales table to give the optimizer more accurate cardinality estimates.

Answer

Reorder the columns in the existing nonclustered index on order_date so region_code becomes the leading key.

Answer

Add OPTION (RECOMPILE) to the query so the optimizer generates a fresh plan for each execution.

Question 2

A data analyst is working with a table named orders that has a column order_date of type DATE. The analyst needs to retrieve all orders placed in the year 2023. Which SQL WHERE clause correctly filters the data?

Accepted Answer

WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'. Filtering a DATE column for a specific year is best done with a range condition using BETWEEN, which is inclusive and sargable. Using functions like YEAR() or pattern matching with LIKE can cause performance issues and may not be portable across database systems. The BETWEEN clause correctly captures all dates in 2023.

Answer

WHERE order_date LIKE '2023%'

Answer

WHERE order_date = '2023'

Answer

WHERE YEAR(order_date) = 2023

Question 3

A data analyst is tasked with writing a SQL query to calculate the average salary per department from an employees table. The analyst wants to ensure the query is optimized for performance and returns only departments with more than 5 employees. Which TWO of the following techniques should the analyst use? (Choose two.)

Accepted Answer

Use a HAVING clause to filter departments with more than 5 employees after grouping.. To filter departments by employee count after grouping, the HAVING clause is required. Additionally, indexing the department column can improve the performance of the GROUP BY operation. These two techniques directly address the requirements of filtering aggregated results and optimizing query speed.

Answer

Use SELECT * to include all columns for easier verification.

Answer

Use a subquery in the SELECT clause to calculate the average salary.

Answer

Use a WHERE clause to filter out departments with fewer than 5 employees before grouping.

Question 4

An analyst is using a CTE (Common Table Expression) to pre-aggregate sales data before performing a join. What is the primary benefit of using a CTE instead of a subquery in this scenario?

Accepted Answer

Better readability and recursive capability. CTEs (WITH clauses) improve query readability by naming intermediate result sets and, unlike most subqueries, support recursion via WITH RECURSIVE — essential for hierarchical data like org charts or bill-of-materials. In the pre-aggregation scenario, the CTE makes the aggregation step explicit and self-documenting, and it can be referenced multiple times in the outer query.

Answer

Automatic indexing of the result set

Answer

Performance is always superior to subqueries

Answer

Lower memory consumption during execution

Question 5

When joining two tables in SQL where you need to return all records from the left table even if there is no match in the right table, which JOIN type is appropriate?

Accepted Answer

LEFT JOIN. A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right side. This exactly matches the requirement to return all records from the left table even if there is no match in the right table. Therefore, LEFT JOIN is correct.

Answer

CROSS JOIN

Answer

INNER JOIN

Answer

FULL OUTER JOIN

Question 6

A data analyst is writing a SQL query to retrieve unique department names from the 'Employees' table. Which keyword should be utilized to ensure the results do not contain duplicate entries?

Accepted Answer

DISTINCT. The DISTINCT keyword is the correct choice because it eliminates duplicate rows from the result set, returning only unique values for the selected columns. When applied as SELECT DISTINCT department FROM Employees, the database engine sorts or hashes the intermediate result to remove duplicates before returning rows. This is the ANSI SQL standard mechanism specifically designed for deduplication of query output.

Answer

GROUP BY

Answer

UNIQUE

Answer

FILTER

Question 7

In Power BI, which visualization component is best suited to show the relationship between two numerical variables and identify potential trends or clusters?

Accepted Answer

Scatter Plot. A scatter plot plots each data point on a two-dimensional plane using two numerical axes, making it the ideal visualization to reveal correlation, trends, clusters, and outliers between two numerical variables. Power BI's scatter chart also supports a category/legend field and a size field for bubble charts, extending its analytical power.

Answer

Gauge Chart

Answer

Pie Chart

Answer

Clustered Column Chart

Question 8

When cleaning a dataset, you notice that column names contain inconsistent casing (e.g., 'Name', 'NAME', 'name'). Which transformation step should be prioritized to facilitate join operations?

Accepted Answer

String Normalization. Inconsistent casing in column names (e.g., 'Name', 'NAME', 'name') is a string-formatting issue that must be resolved before joins, because most databases treat identifiers as case-sensitive (or case-insensitive depending on collation) and mismatched casing causes join failures or duplicate columns. String Normalization standardises casing, whitespace, and formatting across the dataset.

Answer

One-hot encoding

Answer

Data Binning

Answer

Data deduplication

Question 9

A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?

Accepted Answer

ORDER BY. The ORDER BY clause inside the OVER() specification defines the logical ordering of rows for the window function, which is essential for running totals because the cumulative sum must be computed in a defined sequence. Without ORDER BY, the window frame defaults to the entire partition and the running total is meaningless. ORDER BY establishes the row sequence that makes the cumulative calculation deterministic.

Answer

GROUP BY

Answer

PARTITION BY

Answer

ROWS BETWEEN

Question 10

You are debugging a SQL query that uses an aggregate function. You receive an error stating that a column in your SELECT list is not part of an aggregate or a GROUP BY clause. What is the cause?

Accepted Answer

Missing column in GROUP BY. SQL requires that every column in the SELECT list be either aggregated (e.g., SUM, COUNT, AVG) or included in the GROUP BY clause when aggregation is used. The error 'column is not part of an aggregate or GROUP BY' means a non-aggregated column was selected without being listed in GROUP BY, so the engine cannot determine which value to return per group.

Answer

Missing WHERE clause

Answer

Incorrect join type

Answer

Using an alias in WHERE

Question 11

A report needs to identify the 'top 10' customers by total spend. In SQL, which clause would you use to return only the first 10 results after sorting by spend descending?

Accepted Answer

LIMIT. The LIMIT clause restricts the number of rows returned by a query after the result set has been sorted. Combined with ORDER BY spend DESC, LIMIT 10 returns only the top 10 highest-spending customers. WHERE filters rows before aggregation/sorting, OFFSET skips rows, and HAVING filters grouped results — none of these cap the row count.

Answer

WHERE

Answer

OFFSET

Answer

HAVING

Question 12

In data visualization, what is the 'Data-Ink Ratio' intended to improve?

Accepted Answer

Chart readability and clarity. The Data-Ink Ratio, introduced by Edward Tufte, is the proportion of ink used to display actual data versus total ink used in the graphic. A higher ratio means more of the visual elements convey data, reducing non-data ink like gridlines, borders, and decorative elements. This directly improves chart readability and clarity by minimizing distractions and focusing the viewer on the data. Thus, option D is correct.

Answer

Chart rendering speed

Answer

Color palette selection

Answer

Data compression

Question 13

When working with time-series data, what does a 'lag' feature represent in an analysis?

Accepted Answer

The value of a variable from a previous period. In time-series analysis, a lag feature is a copy of a variable's value shifted back by one or more time periods, so the model can use past observations to predict current or future values. For example, lag-1 of daily sales is yesterday's sales value aligned to today's row. This is fundamental to autoregressive models and feature engineering for forecasting.

Answer

The time taken to query the data

Answer

The difference between two time stamps

Answer

The sampling rate of the sensors

Question 14

Which of the following is an example of an unstructured data source that a data analyst might need to ingest?

Accepted Answer

An email message body. An email message body is unstructured because it has no predefined schema — the text, formatting, and embedded content vary freely and must be parsed with NLP or regex rather than mapped to columns. This is the classic example of unstructured data that analysts ingest for sentiment analysis, eDiscovery, or customer-support mining.

Answer

An XML file with defined tags

Answer

A relational SQL table

Answer

A CSV file with fixed columns

Question 15

An analyst notices that a database query is performing full table scans even though a WHERE clause is present on a indexed column. What is a likely reason for this?

Accepted Answer

Using a function on the indexed column. When a function is applied to an indexed column in a WHERE clause (e.g., WHERE UPPER(name) = 'SMITH'), the optimizer cannot use the index because the index stores the raw column values, not the function results. This forces a full table scan. The fix is to rewrite the predicate to avoid wrapping the column in a function or to create a function-based/computed-column index.

Answer

Too many rows match the criteria

Answer

The table is too small

Answer

The index is fragmented