Semester 1 Final Exam
Consider
the following code:
DECLARE
TYPE dept_info_type
IS RECORD
(department_id
departments.department_id%TYPE,
department_name
departments.department_name%TYPE);
TYPE emp_dept_type IS
RECORD
last_name
employees.last_name%TYPE),
dept_info
dept_info_type);
v_emp_dept_rec
emp_dept_type;
How many fields can be addressed in v_emp_dept_rec?
one
two
three
four
(*)
Which of the following methods
can be used to reference elements of an INDEX BY table? (Choose three.)
EXISTS
(*)
FIRST
(*)
PREVIOUS
DROP
COUNT
(*)
What is wrong with the
following code?
BEGIN
FOR emp_rec IN
(SELECT * FROM
employees WHERE ROWNUM < 10 ) LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec%ROWCOUNT || emp_rec.last_name):
END LOOP;
END;
You
cannot use FOR UPDATE NOWAIT with a cursor FOR loop using a subquery.
You
cannot use ROWNUM with a cursor FOR loop.
You
cannot reference %ROWCOUNT with a cursor FOR loop using a subquery. (*)
The
cursor has not been opened.
The
field EMP_REC.LAST_NAME does not exist.
You have declared a cursor as
follows:
CURSOR loc_curs IS SELECT * FROM locations;
How should you code a FOR loop to use this cursor?
FOR
loc_rec IN loc_curs LOOP ... (*)
WHILE
loc_rec IN loc_curs LOOP ...
IF
loc_rec IN loc_curs LOOP ...
FOR
loc_rec IN 1 .. loc_curs%ROWCOUNT LOOP ...
FOR
loc_curs IN loc_rec LOOP ...
There are no employees in
department_id 75.
Which of the following is NOT a valid cursor FOR loop with a
subquery?
None of
these. They are all valid. (*)
FOR
emp_rec IN
(SELECT * FROM
employees) LOOP ...
FOR
emp_rec IN
(SELECT last_name,
salary FROM employees) LOOP ...
FOR
emp_rec IN
(SELECT * FROM
employees WHERE department_id = 75) LOOP ...
FOR
emp_rec IN
(SELECT last_name,
salary FROM employees ORDER BY last_name) LOOP ...
Assume that table BIGDEPTS contains 100 rows, and table
BIGEMPS contains 1000 rows, with 10 employees in each department. Consider the
following code:
DECLARE
CURSOR bigdept_cur
IS
SELECT * FROM
bigdepts;
CURSOR bigemp_cur IS
SELECT * FROM
bigemps;
BEGIN
FOR dept_rec IN
bigdept_cur LOOP
DBMS_OUTPUT.PUT_LINE (dept_rec.department_name);
FOR emp_rec IN
bigemp_cur LOOP
IF
emp_rec.department_id=dept_rec.department_id
THEN
DBMS_OUTPUT.PUT_LINE (emp_rec.last_name);
END IF;
END LOOP;
END LOOP;
END;
Why is this code inefficient?
It is
doing a Cartesian Product, joining every employee with every department and
displaying 1100 lines of output.
It
locks both tables unnecessarily.
It is
using two cursors when one cursor is enough.
It
reads 1000 employee rows every time BIGEMP_CUR is OPENed, and then ignores 990
of them. (*)
It is
using cursor FOR loops, which are less efficient than OPENing and CLOSEing the
cursors explicitly.
Which of the following is NOT
allowed when using multiple cursors with parameters?
Declaring
a cursor based on a join
Declaring
a cursor FOR UPDATE
OPENing
more than one cursor at the same time
Using
cursor FOR loops
None of
these--they are all allowed. (*)
Examine the following code:
DECLARE
CURSOR c IS SELECT *
FROM employees FOR UPDATE;
c_rec c%ROWTYPE;
BEGIN
OPEN c;
FOR i IN 1..20 LOOP
FETCH c INTO
c_rec;
IF i = 6 THEN
UPDATE employees
SET first_name = 'Joe'
WHERE CURRENT OF
c;
END IF;
END LOOP;
CLOSE c;
END;
Which employee row or rows will be updated when this block
is executed?
The
block will not compile because the cursor should have been declared .... FOR UPDATE
WAIT 5;
None of
these.
The 6th
fetched row will be updated. (*)
The
first 6 fetched rows will be updated.
No rows
will be updated because you locked the rows when the cursor was opened.
Consider the following cursor:
CURSOR c IS
SELECT
e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d
USING(department_id)
WHERE
e.last_name='Smith'
FOR UPDATE;
When the cursor is opened and rows are fetched, what is
locked?
The
whole EMPLOYEES and DEPARTMENTS tables are locked.
Each
'Smith' row is locked and Smith's matching rows in DEPARTMENTS are locked. No
other rows are locked in either table. (*)
The
whole EMPLOYEES table is locked.
Nothing
is locked because the cursor was not declared with NOWAIT.
In the
EMPLOYEES table, only the 'Smith' rows are locked. Nothing in the DEPARTMENTS
table is locked.
When must you declare and use an
explicit cursor?
You
want to use a MERGE statement.
You
want to be able to ROLLBACK a transaction if needed.
You
need to SELECT more than one row from a table. (*)
You
need to UPDATE more than one row in a table.
Which one of the following explicit cursor declarations is
NOT valid?
CURSOR
country_curs IS SELECT country_name FROM wf_countries ORDER BY population DESC;
CURSOR
country_curs IS SELECT country_name FROM wf_countries WHERE region_id IN
(SELECT region_id FROM wf_world_regions WHERE LOWER(region_name) LIKE
'%asia%');
CURSOR
country_curs IS SELECT country_name INTO v_country_name FROM wf_countries; (*)
CURSOR
country_curs IS SELECT country_name, region_name FROM wf_countries c,
wf_world_regions r WHERE c.region_id = r.region_id;
You cannot OPEN or CLOSE an
implicit cursor. Why not?
Because
an implicit cursor is OPENed and CLOSEd automatically by Oracle. (*)
Because
an implicit cursor is always called SQL.
What is wrong with the following
code?
DECLARE
CURSOR dept_curs IS
SELECT department_name FROM departments;
v_dept_name
departments.department_name%TYPE;
BEGIN
OPEN dept_curs;
LOOP
FETCH dept_curs
INTO v_dept_name;
EXIT WHEN dept_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dept_name);
CLOSE dept_curs;
END LOOP;
END;
The
EXIT WHEN ... statement should be coded outside the loop.
The
CLOSE statement should be coded after END LOOP; (*)
The
OPEN statement should be inside the loop.
Nothing
is wrong; all the rows will be FETCHed and displayed.
The
loop should be a WHILE loop, not a basic loop.
Examine the following code
fragment:
DECLARE
CURSOR emp_curs IS
SELECT
first_name, last_name FROM employees;
v_emp_rec
emp_curs%ROWTYPE;
BEGIN
...
FETCH emp_curs
INTO v_emp_rec;
DBMS_OUTPUT.PUT_LINE(.. Point A ...);
...
To display the fetched last name, what should you code at
Point A?
v_emp_rec
v_emp_rec(last_name)
None of
these.
v_emp_rec.last_name
(*)
last_name
You can reference explicit cursor
attributes directly in a SQL statement. True or False?
True
False
(*)
Which of the following statements about the %ISOPEN cursor
attribute is true?
You can
issue the %ISOPEN cursor attribute only when a cursor is open.
You can
issue the %ISOPEN cursor attribute only when more than one record is returned.
If a
cursor is open, then the value of %ISOPEN is false.
You can
issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)
What is missing from the
following cursor declaration?
CURSOR emp_curs
IS
SELECT * FROM departments
WHERE location_id =
p_loc_id;
A
parameter is missing. The parameter should be coded as: (p_loc_id IN NUMBER)
Nothing
is wrong; the cursor declaration is correct.
A
parameter is missing. The parameter should be coded as: (p_loc_id NUMBER) (*)
The
declaration is invalid. You cannot reference a cursor parameter in a WHERE
clause.
Look at the following code:
DECLARE
CURSOR emp_curs
(p_dept_id employees.department_id%TYPE) IS
SELECT * FROM
employees
WHERE department_id
= p_dept_id;
v_emp_rec
emp_curs%ROWTYPE;
v_deptid NUMBER(4)
:= 50;
BEGIN
OPEN emp_curs( --
Point A --);
....
You want to open the cursor, passing value 50 to the
parameter. Which of the following are correct at Point A?
100 / 2
v_deptid
All of
these. (*)
50
Examine the following code. Why
does this exception handler not follow good practice guidelines?
DECLARE
v_salary
employees.salary%TYPE;
BEGIN
SELECT salary INTO
v_salary FROM employees
WHERE
employee_id = 999;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
The
exception handler should test for the named exception NO_DATA_FOUND. (*)
The
exception handler should COMMIT the transaction.
employee_id
999 does not exist in the employees table.
You
should not use DBMS_OUTPUT.PUT_LINE in an exception handler.
Which of the following is NOT an
advantage of including an exception handler in a PL/SQL block?
Avoids
costly and time-consuming correction of mistakes
Code is
more readable because error-handling routines can be written in the same block
in which the error occurred
Prevents
errors from occurring (*)
Prevents
errors from being propagated back to the calling environment
The following EXCEPTION section is constructed correctly.
True or False?
EXCEPTION
WHEN NO_DATA_FOUND
OR TOO_MANY_ROWS
THEN
statement_1;
statement_2;
WHEN OTHERS
THEN
statement_3;
END;
TRUE
(*)
FALSE
No employees are in department_id
99. What output will be displayed when the following code is executed?
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM employees
WHERE department_id = 99;
IF v_count = 0
THEN
RAISE
NO_DATA_FOUND;
DBMS_OUTPUT.PUT_LINE('No employees found');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('Department 99 is empty');
END;
The
block will fail because you cannot explicitly RAISE a predefined Oracle Server
error such as NO_DATA_FOUND
No
employees found
No
employees found Department 99 is empty
Department
99 is empty (*)
User-defined exceptions must be
declared explicitly by the programmer, but then are raised automatically by the
Oracle Server. True or False?
TRUE
FALSE
(*)
What is the datatype of a
user-defined exception?
VARCHAR2
NUMBER
None of
these.
EXCEPTION
(*)
BOOLEAN
The following exception handler
will successfully insert the Oracle error number and error message into a log
table whenever an Oracle Server error occurs. True or False?
EXCEPTION
WHEN OTHERS THEN
INSERT INTO
err_log_table (num_col, char_col)
VALUES
(SQLCODE, SQLERRM);
END;
(Assume that err_log_table has been created with suitable
columns and datatypes.)
TRUE
FALSE
(*)
A PL/SQL block executes and an Oracle Server exception is
raised. Which of the following contains the text message associated with the
exception?
SQLERRM
(*)
SQL%MESSAGE
SQL_MESSAGE_TEXT
SQLCODE
Which of these exceptions would
need to be raised explicitly by the PL/SQL programmer?
A row
is FETCHed from a cursor while the cursor is closed.
A
SELECT statement returns more than one row.
OTHERS
A check
constraint is violated.
A SQL
UPDATE statement does not update any rows. (*)
What will happen when the
following code is executed?
DECLARE
e_excep1
EXCEPTION;
e_excep2
EXCEPTION;
BEGIN
RAISE e_excep1;
EXCEPTION
WHEN e_excep1 THEN
BEGIN
RAISE
e_excep2;
END;
END;
It will
fail to compile because you cannot declare more than one exception in the same
block.
It will
compile successfully and return an unhandled e_excep2 to the calling
environment. (*)
It will
fail to compile because e_excep1 is out of scope in the subblock.
It will
fail to compile because you cannot have a subblock inside an exception section.
What will be displayed when the
following code is executed?
<< outer>>
DECLARE
v_myvar NUMBER;
BEGIN
v_myvar := 25;
DECLARE
v_myvar NUMBER
:= 100;
BEGIN
outer.v_myvar
:= 30;
v_myvar :=
v_myvar / 0;
outer.v_myvar
:= 35;
END;
v_myvar := 40;
EXCEPTION
WHEN ZERO_DIVIDE
THEN
DBMS_OUTPUT.PUT_LINE(v_myvar);
END;
40
35
30 (*)
25
100
The following are the steps
involved in creating, and later modifying and re-creating, a PL/SQL procedure
in Application Express. Which step is missing?
Type the procedure code in the SQL Commands window
Click on the "Save" button and save the procedure
code
Retrieve the saved code from "Saved SQL" in SQL
Commands
Modify the code in the SQL Commands window
Execute the code to re-create the procedure
Execute
the code to create the procedure (*)
Enter
parameters and data type
Execute
the procedure from USER_SOURCE data dictionary view
Invoke
the procedure from an anonymous block
A programmer wants to create a PL/SQL procedure named
EMP_PROC. What will happen when the following code is executed?
CREATE OR REPLACE PROCEDURE emp_proc IS
v_salary
employees.salary%TYPE;
BEGIN
SELECT salary INTO
v_salary FROM employees
WHERE
employee_id = 999;
DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary);
END;
The
statement will raise a NO_DATA_FOUND exception because employee_id 999 does not
exist.
The
procedure will be created successfully. (*)
The
statement will fail because the procedure does not have any parameters.
The
statement will fail because you cannot declare variables such as v_salary
inside a procedure.
The
statement will fail because the last line of code should be END emp_proc;
A stored PL/SQL procedure can be
invoked from which of the following?
A. A PL/SQL anonymous block
B. A calling application
C. A SELECT statement
D. Another PL/SQL procedure
A only
A and B
B and C
A, B,
and D (*)
A and C
Which of the following statements
about actual parameters is NOT true?
An
actual parameter can have a TIMESTAMP datatype.
An
actual parameter is declared in the calling environment, not in the called
procedure.
An
actual parameter can have a Boolean datatype.
The
datatypes of an actual parameter and its formal parameter must be compatible.
An
actual parameter must be the name of a variable. (*)
Which one of the following
statements about formal and actual parameters is true?
Formal
and actual parameters must have different names.
A
formal parameter is declared within the called procedure, while an actual
parameter is declared in the calling environment. (*)
An
actual parameter is declared within the called procedure.
Formal
and actual parameters must have the same name.
A procedure is invoked by this
command:
myproc('Smith',100,5000);
What is the method of passing parameters used here?
Named
Positional
(*)
A
combination of positional and named
None of
these.
Procedure
SOMEPROC has five parameters named A, B, C, D, E in that order. The procedure
was called as follows:
SOMEPROC(10,20,30,D=>50,E=>60);
How was parameter B referenced?
A
combination of named and defaulted
Positional
(*)
A
combination of positionally and named
Defaulted
Named
The database administrator has
granted the DROP ANY PROCEDURE privilege to user KIM. This allows Kim to remove
other users' procedures and functions from the database. How would Kim now drop
function GET_EMP, which is owned by user MEHMET?
DROP
FUNCTION mehmet.get_emp (*)
DROP
PROCEDURE mehmet.get_emp
DROP
FUNCTION get_emp FROM mehmet
None of
these
DROP
PROGRAM mehmet.get_emp
Which dictionary view will list
all the PL/SQL subprograms in your schema?
user_subprograms
user_objects
(*)
user_dependencies
user_procedures
user_source
What will happen when the
following procedure is executed?
PROCEDURE log_usage (p_card_id NUMBER, p_loc NUMBER)
IS
PRAGMA
AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO
log_table (card_id, location, tran_date)
VALUES (p_card_id,
p_loc, SYSDATE);
COMMIT;
END log_usage;
The
program will compile successfully.
The
compilation will fail because a semicolon after AUTONOMOUS_TRANSACTION is
required. (*)
The
subprogram will fail because the PRAGMA statement must be before IS.
The
subprogram will fail because it is missing AUTHID CURRENT_USER before IS.
Procedure GET_EMPS includes a
SELECT…FROM EMPLOYEES. The procedure was created using Invoker's Rights. Which
of the following statements are true? (Choose three.)
(Choose
all correct answers)
The
user who executes the procedure needs EXECUTE privilege on the procedure. (*)
The
creator of the procedure needs SELECT privilege on EMPLOYEES. (*)
The
user who executes the procedure does not need any privileges.
The
user who executes the procedure needs SELECT privilege on EMPLOYEES. (*)
You want to allow user JOE to query the CD_DETAILS table in
your schema. Which command should you use?
GRANT
SELECT ON joe TO cd_details;
GRANT
QUERY ON cd_details TO joe;
GRANT
SELECT TO joe ON cd_details;
GRANT
cd_details TO joe;
GRANT
SELECT ON cd_details TO joe; (*)
USERB creates a function called
SEL_PROC which includes the statement:
SELECT ... FROM usera.employees ...;
USERC needs to execute UserB's procedure. What privileges
are needed for this to work correctly? (Choose two.)
(Choose
all correct answers)
UserB
needs SELECT on userA.employees (*)
UserA
needs EXECUTE on userB.sel_proc
UserC
needs SELECT on userA.employees
UserC
needs EXECUTE on userB.sel_proc (*)
UserC
needs EXECUTE on Userb
A PL/SQL function can have IN OUT
parameters. True or False?
True
False
(*)
The following function has been
created:
CREATE OR REPLACE FUNCTION find_sal
(p_emp_id IN
employees.employee_id%TYPE)
RETURN NUMBER IS ...
We want to invoke this function from the following anonymous
block:
DECLARE
v_mynum
NUMBER(6,2);
v_mydate DATE;
BEGIN
... Line A
END;
Which of the following would you include at Line A?
find_sal(100,v_mynum);
v_mydate
:= find_sal(100);
find_sal(v_mynum,100);
v_mynum
:= find_sal(100); (*)
To create a function
successfully, the following steps should be performed.
A Re-execute the
code until it compiles correctly
B Write the code
containing the CREATE or REPLACE FUNCTION followed by the function code
C Test the function
from a SQL statement or an anonymous block
D If the function
fails to compile, correct the errors
E Load the code into
Application Express
F Execute the code
in Application Express
What is the correct order to perform these steps?
B,C,E,F,D,A
D,B,E,F,A,C
A,B,E,F,D,C
B,E,F,D,A,C
(*)
JOHN and FRED are database users. JOHN grants SELECT
privilege to FRED on three of his (JOHN's) tables. Which Dictionary view should
FRED query to see the names of JOHN's three tables?
DICTIONARY
DBA_TABLES
ALL_TABLES
(*)
FRED_TABLES
USER_TABLES
You want to find out how many
Dictionary views will list objects in your schema (but not in other users'
schemas). Which of the following queries should you use to do this?
SELECT
COUNT(*)
FROM DBA_OBJECTS
WHERE
OWNER='USER';
SELECT
COUNT(*)
FROM DICTIONARY
WHERE TABLE_NAME
LIKE 'USER%';
(*)
SELECT
COUNT(*)
FROM DICTIONARY
WHERE TABLE_NAME
NOT LIKE 'DBA%';
SELECT
COUNT(*)
FROM
USER_DICTIONARY;
SELECT
COUNT(*)
FROM DICTIONARY;
When creating a user-defined
function that will be called from a SQL statement, the size of the returned
values may be up to the size of any PL/SQL data type. True or False?
True
False
(*)
Function DOUBLE_SAL has been
created as follows: CREATE OR REPLACE FUNCTION double_sal (p_salary IN
employees.salary%TYPE) RETURN NUMBER IS BEGIN RETURN(p_salary * 2); END; Which
of the following calls to DOUBLE_SAL will NOT work?
SELECT
* FROM employees ORDER BY double_sal(salary) DESC;
None,
they will all work (*)
SELECT
last_name, double_sal(salary) FROM employees;
SELECT
* FROM employees WHERE double_sal(salary) > 20000;
UPDATE
employees SET salary = double_sal(salary);
You want to create a function
which can be used in a SQL statement. Which one of the following can be coded
within your function?
RETURN
BOOLEAN
COMMIT;
One or
more IN parameters (*)
An OUT
parameter
soal pertama yang awalannya consider itu jawaban bener nya two
ReplyDelete