READ Free Dumps For Oracle- 1z0-007
| Question ID 7848 | Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHARD2(10)
START_DATE DATE
END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose two)
|
| Option A | SUM(start_date)
|
| Option B | AVG(start_date)
|
| Option C | COUNT(start_date)
|
| Option D | AVG(start_date, end_date)
|
| Option E | MIN(start_date)
|
| Option F | MAXIMUM(start_date)
|
| Correct Answer | CE |
Explanation Explanation/Reference: Explanation: It is possible to apply COUNT() and MIN() functions on the column with DATE data type. Incorrect Answers A: Function SUM() cannot be used with DATE data type column. B: Function AVG() cannot be used with DATE data type column. D: Function AVG() cannot be used with DATE data type column. And function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement. F: There is no MAXIMUM() function in Oracle, only MAX() function exists. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 81-85 Chapter 2: Limiting, Sorting, and Manipulating Return Data
| Question ID 7849 | The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
|
| Option A | SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;
|
| Option B | SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;
|
| Option C | SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;
|
| Option D | SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;
|
| Correct Answer | D |
Explanation Explanation/Reference: Explanation: This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column in NULL, the special string is returned. the value specified to be returned if the column value is NULL must be the same datatype as the column specified. Incorrect Answers A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT column. B: There is no IFNULL() function in Oracle. C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32 Chapter 1: Overview of Oracle Databases