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