Deck 5: Data Manipulation and Transaction Control

Question 1

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

Answer 1

False

Question 2

The VALUES clause is not included with the INSERT command if the data is being retrieved from another table.

Answer 2

True

Question 3

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

Answer 3

False

Question 4

The ROLLBACK command is used to permanently save changed data.

Question 5

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

Question 6

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

Question 7

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

Question 8

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

Question 9

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

Question 10

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

Question 11

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

Question 12

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

Question 13

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

Question 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.

Question 15

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

Question 16

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

Question 17

If no value is assigned to a column when a row is added to a table, the column will contain a NULL value.

Question 18

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

Question 19

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.

Question 20

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

Question 21

A $\underline { \text { NULL} }$ value can be included in the data being added to a table by explicitly entering the word NULL. _________________________

Question 22

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

Question 23

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

Question 24

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

Question 25

The $\underline { \text { SELECT ..FOR UPDATE } }$ can be used to create a shared lock. _________________________

Question 26

After a COMMIT command is executed, the $\underline { \text { ROLLBACK } }$ command will have no affect on the changed data. _________________________

Question 27

The $\underline { \text { DROP } }$ command can be used to remove rows from an existing table. _________________________

Question 28

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

Question 29

If more than one data value is being added to a table, the values must be separated by $\underline {\text { parentheses. } }$ _________________________

Question 30

The $\underline { \text { COMMIT } }$ command is used to add new rows to a table. _________________________

Question 31

The $\underline { \text { UPDATE } }$ command can be used to change data stored in a table. _________________________

Question 32

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 $\underline { \text { UPDATE } }$ clause. _________________________

Question 33

Data that is being added to a table is specified in the $\underline { \text { VALUES } }$ clause of the INSERT command. _________________________

Question 34

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.

Question 35

Commands used to modify data are known as DML commands.

Question 36

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.

Question 37

The $\underline { \text { SYSDATE } }$ keyword can be used in the INSERT command to enter the computer's system date when adding a row to a table. _________________________

Question 38

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

Question 39

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

Question 40

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

Question 41

A(n) $\underline {\text { shared } }$ lock prevents other users from obtaining another shared lock on the same table. _________________________

Question 42

The $\underline {\text { WHERE } }$ clause of the UPDATE command is used to specify exactly which rows should be changed. _________________________

Question 43

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

A) COMMIT
B) COMMIT TABLE
C) BLOCK
D) LOCK TABLE

Question 44

If the $\underline { \text { SET } }$ clause of the UPDATE command is omitted, then all rows in the table will be updated with the new data value. _________________________

Question 45

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

A) ADD
B) UPDATE
C) INSERT
D) MODIFY

Question 46

A table can be locked in $\underline { \text { SHARE MODE } }$ or EXCLUSIVE MODE. _________________________

Question 47

A(n) $\underline { \text { exclusive lock } }$ prevents other users from changing the data stored in the table. _________________________

Question 48

If the WHERE clause is omitted from the DELETE command, then all columns from the database table will be dropped. _________________________

Question 49

The variable name of a substitution variable is preceded by a(n) $\underline { \text { percent sign. } }$ _________________________

Question 50

The $\underline { \text { VALUES } }$ clause of the UPDATE command is used to specify the columns being updated and the new data values. _________________________

Question 51

$\underline {\text { Transaction control }}$ statements are used to either save modified data or to undo changes before they are committed. _________________________

Question 52

A COMMIT is $\underline { \text { explicitly } }$ issued when the user exits SQL*Plus. _________________________

Question 53

Which of the following commands is used to remove rows from a table?

A) DELETE
B) DROP
C) REMOVE
D) MODIFY

Question 54

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

A) ADD
B) UPDATE
C) INSERT
D) ENTER

Question 55

When including a subquery in an INSERT command, the syntax of enclosing the subquery in parentheses is $\underline { \text { nandatory } }$ _________________________

Question 56

A(n) $\underline {\text { implicit } }$ commit is issued when the user enters and executes COMMIT; in SQL*Plus. _________________________

Question 57

A lock is automatically released when the user issues a transaction control statement such as COMMIT or $\underline { \text { ROLLBACK } }$ . _________________________

Question 58

A(n) $\underline { \text { exclusive } }$ lock is automatically obtained when the user executes the SELECT...FOR UPDATE command. _________________________

Question 59

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

A) ADD
B) UPDATE
C) INSERT
D) MODIFY

Question 60

When DDL operations are performed, Oracle11g will automatically place a(n) $\underline {\text { shared } }$ lock on the table. _________________________

Question 61

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

Question 62

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

Question 63

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

Question 64

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

A) INSERT INTO
B) DATA
C) VALUES
D) ADD

Question 65

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

A) UPDATE
B) SET
C) WHERE
D) COL

Question 66

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

A) UPDATE
B) SET
C) WHERE
D) COL

Question 67

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

Question 68

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

Question 69

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
B) INTO
C) VALUES
D) none of the above

Question 70

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

Question 71

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

Question 72

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

A) SYSTEMDATE
B) DATE
C) SYSDATE
D) COMPDATE

Question 73

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

A) UPDATE
B) SET
C) WHERE
D) none of the above

Question 74

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.

Question 75

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

Question 76

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

Question 77

Which keyword permanently saves changed data in a table?

A) COMMIT
B) SAVE
C) UPDATE
D) ADD

Question 78

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.

Question 79

Which keywords create a shared lock on a table to prevent another user from making changes to data in specified columns?

A) SELECT...LOCK
B) SELECT...COMMIT
C) SELECT...FOR UPDATE
D) SELECT...FOR CHANGE

Question 80

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

A) UNDO
B) ROLLBACK
C) UNCOMMIT
D) UNLOCK