Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 1
(Answer all questions in this section)

1. Fourth-generation programming languages include all except _____ and _____.

Mark for Review
(1) Points

(Choose all correct answers)

PL/SQL (*)
MySQL
SQL
C++ (*)
Java (*)

Correct

2. Third-generation programming languages include all except _____ and _____.

Mark for Review
(1) Points

(Choose all correct answers)

SQL (*)
C++
Java
MySQL (*)
PL/SQL

Correct

3. Which of the following can be done using PL/SQL?

Mark for Review
(1) Points

Update data (DML)

Manage database security
All of these can be done (*)
Create customized reports
Develop Web applications using the Web Application Toolkit

Correct

4. Comparing PL/SQL with other languages such as C and Java, which of the following
statements is true? Mark for Review
(1) Points

PL/SQL is easier to learn and does not require an Oracle database or tool
PL/SQL is easier to learn but less efficient
PL/SQL is easier to learn and more efficient (*)
PL/SQL is harder to learn

Correct

5. Which of the following is NOT a PL/SQL programming environment?

Mark for Review
(1) Points
 SQL Workshop in Application Express
Oracle jDeveloper
SQL*Plus
gSQL*Plus (*)

Correct

Section 1
(Answer all questions in this section)

6. What is the purpose of using DBMS_OUTPUT.PUT_LINE in a PL/SQL block?

Mark for Review
(1) Points

To allow a set of statements to be executed repeatedly

To perform conditional tests
To display results to check if our code is working correctly (*)
To store new rows in the database

Correct

7. Which lines of code will correctly display the message "Hello World" ? (Choose two.)
Mark for Review
(1) Points

(Choose all correct answers)

DBMS_OUTPUT.PUT_LINE('Hello' || 'World');
DBMS_OUTPUT.PUT_LINE('Hello World'); (*)
DBMS_OUTPUT('Hello World');
DBMS_OUTPUT.PUT_LINE('Hello' || ' ' || 'World'); (*)

Correct

8. Which keywords must be included in every PL/SQL block? (Choose two.)

Mark for Review
(1) Points

(Choose all correct answers)

DECLARE
EXCEPTION
DBMS_OUTPUT.PUT_LINE
BEGIN (*)
END; (*)

Correct

Section 2
(Answer all questions in this section)

9. Identify which of the following assignment statements are valid. (Choose three.)
Mark for Review
(1) Points

(Choose all correct answers)

v_music_type := 'ROCK'; (*)

 v_last_name := Chandra;
v_population := 333444; (*)
v_blackout_date := '31-DEC-2006'; (*)

Correct

10. Variables may be reused. True or False?

Mark for Review
(1) Points

True (*)
False

Correct
Section 2
(Answer all questions in this section)

11. Variables can be used in the following ways in a PL/SQL block. (Choose two.) Mark for Review
(1) Points

(Choose all correct answers)

To rename tables and columns.

To refer to a single data value several times. (*)
To comment code.
To store data values. (*)

Correct

12. TO_NUMBER, TO_CHAR, and TO_DATE are all examples of: Mark for Review
(1) Points

Explicit conversion functions (*)

Implicit conversion functions
Operators
Character functions

Correct

13. Examine the following block. What should be coded at Line A? Mark for Review
DECLARE
v_char VARCHAR2(8) := '24-Sep-2007'; (1) Points
v_date DATE;
BEGIN
v_date := ....... Line A
END;

v_date := FROM_CHAR(v_char,'dd-Mon-YYYY');
v_date := TO_DATE(v_char,'dd-Mon-YYYY'); (*)
v_date := v_char;

Correct

14. When PL/SQL converts data automatically from one data type to another, it is called Mark for Review
_______ conversion.
(1) Points

Explicit
Implicit (*)
 TO_CHAR

Correct

15. Which of the following are valid PL/SQL operators? (Choose three.) Mark for Review
(1) Points

(Choose all correct answers)

Exception
Exponential (*)
Concatenation (*)
Arithmetic (*)

Correct
Section 2
(Answer all questions in this section)

16. What is the data type of the variable V_DEPT_TABLE in the following declaration? Mark for Review
DECLARE (1) Points
TYPE dept_table_type IS
TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER;
v_dept_table dept_table_type;
…

Scalar
PLS_INTEGER
Composite (*)
LOB

Correct

17. Which of the following is a composite data type? Mark for Review
(1) Points

DATE
RECORD (*)
CLOB
VARCHAR2

Correct

18. Which of the following can be assigned to a BOOLEAN variable? Mark for Review
1. Null
2. False (1) Points
3. True
4. 0

1, 2 and 3 (*)
2, 3 and 4
1, 2, 3 and 4
2 and 3

Correct

19. Which of the following is NOT a character data type? Mark for Review
(1) Points
 LONG
CHAR
VARCHAR2
BOOLEAN (*)

Correct

20. Delimiters are _____ that have special meaning to the Oracle database. Mark for Review
(1) Points

identifiers
variables
symbols (*)

Correct
Section 2
(Answer all questions in this section)

21. Which of the following symbols can be used to enclose a comment in PL/SQL? Mark for Review
(1) Points

:: ::
/* */ (*)
*/ / *
??

Correct

22. Which good programming practice guideline would make this code easier to read? Mark for Review
DECLARE (1) Points
v_sal NUMBER(8,2);
BEGIN
SELECT salary INTO v_sal
FROM employees WHERE employee_id = 100;
UPDATE employees SET salary = v_sal;
END;

Indenting each level of code (*)

Avoiding implicit data type conversions
Using a consistent naming convention for variables
Declaring variables using %TYPE

Correct

23. What symbol is used to comment a series of lines? Mark for Review
(1) Points

* * before and after the comment

/* */ before and after the comment (*)
/ / before and after the comment

Correct

24. What is wrong with this code? Mark for Review

(1) Points
 DECLARE
v_a NUMBER;
BEGIN
v_a := 27;
<>
BEGIN
v_a := 15;
END;

Variable v_a is out of scope within the inner block and therefore cannot be
referenced.
Nothing is wrong, the code will execute successfully.
The outer block has no label.
The inner block has no END; statement. (*)

Correct

25. If a variable definition is not found in an inner block where it is being referenced, Mark for Review
where does it look for it?
(1) Points

This will result in an error.

It looks upward in the parent blocks. (*)
It downward in any other inner blocks.

Correct
Section 2
(Answer all questions in this section)

26. Examine the following code. At Line A, we want to assign a value of 22 to the outer Mark for Review
block's variable v_myvar. What code should we write at Line A?
(1) Points
<>
DECLARE
v_myvar NUMBER;
BEGIN
<>
DECLARE
v_myvar NUMBER := 15;
BEGIN
-- Line A
END;
END;

We cannot reference the outer block's variable because both variables have the
same name
v_myvar := 22;
<>.v_myvar := 22;
v_myvar(outer_block) := 22;
outer_block.v_myvar := 22; (*)

Correct

Section 3
(Answer all questions in this section)

27. Which of the following is NOT a good guideline for retrieving data in PL/SQL? Mark for Review
(1) Points

Specify the same number of variables in the INTO clause as database columns in
the SELECT clause.
 The WHERE clause is optional in nearly all cases. (*)
THE SELECT statement should fetch exactly one row.
Declare the receiving variables using %TYPE

Correct

28. A variable is declared as: Mark for Review

DECLARE (1) Points
v_holdit employees.last_name%TYPE;
BEGIN ...

Which of the following is a correct use of the INTO clause?

SELECT salary
INTO v_holdit
FROM employees
WHERE employee_id=100;
SELECT last_name
INTO v_holdit
FROM employees;
SELECT *
INTO v_holdit
FROM employees;
SELECT last_name
INTO v_holdit
FROM employees
WHERE employee_id=100;

(*)

Correct

29. The following code will return the last name of the employee whose employee id is
equal to 100: True or False? Mark for Review
(1) Points
DECLARE
v_last_name employees.last_name%TYPE;
employee_id employees.employee_id%TYPE := 100;
BEGIN
SELECT last_name INTO v_last_name
FROM employees
WHERE employee_id = employee_id;
END;

True
False (*)

Correct

30. Which of the following best describes a database transaction? Mark for Review
(1) Points

A related set of SQL DML statements which must be executed either completely
or not at all (*)
All the DML statements in a single PL/SQL block
A single SQL statement that updates multiple rows of a table
A SELECT statement based on a join of two or more database tables

Correct
Section 3
(Answer all questions in this section)

31. How many INSERTs can you have in one transaction? Mark for Review
(1) Points
 As many as you can execute before the database does an AUTOSAVE.
One
As many as you want until a different DML statement (UPDATE, DELETE or
MERGE) is executed.
As many as you want until you do a COMMIT or ROLLBACK. (*)

Correct

32. What is wrong with the following statement? Mark for Review
MERGE INTO emps e (1) Points
USING new_emps ne
ON (e.employee_id = ne.employee_id)
WHEN MATCHED
THEN UPDATE SET ne.salary = e.salary
WHEN NOT MATCHED
THEN INSERT VALUES
(ne.employee_id, ne.first_name, ne.last_name, .... ne.salary, ....);

The INSERT clause must include a column list as well as a list of column values.
Nothing is wrong, the statement will execute correctly.
The SET clause is trying to update the source table from the target table. (*)
The UPDATE clause must include the target table name: UPDATE emps SET ....

Correct

33. What would be the result of the following statement: DELETE FROM employees; Mark for Review
(1) Points

All rows in the employees table will be deleted. (*)

Nothing, no data will be changed.
The row with EMPOYEE_ID=100 will be deleted.
The statement will fail because it contains a syntax error.

Correct

34. A PL/SQL block includes the following statement: Mark for Review
SELECT last_name INTO v_last_name (1) Points
FROM employees
WHERE employee_id=100;

What is the value of SQL%FOUND immediately after the SELECT statement is

executed?

False
Error. That attribute does not apply for implicit cursors.
Null
True (*)

35. Which is the correct way to erase one row from a table?
Mark for Review
(1) Points

REMOVE employee_id=100
FROM employees;
DELETE FROM employees
WHERE employee_id=100;
 (*)
TRUNCATE employees
WHERE employee_id=100;
DROP TABLE employees
WHERE employee_id=100;

Correct
Section 3
(Answer all questions in this section)

36. Employee_id 999 does not exist. What will happen when the following code is
executed? Mark for Review
(1) Points
DECLARE
employee_id employees.employee_id%TYPE := 999;
BEGIN
UPDATE employees SET salary = salary * 1.1
WHERE employee_id = employee_id;
END;

An exception is raised because you cannot give a variable the same name as a
table column.
No rows are updated but the block completes successfully.
An exception is raised because the UPDATE statement did not modify any rows.
Every employee row is updated. (*)

Correct

Section 4
(Answer all questions in this section)

37. What type of control structures are repetition statements that enable you to execute Mark for Review
statements in a PLSQL block repeatedly?
(1) Points

CASE expressions
IF statements
Loops (*)
CASE statements

Correct

38. What is the correct form of a simple IF statement? Mark for Review
(1) Points

IF condition;
THEN statement;
END IF;
IF condition
THEN statement
ENDIF;
IF condition THEN statement;
END IF; (*)
IF condition THEN statement;

Correct

39. Examine the following code: Mark for Review

DECLARE (1) Points
a VARCHAR2(6) := NULL;
b VARCHAR2(6) := NULL;
 BEGIN
IF a = b THEN
DBMS_OUTPUT.PUT_LINE('EQUAL');
ELSIF a != b THEN
DBMS_OUTPUT.PUT_LINE('UNEQUAL');
ELSE
DBMS_OUTPUT.PUT_LINE('OTHER');
END IF;
END;

Which word will be displayed?

Nothing will be displayed

OTHER (*)
UNEQUAL
EQUAL

Correct

40. What is the correct name for CASE, LOOP, WHILE, and IF-THEN-ELSE structures ?
Mark for Review
(1) Points

Array structures
Memory structures
Control structures (*)
Cursor structures

Correct
Section 4
(Answer all questions in this section)

41. Examine the following code: Mark for Review

DECLARE (1) Points
v_a BOOLEAN;
v_b BOOLEAN := FALSE;
v_c BOOLEAN ;
BEGIN
v_c := (v_a AND v_b);
-- Line A
....
END;

What is the value of v_c at Line A?

NULL
True
False (*)
Undefined

Correct

42. Examine the following code: Mark for Review

DECLARE (1) Points
v_score NUMBER(3);
v_grade CHAR(1);
BEGIN
v_grade := CASE v_score
-- Line A
....

The CASE expression must convert a numeric score to a letter grade: 90 -> A, 80 ->
B, 70 -> C and so on. What should be coded at Line A?
 WHEN 90 THEN grade := 'A'
WHEN 90 THEN 'A' (*)
WHEN 90 THEN v_grade := 'A';
WHEN 90 THEN = 'A';

Correct

43. Which statement best describes when a FOR loop should be used? Mark for Review
(1) Points

When the number of iterations is known (*)

When the controlling condition must be evaluated at the start of each iteration
When testing the value in a Boolean variable

Correct

44. Which statement best describes when a FOR loop should be used? Mark for Review
(1) Points

When an EXIT WHEN statement must be coded.

When an implicitly declared counter must increase by 1 in each iteration of the
loop. (*)
When the statements inside the loop must execute at least once.
When we want to exit from the loop when a Boolean variable becomes FALSE.

Correct

45. Which of the following blocks produces the same output as this block?
Mark for Review
BEGIN (1) Points
FOR i in 1 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

DECLARE
i PLS_INTEGER := 0;
BEGIN
WHILE i<3 LOOP
i := i + 1;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

(*)
DECLARE
i PLS_INTEGER := 0;
BEGIN
WHILE i<3 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
i := i+ 1;
END;
DECLARE
i PLS_INTEGER := 0;
BEGIN
WHILE i<3 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;

Correct
Section 4
(Answer all questions in this section)
46. You want to calculate and display the multiplication table for "sevens": 7x1=7,
7x2=14, 7x3=21 and so on. Which kind of PL/SQL construct is best for this? Mark for Review
(1) Points

A Boolean variable
A CASE statement
IF ... END IF;
A loop (*)

Correct

47. Which kind of loop is this?

Mark for Review
i := 10; (1) Points
LOOP
i := i + 1;
EXIT WHEN i > 30;
END LOOP;

A nested loop
A basic loop (*)
A WHILE loop
An infinite loop
A FOR loop

Correct

48. What are the three kinds of loops in PL/SQL? Mark for Review
(1) Points

FOR, WHILE, basic (*)

infinite, finite, recursive
ascending, descending, unordered
IF, CASE, LOOP

Correct

49. When the following code is executed, how many lines of output will be displayed? Mark for Review
BEGIN (1) Points
FOR i IN 1..5 LOOP
FOR j IN 1..8 LOOP
DBMS_OUTPUT.PUT_LINE(i || ',' || j);
END LOOP;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

41
14
45 (*)
80

Correct

50. What type of loop statement would you write for Point A? Mark for Review
BEGIN (1) Points
FOR v_outerloop IN 1..3 LOOP
-- Point A
 DBMS_OUTPUT.PUT_LINE('Outer loop is:'||v_outerloop||
' and inner loop is: '||v_innerloop);
END LOOP;
END LOOP;
END;

WHILE v_outerloop<="" span="">

WHILE v_innerloop <=5 LOOP
LOOP
FOR v_innerloop IN 1..5 LOOP (*)

Correct