Deck 9: Advanced SQL and PlSQL Topics

Question 1

consultant_skill_skill_id_certification is a valid index name.

Answer 1

False

Question 2

A composite index can contain up to 16 columns.

Answer 2

True

Question 3

Other database users may execute stored program units.

Answer 3

True

Question 4

To call a function from a SQL*Plus session assign the return value of the function to a variable using the assignment operator (:=).

Question 5

In a procedure declaration,the DEFINE keyword follows the parameter list.

Question 6

If an index does not improve query performance,it should be dropped.

Question 7

To display a summary listing of compile errors generated by the last program unit that was compiled,you execute the SHOW ERRORS command.

Question 8

The IN OUT parameter mode is the default if parameter mode is not specified.

Question 9

A library is the only client-side stored program unit.

Question 10

To view index names for indexes in your own schema,use the command "SELECT index_name FROM all_indexes".

Question 11

To call a stored procedure from a PL/SQL program,do not use the EXECUTE keyword.

Question 12

If you are passing the value of a named variable to a stored program unit,the variable name must be the same as the corresponding variable name in the program unit parameter declaration list.

Question 13

It is more efficient to create an index when the table is empty,before adding any data.

Question 14

Any stored procedures and functions developed in Forms Builder can never be used by other users.

Question 15

If applications frequently insert or modify table data it is better not to use an index.

Question 16

A program unit cannot be used in a larger program.

Question 17

Index names should using the naming convention tablename_fieldname.

Question 18

A PL/SQL stored program unit can run on either the database server or on the client workstation.

Question 19

A database table cannot have more than five indexes.

Question 20

A composite index is used for a query that uses logical operators in the search condition.

Question 21

Database triggers are often used to gather audit information.

Question 22

What is the maximum number of indexes a table can have?

A)2
B)5
C)99
D)Unlimited

Question 23

Variables declared in a package can only be used within the package.

Question 24

When is the best time to create an index?

A)When the table is empty
B)When the table is half-full
C)When all rows have been added
D)It doesn't matter - any time is fine

Question 25

What is the maximum number of columns that can be contained in a composite index?

A)2
B)4
C)16
D)32

Question 26

You can cause a trigger to be executed only if a certain condition is true by adding the WHEN clause to the trigger header.

Question 27

Every package specification must have a matching package body.

Question 28

What is the name of the table field that stores information about the physical location of a row?

A)ROWNUM
B)ROWID
C)ROWLOC
D)ROWPOS

Question 29

Database triggers is another name for Form Triggers.

Question 30

What type of data structure does Oracle use to store indexes?

A)Linked List
B)Array
C)B-Tree
D)Red-Black Tree

Question 31

What type of database column is indexed automatically?

A)Primary key column
B)Columns with data type NUMBER
C)Foreign key columns
D)Unique key columns

Question 32

When creating a package specification,components must be declared in the following order: public variables,cursors,functions and procedures.

Question 33

What is the purpose of a database table index?

A)Keep track of changes
B)Audit trail
C)Speed up queries
D)Modularize code

Question 34

A PL/SQL library can only contain stored procedures,not functions.

Question 35

A PL/SQL package contains exactly the same functionality as a library,but it resides on the database server.

Question 36

If you modify a library in Forms Builder,you must reattach the library to the forms that use it before the changes will take effect.

Question 37

The Database Objects node ____.

A)is a top-level node within the Object Navigator
B)is not associated with a specific form
C)contains child nodes that represent every database user
D)All of the above

Question 38

The Database Objects node in Forms Builder is a top-level node within the Object Navigator,and is not associated with a specific form.

Question 39

What naming convention should be followed for indexes?

A)The Oracle Naming Convention
B)tablename_fieldname
C)fieldname_tablename
D)in_fieldname_tablename

Question 40

Which of the following is a reason not to create an index?

A)The field contains a large number of null values
B)The field contains a wide range of values
C)The table contains a large number of records
D)Most queries retrieve more than 2% to 4% of the table records

Question 41

The ____ command is used to display error information if a procedure does not compile.

A)VIEW ERRORS
B)ERRORS
C)SEND ERRORS
D)SHOW ERRORS

Question 42

The ____ keyword is used to call a stored procedure in SQL*Plus.

A)CALL
B)EXECUTE
C)RUN
D)No keyword is needed - just the procedure name

Question 43

What keyword follows the parameter declaration list in a procedure header?

A)AND
B)IS
C)BEGIN
D)END

Question 44

To save a program unit as a stored program unit in the database,you use the ____ node in the Object Navigator

A)PL/SQL
B)Database Objects
C)Procedures
D)Functions

Question 45

Which of the following program units must return exactly one value?

A)package
B)function
C)procedure
D)library

Question 46

How many records does it take for a table to be considered large?

A)100
B)1000
C)10,000
D)100,000

Question 47

The ____ parameter mode indicates that the parameter will not be modified by the procedure.

A)IN
B)OUT
C)IN OUT
D)OUT IN

Question 48

What type of program unit may be shared with other users?

A)declared
B)public
C)stored
D)defined

Question 49

Which of the following stored program units can return zero,one,or many values?

A)function
B)procedure
C)trigger
D)package

Question 50

What follows the parameter declaration list in a function?

A)IS
B)RETURN
C)BEGIN
D)END

Question 51

What keyword indicates that a procedure definition is finished?

A)DONE
B)END
C)DROP
D)FINISH

Question 52

How do you test a program unit in Forms Builder?

A)Create a form for the input fields and attach a form trigger to a button.
B)Use the auto test feature.
C)Write a test harness PL/SQL program.
D)Create a new tester component and associate the program unit with it.

Question 53

What keyword indicates the start of the body of a stored procedure?

A)BEGIN
B)WHEN
C)IS
D)AND

Question 54

How many input parameters can a function have?

A)0
B)1
C)10
D)unlimited

Question 55

The ____ parameter mode should be used to return a value from a procedure when the input value to the procedure will not be set.

A)IN
B)OUT
C)IN OUT
D)OUT IN

Question 56

What is the extension for a PL/SQL library design file?

A)psl
B)lib
C)pll
D)plx

Question 57

What is the last command in a function body?

A)DONE
B)RETURN
C)EXIT
D)QUIT

Question 58

How do you call a function from a PL/SQL program?

A)EXECUTE function_name (parameters... )
B)CALL function_name (parameters ... )
C)variable := function_name (parameters ... )
D)RUN function_name (parameters ... )

Question 59

What is the extension for a PL/SQL library executable file?

A)psx
B)lib
C)pll
D)plx

Question 60

Which of the following program units executes on the client workstation?

A)package
B)trigger
C)library
D)function

Question 61

How do you build a library in Forms Builder?

A)Create a new library,add existing program units.
B)Create a new library,then write new program units
C)Create a new library,then locate program units in the database and copy and paste them into the library
D)Create a new library,then ask a DBA to add existing program units from the DBMS

Question 62

A(n)____________________ is a program unit that can receive multiple input parameters and return multiple output values or return no output values.

Question 63

What is the keyword that indicates the start of a trigger body?

A)EXECUTE
B)WHEN
C)START
D)BEGIN

Question 64

How do you temporarily disable a trigger called my_trigger?

A)DROP TRIGGER my_trigger
B)DISABLE TRIGGER my_trigger
C)ALTER TRIGGER my_trigger DISABLE
D)ALTER my_trigger DISABLE

Question 65

____________________ PL/SQL program units are program units that other PL/SQL programs can reference,and that other database users can execute.

Question 66

The first field of a composite index is the ____________________ field.

Question 67

What is the correct order for items in a package specification?

A)variables,cursors,functions,procedures
B)cursors,functions,variables,procedures
C)functions,procedures,variables,cursors
D)any order is acceptable

Question 68

To remove an index,use the ____________________ INDEX command.

Question 69

What is another name for a package header?

A)package body
B)package definition
C)package specification
D)package declaration

Question 70

How do you reference the previous value of a field from within a trigger?

A):OLD.fieldname
B)OLD:fieldname
C):PREV.fieldname
D)PREV:fieldname

Question 71

What type of trigger fires only once per row?

A)Statement-level trigger
B)Row-level trigger
C)Column-level trigger
D)Field-level trigger

Question 72

To find information on the indexes in your own schema use the ____________________ data dictionary view.

Question 73

Which of the following is a legal command when used in a trigger body?

A)COMMIT
B)SAVEPOINT
C)SELECT
D)ROLLBACK

Question 74

The ____________________ field of every database table specifies the internal location of the record in the database.

Question 75

A database table ____________________ is a distinct database table that contains data values,along with a corresponding column that specifies the physical locations of the records that contain those data values.

Question 76

A(n)____________________ PL/SQL program is one which is submitted to the PL/SQL interpreter and run,but that does not interact with other program units.

Question 77

How do you specify that a trigger should only fire if the value of the column AMOUNT is less than 100?

A)IF amount < 100
B)WHEN amount < 100
C)ONLY IF amount < 100
D)ONLY WHEN amount < 100

Question 78

____________________ program units are stored in the database as database objects and execute on the database server.

Question 79

A(n)____________________ is a database index table that contains multiple sorted columns that the DBMS can use for identifying the row location.

Question 80

When using Forms Builder to create a package,what is the first step in the process?

A)Create a new Package
B)Create a new Package Spec
C)Create a new Package Header
D)Create a new Package Definition