Test: Semester 1 Midterm Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 1
(Answer all questions in this section)
1.Which of the following statements is true? (Choose two)
Mark for Review
(1) Points
(Choose all correct answers)
PL/SQL is an Oracle proprietary, procedural, fourth-generation programming
language.
SQL is an ANSI-compliant, nonprocedural, fourth-generation programming language.
(*)
PL/SQL is an Oracle proprietary, procedural, third-generation programming
language. (*)
PL/SQL is an ANSI-compliant, procedural programming language.
Incorrect. Refer to Section 1 Lesson 1.
2.The P in PL/SQL stands for:
Mark for Review
(1) Points
Primary
Proprietary
Processing
Procedural (*)
Correct
3.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 more efficient (*)
PL/SQL is harder to learn
PL/SQL is easier to learn and does not require an Oracle database or tool
PL/SQL is easier to learn but less efficient
Correct
4.Which of the following can be done using PL/SQL?
Mark for Review
(1) Points
Create customized reports
Update data (DML)
All of these can be done (*)
Develop Web applications using the Web Application Toolkit
Manage database security
Correct
5.Which keywords must be included in every PL/SQL block? (Choose two.)
Mark for Review
(1) Points
(Choose all correct answers)
DECLARE
EXCEPTION
BEGIN (*)
END; (*)
DBMS_OUTPUT.PUT_LINE
Correct
Section 1
(Answer all questions in this section)
6. Given below are the parts of a PL/SQL block:
1. END; Mark for Review
2. EXCEPTION (1) Points
3. DECLARE
4. BEGIN
Arrange the parts in order.
4,3,2,1
3,4,2,1 (*)
3,2,4,1
2,1,4,3
Correct
7. Which PL/SQL block type must return a value?
Mark for Review
(1) Points
Procedure
Anonymous
Function (*)
Package
Correct
8. Which component of Oracle Application Express is used to enter and run SQL
statements and PL/SQL blocks? Mark for Review
(1) Points
SQL Workshop (*)
Application Builder
Object Browser
Utilities
Correct
Section 2
(Answer all questions in this section)
9. What will be displayed when the following block is executed?
Mark for Review
<<outer>> (1) Points
DECLARE
v_myvar VARCHAR2(10) := 'Hello' ;
BEGIN
<<inner>> DECLARE
v_myvar VARCHAR2(10) := 'World';
BEGIN
v_myvar := v_myvar || ' ' || outer.v_myvar;
END;
DBMS_OUTPUT.PUT_LINE(inner.v_myvar);
END;
HelloWorld
Hello World
World
The code will fail since the inner variable is not within the scope of the outer block.
(*)
Correct
10.Examine the following code. At Line A, we want to assign a value of 25 to the outer
block's variable (V1). What must we do? Mark for Review
(1) Points
DECLARE
v_myvar NUMBER; -- This is V1
BEGIN
DECLARE
v_myvar NUMBER := 8;
BEGIN
-- Line A
END;
END;
Label both blocks and at line A, code:
v_myvar := 25;
It cannot be done because the outer block's v_myvar is in scope but not visible at
Line A.
It cannot be done because the outer block's v_myvar is out of scope at Line A.
Label the outer block and (at Line A) dot-prefix v_myvar with the block label.
(*)
At Line A, code:
v_myvar := 25;
Correct
Section 2
(Answer all questions in this section)
11.What is wrong with this code?
Mark for Review
(1) Points
DECLARE
v_a NUMBER;
BEGIN
v_a := 27;
<<inner_block>>
BEGIN
v_a := 15;
END;
Nothing is wrong, the code will execute successfully.
The outer block has no label.
Variable v_a is out of scope within the inner block and therefore cannot be
referenced.
The inner block has no END; statement. (*)
Correct
12.A function called FORMAT_TODAYS_DATE accepts no parameters and returns today's
date in the format: Month DD, YYYY Mark for Review
(1) Points
The following anonymous block invokes the function:
DECLARE
v_today DATE;
BEGIN
-- invoke the function here
Which of the following statements correctly assigns the date variable v_today to the
value returned by the format_todays_date function?
format_todays_date := v_today('Month DD, YYYY');
v_today := format_todays_date ('Month DD, YYYY');
v_today := TO_DATE(format_todays_date, 'Month DD, YYYY'); (*)
v_today := format_todays_date(v_today);
Correct
13.Is the following variable declaration correct or not?
Mark for Review
DECLARE (1) Points
display_qty CONSTANT NUMBER;
Correct.
Not correct. (*)
Correct
14.Variables can be assigned a value in both the Executable and Declaration sections of a
PL/SQL program. True or False? Mark for Review
(1) Points
True (*)
False
Correct
15.Which of the following should NOT be used as the name of a variable?
Mark for Review
(1) Points
A table column name.
A PL/SQL reserved word.
Neither should be used. (*)
Incorrect. Refer to Section 2 Lesson 4.
Section 2
(Answer all questions in this section)
16.If you are using the %TYPE attribute, you can avoid hard coding the:
Mark for Review
(1) Points
Column name
Data type (*)
Table name
Constraint
Correct
17.Which of these are PL/SQL data types? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
Scalar (*)
Identifier
Composite (*)
Delimiter
LOB (*)
Correct
18.Which of the following are scalar data types? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
Table
Boolean (*)
Date (*)
Character (*)
Array
Correct
19.Which of the following are valid PL/SQL operators? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
Exponential (*)
Arithmetic (*)
Exception
Concatenation (*)
Correct
20.What will happen when the following code is executed?
DECLARE v_new_date DATE; Mark for Review
BEGIN (1) Points
v_new_date := 'Today';
DBMS_OUTPUT.PUT_LINE(v_new_date);
END;
The block will execute and display today's date.
The block will execute and display the word "Today".
The block will fail because the character value "Today" cannot be implicitly
converted to a date. (*)
Correct
Section 2
(Answer all questions in this section)
21.Which of the following are disadvantages of implicit data type conversions? (Choose
two.) Mark for Review
(1) Points
(Choose all correct answers)
The code is harder to read and understand (*)
If Oracle changes the conversion rules in the future, your code may not work any
more (*)
You cannot store alphabetic characters in a variable of data type NUMBER
Oracle cannot implicitly convert a number value to a character string
Correct
22.PL/SQL can convert a VARCHAR2 value containing alphabetic characters to a NUMBER
value. True or False? Mark for Review
(1) Points
True
False (*)
Correct
23.Which of the following are valid identifiers? (Choose two.)
Mark for Review
(1) Points
(Choose all correct answers)
Full Name
completion_%
#hours
v_code (*)
students_street_address (*)
Correct
24.Which of the following are lexical units? (Choose two.)
Mark for Review
(1) Points
(Choose all correct answers)
Identifiers (*)
Data types
Literals (*)
PL/SQL blocks
Correct
25.Which of the following are examples of good programming practice? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
Indent code so that it can be read more easily. (*)
Use table column names as the names of variables.
Develop naming conventions for identifiers and other objects. (*)
Document code with comments. (*)
Use implicit data type conversions.
Correct
ection 2
(Answer all questions in this section)
26.Which of the following are examples of good programming practice? (Choose two.)
Mark for Review
(1) Points
(Choose all correct answers)
Use the %TYPE attribute to declare a variable according to another previously
declared variable or database column. (*)
Use meaningful names for identifiers. (*)
Declare one or more identifiers per line for improved performance.
For clarity, use column names as identifiers.
Correct
Section 3
(Answer all questions in this section)
27.Which rows will be deleted from the EMPLOYEES table when the following code is
executed? Mark for Review
(1) Points
DECLARE
salary employees.salary%TYPE := 12000;
BEGIN
DELETE FROM employees
WHERE salary > salary;
END;
No rows. (*)
All rows whose SALARY column value is equal to 12000.
All rows whose SALARY column value is greater than 12000.
All rows in the table.
Correct
28.A variable is declared as:
Mark for Review
DECLARE (1) Points
v_salary employees.salary%TYPE;
BEGIN
Which of the following is a correct use of the INTO clause?
SELECT salary
FROM employees
INTO v_salary;
SELECT salary
FROM employees
WHERE employee_id=100
INTO v_salary;
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id=100;
(*)
SELECT v_salary
INTO salary
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.What is wrong with the following statement?
DELETE from employees WHERE salary > (SELECT AVG(salary) FROM employees); Mark for Review
(1) Points
You cannot use inequality operators such as "<" and ">" inside a DELETE
statement.
You cannot code a subquery inside a DELETE statement.
Nothing is wrong, the statement will execute correctly. (*)
Correct
Section 3
(Answer all questions in this section)
31.What would be the result of the following statement: DELETE FROM employees;
Mark for Review
(1) Points
The statement will fail because it contains a syntax error.
All rows in the employees table will be deleted. (*)
The row with EMPOYEE_ID=100 will be deleted.
Nothing, no data will be changed.
Correct
32.Examine the following code: BEGIN
INSERT INTO animals VALUES ('aa','aardvarks'); Mark for Review
SAVEPOINT sp_1; (1) Points
INSERT INTO animals VALUES ('bb','big birds');
SAVEPOINT sp_2;
ROLLBACK TO sp_1;
INSERT INTO animals VALUES ('cc','cool cats');
COMMIT;
END;
Which row(s) will be in the ANIMALS table after this block is executed?
aardvaarks and cool cats (*)
aardvaarks, big birds and cool cats
cool cats
big birds and cool cats
Correct
33.Which of the following best describes a database transaction?
Mark for Review
(1) Points
All the DML statements in a single PL/SQL block
A SELECT statement based on a join of two or more database tables
A single SQL statement that updates multiple rows of a table
A related set of SQL DML statements which must be executed either completely or
not at all (*)
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?
True (*)
False
Error. That attribute does not apply for implicit cursors.
Null
Incorrect. Refer to Section 3 Lesson 3.
35.There are no employees in Department 77. What will happen when the following block
is executed? Mark for Review
BEGIN (1) Points
DELETE FROM employees
WHERE department_id=77;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT)
END;
A NULL is displayed.
A zero (0) is displayed. (*)
An exception is raised because the block does not contain a COMMIT statement.
A NO_DATA_FOUND exception is raised.
Correct
Section 3
(Answer all questions in this section)
36.You can use implicit cursor attributes such as SQL%ROWCOUNT directly inside a DML
statement. For example: Mark for Review
(1) Points
INSERT INTO log_table
VALUES (SYSDATE, USER, SQL%ROWCOUNT);
True or False?
True
False (*)
Correct
Section 4
(Answer all questions in this section)
37.How must you end a CASE expression?
Mark for Review
(1) Points
ENDCASE;
END CASE;
ENDIF;
END; (*)
Correct
38.What value will v_answer contain after the following code is executed?
Mark for Review
DECLARE (1) Points
v_age NUMBER:= 18;
v_answer VARCHAR2(10);
BEGIN
v_answer :=
CASE
WHEN v_age < 25 THEN 'Young'
WHEN v_age = 18 THEN 'Exactly 18'
ELSE 'Older'
END;
Null
Exactly 18
Older
Young (*)
Correct
39.Which one of these is NOT a kind of loop?
Mark for Review
(1) Points
Basic loop
WHILE loop
ASCENDING loop (*)
FOR loop
Correct
40.A PL/SQL block contains the following code:
Mark for Review
v_counter := 1; (1) Points
LOOP
EXIT WHEN v_counter = 5;
v_counter := v_counter + 1;
END LOOP;
What is the value of V_COUNTER after the loop is finished?
5 (*)
This is an infinite loop; the loop will never finish.
Incorrect. Refer to Section 4 Lesson 3.
Section 4
(Answer all questions in this section)
41.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 FOR loop
A WHILE loop
An infinite loop
Correct
42.Look at the following block:
Mark for Review
DECLARE (1) Points
v_date DATE := SYSDATE;
BEGIN
WHILE v_date < LAST_DAY(v_date) LOOP
v_date := v_date + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_date);
END;
If today's date is 17th April 2007, what will be displayed when this block executes?
31-Dec-2007
30-Apr-2007 (*)
01-May-2007
17-Apr-2007
Correct
43.You should use a WHILE loop when the number of iterations of the loop is known in
advance. True or False? Mark for Review
(1) Points
True
False (*)
Correct
44.Which statement best describes when a FOR loop should be used?
Mark for Review
(1) Points
When testing the value in a Boolean variable
When the controlling condition must be evaluated at the start of each iteration
When the number of iterations is known (*)
Correct
45.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?
UNEQUAL
EQUAL
Nothing will be displayed
OTHER (*)
Correct
Section 4
(Answer all questions in this section)
46.What is the correct form of a compound IF statement?
Mark for Review
(1) Points
IF condition
THEN statement1
ELSE statement 2;
IF condition
THEN statement1
ELSE statement 2;
END IF;
IF condition THEN statement1;
ELSE statement2;
END IF;
(*)
IF condition;
THEN statement1;
ELSE statement2;
END IF;
Correct
47.Examine the following code:
DECLARE Mark for Review
a BOOLEAN := TRUE; (1) Points
b BOOLEAN := FALSE;
c BOOLEAN := TRUE;
d BOOLEAN := FALSE;
game char(4) := 'lost';
BEGIN
IF ((a AND b) AND (c OR d))
THEN game := 'won';
END IF;
What is the value of GAME at the end of this block?
NULL
lost (*)
won
False
Correct
48.Which one of the following is correct syntax for an IF statement?
Mark for Review
(1) Points
IF condition THEN
statement1;
statement2;
ENDIF;
IF condition THEN
statement1;
AND statement2;
END IF;
IF condition THEN
statement1;
statement2;
END IF;
(*)
IF condition THEN DO
statement1;
statement2;
END IF;
Correct
49.What kinds of loops can be nested?
Mark for Review
(1) Points
All of these. (*)
WHILE loops
FOR loops
BASIC loops
Correct
50.Examine the following code:
BEGIN Mark for Review
FOR i IN 1..5 LOOP (1) Points
FOR j IN 1..8 LOOP
EXIT WHEN j = 7;
DBMS_OUTPUT.PUT_LINE(i || j);
END LOOP;
END LOOP;
END;
How many lines of output will be displayed when this code is executed?
35
40
30 (*)
Correct
