CSIT156chap11

ALL

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

both a and b

A group function can be nested inside a(n)____.

WHERE

A(n) ____ clause cannot include a group function

3. SELECT COUNT(pubid)FROM booksWHERE pubid = 3;

Based upon the contents of the BOOKS table, which of the following will determine the number of books provided by publisher 3?

SELECT MAX(retail)FROM booksWHERE pubid = 3;

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 COUNT(DISTINCT pubid) FROM books;

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 shipstate, COUNT(*)FROM ordersGROUP BY shipstate;

Based upon the contents of the ORDERS table, which of the following will display how many orders were shipped to each state?

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.

ALL

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

ALL

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

ALL

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

HAVING

If the output is to be restricted based upon a group function, a(n) ____ clause must be included in the SQL statement.

HAVING

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

AVG

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

STDDEV

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

MAX

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

COUNT

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

COUNT

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

NVL

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

STDDEV

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

SUM

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

VARIANCE

The ____ function is used to determine how widely data are spread out within a group.

MIN

The ____ function returns the smallest value in a specified column

ALL

The default keyword for group functions is ____.

GROUP BY

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

numeric

The VARIANCE function can be used with ____ columns.

SELECT SUM(retail-cost) FROM booksWHERE pubid = 4;

Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit generated by books provided by publisher 4?

SELECT pubid, AVG(retail-cost) "Average Profit"FROM booksGROUP BY pubid;

Based on the contents of the BOOKS table, which of the following is a valid SQL statement?

SELECT MIN(pubdate)FROM books;

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 customer#, COUNT(customer#)FROM ordersGROUP BY customer#;

Based on the contents of the ORDERS table, which of the following SELECT statements will determine the number of orders placed by each customer?

SELECT COUNT(*)FROM ordersWHERE shipdate IS NULL;

Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?

numeric

The STDDEV function can be used with ____ columns

all of the above

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

2

Group functions can be nested to a depth of ____.

WHERE

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

HAVING

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

numeric

The SUM function can only be used with ____ data.

numeric

The AVG function can be used with ____ values.

all of the above

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

all of the above

The MIN function can be used with ____ columns

all of the above

Which of the following can be used with character data?

all of the above

Which of the following can be used with date columns?

AVG

Which of the following cannot be used with date columns?

WHERE, GROUP BY, HAVING

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

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?

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

Which of the following is a valid SQL statement?

Column aliases cannot be used in a GROUP BY clause.

Which of the following is a valid statement?

all of the above

Which of the following is a valid statement?

TRUNC

Which of the following is not considered a group function?

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;