PLSQL 2

1. Which of the following are valid identifiers? (Choose two.) (1) Points
(Choose all correct answers)
Full Name
students_street_address (*)
v_code (*)
#hours
completion_%

2. Which of the following are PL/SQL lexical units? (Choosetwo.) (1) Points
(Choose all correct answers)
Identifiers (*)
Table Columns
Reserved Words (*)
Anonymous Blocks
SQL Workshop

3. Which statements about lexical units are true? (Choosetwo.) (1) Points
(Choose all correct answers)

They are named objects stored in the database
They are the building blocks of every PL/SQL program (*)
They are optional but can make a PL/SQL block execute faster
They are sequences of characters including letters, digits, tabs,
returns and symbols (*)

4. Which of the following statements about PL/SQL and SQLis true? (1) Points
PL/SQL and SQL are both ANSI-compliant.
PL/SQL and SQL can be used with many types of databases, including Oracle.
PL/SQL and SQL are both Oracle proprietary programming languages.
PL/SQL allows basic program logic and control flow to be combined with SQL statements. (*)

5. SQL is a common access language for many types ofdatabases, including Oracle. True or False?
(1) Points
True (*)
False

 6. The P in PL/SQL stands for: (1) Points
Processing
Procedural (*)
Primary
Proprietary

7. When a variable is defined using the CONSTANT keyword, the value of the variable cannot change. True or False? (1) Points
True (*)
False

8. Variables can be assigned a value in both the Executableand Declaration sections of a PL/SQL program. True or False? (1) Points
True (*)
False

9. Assignment statements can continue over several lines in PL/SQL. True or False? (1) Points
True (*)

False

10. Variables can be used in the following ways in a PL/SQLblock. (Choose two.) (1) Points
(Choose all correct answers)
To store data values. (*)
To rename tables and columns.
To refer to a single data value several times. (*)
To comment code.

11. Is the following variable declaration correct or not ?
DECLARE
display_qty CONSTANT NUMBER;
(1) Points

Correct.
Not correct. (*)

12. There are no employees in Department 77. What willhappen when the following block is executed?
BEGIN
DELETE FROM employeesWHERE department_id=77;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT)
END;
(1) Points


A NO_DATA_FOUND exception is raised.
A NULL is displayed.
A zero (0) is displayed. (*)
An exception is raised because the block does not contain a COMMIT statement.

13. You declare an implicit cursor in the DECLARE section ofa PL/SQL block. True or False? (1) Points
True
False (*)

14. Assume there are 5 employees in Department 10. Whathappens when the following statement is executed?
UPDATE employeesSET salary=salary*1.1;
(1) Points

All employees get a 10% salary increase. (*)
No rows are modified because you did not specify "WHERE
department_id=10"

A TOO_MANY_ROWS exception is raised.

An error message is displayed because you must use the INTO clause to hold the new salary.

15. Which of the following should NOT be used as the name ofa variable? (1) Points
A table name.
A table column name. (*)
The database name.

16. Which of the following declarations is invalid? (1) Points
v_count PLS_INTEGER:=0;
college_name VARCHAR2(20):='Harvard';
v_pages CONSTANT NUMBER; (*)
v_start_date DATE := sysdate+1;

17. 1. Null
2. False
3. True
4. 0
Which of the above can be assigned to a Boolean variable? (1) Points
2 and 3
2, 3 and 4
1, 2 and 3 (*)

1, 2, 3 and 4

18. What will be displayed when the following code isexecuted?
DECLARE
varA NUMBER := 12;
BEGIN
DECLARE
varB NUMBER := 8;
BEGIN

varA := varA + varB;
END;
DBMS_OUTPUT.PUT_LINE(varB);


END;
(1) Points

8
12
Nothing, the block will fail with an error (*)
20
VarB

19. When an exception occurs within a PL/SQL block, theremaining statements in the executable section of the block are skipped.
True or False? (1) Points
True (*)
False

20. When nested blocks are used, which blocks can or must belabeled? (1) Points
The inner block must be labeled, the outer block can be labeled.
Both blocks must be labeled
Nested blocks cannot be labeled
The outer block must be labeled if it is to be referred to in the inner block. (*)

21. Examine the following code. Line A causes an exception. What willbe displayed when the block is executed?

DECLARE
var_a NUMBER := 6;
var_b DATE;

BEGIN
var_a := var_a * 2;
var_b := '28 December 2006'; --Line A
var_a := var_a * 2;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(var_a);
END;
(1) Points
12 (*)
24
6
Nothing will be displayed

22. An exception occurs within the inner block of two nested blocks. The inner block does not have an EXCEPTION section. What alwayshappens? (1) Points
Both blocks fail and an error message is displayed by the calling environment
The exception is propagated to the outer block (*)
Oracle automatically tries to re-execute the inner block
The user's database session is automatically disconnected

23. A movie is an example of which category of data type? (1) Points
Scalar
Composite
Reference
LOB (*)

24. Which of these are PL/SQL data types? (Choose three.) (1) Points
(Choose all correct answers)
Scalar (*)
Identifier
Delimiter
Composite (*)
LOB (*)

25. Which statement most closely describes "data type"? (1) Points
It is the value of a variable.
It specifies a storage format, constraints, and a valid range of
values for a variable. (*)
It allows different kinds of data to be stored in a singlevariable.
It is used to test if errors have occurred.

26. Which of the following are disadvantages of implicit data typeconversions? (Choose two.) (1) Points
(Choose all correct answers)
The code is harder to read and understand (*)
You cannot store alphabetic characters in a variable of data typeNUMBER
If Oracle changes the conversion rules in the future, your code maynot work any more (*)
Oracle cannot implicitly convert a number value to a characterstring
Incorrect Incorrect. Refer to Section 2.


27. Which of the following are valid assignment statements? (Choose two.) (1) Points
(Choose all correct answers)
v_string = 'Hello';
v_string := Hello;
v_number := 17 + 34; (*)
v_string := 'Hello'; (*)
v_date := 28-DEC-06;

28. The DECODE function is available in PL/SQL proceduralstatements. True or False? (1) Points
True
False (*)

29. The implicit data type conversion at Point A may notwork correctly. Why not?
DECLARE
v_mydate DATE;
BEGIN
V_MYDATE := '29-Feb-04'; --Point A
END;
(1) Points

There are only 28 days in February

Oracle cannot implicitly convert a character string to a date, evenif the string contains a valid date value if the string contains a valid date value
If the database language is not English, 'Feb' has no meaning. (*)
V_MYDATE has been entered in uppercase

30. PL/SQL can convert a VARCHAR2 value containingalphabetic characters to a NUMBER value. True or False? (1) Points
True
False (*)

31. Examine the following code. What is the final value of V_MYBOOL ?
DECLARE
v_mynumber NUMBER;
v_mybool BOOLEAN ;

BEGIN
v_mynumber := 6;
v_mybool := (v_mynumber BETWEEN 10 AND 20);
v_mybool := NOT (v_mybool);

END;
(1) Points

True (*)
False

32. What is the output when the following program isexecuted?

set serveroutput on

DECLARE
a VARCHAR2(10) := '333';
b VARCHAR2(10) := '444';
c PLS_INTEGER;
d VARCHAR2(10);

BEGIN
c := TO_NUMBER(a) + TO_NUMBER(b);
d := a || b;
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(d);

END;
(1) Points

Nothing. The code will result in an error.
c=777 and d=333444 (*)
c=777 and d=777
c=333444 and d=777

33. TO_NUMBER, TO_CHAR, and TO_DATE are all examples of: (1) Points
Implicit conversion functions
Explicit conversion functions (*)
Character functions
Operators

34. If today's date is 14th June 2007, which statement willcorrectly convert today's date to the value: June 14, 2007 ? (1) Points

TO_CHAR(sysdate)
TO_DATE(sysdate)
TO_DATE(sysdate,'Month DD, YYYY')
TO_CHAR(sysdate, 'Month DD, YYYY') (*)

35. Examine the following code:
1 DECLARE
2 x NUMBER;
3 BEGIN
4 x:= '300';
5 END;

After line 4, what is the value of x?
(1) Points

'300'
300 (*)
NULL

36. How many DML statements can be included in a single transaction? (1) Points
Only one
None. A transaction cannot include DML statements.
A maximum of four DML statements

As many as needed (*)

37. The following anonymous block of code is run:
BEGIN
INSERT INTO countries (id, name)
VALUES ('XA', 'Xanadu');
INSERT INTO countries (id, name)
VALUES ('NV','Neverland');
COMMIT;
COMMIT;
ROLLBACK;
END;

What happens when the block of code finishes? (1) Points

You have nothing new; the last ROLLBACK undid the INSERTs.
You have the rows added twice; there are four new rows.
You have the two new rows added. (*)
You get an error; you cannot COMMIT twice in a row.

38. Which of the following is NOT a good guideline forretrieving data in PL/SQL? (1) Points

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


39. Which rows will be deleted from the EMPLOYEES table when the following code is executed?
DECLARE
salary employees.salary%TYPE := 12000;

BEGIN
DELETE FROM employeesWHERE salary > salary;

END;
(1) Points

All rows whose SALARY column value is greater than 12000.
All rows in the table.
No rows. (*)
All rows whose SALARY column value is equal to 12000.

40. Which one of these SQL statements can be directlyincluded in a PL/SQL executable block? (1) Points
SELECT last_name FROM employeesWHERE employee_id=100;
DESCRIBE employees;
UPDATE employeesSET last_name='Smith'; (*)
DROP TABLE employees;


41. Given this first section of code:
DECLARE

v_result employees.salary%TYPE;
BEGIN

Which statement will always return exactly one value?
(1) Points

SELECT salaryINTO v_result
FROM employees;

SELECT salaryINTO v_result
FROM employeesWHERE last_name ='Smith';

SELECT salaryINTO v_result
FROM employeesWHERE department_id = 80;

SELECT SUM(salary)
INTO v_result
FROM employees; (*)

42. A variable is declared as:
DECLARE
v_holdit employees.last_name%TYPE;
BEGIN ...

Which of the following is a correct use of the INTO clause?
(1) Points

SELECT *


INTO v_holdit
FROM employees;
NTO v_holdit
FROM employees;

SELECT last_name
INTO v_holdit
FROM employees;

SELECT last_name
INTO v_holdit
FROM employeesWHERE employee_id=100; (*)

SELECT salaryINTO v_holdit
FROM employeesWHERE employee_id=100;

43. Errors are handled in the Exception part of the PL/SQLblock. True or False? (1) Points
True (*)
False

44. In which part of the PL/SQL block are declarations ofvariables defined? (1) Points
Executable
Exception
Declarative (*)
Definition

45. What kind of block is defined by the following PL/SQLcode?
BEGIN
DBMS_OUTPUT.PUT_LINE('My first quiz');
END;
(1) Points

procedure
subroutine
function
anonymous (*)

46. Every PL/SQL anonymous block must start withthe keyword DECLARE. True or False? (1) Points
True
False (*)

47. What is the purpose of using DBMS_OUTPUT.PUT_LINE in aPL/SQL block? (1) Points
To perform conditional tests
To allow a set of statements to be executed repeatedly

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

48. Which statements are optional in a PL/SQL block? (Choosetwo.) (1) Points
(Choose all correct answers)
DECLARE (*)
BEGIN
EXCEPTION (*)
END;

49. Which PL/SQL block type must return a value? (1) Points
Anonymous
Function (*)
Procedure

50. Which of the following tools can NOT be used to developand test PL/SQL code? (1) Points
Oracle Jdeveloper

Oracle Application Express Oracle JSQL (*)
Oracle iSQL*Plus Express Oracle JSQL (*)
Oracle iSQL*Plus