Joan Casteel Oracle 11g SQL Chapters 11 Multiple Choice Questions Solution

Description

Joan Casteel Oracle 11g SQL Chapters 11 Multiple Choice Questions Solution

To answer these questions, refer to the tables in the JustLee Books database.
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.

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;

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.

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#;

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

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#;

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

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

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

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

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

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

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;

Use the following SELECT statement to answer questions 14–18:
1 SELECT customer#, COUNT(*)
2 FROM customers JOIN orders USING (customer#)
3 WHERE orderdate > ’02-APR-09′
4 GROUP BY customer#
5 HAVING COUNT(*) > 2;

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

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

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

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

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

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

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;