Oracle SQL Advanced Features Flashcards

Which Oracle SQL analytic function assigns a unique rank to each row, skipping ranks for ties?

Answer: RANK()

RANK() assigns the same rank to tied rows but skips the next rank number(s), causing gaps in the ranking sequence.

What does the PARTITION BY clause do in Oracle SQL analytic functions?

Answer: Divides the result set into groups for the analytic function to operate on

PARTITION BY divides the result set into logical groups (partitions) within which the analytic function is computed independently.

Which Oracle SQL clause is used to define a Common Table Expression (CTE)?

Answer: WITH

The WITH clause (also called a subquery factoring clause) defines named CTEs that can be referenced multiple times in the main query.

What does the LAG analytic function do in Oracle SQL?

Answer: Retrieves data from a previous row without a self-join

LAG(column, n) accesses data from a row n positions before the current row in the result set, useful for comparing sequential values.

Which Oracle SQL feature allows recursive queries for hierarchical data like org charts?

Answer: CONNECT BY ... START WITH

Oracle's CONNECT BY clause with START WITH enables hierarchical queries, traversing parent-child relationships within a table.

What does the PIVOT clause do in Oracle SQL?

Answer: Rotates rows into columns, converting distinct row values into column headers

The PIVOT clause transforms rows into columns by rotating distinct values of one column into separate column headings with aggregated values.