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

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

Instant Download – Complete Test Bank With Answers

Sample Questions Are Posted Below

1
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
2
12. 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
13. The SYSDATE keyword cannot be included in the INSERT command, only the UPDATE
command.
ANS: F PTS: 1 REF: 142
14. 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
15. 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
16. 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
17. 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
18. The SET clause of the UPDATE command is used to identify the column(s) being updated.
ANS: T PTS: 1 REF: 150
19. 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
20. Single-row functions cannot be included in the UPDATE command.
ANS: F PTS: 1 REF: 150
21. 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
22. Commands used to modify data are known as DML commands.
ANS: T PTS: 1 REF: 138
3
23. 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
4
3. The DROP command can be used to remove rows from an existing table.
_________________________
ANS: F, DELETE
PTS: 1 REF: 138
4. After a COMMIT command is executed, the ROLLBACK command will have no affect on the
changed data. _________________________
ANS: T PTS: 1 REF: 138
5. The SELECT…FOR UPDATE can be used to create a shared lock. _________________________
ANS: T PTS: 1 REF: 138
6. 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
7. Data that is being added to a table is specified in the VALUES clause of the INSERT command.
_________________________
ANS: T PTS: 1 REF: 139
8. 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
9. 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
10. 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
11. 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
5
12. 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
13. 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
14. The WHERE clause of the UPDATE command is used to specify exactly which rows should be
changed. _________________________
ANS: T PTS: 1 REF: 150
15. The variable name of a substitution variable is preceded by a(n) percent sign.
_________________________
ANS: F, ampersand
PTS: 1 REF: 152
16. Transaction control statements are used to either save modified data or to undo changes before
they are committed. _________________________
ANS: T PTS: 1 REF: 157
17. A(n) implicit commit is issued when the user enters and executes COMMIT; in SQL*Plus.
_________________________
ANS: F, explicit
PTS: 1 REF: 158
18. A COMMIT is explicitly issued when the user exits SQL*Plus. _________________________
ANS: F, implicitly
PTS: 1 REF: 158
19. 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
20. A table can be locked in SHARE MODE or EXCLUSIVE MODE. _________________________
ANS: T PTS: 1 REF: 162
6
21. 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
7
5. 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
6. Which keyword permanently saves changed data in a table?
a. COMMIT c. UPDATE
b. SAVE d. ADD
ANS: A PTS: 1 REF: 138
7. 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
8. 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
9. 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
10. 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
11. If more than one column is listed in the INSERT INTO clause, the column names must be
separated by ____.
a. commas c. double quotation marks
b. single quotation marks d. parentheses
ANS: A PTS: 1 REF: 139
12. Which of the following will allow a user to enter a NULL value using the INSERT command?
a. Omit the column from the column list in the INSERT INTO clause.
b. 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
8
13. Which of the following statements about the INSERT keyword is incorrect?
a. The keywords INSERT INTO are followed by the table name.
b. The VALUES clause identifies the data values that will be inserted into the table.
c. Oracle11g does not allow column names to be omitted from the INSERT INTO clause.
d. If more than one column is listed, column names must be separated by commas.
ANS: C PTS: 1 REF: 139
14. Regarding the INSERT statement, which of the following is correct?
a. Non-numeric data is enclosed in single quotation marks.
b. Column names are enclosed in parentheses ( ).
c. 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
15. When the INSERT command is being used to enter data into a non-numeric column, the data must
be enclosed in ____.
a. parentheses c. single quotation marks
b. double quotation marks d. upper-case letters
ANS: C PTS: 1 REF: 139
16. 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
17. 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
18. When inserting a row into a table, how can you indicate that a row contains a NULL value?
a. In the VALUES clause, substitute two single quotation marks for the NULL value.
b. 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
19. 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
9
20. 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. The command will execute because a PRIMARY KEY value can be NULL.
b. An error message is returned and the row is not added to the table.
c. The command will execute because only a UNIQUE constraint forbids NULL values.
d. An error message is returned but the unaffected portion of the row is added to the table.
ANS: B PTS: 1 REF: 152
21. 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
22. If a table already exists, what command would you issue to add copies of existing rows from one
table to the other?
a. INSERT command with an UPDATE clause
b. INSERT command with a SET clause
c. INSERT command with a subquery
d. none of the above
ANS: C PTS: 1 REF: 148
23. 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
24. 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
25. 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
10
26. 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
27. Which of the following statements about the UPDATE command is incorrect?
a. The UPDATE clause identifies the table containing the record(s) to be changed.
b. 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
28. Which of the following is not a valid SQL command?
a. UPDATE acctmanager SET amedate = SYSDATE WHERE amid = ‘J500’;
b. UPDATE acctmanager SET amname = UPPER(amname);
c. UPDATE acctmanager SET amname = UPPER(amname) WHERE amid = ‘J500’;
d. UPDATE acctmanager WHERE amid = ‘J500’;
ANS: D PTS: 1 REF: 150
29. 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
30. 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. A substitution variable is identified by an ampersand (&) and the variable name.
c. 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
31. 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
32. A user who is issuing DML commands can save modified data or undo uncommitted changes by
issuing ____ statements.
a. transaction control c. data manipulation
b. database control d. rollback control
ANS: A PTS: 1 REF: 157
11
33. When does a COMMIT command explicitly occur?
a. When the user executes COMMIT;.
b. When the user issues a DDL command such as CREATE or ALTER TABLE.
c. When the user executes ROLLBACK;.
d. When the user exists the system.
ANS: A PTS: 1 REF: 158
34. 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
35. When does a COMMIT command implicitly occur?
a. When the user executes COMMIT;.
b. When the user issues a DDL command such as CREATE or ALTER TABLE.
c. When the user executes ROLLBACK;.
d. When the computer loses power unexpectedly.
ANS: B PTS: 1 REF: 158
36. 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
37. 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. The proper command syntax for the DELETE command is DELETE FROM tablename;
c. If you omit the optional WHERE clause, then all the rows in the table will be deleted.
d. If you omit the mandatory WHERE clause, an error message will be issued.
ANS: D PTS: 1 REF: 156
38. The ____ command will prevent two users from trying to make changes to the same table at the
same time.
a. SHARED LOCK c. LOCK TABLE
b. EXCLUSIVE LOCK d. TABLE LOCK
ANS: C PTS: 1 REF: 162
39. Which of the following types of locks permits other users access to unlocked portions of a table?
a. shared lock c. exclusive lock
b. table lock d. partition lock
ANS: A PTS: 1 REF: 162
12
40. 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
41. 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
42. 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
43. 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
44. 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
13
45. 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
46. 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
47. Which of the following commands will delete only publisher 4 from the PUBLISHER table?
a. DELETE FROM publisher;
b. DELETE pubid = 4 FROM publisher;
c. DROP FROM publisher WHERE pubid = 4;
d. DELETE FROM publisher WHERE pubid = 4;
ANS: D PTS: 1 REF: 156
48. 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
14
49. 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
15
7. The ____________________ TABLE command is used to prevent other users from making
changes to a portion of a table.
ANS: LOCK
PTS: 1 REF: 138
8. 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
9. The data values being added to a table with the INSERT command must be listed in the
____________________ clause.
ANS: VALUES
PTS: 1 REF: 139
10. When non-numeric data is being added to a table, the data must be enclosed in
____________________ quotation marks.
ANS: single
PTS: 1 REF: 139
11. 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
12. 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
13. 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
14. 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
16
15. A substitution variable can be identified by the ____________________ symbol that precedes the
variable name.
ANS:
&
ampersand
PTS: 1 REF: 152
16. ______________________________ commands are used to modify data.
ANS:
DML
Data manipulation language
PTS: 1 REF: 138
17. The duration of a(n) ____________________ is defined by when a COMMIT implicitly or
explicitly occurs.
ANS: transaction
PTS: 1 REF: 157
18. The ____________________ command will reverse all DML operators performed since the last
COMMIT was performed.
ANS: ROLLBACK
PTS: 1 REF: 158
19. Rows can be deleted from a table using the ____________________ command.
ANS: DELETE
PTS: 1 REF: 156
20. The rows to be deleted from a table are identified by the ____________________ clause of the
DELETE command.
ANS: WHERE
PTS: 1 REF: 156
21. 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
17
22. 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
18
3. 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