Section 1
Preview this deck
A view that is not updateable is called a ________ view.
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
1
All-time users
1
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (71)
Section 1
(50 cards)
A view that is not updateable is called a ________ view.
read-only
To replace an existing view with a new one, you use the _______ statement.
CREATE OR REPLACE VIEW
In the view that follows, which field can't be updated CREATE VIEW example_2 AS SELECT invoice_number, invoice_date, invoice_total, payment_total, invoice_total - payment_total - credit_total AS balance_due, SYSDATE() - invoice_date AS days_old FROM invoices A. invoice_total B. balance_due C. invoice_date D. all of the above
B. balance_due
Code a SQL statement that displays the value of a variable named message_var.
SELECT(message_var) AS message_var;
To turn the event scheduler on or off, you can use the _______ statement.
SET
The ______ statement raises an error.
SIGNAL
The WITH CHECK OPTION clause of the CREATE VIEW statement a. prevents users from using the view without the appropriate authorization b. prevents an update from being performed through the view if it causes a row to be excluded from the view c. prevents rows from being deleted through the view d. prevents rows from being inserted through the view
B. prevents an update from being performed through the view if it causes a row to be excluded from the view
A/An _______ stores a value that can change as a procedure executes.
variable
When working with dynamic SQL, you use a stored routine to build a string variable that contains a SQL statement. Then, you use the ________, EXECUTE, and DEALLOCATE statements to execute the statement contained in the string.
PREPARE
To create a row-level trigger, you must code the ______ clause.
FOR EACH ROW
If you want MySQL to exit the current block of code as soon as it encounters an error, you use a/an _____ handler.
EXIT
With MySQL, you can only create ______ functions, which return a single value.
scalar
MySQL automatically avoids some concurrency problems by using _______.
locks, locking
Assume that this is the start of the code for a store procedure: CREATE OR REPLACE PROCEDURE update_credits ( in_param VARCHAR(50), cr_param DECIMAL(9,2) ) AS Then, code a CALL statement that calls the stored procedure and passes the values 47B and 200 to it.
CALL update_credits('47B', 200);
You can use the DECLARE...HANDLER statement to declare a handler for errors that may occur. In MySQL this is referred to as a/an ______ handler.
condition
To work with the data in a result set one row at a time, you would use a/an _______.
cursor
You usually don't insert rows into a table through view because A. due to table constraints, using a view to insert rows often results in errors B. the view would have to include all of the required columns for the base table that require a value C. if the view names more than one base table, the INSERT statement can insert data into only one of those tables D. all of the above
D. all of the above
A table that's used to create a view is called a/an ____ table.
base, underlying
Each of the following is a benefit provided by using views except for one. Which on is it? a. Views can be used to update multiple tables with a single statement. b. You can use views to limit the exposure of the tables in your database to certain rows or applications. c. Views can be used to hide the complexity of retrieval operations. d. You can code views that join tables.
A. Views can be used to update multiple tables with a single statement.
To set a default value for a parameter, you can use a/an ______ statement to check if the parameter contains a null value.
IF
MySQL only supports _______ triggers.
row_level
To insert rows into an audit table, you typically use a/an _______ trigger.
AFTER
When you use _____ with transactions, you can rollback a transaction to the beginning of a transaction.
save points
A trigger is anamed block of code that executes, or _________, in response to an INSERT, UPDATE, or DELETE statement.
fires, runs
A view is a/an ______ statement that's stored in the database as a database object.
SELECT
By default, MySQL uses ____ mode for INSERT, UPDATE, or DELETE statements.
autocommit
One of the best reasons for using views is that they can limit the exposure of the tables in your database to external _______ and applications.
users
The statement CREATE VIEW example_1 AS SELECT vendor_name, SUM(invoice_total) AS sum_of_invoices FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id GROUP BY vendor_name ORDER BY vendor_name A. will fail because the GROUP BY clause isn't allowed B. will fail because SUM function isn't allowed C. will fail because the ORDER BY clause isn't allowed D. will succeed
D. will succeed
An event can be a one-time event that occurs only once or a ______ event that occurs regularly at a specified interval.
recurring
To create a procedure, you use the CREATE PROCEDURE statement, but to execute a procedure you use the ______ statement.
CALL
You can use a/an _______ to pass one or more values from the calling program to the stored procedure.
parameter, input parameter
The _______ statement turns off autocommit mode until statements in the transaction are committed or rolled back.
START TRANSACTION
Transactions are not supported by the _____ database engine.
MyISAM
A ________ occurs when neither of two transactions can be committed because they each have a lock on a resource needed by the other.
deadlock
To repeatedly execute a SQL statement, you can code a simple loop, a WHILE loop, or a/an _____ loop.
REPEAT
If you want to allow MySQL to attempt to execute statements in a block of code even after it encounters an error, you should use a/an ______ handler.
CONTINUE
When you work with output parameters or input/output parameters, the calling program typically passes a/an _______ to the parameter list.
user variable
If you don't include a/an _______ clause when you create a view, a change you make through the view can clause the modified rows to no longer be included in the view.
WITH CHECK OPTION
An event executes according to the event _______.
scheduler
When you use a BEFORE trigger to work with an UPDATE statement, you can use the keyword and a column name to refer to a value in the row before it is updated.
OLD
Triggers can be used to enforce rules for data consistency that can't be enforced by _________.
constraints
Code a SQL statement that declares a variable named message_var with a VARCHAR data type that can store a maximum of 140 characters. Then, code a second statement that assigns the value "Test" to that variable.
DECLARE message_var VARCHAR(140); SET message_var = 'Test';
A stored program that automatically executes in response to an INSERT, UPDATE, or DELETE statement is called a/an _________.
trigger
Which of the following can be used with an updateable view? a. a DISTINCT clause b. a join c. an aggregate function d. the UNION operator
B. a join
When you raise an error in a stored procedure, you must specify a/an ______ code.
SQLSTATE
A view that is based on another views, rather than a table is called a ________ view.
nested
A/An __________ statement is used to execute one or more statements based on a value that's returned by a Boolean expression.
IF
Assume that this is the start of the code for creating a stored function: CREATE OR REPLACE FUNCTION get_days_overdue ( invoice_date_param DATE, invoice_terms_param INT ) RETURN INT AS Code the column specifications that you would use in a SELECT statement to pass the invoice_date and invoice_terms columns to the function and use days_old as the column name.
get_days_overdue(invoice_date, invoice_terms) AS days_old
________ parameters store values that are passed back to the calling program.
Output
If you declare multiple condition handlers when coding a stored program, you should code the _____ specific error handler last.
least
Section 2
(21 cards)