Question 1

(TCO 7) Which is NOT an aggregate function?
1. SUM
2. COUNT
3. ROUND
4. MAX

Question 2
(TCO 7) If your SELECT clause contains both aggregate and nonaggregate functions, _____.
1. all nonaggregate columns must be included in a WHERE clause
2. all nonaggregate columns must be included in a GROUP BY clause
3. all aggregate and non-aggregate columns must be included in a GROUP BY clause
4. all aggregate columns must be included in a WHERE clause

Question 3
(TCO 7) Given the books table described below, which of the following will display the number of different categories contained in the books table?
books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)

1. SELECT DISTINCT category COUNT(*) FROM books;
2. SELECT COUNT(category) FROM books GROUP BY category;
3. SELECT DISTINCT (COUNT(category)) FROM books;
4. SELECT COUNT(DISTINCT category) FROM books;

Question 4
(TCO 7) Given the books table described below, which of the following will list retail price of the most expensive book in the COMPUTER category?
books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)
1. SELECT MAX(retail) FROM books GROUP BY category;
2. SELECT MAX(retail) FROM books WHERE category = ‘COMPUTER’;
3. SELECT MAX(retail) FROM books HAVING category = ‘COMPUTER’;
4. SELECT retail FROM (SELECT MAX(retail) FROM books);

Question 5
(TCO 7) Which of the values listed below can be returned by a subquery referenced as follows?
WHERE vendorid = (subquery)

1. A single value
2. A column of one or more rows
3. A table
4. A subquery can’t be used in this way.

Question 6

(TCO 7) What type of subquery is executed once for each row processed by the main query?

1. Correlated
2. Uncorrelated
3. Inner
4. Outer

Question 7
(TCO 7) Given the two tables described below, which of the following queries will display the names of the customers whose orders have not shipped?
customer: with the fields customerid(PK), lastname, firstname
order: with the fields orderid(PK), orderdate, shipdate, customerid(FK)
1. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate = orderdate);
2. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerid FROM order WHERE orderid IS NULL);
3. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerid FROM order WHERE shipdate IS NULL);
4. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerid FROM order WHERE shipdate IS NOT NULL);

Question 8
(TCO 7) Given the two tables described below, which of the following queries will display each customerid along with the number of orders placed by that customer?
customer: with the fields customerid(PK), lastname, firstname
order: with the fields orderid(PK), orderdate, shipdate, customerid(FK)
1. SELECT customerid, COUNT(customerid) FROM order;
2. SELECT customerid, COUNT(ordered) FROM order;
3. SELECT customerid, COUNT(orderid) FROM order GROUP BY customerid;
4. SELECT customerid, COUNT(orderid) FROM order GROUP BY orderid;

Question 9
(TCO 7) Given the books table described below, which of the following will display the average retail price of books?
books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)
1. SELECT COUNT(retail) FROM books;
2. SELECT SUM(retail) FROM books;
3. SELECT AVG(retail) FROM books;
4. SELECT AVG(retail) FROM books GROUP BY category;

Question 10
(TCO 7) The expression below will be TRUE for what values of x?
x > ALL(5, 9)
1. 5 and below
2. 5 to 9
3. 5 and above
4. 9 and above