Deck 11: Group Functions

Question 1

The MAX function can be used with character data.

Answer 1

True

Question 2

If a group function is used in the WHERE clause, then any individual columns listed in the SELECT clause must also be listed in the GROUP BY clause.

Answer 2

False

Question 3

The MAX function can only be used with numeric or date columns.

Answer 3

False

Question 4

The COUNT function can be used to count NULL, as well as, non-NULL values.

Question 5

The optional UNIQUE keyword instructs Oracle 11g to include only unique numeric values in its calculation.

Question 6

Group functions return a group of results per row processed.

Question 7

The COUNT(*) function is only used to count the number of records containing NULL values.

Question 8

Group functions are also known as aggregate functions.

Question 9

The MIN function can be used with numeric, date, and character columns.

Question 10

Group functions return one result per group of rows processed.

Question 11

The GROUP BY clause can only be used when a SELECT statement includes a group function.

Question 12

By default, the AVG function will include NULL values when calculating the average of the numeric values in a specified column.

Question 13

By default, the SUM function assumes the ALL keyword and the result will include multiple occurrences of numeric values.

Question 14

The COUNT function can only be used for numeric columns.

Question 15

The DISTINCT keyword cannot be used with the MAX function.

Question 16

The only group function that includes NULL values by default is the MIN function.

Question 17

The SUM function is used to calculate the total amount stored in a numeric field for a group of records.

Question 18

The MIN function is used to determine the smallest value in a specified column.

Question 19

The GROUP BY clause is used to group data.

Question 20

AVG, COUNT, and STDDEV are all considered group functions.

Question 21

The MAX functions returns the $\underline { \text { smallest } }$ value stored in the specified column. _________________________

Question 22

$\underline { \text { Single-row } }$ functions return one result per group of rows processed. _________________________

Question 23

If both nonaggregated and aggregated columns are used in the SELECT clause, the GROUP BY clause must contain the nonaggregated columns.

Question 24

When the SELECT statement includes both WHERE and GROUP BY clauses, the GROUP BY clause is always evaluated first.

Question 25

The VARIANCE function is used to determine the variance in a group of numeric data.

Question 26

By default, the COUNT function uses the $\underline {\text { DISTINCT } }$ keyword when counting the values contained in a column. _________________________

Question 27

Group functions can only be nested to a depth of two.

Question 28

The WHERE clause specifies which groups will be displayed in the results.

Question 29

Columns used to group data in the GROUP BY clause must also be listed in the SELECT clause.

Question 30

The optional $\underline { \text { DISTINCT } }$ keyword instructs Oracle11g to include only unique numeric values in the calculation. _________________________

Question 31

The $\underline { \text { TOTAL } }$ function is used to calculate the total value stored in a numeric field for a group of records. _________________________

Question 32

The only group function that includes NULL values is the $\underline { \text { NVL } }$ function. _________________________

Question 33

SUM, AVG, and COUNT are all considered $\underline {\text { group } }$ functions. _________________________

Question 34

The $\underline { \text { AVG } }$ function only includes non-NULL values in its calculations. _________________________

Question 35

Column aliases can be used in the GROUP BY clause.

Question 36

The STDVAR function is used to determine the standard deviation of numeric data.

Question 37

The $\underline { \operatorname{COUNT}\left(^{*}\right) }$ function can be used to count the number of rows containing NULL and non-NULL values. _________________________

Question 38

The $\underline { \text { COUNT } }$ function is used to total the values stored in numeric columns. _________________________

Question 39

The HAVING clause is used to restrict the groups returned by a query.

Question 40

A SELECT statement cannot include both a GROUP BY and an ORDER BY clause.

Question 41

The HAVING clause serves as the $\underline { \mathrm{WHERE} }$ clause for aggregated data. _________________________

Question 42

The $\underline { \text { VAR } }$ function is used to determine the variance contained within a set of data. _________________________

Question 43

The WHERE clause $\underline { \text { can } }$ contain group functions. _________________________

Question 44

Which of the following is not considered a group function?

A) TRUNC
B) MIN
C) COUNT
D) SUM

Question 45

The $\underline{\text { STDDEV }}$ function calculates the standard deviation for the data contained in a specified field. _________________________

Question 46

The $\underline {\text { HAVING } }$ clause specifies which groups will be displayed in the results, while the WHERE clause restricts the records that are retrieved from the table for processing. _________________________

Question 47

The default keyword for group functions is ____.

A) ALL
B) DISTINCT
C) INCLUDENULLS
D) NONULLS

Question 48

The WHERE clause filters the data before grouping, while the HAVING clause filters the groups $\underline { \text { after } }$ grouping is performed. _________________________

Question 49

Functions that return one result per group of rows are called ____ functions.

A) group
B) multiple-row
C) aggregate
D) all of the above

Question 50

When group functions are nested, the $\underline { \text { inner } }$ function is always resolved first. _________________________

Question 51

Group functions can be nested inside $\underline { \text { single-row } }$ functions. _________________________

Question 52

Columns used to group data in the $\underline { \text { GROUP BY } }$ clause do not have to be listed in the SELECT clause. _________________________

Question 53

The $\underline {\text { GROUP BY } }$ clause is used to restrict the groups returned by a query. _________________________

Question 54

The $\underline { \text { ORDER BY } }$ clause is used to group data. _________________________

Question 55

The $\underline {\text { MIN }}$ function can be used with the DISTINCT or ALL keywords. _________________________

Question 56

The SUM function can only be used with ____ data.

A) numeric
B) character
C) date
D) alphanumeric

Question 57

If a group function is used in the SELECT clause, then any individual columns listed in the SELECT clause must also be listed in the $\underline { \text { ORDER BY } }$ clause. _________________________

Question 58

Results returned from a SELECT statement that include a GROUP BY clause will present the results in $\underline { \text { descending } }$ order of the column(s) listed in the GROUP BY clause. _________________________

Question 59

The ____ function is used to calculate the total amount stored in a numeric field.

A) TOTAL
B) SUM
C) AVERAGE
D) ADD

Question 60

When a SELECT statement includes WHERE, HAVING, and GROUP BY clauses, the $\underline {\text { GROUP BY }}$ clause is always evaluated first. _________________________

Question 61

The ____ function calculates the average of the numeric values in a specified column.

A) AVG
B) AVERAGE
C) MEAN
D) AG

Question 62

If the DISTINCT keyword is not included in the SUM function, the ____ keyword will be assumed.

A) UNIQUE
B) ALL
C) RANDOM
D) none of the above

Question 63

Which of the following is a valid statement?

A) The ORDER BY clause cannot be used in a SELECT statement containing a GROUP BY clause.
B) Data returned from a GROUP BY clause will automatically be sorted in descending order.
C) Column aliases cannot be used in a GROUP BY clause.
D) Columns referenced in the GROUP BY clause must also be contained in the SELECT clause.

Question 64

If the DISTINCT keyword is not included in the AVG function, the ____ keyword will be assumed.

A) ALL
B) UNIQUE
C) GROUP
D) none of the above

Question 65

Which of the following lines of the SQL statement contains an error? 1 SELECT title, MAX(retail)
2 FROM books
3 WHERE retail > 30
4 AND pubid = 1
5 GROUP BY retail;

A) Line 2
B) Line 3
C) Line 4
D) Line 5

Question 66

A(n) ____ clause cannot include a group function.

A) WHERE
B) HAVING
C) SELECT
D) both a and c

Question 67

The ____ function can be used to determine the number of rows containing a specified value.

A) TOTAL
B) SUM
C) COUNT
D) ADD

Question 68

The ____ clause is used to restrict the groups returned by a query.

A) FROM
B) WHERE
C) HAVING
D) GROUP BY

Question 69

Which of the following is a correct statement?

A) An asterisk can be used as the argument for the COUNT function to include NULL values in the results.
B) The NULL keyword can be included in the argument of the COUNT function to include NULL values in the results.
C) The ALL keyword can be included in the argument of the COUNT function to include NULL values in the results.
D) The INCLUDE NULLS keywords can be included in the argument for the COUNT function to include NULL values in the results.

Question 70

The ____ function can be used to determine the largest value stored in a specified column.

A) MAXIMUM
B) MAX
C) LARGE
D) LARGEST

Question 71

Which of the following cannot be used with date columns?

A) MIN
B) MAX
C) AVG
D) all of the above

Question 72

The ____ function can be used to determine the number of rows meeting a specific condition.

A) ADD
B) SUM
C) COUNT
D) TOTAL

Question 73

If a group function is used in the SELECT clause, any ____ listed in the SELECT clause must also be listed in the GROUP BY clause.

A) aggregate function
B) single-row function
C) individual column
D) all of the above

Question 74

The ____ clause is used to indicate that groups should be created.

A) ORDER BY
B) GROUP BY
C) AGGREGATED
D) GROUPING

Question 75

The ____ function can be used to include NULL values in a calculation.

A) AVG
B) SUM
C) NULL
D) NVL

Question 76

The MIN function can be used with ____ columns.

A) numeric
B) character
C) date
D) all of the above

Question 77

The MAX function can be used with which type of columns?

A) numeric
B) date
C) character
D) all of the above

Question 78

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed first.

A) HAVING
B) GROUP BY
C) WHERE
D) ORDER BY

Question 79

The AVG function can be used with ____ values.

A) numeric
B) character
C) date
D) all of the above

Question 80

The ____ function returns the smallest value in a specified column.

A) LOW
B) LOWEST
C) MIN
D) MINIMUM