Intermediate SQL test (Intermediate)

Sample questions

5 sample questions from the Intermediate SQL test, with the answers. Samples are taken out of the live test, so you will not meet these when you sit it.

1. What is a potential consequence of using non-unique CTE names within the same query scope?

   1. CTE names must always be prefixed with a specific keyword to avoid naming conflicts.
   2. Using duplicate CTE names in different parts of the query does not affect performance or complexity.
   3. Using a CTE name that is not unique within the same query scope can lead to performance issues due to internal resolution conflicts.
   4. CTE names are automatically resolved by the SQL engine, so naming conflicts are not a concern.

   Answer: C. Using a CTE name that is not unique within the same query scope can lead to performance issues due to internal resolution conflicts.

   In SQL, when using Common Table Expressions (CTEs), it is important to ensure that the names are unique within the same query scope. If a CTE name is not unique, the SQL engine may have to perform additional work to resolve which CTE is being referenced, potentially leading to performance degradation. This is because the query optimizer may not be able to efficiently plan the execution of the query if there are name conflicts. Other options may suggest incorrect or misleading information about CTE naming and its impact on performance.

2. Which of the following statements correctly describes the appropriate use cases for CTEs and subqueries?

   1. CTEs are better for breaking down complex queries into smaller, reusable parts, while subqueries are better for simple, one-time calculations.
   2. CTEs are better for simple, one-time calculations, while subqueries are better for breaking down complex queries into smaller, reusable parts.
   3. CTEs and subqueries are functionally identical and can be used interchangeably in all situations.
   4. CTEs are better for nested queries, while subqueries are better for top-level queries.

   Answer: A. CTEs are better for breaking down complex queries into smaller, reusable parts, while subqueries are better for simple, one-time calculations.

   CTEs (Common Table Expressions) allow for better readability and reusability in complex queries, especially when multiple parts of the query depend on the same subquery. Subqueries are more suitable for simple, one-time calculations that do not require reuse. This makes the statement in option 3 correct.

3. Which of the following statements accurately describes the impact of index selectivity on query performance?

   1. Low-selectivity columns rarely benefit from indexing, while high-selectivity columns are good candidates for indexing.
   2. Indexing has no impact on query performance, regardless of selectivity.
   3. Low-selectivity columns are always better indexed than high-selectivity columns.
   4. High-selectivity columns rarely benefit from indexing, while low-selectivity columns are good candidates for indexing.

   Answer: A. Low-selectivity columns rarely benefit from indexing, while high-selectivity columns are good candidates for indexing.

   Index selectivity refers to how unique the values in a column are. High-selectivity columns (those with many distinct values) are better candidates for indexing because they allow the database to quickly narrow down the search results. Low-selectivity columns (such as boolean flags or status fields with few distinct values) do not benefit much from indexing because the index would not reduce the number of rows scanned significantly. Therefore, the correct statement is that low-selectivity columns rarely benefit from indexing, while high-selectivity columns are good candidates for indexing.

4. In a project where you need to rank sales representatives by their monthly sales, and you want to ensure that the ranking does not skip numbers even for tied values, which of the following statements is correct?

   1. DENSE_RANK() assigns a unique rank to every row, whereas RANK() assigns the same rank to tied rows.
   2. DENSE_RANK() assigns the same rank to tied rows but does not skip the next rank value, whereas RANK() skips the next rank value for tied rows.
   3. RANK() assigns a unique rank to every row, whereas DENSE_RANK() assigns the same rank to tied rows.
   4. RANK() assigns the same rank to tied rows but does not skip the next rank value, whereas DENSE_RANK() skips the next rank value for tied rows.

   Answer: B. DENSE_RANK() assigns the same rank to tied rows but does not skip the next rank value, whereas RANK() skips the next rank value for tied rows.

   DENSE_RANK() does not skip the next rank value when there are ties, unlike RANK(). This makes DENSE_RANK() more suitable for scenarios where continuous ranking is needed, such as calculating sales performance rankings where ties are common.

5. Which of the following best describes the behavior of the ROW_NUMBER() function in SQL?

   1. The ROW_NUMBER() function skips numbers when there are ties in the ordered column.
   2. The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for each partition.
   3. The ROW_NUMBER() function resets to 1 for every row in the result set, regardless of partitioning.
   4. The ROW_NUMBER() function assigns the same number to all rows with the same value in the ordered column.

   Answer: B. The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for each partition.

   ROW_NUMBER() assigns a unique number to each row within a partition, starting from 1. It does not skip numbers, unlike RANK() or DENSE_RANK(). It also does not reset for every row, unlike other functions like NTILE().