READ Free Dumps For Oracle- 1z0-007
| Question ID 7907 | Which four statements correctly describe functions that are available in SQL? (Choose four)
|
| Option A | INSTR returns the numeric position of a named character.
|
| Option B | NVL2 returns the first non-null expression in the expression list.
|
| Option C | TRUNCATE rounds the column, expression, or value to n decimal places.
|
| Option D | DECODE translates an expression after comparing it to each search value.
|
| Option E | TRIM trims the heading of trailing characters (or both) from a character string.
|
| Option F | G. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.
|
| Correct Answer | ADEG |
Explanation Explanation/Reference: Explanation: INSTR returns the numeric position of a named character. DECODE translates an expression after comparing it to each search value. TRIM trims the heading of trailing characters (or both) from a character string. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal. Incorrect Answers B: This statement is not correct. The following is the Syntax for NVL2 function: NVL2(expr1, expr2, expr3). If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. C: Command TRUNCATE is used to remove all row data from the table, while leaving the definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table. F: NVL returns second parameter value if first one is NULL. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 67-73 Chapter 2: Limiting, Sorting, and Manipulating Return Data
| Question ID 7908 | Examine the data from the EMP table:
EMP_ID DEPT_ID COMMISSION
1 10 500
2 20 1000
3 10
4 10 600
5 30 800
6 30 200
7 10
8 20 300
The COMMISSION column shows the monthly commission earned by the employee. Which three tasks would require subqueries or joins in order to perform in a single step? (Choose three)
|
| Option A | Deleting the records of employees who do not earn commission.
|
| Option B | Increasing the commission of employee 3 by the average commission earned in department 20.
|
| Option C | Finding the number of employees who do NOT earn commission and are working for department 20.
|
| Option D | Inserting into the table a new employee 10 who works for department 20 and earns a commission that is equal to the commission earned by employee 3.
|
| Option E | Creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSIONS of the EMP table.
|
| Option F | Decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more then 800.
|
| Correct Answer | BDE |
Explanation Explanation/Reference: Explanation: Increasing the commission of employee 3 by the average commission earned in department 20 will require subqueries or joins in order to perform in a single step. Inserting into the table a new employee 10 who works for department 20 and earns a commission that is equal to the commission earned by employee 3 is correct answer also. To create a table that has the same structure as subset of columns from another table it's required to use a subquery in order to perform in a single step. Incorrect Answers A: This query can be done without using sub-queries or joins in order to perform in a single step. C: It is not required to use sub-queries or joins to create SQL statement for that query. F: This query can be done without using sub-queries or joins in order to perform in a single step: only two conditions in the WHERE clause should be specified.