Exam 3
A view is a _________ statement that's stored as an object in the database.
SELECT
To repeatedly execute a statement or set of statements, you code a ____________ statement
WHILE
A user-defined function
can return a single scalar value or a single table value
You typically use the return value of a stored procedure to
indicate to the calling program whether the stored procedure completed successfully
The scope of a derived table is limited to
the statement in which it's defined
Each of the following statements about triggers is true except for one. Which one is it?
A trigger can have more than one batch.
To control the flow of execution based on a true/false condition, you code an _________________ statement.
IF, ELSE. IF, ELSE IF.
When you code a DELETE statement for one or more rows, the __________ clause specifies which row or rows are to be deleted.
WHERE
To return the value of the most recently assigned identity column, you can use the _________________ system function.
@@IDENTITY
To modify an existing view, you use the ________ statement
ALTER VIEW
To divide a script into multiple batches, you use the ______________ command.
Go
A parameter passes a value from the calling program to the stored procedure, unless you code the ________________ keyword.
OUTPUT
Code a statement that changes the database context to a database named TestDB.
Use TestDB
Each of the following is a benefit provided by using views except for one. Which one is it?
You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.
A local variable that can store an entire result set is called a ____ variable.
table
Parameters for stored procedures and functions can be of any valid SQL Server data type except
table
A temporary table is stored in the system database named __________.
temp-db
The scope of a local variable is limited to
the batch in which it's defined
The scope of a temporary table is limited to
the database session in which it's defined
Unless a database system supports declarative referential integrity, the only way to enforce referential integrity is to use _________.
triggers
If you delete a stored procedure, function, or trigger, and then create it again
you delete the security permissions assigned to the object
A table that's used to create a view is called a __________ table.
Base
Write the code for a DELETE statement that deletes every row in the Vendors table:
DELETE Vendors DELETE FROM Vendors
Data validation is the process of
preventing errors due to invalid data
The WITH CHECK option of the CREATE VIEW statement
prevents an update from being performed through the view if it causes a row to no longer be included in the view
The WITH ENCRYPTION clause of the CREATE VIEW statement
prevents users from seeing the code that defines the view
One way to examine the system objects that define a database is to use the _________ views
CATALOG
What statement can be coded in a batch with other statements?
CREATE TABLE
If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the _____________ keyword in the VALUES clause of the INSERT statement.
DEFAULT
To delete an existing view, you use the ________ statement.
DROP VIEW
Code a statement that creates a table variable named @TestTable.
Declare @TestTable table
If you want to prevent users from examining the SQL code that defines a procedure, function, or trigger, you code the CREATE statement with the ________ option.
ENCRYPTION, WITH ENCRYPTION
To execute a dynamic SQL statement, you code an ________ statement.
EXEC
Code a statement that tests if the database named TestDB exists.
IF DB_ID('TestDB') IS NOT NULL
To create a new table by using a SELECT statement, you code the ___________ clause
INTO
When you code a column list in the INTO clause of an INSERT statement, you can't include an __________ column.
Identity
How would you code the Instead Of clause for a trigger that's fired whenever a view is deleted?
Instead Of drop_view
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow _________ values.
Null
How would you code the On clause for a trigger that's fired after a table is deleted from the current database (assume that database_name is the name of the current database)?
ON DATABASE
To return a message to the client, you use the _________ statement.
To manually raise an error within a stored procedure, you use the ______________ statement.
RAISERROR
Code a statement that assigns the value "Test", to a scalar variable named @Name, that's declared with the varchar data type.
SET @Name = 'Test'
To execute Transact-SQL scripts from a command line, you use the _________ utility.
SQLCMD
All of the system objects that define a database are stored in the ______________________________.
SYSTEM CATALOG
To handle errors caused by one or more SQL statements, you can use the _______________ statement.
TRY CATCH
When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the _________ clause specifies which row or rows are to be updated
WHERE
The WITH SCHEMA-BINDING clause of the CREATE VIEW statement
a. protects the view by binding it to the database schema b. prevents the tables that the view is based on from being deleted c. prevents the tables that the view is based on from being modified in a way that affects the view
System stored procedures
a., perform standard tasks on the current database, b., are stored in the Master database, c., can change with each version of SQL Server
You can invoke a table-valued user-defined function
anywhere you'd refer to a table or a view
To make a parameter for a stored procedure optional, you assign it a _____________.
default value
For each type of action query, a table can have
multiple After triggers and one Instead Of trigger
When passing a list of parameters to a stored procedure by name, you can omit optional parameters by
omitting the parameter name and value from the list
Stored procedures execute faster than an equivalent SQL script, because stored procedures are ___________.
pre-compiled
A local variable that can store a single value is called a __________ variable.
scalar
Unlike other database objects, when you invoke a user-defined function, you must always include the _______________.
schema name
A series of SQL statements that you can store in a file is called a _____.
script
To insert several rows into a table with an INSERT statement, you code a ___________ in place of the VALUES clause.
sub-query
