Group Functions

Question 1

Which group functions will consider NULL values?

Answer 1

COUNT(*) and GROUPING

Question 2

Can custom functions work on grouped data?

Answer 2

No

Question 3

If no GROUP BY clause is specified, the default grouping becomes ____

Answer 3

The entire result set

Question 4

What can be used as arguments for ORDER BY

Answer 4

the identifier or the column position (number)

Question 5

AVG

Answer 5

AVG([{DISTINCT | ALL}] n) where n is a numeric expression to be evaluated.

Question 6

COUNT

Answer 6

COUNT({* | [DISTINCT | ALL] })
If DISTINCT not specified, defaults to ALL
* counts all rows in result set, including NULL

Question 7

MAX

Answer 7

MAX([{DISTINCT | ALL}] )
Can be datetime, numeric or character.
Character returns highest based on character set.

Question 8

MIN

Answer 8

MIN([{DISTINCT | ALL}] )
Can be datetime, numeric or character.
Character returns highest based on character set.

Question 9

SUM

Answer 9

SUM([{DISTINCT | ALL}] )

Question 10

MEDIAN

Answer 10

MEDIAN() Returns middle value of result set.

Question 11

STDDEV

Answer 11

STDDEV([{DISTINCT | ALL}] ) Returns standard deviation of result set.

Question 12

VARIANCE

Answer 12

VARIANCE([{DISTINCT | ALL}] ) Returns numerical variance of result set.

Question 13

LISTAGG

Answer 13

LISTAGG( [col_name, ‘, ‘]) WITHIN GROUP ( ORDER BY )

Aggregates data from multiple rows into one row per group.

Question 14

CORR

Answer 14

CORR(y, x) Returns coefficient of the correlation of a set of number pairs.

Question 15

COVAR_POP

Answer 15

COVAR_POP(y, x) Returns population covariance of a set of number pairs. Covariance can return NULL.

Question 16

COVAR_SAMP

Answer 16

COVAR_SAMP(y, x) Returns the sample covariance of a set of number pairs. Covariance can return NULL.

Question 17

CUME_DIST

Answer 17

CUME_DIST() WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
where val_list is a comma-delimited list of expressions that evaluate to numeric constants and col_list is the comma-delimited list of column expressions.
Returns the cumulative distribution of a value in val_list within a distribution in col_list.
Result will be between 0 and 1.

Question 18

DENSE_RANK

Answer 18

DENSE_RANK(val_list) WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
where val_list is a comma-delimited list of expressions that evaluate to numeric constants and col_list is the comma-delimited list of column expressions.
Returns the row’s rank within an ordered group, which are consecutive integers starting with 1 reflecting the number of unique values returned by the query.

Question 19

GROUP_ID

Answer 19

GROUP_ID( )
No arguments
Requires GROUP BY
Returns a unique identifier for duplicate groups which is a value between 0 and i-1 where i is the number of duplicate groups.

Question 20

GROUPING

Answer 20

GROUPING(x) Applicable to statements with a ROLLUP or CUBE clause. Returns 1 for summary rows and 0 for nonsummary.

Question 21

PERCENT_RANK

Answer 21

PERCENT_RANK() WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
Returns the percent ranking of a value in val_list within a distribution in col_list in the range between 0 and 1. Can return 0 whereas CUME_DIST cannot.

Question 22

PERCENTILE_CONT

Answer 22

PERCENTILE_CONT() WITHIN GROUP (ORDER BY col_list [ASC|DESC])
Assumes a continuous distribution and returns the interpolated value that would fall in percentile position x within the sorted group col_list.

Question 23

PERCENTILE_DISC

Answer 23

Same as PERCENTILE_CONT but assumes discrete distribution and will return the middle-most of the values in the list.

Question 24

RANK

Answer 24

RANK() WITHIN GROUP (ORDER BY col_list [ASC|DESC] [NULLS {first|last}])
Like DENSE_RANK but will include duplicate values.
If there are three values tied for first place, DENSE_RANK would just list one of them as first and ignore the next two, putting the fourth in 2nd place. RANK on the other hand will list them all as in 1st place and place the 4th in 4th place.<»

Question 25

STDDEV_POP

Answer 25

STDDEV_POP() Returns numeric population standard deviation of x.

Question 26

STDDEV_SAMP

Answer 26

STDDEV_SAMP() Returns the numeric sample standard deviation of x.

Question 27

VAR_POP

Answer 27

VAR_POP() Returns population variance of x.

Question 28

VAR_SAMP

Answer 28

VAR_SAMP() Returns sample variance of x.

Question 29

HAVING

Answer 29

HAVING() Works like WHERE but for GROUP functions (WHERE can’t use group functions). Comes after GROUP BY

Question 30

ROLLUP

Answer 30

Used to return superaggregate rows. Will return an extra row of subtotals for each group. NULL values will be counted separately for each group and will have their own row unless using an NVL function.

Question 31

CUBE

Answer 31

Creates subtotals for all possible combinations of grouping columns.

Question 32

How many levels of nesting are allowed with group functions?

Answer 32

Any number for single-row within group, only one for group within group.