Semester 1 Midterm Exam
Which of the following can be done using PL/SQL?
Manage
database security
All of
these can be done (*)
Develop
Web applications using the Web Application Toolkit
Create
customized reports
Update
data (DML)
You can create a Web site
application written entirely in PL/SQL. True or False?
True
False
(*)
How can you display results to
check that a PL/SQL block is working correctly?
Use
DBMS_OUTPUT.PUT_LINE (*)
You
don't need to do anything, the results will display automatically.
Use an
Exception section
Write a
C or Java program to display the results
Which of the following is NOT a
PL/SQL programming environment?
SQL*Plus
SQL
Workshop in Application Express
gSQL*Plus
(*)
Oracle
jDeveloper
Which component of Oracle
Application Express is used to enter and run SQL statements and PL/SQL blocks?
SQL
Workshop (*)
Application
Builder
Utilities
Object
Browser
Which lines of code will correctly display the message
"Hello World" ? (Choose two.)
DBMS_OUTPUT.PUT_LINE('Hello'
|| 'World');
DBMS_OUTPUT('Hello
World');
DBMS_OUTPUT.PUT_LINE('Hello
World'); (*)
DBMS_OUTPUT.PUT_LINE('Hello'
|| ' ' || 'World'); (*)
PL/SQL extends SQL by including
all of the following except:
constants
reusable
program units
nonprocedural
constructs (*)
conditional
statements
variables
Which of the following
statements about PL/SQL and SQL is true?
PL/SQL
allows basic program logic and control flow to be combined with SQL statements.
(*)
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.
A variable must have a value if
NOT NULL is specified. True or False?
True
(*)
False
Which of the following variable
declarations does NOT use a number data type?
v_count
PLS_INTEGER := 0;
v_count
BINARY_INTEGER;
v_median_age
NUMBER(6,2);
v_students
LONG; (*)
Which of the following are examples of good programming
practice? (Choose two.)
Declare
one or more identifiers per line for improved performance.
Use
meaningful names for identifiers. (*)
Use the
%TYPE attribute to declare a variable according to another previously declared
variable or database column. (*)
For
clarity, use column names as identifiers.
Which good programming practice
guideline would make this code easier to read?
DECLARE
v_sal NUMBER(8,2);
BEGIN
SELECT salary INTO v_sal
FROM employees WHERE employee_id = 100;
UPDATE employees
SET salary = v_sal;
END;
Declaring
variables using %TYPE
Indenting
each level of code (*)
Using a
consistent naming convention for variables
Avoiding
implicit data type conversions
A Scalar data type holds a(n)
____ value.
Large
Single
(*)
image
Multi
Which of the following are scalar
data types? (Choose three.)
Boolean
(*)
Table
Array
Date
(*)
Character
(*)
PL/SQL can convert a VARCHAR2
value containing alphabetic characters to a NUMBER value. True or False?
True
False
(*)
The LENGTH and ROUND functions can be used in PL/SQL
statements. True or False?
True
(*)
False
What is wrong with this
assignment statement?
myvar := 'To
be or not to be';
'That is the question';
An
assignment statement must have a single semicolon at the end (*)
An
assignment statement must be a single line of code
"myvar"
is not a valid name for a variable
Nothing
is wrong, the statement is fine
Character
literals should not be enclosed in quotes
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
NULL
NOT
FALSE
NOT
TRUE
Constants must be initialized.
True or False?
True
(*)
False
Variables can be used in the
following ways in a PL/SQL block. (Choose two.)
To
rename tables and columns.
To
comment code.
To
store data values. (*)
To
refer to a single data value several times. (*)
Variables may be reused. True or False?
True
(*)
False
Which of the following is a valid
naming convention for an identifier? (Choose two.)
Can
include letters or numbers (*)
Can
start with a number or special character
Can be
over 30 characters
Cannot
contain a reserved word (*)
Reserved words can be used as
identifiers. True or False?
True
False
(*)
If a variable definition is not
found in an inner block where it is being referenced, where does it look for
it?
This
will result in an error.
It
looks upward in the parent blocks. (*)
It
downward in any other inner blocks.
Two variables with the same name
can be declared in an outer block and an inner block. True or False
True
(*)
False
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;
8
Nothing,
the block will fail with an error (*)
20
12
VarB
Which one of these SQL statements
can be directly included in a PL/SQL executable block?
SELECT
salary FROM employees
WHERE department_id=60;
DROP
TABLE locations;
DELETE
FROM employees
WHERE department_id=60;
(*)
CREATE
TABLE new_emps (last_name VARCHAR2(10), first_name VARCHAR2(10));
Given this first section of code:
DECLARE
v_result
employees.salary%TYPE;
BEGIN
Which statement will always return exactly one value?
SELECT
salary
INTO v_result
FROM employees
WHERE department_id = 80;
SELECT
SUM(salary)
INTO v_result
FROM employees;
(*)
SELECT
salary
INTO v_result
FROM employees;
SELECT
salary
INTO v_result
FROM employees
WHERE last_name ='Smith';
Look at this PL/SQL block:
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees
WHERE salary > 50000;
END;
No employees earn more than $50000. Which of the following
statements are true?
The
SELECT will fail because it does NOT return exactly one row.
The
SELECT will return value 0 into V_COUNT. (*)
The
block will fail because variable V_SALARY was not declared.
The
block will fail because no results are displayed to the user.
The
SELECT returns exactly one row. (*)
In a PL/SQL block, where can you
code a COMMIT statement?
In the
Executable and/or the Exception sections. (*)
Only
the Executable section.
In any
section of the block: Declaration, Executable, or Exception.
Nowhere;
the COMMIT statement must be outside the block.
How many INSERTs can you have in one transaction?
As many
as you want until you do a COMMIT or ROLLBACK. (*)
As many
as you want until a different DML statement (UPDATE, DELETE or MERGE) is
executed.
As many
as you can execute before the database does an AUTOSAVE.
One
You can use implicit cursor
attributes such as SQL%ROWCOUNT directly inside a DML statement. For example:
INSERT INTO log_table
VALUES (SYSDATE,
USER, SQL%ROWCOUNT);
True or False?
True
False
(*)
There are no employees in
Department 77. What will happen when the following block is executed?
BEGIN
DELETE FROM employees
WHERE department_id=77;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT)
END;
An
exception is raised because the block does not contain a COMMIT statement.
A NULL
is displayed.
A zero
(0) is displayed. (*)
A
NO_DATA_FOUND exception is raised.
Which is the correct way to erase
one row from a table?
DELETE
FROM employees
WHERE employee_id=100;
(*)
REMOVE
employee_id=100
FROM employees;
TRUNCATE
employees
WHERE employee_id=100;
DROP
TABLE employees
WHERE employee_id=100;
To modify an existing row in a
table, you can use the ________ statement.
ALTER
INSERT
MODIFY
UPDATE
(*)
What is wrong with the following statement?
MERGE INTO emps e
USING new_emps ne
ON (e.employee_id =
ne.employee_id)
WHEN MATCHED
THEN UPDATE
SET ne.salary =
e.salary
WHEN NOT MATCHED
THEN INSERT VALUES
(ne.employee_id,
ne.first_name, ne.last_name, .... ne.salary, ....);
The
INSERT clause must include a column list as well as a list of column values.
The
UPDATE clause must include the target table name: UPDATE emps SET ....
Nothing
is wrong, the statement will execute correctly.
The SET
clause is trying to update the source table from the target table. (*)
What clause will leave the outer
loop at Point A?
DECLARE
i INTEGER := 0;
BEGIN
<< i_loop
>>
WHILE i <= 10
LOOP
i := i+1;
<< j_loop
>>
FOR j IN 1..5
LOOP
_______ WHEN i =
j*2; -- Point A
DBMS_OUTPUT.PUT_LINE(i || j);
END LOOP;
END LOOP;
END;
EXIT
outerloop
EXIT << outerloop>>
EXIT
j_loop
EXIT
i_loop (*)
Examine the following code:
DECLARE
v_outer_count
NUMBER := 1;
v_inner_count
NUMBER := 1;
BEGIN
LOOP
LOOP
v_inner_count := v_inner_count + 1;
EXIT WHEN
v_inner_count > 5; -- Line A
END LOOP;
v_outer_count
:= v_outer_count + 1;
EXIT WHEN v_outer_count > 3;
END LOOP;
END;
What happens at Line A when the value of V_INNER_COUNT
equals 6?
Both
loops are exited and the block's execution is terminated.
An
error condition is returned.
The
outer loop is exited but the inner loop continues execution.
The
inner loop is exited but the outer loop continues execution. (*)
You need to execute a set of
statements 10 times, increasing a counter by 1 each time. Which of the
following PL/SQL constructs can do this? (Choose three)
IF ...
THEN ... ELSE
CASE
... WHEN ... THEN
A basic
loop (*)
A FOR
loop (*)
A WHILE
loop (*)
We want to execute one of three
statements depending on whether the value in V_VAR is 10, 20, or some other
value. What should be coded at Line A?
IF v_var = 10 THEN
statement1;
-- Line A
statement2;
ELSE
statement3;
END IF;
ELSIF
v_var = 20 THEN (*)
ELSIF
v_var = 20
IF
v_var = 20 THEN
ELSE IF
v_var = 20 THEN
What is wrong with the following trivial IF statement:
IF (v_job='President')
THEN v_salary := 10000;
The
condition should be coded: IF (v_job := 'President')
IF and
THEN must be on the same line: IF (v_job='President') THEN ...
END IF;
is missing (*)
ELSE is
missing
What is the correct name for
CASE, LOOP, WHILE, and IF-THEN-ELSE structures ?
Array
structures
Control
structures (*)
Memory
structures
Cursor
structures
You should use a WHILE loop when
the number of iterations of the loop is known in advance. True or False?
True
False
(*)
In a WHILE loop, the controlling
condition is checked at the start of each iteration. True or False?
True
(*)
False
In a FOR loop, an explicitly
declared counter is automatically incremented by 1 for each iteration of the
loop. True or False?
True
False
(*)
Which
kind of loop is this?
v_count := 1;
LOOP
v_count := v_count
+ 1;
EXIT WHEN i >
20;
END LOOP;
IF-THEN
loop
WHILE
loop
Basic
loop (*)
CASE
loop
FOR
loop
Examine the following block:
DECLARE
v_counter
PLS_INTEGER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(v_counter);
v_counter :=
v_counter + 1;
EXIT WHEN
v_counter = 5;
END LOOP;
END;
What is the last value of V_COUNTER that is displayed?
5
4 (*)
6
This is
an infinite loop; the loop will never finish.
A PL/SQL block contains the
following code:
v_counter := 1;
LOOP
EXIT WHEN
v_counter=5;
END LOOP;
v_counter := v_counter + 1;
What is the value of V_COUNTER after the loop is finished?
6
1
This is
an infinite loop; the loop will never finish. (*)
5
What will be displayed when the
following block is executed?
DECLARE
v_age1 NUMBER(3);
v_age2 NUMBER(3);
v_message
VARCHAR2(20);
BEGIN
CASE
WHEN v_age1 =
v_age2 THEN v_message := 'Equal';
WHEN v_age1
<> v_age2 THEN v_message := 'Unequal';
ELSE v_message :=
'Undefined';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_message);
END;
Equal
Unequal
Undefined
(*)
Nothing
will be displayed because V_MESSAGE is set to NULL.
Examine the following code:
DECLARE
v_score NUMBER(3);
v_grade CHAR(1);
BEGIN
v_grade := CASE
v_score
-- Line A
....
The CASE expression must convert a numeric score to a letter
grade: 90 -> A, 80 -> B, 70 -> C and so on. What should be coded at
Line A?
WHEN 90
THEN v_grade := 'A';
WHEN 90
THEN = 'A';
WHEN 90
THEN grade := 'A'
WHEN 90
THEN 'A' (*)
No comments:
Post a Comment