Data+ (DA0-002). Set it up and run it.

1. A supplied extract opens with the rows: cust_id,postcode,balance | 00417,02139,1200.50 | 00418,60614,980.00 . After a default load the postcode column reads 2139 and 60614. Which fix is MOST appropriate?

• A. Load the postcode column as text so the leading zero survives
• B. Convert the postcode column to a float instead of an integer
• C. Round-trip the file through a spreadsheet so the formatting is restored
• D. Increase the numeric precision of the column before loading

Correct answer: A. A postcode is an identifier, not a quantity. Reading it as text preserves the leading zero and stops the loader from treating a code as a number it can do arithmetic on.

Why the others are wrong. B: A float discards leading zeros in exactly the same way and adds a decimal component the code does not have. C: A spreadsheet applies the same numeric inference and will drop the zero again on open. D: Precision governs how many significant digits are kept, and a leading zero is not a significant digit.

2. A 180-line reporting query contains the same 20-line subquery three times, and a change to the filtering logic must currently be made in all three places. Which two benefits follow from moving that subquery into a common table expression? (Choose two.)

• A. The logic is written and maintained in one place and referenced by name wherever it is needed
• B. The database materialises and indexes the CTE automatically, guaranteeing faster execution
• C. Each stage of the query becomes a named block, which makes the statement easier to read and test
• D. The CTE persists after the statement finishes so other queries in the session can reuse it
• E. The CTE removes the need for a GROUP BY in the outer query

Correct answer: A. A CTE names a result set once at the top of the statement and lets the rest of the query refer to it by name, so the logic is defined in one place and each stage of the query becomes a readable, separately testable block.

Why the others are wrong. B: Whether a CTE is materialised is dialect-specific and never indexed automatically, so no performance guarantee follows. D: A CTE lives only for the statement that defines it; a temporary table or view is needed for reuse across statements. E: A CTE only names a result set; any aggregation the report needs must still be written somewhere.

3. Opening a billing CSV in a spreadsheet, an analyst sees the account column display 4.61E+15 rather than 4610000000000417, and widening the cell shows the last three digits are now zeros. What has happened?

• A. The wrong delimiter was used, so two columns merged into one
• B. A byte order mark at the start of the file corrupted the field
• C. The identifier exceeded numeric precision and became a float
• D. A null in the column forced the whole column to change type

Correct answer: C. Spreadsheets and many loaders infer long digit strings as double-precision numbers, which carry roughly fifteen significant digits. A sixteen-digit account number exceeds that, so the low-order digits are lost and the value renders in scientific notation.

Why the others are wrong. A: A delimiter fault produces run-together text in one field, not a rounded number in scientific notation. B: A byte order mark affects only the first characters of the first line and shows as stray symbols. D: A missing value does not truncate the digits of the values that are present.

4. The orders table holds 1,200 rows and the customers table holds 800 rows, where customer_id is the primary key. Ninety orders carry a customer_id that is not present in customers. An analyst runs: SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id . What is returned?

• A. 1,200 rows, with name set to NULL on the 90 unmatched orders
• B. 1,110 rows, because the 90 unmatched orders are excluded from the result
• C. 1,200 rows, with name set to an empty string on the 90 unmatched orders
• D. 800 rows, because the join can never return more rows than the smaller table

Correct answer: A. A LEFT JOIN keeps every row of the left table, and because customer_id is unique in customers each order matches at most one customer. All 1,200 orders are returned, and the 90 with no match are NULL-extended, so name is NULL on exactly those 90 rows.

Why the others are wrong. B: That is the INNER JOIN result of 1,200 minus 90; a LEFT JOIN keeps unmatched left rows rather than discarding them. C: The row count is right but an outer join supplies NULL, not an empty string, and the two behave differently in comparisons and aggregates. D: Row counts are governed by matching, not by table size; a join between a 1,200-row and an 800-row table can return more rows than either.

5. A retail operations manager asks for units sold by store and by week for the last 13 weeks, with no explanation of the movements and no forecast attached. Which type of analysis does this request call for?

• A. Descriptive analysis
• B. Diagnostic analysis
• C. Predictive analysis
• D. Prescriptive analysis

Correct answer: A. Reporting what happened, summarised over a chosen grain and period without explaining causes or projecting forward, is descriptive analysis. The absence of any why or what next in the request is the tell.

Why the others are wrong. B: Diagnostic work explains why a figure moved; the manager has explicitly not asked for causes. C: Predictive work estimates a future value; the request covers 13 weeks that have already happened. D: Prescriptive work recommends an action to take; nothing in the request asks what the stores should do.

6. With 36 consecutive months of total revenue in hand, a retail analyst must show the finance director how revenue has moved over that period, including whether the summer dip repeats each year. Which visualisation is BEST?

• A. A line chart with month on the horizontal axis and revenue on the vertical axis
• B. A pie chart in which each of the 36 months is a slice of total revenue
• C. A column chart showing the three annual revenue totals side by side
• D. A treemap of revenue with rectangles sized by month and coloured by calendar year

Correct answer: A. A line chart places an ordered time variable on the horizontal axis and joins consecutive points, so both the overall direction and any repeating seasonal shape are read directly off the line.

Why the others are wrong. B: Slices carry no ordering, so a pie destroys the very sequence that makes a trend or a seasonal pattern visible. C: Three annual bars collapse the monthly detail, so neither the month-to-month movement nor the summer dip survives. D: A treemap encodes proportion and hierarchy, not sequence, so it cannot show when revenue rose or fell.

7. A marketing API returns: {"order_id": 88120, "customer": {"id": 41, "tier": "gold"}, "items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}]} . How is this data BEST described?

• A. Structured, because every field is named and typed
• B. Semi-structured, because it is self-describing but nested
• C. Unstructured, because it has no schema at all
• D. Structured, because it can be loaded to a relational table unchanged

Correct answer: B. The record labels every value with a field name but nests an object inside it and repeats a group of fields in an array. Self-describing data that does not sit in fixed rows and columns is semi-structured.

Why the others are wrong. A: Structured data fits a fixed row-and-column schema, and the repeating items array does not. C: Unstructured content such as audio or free text carries no field labels, whereas every value here is labelled. D: Loading it relationally first requires flattening the nested array into a separate child table.

8. A shipments table has 4,500 rows and a carriers table has 120 rows keyed on carrier_id. Profiling shows 210 shipments carry a carrier_id that does not exist in carriers. How many rows does SELECT s.shipment_id, c.carrier_name FROM shipments s INNER JOIN carriers c ON s.carrier_id = c.carrier_id return?

• A. 4,500
• B. 4,710
• C. 4,290
• D. 120

Correct answer: C. An INNER JOIN returns only rows that match on both sides. Each carrier_id is unique in carriers, so every matching shipment produces exactly one row: 4,500 minus the 210 orphaned shipments gives 4,290.

Why the others are wrong. A: That is what a LEFT JOIN from shipments would return, keeping the 210 orphans with NULL carrier details. B: This adds the orphans to the matched rows instead of removing them; the 210 rows are excluded once, not counted twice. D: The carriers table size does not cap the result; each carrier can appear on many shipments.