PLSQL Mid Term Exam Semester 1

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

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

3.
1. Null
2. False
3. True
4. 0
Which of the above can be assigned to a Boolean variable?

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

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

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.

5. Which of the following best describes a databasetransaction?
All the DML statements in a single PL/SQL block
A related set of SQL DML statements which must be executed eithercompletely 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

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

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

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

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

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

10. In the following code, Line A causes an exception. Whatvalue 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;

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

11. When nested blocks are used, which blocks can or must be labeled?
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. (*)

12. What will be displayed when the following code isexecuted?
DECLARE
x VARCHAR2(6) := 'Chang';
BEGIN
DECLARE
x VARCHAR2(12) := 'Susan';
BEGIN
x := x || x;
END;
DBMS_OUTPUT.PUT_LINE(x);
END;

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

13. Examine the following code. At Line A, we want to assigna value of 22 to the outer block's variable v_myvar. What code should wewrite at Line A?
<<outer_block>>
DECLARE
v_myvar NUMBER;
BEGIN
<<inner_block>>
DECLARE
v_myvar NUMBER := 15;
BEGIN
--Line A
END;
END;

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

14. Which one of these SQL statements can be directlyincluded in a PL/SQL executable block?
DELETE FROM employeesWHERE department_id=60;(*)
SELECT salary FROM employeesWHERE department_id=60;
CREATE TABLE new_emps (last_name VARCHAR2(10), first_name VARCHAR2(10));
DROP TABLE locations;

15. Which of the following is NOT a good guideline forretrieving data in PL/SQL?
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.

16. A variable is declared as:
DECLARE
v_salary employees.salary%TYPE;
BEGIN
Which of the following is a correct use of the INTO clause?
SELECT salaryINTO v_salaryFROM employeesWHERE employee_id=100;(*)
SELECT v_salaryINTO salaryFROM employeesWHERE employee_id=100;
SELECT salaryFROM employeesINTO v_salary;
SELECT salaryFROM employeesWHERE employee_id=100INTO v_salary;

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

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.

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

19. Evaluate the following declaration. Determine whether ornot it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;

Correct.
Not correct. (*)

20. Assignment statements can continue over several lines inPL/SQL. True or False?
True (*)
False

21. When a variable is defined using the CONSTANT keyword, the value ofthe variable cannot change. True or False?
True (*)
False

22. Variables can be used in the following ways in a PL/SQLblock. (Choose two.) (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.

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

24. PL/SQL is an Oracle proprietary, procedural, 4GLprogramming language. True or False?
True
False (*)

25. A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called:

declarative
nondeclarative
procedural (*)
low level

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

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

28. What is the purpose of using DBMS_OUTPUT.PUT_LINE in aPL/SQL block?
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

29. Which of the following tools can NOT be used to developand test PL/SQL code?
Oracle Jdeveloper
Oracle Application Express
Oracle JSQL (*)
Oracle iSQL*Plus

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

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

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

32. Which lines of code will correctly display the message"The cat sat on the mat"? (Choose two.)
(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'); (*)

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

34. Which component of Oracle Application Express is used toenter and run SQL statements and PL/SQL blocks?
Application Builder
SQL Workshop (*)
Utilities
Object Browser
Correct Correct

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

36. What is the data type of the variable V_DEPT_TABLE in the followingdeclaration?
DECLARE
TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BYPLS_INTEGER; v_dept_table dept_table_type; ...

Scalar
Composite (*)
LOB

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

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

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

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

There are only 28 days in February
Oracle cannot implicitly convert a character string to a date, even
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

40. If today's date is 14th June 2007, which statement willcorrectly convert today's date to the value: June 14, 2007 ?
TO_CHAR(sysdate)
TO_DATE(sysdate)
TO_DATE(sysdate,'Month DD, YYYY')
TO_CHAR(sysdate, 'Month DD, YYYY') (*)

Incorrect Incorrect. Refer to Section 2.
Previous Page 8 of 10 Next Summary

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

42. What is wrong with this assignment statement?
myvar :=
'To be or not to be';
'That is the question';

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

43. Examine the following code. What is the final value ofV_MYVAR ?
DECLARE
v_myvar NUMBER;
BEGIN
v_myvar := 1 + 2 * 3;
v_myvar := v_myvar * 2;
END;

81
49
14 (*)
18

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

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

True (*)
False

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

'300'
300 (*)
NULL

47. TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:

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

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

All employees get a 10% salary increase. (*)
No rows are modified because you did not specify "WHEREdepartment_id=10"
A TOO_MANY_ROWS exception is raised.
An error message is displayed because you must use the INTO clauseto hold the new salary.

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

50. A PL/SQL block includes the following statement:
SELECT last_name INTO v_last_name
FROM employeesWHERE employee_id=100;
What is the value of SQL%ISOPEN immediately after the SELECT statement isexecuted?
True
False (*)
Null
Error. That attribute does not apply for implicit cursors.