Midterms - Database

Question 1

The ______ clause is used in DML statement to specify variable(s) that will hold the value(s) that SQL returns from the SELECT clause.

Answer 1

INTO

Question 2

The use of INTO clause in PL/SQL select statement is to specify the name of ________.

Answer 2

VARIABLE

Question 3

The INTO clause occurs between the SELECT and _______ clauses.

Answer 3

FROM

Question 4

The DML statement: INSERT, DELETE, ________ make changes to the database.

Answer 4

UPDATE

Question 5

Which SQL statements cannot be used directly in PL/SQL?
Group of answer choices

DDL, DCL

DML

TCL

DML, TCL

Answer 5

DDL, DCL

Question 6

What could be the possible error of the given code below?

DECLARE

v_sal_increase employees.salary%TYPE := 800;

BEGIN

UPDATE copy_emp

SET salary = salary + v_sal_increase

WHERE job_id = ‘ST_CLERK’;

END;

Group of answer choices

WHERE section

Variable declaration

No error

SET section

Answer 6

No error

Question 7

What could be the possible error of the given code below?

DECLARE

v_sal_increase employees.salary%TYPE = 800;

BEGIN

UPDATE copy_emp

SET salary = salary + v_sal_increase

WHERE job_id = ‘ST_CLERK’;

END;

Group of answer choices

SET section

No error

WHERE section

Variable declaration

Answer 7

Variable declaration

Question 8

The memory area allocated by Oracle server to store the SQL statement and the data that it uses in known as:

Answer 8

Cursor

Question 9

The use of CASE statement to test conditions such as <, >, >=, <= is know as case expression.

Answer 9

False

Question 10

Applying the logical operator NOT to a null yields FALSE.

Answer 10

False

Question 11

The type of loop where the statement inside the loop must execute at least once.

Answer 11

BASIC

Question 12

In BASIC and WHILE loop, the iteration will terminate when the counter is > than the upper bound.
Group of answer choices

True

False

Answer 12

FALSE

Question 13

In BASIC and WHILE loop, initialization of counter variable is necessary.

Answer 13

TRUE

Question 14

The ______ is a Boolean attribute that evaluates to TRUE if the most recent SQL statement did not return even one row.

Answer 14

SQL%NOTFOUND

Question 15

The DDL and _______ SQL statements cannot be used directly in PL/SQL.

Answer 15

DCL

Question 16

The _______cursor are automatically defined by Oracle.

Answer 16

IMPLICIT

Question 17

What could be the possible error of the given code below?

DECLARE

v_salary employees.salary%TYPE;

BEGIN

SELECT salary INTO v_salary

FROM employees;

DBMS_OUTPUT.PUT_LINE(‘ Salary is : ‘ || v_salary);

END;

Answer 17

Query returns more than 1 row

Question 18

The _______ statement selects rows from one table to update and/or insert into another table.
Group of answer choices

DML

COMBINE

MERGE

SELECT, INSERT, UPDATE

Answer 18

MERGE

Question 19

What is missing in the given code below?

DECLARE

v_salary employees.salary%TYPE;

BEGIN

SELECT salary INTO ________

FROM employees where employee_id = 100;

DBMS_OUTPUT.PUT_LINE(‘ Salary is : ‘ || v_salary);

END;

Answer 19

V_SALARY

Question 20

What could be the possible error of the given code below?

DECLARE

v_sum_sal NUMBER(10,2);

v_deptno NUMBER NOT NULL := 60;

BEGIN

SELECT SUM(salary)

INTO v_sum_sal FROM employees WHERE department_id = v_deptno;

DBMS_OUTPUT.PUT_LINE(‘Dep #60 Salary Total: ‘ || v_sum_sal);

END;

Answer 20

No error

Question 21

Consider the given code fragment below. The condition will return a false value.

  A:=null; B:=null;

If A = B then . . . . .

Answer 21

TRUE

Question 22

The BASIC LOOP control structures are statements that enable you to execute statements in a PL/SQL block repeatedly.
Group of answer choices

True

False

Answer 22

TRUE

Question 23

In for loop, counter must be explicitly declared.
Group of answer choices

True

False

Answer 23

False

Question 24

In BASIC and WHILE loop, the iteration will terminate when the counter is < than the upper bound.

Answer 24

False

Question 25

The INTO clause occurs between the _______ and FROM clauses.

Answer 25

SELECT

Question 26

The memory area allocated by Oracle server to store the SQL statement and the data that it uses in known as __________.

Answer 26

IMPLICIT CURSOR

Question 27

Which DML statement make changes to the database?
Group of answer choices

INSERT

DELETE

All the options

UPDATE

Answer 27

All the options

Question 28

The code below must print the number of rows updated, what is the missing part of the code?

Declare

v_cal_increase employees.salary%type := 800;

begin

update copy_emp

set salary = salary + v_sal_increase

where job_id = ‘ST_CLERK’;

dbms_output.put_line(_____________ || ‘ rows updated.’);

end;

Answer 28

SQL%ROWCOUNT

Question 29

The code below must print the number of rows updated. Which part of the code must be changed to satisfy the output?

Declare

v_sal_increase employees.salary%type := 800;

begin

update copy_emp

set salary = salary + v_sal_increase

where job_id = ‘ST_CLERK’;

dbms_output.put_line(SQL%FOUND || ‘ rows updated.’);

end;

Answer 29

SQL%FOUND

Question 30

Consider the given code fragment below. The condition will return a true value.

A:=null; B:=null;

If A = B then . . . . .

Answer 30

False

Question 31

Applying the logical operator NOT to a null yields NULL.
Group of answer choices

True

False

Answer 31

True

Question 32

In for loop, counter variable is declared _______.

Answer 32

IMPLICITLY

Question 33

Without the EXIT statement, the loop would never end (an infinite loop).

Answer 33

True

Question 34

All counter variables must be declared at the declaration section.
Group of answer choices

True

False

Answer 34

FALSE

Question 35

An EXIT statement is used in order to come out of from the outer loop within an inner loop.
Group of answer choices

True

False

Answer 35

False

Question 36

The _________ SQL Rule: queries must return only one row.

Answer 36

EMBEDDED

Question 37

The _____ is an integer value that represents the number of rows affected by the most recent SQL statement.

Answer 37

SQL%ROWCOUNT

Question 38

The _____ statement selects rows from one table to update and/or insert into another table.

Answer 38

MERGE

Question 39

The ______ are automatically declared variables that allow you to evaluate what happened when a cursor was last used.
Group of answer choices

Explicit attributes

Cursor attributes

Attributes

Implicit attributes

Answer 39

Cursor attributes

Question 40

What could be the possible error of the given code below?

DECLARE

v_salary employees.salary%TYPE;

BEGIN

SELECT salary INTO v_salary

FROM employees where employee_id = 100;

DBMS_OUTPUT.PUT_LINE(‘ Salary is : ‘ || v_salary);

END;

Group of answer choices

VARIABLE ERROR

Query returns more than 1 row

TABLE ERROR

NO ERROR

Answer 40

NO ERROR

Question 41

A counter variable is an expression that returns true, false, or null.
Group of answer choices

True

False

Answer 41

False

Question 42

What is missing in the given WHILE loop syntax? ________

WHILE condition

statement1;

statement2;

. . .

END ;

Answer 42

LOOP

Question 43

A loop structure must have an exit clause.
Group of answer choices

True

False

Answer 43

True

Question 44

What could be the possible error of the given code below?

DECLARE

v_sum_sal NUMBER(10,2);

v_deptno NUMBER NOT NULL := 60;

BEGIN

SELECT SUM(salary) FROM employees WHERE department_id = v_deptno;

DBMS_OUTPUT.PUT_LINE(‘Dep #60 Salary Total: ‘ || v_sum_sal);

END;

Group of answer choices

Group function error

Query returns more than 1 row

Variable error

Missing INTO clause

Answer 44

Missing INTO clause

Question 45

Is there something missing in the given code?

DECLARE

 v_myage NUMBER := 31; 

BEGIN

IF v_myage < 11

DBMS_OUTPUT.PUT_LINE(‘I am a child’);

END IF;

END;

Group of answer choices

True

False

Answer 45

True

Question 46

A ________ is used in order to come out of from the outer loop within an inner loop.

Answer 46

LOOP LABELS

Question 47

The lower and upper bound of a for loop must be a numeric literals.
Group of answer choices

True

False

Answer 47

False

Question 48

The _________ cursors are defined by the PL/SQL programmer.

Answer 48

EXPLICIT

Question 49

What is the output of the given code below?

DECLARE

v_deptno copy_emp.department_id%TYPE := 50;

BEGIN

DELETE FROM copy_emp

WHERE department_id = v_deptno;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘ rows deleted.’);

END;

Answer 49

Print the number of rows deleted

Question 50

Control structures are used to change the logical flow of statements within the PL/SQL block.
Group of answer choices

True

False

Answer 50

True

Question 51

Use the ______ loop when the statement inside the loop must execute at least once.

Answer 51

BASIC

Question 52

The use of INTO clause in PL/SQL select statement is to specify the name of: ____________.
Group of answer choices

VARIABLE

CURSOR

TABLE

VIEW

Answer 52

VARIABLE

Question 53

The code below must print the number of rows deleted. What is missing in the given code to satisfy the output?

DECLARE

v_deptno copy_emp.department_id%TYPE := 50;

BEGIN

DELETE FROM copy_emp

WHERE _____________ = v_deptno;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘ rows deleted.’);

END;

Group of answer choices

DEPARTMENT_ID

DEPARTMENTS

JOBS

JOB_ID

Answer 53

DEPARTMENT_ID

Question 54

Use a DO..WHILE loop when the statement inside the loop must execute at least once.
Group of answer choices

True

False

Answer 54

False

Question 55

Start with the _____ keyword to define a user-defined record structure.

Answer 55

TYPE

Question 56

Given:

        DECLARE

                  v_emp_record   \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_; 

What is missing in the declaration section if we want to declare v_emp_record as type record of EMPLOYEES table? __________

Answer 56

EMPLOYEES%ROWTYPE

Question 57

The OPEN will populate the cursor’s active set with the result of the SELECT statement in the cursor’s definition?

True

False

Answer 57

True

Question 58

The FETCH statement positions the cursor pointer at the first row.
Group of answer choices

True

False

Answer 58

False

Question 59

What is missing in the given exception syntax? ____

EXCEPTION

     WHEN  exception1  [OR  exception2  .  .  .]

              statement1; statement2;

Answer 59

THEN

Question 60

Always add ________ whenever there is a possibility of an error occurring.

Answer 60

EXCEPTION HANDLERS

Question 61

Handle named exceptions whenever possible, instead of using ______ in exception handlers.

Answer 61

OTHERS

Question 62

Error in PL/SQL is known as ____________.

Answer 62

EXCEPTION

Question 63

Each ________ is consists of a WHEN clause, which specifies an exception name.

Answer 63

HANDLER

Question 64

When an exception is raised, control immediately shifts to the exception section and the appropriate handler in the exception section is executed.

True

False

Answer 64

TRUE

Question 65

The term TRAP in exceptions is the same as handling any error by including corresponding exception handler.
Group of answer choices

True

False

Answer 65

True

Question 66

A block always terminates when PL/SQL raises an exception.
Group of answer choices

True

False

Answer 66

True

Question 67

An exception handler for a particular exception must contain only one statement.
Group of answer choices

True

False

Answer 67

True

Question 68

The RAISE keyword is used in user-defined exception for error notification.
Group of answer choices

True

False

Answer 68

True

Question 69

The PRAGMA clause is used in predefined exception to tell the compiler to associate an exception name with a specific Oracle error number.
Group of answer choices

True

False

Answer 69

False

Question 70

User-defined exceptions are declared within the declarative section and are raised explicitly.
Group of answer choices

True

False

Answer 70

TRUE

Question 71

Non-predefined errors are raised explicitly.
Group of answer choices

True

False

Answer 71

FALSE

Question 72

The RAISE statement can be used to raise either user-defined or non-predefined exception.
Group of answer choices

True

False

Answer 72

TRUE

Question 73

The user-defined exceptions are declared within the declarative section and are raised implicitly.
Group of answer choices

True

False

Answer 73

False

Question 74

What is the first step in handing non-predefined exception?

Group of answer choices

Exception name declaration

Pragma declaration

Answer 74

Exception name declaration

Question 75

The RAISE statement can be used to raise either ________ or predefined exception.
Group of answer choices

USER-DEFINED

NON-PREDEFINED

Answer 75

USER-DEFINED

Question 76

The ________ clause is used in non-predefined exception to tell the compiler to associate an exception name with a specific Oracle error

Group of answer choices

PRAGMA EXCEPTION_INIT

SQLERRM

RAISE_APPLICATION_ERROR

SQLCODE

Answer 76

PRAGMA EXCEPTION_INIT

Question 77

Two methods for raising an exception:

Group of answer choices

Implicit, Explicit

Predefined, Non-predefined

Answer 77

Implicit, Explicit

Question 78

You can use the ________________ procedure to return user-defined error messages from stored subprograms.

Group of answer choices

RAISE_APPLICATION_ERROR

PRAGMA EXCEPTION_INIT

SQLCODE

SQLERRM

Answer 78

RAISE_APPLICATION_ERROR

Question 79

The following declaration is a PL/SQL syntax for defining a record.

Record_name table_name%rowtype;

Group of answer choices

True

False

Answer 79

TRUE

Question 80

Type and record declared in the outer block are visible only in the outer block.
Group of answer choices

True

False

Answer 80

False

Question 81

A user-defined PL/SQL record contains one or more fields of scalar data type.
Group of answer choices

True

False

Answer 81

True

Question 82

The cursor defined in the code below is ____

DECLARE
CURSOR cur_emps IS
SELECT employee_id, last_name, salary FROM employees
WHERE department_id = 30;

Group of answer choices

IMPLICIT CURSOR

Explicit Cursor

Answer 82

Explicit Cursor

Question 83

Given the code below:

1. DECLARE
2. CURSOR cur_emps IS
3. SELECT employee_id, last_name, salary FROM employees WHERE department_id = 30;
4. v_empno employees.employee_id%TYPE;
5. v_lname employees. last_name%TYPE;
6. v_sal employees.salary%TYPE;
7. BEGIN
8. OPEN cur_emps;
9. LOOP
10. FETCH cur_emps INTO v_empno, v_lname;
11. EXIT WHEN cur_emps%notfound;
12. DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
13. END LOOP;
14. END;

is line #12 valid or invalid

Answer 83

Valid

Question 84

The given code below declares an explicit cursor. What will cause an error in the code?

DECLARE

CURSOR cur_depts

SELECT * FROM departments WHERE location_id = 1700

ORDER BY department_name;

Group of answer choices

WHERE location_id = 1700

SELECT *

ORDER BY department_name;

IS

Answer 84

IS

Question 85

The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter.

1. DECLARE
2. CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
3. SELECT country_name, area FROM ____ WHERE region_id = p_region_id;
4. country_rec country_curs%ROWTYPE;
5. BEGIN
6. OPEN country_curs(____);
7. LOOP
8. FETCH country_curs INTO ______;
9. EXIT WHEN ________%NOTFOUND;
10. DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | 11. country_rec.area);
11. END LOOP;
12. CLOSE ______;
13. END;

What is missing in line #3?

Answer 85

countries

Question 86

If you omit the ______ keyword, then the Oracle server waits indefinitely until the rows are available.

Answer 86

NOWAIT

Question 87

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

What is the parameter mode of the formal parameter?_____

Answer 87

IN

Question 88

The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. The block is tested using region 5 (South America).

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

The missing element in in line 6 is Region_id.

Answer 88

False

Question 89

The NOWAIT keyword is optional in the FOR UPDATE clause.
Group of answer choices

True

False

Answer 89

True

Question 90

What symbol is used to terminate the TYPE statement?

DECLARE

    TYPE person_dept IS . . . . . . . .

Answer 90

;

Question 91

What symbol is used to enclose the elements of the TYPE structure?

Answer 91

()

Question 92

Given the code below:

The closing of cursor is missing

True

False

Answer 92

True

Question 93

The OPEN statement positions the cursor pointer at the first row.

True

False

Answer 93

True

Question 94

The __________ keyword is used in user-defined exception for error notification.

Answer 94

RAISE

Question 95

Code that defines the recovery actions to be performed when execution-time errors occur.

Answer 95

EXCEPTION HANDLER

Question 96

The following statements are examples of ________________.

Entering an expiration date that has passed
Selecting more than one row into a single variable
Receiving “no rows returned” from a select statement

Answer 96

EXCEPTION

Question 97

The ___________ contains the exceptions handlers.

Answer 97

EXCEPTION SECTION

Question 98

The EXECUTE keyword is used in user-defined exception for error notification.
Group of answer choices

True

False

Answer 98

False

Question 99

When code does not work as expected, PL/SQL raises an exception handler.
Group of answer choices

True

False

Answer 99

False

Question 100

The OTHERS is an optional exception-handling clause that traps any exceptions that have not been explicitly handled.
Group of answer choices

True

False

Answer 100

True

Question 101

Names for predefined exceptions must be declared in the declaration section.
Group of answer choices

True

False

Answer 101

FALSE

Question 102

The SQLCODE function returns the numeric value for the error code.
Group of answer choices

True

False

Answer 102

True

Question 103

You can use the RAISE_APPLICATION_ERROR procedure to return user-defined error messages from stored subprograms.
Group of answer choices

True

False

Answer 103

True

Question 104

Pragma declaration is used in declaring user-defined exceptions.
Group of answer choices

True

False

Answer 104

False

Question 105

What is the first parameter of the PRAGMA EXCEPTION_INIT function?

Group of answer choices

Oracle error number

EXCEPTION NAME

Answer 105

Oracle error number

Question 106

Each exception handler is consists of a _____ clause, which specifies an exception name.

Group of answer choices

IF

CONDITION

Not in the options

WHEN

Answer 106

WHEN

Question 107

In trapping a user-defined exception, these steps must be followed: DECLARE -> RAISE -> __________.

Group of answer choices

Reference

Identify Exception

Not in the options

Exception Handling

Answer 107

Reference

Question 108

In non-predefined exception, you must reference the ________ within a WHEN clause in the exception-handling section.

Group of answer choices

Exception name

Declared exception name

Oracle associated error#

All the options are possible

Answer 108

Declared exception name

Question 109

PL/SQL record is a composite data type, you can refer to the whole record by its name and/or to individual fields by their names.
Group of answer choices

True

False

Answer 109

True

Question 110

The given syntax in declaring a user-define record is correct.

TYPE type_name IS RECORD

(field_declaration[,field_declaration]…);

identifier type_name ;

Group of answer choices

True

False

Answer 110

True

Question 111

COMPOSITE

Answer 111

True

Question 112

1. DECLARE
2. CURSOR cur_emps IS
3. SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
4. v_empno employees.employee_id%TYPE;
5. v_lname employees.last_name%TYPE;
6. v_sal employees.salary%TYPE;
7. BEGIN
8. OPEN cur_emps;
9. LOOP
10. FETCH cur_emps INTO v_empno, v_lname;
11. EXIT WHEN cur_emps%notfound;
12. DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
13. END LOOP;
14. END

which line number(s) contains error?

Line 2

Line 4

No error

Line 3

Answer 112

Line 3

Question 113

Given the code below:

DECLARE

 CURSOR cur_emps

                SELECT employee_id, last_name  FROM employees;

BEGIN

FOR v_emp_record IN cur_emps LOOP 

EXIT WHEN cur_emps%ROWCOUNT > 5;

DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);

END LOOP;

END;

What step is missing in the given code?

Group of answer choices

CLOSE

FETCH

Nothing is missing

Open cursor

Answer 113

Nothing is missing

Question 114

Given the code below:

1. DECLARE
2. CURSOR cur_emps
3. SELECT employee_id, last_name FROM employees;
4. BEGIN
5. FOR cur_emps IN v_emp_record LOOP
6. EXIT WHEN cur_emps%ROWCOUNT > 5;
7. DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ‘ ‘ || v_emp_record.last_name);
8. END LOOP;
9. END;

What is missing in line 2 and 3?

INTO

IS

Nothing is missing

RECORD

Answer 114

IS

Question 115

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO _____;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

What is missing in line #8

Answer 115

country_rec

Question 116

DECLARE
CURSOR country_curs(_____ countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

What is missing in line#2?

Answer 116

p_region_id

Question 117

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec _____%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

What is missing in line #4

Answer 117

country_curs

Question 118

Refer to the code below. The missing element in line 8 COUNTRY_REC.

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO _____;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

True

False

Answer 118

True

Question 119

TYPE and Records are ________ structures.

Answer 119

COMPOSITE

Question 120

A ________ allows us to declare a variable as a record based on a particular table’s structure.

Answer 120

%ROWTYPE

Question 121

Use the %ISOPEN cursor attribute before performing a fetch to test whether the cursor is open.
Group of answer choices

True

False

Answer 121

True

Question 122

Each exception handler is consists of a _____ clause, which specifies an exception name.

Answer 122

WHEN

Question 123

The term ________ in exceptions is the same as handling any error by including corresponding exception handler.

Answer 123

TRAP

Question 124

Exception handlers are code that defines the recovery actions to be performed when execution-time errors occur.
Group of answer choices

True

False

Answer 124

True

Question 125

When an exception is raised, the rest of the execution section of the PL/SQL block is not executed.
Group of answer choices

True

False

Answer 125

True

Question 126

You can use the EXCEPTION_INIT procedure to return user-defined error messages from stored subprograms.
Group of answer choices

True

False

Answer 126

False

Question 127

Pragma declaration is used in declaring non-predefined exceptions.
Group of answer choices

True

False

Answer 127

TRUE

Question 128

The NO_DATA_FOUND is an example of:

Group of answer choices

Non-predefined exception

Predefined exception

Answer 128

Predefined exception

Question 129

The RAISE_APPLICATION_ERROR can be used in:

Group of answer choices

Declaration and Exception section

Executable and Exception section

Executable section

Exception section

Answer 129

Executable and Exception section

Question 130

The oracle error number, at the PRAGMA EXCEPTION_INIT function, starts with _____.

Group of answer choices

0

HYPEN

1

UNDERSCORE

Answer 130

HYPEN

Question 131

The given syntax in declaring a user-define record is incorrect.

TYPE type_name IS RECORD

(field_declaration[,field_declaration]…);

identifier type_name ;

Group of answer choices

True

False

Answer 131

False

Question 132

Given the code below, EMPLOYEES table must be specified at the DBMS_OUTPUT section.

DECLARE

        v_emp_record employees2%rowtype; 

BEGIN

SELECT * INTO v_emp_record FROM employees2 WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE(‘Email for ‘ || _____________.first_name || ‘ ‘ || ____________.last_name);

END;

Group of answer choices

True

False

Answer 132

True

Question 133

1. DECLARE
2. CURSOR cur_emps IS
3. SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
4. v_empno employees.employee_id%TYPE;
5. v_lname employees.last_name%TYPE;
6. v_sal employees.salary%TYPE;
7. BEGIN
8. OPEN cur_emps;
9. LOOP
10. FETCH cur_emps INTO v_empno, v_lname;
11. EXIT WHEN cur_emps%_____;
12. DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
13. END LOOP;
14. END;

What is missing in line#11?

Group of answer choices

FOUND

NOTFOUND

Answer 133

NOTFOUND

Question 134

Given the code below:

DECLARE

 CURSOR cur_emps

                SELECT employee_id, last_name  FROM employees;

BEGIN

FOR  cur_emps IN v_emp_record LOOP 

EXIT WHEN cur_emps%ROWCOUNT > 5;

DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);

END LOOP;

END;

What will cause an error in the FOR section?

Group of answer choices

No error

cur_emps IN v_emp_record

Counter declaration

Remove LOOP

Answer 134

cur_emps IN v_emp_record

Question 135

Given the code below, assume that the cursor contains 20 rows. How many rows will be fetched from the cursor?

1. DECLARE
2. CURSOR cur_emps
3. SELECT employee_id, last_name FROM employees;
4. BEGIN
5. FOR cur_emps IN v_emp_record LOOP
6. EXIT WHEN cur_emps%ROWCOUNT > 5;
7. DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ‘ ‘ || v_emp_record.last_name);
8. END LOOP;
9. END;

Group of answer choices

0

5

6

4

Answer 135

5

Question 136

The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. The block is tested using region 5 (South America).

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN _______;
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

How many parameter is needed to be passed from the calling environment? _____

Answer 136

1

Question 137

You must include the FOR UPDATE clause in the cursor query so that the rows are _____ on OPEN.

Answer 137

LOCKED

Question 138

Refer to the code below. The missing element in line 9 is COUNTRY_REC.

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN _______;
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN ______%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

True

False

Answer 138

FALSE

Question 139

You must include the FOR UPDATE clause in the cursor query so that the rows are locked on OPEN.
Group of answer choices

True

False

Answer 139

True

Question 140

Given the code below:

DECLARE

        v_emp_record employees%rowtype; 

BEGIN

SELECT * INTO v_emp_record FROM employees WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE(‘Email for ‘ || _____________.first_name ||

’ ‘ || ____________.last_name);

END;

What is missing in the DBMS_OUTPUT section?

Answer 140

v_emp_record

Question 141

Given the declaration below:

DECLARE

TYPE person_dept IS _________

first_name employees.first_name%TYPE,

last_name employees.last_name%TYPE,

department_name departments.department_name%TYPE;

What is missing in the TYPE section?__________

Answer 141

RECORD

Question 142

The FETCH will populate the cursor’s active set with the result of the SELECT statement in the cursor’s definition?
Group of answer choices

True

False

Answer 142

FALSE

Question 143

1. DECLARE
2. CURSOR cur_emps IS
3. SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
4. v_empno employees.employee_id%TYPE;
5. v_lname employees.last_name%TYPE;
6. v_sal employees.salary%TYPE;
7. BEGIN
8. OPEN cur_emps;
9. LOOP
10. FETCH cur_emps INTO v_empno, v_lname;
11. EXIT WHEN cur_emps%NOTFOUND;
12. DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
13. END LOOP;
14. END;

The cursor declaration is correct.

Group of answer choices

True

False

Answer 143

TRUE

Question 144

The exception section begins with the keyword _______.

Answer 144

EXCEPTION

Question 145

In exception section, the WHEN clause if followed by _______.

Answer 145

THEN

Question 146

Each exception handler is consists of a WHEN clause, which specifies an exception name.
Group of answer choices

True

False

Answer 146

TRUE

Question 147

Exception section is mandatory in PL/SQL block.
Group of answer choices

True

False

Answer 147

False

Question 148

The following statements are examples of exception handler.

Entering an expiration date that has passed
Selecting more than one row into a single variablE
Receiving “no rows returned” from a select statement
Group of answer choices

True

False

Answer 148

False

Question 149

The RAISE statement can be used to raise either user-defined or predefined exception.
Group of answer choices

True

False

Answer 149

FALSE

Question 150

The _______________ is used in non-predefined exception to tell the compiler to associate an exception name with a specific Oracle error

Group of answer choices

PRAGMA EXCEPTION_INIT

EXCEPTION_INIT

PRAGMA EXCEPTION INIT

EXCEPTION INIT

Answer 150

PRAGMA EXCEPTION_INIT

Question 151

The ___________ and ___________ are the two types of Oracle Server Errors.

Group of answer choices

Predefined, Non-predefined

Implicit, Explicit

Answer 151

Predefined, Non-predefined

Question 152

The RAISE statement can be used to raise either user-defined or ________ exception.

Group of answer choices

PREDEFINED

NON-PREDEFINED

Answer 152

NON-PREDEFINED

Question 153

Types and records are composite structures that can be declared anywhere that scalar variables can be declared.
Group of answer choices

True

False

Answer 153

TRUE

Question 154

Given the code below, EMPLOYEES%ROWTYPE is missing in the declaration section to declare v_emp_record as type record of EMPLOYEES table.

DECLARE

           v_emp_record    \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_;

Group of answer choices

True

False

Answer 154

TRUE

Question 155

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

What is the counter variable?

Answer 155

NONE

Question 156

The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. Test the block using region 5 (South America).

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(_);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

What is missing in line#6?

Answer 156

5

Question 157

When we declare a cursor FOR UPDATE, each row is locked as we open the cursor and prevent other users from reading the rows.
Group of answer choices

True

False

Answer 157

False

Question 158

The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. Test the block using region 5 (South America).

DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(_);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;

The missing element in in line 6 is 1.

True

False

Answer 158

False