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

Question 1

Which THREE of the following are common data types in relational databases?

Accepted Answer

BOOLEAN. BOOLEAN (A) is a standard relational data type that stores TRUE/FALSE (or NULL) values, commonly used for flags and binary states. VARCHAR (D) is a variable-length character string type that stores alphanumeric text up to a defined maximum length, making it one of the most widely used column types. INTEGER (E) is a numeric data type storing whole numbers, typically used for IDs, counts, and other discrete values. PIVOT (B) is not a data type but a relational operation/query construct that rotates rows into columns, and DASHBOARD (C) is a reporting/visualization artifact rather than a column data type, so neither belongs in this list.

Answer

PIVOT

Answer

DASHBOARD

Question 2

Which TWO of the following actions are considered best practices for maintaining data integrity in a data pipeline?

Accepted Answer

Automated schema validation. Automated schema validation (A) is a best practice because it enforces expected data types, column names, and constraints at each pipeline stage, catching malformed or drifted records before they corrupt downstream tables. Logging data quality metrics (E) is also a best practice because it provides continuous observability into completeness, uniqueness, validity, and timeliness, enabling alerting and root-cause analysis when integrity degrades. By contrast, using only manual entry (B) is error-prone and unscalable, hardcoding credentials in scripts (C) is a security anti-pattern that risks unauthorized access and tampering, and ignoring error logs (D) hides failures that would otherwise reveal integrity problems.

Answer

Using only manual entry

Answer

Hardcoding credentials in scripts

Answer

Ignoring error logs

Question 3

Which TWO of the following are legitimate ways to optimize SQL query performance?

Accepted Answer

Creating indexes on frequently filtered columns. Option C is correct because creating indexes on frequently filtered columns (those used in WHERE, JOIN, and ORDER BY clauses) allows the database engine to locate rows via B-tree or hash lookups instead of full table scans, dramatically reducing I/O and query time. Option D is correct because replacing subqueries with JOINs where possible lets the optimizer build a single, more efficient execution plan, often avoiding repeated subquery evaluation (especially correlated subqueries) and enabling better use of indexes and join algorithms. Option A is wrong because consolidating data into a single large table increases scan costs, worsens contention, and prevents proper normalization and partitioning. Option B is wrong because SELECT * retrieves unnecessary columns, increasing I/O, memory, and network overhead and preventing covering-index optimizations. Option E is wrong because deleting system statistics removes the optimizer's cardinality and distribution data, leading to poor execution plans and degraded performance.

Answer

Using a single large table

Answer

Selecting all columns (SELECT *)

Answer

Deleting all system statistics

Question 4

Which TWO of the following are benefits of using a data warehouse?

Accepted Answer

Optimization for complex analytical queries. Option B is correct because a data warehouse is specifically architected for analytical workloads, using columnar storage, star/snowflake schemas, and MPP engines to efficiently run complex aggregations, joins, and OLAP queries over large historical datasets. Option C is correct because a data warehouse consolidates data from multiple heterogeneous source systems into a single, subject-oriented, integrated repository, giving the enterprise one authoritative version of the truth for reporting and BI. Option A is wrong because a data warehouse complements, rather than replaces, source systems — the OLTP systems remain the systems of record that feed the warehouse via ETL/ELT. Option D is wrong because a warehouse stores and serves data; it does not itself perform automated AI decision making, which requires separate ML/analytics tooling. Option E is wrong because warehouses are optimized for bulk analytical reads, not low-cost real-time transactional updates, which are the domain of OLTP databases.

Answer

Complete replacement of all source systems

Answer

Automated AI decision making

Answer

Low cost for real-time transactional updates

Question 5

Which THREE of the following are examples of descriptive statistics?

Accepted Answer

Standard deviation. Standard deviation (A) is a descriptive statistic because it quantifies the spread or dispersion of a dataset around its mean, summarizing variability without inferring beyond the observed data. The mean (B) is a measure of central tendency that summarizes a dataset by averaging all values, making it a classic descriptive statistic. The median (D) is also a measure of central tendency, representing the middle value of an ordered dataset and summarizing its central position descriptively. Machine learning (C) is not a descriptive statistic; it is a field of algorithms used for prediction or pattern recognition, often involving inferential or predictive modeling. Linear regression (E) is an inferential/predictive technique that models relationships between variables to estimate or forecast outcomes, not merely to summarize data.

Answer

Machine learning

Answer

Linear regression

Question 6

Which THREE of the following are types of data joins in SQL?

Accepted Answer

INNER JOIN. INNER JOIN (A) is a core SQL join type that returns only rows where the join condition matches in both tables, making it one of the standard join categories. LEFT JOIN (C) is also a standard SQL join that returns all rows from the left table plus matching rows from the right table, filling unmatched right-side columns with NULL. RIGHT JOIN (D) is the mirror of LEFT JOIN, returning all rows from the right table plus matches from the left, and is likewise a recognized SQL join type. The other options are not valid SQL joins: UPPER JOIN (B) is not a SQL keyword (UPPER is a string function), and FAST JOIN (E) is not a defined join type in standard SQL or major RDBMS implementations.

Answer

UPPER JOIN

Answer

FAST JOIN

Question 7

Which THREE of the following represent common data transformation tasks in a preparation workflow?

Accepted Answer

Data cleaning. Data cleaning (A) is a core transformation task because it fixes missing values, duplicates, outliers, and inconsistent formats so downstream analysis is reliable. Renaming columns (B) is a standard transformation that standardizes schema and improves readability before joining or modeling data. Data filtering (D) is also a transformation task because it selects or excludes rows based on conditions, reducing the dataset to the relevant subset. Database deletion (C) is an administrative or destructive operation, not a preparation transformation, and hardware installation (E) is an infrastructure task unrelated to data preparation workflows.

Answer

Database deletion

Answer

Hardware installation

Question 8

Which TWO of the following are common issues encountered when integrating data from disparate sources?

Accepted Answer

Inconsistent data formats. Option B (Inconsistent data formats) is correct because disparate sources often store the same attribute using different representations—such as dates as MM/DD/YYYY versus ISO 8601, currencies with different symbols or decimal separators, or text encodings like UTF-8 versus Latin-1—which must be normalized before the data can be joined or compared. Option D (Conflicting identifiers) is correct because the same real-world entity may be keyed differently across systems (e.g., a customer as SSN in one source, a UUID in another, or a legacy account number in a third), creating duplicate or mismatched records that require entity resolution or key mapping. The unmarked options do not belong: excessive storage space (A) is a capacity/cost concern rather than an integration problem, fast network speeds (C) are beneficial and not an issue, and over-documentation (E) is a documentation practice, not a data-integration obstacle.

Answer

Excessive storage space

Answer

Fast network speeds

Answer

Over-documentation

Question 9

A data engineer is profiling a customer table and finds that the 'email' column contains 12% NULL values, while the 'customer_id' column is fully populated and unique. The business rule states that every customer record must have a valid email address. Which action should the engineer take to enforce this rule in the pipeline?

Accepted Answer

Implement a validation rule that quarantines records with NULL or malformed email addresses for remediation.. The business rule requires every customer record to have a valid email address. Quarantining records with NULL or malformed emails prevents invalid data from entering the trusted dataset while keeping those records for remediation. This enforces the rule without discarding data or fabricating values, and it handles both missing and invalid formats.

Answer

Drop the email column from the customer table and rely on customer_id for identification.

Answer

Replace NULL email values with a default placeholder such as 'unknown@example.com' and load the rows.

Answer

Add a NOT NULL constraint to the email column and reject rows that fail the constraint.

Question 10

A data analyst is writing a query to find the average order value (AOV) per customer from an orders table that includes cancelled and returned orders. They want to exclude those orders from the calculation but still show all customers, including those with no valid orders. Which SQL construct should they use to ensure customers with no valid orders appear with a NULL or zero AOV?

Accepted Answer

LEFT JOIN between customers and orders with the order status filter in the ON clause. To include all customers while excluding certain orders from the aggregation, the filter must be applied in the ON clause of a LEFT JOIN. This keeps all left-table rows and only joins matching right-table rows that satisfy the condition. Customers without valid orders retain NULLs, which can be handled in the SELECT list to display zero or NULL AOV.

Answer

LEFT JOIN between customers and orders with a WHERE clause filtering out cancelled and returned orders

Answer

FULL OUTER JOIN between customers and orders

Answer

INNER JOIN between customers and orders

Question 11

A data analyst is querying a sales table with columns order_id, order_date, customer_id, and order_total. They need to return each order along with the running total of order_total for each customer, ordered by order_date. Which SQL feature should they use?

Accepted Answer

A window function with PARTITION BY customer_id ORDER BY order_date and a SUM() aggregate. The running total per customer requires preserving each order row while accumulating order_total in date order within each customer. A window function with PARTITION BY customer_id and ORDER BY order_date, using SUM() as the window aggregate, achieves this in one pass and returns the detail rows plus the cumulative value. Grouping, self-joins, or correlated subqueries either collapse rows or perform poorly and ambiguously.

Answer

A GROUP BY customer_id with SUM(order_total) and ORDER BY order_date

Answer

A self-join on customer_id with a condition that the joined order_date is less than or equal to the outer order_date, then SUM the joined order_total

Answer

A correlated subquery that selects SUM(order_total) from the same table where customer_id matches and order_date is less than the outer order_date

Question 12

A junior data analyst runs the following query against a retail sales table and receives 42,000 rows instead of the expected 12,000:

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_status <> 'cancelled';

Which action should the analyst take to return all customers, including those with no orders, while still excluding cancelled orders?

Accepted Answer

Move the o.order_status <> 'cancelled' condition into the ON clause of the LEFT JOIN.. In a LEFT JOIN, predicates placed in the WHERE clause are evaluated after the join and remove rows where the right-side column is NULL, effectively turning the outer join into an inner join. Moving the status filter into the ON clause restricts which orders are matched without discarding customers who have no orders. This is the standard fix for returning all customers while excluding cancelled orders.

Answer

Change the WHERE clause to WHERE o.order_status <> 'cancelled' OR o.order_status IS NULL.

Answer

Add DISTINCT to the SELECT list to eliminate duplicate customer rows.

Answer

Replace LEFT JOIN with INNER JOIN and keep the WHERE clause unchanged.

Question 13

A data analyst needs to retrieve the average order value for each region from a sales table. The table has columns region, order_id, and order_total. Which SQL statement correctly returns one row per region with its average order value?

Accepted Answer

SELECT region, AVG(order_total) FROM sales GROUP BY region;. Per-region averages require grouping rows by region and applying AVG to order_total within each group. The GROUP BY clause creates one result row per distinct region, and AVG computes the mean for that region. Omitting GROUP BY yields a single overall aggregate, and using DISTINCT does not substitute for grouping because it operates on the final result set rather than partitioning rows for aggregation.

Answer

SELECT region, SUM(order_total)/COUNT(*) FROM sales;

Answer

SELECT region, AVG(order_total) FROM sales;

Answer

SELECT DISTINCT region, AVG(order_total) FROM sales;

Question 14

A data engineer must load a 400 GB Parquet dataset from an object store into a columnar warehouse. The source files are partitioned by date but each partition contains many small files averaging 5 MB. Queries against the loaded table are slow because the warehouse scans far more data than necessary. Which action most directly improves query performance by reducing scanned data?

Accepted Answer

Compact the small files into larger files and ensure the target table is partitioned on the same date column.. Query latency in columnar warehouses is dominated by how much data must be read and how many files must be opened. Many small files increase metadata and open/close overhead, and a lack of partitioning on the common filter column forces full scans. Compacting files and aligning partitioning with the date column enable partition pruning and efficient columnar reads, directly reducing scanned data.

Answer

Convert the Parquet files to CSV before loading to simplify parsing.

Answer

Increase the warehouse compute cluster size to add more nodes.

Answer

Compress each 5 MB file with gzip before loading.

Question 15

A data analyst runs a query against a PostgreSQL 15 data warehouse to calculate month-over-month revenue growth. The query uses the LAG window function partitioned by region and ordered by month. The analyst notices that the first month for each region returns a NULL growth value, which is causing downstream dashboard errors. Which action should the analyst take to handle this NULL value appropriately?

Accepted Answer

Wrap the LAG expression with COALESCE to substitute a default value such as 0 for the first month's growth.. The LAG window function returns NULL when there is no preceding row within the partition, which occurs for the first month of each region. Wrapping the expression with COALESCE replaces that NULL with a defined default, such as 0, so downstream calculations and dashboards do not fail. This preserves all rows and maintains correct growth calculations for subsequent months.

Answer

Change the ORDER BY clause in the window function to sort by revenue instead of month.

Answer

Replace the LAG function with a self-join on the same table to retrieve the previous month's revenue.

Answer

Add a WHERE clause that filters out rows where the LAG result IS NULL before the dashboard consumes the data.