PLSQL Mid Term 1

1. A PL/SQL block includes the following statement:
SELECT last_name INTO v_last_name
FROM employees
WHERE employee_id=100;

What is the value of SQL%ISOPEN immediately after the SELECT statement is executed?
(1) Points

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

2. Assume there are 5 employees in Department 10. What happens when the
following statement is executed?
UPDATE employees
SET 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.

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

4. Given this first section of code:

DECLARE

v_result employees.salary%TYPE;
BEGIN

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

SELECT salary
INTO v_result
FROM employees;

SELECT salary
INTO v_result
FROM employees
WHERE last_name ='Smith';


SELECT salary
INTO v_result
FROM employees
WHERE department_id = 80;

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

5. The following code will return the last name of the employee whose employee id
is equal to 100: True or False?
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;
(1) Points
True
False (*)

6. Which one of these SQL statements can be directly included in a PL/SQL
executable block? (1) Points

SELECT last_name FROM employees WHERE employee_id=100;
DESCRIBE employees;
UPDATE employees SET last_name='Smith'; (*)
DROP TABLE employees;

7. Which of the following is NOT a good guideline for retrieving 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 database columns in the SELECT clause.
THE SELECT statement should fetch exactly one row.

8. Which one of these SQL statements can be directly included in a PL/SQL executable block?
(1) Points

DELETE FROM employees
WHERE department_id=60; (*)

SELECT salary FROM employees
WHERE department_id=60;

CREATE TABLE new_emps (last_name VARCHAR2(10), first_name
VARCHAR2(10));

DROP TABLE locations;

9. What is the output when the following program is executed?

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

10. Single row character functions are valid SQL functions in PL/SQL. True or False? (1) Points
True (*)
False

11. What is wrong with this assignment statement?
myvar := 'To be or not to be';
'That is the question';
(1) Points

An assignment statement must be a single line of code
Nothing is wrong, the statement is fine
An assignment statement must have a single semicolon at the end (*)
"myvar" is not a valid name for a variable
Character literals should not be enclosed in quotes

12. 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;

13. The DECODE function is available in PL/SQL procedural statements. True or False? (1) Points
True
False (*)

14. When you use a function to convert data types in a PL/SQL program, it is called
______ conversion. (1) Points
Explicit (*)
Implicit
TO_CHAR

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

16. 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

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

18. Examine the following code. What is the final value of V_MYVAR ?
DECLARE
v_myvar NUMBER;

BEGIN
v_myvar := 1 + 2 * 3;
v_myvar := v_myvar * 2;

END;
(1) Points

81
49
14 (*)
18

19. Reserved words can be used as identifiers. True or False? (1) Points
True
False (*)

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

21. Which of the following are valid identifiers? (Choose two.) (1) Points (Choose all correct answers)
yesterday (*)
yesterday's date
number_of_students_in_the_class
v$testresult (*)
#students

22. Examine the following code. At Line A, we want to assign a value of 22 to the outer block's variable v_myvar. What code should we write at Line A?
<<outer_block>>
DECLARE
v_myvar NUMBER;


BEGIN
<<inner_block>>
DECLARE
<<inner_block>>
DECLARE

v_myvar NUMBER := 15;
BEGIN
--Line A
END;
END;
(1) Points
outer_block.v_myvar := 22; (*)
v_myvar := 22;
<<outer_block>>.v_myvar := 22;
v_myvar(outer_block) := 22;
We cannot reference the outer block's variable because both variables have the same
name

23. An exception occurs within the inner block of two nested blocks. The inner block does not have an EXCEPTION section. What always happens? (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

24. What will be displayed when the following code is executed?
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

25. In the following code, Line A causes an exception. What value will be displayed
when the code is executed?
DECLARE
outer_var VARCHAR2(50) := 'My';

BEGIN
outer_var := outer_var || ' name';
DECLARE

inner_var NUMBER;

BEGIN
inner_var := 'Mehmet'; --Line A
outer_var := outer_var || ' is';


END;
outer_var := outer_var || ' Zeynep';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(outer_var);
END;
(1) Points

My
My name (*)
My name is
My name is Zeynep

26. What will be displayed when the following code is executed?
DECLARE
x VARCHAR2(6) := 'Chang';
BEGIN


DECLARE

x VARCHAR2(12) := 'Susan';

BEGIN

x := x || x;

END;

DBMS_OUTPUT.PUT_LINE(x);
END;
(1) Points

Susan
Chang (*)
ChangChang
SusanChang
The code will fail with an error

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

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

29. Variables can be used in the following ways in a PL/SQL block. (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. To comment code.

30. When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False? (1) Points
True (*)
False

31. Identify which of the following assignment statements are valid. (Choose three.) (1) Points (Choose all correct answers)
v_last_name := Chandra;
v_blackout_date := '31-DEC-2006'; (*)
v_population := 333444; (*)
v_music_type := 'ROCK'; (*)

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

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

Scalar
Composite (*)
LOB

34. ______ are meant to store large amounts of data. (1) Points
Variables
Scalar data types
LOBs (*)

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

36. Which keywords must be included in every PL/SQL block? (Choose two.) (1) Points
(Choose all correct answers)
DECLARE
END; (*)
EXCEPTION
BEGIN (*)
DBMS_OUTPUT.PUT_LINE

37. Errors are handled in the Exception part of the PL/SQL block. True or False? (1) Points

True (*)
False

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

39. Given below are the parts of a PL/SQL block:
1. END;
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.
(1) Points

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

40. Which lines of code will correctly display the message "The cat sat on the mat"? (Choose two.) (1) Points
(Choose all correct answers)
DBMS_OUTPUT.PUT_LINE('The cat sat on the mat'); (*)
DBMS_OUTPUT.PUT_LINE(The cat sat on the mat);
DBMS_OUTPUT.PUT_LINE('The cat' || 'sat on the mat');
DBMS_OUTPUT.PUT_LINE('The cat sat ' || 'on the mat'); (*)

41. What kind of block is defined by the following PL/SQL code?
BEGIN
DBMS_OUTPUT.PUT_LINE('My first quiz');
END;
(1) Points
procedure
subroutine
function
anonymous (*)

42. Which of the following tools can NOT be used to develop and test PL/SQL code? (1) Points
Oracle Jdeveloper
Oracle Application Express
Oracle JSQL (*)
Oracle iSQL*Plus

43. 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;

44. 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

45. A variable must have a value if NOT NULL is specified. True or False? (1) Points
True (*)
False

46. 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.


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

48. Which of the following statements about PL/SQL and SQL is 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. (*)

49. A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called: (1) Points
declarative
nondeclarative
procedural (*)
low level

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