26.
What are the benefits of using a CTE over derived tables? (Choose all that apply.)
- A.CTEs are better performing than derived tables.
- B.CTEs don’t nest; the code is more modular, making it easier to follow the logic.
- C.Unlike with derived tables, you can refer to multiple instances of the same CTE name, avoiding repetition of code.
- D.Unlike derived tables, CTEs can be used by all statements in the session, and not just the statement defining them.
- Answer & Explanation
- Report
Answer : [B, C]
Explanation :
Explanation :
|
A. Incorrect: All types of table expressions are treated the same in terms of optimization—
they get unnested. B. Correct: If you want to refer to one derived table from another, you need to nest them. With CTEs, you separate those by commas, so the code is more modular and easier to follow. C. Correct: Because the CTE name is defined before the outer query that uses it, the outer query is allowed to refer to multiple instances of the same CTE name. D. Incorrect: CTEs are visible only in the scope of the statement that defined them. |
27.
What is the difference between the result of T1 CROSS APPLY T2 and T1 CROSS JOIN
T2 (the right table expression isn’t correlated to the left)?
- A.CROSS APPLY filters only rows where the values of columns with the same name are equal; CROSS JOIN just returns all combinations.
- B.If T1 has rows and T2 doesn’t, CROSS APPLY returns an empty set and CROSS JOIN still returns the rows from T1.
- C.If T1 has rows and T2 doesn’t, CROSS APPLY still returns the rows from T1 and CROSS join returns an empty set.
- D.There is no difference.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
|
A. Incorrect: Both return all combinations. B. Incorrect: Both return an empty set. C. Incorrect: Both return an empty set. D. Correct: Both return the same result when there’s no correlation because CROSS APPLY applies all rows from T2 to each row from T1. |
28.
Which of the following operators removes duplicates from the result? (Choose all that
apply.)
- A.UNION
- B.UNION ALL
- C.INTERSECT
- D.EXCEPT
- Answer & Explanation
- Report
Answer : [A, B, C]
Explanation :
Explanation :
|
A. Correct: UNION removes duplicates. B. Incorrect: UNION ALL doesn’t remove duplicates. C. Correct: INTERSECT removes duplicates. D. Correct: EXCEPT removes duplicates. |
29.
In which operator does the order of the input queries matter?
- A.UNION
- B.UNION ALL
- C.INTERSECT
- D.EXCEPT
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
|
A. Incorrect: With UNION, the order of the inputs doesn’t matter. B. Incorrect: With UNION ALL, the order of the inputs doesn’t matter. C. Incorrect: With INTERSECT, the order of the inputs doesn’t matter. D. Correct: With EXCEPT, the order of the inputs matters. |
30.
Which of the following is the equivalent of <query 1> UNION <query 2> INTERSECT
<query 3> EXCEPT <query 4>?
- A.(<query 1> UNION <query 2>) INTERSECT (<query 3> EXCEPT <query 4>)
- B.<query 1> UNION (<query 2> INTERSECT <query 3>) EXCEPT <query 4>
- C.<query 1> UNION <query 2> INTERSECT (<query 3> EXCEPT <query 4>)
- D.<query 1> UNION (<query 2> INTERSECT <query 3> EXCEPT <query 4>)
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
|
A. Incorrect: Without the parentheses, the INTERSECT precedes the other operators,
and with the specified parentheses, it gets evaluated last. B. Correct: Without the parentheses, the INTERSECT precedes the other operators, and with the specified parentheses, it’s the same. C. Incorrect: Without the parentheses, the INTERSECT precedes the other operators, and with the specified parentheses, EXCEPT is evaluated first. D. Incorrect: Without the parentheses, the UNION operator is evaluated second (after the INTERSECT), and with the specified parentheses, UNION is evaluated last. |
|
|
||||||||||||||||||||||||||||
