All MCQs

Indexes can support efficient probes, ordering, or reduced scans on join columns.
The optimizer still chooses the join algorithm based on row counts and costs.

Early filtering reduces I/O, transfer, join inputs, and aggregation work.
Modern engines may push predicates into scans, remote sources, or storage segments.

A sargable predicate leaves the indexed column unwrapped so the engine can seek a range.
Applying functions to the column often prevents efficient index navigation.

The plan shows how the optimizer intends to execute the SQL statement.
It helps identify scans, bad estimates, spills, and missing access paths.

FIRST_VALUE follows the window ordering and frame definition.
It can repeat a baseline value across rows for comparison.

NTILE divides ordered rows into a requested number of groups.
It is often used for quartiles, deciles, and customer segmentation.

A window SUM over all result rows supplies the denominator while retaining each region.
The regional amount divided by that total yields its share.

A bounded window frame computes the average over a sliding set of periods.
As the current row changes, the three-row frame moves with it.

A cumulative frame from the first row through the current row produces a running total.
The ORDER BY defines the sequence in which values accumulate.

LEAD accesses a later row within the ordered partition.
It can compare a current value with the next period or milestone.

LAG accesses an earlier row without a self-join.
It is useful for period-over-period differences and change detection.

DENSE_RANK advances by one distinct ordered value.
Therefore the rank sequence remains consecutive after ties.