Oracle SQL Oracle SQL Advanced Features 2

Question 1

Which Oracle SQL analytic function calculates a running total?

Accepted Answer

SUM() OVER (ORDER BY)

Answer

SUM() used as an analytic function with OVER (ORDER BY column) computes a cumulative running total up to each row.

Question 2

What does the LISTAGG function do in Oracle SQL?

Accepted Answer

Aggregates string values from multiple rows into a single delimited string

Answer

LISTAGG(column, delimiter) concatenates values from multiple rows into a single string with the specified separator.

Question 3

Which Oracle SQL function returns the cumulative distribution of a value within a partition?

Accepted Answer

CUME_DIST()

Answer

CUME_DIST() calculates the cumulative distribution (fraction of partition rows at or before the current row) as a value between 0 and 1.

Question 4

What is the purpose of the UNPIVOT clause in Oracle SQL?

Accepted Answer

Converts columns into rows

Answer

UNPIVOT rotates columns into rows, the opposite of PIVOT, transforming wide data formats into tall (normalized) formats.

Question 5

Which Oracle SQL clause creates a window frame for analytic functions based on a range of rows?

Accepted Answer

ROWS BETWEEN

Answer

ROWS BETWEEN n PRECEDING AND n FOLLOWING (or CURRENT ROW/UNBOUNDED) defines the sliding window of rows for analytic function calculations.

Oracle SQL Practice Test