Oracle 11G SQL 2nd Edition By Joan Casteel – Test bank

Complete Test Bank With Answers

 

 

Sample Questions Posted Below

 

Chapter 5: Data Manipulation and Transaction Control

TRUE/FALSE

1. The INSERT command is used to modify existing data in a row.

ANS: F PTS: 1 REF: 138

2. The UPDATE command can be used to add data to existing rows in a table.

ANS: T PTS: 1 REF: 138

3. The MODIFY command can be used to change existing data stored in a table.

ANS: F PTS: 1 REF: 138

4. The ROLLBACK command is used to permanently save changed data.

ANS: F PTS: 1 REF: 138

5. The LOCK TABLE command can be used to prevent other users from making changes to a table.

ANS: T PTS: 1 REF: 138

6. The BLOCK ACCESS command can be used to prevent other users from updating a specific

table.

ANS: F PTS: 1 REF: 138

7. When the INSERT command is used to add rows to a table, the column names of the table must be

specified.

ANS: F PTS: 1 REF: 139

8. The data being inserted into a table are listed in the ADD clause of the INSERT command.

ANS: F PTS: 1 REF: 139

9. If data is only being added to some of the columns in a table, the names of the columns must be

listed in the VALUES clause.

ANS: F PTS: 1 REF: 139

10. If non-numeric data is being added to a column, the data must be enclosed in double quotation

marks.

ANS: F PTS: 1 REF: 139

11. When data is added to a database table, it is automatically converted to upper-case letters.

ANS: F PTS: 1 REF: 139

112. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. If no value is assigned to a column when a row is added to a table, the column will contain a

NULL value.

ANS: T PTS: 1 REF: 141

The SYSDATE keyword cannot be included in the INSERT command, only the UPDATE

command.

ANS: F PTS: 1 REF: 142

If one of the data values being entered into a table by the INSERT command violates an existing

constraint, the remaining portion of the row will be added, but the data violating the constraint

will need to be added at a later time using the UPDATE command.

ANS: F PTS: 1 REF: 152

A subquery can be used with the INSERT command to enter data from an existing table into the

destination table.

ANS: T PTS: 1 REF: 148

The VALUES clause is not included with the INSERT command if the data is being retrieved

from another table.

ANS: T PTS: 1 REF: 148

When a subquery is used in the INSERT command, the subquery does not have to be enclosed in

parentheses.

ANS: T PTS: 1 REF: 148

The SET clause of the UPDATE command is used to identify the column(s) being updated.

ANS: T PTS: 1 REF: 150

If the WHERE clause is omitted from the UPDATE command, then all columns in the table will

be changed.

ANS: F PTS: 1 REF: 150

Single-row functions cannot be included in the UPDATE command.

ANS: F PTS: 1 REF: 150

When Oracle11g encounters a substitution variable, the user will be prompted for the actual value

to be assigned to the variable.

ANS: T PTS: 1 REF: 152

Commands used to modify data are known as DML commands.

ANS: T PTS: 1 REF: 138

223. An explicit commit is issued when the user executes a COMMIT; command.

ANS: T PTS: 1 REF: 158

24. A transaction is defined as the set of statements that are committed at one time.

ANS: T PTS: 1 REF: 157

25. The WHERE clause of the DELETE command is used to identify the rows to be deleted from the

table.

ANS: T PTS: 1 REF: 156

26. If the WHERE clause of the DELETE command is omitted, then all rows in the referenced table

will be removed.

ANS: T PTS: 1 REF: 156

27. If a user has a shared lock on a table, this will prevent any other user from obtaining a shared or

exclusive lock on the same table.

ANS: F PTS: 1 REF: 162

28. A shared lock prevents another user from performing DDL or DML operations on the table.

ANS: F PTS: 1 REF: 162

29. A deadlock occurs when two users hold shared locks on portions of a table that are needed to

complete the transaction of the other user.

ANS: T PTS: 1 REF: 162

30. A shared lock is automatically obtained when the SELECT…FOR UPDATE command is

executed.

ANS: T PTS: 1 REF: 162

MODIFIED TRUE/FALSE

1. The COMMIT command is used to add new rows to a table. _________________________

ANS: F, INSERT

PTS: 1 REF: 138

2. The UPDATE command can be used to change data stored in a table.

_________________________

ANS: T PTS: 1 REF: 138

33. 4. 5. 6. 7. 8. 9. 10. 11. The DROP command can be used to remove rows from an existing table.

_________________________

ANS: F, DELETE

PTS: 1 REF: 138

After a COMMIT command is executed, the ROLLBACK command will have no affect on the

changed data. _________________________

ANS: T PTS: 1 REF: 138

The SELECT…FOR UPDATE can be used to create a shared lock. _________________________

ANS: T PTS: 1 REF: 138

If more than one data value is being added to a table, the values must be separated by parentheses.

_________________________

ANS: F, commas

PTS: 1 REF: 139

Data that is being added to a table is specified in the VALUES clause of the INSERT command.

_________________________

ANS: T PTS: 1 REF: 139

A NULL value can be included in the data being added to a table by explicitly entering the word

NULL. _________________________

ANS: T PTS: 1 REF: 141

When the column names are listed in the INSERT INTO clause of the INSERT command, the

order of the names must exactly match the order of the data values in the UPDATE clause.

_________________________

ANS: F, VALUES

PTS: 1 REF: 139

The SYSDATE keyword can be used in the INSERT command to enter the computer’s system date

when adding a row to a table. _________________________

ANS: T PTS: 1 REF: 142

When including a subquery in an INSERT command, the syntax of enclosing the subquery in

parentheses is mandatory. _________________________

ANS: F, optional

PTS: 1 REF: 148

412. 13. 14. 15. 16. 17. 18. 19. 20. The VALUES clause of the UPDATE command is used to specify the columns being updated and

the new data values. _________________________

ANS: F, SET

PTS: 1 REF: 150

If the SET clause of the UPDATE command is omitted, then all rows in the table will be updated

with the new data value. _________________________

ANS: F, WHERE

PTS: 1 REF: 150

The WHERE clause of the UPDATE command is used to specify exactly which rows should be

changed. _________________________

ANS: T PTS: 1 REF: 150

The variable name of a substitution variable is preceded by a(n) percent sign.

_________________________

ANS: F, ampersand

PTS: 1 REF: 152

Transaction control statements are used to either save modified data or to undo changes before

they are committed. _________________________

ANS: T PTS: 1 REF: 157

A(n) implicit commit is issued when the user enters and executes COMMIT; in SQL*Plus.

_________________________

ANS: F, explicit

PTS: 1 REF: 158

A COMMIT is explicitly issued when the user exits SQL*Plus. _________________________

ANS: F, implicitly

PTS: 1 REF: 158

If the WHERE clause is omitted from the DELETE command, then all columns from the database

table will be dropped. _________________________

ANS: F, rows

PTS: 1 REF: 156

A table can be locked in SHARE MODE or EXCLUSIVE MODE. _________________________

ANS: T PTS: 1 REF: 162

521. A(n) exclusive lock prevents other users from changing the data stored in the table.

_________________________

ANS: T PTS: 1 REF: 162

22. A(n) shared lock prevents other users from obtaining another shared lock on the same table.

_________________________

ANS: F, exclusive

PTS: 1 REF: 162

23. When DDL operations are performed, Oracle11g will automatically place a(n) shared lock on the

table. _________________________

ANS: F, exclusive

PTS: 1 REF: 162

24. A lock is automatically released when the user issues a transaction control statement such as

COMMIT or ROLLBACK. _________________________

ANS: T PTS: 1 REF: 162

25. A(n) exclusive lock is automatically obtained when the user executes the SELECT…FOR

UPDATE command. _________________________

ANS: F, shared

PTS: 1 REF: 163

MULTIPLE CHOICE

1. Which of the following commands is used to add rows to a table?

a. ADD c. INSERT

b. UPDATE d. ENTER

ANS: C PTS: 1 REF: 138

2. Which of the following commands is used to modify existing data in a table?

a. ADD c. INSERT

b. UPDATE d. MODIFY

ANS: B PTS: 1 REF: 138

3. Which of the following commands is used to add data to an existing row in a table?

a. ADD c. INSERT

b. UPDATE d. MODIFY

ANS: B PTS: 1 REF: 138

4. Which command is used to prevent other users from making changes to a table?

a. COMMIT c. BLOCK

b. COMMIT TABLE d. LOCK TABLE

ANS: D PTS: 1 REF: 138

65. 6. 7. 8. 9. 10. 11. 12. Which of the following commands is used to remove rows from a table?

a. DELETE c. REMOVE

b. DROP d. MODIFY

ANS: A PTS: 1 REF: 138

Which keyword permanently saves changed data in a table?

a. COMMIT c. UPDATE

b. SAVE d. ADD

ANS: A PTS: 1 REF: 138

Which keywords create a shared lock on a table to prevent another user from making changes to

data in specified columns?

a. SELECT…LOCK c. SELECT…FOR UPDATE

b. SELECT…COMMIT d. SELECT…FOR CHANGE

ANS: C PTS: 1 REF: 138

Which of the following commands allows a user to “undo” uncommitted changes to data?

a. UNDO c. UNCOMMIT

b. ROLLBACK d. UNLOCK

ANS: B PTS: 1 REF: 138

Which of the following is a valid statement?

a. When rows are added to a table, the column names can be omitted if an entry is only

being made into the first column.

b. When rows are added to a table, the column names can be omitted if the values are listed

in the same order as the columns are listed in the table.

c. If rows are being added to a table with the UPDATE command, an entry must be made

into each column contained in the table.

d. none of the above

ANS: B PTS: 1 REF: 139

When new rows are being added to a table, the actual data being added are listed in the ____

clause.

a. INSERT INTO c. VALUES

b. DATA d. ADD

ANS: C PTS: 1 REF: 139

If more than one column is listed in the INSERT INTO clause, the column names must be

separated by ____.

a. commas b. single quotation marks c. double quotation marks

d. parentheses

ANS: A PTS: 1 REF: 139

Which of the following will allow a user to enter a NULL value using the INSERT command?

a. b. Omit the column from the column list in the INSERT INTO clause.

Substitute two single quotation marks in the VALUES clause in the position of the

column that is to be assigned the NULL value.

c. Use the NULL keyword.

d. all of the above

ANS: D PTS: 1 REF: 141

713. 14. 15. 16. 17. 18. 19. Which of the following statements about the INSERT keyword is incorrect?

a. b. c. d. The keywords INSERT INTO are followed by the table name.

The VALUES clause identifies the data values that will be inserted into the table.

Oracle11g does not allow column names to be omitted from the INSERT INTO clause.

If more than one column is listed, column names must be separated by commas.

ANS: C PTS: 1 REF: 139

Regarding the INSERT statement, which of the following is correct?

a. b. c. Non-numeric data is enclosed in single quotation marks.

Column names are enclosed in parentheses ( ).

The data values to be inserted are enclosed in parentheses ( ) after the VALUES

keyword.

d. all of the above

ANS: C PTS: 1 REF: 139

When the INSERT command is being used to enter data into a non-numeric column, the data must

be enclosed in ____.

a. parentheses b. double quotation marks c. single quotation marks

d. upper-case letters

ANS: C PTS: 1 REF: 139

When data is being entered into a VARCHAR2 or CHAR column using the INSERT command,

the data will be stored in ____.

a. upper-case characters

b. lower-case characters

c. the default case setting for the table

d. the same case used in the INSERT command

ANS: D PTS: 1 REF: 139

Use the ____ keyword to enter the computer’s date as a data value in the INSERT command.

a. SYSTEMDATE c. SYSDATE

b. DATE d. COMPDATE

ANS: C PTS: 1 REF: 142

When inserting a row into a table, how can you indicate that a row contains a NULL value?

a. b. In the VALUES clause, substitute two single quotation marks for the NULL value.

In the VALUES clause, include the keyword NULL in the position where the value

should be listed.

c. In the VALUES clause, include a blank space in the position where the value should be

listed.

d. both a and b

ANS: D PTS: 1 REF: 141

Which of the following keywords is omitted from the INSERT command if the data to be added to

a table is already contained in another table?

a. INSERT c. VALUES

b. INTO d. none of the above

ANS: C PTS: 1 REF: 148

820. 21. 22. 23. 24. 25. What will happen if you try to use the INSERT command to insert a NULL value into a column

that has been assigned a PRIMARY KEY constraint?

a. b. c. d. The command will execute because a PRIMARY KEY value can be NULL.

An error message is returned and the row is not added to the table.

The command will execute because only a UNIQUE constraint forbids NULL values.

An error message is returned but the unaffected portion of the row is added to the table.

ANS: B PTS: 1 REF: 152

Which of the following is not a valid SQL command?

a. INSERT INTO acctmantable

SELECT amid, amname, amedate, region

FROM acctmanager

WHERE amedate <= SYSDATE;

b. INSERT INTO acctmantable

(SELECT amid, amname, amedate, region

FROM acctmanager

WHERE amedate <= SYSDATE);

c. INSERT INTO acctmantable

AS (SELECT amid, amname, amedate, region

FROM acctmanager

WHERE amedate <= SYSDATE);

d. none of the above

ANS: C PTS: 1 REF: 148

If a table already exists, what command would you issue to add copies of existing rows from one

table to the other?

a. b. c. INSERT command with an UPDATE clause

INSERT command with a SET clause

INSERT command with a subquery

d. none of the above

ANS: C PTS: 1 REF: 148

The column to be updated by the UPDATE command is specified in the ____ clause.

a. UPDATE c. WHERE

b. SET d. COL

ANS: B PTS: 1 REF: 150

The row(s) to be updated by the UPDATE command is specified by the ____ clause.

a. UPDATE c. WHERE

b. SET d. COL

ANS: C PTS: 1 REF: 150

Which of the following clauses of the UPDATE command is optional?

a. UPDATE c. WHERE

b. SET d. none of the above

ANS: C PTS: 1 REF: 150

926. 27. 28. 29. 30. 31. 32. If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table

will be changed.

a. UPDATE c. WHERE

b. SET d. none of the above

ANS: C PTS: 1 REF: 150

Which of the following statements about the UPDATE command is incorrect?

a. b. The UPDATE clause identifies the table containing the record(s) to be changed.

The SET clause is used to identify the column(s) to be changed, and the new value(s) to

be assigned to that column.

c. The mandatory WHERE clause identifies the specific row(s) to be changed by the

UPDATE command.

d. The optional WHERE clause identifies the specific row(s) to be changed by the

UPDATE command.

ANS: C PTS: 1 REF: 150

Which of the following is not a valid SQL command?

a. b. c. d. UPDATE acctmanager SET amedate = SYSDATE WHERE amid = ‘J500’;

UPDATE acctmanager SET amname = UPPER(amname);

UPDATE acctmanager SET amname = UPPER(amname) WHERE amid = ‘J500’;

UPDATE acctmanager WHERE amid = ‘J500’;

ANS: D PTS: 1 REF: 150

A(n) ____ in a SQL command instructs Oracle11g to use a substituted value in place of the

variable at the time the command is actually executed.

a. substitution value c. substitution variable

b. substitution clause d. substitution condition

ANS: C PTS: 1 REF: 152

Which of the following statements about substitution variables is incorrect?

a. They allow you to add records to a table without issuing the same command again and

again.

b. c. A substitution variable is identified by an ampersand (&) and the variable name.

When Oracle10g executes a command with a substitution variable, it will prompt the

user to enter a value.

d. The command SET VERIFY OFF will delete all values stored in substitution variables.

ANS: D PTS: 1 REF: 152

Commands used to modify data are called ____ commands.

a. data control language (DCL) c. data modification language (DML)

b. data manipulation language (DML) d. data definition language (DDL)

ANS: B PTS: 1 REF: 138

A user who is issuing DML commands can save modified data or undo uncommitted changes by

issuing ____ statements.

a. transaction control b. database control c. data manipulation

d. rollback control

ANS: A PTS: 1 REF: 157

1033. 34. 35. 36. 37. 38. 39. When does a COMMIT command explicitly occur?

a. b. c. d. When the user executes COMMIT;.

When the user issues a DDL command such as CREATE or ALTER TABLE.

When the user executes ROLLBACK;.

When the user exists the system.

ANS: A PTS: 1 REF: 158

Which of the following statements about COMMIT and ROLLBACK commands is incorrect?

a. All DML commands (INSERT, UPDATE, DELETE) are explicitly committed and

cannot be rolled back.

b. All DDL commands (CREATE, TRUNCATE, ALTER TABLE) are explicitly

committed and cannot be rolled back.

c. A ROLLBACK command will reverse all DML operations performed since the last

COMMIT was performed.

d. all of the above

ANS: D PTS: 1 REF: 157-158

When does a COMMIT command implicitly occur?

a. b. c. d. When the user executes COMMIT;.

When the user issues a DDL command such as CREATE or ALTER TABLE.

When the user executes ROLLBACK;.

When the computer loses power unexpectedly.

ANS: B PTS: 1 REF: 158

The effect of which of the following commands can never be reversed by the ROLLBACK

command?

a. CREATE TABLE c. COMMIT

b. ALTER TABLE d. all of the above

ANS: D PTS: 1 REF: 158

Which of the following statements about the DELETE command is incorrect?

a. The DELETE command applies to an entire row and cannot be applied to specific

columns within a row.

b. c. d. The proper command syntax for the DELETE command is DELETE FROM tablename;

If you omit the optional WHERE clause, then all the rows in the table will be deleted.

If you omit the mandatory WHERE clause, an error message will be issued.

ANS: D PTS: 1 REF: 156

The ____ command will prevent two users from trying to make changes to the same table at the

same time.

a. SHARED LOCK b. EXCLUSIVE LOCK c. LOCK TABLE

d. TABLE LOCK

ANS: C PTS: 1 REF: 162

Which of the following types of locks permits other users access to unlocked portions of a table?

a. shared lock b. table lock c. exclusive lock

d. partition lock

ANS: A PTS: 1 REF: 162

1140. 41. 42. 43. 44. The ____ command can be used to view the contents of a record when it is anticipated that the

record will need to be modified. It places a shared lock on the record(s) to be changed and

prevents any other user from acquiring a lock on the same record(s).

a. SELECT…LOCK TABLE c. SELECT…FOR UPDATE

b. COMMIT…LOCK TABLE d. COMMIT…FOR UPDATE

ANS: C PTS: 1 REF: 163

A(n) ____ lock will prevent any DDL operations from being performed on the locked table.

a. shared c. partial

b. exclusive d. partitioned

ANS: B PTS: 1 REF: 162

A lock arising from a SELECT…FOR UPDATE command will be released when ____.

a. a COMMIT command is executed c. an implicit commit occurs

b. a ROLLBACK command is executed d. all of the above

ANS: A PTS: 1 REF: 163

Contents of the PROMOTION table

Based on the contents of the PROMOTION table, which of the following will correctly change the

value assigned to the MAXRETAIL column for Free Shipping to 75.00?

a. INSERT INTO promotion (maxretail)

VALUES (75)

WHERE gift = ‘FREE SHIPPING’;

b. UPDATE promotion (maxretail)

SET = 75

WHERE gift = ‘FREE SHIPPING’;

c. UPDATE promotion

SET maxretail = 75

WHERE gift = ‘FREE SHIPPING’;

d. none of the above

ANS: C PTS: 1 REF: 150

Based on the contents of the PROMOTION table, which of the following commands will delete

only the row for the Free Bookmark from the table?

a. DELETE FROM promotion;

b. DELETE gift FROM promotion;

c. DELETE gift FROM promotion WHERE gift = ‘BOOKMARKER’;

d. DELETE FROM promotion WHERE gift = ‘BOOKMARKER’;

ANS: D PTS: 1 REF: 156

1245. 46. 47. 48. Which of the following SQL statements will insert a new row into the PROMOTION table?

a. INSERT INTO promotion (gift, minretail, maxretail)

VALUES (FREE BOOK, 75.01, 89.99);

b. INSERT INTO promotion (gift, minretail, maxretail)

VALUES (‘FREE BOOK’, 75.01, 89.99);

c. INSERT INTO promotion VALUES (FREE BOOK, 75.01, 89.99);

d. both a and c

ANS: B PTS: 1 REF: 139

If a new row is added to the PROMOTION table, which of the following will make the change

permanent?

a. COMMIT;

b. UPDATE;

c. ROLLBACK;

d. The change is permanent when the command is executed.

ANS: A PTS: 1 REF: 158

Contents of the PUBLISHER table

Which of the following commands will delete only publisher 4 from the PUBLISHER table?

a. DELETE FROM publisher;

b. c. d. DELETE pubid = 4 FROM publisher;

DROP FROM publisher WHERE pubid = 4;

DELETE FROM publisher WHERE pubid = 4;

ANS: D PTS: 1 REF: 156

Based on the contents of the PUBLISHER table, which of the following SQL statements will

change the phone number for Printing Is Us to 800-714-8321?

a. UPDATE publisher REPLACE phone WITH ‘800-714-8321’

WHERE pubid = 1;

b. UPDATE publisher REPLACE phone = ‘800-714-8321’

WHERE pubid = 1;

c. UPDATE publisher SET phone = ‘800-714-8321’

WHERE pubid = 1;

d. none of the above

ANS: C PTS: 1 REF: 150

1349. Based on the contents of the PUBLISHER table, which of the following will add a new record to

the table?

a. INSERT INTO publisher

VALUES (‘BOOKS MADE CHEAP’, ‘800-111-2222’);

b. INSERT INTO publisher (pubid, name)

VALUES (6, ‘BOOKS MADE CHEAP’);

c. UPDATE publisher

VALUES (‘BOOKS MADE CHEAP’, ‘800-111-2222’);

d. UPDATE publisher (pubid, name)

VALUES (6, ‘BOOKS MADE CHEAP’);

ANS: B PTS: 1 REF: 139

COMPLETION

1. The ____________________ command is used to add new rows to a table.

ANS: INSERT

PTS: 1 REF: 138

2. The ____________________ command is used to add data to an existing row in a table.

ANS: UPDATE

PTS: 1 REF: 138

3. The ____________________ command is used to change data stored in a table.

ANS: UPDATE

PTS: 1 REF: 138

4. The ____________________ command is used to permanently update a table and allow other

users to view the changed data.

ANS: COMMIT

PTS: 1 REF: 138

5. The ____________________ command is used to “undo” changes that have not yet been

committed.

ANS: ROLLBACK

PTS: 1 REF: 138

6. The ____________________ command is used to remove rows from a table.

ANS: DELETE

PTS: 1 REF: 138

147. 8. 9. 10. 11. 12. 13. 14. The ____________________ TABLE command is used to prevent other users from making

changes to a portion of a table.

ANS: LOCK

PTS: 1 REF: 138

The SELECT …. ____________________ command is used to create a shared lock on a table to

prevent other users from making changes in the specified column.

ANS: FOR UPDATE

PTS: 1 REF: 138

The data values being added to a table with the INSERT command must be listed in the

____________________ clause.

ANS: VALUES

PTS: 1 REF: 139

When non-numeric data is being added to a table, the data must be enclosed in

____________________ quotation marks.

ANS: single

PTS: 1 REF: 139

The ____________________ keyword can be included in the INSERT command to enter the

computer’s date into the row when it is added to the table.

ANS: SYSDATE

PTS: 1 REF: 142

The ____________________ keyword is not included in the INSERT command if the data is

being retrieved from another database table.

ANS: VALUES

PTS: 1 REF: 139

When the UPDATE command is used to change existing data values, the ____________________

clause is used to specify the new data value and the column being changed.

ANS: SET

PTS: 1 REF: 150

If a(n) ____________________ clause is not included in the UPDATE command, then all rows in

the table will be changed.

ANS: WHERE

PTS: 1 REF: 150

1515. 16. 17. 18. 19. 20. 21. A substitution variable can be identified by the ____________________ symbol that precedes the

variable name.

ANS:

&

ampersand

PTS: 1 REF: 152

______________________________ commands are used to modify data.

ANS:

DML

Data manipulation language

PTS: 1 REF: 138

The duration of a(n) ____________________ is defined by when a COMMIT implicitly or

explicitly occurs.

ANS: transaction

PTS: 1 REF: 157

The ____________________ command will reverse all DML operators performed since the last

COMMIT was performed.

ANS: ROLLBACK

PTS: 1 REF: 158

Rows can be deleted from a table using the ____________________ command.

ANS: DELETE

PTS: 1 REF: 156

The rows to be deleted from a table are identified by the ____________________ clause of the

DELETE command.

ANS: WHERE

PTS: 1 REF: 156

A(n) ____________________ lock will allow other users to view the data stored in the table, but

it prevents anyone from altering the structure of the table.

ANS:

shared

share

PTS: 1 REF: 162

1622. If a user has a shared lock on a table, this prevents another user from obtaining a(n)

____________________ lock on the same table.

ANS: exclusive

PTS: 1 REF: 162

23. When ____________________ operations are performed, an exclusive lock is automatically

placed on the specified table.

ANS: DDL

PTS: 1 REF: 162

24. ____________________ are automatically released when the user exits the system or executes a

COMMIT or ROLLBACK command.

ANS: Locks

PTS: 1 REF: 162

25. The SELECT…FOR UPDATE command will automatically obtain a(n) ____________________

lock on the row to be changed.

ANS:

shared

SHARE

PTS: 1 REF: 163

ESSAY

1. Explain the difference between an implicit and an explicit COMMIT.

ANS:

An explicit COMMIT occurs when the user executes a COMMIT command. An implicit commit

occurs when the user properly exits SQL*Plus or issues a DDL command, such as the CREATE or

ALTER TABLE commands.

PTS: 1 REF: 158

2. What is the difference between a standard SELECT statement and a SELECT…FOR UPDATE

statement?

ANS:

The purpose of the standard SELECT statement is to retrieve data. Although the SELECT…FOR

UPDATE command also retrieves data, it will place a shared lock on the row(s) retrieved because

the command is issued with the expectation that the user will be updating one or more of the rows

retrieved. The lock will not be released until the user exits the database or executes a COMMIT or

ROLLBACK command.

PTS: 1 REF: 163

173. What is the difference between a shared and exclusive lock?

ANS:

A shared lock allows other users to view the data stored in the table, but prevents DDL commands

from being executed or exclusive locks from being obtained for the same table. An exclusive lock

prevents anyone from obtaining a shared or exclusive lock on the table so no DML or DDL

commands can be executed for the table by other users until the lock is released.

PTS: 1 REF: 162

18