AllExam Dumps

DUMPS, FREE DUMPS, VCP5 DUMPS| VMWARE DUMPS, VCP DUMPS, VCP4 DUMPS, VCAP DUMPS, VCDX DUMPS, CISCO DUMPS, CCNA, CCNA DUMPS, CCNP DUMPS, CCIE DUMPS, ITIL, EXIN DUMPS,


READ Free Dumps For Oracle- 1z0-007





Question ID 7901

Examine the structure of the STUDENTS table: STUDENT_ID NUMBER NOT NULL, Primary Key STUDENT_NAME VARCHAR2(30) COURSE_ID VARCHAR2(10) NOT NULL MARKS NUMBER START_DATE DATE FINISH_DATE DATE You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Which SQL statement accomplishes this task?

Option A

SELECT student_ id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC;

Option B

SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks;

Option C

SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC);

Option D

SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks DESC) WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course _ id ='INT _ SQL';

Correct Answer D
Explanation Explanation/Reference: Explanation: This statement using inline sub-query will provide correct results to show 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Incorrect Answers A: This view will just show first 10 students from the STUDENTS table with limitation on the course INT SQL and who completed the course in the year 1999. B: It's wrong to use here ROWID to achieve desired results. C: This statement will provide wrong result due to wrong conditions in the inline sub-query. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 162-165 Chapter 4: Subqueries


Question ID 7902

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

Send email to admin@getfreedumps for new dumps request!!!