Is640 chapter 11

Hands on assignment 1 1. Determine how many books are in the Cooking category.

1. SELECT COUNT(*) FROM books WHERE category = 'COOKING';

Hands on assignment 10 10. What's the retail price of the most expensive book written by Lisa White?

10. SELECT MAX(retail) FROM books JOIN bookauthor USING (isbn) JOIN author USING(authorid) WHERE lname = 'WHITE' AND fname = 'LISA';

Multiple choice 12 12. Which of the following is not a valid statement? a. You must enter the ALL keyword in a group function to include all duplicate values. b. The AVG function can be used to find the average calculated difference between two dates. c. The MIN and MAX functions can be used on any type of data. d. all of the above e. none of the above

12. a. a. You must enter the ALL keyword in a group function to include all duplicate values.

17. Because the SELECT clause contains the Customer* column, which clause must be included for the query to execute successfully? a. 1 b. 3 c. 4 d. 5

17. c. c. 4

Multiple choice 18 18. The COUNT(*) function in the SELECT clause is used to return. a. the number of records in the specified tables b. the number of orders placed by each customer c. the number of NULL values in the specified tables d. the number of customers who have placed an order

18. b b. the number of orders placed by each customer

Multiple choice 19 19. Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books? a. COUNT function b. MAX function c. MIN function d. STDDEV function e. VARIANCE function

19 c. c. MIN function

Rq 8 8. What's the maximum depth allowed when nesting group functions?

2

Hands on assignment 2 2. Display the number of books with a retail price of more than $30.00.

2. SELECT COUNT(*) FROM books WHERE retail > 30;

Hands on assignment 3 3. Display the most recent publication date of all books sold by JustLee Books.

3. SELECT MAX(pubdate) FROM books;

Hands on assignment 4 4. Determine the total profit generated by sales to customer 1017. Note: Quantity should be reflected in the total profit calculation.

4. SELECT SUM((retail-cost)*quantity) FROM books, orders, orderitems WHERE books.isbn = orderitems.isbn AND orderitems.order# = orders.order# AND customer# = 1017;

Hands on assignment 5 5. List the retail price of the least expensive book in the Computer category.

5. SELECT MIN(retail) FROM books WHERE category = 'COMPUTER';

Review question 1 1. Explain the difference between single-row and group functions.

A single-rbow function returns one result for each row processed while a group function returns one result for each group of data processed.

Rq 4 4. Under what circumstances must you include a GROUP BY clause in a query?

If a single column is listed in a SELECT clause along with a group function

Rq 9 In what order are output results displayed if a SELECT statement contains a GROUP BY clause and no ORDER BY clause?

In ascending order, based on the column specified in the GROUP BY clause

Multiple choice 1 1. Which of the following statements is true? a. The MIN function can be used only with numeric data. b. The MAX function can be used only with date values. c. The AVG function can be used only with numeric data. d. The SUM function can't be part of a nested function.

MC 1. c c. The AVG function can be used only with numeric data.

Multiple choice 10 10. Which of the following SELECT statements lists the highest retail price of all books in the Family category? a. SELECT MAX(retail) FROM books WHERE category = 'FAMILY'; b. SELECT MAX(retail) FROM books HAVING category = 'FAMILY'; c. SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail); d. none of the above

MC 10. a. a. SELECT MAX(retail) FROM books WHERE category = 'FAMILY';

Multiple choice 13 13. Which of the following SQL statements determines how many total customers were referred by other customers? a. SELECT customer#, SUM(referred) FROM customers GROUP BY customer#; b. SELECT COUNT(referred) FROM customers; c. SELECT COUNT(*) FROM customers; d. SELECT COUNT(*) FROM customers WHERE referred IS NULL;

MC 13. b. b. SELECT COUNT(referred) FROM customers;

Multiple choice 14 14. Which line of the SELECT statement is used to restrict the number of records the query processes? a. 1 b. 3 c. 4 d. 5

MC 14. b. 3

Multiple choice 15 15. Which line of the SELECT statement is used to restrict groups displayed in the query results? a. 1 b. 3 c. 4 d. 5

MC 15. d. d. 5

Multiple choice 16 16. Which line of the SELECT statement is used to group data stored in the database? a. 1 b. 3 c. 4 d. 5

MC 16. c. c. 4

Multiple choice 2 2. Which of the following is a valid SELECT statement? a. SELECT AVG(retail-cost) FROM books GROUP BY category; b. SELECT category, AVG(retail-cost) FROM books; c. SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category; d. SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;

MC 2. a. a. SELECT AVG(retail-cost) FROM books GROUP BY category;

Syntax 4 MAX([DISTINCT| ALL] c) Returns the highest (maximum) value from the selected field. Ignores NULL values.

SELECT MAX(customer#) FROM customers;

Syntax 5 MIN([DISTINCT| ALL] c) Returns the lowest (minimum) value from the selected field. Ignores NULL values.

SELECT MIN(retail-cost) FROM books;

Syntax 6 STDDEV([DISTINCT| ALL] n) Returns the standard deviation of the selected numeric field. Ignores NULL values.

SELECT STDDEV(retail) FROM books;

Syntax 1 SUM([ DISTINCT| ALL] n) Returns the sum or total value of the selected numeric field. Ignores NULL values.

SELECT SUM(retail-cost) FROM books;

Syntax 7 VARIANCE([DISTINCT| ALL] n) Returns the variance of the selected numeric field. Ignores NULL values.

SELECT VARIANCE(retail) FROM books;

Hands on assignment 6 6. Determine the average profit generated by orders in the ORDERS table. Note: The total profit by order must be calculated before finding the average profit.

6. SELECT AVG(SUM((retail-cost)*quantity)) FROM books, orders, orderitems WHERE books.isbn = orderitems.isbn AND orderitems.order# = orders.order# GROUP BY orders.order#;

Hands on assignment 7 7. Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn't recently placed an order with JustLee Books.

7. SELECT customer#, COUNT(*) FROM orders GROUP BY customer#;

Hands on assignment 8 8. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.

8. SELECT name, category, AVG(retail) FROM books JOIN publisher USING (pubid) WHERE category IN('COMPUTER', 'CHILDREN') GROUP BY name, category HAVING AVG(retail) > 50;

Hands on assignment 9 9. List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.

9. SELECT DISTINCT firstname, lastname FROM customers, books, orders, orderitems WHERE customers.customer# = orders.customer# AND orders.order# = orderitems.order# AND orderitems.ISBN = books.ISBN AND (state = 'GA' OR state = 'FL') GROUP BY orders.order#, firstname, lastname HAVING SUM(retail*quantity) > 80;

Rq 2 2. Which group function can be used to perform a count that includes NULL values?

COUNT(*)

Rq 3 3. Which clause can be used to restrict or filter the groups returned by a query based on a group function?

HAVING

Rq 6 6. In which clause should you include the condition MAX(cost) > 39 to restrict groups displayed in the query results?

HAVING

Multiple choice 20. 20. Which of the following is not a valid SELECT statement? a. SELECT STDDEV(retail) FROM books; b. SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#; c. SELECT order#, TO_CHAR(SUM(retail), '999.99') FROM orderitems JOIN books USING(isbn) GROUP BY order#; d. SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;

MC 20. d. d. SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;

Multiple choice 3 3. Which of the following statements is correct? a. The WHERE clause can contain a group function only if the function isn't also listed in the SELECT clause. b. Group functions can't be used in the SELECT, FROM, or WHERE clauses. c. The HAVING clause is always processed before the WHERE clause. d. The GROUP BY clause is always processed before the HAVING clause.

MC 3. d. d. The GROUP BY clause is always processed before the HAVING clause.

Multiple choice 4 4. Which of the following is not a valid SQL statement? a. SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4; b. SELECT MIN(pubdate) FROM books WHERE category = 'COOKING'; c. SELECT COUNT(*) FROM orders WHERE customer# = 1005; d. SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;

MC 4. a. a. SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;

Multiple choice 5 5. Which of the following statements is correct? a. The COUNT function can be used to determine how many rows contain a NULL value. b. Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause. c. The HAVING clause restricts which rows are processed. d. The WHERE clause determines which groups are displayed in the query results. e. none of the above

MC 5. a. a. The COUNT function can be used to determine how many rows contain a NULL value.

Multiple choice 6 6. Which of the following is a valid SQL statement? a. SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001; b. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#; c. SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#); d. SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;

MC 6. b. b. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;

Multiple choice 7 7. Which of the following SELECT statements lists only the book with the largest profit? a. SELECT title, MAX(retail-cost) FROM books GROUP BY title; b. SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost); c. SELECT title, MAX(retail-cost) FROM books; d. none of the above

MC 7. d. d. none of the above

Multiple choice 8 8. Which of the following is correct? a. A group function can be nested inside a group function. b. A group function can be nested inside a single-row function. c. A single-row function can be nested inside a group function. d. a and b e. a, b, and c

MC 8. e. e. a, b, and c

Multiple choice 9 9. Which of the following functions is used to calculate the total value stored in a specified column? a. COUNT b. MIN c. TOTAL d. SUM e. ADD

MC 9. d. d. SUM

Multiple choice 11 11. Which of the following functions can be used to include NULL values in calculations? a. SUM b. NVL c. MAX d. MIN

MC. 11. b. b. NVL

Rq 10 10. Which clause is used to restrict the records retrieved from a table? Which clause restricts groups displayed in the query results?

Records: WHERE clause; groups: HAVING clause

Syntax 14 HAVING group function comparisonoperator value Restricts the groups displayed in query results.

SELECT AVG(cost) FROM books GROUP BY category HAVING AVG(cost) > 21;

Syntax 13 GROUP BY columnname [, columnname,...] Divides data into sets or groups, based on the contents of specified columns.

SELECT AVG(cost) FROM books GROUP BY category;

Syntax 2 AVG([ DISTINCT| ALL] n) Returns the average value of the selected numeric field. Ignores NULL values.

SELECT AVG(cost) FROM books;

Syntax 3 COUNT(*[ DISTINCT| ALL] c) Returns the number of rows containing a value in the identified field. Rows containing NULL values in the field aren't included in the results. To count rows containing NULL values, use an asterisk (*) rather than a field name.

SELECT COUNT(*) FROM books; or SELECT COUNT(shipdate) FROM orders;

Syntax 10 ROLLUP Performs increasing levels of cumulative subtotals, based on the provided column list.

SELECT name, category, AVG(retail) FROM publisher JOIN books USING (pubid) GROUP BY ROLLUP(name, category) ORDER BY name, category;

Syntax 9 CUBE Performs aggregations for all possible combinations of columns included.

SELECT name, category, AVG(retail) FROM publisher JOIN books USING (pubid) GROUP BY CUBE(name, category) ORDER BY name, category;

Syntax 8 GROUPING SETS Enables performing multiple GROUP BY clauses with a single query.

SELECT name, category, AVG(retail) FROM publisher JOIN books USING (pubid) GROUP BY GROUPING SETS (name, category, (name,category),());

Rq 7 7. What's the basic difference between the ROLLUP and CUBE extensions of the GROUP BY clause?

The CUBE extension performs aggregations for all possible combinations of columns, and the ROLLUP extension simply calculates cumulative subtotals for the columns.

Rq 5 5. In which clause should you include the condition "pubid ¼ 4" to restrict the rows processed by a query?

WHERE