31.
What is the restriction that grouped queries impose on your expressions?
- A.If the query is a grouped query, you must invoke an aggregate function.
- B.If the query has an aggregate function, it must have a GROUP BY clause.
- C.The elements in the GROUP BY clause must also be specified in the SELECT clause.
- D.If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an aggregate function.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
|
A. Incorrect: You can group rows without invoking an aggregate function. B. Incorrect: A query can have an aggregate function without a GROUP BY clause. The grouping is implied—all rows make one group. C. Incorrect: There’s no requirement for grouped elements to appear in the SELECT list, though it’s common to return the elements that you group by. D. Correct: A grouped query returns only one row per group. For this reason, all expressions that appear in phases that are evaluated after the GROUP BY clause (HAVING, SELECT, and ORDER BY) must guarantee returning a single value per group. That’s where the restriction comes from. |
32.
What is the purpose of the GROUPING and GROUPING_ID functions? (Choose all that apply.)
- A.You can use these functions in the GROUP BY clause to group data.
- B.You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from the table.
- C.You can use these functions to uniquely identify the grouping set that the result row is associated with.
- D.These functions can be used to sort data based on grouping set association—that is, first detail, and then aggregates.
- Answer & Explanation
- Report
Answer : [B, C, D]
Explanation :
Explanation :
|
A. Incorrect: These functions cannot be used in the GROUP BY clause. B. Correct: When the functions return a 1 bit, a NULL is a placeholder; when they return a 0 bit, the NULL originates from the table. C. Correct: Each grouping set can be identified with a unique combination of 1s and 0s returned by these functions. D. Correct: These functions can be used for sorting because they return a 0 bit for a detail element and a 1 bit for an aggregated element. So if you want to see detail first, sort by the result of the function in ascending order. |
33.
What is the difference between the COUNT(*) aggregate function and the
COUNT() general set function?
- A.COUNT(*) counts rows; COUNT(
) counts rows where is not NULL. - B.COUNT(*) counts columns; COUNT(
) counts rows. - C.COUNT(*) returns a BIGINT; COUNT(
) returns an INT. - D.There’s no difference between the functions.
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
|
A. Correct: The COUNT(*) function doesn’t operate on an input expression; instead,
it counts the number of rows in the group. The COUNT(<expression>) function
operates on an expression and ignores NULLs. Interestingly, COUNT(<expression>)
returns 0 when all inputs are NULLs, whereas other general set functions like MIN,
MAX, SUM, and AVG return a NULL in such a case. B. Incorrect: COUNT(*) counts rows. C. Incorrect: COUNT(*) returns an INT. D. Incorrect: Clearly, there is a difference between the functions in the treatment of NULLs. |
34.
How does the PIVOT operator determine what the grouping element is?
- A.It’s the element specified as input to the GROUPING function.
- B.It’s determined by elimination—the element(s) from the queried table that were not specified as the spreading or aggregation elements.
- C.It’s the element specified in the GROUP BY clause.
- D.It’s the primary key.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
|
A. Incorrect: The GROUPING function is related to grouping sets—not to pivoting. B. Correct: The PIVOT operator determines the grouping element by elimination— it’s what’s left besides the spreading and aggregation elements. C. Incorrect: When using the PIVOT operator, the grouping for pivoting happens as part of the PIVOT operator—before the GROUP BY clause gets evaluated. D. Incorrect: The PIVOT operator doesn’t look at constraint definitions to determine the grouping element. |
35.
Which of the following are not allowed in the PIVOT operator’s specification? (Choose
all that apply.)
- A.Specifying a computation as input to the aggregate function
- B.Specifying a computation as the spreading element
- C.Specifying a subquery in the IN clause
- D.Specifying multiple aggregate functions
- Answer & Explanation
- Report
Answer : [A, B, C, D]
Explanation :
Explanation :
|
A. Correct: You cannot specify a computation as input to the aggregate function,
rather just a name of a column from the input table. B. Correct: You cannot specify a computation as the spreading element, rather just a name of a column from the input table. C. Correct: You cannot specify a subquery in the IN clause, rather just a static list. D. Correct: You cannot specify multiple aggregate functions, rather just one. |
|
|
||||||||||||||||||||||||||||
