Data Manipulation and Transaction Control
The ____________________ command is used to remove rows from a table.
DELETE
Contents of the PROMOTION table 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?
DELETE FROM promotion WHERE gift = 'BOOKMARKER';
Rows can be deleted from a table using the ____________________ command.
DELETE
Contents of the PROMOTION table If a new row is added to the PROMOTION table, which of the following will make the change permanent?
COMMIT
The ____________________ command is used to permanently update a table and allow other users to view the changed data.
COMMIT
Which keyword permanently saves changed data in a table?
COMMIT
The SELECT .... ____________________ command is used to create a shared lock on a table to prevent other users from making changes in the specified column.
FOR UPDATE
The ____________________ command is used to add new rows to a table.
INSERT
Which of the following commands is used to add rows to a table?
INSERT
Which of the following is not a valid SQL command?
INSERT INTO acctmantable AS (SELECT amid, amname, amedate, region FROM acctmanager WHERE amedate <= SYSDATE);
Contents of the PROMOTION table Which of the following SQL statements will insert a new row into the PROMOTION table?
INSERT INTO promotion (gift, minretail, maxretail) VALUES ('FREE BOOK', 75.01, 89.99);
Contents of the PUBLISHER table Based on the contents of the PUBLISHER table, which of the following will add a new record to the table?
INSERT INTO publisher (pubid, name) VALUES (6, 'BOOKS MADE CHEAP');
The ____________________ TABLE command is used to prevent other users from making changes to a portion of a table.
LOCK
The ____ command will prevent two users from trying to make changes to the same table at the same time.
LOCK TABLE
Which command is used to prevent other users from making changes to a table?
LOCK TABLE
The ____________________ command is used to "undo" changes that have not yet been committed.
ROLLBACK
The ____________________ command will reverse all DML operators performed since the last COMMIT was performed.
ROLLBACK
Which of the following commands allows a user to "undo" uncommitted changes to data?
ROLLBACK
Contents of the PUBLISHER table If a column named SAL_PCT is to be defined to ensure a value of 100 is input if a NULL value is provided when a new row is inserted, which column definition should be used?
SAL_PCT DEFAULT ON NULL 100.
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).
SELECT...FOR UPDATE
Which keywords create a shared lock on a table to prevent another user from making changes to data in specified columns?
SELECT...FOR UPDATE
Use the ____ keyword to enter the computer's date as a data value in the INSERT command.
SYSDATE
The ____________________ command is used to add data to an existing row in a table.
UPDATE
The ____________________ command is used to change data stored in a table.
UPDATE
Which of the following commands is used to modify existing data in a table?
UPDATE
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?
UPDATE promotion SET maxretail = 75 WHERE gift = 'FREE SHIPPING';
Contents of the PUBLISHER table 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?
UPDATE publisher SET phone = '800-714-8321' WHERE pubid = 1;
When does a COMMIT command explicitly occur?
When the user executes COMMIT
A lock arising from a SELECT...FOR UPDATE command will be released when ____.
a COMMIT command is executed
Which of the following statements about COMMIT and ROLLBACK commands is incorrect?
all of the above
Which of the following will allow a user to enter a NULL value using the INSERT command?
all of the above
A substitution variable can be identified by the ____________________ symbol that precedes the variable name.
ampersand
If more than one column is listed in the INSERT INTO clause, the column names must be separated by ____.
commas
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?
values
A(n) ____ lock will prevent any DDL operations from being performed on the locked table.
exclusive
If a user has a shared lock on a table, this prevents another user from obtaining a(n) ____________________ lock on the same table.
exclusive
A COMMIT is explicitly issued when the user exits SQL*Plus. _________________________
false
A shared lock prevents another user from performing DDL or DML operations on the table.
false
A value must be provided in an INSERT statement for a virtual column.
false
A(n) exclusive lock is automatically obtained when the user executes the SELECT...FOR UPDATE command. _________________________
false
When the INSERT command is being used to enter data into a non-numeric column, the data must be enclosed in ____.
single quotation marks
A(n) ____ in a SQL command instructs Oracle 12c to use a substituted value in place of the variable at the time the command is actually executed.
substitution variable
When data is being entered into a VARCHAR2 or CHAR column using the INSERT command, the data will be stored in ____.
the same case used n the INSERT command
The duration of a(n) ____________________ is defined by when a COMMIT implicitly or explicitly occurs.
transaction
A user who is issuing DML commands can save modified data or undo uncommitted changes by issuing ____ statements.
transaction control
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?
An error message is returned and the row is not added to the table.
Which of the following statements about the DELETE command is incorrect?
If you omit the mandatory WHERE clause, an error message will be issued.
Contents of the PUBLISHER table If a column named SAL_PCT is to be defined to ensure a value of 100 is input if a NULL value is provided when a new row is inserted, which column definition should be used?
SAL_PCT DEFAULT ON NULL 100.
When does a COMMIT command implicitly occur?
When the user issues a DDL command such as CREATE or ALTER TABLE.
When ____________________ operations are performed, an exclusive lock is automatically placed on the specified table.
DDL
______________________________ commands are used to modify data.
DML or Data manipulation language
If a table already exists, what command would you issue to add copies of existing rows from one table to the other?
INSERT command with a subquery
The column to be updated by the UPDATE command is specified in the ____ clause.
SET
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.
SET
When new rows are being added to a table, the actual data being added are listed in the ____ clause.
VALUES
If a(n) ____________________ clause is not included in the UPDATE command, then all rows in the table will be changed.
WHERE
If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.
WHERE
The rows to be deleted from a table are identified by the ____________________ clause of the DELETE command.
WHERE
Which of the following clauses of the UPDATE command is optional?
WHERE
A(n) implicit commit is issued when the user enters and executes COMMIT; in SQL*Plus. _________________________
false
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.
false
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.
false
If the SET clause of the UPDATE command is omitted, then all rows in the table will be updated with the new data value. _________________________
false
If the WHERE clause is omitted from the DELETE command, then all columns from the database table will be dropped. _________________________
false
If the WHERE clause is omitted from the UPDATE command, then all columns in the table will be changed.
false
The COMMIT command is used to add new rows to a table. _________________________
false
The DROP command can be used to remove rows from an existing table. _________________________
false
The INSERT command is used to modify existing data in a row.
false
The MODIFY command can be used to change existing data stored in a table.
false
The ROLLBACK command is used to permanently save changed data.
false
The SYSDATE keyword cannot be included in the INSERT command, only the UPDATE command.
false
When DDL operations are performed, Oracle 12c will automatically place a(n) shared lock on the table. _________________________
false
When data is added to a database table, it is automatically converted to upper-case letters.
false
When including a subquery in an INSERT command, the syntax of enclosing the subquery in parentheses is mandatory. _________________________
false
When the INSERT command is used to add rows to a table, the column names of the table must be specified.
false
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. _________________________
false
The data being inserted into a table are listed in the ADD clause of the INSERT command.
false
____________________ are automatically released when the user exits the system or executes a COMMIT or ROLLBACK command.
locks
Which of the following types of locks permits other users access to unlocked portions of a table?
shared lock
The SELECT...FOR UPDATE command will automatically obtain a(n) ____________________ lock on the row to be changed.
shared or SHARE
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.
shared or share
When non-numeric data is being added to a table, the data must be enclosed in ____________________ quotation marks.
single
A NULL value can be included in the data being added to a table by explicitly entering the word NULL. _________________________
true
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.
true
A lock is automatically released when the user issues a transaction control statement such as COMMIT or ROLLBACK. _________________________
true
A shared lock is automatically obtained when the SELECT...FOR UPDATE command is executed.
true
A table can be locked in SHARE MODE or EXCLUSIVE MODE. _________________________
true
A transaction is defined as the set of statements that are committed at one time.
true
A(n) exclusive lock prevents other users from changing the data stored in the table. _________________________
true
After a COMMIT command is executed, the ROLLBACK command will have no affect on the changed data. _________________________
true
An ON NULL clause may be used in conjunction with a column's DEFAULT option.
true
An explicit commit is issued when the user executes a COMMIT; command.
true
Commands used to modify data are known as DML commands.
true
Data that is being added to a table is specified in the VALUES clause of the INSERT command. _________________________
true
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.
true
If no value is assigned to a column when a row is added to a table, the column will contain a NULL value.
true
The LOCK TABLE command can be used to prevent other users from making changes to a table.
true
The SET clause of the UPDATE command is used to identify the column(s) being updated.
true
The SYSDATE keyword can be used in the INSERT command to enter the computer's system date when adding a row to a table. _________________________
true
The UPDATE command can be used to add data to existing rows in a table.
true
The UPDATE command can be used to change data stored in a table. _________________________
true
The USER_TAB_COLUMNS data dictionary object may be used to identify columns that are assigned an ON NULL clause.
true
The VALUES clause is not included with the INSERT command if the data is being retrieved from another table.
true
The WHERE clause of the DELETE command is used to identify the rows to be deleted from the table.
true
The WHERE clause of the UPDATE command is used to specify exactly which rows should be changed. _________________________
true
When Oracle 12c encounters a substitution variable, the user will be prompted for the actual value to be assigned to the variable.
true
When a subquery is used in the INSERT command, the subquery does not have to be enclosed in parentheses.
true
The ____________________ keyword is not included in the INSERT command if the data is being retrieved from another database table.
values
Which of the following statements about substitution variables is incorrect?
The command SET VERIFY OFF will delete all values stored in substitution variables.
Which of the following statements about the UPDATE command is incorrect?
The mandatory WHERE clause identifies the specific row(s) to be changed by the UPDATE command.
Which of the following is a valid statement?
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.
Commands used to modify data are called ____ commands.
data manipulation language (DML)