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;
