Chapter 11 - Group Functions

A. WHERE

A(n) ____ clause cannot include a group function. A. WHERE B. SELECT C. HAVING D. both a and c

A. True

All group functions ignore NULL values except COUNT(*). A. True B. False

B. individual column

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. individual column C. single-row function D. all of the above

A. ALL

If the DISTINCT keyword is not included in the AVG function, the ____ keyword will be assumed. A. ALL B. GROUP C. UNIQUE D. none of the above

C. ALL

If the DISTINCT keyword is not included in the SUM function, the ____ keyword will be assumed. A. UNIQUE B. RANDOM C. ALL D. none of the above

B. HAVING

If the output is to be restricted based upon a group function, a(n) ____ clause must be included in the SQL statement. A. GROUP BY B. HAVING C. WHERE D. ORDER BY

C. GROUP BY

The ____ clause is used to indicate that groups should be created. A. ORDER BY B. AGGREGATED C. GROUP BY D. GROUPING

B. HAVING

The ____ clause is used to restrict the groups returned by a query. A. FROM B. HAVING C. WHERE D. GROUP BY

A. AVG

The ____ function calculates the average of the numeric values in a specified column. A. AVG B. MEAN C. AVERAGE D. AG

C. MAX

The ____ function can be used to determine the largest value stored in a specified column. A. MAXIMUM B. LARGE C. MAX D. LARGEST

B. COUNT

The ____ function can be used to determine the number of rows containing a specified value. A. TOTAL B. COUNT C. SUM D. ADD

B. COUNT

The ____ function can be used to determine the number of rows meeting a specific condition. A. ADD B. COUNT C. SUM D. TOTAL

D. NVL

The ____ function can be used to include NULL values in a calculation. A. AVG B. NULL C. SUM D. NVL

C. SUM

The ____ function is used to calculate the total amount stored in a numeric field. A. TOTAL B. AVERAGE C. SUM D. ADD

B. MIN

The ____ function returns the smallest value in a specified column. A. LOW B. MIN C. LOWEST D. MINIMUM

A. ALL

The default keyword for group functions is ____. A. ALL B. INCLUDENULLS C. DISTINCT D. NONULL

B. 2

The maximum nesting depth is? A. 1 B. 2 C. 3 D. 4

C. SUM

To calculate the total amount stored in a numeric column for a group of rows, the __________ function should be used. A. AVG B. MAX C. SUM D. COUNT

C. NVL

To include NULL values, which functions should be used? A. NULLIF B. NULL C. NVL D. none of the above

C. 4

Use the following SELECT statement to answer: 1- SELECT customer#, COUNT(*) 2- FROM customers JOIN orders USING (customer#) 3- WHERE orderdate > '02-APR-09' 4- GROUP BY customer# 5- HAVING COUNT(*) >2; Because the SELECT statement clause contains the Customer# column, which clause must be included for the query to execute sucessfully? A. 1 B. 3 C. 4 D. 5

A. numeric

The SUM function can only be used with ____ data. A. numeric B. date C. character D. alphanumeric

D. all of the above

Functions that return one result per group of rows are called ____ functions. A. group B. aggregate C. multiple-row D. all of the above

B. WHERE

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

A. HAVING

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last. A. HAVING B. WHERE C. GROUP BY D. SELECT

A. numeric

The AVG function can be used with ____ values. A. numeric B. date C. character D. all of the above

D. all of the above

The MAX function can be used with which type of columns? A. numeric B. character C. date D. all of the above

D. all of the above

The MIN function can be used with ____ columns. A. numeric B. date C. character D. all of the above

B. the number of orders placed by each customer

Use the following SELECT statement to answer: 1- SELECT customer#, COUNT(*) 2- FROM customers JOIN orders USING (customer#) 3- WHERE orderdate > '02-APR-09' 4- GROUP BY customer# 5- HAVING COUNT(*) >2; The COUNT(*) function in the SELECT clause is used to return: A. the number of records in the specified tables B. the number of orders placed by each customer C. the number of NULL values in the specified tables D. the number of customers who have placed an order

C. 4

Use the following SELECT statement to answer: 1- SELECT customer#, COUNT(*) 2- FROM customers JOIN orders USING (customer#) 3- WHERE orderdate > '02-APR-09' 4- GROUP BY customer# 5- HAVING COUNT(*) >2; Which line of the SELECT statement is used to group data stored in the database? A. 1 B. 3 C. 4 D. 5

D. 5

Use the following SELECT statement to answer: 1- SELECT customer#, COUNT(*) 2- FROM customers JOIN orders USING (customer#) 3- WHERE orderdate > '02-APR-09' 4- GROUP BY customer# 5- HAVING COUNT(*) >2; Which line of the SELECT statement is used to restrict groups displayed in the query? A. 1 B. 3 C. 4 D. 5

B. 3

Use the following SELECT statement to answer: 1- SELECT customer#, COUNT(*) 2- FROM customers JOIN orders USING (customer#) 3- WHERE orderdate > '02-APR-09' 4- GROUP BY customer# 5- HAVING COUNT(*) >2; Which line of the SELECT statement is used to restrict the number of records the query processes? A. 1 B. 3 C. 4 D. 5

C. FROM, JOINS, WHERE, GROUP BY, SELECT, HAVING

What is the order that databases work from? A. SELECT, FROM, WHERE, GROUP BY, HAVING B. FROM, WHERE, JOIN, GROUP BY, HAVING, SELECT C. FROM, JOINS, WHERE, GROUP BY, SELECT, HAVING D. SELECT, WHERE, FROM, GROUP BY, HAVING, JOIN

C. DISTINCT

What keyword should be used to include all values, except NULLS. A. NULL B. SUM C. DISTINCT D. TO_CHAR

D. MAX

Which function returns the largest value? A. SUM B. AVG C. MIN D. MAX

D. none of the above

Which of the following SELECT statements lists only the book with the largest profit? A. SELECT title, MAX(retail-cost) FROM books GROUP BY title; B. SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX (retail-cost); C. SELECT title, MAX(retail-cost) FROM books; D. none of the above

A. SELECT MAX (retail) FROM books WHERE category='FAMILY';

Which of the following SELECT statements lists the highest retail prices of all books in the FAMILY category? A. SELECT MAX (retail) FROM books WHERE category='FAMILY'; B. SELECT MAX (retail) FROM books HAVING category='FAMILY'; C. SELECT retail FROM books WHERE category='FAMILY' HAVING MAX (retail); d. none of the above

B. SELECT COUNT (referred) FROM customers;

Which of the following SQL statements determines how many total customers were referred by other customers? A. SELECT customer#, SUM(referred) FROM customers GROUP BY customer#; B. SELECT COUNT (referred) FROM customers; C. SELECT COUNT(*) FROM customers; D. SELECT COUNT(*) FROM customers WHERE referred IS NULL;

B. AVG

Which of the following cannot be used with date columns? A. MIN B. AVG C. MAX D. all of the above

C. MIN function

Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books? A. COUNT function B. MAX function C. MIN function D. STDDEV function E. VARIANCE function

B. NVL

Which of the following functions can be used to include NULL values in calculations? A. SUM B. NVL C. MAX D. MIN

D. SUM

Which of the following functions is used to calculate the total value stored in a specified column? A. COUNT B. MIN C. TOTAL D. SUM E. ADD

B. WHERE, GROUP BY, HAVING

Which of the following indicates the processing order for the indicated clauses? A. WHERE, HAVING, GROUP BY B. WHERE, GROUP BY, HAVING C. GROUP BY, HAVING, WHERE D. HAVING, WHERE, GROUP BY

A. An asterisk can be used as the argument for the COUNT function to include NULL values in the results.

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.

A. SELECT AVG (retail-cost) FROM books GROUP BY category;

Which of the following is a valid SELECT statement? A. SELECT AVG (retail-cost) FROM books GROUP BY category; B. SELECT category, AVG(retail-cost) FROM books; C. SELECT category, AVG(retail-cost) FROM books; WHERE AVG(retail-cost) >8.56 GROUP BY category; D. SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit >8.56;

A. SELECT SUM(quantity*retail) "Total Sales" FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems ON orderitems.ISBN = books.ISBN WHERE orderdate = '02-APR-03';

Which of the following is a valid SQL statement? A. SELECT SUM(quantity*retail) "Total Sales" FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems ON orderitems.ISBN = books.ISBN WHERE orderdate = '02-APR-03'; B. SELECT SUM(quantity*retail) "Total Sales" FROM orders JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems ON orderitems.ISBN = books.ISBN HAVING orderdate = '02-APR-03';

B. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;

Which of the following is a valid SQL statement? A. SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer #=1001; B. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#; C. SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#); D. SELECT customer#, COUNT(order#) FROM orders GROUP BY order#

C. Column aliases cannot be used in a GROUP BY clause.

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.

E. a, b, and c

Which of the following is correct? A. A group function can be nested inside a group function. B. A group function can be nested inside a single-row functions. C. A single-row function can be nested inside a group function. D. a and b E. a, b, and c

D. SELECT title, VARIANCE (retail-cost) FROM books GROUP BY pubid;

Which of the following is not a valid SELECT statement? A. SELECT STDDEV (retail) FROM books; B. SELECT AVG (SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#; C. SELECT order#, TO_CHAR(SUM(retail), '999.99') FROM orderitems JOIN books USING (isbn) GROUP BY order#; D. SELECT title, VARIANCE (retail-cost) FROM books GROUP BY pubid;

A. SELECT MIN (pubdate) FROM books GROUP BY category HAVING pubid-4;

Which of the following is not a valid SQL statement? A. SELECT MIN (pubdate) FROM books GROUP BY category HAVING pubid-4; B. SELECT MIN (pubdate) FROM books WHERE category='COOKING'; C. SELECT COUNT(*) FROM orders WHERE customer#=1005; D. SELECT MAX (COUNT(customer#)) FROM orders GROUP BY customer#;

B. The AVG function can be used to find the average calculated difference between two dates.

Which of the following is not a valid statement? A. You must enter the ALL keyword in a group function to include all duplicate values. B. The AVG function can be used to find the average calculated difference between two dates. C. the MIN and MAX functions can be used on any type of data. D. all of the above E. none of the above

A. TRUNC

Which of the following is not considered a group function? A. TRUNC B. COUNT C. MIN D. SUM

D. Line 5

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 4 C. Line 3 D. Line 5

A. The COUNT function can be used to determine how many rows contain a NULL value.

Which of the following statements is correct? A. The COUNT function can be used to determine how many rows contain a NULL value. B. Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause. C. The HAVING clause restricts which rows are processed. D. The WHERE clause determines which groups are displayed in the query results. E. none of the above.

D. The GROUP BY clause is always processed before the HAVING clause.

Which of the following statements is correct? A. The WHERE clause can contain a group function only if the function isn't also listed in the SELECT clause. B. Group functions can't be used in the SELECT, FROM, or WHERE clause. C. The HAVING clause is always processed before the WHERE clause. D. The GROUP BY clause is always processed before the HAVING clause.

C. The AVG function can be used only with numeric date.

Which of the following statements is true? A. The MIN function can be used only with numeric data. B. The MAX function can only be used with date values. C. The AVG function can be used only with numeric date. D. The SUM function can't be part of a nest function.

A. SELECT COUNT (category) FROM books;

Which statement will exclude NULLS from the count? A. SELECT COUNT (category) FROM books; B. SELECT COUNT(*) FROM orders; C. all of the above D. none of the above

B. SELECT COUNT(*) FROM orders;

Which statement will include NULLS in the count? A. SELECT COUNT (category) FROM books; B. SELECT COUNT(*) FROM orders; C. all of the above D. none of the above

SELECT TO_CHAR(AVG(retail-cost), '$999.99') AS "Average Profit";

Which statement will round the result? A. SELECT TO_CHAR(AVG(retail-cost), '$999.99') AS "Average Profit"; B. SELECT TO_CHAR((retail-cost), '$999.99') AS "Average Profit"; C.SELECT TO_CHAR(AVG(retail-retail), '$999.99') AS "Average Profit"; D. none of the above