MCQs on Advanced Querying and Analytics | Analytical Functions

Analytical Functions (ROW_NUMBER, RANK, DENSE_RANK)

1. Which of the following functions assigns a unique number to each row in the result set based on the order specified?
   • a) RANK
   • b) ROW_NUMBER
   • c) DENSE_RANK
   • d) NTILE
2. Which analytical function can be used to assign ranks to rows with the same value, skipping the subsequent rank?
   • a) ROW_NUMBER
   • b) DENSE_RANK
   • c) RANK
   • d) NTILE
3. The function ROW_NUMBER() is used for:
   • a) Ranking rows with duplicates
   • b) Assigning a unique number to each row in a result set
   • c) Returning the rank of a row among its peers
   • d) Returning the cumulative sum
4. Which of the following is true about the RANK() function?
   • a) It generates consecutive ranking without skipping any numbers.
   • b) It ranks the rows based on their order without skipping ranks for ties.
   • c) It assigns the same rank for rows with the same values but skips the next rank.
   • d) It calculates a cumulative total for each row.
5. What is the primary difference between RANK() and DENSE_RANK()?
   • a) RANK() does not assign ranks to duplicate rows, whereas DENSE_RANK() does.
   • b) DENSE_RANK() does not skip rank numbers for ties, whereas RANK() does.
   • c) DENSE_RANK() skips rank numbers for ties, whereas RANK() does not.
   • d) RANK() calculates cumulative values, while DENSE_RANK() does not.

Window Functions and PARTITION BY

6. Which of the following SQL clauses is used with window functions to break the result set into partitions?
   • a) GROUP BY
   • b) HAVING
   • c) PARTITION BY
   • d) ORDER BY
7. The PARTITION BY clause in a window function is used to:
   • a) Organize rows in groups for window function calculation
   • b) Filter rows based on specified conditions
   • c) Sort the rows in ascending order
   • d) Perform a GROUP BY operation
8. Which of the following window functions calculates the running total of a numeric expression?
   • a) SUM()
   • b) COUNT()
   • c) ROW_NUMBER()
   • d) CUME_DIST()
9. Which of these functions can be used to calculate the moving average of a series of data points?
   • a) SUM()
   • b) AVG()
   • c) LEAD()
   • d) LAG()
10. In a query with a PARTITION BY clause, which window function would you use to calculate the cumulative distribution of values?
    • a) RANK()
    • b) NTILE()
    • c) CUME_DIST()
    • d) LEAD()

Using ROLLUP and CUBE for Aggregations

11. The ROLLUP operator is used to:
    • a) Summarize data by producing subtotals and grand totals
    • b) Pivot data for comparison
    • c) Perform a detailed row-by-row calculation
    • d) Remove duplicate records
12. Which of the following is a key benefit of using CUBE in aggregation queries?
    • a) It calculates subtotals for each group
    • b) It produces all possible combinations of subtotals
    • c) It only provides the overall total
    • d) It organizes the results into a hierarchical structure
13. What is the result of applying the ROLLUP operator in SQL?
    • a) It only shows the grand total.
    • b) It shows the total and subtotals for each group.
    • c) It aggregates only the distinct values.
    • d) It partitions data based on groups.
14. Which aggregation operation can you perform with CUBE?
    • a) Only summing values
    • b) Performing a simple group by operation
    • c) Summing, averaging, and counting combinations of data
    • d) Sorting and filtering results
15. How do ROLLUP and CUBE differ in terms of result sets?
    • a) ROLLUP generates fewer rows than CUBE.
    • b) CUBE generates a more detailed result set with all possible combinations.
    • c) ROLLUP can only generate grand totals, while CUBE does not.
    • d) CUBE produces subtotals for specific groups, while ROLLUP does not.

Using PIVOT and UNPIVOT Operators

16. The PIVOT operator is used to:
    • a) Rotate data from columns into rows
    • b) Rotate data from rows into columns
    • c) Calculate sums and averages across rows
    • d) Merge multiple rows into one
17. Which of the following statements about the PIVOT operator is true?
    • a) It is used to transform rows into columns in an aggregated format.
    • b) It is used to pivot columns into rows for better analysis.
    • c) It aggregates data using only numerical values.
    • d) It requires multiple tables for its operation.
18. What does the UNPIVOT operator do?
    • a) It rotates rows into columns.
    • b) It filters rows from a table.
    • c) It reverses the effect of PIVOT, turning columns into rows.
    • d) It eliminates null values from columns.
19. In SQL, the PIVOT operation is primarily used in:
    • a) Creating calculated fields
    • b) Rotating data to make analysis easier
    • c) Filtering data based on conditions
    • d) Aggregating data by specific groups
20. Which of the following would you use to convert sales data from multiple months into separate columns for each month?
    • a) UNPIVOT
    • b) JOIN
    • c) PIVOT
    • d) MERGE

Hierarchical Queries and CONNECT BY

21. The CONNECT BY clause is used for:
    • a) Joining two or more tables based on a common column
    • b) Performing hierarchical queries to navigate tree-like data
    • c) Aggregating results across multiple tables
    • d) Filtering data from multiple sources
22. In hierarchical queries, which function is used to get the parent row of the current row?
    • a) CONNECT_BY_ROOT
    • b) PRIOR
    • c) LEVEL
    • d) SYS_CONNECT_BY_PATH
23. What does the LEVEL pseudo-column represent in hierarchical queries?
    • a) The level of nesting in a recursive query
    • b) The total number of rows in a table
    • c) The aggregate value of data within a row
    • d) The hierarchical depth of the current row
24. Which keyword is used in CONNECT BY queries to retrieve the first row of a hierarchy?
    • a) CONNECT_BY_ROOT
    • b) LEVEL
    • c) PRIOR
    • d) SYS_CONNECT_BY_PATH
25. Which of the following SQL functions is used to combine columns in a hierarchical query to show the path from the root to the current row?
    • a) SYS_CONNECT_BY_PATH
    • b) CONNECT_BY_ROOT
    • c) LEVEL
    • d) PRIOR
26. In a hierarchical query, how can you limit the depth of the hierarchy?
    • a) By using the PRIOR function
    • b) By using the LEVEL pseudo-column in the WHERE clause
    • c) By using the CONNECT_BY clause
    • d) By setting a fixed number of rows
27. In the CONNECT BY clause, what does the PRIOR keyword specify?
    • a) The previous row in a table
    • b) The row that comes before the current row in the hierarchy
    • c) The next row in the hierarchy
    • d) The root of the tree
28. Which type of query uses the CONNECT BY and PRIOR keywords to create a hierarchical structure?
    • a) Linear query
    • b) Recursive query
    • c) Self-join query
    • d) Aggregated query
29. The SYS_CONNECT_BY_PATH function is useful for:
    • a) Filtering rows based on a path expression
    • b) Retrieving the path from the root to the current row in hierarchical data
    • c) Summing values for each row in the path
    • d) Calculating the hierarchy’s level for each row
30. In Oracle, when performing hierarchical queries, the CONNECT BY clause requires which of the following?
    • a) A direct link between columns in the same table
    • b) A condition specifying the relationship between parent and child rows
    • c) A table with no primary key
    • d) A window function for aggregation

Answer Key: