Oracle Database Programming with SQL Section 5 Quiz
Section 5 Quiz
(Answer all questions in this section)
1. Which SQL Statement should you use to display the prices in this format: "$00.30"? Mark for Review
(1) Points
SELECT TO_CHAR(price, '$99,999.99')
FROM product;
SELECT TO_CHAR(price, '$99,900.99')
FROM product;
(*)
SELECT TO_CHAR(price, '$99,990.99')
FROM product;
SELECT TO_NUMBER(price, '$99,900.99')
FROM product;
2. Which two statements concerning SQL functions are true? (Choose two.) Mark for Review
(1) Points
(Choose all answers)
Character functions can accept numeric input.
Number functions can return number or character values.
Not all date functions return date values. (*)
Single-row functions manipulate groups of rows to return one result per group of rows.
Conversion functions convert a value from one data type to another data type. (*)
3. Sysdate is 12-May-2004.
You need to store the following date: 7-Dec-89
Which statement about the date format for this value is true? Mark for Review
(1) Points
Both the YY and RR date formats will interpret the year as 1989
The RR date format will interpret the year as 2089, and the YY date format 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 (*)
Both the YY and RR date formats will interpret the year as 2089
4. Which statement will return the salary (for example, the salary of 6000) from the Employees table in the following format? $6000.00 Mark for Review
(1) Points
SELECT TO_CHAR(salary, '$99999.00') SALARY
FROM employees
(*)
SELECT TO_CHAR(salary, '99999.00') SALARY
FROM employees
SELECT TO_CHAR(salary, '$99999') SALARY
FROM employees
SELECT TO_CHAR(sal, '$99999.00') SALARY
FROM employees
5. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SQL statement could you use?
Mark for Review
(1) Points
SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
FROM employees;
SELECT hire_date(TO_CHAR '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;
6. Which functions allow you to perform explicit data type conversions? Mark for Review
(1) Points
NVL, NVL2, NULLIF
ROUND, TRUNC, ADD_MONTHS
LENGTH, SUBSTR, LPAD, TRIM
TO_CHAR, TO_DATE, TO_NUMBER (*)
7. Which of the following General Functions will return the first non-null expression in the expression list? Mark for Review
(1) Points
NULLIF
NVL
NVL2
COALESCE (*)
8. The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual; Mark for Review
(1) Points
True
False (*)
9. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null? Mark for Review
(1) Points
SELECT TO_NUMBER(tuition_balance, 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;
(*)
SELECT tuition_balance + housing_balance
FROM student_accounts;
SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
FROM student_accounts;
10. Which statement about group functions is true? Mark for Review
(1) Points
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. (*)
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.
11. With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement?
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 ;
Mark for Review
(1) Points
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
Statement will fail.
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)
12. If quantity is a number datatype, what is the result of this statement?
SELECT NVL(200/quantity, 'zero') FROM inventory; Mark for Review
(1) Points
zero
The statement fails (*)
Null
ZERO
13. Which of the following is a conditional expression used in SQL? Mark for Review
(1) Points
DESCRIBE
CASE (*)
WHERE
NULLIF
14. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
Mark for Review
(1) Points
Invalid statement.
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
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, A N Other
Ernst, A N Other
(*)
15. Which statement will return a listing of last names, salaries, and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value? Mark for Review
(1) Points
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,
(CASE WHEN salary<5000 THEN 'Low'
WHEN sal <10000 THEN 'Medium'
WHEN sal <20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
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,salary,
(RATING WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
(Answer all questions in this section)
1. Which SQL Statement should you use to display the prices in this format: "$00.30"? Mark for Review
(1) Points
SELECT TO_CHAR(price, '$99,999.99')
FROM product;
SELECT TO_CHAR(price, '$99,900.99')
FROM product;
(*)
SELECT TO_CHAR(price, '$99,990.99')
FROM product;
SELECT TO_NUMBER(price, '$99,900.99')
FROM product;
2. Which two statements concerning SQL functions are true? (Choose two.) Mark for Review
(1) Points
(Choose all answers)
Character functions can accept numeric input.
Number functions can return number or character values.
Not all date functions return date values. (*)
Single-row functions manipulate groups of rows to return one result per group of rows.
Conversion functions convert a value from one data type to another data type. (*)
3. Sysdate is 12-May-2004.
You need to store the following date: 7-Dec-89
Which statement about the date format for this value is true? Mark for Review
(1) Points
Both the YY and RR date formats will interpret the year as 1989
The RR date format will interpret the year as 2089, and the YY date format 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 (*)
Both the YY and RR date formats will interpret the year as 2089
4. Which statement will return the salary (for example, the salary of 6000) from the Employees table in the following format? $6000.00 Mark for Review
(1) Points
SELECT TO_CHAR(salary, '$99999.00') SALARY
FROM employees
(*)
SELECT TO_CHAR(salary, '99999.00') SALARY
FROM employees
SELECT TO_CHAR(salary, '$99999') SALARY
FROM employees
SELECT TO_CHAR(sal, '$99999.00') SALARY
FROM employees
5. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SQL statement could you use?
Mark for Review
(1) Points
SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
FROM employees;
SELECT hire_date(TO_CHAR '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;
6. Which functions allow you to perform explicit data type conversions? Mark for Review
(1) Points
NVL, NVL2, NULLIF
ROUND, TRUNC, ADD_MONTHS
LENGTH, SUBSTR, LPAD, TRIM
TO_CHAR, TO_DATE, TO_NUMBER (*)
7. Which of the following General Functions will return the first non-null expression in the expression list? Mark for Review
(1) Points
NULLIF
NVL
NVL2
COALESCE (*)
8. The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual; Mark for Review
(1) Points
True
False (*)
9. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null? Mark for Review
(1) Points
SELECT TO_NUMBER(tuition_balance, 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;
(*)
SELECT tuition_balance + housing_balance
FROM student_accounts;
SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
FROM student_accounts;
10. Which statement about group functions is true? Mark for Review
(1) Points
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. (*)
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.
11. With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement?
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 ;
Mark for Review
(1) Points
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
Statement will fail.
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)
12. If quantity is a number datatype, what is the result of this statement?
SELECT NVL(200/quantity, 'zero') FROM inventory; Mark for Review
(1) Points
zero
The statement fails (*)
Null
ZERO
13. Which of the following is a conditional expression used in SQL? Mark for Review
(1) Points
DESCRIBE
CASE (*)
WHERE
NULLIF
14. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
Mark for Review
(1) Points
Invalid statement.
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
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, A N Other
Ernst, A N Other
(*)
15. Which statement will return a listing of last names, salaries, and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value? Mark for Review
(1) Points
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,
(CASE WHEN salary<5000 THEN 'Low'
WHEN sal <10000 THEN 'Medium'
WHEN sal <20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
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,salary,
(RATING WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
Comments
Post a Comment