Oracle SQL Oracle SQL Advanced Features

Question 1

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

Accepted Answer

RANK()

Answer

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

Question 2

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

Accepted Answer

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

Answer

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

Question 3

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

Accepted Answer

WITH

Answer

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

Question 4

What does the LAG analytic function do in Oracle SQL?

Accepted Answer

Retrieves data from a previous row without a self-join

Answer

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

Question 5

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

Accepted Answer

CONNECT BY ... START WITH

Answer

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

Oracle SQL Practice Test