Oracle 1Z0-001 Exam - PassITexam.com

Free 1Z0-001 Sample Questions:

1.Which statement is true when writing a cursor FOR loop?
A: You must explicitly fetch the rows within a cursor FOR loop.
B: You must explicitly open the cursor prior to the cursor FOR loop.
C: You must explicitly close the cursor prior to the end of the program.
D: You do not explicitly open, fetch or close the cursor within a cursor FOR loop .
E: You must explicitly declare the record variable that holds the row returned from the cursor.
Answer: D

2.The EMP table contains columns to hold the birth date and hire date of employees. Both of these columns are defined with DATE as their datatype. You want to insert a row with the details of employee Smith who was born in 1944 and hired in 2004. Which statement will ensure that values are inserted into the table in the correct century?
A: INSERT INTO EMP(empno, ename, birthdate, hiredate)
VALUES (EMPNO_SEQ.NEXTVAL,'SMITH', '12-DEC-44',
'10-JUN-04');
B: INSERT INTO EMP(empno, ename, birthdate, hiredate)
VALUES (EMPNO_SEQ.NEXTVAL,'SMITH',
TO_DATE('12-DEC-44','DD-MON-RR'),
TO_DATE('10-JUN-04','DD-MON-RR'));
C: INSERT INTO EMP(empno, ename, birthdate, hiredate)
VALUES (EMPNO_SEQ.NEXTVAL,'SMITH',
TO_DATE('12-DEC-44','DD-MON-YY'),
TO_DATE('10-JUN-04','DD-MON-YY'));
D: INSERT INTO EMP(empno, ename, birthdate, hiredate)
VALUES (EMPNO_SEQ.NEXTVAL,'SMITH',
TO_DATE('12-DEC-1944','DD-MON-YYYY'),
TO_DATE('10-JUN-04','DD-MON-RR'));
Answer: D

3.You are a user of the PROD database which contains over 1000 tables, and you need to determine the number of tables you can access. Which data dictionary view could you query to display this information?
A: USER_OBJECTS
B: ALL_OBJECTS
C: DBA_SEGMENTS
D: DBA_TABLES
Answer: B

4.Which SELECT statement would you use in a PL/SQL block to query the employee table and retrieve the last name and salary of the employee whose id is 3?
A: SELECT last_name, salary
FROM employee;
B: SELECT last_name, salary
FROM employee
WHERE id = 3;
C: SELECT last_name, salary
INTO v_last_name, v_salary
FROM employee
WHERE id = 3;
D: SELECT last_name, salary
FROM employee
INTO v_last_name, v_salary
WHERE id = 3;
Answer: C

5.You need to perform a major update on the EMPLOYEE table. You have decided to disable the PRIMARY KEY constraint on the empid column and the CHECK constraint on the job column.
What happens when you try to enable the constraints after the update is completed?
A: You need to recreate the constraints once they are disabled.
B: Any existing rows that do not confirm with the constraints are automatically deleted.
C: Only the future values are verified to confirm with the constraints, leaving the existing values unchecked.
D: The indexes on both the columns with the PRIMARY KEY constraint and the CHECK constraint are automatically re-created.
E: All the existing column values are verified to confirm with the constraints and an error message is generated if any existing values do not confirm.
Answer: E

6.In SQL*Plus, you issued this command:
DELETE FROM dept
WHERE dept_id = 901;
You received an integrity constraint error because a child record was found. What could you do to make the statement execute?
A: Delete the child records first.
B: You cannot make the command execute.
C: Add the FORCE keyword to the command.
D: Add the CONSTRAINTS CASCADE option to the command.
Answer: A

7.You want to display the average salary for departments 20 and 50, but only if those departments have an average salary of at least 2000. Which statement will produce the required results?
A: SELECT deptno, AVG(sal)
FROM emp
WHERE deptno IN (20, 50)
GROUP BY deptno
HAVING AVG(sal) >= 2000;
B: SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) >= 2000
AND deptno IN (20, 50);
C: SELECT deptno, AVG(sal)
FROM emp
WHERE deptno IN (20, 50)
AND AVG(sal) >= 2000
GROUP BY deptno;
D: SELECT deptno, AVG(sal)
FROM emp
WHERE deptno IN (20, 50)
GROUP BY AVG(sal)
HAVING AVG(sal) >= 2000;
Answer: A

8.In which section of a PL/SQL block is a user-defined exception raised?
A: heading
B: executable
C: declarative
D: exception handling
Answer: B

9.The structure of the DEPT table is as follows:
Name Null? Type
------------------------------- ---------- --------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Examine the declaration section:
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
You need to assign the LOC field in record 15, the value of 'Atlanta'. Which PL/SQL statement makes this assignment?
A: dept_table.loc.15 := 'Atlanta';
B: dept_table[15].loc := 'Atlanta';
C: dept_table(15).loc := 'Atlanta';
D: dept_table_type(15).loc := 'Atlanta';
Answer: C

10.Given this executable section of a PL/SQL block:
BEGIN
FOR employee_record IN salary_cursor LOOP
employee_id_table(employee_id) :=
employee_record.last_name;
END LOOP;
CLOSE salary_cursor;
END;
Why does this section cause an error?
A: The cursor needs to be opened.
B: No FETCH statements were issued.
C: Terminating conditions are missing.
D: The cursor does not need to be closed.
Answer: D