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

Description

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

To answer the following questions, refer to the tables in the JustLee Books database.
1. Which of the following is a valid SQL statement?
a. SELECT SYSDATE;
b. SELECT UPPER(Hello) FROM dual;
c. SELECT TO_CHAR(SYSDATE, ‘Month DD, YYYY’) FROM dual;
d. all of the above
e. none of the above

2. Which of the following functions can be used to extract a portion of a character string?
a. EXTRACT
b. TRUNC
c. SUBSTR
d. INITCAP

3. Which of the following determines how long ago orders that haven’t shipped were received?
a. SELECT order#, shipdate-orderdate delay FROM orders;
b. SELECT order#, SYSDATE – orderdate FROM orders WHERE shipdate IS NULL;
c. SELECT order#, NVL(shipdate, 0) FROM orders WHERE orderdate is NULL;
d. SELECT order#, NULL(shipdate) FROM orders;

4. Which of the following SQL statements produces “Hello World” as the output?
a. SELECT “Hello World” FROM dual;
b. SELECT INITCAP(‘HELLO WORLD’) FROM dual;
c. SELECT LOWER(‘HELLO WORLD’) FROM dual;
d. both a and b
e. none of the above

5. Which of the following functions can be used to substitute a value for a NULL value?
a. NVL
b. TRUNC
c. NVL2
d. SUBSTR
e. both a and d
f. both a and c

6. Which of the following is not a valid format argument for displaying the current time?
a. ‘HH:MM:SS’
b. ‘HH24:SS’
c. ‘HH12:MI:SS’
d. All of the above are valid.

7. Which of the following lists only the last four digits of the contact person’s phone number at American Publishing?
a. SELECT EXTRACT(phone, -4, 1) FROM publisher WHERE name ¼ ‘AMERICAN PUBLISHING’;
b. SELECT SUBSTR(phone, -4, 1) FROM publisher WHERE name = ‘AMERICAN PUBLISHING’;
c. SELECT EXTRACT(phone, -1, 4) FROM publisher WHERE name = ‘AMERICAN PUBLISHING’;
d. SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name = ‘AMERICAN PUBLISHING’;

8. Which of the following functions can be used to determine how many months a book has been available?
a. MONTH
b. MON
c. MONTH_BETWEEN
d. none of the above

9. Which of the following displays the order date for order 1000 as 03/31?
a. SELECT TO_CHAR(orderdate, ‘MM/DD’) FROM orders WHERE order# = 1000;
b. SELECT TO_CHAR(orderdate, ‘Mth/DD’) FROM orders WHERE order# = 1000;
c. SELECT TO_CHAR(orderdate, ‘MONTH/YY’) FROM orders WHERE order# = 1000;
d. both a and b
e. none of the above

10. Which of the following functions can produce different results, depending on the value of a specified column?
a. NVL
b. DECODE
c. UPPER
d. SUBSTR

11. Which of the following SQL statements is not valid?
a. SELECT TO_CHAR(orderdate, ’99/9999′) FROM orders;
b. SELECT INITCAP(firstname), UPPER(lastname) FROM customers;
c. SELECT cost, retail, TO_CHAR(retail-cost, ‘$999.99’) profit FROM books;
d. all of the above

12. Which function can be used to add spaces to a column until it’s a specific width?
a. TRIML
b. PADL
c. LWIDTH
d. none of the above

13. Which of the following SELECT statements returns 30 as the result?
a. SELECT ROUND(24.37, 2) FROM dual;
b. SELECT TRUNC(29.99, 2) FROM dual;
c. SELECT ROUND(29.01, -1) FROM dual;
d. SELECT TRUNC(29.99, -1) FROM dual;

14. Which of the following is a valid SQL statement?
a. SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;
b. SELECT ROUND(TRUNC(125.38, 0) FROM dual;
c. SELECT LTRIM(LPAD(state, 5, ‘ ‘), 4, -3, “*”) FROM dual;
d. SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM dual;

15. Which of the following functions can’t be used to convert the letter case of a character string?
a. UPPER
b. LOWER
c. INITIALCAP
d. All of the above can be used for case conversion.

16. Which of the following format elements causes months to be displayed as a three-letter abbreviation?
a. MMM
b. MONTH
c. MON
d. none of the above

17. Which of the following SQL statements displays a customer’s name in all uppercase
characters?
a. SELECT UPPER(‘firstname’, ‘lastname’) FROM customers;
b. SELECT UPPER(firstname, lastname) FROM customers;
c. SELECT UPPER(lastname, ‘,’ firstname) FROM customers;
d. none of the above

18. Which of the following functions can be used to display the character string FLORIDA in the query results whenever FL is entered in the State field?
a. SUBSTR
b. NVL2
c. REPLACE
d. TRUNC
e. none of the above

19. What’s the name of the table provided by Oracle 11g for completing queries that don’t involve a table?
a. DUMDUM
b. DUAL
c. ORAC
d. SYS

20. If an integer is multiplied by a NULL value, the result is:
a. an integer
b. a whole number
c. a NULL value
d. None of the above—a syntax error message is returned.