Section 5 Quiz
(Answer all questions in this section)
1. Which three statements concerning explicit data type conversions are Mark for
true? (Choose three.)
Review
(1) Points
(Choose all correct answers)
Use the TO_NUMBER function to convert a character string of digits to
a number. (*)
Use the TO_DATE function to convert a date value to a character
string or number.
Use the TO_CHAR function to convert a number or date value to a
character string. (*)
Use the TO_DATE function to convert a character string to a date
value. (*)
Use the TO_NUMBER function to convert a number to a character
string.
Correct
2. Which SQL Statement should you use to display the prices in this format: Mark for
"$00.30"?
Review
(1) Points
SELECT TO_CHAR(price, '$99,900.99')
FROM product;
(*)
SELECT TO_CHAR(price, '$99,999.99')
FROM product;
SELECT TO_NUMBER(price, '$99,900.99')
FROM product;
SELECT TO_CHAR(price, '$99,990.99')
FROM product;
Incorrect. Refer to Section 5 Lesson 1.
3. Which best describes the TO_CHAR function? Mark for
Review
(1) Points
The TO_CHAR function can be used to remove text from column data
that will be returned by the database.
The TO_CHAR function can be used to display dates and numbers
according to formatting conventions that are supported by Oracle. (*)
The TO_CHAR function can be used to specify meaningful column
names in an SQL statement's result set.
The TO_CHAR function can only be used on Date columns.
Correct
4. You need to display the HIRE_DATE values in this format: 25th of July Mark for
2002. Which SELECT statement would you use?
Review
(1) Points
SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM employees;
(*)
SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM employees;
Correct
5. The EMPLOYEES table contains these columns: Mark for
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25) Review
FIRST_NAME VARCHAR2 (25) (1) Points
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SQL statement could you use?
SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM employees;
(*)
SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
FROM employees;
SELECT TO_CHAR(hire_date, Month DD, YYYY)
FROM employees;
SELECT hire_date(TO_CHAR 'Month DD', ' YYYY')
FROM employees;
Correct
6. Sysdate is 12-May-2004. Mark for
You need to store the following
date: 7-Dec-89
Which statement about the Review
date format for this value is (1) Points
true?
Both the YY and RR date
formats will interpret the
year as 1989
The RR date format will
interpret the year as 1989,
and the YY date format
will interpret the year as
2089 (*)
The RR date format will
interpret the year as 2089,
and the YY date format
will interpret the year as
1989
Both the YY and RR date
formats will interpret the
year as 2089
Correct
7. With the following data in Mark for
Employees (last_name,
commission_pct, manager_id)
what is the result of the Review
following statement? (1) Points
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
SELECT last_name,
NVL2(commission_pct,
manager_id, -1) comm
FROM employees ;
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)
Statement will fail.
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2
Incorrect. Refer to Section
5 Lesson 2.
8. Which function compares two Mark for
expressions?
Review
(1) Points
NVL2
NULLIF (*)
NVL
NULL
Correct
9. You need to replace null values Mark for
in the DEPT_ID column with
the text N/A. Which functions
should you use? Review
(1) Points
TO_CHAR and NVL (*)
TO_NUMBER and NULLIF
TO_CHAR and NULL
TO_CHAR and NULLIF
Correct
10. When executed, which Mark for
statement displays a zero if
the TUITION_BALANCE value is
zero and the Review
HOUSING_BALANCE value is (1) Points
null?
SELECT
NVL(tuition_balance, 0),
NVL (housing_balance),
tuition_balance +
housing_balance "Balance
Due"
FROM student_accounts;
SELECT tuition_balance +
housing_balance
FROM student_accounts;
SELECT
TO_NUMBER(tuition_balan
ce, 0), TO_NUMBER
(housing_balance, 0),
tutition_balance +
housing_balance "Balance
Due"
FROM student_accounts;
SELECT NVL
(tuition_balance +
housing_balance, 0)
"Balance Due"
FROM student_accounts;
(*)
Correct
11. Which Mark for Review
stateme
nt about
group (1) Points
function
s is
true?
NVL and NVL2, but not COALESCE, can be used with group
functions to replace null values.
NVL and COALESCE, but not NVL2, can be used with group
functions to replace null values.
COALESCE, but not NVL and NVL2, can be used with group
functions to replace null values.
NVL, NVL2, and COALESCE can be used with group functions
to replace null values. (*)
Correct
12. Consider the following data in the Employees table: (last_name, Mark for
commission_pct, manager_id)
DATA:
King, null, null Review
Kochhar, null, 100 (1) Points
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1)
comm
FROM employees ;
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, 100
Statement will fail
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)
King, null
Kochhar, 100
Vargas, 124
Zlotkey, .2
Correct
13. Which statement will return a listing of last names, salaries, and a Mark for
rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the
salary value?
Review
(1) Points
SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
(*)
SELECT last_name,sal,
(CASE WHEN sal<5000 THEN 'Low'
WHEN sal<10000 THEN 'Medium'
WHEN sal<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT last_name,salary,
(RATING WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN sal <10000 THEN 'Medium'
WHEN sal <20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
Correct
14. Which of the following is a conditional expression used in SQL? Mark for
Review
(1) Points
CASE (*)
DESCRIBE
NULLIF
WHERE
Correct
15. For the given data from Employees (last_name, manager_id) what Mark for
is the result of the following statement:
DATA:( King, null
Kochhar, 100 Review
De Haan, 100 (1) Points
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
Invalid statement.
Correct
