Ch. 11 Group Functions

Question 1

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

• numeric
• date
• character
• all of the above

Answer 1

-all of the above

Question 2

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

• TOTAL
• SUM
• COUNT
• ADD

Answer 2

-COUNT

Question 3

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;

• Line 2
• Line 3
• Line 4
• Line 5

Answer 3

-Line 5

Question 4

Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?

SELECT COUNT(DISTINCT pubid) FROM books;

SELECT DISTINCT COUNT(pubid) FROM books;

SELECT DISTINCT (COUNT(pubid)) FROM books;

SELECT (DISTINCT COUNT(pubid)) FROM books;

Answer 4

SELECT COUNT(DISTINCT pubid) FROM books;

Question 5

The MIN function can be used with ____ columns.

• numeric
• character
• date
• all of the above

Answer 5

-all of the above

Question 6

Based on the contents of the BOOKS table, which of the following will display the date of the book with the earliest publication date?

SELECT MIN(pubdate)
FROM books;

SELECT title
FROM books
WHERE pubdate = MIN(pubdate);

SELECT title
FROM books
WHERE pubdate = MINIMUM(pubdate);

SELECT MINIMUM(pubdate)
FROM books;

Answer 6

SELECT MIN(pubdate)
FROM books;

Question 7

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

• AVG
• SUM
• NULL
• NVL

Answer 7

-NVL

Question 8

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

• group
• multiple-row
• aggregate
• all of the above

Answer 8

-all of the above

Question 9

The ____ function calculates the standard deviation for a specific set of data.

• STDDEVIATION
• STD
• STDDEV
• STANDARDDEV

Answer 9

-STDDEV

Question 10

Which of the following indicates the processing order for the indicated clauses?

• WHERE, HAVING, GROUP BY
• GROUP BY, HAVING, WHERE
• WHERE, GROUP BY, HAVING
• HAVING, WHERE, GROUP BY

Answer 10

-WHERE, GROUP BY, HAVING

Question 11

Which of the following is a valid statement?

The ORDER BY clause cannot be used in a SELECT statement containing a GROUP BY clause.

Data returned from a GROUP BY clause will automatically be sorted in descending order.

Column aliases cannot be used in a GROUP BY clause.

Columns referenced in the GROUP BY clause must also be contained in the SELECT clause.

Answer 11

Column aliases cannot be used in a GROUP BY clause.

Question 12

The VARIANCE function can be used with ____ columns.

• alphanumeric
• numeric
• date
• none of the above

Answer 12

-numeric

Question 13

The ____ function is based upon the concept of a normal distribution.

• MIN
• MAX
• SUM
• STDDEV

Answer 13

-STDDEV

Question 14

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.

• aggregate function
• single-row function
• individual column
• all of the above

Answer 14

-individual column

Question 15

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

• FROM
• WHERE
• HAVING
• GROUP BY

Answer 15

-HAVING

Question 16

Based upon the contents of the BOOKS table, which of the following will display the retail price of the most expensive book provided by publisher 3?

SELECT MAX(retail)
FROM books
GROUP BY pubid;

SELECT MAXIMUM(retail)
FROM books
WHERE pubid = 3;

SELECT MAX(retail)
FROM books
WHERE pubid = 3; 

SELECT MAXIMUM(retail)
FROM books
HAVING pubid = 3

Answer 16

SELECT MAX(retail)
FROM books
WHERE pubid = 3;

Question 17

Which of the following can be used with date columns?

• MIN
• MAX
• COUNT
• all of the above

Answer 17

-all of the above

Question 18

The STDDEV function can be used with ____ columns.

• numeric
• character
• date
• all of the above

Answer 18

-numeric

Question 19

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

• UNIQUE
• ALL
• NULLS ONLY
• NONULLS

Answer 19

-ALL