Oracle SQL Oracle SQL Advanced Features 2 — Questions and Answers
Question 1: Which Oracle SQL analytic function calculates a running total?
- RUNNING_SUM()
- SUM() OVER (ORDER BY) (Correct answer)
- CUMULATIVE_SUM()
- TOTAL() OVER ()
Correct answer: SUM() OVER (ORDER BY)
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?
- Splits a delimited string into rows
- Aggregates string values from multiple rows into a single delimited string (Correct answer)
- Lists all aggregate functions available
- Returns the aggregate count of a list
Correct answer: Aggregates string values from multiple rows into a single delimited string
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?
- PERCENT_RANK()
- CUME_DIST() (Correct answer)
- PERCENTILE_CONT()
- RATIO_TO_REPORT()
Correct answer: CUME_DIST()
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?
- Converts columns into rows (Correct answer)
- Converts rows into columns
- Pivots data back to its original position
- Reverses the ORDER BY clause
Correct answer: Converts columns into rows
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?
- ROWS BETWEEN (Correct answer)
- WINDOW FRAME
- RANGE SET
- BETWEEN ROWS
Correct answer: ROWS BETWEEN
ROWS BETWEEN n PRECEDING AND n FOLLOWING (or CURRENT ROW/UNBOUNDED) defines the sliding window of rows for analytic function calculations.
Question 6: What does the LEAD analytic function do in Oracle SQL?
- Accesses data from a row before the current row
- Accesses data from a row after the current row (Correct answer)
- Returns the first row in a partition
- Calculates the lead time between two dates
Correct answer: Accesses data from a row after the current row
LEAD(column, n) accesses data from a row n positions after the current row, the forward-looking complement to LAG.
Oracle SQL Oracle SQL Advanced Features 2
Which Oracle SQL analytic function calculates a running total?