Oracle 12c: SQL - Chapter 11

A grouping sets expression can perform multiple Group BY clauses in a single query .

True

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

True

Group functions are also known as aggregate functions.

True

Group functions return one result per group of rows processed.

True

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

True

SUM, AVG, and COUNT are all considered *group* functions.

True

The *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.

True

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

True

The GROUP BY clause is used to group data.

True

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

True

The MAX function can be used with character data.

True

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

True

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

True

The WHERE clause filters the data before grouping, while the HAVING clause filters the groups *after* grouping is performed.

True

Group functions can be nested to a depth of ____.

​2

If the DISTINCT keyword is not included in the STDDEV 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.

​ALL

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

​AVG

Which of the following cannot be used with date columns?

​AVG

Which of the following is a valid statement?

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

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.

​HAVING

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

​Line 5

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

​MAX

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

​NVL

Which of the following is not a GROUP BY extension?

​OLAP

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;

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

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

​SELECT pubid, AVG(retail-cost) "Average Profit" FROM books GROUP BY pubid;

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

​STDDEV

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

​SUM

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

​VARIANCE

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

​all of the above

Which of the following can be used with date columns?

​all of the above

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

​both a and b

The AVG function can be used with ____ values.

​numeric

The STDDEV function can be used with ____ columns.

​numeric

The VARIANCE function can be used with ____ columns.

​numeric

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 *ORDER BY* clause.

False

Results returned from a SELECT statement that include a GROUP BY clause will present the results in *descending* order of the column(s) listed in the GROUP BY clause.

False

The *COUNT* function is used to total the values stored in numeric columns.

False

The *GROUP BY* clause is used to restrict the groups returned by a query.

False

The COUNT function can only be used for numeric columns.

False

The DISTINCT keyword cannot be used with the MAX function.

False

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

False

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

False

The MAX functions returns the *smallest* value stored in the specified column.

False

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

False

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

False