 |
| Interacting with the Oracle Database Server |
01. Written a PL/SQL loop, you need to test if the current FETCH was successful. Which SQL cursor attribute would you use to accomplish this task?
a. SQL % ISOPEN.
b. SQL % ROWCOUNT.
c. SQL % FOUND.
d. This task cannot be accomplished with a SQL cursor attribute.
e. A SQL cursor attribute cannot be used within a PL/SQL loop.
Answer: c
02. Which statement about implicit cursors is true?
a. Implicit cursors are declared implicitly only for DML statements.
b. Implicit cursors are declared implicitly for all the DML and SELECT statements.
c. Programmers need to close all the implicit cursors before the end of the PL/SQL program.
d. Programmers can declare implicit cursors by using the cursor type in the declaration section.
Answer: b
03. Evaluate this PL/SQL block:
DECLARE
v_result NUMBER(2);
BEGIN
DELETE
FROM employee
WHERE dept_id IN (10,20,30);
v_result := SQL%ROWCOUNT;
COMMIT;
END;
What will be the value of v_result if no rows are deleted?
a. 0.
b. 1.3
c. True.
d. Null.
Answer: a
04. 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 l last_name,salary
FROM employee;
INTO v_last_na me,v_salary
WHERE id=3;
e. SELECT last_name,salary
INTO v_last_name,v_salary
WHERE id=3;
Answer: c
05. You need to create a PL/SQL program to insert records into employee table. Which block of code successfully uses the insert command?
a. DECLARE
v_hiredate DATE := SYSDATE:
BEGIN
INSERT INTO emp (empnp, ename, hiredate, deptno)
VALUES (empno_sequence.nextval, ‘&name’,v_hirerdate and deptno)
b. DECLARE
v_hiredate D ATE := SYSDATE;
BEGIN
INSERT INTO emp (empnp, ename, hiredate, deptno)
c. DECLARE
v_hiredate DATE := SYSDATE;
BEGIN
INSERT INTO emp (empnp, ename, hiredate)
VALUES (empno_sequence.nextval, name, v_hirerdate)
END;
d. DECLARE
v-hiredate D ATE := SYSDATE:
BEGIN
INSERT INTO emp (empnp,ename,heridate,deptno)
VALUES (empno_sequence.nextval, ‘&name’,v_herdate, &deptno)
Job=Clerk
END;
Answer: c
06. In the declarative section of a PL/SQL block, you created but did not initialize a number variable. When the block executes what will be the initial value of the variable?
a. 0.
b. Null.
c. It depends on the scale and precision of the variable.
d. The block will not execute because the variable was not initialized.
Answer: b
07. The employee table contains three columns:
BONUS NUMBER(7,2)
DEPT_ID NUMBER(9)
There are three departments and each department has at least one employee bonus values at least one employee. Bonus values are greater than 500;not all employee receive a bonus.
Evaluate this PL/SQL block:
DECLARE
v_bonus employee.bonus%TYPE := 300;
BEGIN
UPDATE employee
SET bonus = bonus + v_bonus
WHERE dept_id IN (10, 20, 30);
COMMIT;
END;
What will be the result?
a. All the employees will be given a 300 bonus.
b. A subset of 300 employees will be given a 300 bonus.
c. All employees will be given a 300 increase in bonus.
d. A subset of employees will be given a 300 increase in bonus.
Answer: d
08. Which PL/SQL section contains SQL statements to manipulate data in the database?
a. Header.
b. Exception.
c. Executable.
d. Declarative.
Answer: c
09. Which command will end the current transaction by making all the pending changes to the database permanent?
a. DELETE.
b. UPDATE.
c. COMMIT.
d. ROLLBACK.
Answer: c
10. Which types of commands are supported by PL/SQL?
a. DDL.
b. DCL.
c. DML.
d. No commands are supported by PL/SQL.
Answer: c
11. Which cursor attribute evaluates to TRUE if the most recent SQL statement affects one or more rows?
a. SQL%FOUND.
b. SQL%ISOPEN.
c. SQL%ROWCOUNT.
d. SQL%NOTFOUND.
Answer: a
12. Which will a SELECT statement in a PL/SQL block raise an exception?
a. It retrieves only one row.
b. It retrieves more than one row.
c. The SELECT statement is missing a required clause.
d. The data types within the SELECT statement are inconsistent.
Answer: b
13. Which clause is required in a SELECT statement within a PL/SQL block?
a. INTO.
b. WHERE.
c. HAVING.
d. GROUP BY.
e. ORDER BY.
Answer: a
14. Which characteristic applies to an implicit cursor?
a. Will process only one row.
b. Will attempt only one fetch.
c. Allows the programmer to control the number of fetches performed.
d. Will perform only one fetch and will process all of the rows returned by the query.
Answer: a
15. Which of the following statements is true about implicit cursor?
a. Implicit cursors are used for SQL statements that are not named.
b. Developers should use implicit cursors with great care.
c. Implicit cursors are used in cursor for loops to handle data processing.
d. Implicit cursors are no longer a feature in Oracle.
Answer: a
16. After executing an UPDATE statement, the developer codes a PL/SQL block to perform an operation based on SQL%ROWCOUNT.
a. A BOOLEAN value representing the success or failure of the update.
b. A numeric value representing the number of row updated.
c. A VARCHAR2 value identifying the name of the table updated.
d. A LONG value containing all data from the table.
Answer: b
17. Which three of the following are implicit cursors attributes? Choose three.
a. %FOUND.
b. %TOO_MANY_ROWS.
c. %NOTFOUND.
d. %ROWCOUNT.
e. %ROWTYPE.
Answer: a,c,d
0 Comments