READ Free Dumps For Oracle- 1z0-001
| Question ID 8186 | Examine the declaration section: DECLARE CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT EMPNO, ENAME FROM EMPWHERE DEPTNO=p_deptno AND JOB=p_job; BEGIN . . . Which statement opens the cursor successfully?
|
| Option A | OPEN emp_cursor
|
| Option B | OPEN emp_cursor('clerk',10);
|
| Option C | OPEN emp_cursor(10, 'analyst');
|
| Option D | OPEN emp_cursor (p_deptno,p_job);
|
| Correct Answer | C |
Explanation Explanation/Reference: Explanation: Answers C is correct because the statement uses correct data types (NUMBER and VARCHAR2) for the parameters of cursor. Incorrect Answers: A: The statement does not use at all parameters of cursor. B: The statement does not use correct data types for the parameters of cursor. D: The statement needs to use bind variables to pass correct data types (NUMBER and VARCHAR2) for the parameters of cursor, not variables used for cursor definition. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 231-233 Chapter 5: Introducing PL/SQL
| Question ID 8187 | You want to display the average salary for the departments 20 and 50 but only if those departments have an average salary of at least 2000. Which statement will produce the required results?
|
| Option A | SELECT deptno, AVG(sal) FROM emp WHERE depno IN(20,50) GROUP BY deptno HAVING AVG (sal)>=2000;
|
| Option B | SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG (sal)>=2000; Deptno IN (20,50);
|
| Option C | SELECT deptno, AVG(sal) FROM emp WHERE deptno IN (20,50) AND AVG (sal)>=2000 GROUP BY deptno;
|
| Option D | SELECT deptno, AVG(sal) FROM emp WHERE deptno IN (20,50) GROUP BY AVG(sal) HAVING AVG(sal)>=2000
|
| Correct Answer | A |
Explanation Explanation/Reference: Explanation: Answers A is correct because this statement will display correct results using the WHERE, the GROUP BY, the HAVING clauses and function AVG. Incorrect Answers: B: The WHERE clause need to be used to restrict rows only with employees from the departments 20 and 50. C: Function AVG cannot be used in the WHERE clause. D: We don't need to group data by average salary, but by the department. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 57-64 Chapter 2: Advanced Data Selection in Oracle