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

ROW_NUMBER()

Answer

DENSE_RANK()

Answer

NTILE()

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

Splits the physical table into partitions

Answer

Filters rows before the analytic function runs

Answer

Groups rows like GROUP BY does

Question 3

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

Accepted Answer

WITH

Answer

TEMP AS

Answer

DEFINE

Answer

COMMON TABLE

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

Retrieves data from the next row in the result set

Answer

Calculates running totals

Answer

Ranks rows within a partition

Question 5

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

Accepted Answer

CONNECT BY ... START WITH

Answer

HIERARCHICAL JOIN

Answer

RECURSIVE JOIN

Answer

TREE SELECT

Question 6

What does the PIVOT clause do in Oracle SQL?

Accepted Answer

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

Answer

Sorts data by multiple columns simultaneously

Answer

Transposes the table structure permanently

Answer

Creates a pivot table in Excel format