1. Which is NOT an aggregate function?
a. SUM
b. COUNT
c. ROUND
d. MAX
2. If your SELECT clause contains both aggregate and non-aggregate functions, _.
a. all non-aggregate columns must be included in a WHERE clause
b. all non-aggregate columns must be included in a GROUP BY clause
c. all aggregate and non-aggregate columns must be included in a GROUP BY clause
d. all aggregate columns must be included in a WHERE clause
3. Given the books table described below, which of the following will display each category along with the average price of books in that category- books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)
a. SELECT category, AVG(retail) FROM books;
b. SELECT AVG(retail) FROM books GROUP BY category;
c. SELECT category, AVG(retail) FROM books GROUP BY category;
d. SELECT category, AVG(retail) FROM books GROUP BY category, retail;
4. Given the books table described below, which of the following will list the title(s) of books with the earliest publication date- books: bookid(PK), ISBN, title, pubdate, cost, retail, category, pubid(FK)
a. SELECT title FROM books WHERE pubdate = MIN(pubdate);
b. SELECT title , MIN(pubdate) FROM books
c. SELECT title FROM (SELECT title, MIN(pubdate) FROM books);
d. SELECT title FROM books WHERE pubdate = (SELECT MIN(pubdate) FROM books);
5. When executing a query containing one or more subqueries, the _ query is executed first.
a. right
b. left
c. innermost
d. outermost
6. What type of subquery is executed only once for the entire query?
a. Correlated
b. Uncorrelated
c. Inner
d. Outer
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)
a. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerID FROM order WHERE shipdate = orderdate);
b. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerid FROM order WHERE orderid IS NULL);
c. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerid FROM order WHERE shipdate IS NULL);
d. SELECT lastname, firstname FROM customer WHERE customerid = (SELECT customerid FROM order WHERE shipdate IS NOT NULL);
8. 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)
a. SELECT customerid, COUNT(customerid) FROM order;
b. SELECT customerid, COUNT(ordered) FROM order;
c. SELECT customerid, COUNT(orderid) FROM order GROUP BY customerid;
d. SELECT customerid, COUNT(orderid) FROM order GROUP BY orderid;
9. 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)
a. SELECT COUNT(retail) FROM books;
b. SELECT SUM(retail) FROM books;
c. SELECT AVG(retail) FROM books;
d. SELECT AVG(retail) FROM books GROUP BY category;
10. The expression below will be TRUE for what values of x? x > ALL(5, 9)
a. 5 and below
b. 5 to 9
c. 5 and above
d. 9 and above
