Untitled

1. 	You have been asked to create a report that lists all customers who have placed orders of at least $2,500. The report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004 ).
Which statement should you issue?	 Mark for Review
(1) Points


	SELECT companyname, TO_CHAR (sysdate, 'fmdd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;


	SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
(*)


	SELECT companyname, TO_DATE (sysdate, 'dd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;


	SELECT companyname, TO_DATE (date, 'day, dd month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;


Correct		Correct


		2. 	Which three statements concerning explicit data type conversions are true? (Choose three.)	 Mark for Review
(1) Points

			(Choose all correct answers)


	Use the TO_DATE function to convert a date value to a character string or number.


	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.


	Use the TO_CHAR function to convert a number or date value to a character string. (*)


	Use the TO_NUMBER function to convert a character string of digits to a number. (*)


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		3. 	If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result?	 Mark for Review
(1) Points


	1917


	2017 (*)


	1901


	2001


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		4. 	Which functions allow you to perform explicit data type conversions?	 Mark for Review
(1) Points


	ROUND, TRUNC, ADD_MONTHS


	TO_CHAR, TO_DATE, TO_NUMBER (*)


	NVL, NVL2, NULLIF


	LENGTH, SUBSTR, LPAD, TRIM


Correct		Correct


		5. 	The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
SALARY NUMBER(6)
You need to create a report to display the salaries of all employees. Which SQL Statement should you use to display the salaries in format: "$45,000.00"?

 Mark for Review
(1) Points


	SELECT TO_NUM(salary, '$999,999.00')
FROM employees;


	SELECT TO_CHAR(salary, '$999,999')
FROM employees;


	SELECT TO_CHAR(salary, '$999,999.00')
FROM employees;
(*)


	SELECT TO_NUM(salary, '$999,990.99')
FROM employees;


Incorrect		Incorrect. Refer to Section 5 Lesson 1.

				6. 	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 only be used on Date columns.


	The TO_CHAR function can be used to specify meaningful column names in an SQL statement's result set.


Correct		Correct


		7. 	Which statement about group functions is true?	 Mark for Review
(1) Points


	NVL, NVL2, and 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.


	NVL and NVL2, but not COALESCE, 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.


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		8. 	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


	Null


	The statement fails (*)


	ZERO


	zero


Correct		Correct


		9. 	The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;	 Mark for Review
(1) Points


	True


	False (*)


Correct		Correct


		10. 	Which of the following General Functions will return the first non-null expression in the expression list?	 Mark for Review
(1) Points


	NVL


	NVL2


	NULLIF


	COALESCE (*)


Incorrect		Incorrect. Refer to Section 5 Lesson 2.11. 	Which function compares two expressions?	 Mark for Review
(1) Points


	NVL


	NULLIF (*)


	NULL


	NVL2


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		12. 	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


	Statement will fail.


	King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2


	King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2


	King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		13. 	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, 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


	King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)


Incorrect		Incorrect. Refer to Section 5 Lesson 3.


		14. 	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,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,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;
(*)


Incorrect		Incorrect. Refer to Section 5 Lesson 3.


		15. 	Which of the following is a conditional expression used in SQL?	 Mark for Review
(1) Points


	WHERE


	DESCRIBE


	CASE (*)


	NULLIF


Incorrect		Incorrect. Refer to Section 5 Lesson 3.1. 	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 (*)


Correct		Correct


		2. 	The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;	 Mark for Review
(1) Points


	True


	False (*)


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		3. 	Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
DATA:
King, null, null
Kochhar, null, 100
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 ;

 Mark for Review
(1) Points


	King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)


	King, -1
Kochhar, 100
Vargas, 124
Zlotkey, 100


	King, null
Kochhar, 100
Vargas, 124
Zlotkey, .2


	Statement will fail


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		4. 	The STYLES table contains this data:
STYLE_ID	STYLE_NAME	CATEGORY	COST
895840	SANDAL	85940	12.00
968950	SANDAL	85909	10.00
869506	SANDAL	89690	15.00
809090	LOAFER	89098	10.00
890890	LOAFER	89789	14.00
857689	HEEL	85940	11.00
758960	SANDAL	86979
Evaluate this SELECT statement:

SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;

Which result will the query provide?

 Mark for Review
(1) Points


STYLE_ID	STYLE_NAME	CATEGORY	COST
895840	SANDAL	85909	12.00
968950	SANDAL	85909	10.00
758960	SANDAL	86979
869506	SANDAL	89690	15.00


STYLE_ID	STYLE_NAME	CATEGORY	COST
895840	SANDAL	85940	12.00
968950	SANDAL	85909	10.00
758960	SANDAL	86979


STYLE_ID	STYLE_NAME	CATEGORY	COST
968950	SANDAL	85909	10.00
895840	SANDAL	85940	12.00
758960	SANDAL	86979
(*)


STYLE_ID	STYLE_NAME	CATEGORY	COST
895840	SANDAL	85909	12.00
968950	SANDAL	85909	10.00
869506	SANDAL	89690	15.00
758960	SANDAL	86979


Correct		Correct


		5. 	Which statement about group functions is true?	 Mark for Review
(1) Points


	NVL and NVL2, but not COALESCE, 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 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.


Incorrect		Incorrect. Refer to Section 5 Lesson 2.6. 	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


	Statement will fail.


	King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2


	King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)


	King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		7. 	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,990.99')
FROM product;


	SELECT TO_NUMBER(price, '$99,900.99')
FROM product;


	SELECT TO_CHAR(price, '$99,900.99')
FROM product;
(*)


	SELECT TO_CHAR(price, '$99,999.99')
FROM product;


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		8. 	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 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;


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		9. 	Which arithmetic operation will return a numeric value?	 Mark for Review
(1) Points


	NEXT_DAY(hire_date) + 5


	TO_DATE('01-Jun-2004') - TO_DATE('01-Oct-2004') (*)


	SYSDATE - 6


	SYSDATE + 30 / 24


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		10. 	A table has the following definition: EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))

and contains the following rows:

(1001, 'Bob Bevan', '200')
(200, 'Natacha Hansen', null)

Will the folloiwng query work?

SELECT *
FROM employees
WHERE employee_id = manager_id;	 Mark for Review
(1) Points


	Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)


	No, because the datatypes of EMPLOYEE_ID and MANAGER_ID are different.


	Yes, Oracle will perform implicit dataype conversion, and the query will return one row of data.


	No.? You will have to re-wirte the statement and perform explicit datatype conversion.


Incorrect		Incorrect. Refer to Section 5 Lesson 1.
		11. 	Which statement concerning single row functions is true?	 Mark for Review
(1) Points


	Single row functions cannot modify a data type.


	Single row functions can be nested. (*)


	Single row functions can accept only one argument, but can return multiple values.


	Single row functions return one or more results per row.


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		12. 	Which two statements concerning SQL functions are true? (Choose two.)	 Mark for Review
(1) Points

			(Choose all correct answers)


	Number functions can return number or character values.


	Not all date functions return date values. (*)


	Character functions can accept numeric input.


	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. (*)


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		13. 	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,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;


	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;


Incorrect		Incorrect. Refer to Section 5 Lesson 3.


		14. 	Which of the following is a conditional expression used in SQL?	 Mark for Review
(1) Points


	CASE (*)


	WHERE


	NULLIF


	DESCRIBE


Incorrect		Incorrect. Refer to Section 5 Lesson 3.


		15. 	CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?	 Mark for Review
(1) Points


	True (*)


	False


Correct		Correct1. 	You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use?	 Mark for Review
(1) Points


	SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM employees;


	SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM employees;


	SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM employees;
(*)


	SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM employees;


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		2. 	Which arithmetic operation will return a numeric value?	 Mark for Review
(1) Points


	NEXT_DAY(hire_date) + 5


	SYSDATE - 6


	TO_DATE('01-Jun-2004') - TO_DATE('01-Oct-2004') (*)


	SYSDATE + 30 / 24


Correct		Correct


		3. 	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 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;


	SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM employees;
(*)


Incorrect		Incorrect. Refer to Section 5 Lesson 1.


		4. 	If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result?	 Mark for Review
(1) Points


	2017 (*)


	1901


	2001


	1917


Correct		Correct


		5. 	Which functions allow you to perform explicit data type conversions?	 Mark for Review
(1) Points


	LENGTH, SUBSTR, LPAD, TRIM


	ROUND, TRUNC, ADD_MONTHS


	NVL, NVL2, NULLIF


	TO_CHAR, TO_DATE, TO_NUMBER (*)


Incorrect		Incorrect. Refer to Section 5 Lesson 1.6. 	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(sal, '$99999.00') SALARY
FROM employees


	SELECT TO_CHAR(salary, '$99999') SALARY
FROM employees


	SELECT TO_CHAR(salary, '$99999.00') SALARY
FROM employees
(*)


Correct		Correct


		7. 	Which of the following General Functions will return the first non-null expression in the expression list?	 Mark for Review
(1) Points


	NVL


	COALESCE (*)


	NVL2


	NULLIF


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		8. 	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 and COALESCE, but not 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.


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		9. 	Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
DATA:
King, null, null
Kochhar, null, 100
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 ;

 Mark for Review
(1) Points


	King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)


	King, null
Kochhar, 100
Vargas, 124
Zlotkey, .2


	King, -1
Kochhar, 100
Vargas, 124
Zlotkey, 100


	Statement will fail


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		10. 	The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;	 Mark for Review
(1) Points


	True


	False (*)


Incorrect		Incorrect. Refer to Section 5 Lesson 2.

				11. 	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 NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts;
(*)


	SELECT tuition_balance + housing_balance
FROM student_accounts;


	SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance "Balance Due"
FROM student_accounts;


	SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
FROM student_accounts;


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		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


	Null


	The statement fails (*)


	ZERO


Incorrect		Incorrect. Refer to Section 5 Lesson 2.


		13. 	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, 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


Correct		Correct


		14. 	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,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,
(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,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;


Incorrect		Incorrect. Refer to Section 5 Lesson 3.


		15. 	CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?	 Mark for Review
(1) Points


	True (*)


	False


Incorrect		Incorrect. Refer to Section 5 Lesson 3.