Chapter 10 Quiz: Advanced SQL — Window Functions, OLAP Functions, Recursive Queries, and Temporal Queries

Instructions: Choose the single best answer for each question. Questions cover conceptual understanding, syntax accuracy, and practical application of advanced SQL features in DB2.

Question 1

What is the fundamental difference between a GROUP BY query and a window function?

A) GROUP BY is faster than window functions B) Window functions collapse rows into groups; GROUP BY preserves individual rows C) GROUP BY collapses rows into groups; window functions preserve individual rows while adding computed columns D) Window functions can only be used with numeric columns

Question 2

At what point in the logical query execution order are window functions evaluated?

A) Before the WHERE clause B) After WHERE and GROUP BY, but before DISTINCT and the final ORDER BY C) After the final ORDER BY D) Simultaneously with the WHERE clause

Question 3

Which of the following is a valid reason you CANNOT use a window function in a WHERE clause?

A) Window functions are too slow for filtering B) The WHERE clause executes before window functions are computed, so the values do not exist yet C) DB2 does not allow functions of any kind in WHERE clauses D) Window functions can only return NULL in WHERE clauses

Question 4

Given the following data after ordering by balance DESC:

What values do RANK() and DENSE_RANK() assign to customer C?

A) RANK: 2, DENSE_RANK: 2 B) RANK: 3, DENSE_RANK: 3 C) RANK: 3, DENSE_RANK: 2 D) RANK: 2, DENSE_RANK: 3

Question 5

You need to divide 103 customers into 4 equal groups using NTILE(4). How are the customers distributed?

A) 25, 25, 25, 28 B) 26, 26, 26, 25 C) 26, 26, 25, 26 D) 25, 26, 26, 26

Question 6

What is the default frame specification when ORDER BY is present in a window function but no explicit frame is specified?

A) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING B) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW C) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW D) RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Question 7

Why can the default frame (with ORDER BY present) produce unexpected results for running totals when multiple rows share the same ORDER BY value?

A) DB2 skips duplicate rows in the default frame B) RANGE mode includes all rows with the same ORDER BY value as the current row, causing the total to "jump" instead of incrementing one row at a time C) The default frame excludes the current row D) DB2 randomly orders tied rows, producing nondeterministic totals

Question 8

Which frame specification creates a 7-day moving average window (current row plus the 6 preceding rows)?

A) ROWS BETWEEN 7 PRECEDING AND CURRENT ROW B) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW C) ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING D) RANGE BETWEEN 7 PRECEDING AND CURRENT ROW

Question 9

What does LAG(total_deposits, 12) OVER (PARTITION BY branch_id ORDER BY yr, mo) return?

A) The total deposits 12 months into the future B) The total deposits from 12 rows earlier in the partition, enabling year-over-year comparison C) The 12th percentile of total deposits D) NULL, because LAG only accepts an offset of 1

Question 10

What is the most common bug when using LAST_VALUE() with ORDER BY?

A) LAST_VALUE always returns NULL B) The default frame ends at CURRENT ROW, so LAST_VALUE returns the current row's value instead of the partition's last value C) LAST_VALUE does not work with PARTITION BY D) LAST_VALUE requires DENSE_RANK to function correctly

Question 11

In a recursive CTE, which part is executed first?

A) The recursive member B) The UNION ALL clause C) The anchor member D) The outer SELECT

Question 12

Which statement about recursive CTEs in DB2 is TRUE?

A) UNION (with deduplication) is supported in recursive CTEs B) UNION ALL is required — UNION is not supported in recursive CTEs C) Recursive CTEs automatically detect and handle cycles D) Recursive CTEs can reference themselves in both the anchor and recursive members

Question 13

You have an EMPLOYEES table where manager_id references employee_id. Which technique correctly traverses the entire organizational hierarchy from the CEO down to all levels?

A) A self-join of EMPLOYEES to EMPLOYEES B) A recursive CTE with the CEO as the anchor and a join on manager_id as the recursive step C) A ROLLUP on manager_id D) A window function with PARTITION BY manager_id

Question 14

What is the purpose of a depth limit in a recursive CTE?

A) To improve query performance by using indexes B) To prevent runaway recursion that could consume excessive resources or run infinitely due to cyclic data C) To sort the results by depth level D) DB2 requires a depth limit for all recursive CTEs

Question 15

In DB2, a system-time temporal table automatically tracks:

A) When facts are effective in the real world (business validity periods) B) When rows were physically inserted, updated, or deleted in the database C) Both business validity and system transaction times simultaneously D) The order in which rows were read by queries

Question 16

What does FOR SYSTEM_TIME AS OF '2024-06-15' do in a query?

A) Filters rows where a date column equals June 15, 2024 B) Returns the version of each row that was current (active) in the database at June 15, 2024, combining the current and history tables transparently C) Shows rows that were deleted on June 15, 2024 D) Sets the query's execution timestamp to June 15, 2024

Question 17

In a business-time temporal table with PERIOD BUSINESS_TIME (bus_start, bus_end) and PRIMARY KEY (loan_type, BUSINESS_TIME WITHOUT OVERLAPS), what does the WITHOUT OVERLAPS constraint enforce?

A) That no two rows in the entire table can have overlapping business-time periods B) That no two rows with the same loan_type can have overlapping business-time periods C) That bus_start must always be before bus_end D) That business-time periods cannot span more than one year

Question 18

What makes a bitemporal table different from a system-time-only temporal table?

A) Bitemporal tables support more data types B) Bitemporal tables track both when the database physically changed (system-time) AND when facts are effective in the real world (business-time) C) Bitemporal tables are faster for queries D) Bitemporal tables do not require a history table

Question 19

What does ROLLUP(A, B, C) in a GROUP BY clause produce?

A) All 8 possible combinations of groupings of A, B, and C B) Groupings (A,B,C), (A,B), (A), and () — a hierarchical set of subtotals plus a grand total C) Only the grouping (A,B,C) and the grand total () D) Groupings (A), (B), (C), and ()

Question 20

How does CUBE(A, B) differ from ROLLUP(A, B)?

A) CUBE produces fewer groupings than ROLLUP B) CUBE produces groupings (A,B), (A), (B), and () — all possible combinations — while ROLLUP produces only (A,B), (A), and () C) CUBE and ROLLUP produce identical results D) CUBE only works with numeric columns

Question 21

What does the GROUPING() function return?

A) The group number assigned by GROUP BY B) 1 when the column's NULL value represents "all values" in a subtotal row, 0 otherwise C) The count of rows in each group D) The position of the column in the GROUP BY list

Question 22

Which of the following correctly uses GROUPING SETS to produce exactly two groupings: (branch_id, account_type) and (branch_id)?

A) GROUP BY GROUPING SETS (branch_id, account_type) B) GROUP BY GROUPING SETS ((branch_id, account_type), (branch_id)) C) GROUP BY ROLLUP (branch_id, account_type) D) GROUP BY CUBE (branch_id, account_type)

Question 23

A recursive CTE uses LOCATE(CHAR(employee_id), path) = 0 in its WHERE clause. What is this technique called and why is it used?

A) Index optimization — it speeds up the recursive join B) Cycle detection — it prevents an employee from appearing twice in their own hierarchy path, avoiding infinite recursion C) Path filtering — it limits results to employees not in the path D) String aggregation — it builds the hierarchy path

Question 24

You want to calculate a running total that resets at the beginning of each calendar year, partitioned by branch. Which PARTITION BY clause achieves this?

A) PARTITION BY branch_id B) PARTITION BY branch_id, YEAR(txn_date) C) PARTITION BY YEAR(txn_date) D) PARTITION BY branch_id ORDER BY YEAR(txn_date)

Question 25

Which combination of advanced SQL features would you use to build an executive dashboard that shows branch-level monthly totals, branch rankings that change over time, and multi-level subtotals in a single result set?

A) Only recursive CTEs B) Only temporal tables C) Aggregate window functions for totals, RANK() for rankings, LAG() for trend comparison, and ROLLUP for multi-level subtotals D) Only GROUPING SETS

Answer Key