Week 10 Flashcards
Transaction control
The commands used to modify data is called what?
A. SQL
B. DDL
C. DML
C. DML (Data manipulation language)
Match the following commands (A-H) with their descriptions (1-8).
A. INSERT 1. Allows ‘undoing’ uncommited changes
B. UPDATE 2. Adds data to/modifies existing rows
C. DELETE 3. Add new rows to table
D. COMMIT 4. Saves changed data permanently
E. ROLLBACK 5. Prevents others from making
changes to a table
F. SAVEPOINT 6. Creates shared lock to prevent
changes in specific columns
G. LOCK TABLE 7. Sets markers in a transaction
H. SELECT…FOR UPDATE 8. Removes rows
A. 3 B. 2 C. 8 D. 4 E. 1 F. 7 G. 5 H. 6
______ must enclose nonnumeric data inserted in a column.
A. double quotes
B. single quotes
C. brackets
B. single quotes
What are the ways to insert data with missing data for a column? (select all that apply)
A. List the columns after the table and omit that column name
B. Insert the word NULL where the column would be
C. Insert 2 double quotes where the column would be
D. Insert 2 single quotes where the column would be
A. List the columns after the table and omit that column name
B. Insert the word NULL where the column would be
D. Insert 2 single quotes where the column would be
True/False: You can insert data for a virtual column.
False.
If the string you need to enter has a single quote (O’hara) what must you do for it to correctly insert into the table?
A. Make is 2 single quotes
B. Nothing
C. Put double quotes around it
D. Remove the single quote to show as a single word instead
A. Make is 2 single quotes
You can copy data from one table into another using what command?
A. COPY FROM
B. COPY INTO
C. INSERT INTO
D. INSERT FROM
C. INSERT INTO
Choose the correct function to update a row.
A. UPDATE customer (lastname)
TO ‘smith’
WHERE lastname=’jones’;
B. UPDATE customer
IN lastname = ‘smith’
WHERE lastname = ‘jones’;
C. UPDATE customer
SET lastname = ‘smith’
WHERE lastname = ‘jones’;
C. UPDATE customer
SET lastname = ‘smith’
WHERE lastname = ‘jones’;
When updating rows in a table, if the WHERE clause is missing what happens?
A. Throws an error
B. updates all rows with the table for the column
C. updates no rows in the table
B. updates all rows with the table for the column
The substitution variable is used to substitute a value in place of the variable at the time of execution. What symbol is used for this?
A. &
B. #
C. %
D. @
A. &
What command is issued to delete all records in a table?
DELETE FROM ;
True/False: An implicit COMMIT occurs if you exit the client tools or a DDL command is issued.
True
You use a _____ to create a checkpoint in a transaction where it can be rolled back to if something fails in the transaction.
A. CHECKPOINT
B. SAVEPOINT
C. BOOKMARK
B. SAVEPOINT
In a ____ lock, users can view data in a table but they cannot alter the table structure or perform other DDL operations, or make changes to the row that is locked.
Shared
If you need to place a shared lock on data you anticipate needs updating what command should you use?
A. LOCK ….FOR UPDATE
B. HOLD … FOR UPDATE
C. SELECT …. FOR UPDATE
D. Non of the above
C. SELECT …. FOR UPDATE
If you want to restrict showing results for data based on the data that is in a column, what clause do you use?
WHERE
If searching for part of a string or number, what symbol do you use as a wildcard to return all values with part of the string?
A. *
B. %
C. $
D. !
B. %
(EX: SELECT customer
FROM Name
Where name LIKE ‘SM%’;
True/False: You can only use the >, < , = operator types on numerical data.
False
If you are looking for data in a table where the number could be 5-9, what command could you use?
BETWEEN
(Ex: SELECT cost
FROM Book
Where cost BETWEEN 5 AND 9;
If you are looking for data in a table where the state is specifically CA or GA, what command could you use?
IN
(Ex: SELECT state
FROM customers
WHERE state IN (‘GA’ , ‘CA’);
You use the _____ operator with wildcard characters to search for patterns.
LIKE
In using the LIKE operator what does the underscore (_) mean?
A single character is missing or you do not care what character it is.
To sort output in an order what command do you use?
A. ORDER BY
B. LIST BY
C. SORT BY
A. ORDER BY
True/False: You cannot order by more than one column at a time.
False
