Ch. 11 Group Functions Flashcards Preview

Oracle 11g SQL > Ch. 11 Group Functions > Flashcards

Flashcards in Ch. 11 Group Functions Deck (19):
1


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

-numeric
-date
-character
-all of the above

-all of the above

2


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


-TOTAL
-SUM
-COUNT
-ADD

-COUNT

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

-Line 5

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;

SELECT COUNT(DISTINCT pubid) FROM books;

5


The MIN function can be used with ____ columns.


-numeric
-character
-date
-all of the above

-all of the above

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;

SELECT MIN(pubdate)
FROM books;

7


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

-AVG
-SUM
-NULL
-NVL

-NVL

8


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

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

-all of the above

9


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

-STDDEVIATION
-STD
-STDDEV
-STANDARDDEV

-STDDEV

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

-WHERE, GROUP BY, HAVING

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.

Column aliases cannot be used in a GROUP BY clause.

12


The VARIANCE function can be used with ____ columns.

-alphanumeric
-numeric
-date
-none of the above

-numeric

13


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

-MIN
-MAX
-SUM
-STDDEV

-STDDEV

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

-individual column

15


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

-FROM
-WHERE
-HAVING
-GROUP BY

-HAVING

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

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

17


Which of the following can be used with date columns?

-MIN
-MAX
-COUNT
-all of the above

-all of the above

18


The STDDEV function can be used with ____ columns.

-numeric
-character
-date
-all of the above

-numeric

19


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

-UNIQUE
-ALL
-NULLS ONLY
-NONULLS

-ALL