Mining Data practice questions

UNIQUE

Why wrong: UNIQUE is not a SQL keyword for this purpose; it is used in constraints.

REMOVE DUPLICATES

Why wrong: This is not valid SQL syntax.

FILTER

Why wrong: FILTER is not a standard SQL keyword; filtering is done with WHERE.

Null count

Why wrong: Null count identifies missing values but not invalid formats.

Cardinality

Why wrong: Cardinality counts distinct values; it does not check format.

Row count

Why wrong: Row count only tells how many rows exist, not the validity of data.

AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date)

Why wrong: Without a frame, the default is range between unbounded preceding and current row, which is not a moving window.

AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Why wrong: Missing PARTITION BY product_id, so it would average across all products.

SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Why wrong: This calculates a moving sum, not an average.

GET_YEAR(order_date)

Why wrong: This function does not exist in SQL.

YEAR(order_date)

Why wrong: YEAR() is not a standard SQL function; it exists in some RDBMS but EXTRACT is standard.

DATE_PART('year', order_date)

Why wrong: This is PostgreSQL-specific, not standard SQL.

SELECT TOP 5 name, salary FROM employees ORDER BY salary DESC;

Why wrong: TOP is used in SQL Server, but LIMIT is more standard; however, the question does not specify RDBMS, and LIMIT is widely used.

SELECT name, salary FROM employees ORDER BY salary ASC LIMIT 5;

Why wrong: ASC gives lowest salaries.

SELECT name, salary FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;

Why wrong: ROWNUM is Oracle-specific and would filter before sorting.

$500,000

Why wrong: Incorrect calculation.

$425,000

Why wrong: This is Q3 + 0.5*IQR, not the correct multiplier.

$650,000

Why wrong: Incorrect calculation.

333

Why wrong: Incorrect calculation.

500

Why wrong: That would be 50%.

300

Why wrong: That would be 30% of 1000, which corresponds to South or West.

DECLARE

Why wrong: DECLARE is used for variables, not CTEs.

DEFINE

Why wrong: DEFINE is not used for CTEs.

CTE

Why wrong: CTE is not a SQL keyword.

combine()

Why wrong: combine() is used for element-wise operations, not merging.

join()

Why wrong: join() is a method on DataFrames that uses index by default, but merge is more explicit for column joins.

concat()

Why wrong: concat() concatenates along an axis, not on a key column.

RTRIM

Why wrong: RTRIM removes only trailing spaces.

LTRIM

Why wrong: LTRIM removes only leading spaces.

CLEAN

Why wrong: CLEAN is not a standard SQL function.

ROW_NUMBER()

Why wrong: ROW_NUMBER() assigns a unique sequential number, ties would get different numbers.

RANK()

Why wrong: RANK() gives same rank to ties but leaves gaps.

NTILE()

Why wrong: NTILE() divides rows into buckets, not ranking.

FILTER

Why wrong: FILTER is not a standard SQL clause.

WHERE

Why wrong: WHERE filters rows before aggregation.

LIMIT

Why wrong: LIMIT restricts the number of rows returned, not filtering groups.

Row count

Why wrong: Row count is a basic metric but not specifically for quality assessment.

Cardinality

Why wrong: Cardinality measures distinct values, which is more about data distribution than quality.

Data type verification

Why wrong: Data type verification is part of profiling but the question asks for metrics; this is an action, not a metric.

ORDER BY clause

Why wrong: ORDER BY is not required for recursion.

WITH clause

Why wrong: The WITH clause is used to define the CTE but is not a component of the recursion logic.

Convenience sampling

Why wrong: Convenience sampling is non-probability.

Systematic sampling

Why wrong: Systematic sampling is also probability-based, but the question asks for TWO; we choose the two most fundamental.

Cluster sampling

Why wrong: Cluster sampling is probability-based but not as fundamental as the selected two.

DISTINCT COUNT(customer_id)

Why wrong: Invalid SQL syntax; DISTINCT must go inside the COUNT function.

COUNT(customer_id)

Why wrong: Counts all non-null customer IDs including duplicates.

COUNT(*)

Why wrong: COUNT(*) counts all rows including duplicates, not distinct customers.

df.drop_duplicates()

Why wrong: Removes duplicate rows, not missing values.

df.fillna(0)

Why wrong: Fills missing values with 0, does not remove rows.

df.isna()

Why wrong: Returns a DataFrame of booleans indicating missing values.

ORDER BY salary DESC LIMIT 5

Why wrong: LIMIT 5 excludes ties beyond the fifth row.

HAVING salary > 50000

Why wrong: HAVING filters groups, not rows; not suitable.

WHERE ROWNUM <= 5

Why wrong: ROWNUM limits rows but does not handle ties.

String function

Why wrong: String functions manipulate text, not numeric calculations across rows.

Aggregate function with GROUP BY

Why wrong: GROUP BY collapses rows and cannot compute moving averages across rows.

Date function

Why wrong: Date functions extract date parts, not compute moving aggregates.

Cardinality analysis

Why wrong: Counts distinct values, not pattern violations.

Referential integrity check

Why wrong: Checks foreign key relationships, not column patterns.

Data type verification

Why wrong: Confirms data type, not specific value patterns.