Exam 3

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

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.

PRINT

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


Ensembles d'études connexes

Physician Assistant Interview-Example 3

View Set

Sample Professional Scrum Master Exam

View Set

Chapter 16 | Capital Structure: Basic Concepts

View Set

Med Surg ATI Cardiovascular and respiratory

View Set

Chapter 17: Business Organizations

View Set

Patho Case Study #6 Chapter 13: Respiratory System Disorders Asthma

View Set