Database programming section 5

1. 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 ;
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2 (*)

2. If quantity is a number datatype, what is the result of this statement?
SELECT NVL(200/quantity, 'zero') FROM inventory;
The statement fails (*)

3. The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;
False(*)

4. Which of the following General Functions will return the first non-null expression in the expression list?
COALESCE(*)

5. 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?
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)

6. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts; (*)

7. Which of the following is a conditional expression used in SQL?
CASE (*)

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

King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan

9. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?
True(*)

10.  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?

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

11. Which functions allow you to perform explicit data type conversions?
TO_CHAR, TO_DATE, TO_NUMBER (*)

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

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

13. All Human Resources data is stored in a table named EMPLOYEES. You have been asked to create a report that displays each employee's name and salary. Each employee's salary must be displayed in the following format: $000,000.00. Which function should you include in a SELECT statement to achieve the desired result?
TO_CHAR (*)

14. 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?
SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500; (*)

15. 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?
The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)