DS0-001 Data Querying And Analysis — 10 Questions

Question 1

A data analyst needs to combine data from three tables. If the tables are joined sequentially, what is the result called?

Accepted Answer

Data denormalization. A series of joins creates a combined result set, often referred to as a denormalized view or result set.

Answer

Data normalization

Answer

Data aggregation

Answer

Data pivoting

Question 2

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 improve readability and maintainability of complex queries by allowing the logic to be defined in a named temporary result set.

Answer

Automatic indexing of the result set

Answer

Performance is always superior to subqueries

Answer

Lower memory consumption during execution

Question 3

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 returns all rows from the left table and the matched rows from the right table, filling with NULL if no match exists.

Answer

CROSS JOIN

Answer

INNER JOIN

Answer

FULL OUTER JOIN

Question 4

Which SQL function is used to convert a NULL value in a query result into a default value, such as 0?

Accepted Answer

COALESCE. COALESCE is a standard function used to return the first non-null value in a list, often used to replace NULLs.

Answer

NVL

Answer

IFNULL

Answer

ISNULL

Question 5

You are performing data transformation in Python using Pandas. You have a 'DataFrame' with missing values in the 'Revenue' column. Which method should be used to replace these missing values with the column mean?

Accepted Answer

fillna(). The fillna() method is the standard way to replace NaN values in a Pandas DataFrame.

Answer

replace()

Answer

dropna()

Answer

interpolate()

Answer

map()

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 used to return only unique values from a specific column.

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 is specifically designed to plot two numerical variables to show correlations.

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. Standardizing string casing (normalization) is a fundamental prerequisite for accurate merging and joining.

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() function determines the sequence for running calculations.

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. Standard SQL requires every non-aggregated column in the SELECT clause to be included in the GROUP BY clause to ensure deterministic results.

Answer

Missing WHERE clause

Answer

Incorrect join type

Answer

Using an alias in WHERE