PLSQL Mid Term Exam Semester 1 - Part 1

1.

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

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

3. A variable must have a value if NOT NULL is specified. True orFalse? (1) Points

True (*)
False

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

5. Evaluate the following declaration. Determine whether or not it islegal.
DECLARE
maxsalary NUMBER(7) = 5000;
(1) Points

Correct.
Not correct. (*)

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

Correct.
Not correct. (*)

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

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

True (*)
False


9. Which of these are PL/SQL data types? (Choose three.) (1) Points (Choose all correct answers)

Scalar (*)
Identifier
Delimiter
Composite (*)
LOB (*)

10. ______ are meant to store large amounts of data. (1) Points

Variables
Scalar data types
LOBs (*)

11. 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 single variable.
It is used to test if errors have occurred.

12. You declare an implicit cursor in the DECLARE section of a PL/SQLblock. True or False? (1) Points

True
False (*)



13. There are no employees in Department 77. What will happen when thefollowing 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 COMMITstatement.

14. Which is the correct way to erase one row from a table? (1) Points

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

15. Delimiters are _____ that have special meaning to the Oracledatabase. (1) Points

identifiers
variables

symbols (*)

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

17. Which statements about lexical units are true? (Choose two.) (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 (*)

18. Examine the following code. At Line A, we want to assign a valueof 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

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

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

20. 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;
Mark for Review
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(var_a);
END;
(1) Points

12 (*)
24
6
Nothing will be displayed


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

22. When an exception occurs within a PL/SQL block, the remainingstatements in the executable section of the block are skipped. True orFalse? (1) Points

True (*)
False

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

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

25. Which PL/SQL block type must return a value? (1) Points

Anonymous
Function (*)
Procedure

26. What is the purpose of using DBMS_OUTPUT.PUT_LINE in a PL/SQLblock? (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

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

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

29. In which part of the PL/SQL block are declarations of variablesdefined? (1) Points
Executable
Exception

Declarative (*)
Definition

30. Which lines of code will correctly display the message "The catsat 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'); (*)

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

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


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

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

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

36. If today's date is 14th June 2007, which statement will correctlyconvert 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') (*)

37. TO_NUMBER, TO_CHAR, and TO_DATE are all examples of: (1) Points

Implicit conversion functions
Explicit conversion functions (*)
Character functions
Operators

38. What is the output when the following program is executed?
set serveroutput onDECLARE
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

39. Which of the following are valid assignment statements? (Choosetwo.) (1) Points (Choose all correct answers)

v_string = 'Hello';
v_string := Hello;
v_number := 17 + 34; (*)
v_string := 'Hello'; (*)
v_date := 28-DEC-06;

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

41. The following code will return the last name of the employee whoseemployee 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 employeesWHERE employee_id = employee_id;

END;
(1) Points
True
False (*)

42. Which of the following is NOT a good guideline for retrieving datain 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.

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

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;

44. 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;(*)

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

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

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 INSERTsSERTs.
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. 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 (*)

48. PL/SQL extends SQL by including all of the following except: (1) Points

variables
conditional statements
reusable program units
constants
nonprocedural constructs (*)


49. A program which specifies a list of operations to be performedsequentially to achieve the desired result can be called: (1) Points

declarative
nondeclarative
procedural (*)

low level

50. SQL is a common access language for many types of databases, including Oracle. True or False? (1) Points

True (*)
False