Oracle SQL Oracle SQL Advanced Features 1 — Questions and Answers
Question 1: Which Oracle SQL analytic function assigns a unique rank to each row, skipping ranks for ties?
- ROW_NUMBER()
- DENSE_RANK()
- RANK() (Correct answer)
- NTILE()
Correct answer: RANK()
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?
- Splits the physical table into partitions
- Divides the result set into groups for the analytic function to operate on (Correct answer)
- Filters rows before the analytic function runs
- Groups rows like GROUP BY does
Correct 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.
Question 3: Which Oracle SQL clause is used to define a Common Table Expression (CTE)?
- TEMP AS
- WITH (Correct answer)
- DEFINE
- COMMON TABLE
Correct answer: WITH
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?
- Retrieves data from the next row in the result set
- Retrieves data from a previous row without a self-join (Correct answer)
- Calculates running totals
- Ranks rows within a partition
Correct 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.
Question 5: Which Oracle SQL feature allows recursive queries for hierarchical data like org charts?
- HIERARCHICAL JOIN
- CONNECT BY ... START WITH (Correct answer)
- RECURSIVE JOIN
- TREE SELECT
Correct answer: CONNECT BY ... START WITH
Oracle's CONNECT BY clause with START WITH enables hierarchical queries, traversing parent-child relationships within a table.
Question 6: What does the PIVOT clause do in Oracle SQL?
- Rotates rows into columns, converting distinct row values into column headers (Correct answer)
- Sorts data by multiple columns simultaneously
- Transposes the table structure permanently
- Creates a pivot table in Excel format
Correct 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.
Oracle SQL Oracle SQL Advanced Features 1
Which Oracle SQL analytic function assigns a unique rank to each row, skipping ranks for ties?