CCNA Data Querying And Analysis Questions

Placing the filter in the ON clause preserves all rows from the left table, so customers with no orders still appear with NULL order values, while cancelled orders are excluded from the join matches. The result set includes every customer and only non-cancelled orders, which matches the stated requirement exactly. Filtering in the WHERE clause instead converts the outer join into an inner join.

This approach accounts for both true NULLs and common placeholder strings that represent missing data. By explicitly listing these values in an IN clause along with IS NULL, the analyst captures all instances of missing information. This is the most comprehensive method for counting truly missing values in this dataset.

Star schemas denormalise dimension tables into flat structures, so descriptive attributes repeat across fact rows. This deliberate redundancy speeds reads but increases storage and risks inconsistent updates, unlike a fully normalised model where data is stored once and joins reconstruct relationships.

Automated schema validation catches structural drift—unexpected column additions, type changes or missing fields—before malformed records propagate downstream. It enforces the pipeline's defined contract at ingestion, satisfying the data-integrity requirement by rejecting or quarantining non-conforming batches early, rather than allowing corrupt data to contaminate transformations and reporting.

Data cleaning is a core transformation task in preparation workflows, resolving duplicates, inconsistencies, missing values and formatting errors before analysis. It satisfies the stem's requirement by standardising raw input into a consistent, usable dataset, which is precisely the transformation work performed between extraction and loading.

A CASE expression evaluates conditions and returns a corresponding value for each row, making it ideal for deriving a tier label based on total spend without altering the table. It can be included directly in the SELECT list, so the original data remains unchanged. This is the standard SQL approach for conditional categorization in a query result.

A data warehouse stores data in dimensional, columnar or otherwise query-optimised structures with pre-computed aggregates, so complex analytical queries scan less data than on transactional systems. This directly satisfies the stem's benefit criterion by delivering fast, predictable analytical performance.

Compacting small files reduces per-file overhead and enables more efficient sequential reads, while aligning partitioning with the source date column lets the warehouse prune irrelevant partitions during queries. Together these reduce the volume of data scanned for typical date-filtered queries. This directly addresses the slow scans described in the scenario.

Lemmatization maps inflected forms to their dictionary base using vocabulary and morphological analysis, so 'Running' resolves to 'run' rather than the stem 'runn' that stemming produces. This yields identical tokens for sentiment scoring, satisfying the requirement that both forms share one base concept.

A LEFT JOIN returns all rows from the fact table (transactions) and matching rows from the product dimension. When there is no match, the product columns are NULL, but the transaction remains. Wrapping the category column with COALESCE(category, 'Uncategorized') replaces NULL with the desired label, ensuring all transactions appear and missing categories are clearly identified.

BOOLEAN holds true or false values, a standard relational data type for flags such as active status or consent. It satisfies the question's criterion of being a common data type by representing binary logical states compactly, typically as a single bit or small integer column.

Standard deviation is a descriptive statistic, quantifying the spread or dispersion of values around the mean. It satisfies the stem's requirement by summarising variability within the collected dataset, not by drawing inferences about a wider population.

LIMIT restricts the result set to a specified number of rows after ORDER BY has sorted spend descending, satisfying the requirement to return only the first 10 customers. It applies after sorting, so the highest spenders are retained rather than an arbitrary subset.

A CTE names the pre-aggregated result set, separating it from the join logic so the query reads top-down and is easier to maintain. It also supports recursion, unlike a plain subquery, which satisfies the readability and recursive capability requirement.

INNER JOIN returns only rows with matching keys in both tables, making it one of SQL's core join types. The stem asks for join types, and INNER JOIN is a distinct, standard category alongside LEFT, RIGHT and FULL OUTER joins.

A lag feature shifts a variable's values backward by a defined number of time steps, so each observation carries the value from an earlier period. This exposes temporal dependence to the model, letting it learn autocorrelation and trends rather than treating each timestamp as independent.

Indexes on frequently filtered columns let the optimiser seek directly to matching rows instead of scanning the whole table, cutting I/O for WHERE, JOIN and ORDER BY predicates. This satisfies the stem's demand for a legitimate query-performance optimisation.

A scatter plot places one numerical variable on each axis, so each point represents a paired observation. This reveals correlation, trends, and clusters between the two measures, which bar, line, and pie charts cannot show because they plot a single measure against categories or time.

Transformation applies business rules, cleansing and validation to extracted data before it is loaded, so checks such as age being positive are enforced here. Extraction only retrieves source data and loading writes it to the target, neither of which validates business rules.

SUM totals numeric values across rows, satisfying the stem's requirement for a widely supported aggregate. It is part of the SQL standard and implemented across virtually all relational databases, including SQL Server, Oracle, PostgreSQL and MySQL. This ubiquity confirms it as one of the three commonly supported functions the question demands.

Interactive filters let users narrow the displayed dataset to the dimensions and ranges they need, so the dashboard presents only relevant subsets rather than every record at once. This directly reduces cognitive overload while preserving access to the full underlying data.

Quarantining records with NULL or malformed emails enforces the business rule by preventing invalid customer records from entering the trusted dataset while preserving them for correction. This approach handles both missing and invalid values and supports data quality remediation rather than silent data loss, which aligns with the requirement that every customer record must have a valid email.

INSERT appends new rows to an existing table, specifying target columns and values. UPDATE modifies existing rows, DELETE removes them, and CREATE TABLE defines structure, so INSERT is the only command that adds records as the question requires.

GROUP BY region collapses rows into one per region, and AVG(order_total) computes the average order value for each group. This returns exactly one row per region with the average, which is the required output. It is the standard and most efficient way to produce per-group aggregates in SQL.

Normalizing casing ensures consistency for joins.

A window function with PARTITION BY customer_id ORDER BY order_date and SUM() computes a running total per customer without collapsing rows. The PARTITION BY restarts the sum for each customer, and the ORDER BY defines the accumulation sequence. This returns every order row plus the cumulative total, exactly matching the requirement for a per-customer running total ordered by date.

Placing the order status filter in the ON clause of a LEFT JOIN preserves all customers from the left table. Customers with no valid orders will have NULL values from the orders table, allowing aggregation to produce NULL or zero AOV. This correctly satisfies the requirement to show all customers while excluding cancelled and returned orders from the calculation.

This query correctly groups rows by category, computes the sum of amount for each group, and then filters the grouped results using HAVING to include only categories where the total exceeds $10,000. The HAVING clause is specifically designed to filter aggregated results, making it the right choice for this scenario.

`plt.figure()` creates a new Figure object and registers it with pyplot's state machine, returning it for subsequent plotting calls. This directly satisfies the stem's requirement to add a new figure to the canvas, unlike axis-level or subplot functions that operate within an existing figure.

The IN operator with a subquery tests whether each customer_id appears in the set of customer_id values returned by the orders subquery. This correctly filters customers who have at least one order, regardless of duplicates in the subquery. It is the standard and readable way to express a semi-join in SQL.

Completeness measures whether all required values are actually populated, so it refers to the absence of missing values in a dataset. Records with null or absent entries reduce completeness, distinguishing it from accuracy, which concerns correctness of present values.

In a LEFT JOIN, each row from the left table (customers) is combined with matching rows from the right table (orders). If a customer has multiple orders, that customer will appear multiple times in the result set, once for each order. This is expected behavior and the most common reason for duplicate customer rows in a LEFT JOIN result.

DISTINCT eliminates duplicate rows from the result set by comparing all selected column values, returning each unique department name once. It directly satisfies the requirement that results contain no duplicate entries, unlike keywords that filter rows conditionally or aggregate values rather than deduplicating the projected output.

Deduplicating the lookup table so that each join key appears once ensures the inner join returns at most one lookup row per fact row, eliminating the row multiplication. This can be done with a subquery that groups by the key or uses a window function such as ROW_NUMBER to pick a representative row. It directly addresses the duplicate-key cause of the inflated row count.

Materialising the view precomputes and stores the result set, so the visualisation queries a physical table instead of re-executing the underlying joins and aggregations on every refresh. This directly removes the repeated full-view scan that causes the bottleneck, satisfying the large SQL view constraint.

DENSE_RANK() assigns ranks without gaps, so ties receive the same rank and the next rank is the next consecutive integer (e.g., 1,2,2,3). This matches the requirement to assign consecutive ranks without gaps. It is specifically designed for this purpose and is the correct choice.

Wrapping the indexed column in a function, such as UPPER(column), prevents the optimiser from using the index because the stored values no longer match the computed expression. The database therefore falls back to a full table scan despite the WHERE clause referencing that column.

A calendar spine guarantees every month appears for every customer, and LEFT JOIN preserves customers with no transactions. Conditional aggregation can then count transactions in M-3 and confirm zero in M-2, M-1, and M, correctly identifying churn even when no rows exist. This approach explicitly handles absent activity, which the definition requires.

Third normal form eliminates transitive dependencies, where a non-key attribute depends on another non-key attribute rather than the primary key. Achieving 3NF therefore requires every non-key column to depend solely on the key, satisfying the stem's stated goal of removing those indirect relationships.

This query counts all rows where the Email column has no value, which is exactly the metric the engineer needs before choosing a cleansing approach. The IS NULL predicate is the only standard way to test for missing values reliably, and COUNT(*) includes rows regardless of other column values, giving an accurate total of missing emails.

Aggregate queries collapse rows, so every non-aggregated SELECT column must appear in GROUP BY to define grouping keys. The named column is absent from that clause, leaving the engine unable to determine which value to return per group.

WHERE filters rows before grouping, satisfying the stem's requirement for a standard SELECT component. It evaluates each row against a predicate, restricting which records proceed to later clauses such as GROUP BY or ORDER BY. This row-level filtering distinguishes it from HAVING, which filters aggregated groups after GROUP BY has been applied.

A dashboard supports interactive filtering, drill-down and live data refresh, letting users explore dimensions and uncover patterns on demand. A static report presents fixed, pre-rendered output, so it cannot answer follow-up questions without regeneration, making dynamic exploration the dashboard's core benefit.

The Data-Ink Ratio, proposed by Edward Tufte, measures the proportion of ink devoted to actual data versus decorative elements. Maximising it removes chartjunk such as redundant gridlines and 3D effects, directly improving readability and clarity so viewers interpret the data faster and more accurately.

JSON stored in relational columns is queried with path-based operators such as JSON_VALUE or the -> and ->> operators, which traverse the document hierarchy to reach nested keys. These operators return the value at a specified path, unlike plain column selection.

LEFT JOIN returns every row from the left table and matching rows from the right, filling unmatched right-side columns with NULL. It satisfies the stem's requirement to retain all left-table records despite missing matches, unlike INNER JOIN, which discards them.

Line charts plot data points connected by straight segments along a continuous time axis, making them the standard visualisation for showing trends and rate of change over time. This directly satisfies the stem's requirement to represent trends over time.

Sources frequently represent the same field differently — dates as DD/MM/YYYY versus MM-DD-YYYY, or varying numeric precision — which breaks parsing and comparison during merging. Standardising inconsistent data formats is thus a common integration issue, directly satisfying the stem's scenario of combining disparate sources.

A direct many-to-many join between orders and products multiplies rows because each order matches many products and each product matches many orders. Without a bridge table like order_items that resolves the relationship, the result set expands combinatorially. Introducing the proper junction table with quantity and price attributes restores the intended grain.

An email message body is free-form text with no predefined schema or tabular structure, making it unstructured. Relational tables, CSV columns and fixed-format records are structured; the analyst must parse or apply natural-language techniques to ingest this content.

The ORDER BY clause inside the OVER() specification defines the row sequence the window function processes, which is essential for cumulative running totals. Without it, the frame is unordered and the sum accumulates arbitrarily, producing incorrect totals regardless of partitioning.

Missing values in the measure column can bias averages because aggregate functions like AVG ignore NULLs, effectively changing the denominator for each segment. Explicitly deciding to exclude or impute them, and documenting the choice, ensures the comparison reflects a known and consistent population. Leaving them unaddressed risks misleading differences between segments that are actually artifacts of missing data.

Using ROW_NUMBER() partitioned by user_id and ordered by event_timestamp descending assigns rank 1 to the latest event per user. Filtering on row number = 1 returns exactly one latest event per user. This eliminates the extra join and subquery, often reducing shuffle and improving performance in Hive, especially when combined with partition pruning on event_date.

COALESCE returns the first non-NULL argument, so wrapping LAG(...) OVER (...) with COALESCE(..., 0) replaces the NULL growth for the first month per region with 0. This is the standard SQL approach to handle missing previous-period values, preventing downstream arithmetic or visualization errors while preserving the intended growth calculation for later months.

KPI expands to Key Performance Indicator, so the 'P' denotes Performance — the measurable value tracking how effectively an objective is met. It is not Productivity or Profit, which do not appear in the standard acronym.