DBA-221 part 2

Ace your homework & exams now with Quizwiz!

Which system function can you use to determine how many levels deep transactions are nested?

@@TRANCOUNT

To transfer a database object from one schema to another, you use the which statement?

ALTER SCHEMA

You can invoke a table-valued user-defined function

Anywhere you'd refer to a table or a view

Given the following statements that declare a local variable and sets its value, which of the following will cause an error? DECLARE @Example1 varchar(128); SET @Example1 = 'Invoices';

SELECT * FROM @Example1

Which is not one of the four types of concurrency problems?

integrity reads

The statement CREATE VIEW Example3 AS SELECT * FROM INVOICES;

will create an updatable view

Which of the following is the default transaction isolation level for SQL Server?

READ COMMITTED

USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; DECLARE @Total money; SET @Total = 0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC; IF @InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); What is the maximum value of the @Total variable?

200,000.00

Which system function can you use to return the value of the most recently assigned identity column?

@@IDENTITY

Which statement is true about the DENY and REVOKE statements?

A denied permission can't be granted by role membership, but a revoked permission can

Each of the following statements about triggers is true except for one. Which one?

A trigger can have more than one batch

Which statement can you use to explicitly start a transaction?

BEGIN TRAN

Each of the outcomes listed below is a result of executing the following script except for one. Which one? CREATE ROLE ExampleRole; ALTER ROLE db_datareader ADD MEMBER ExampleRole; GRANT INSERT, UPDATE ON Vendors TO ExampleRole; DENY INSERT ON Vendors TO ASmith; ALTER ROLE ExampleRole ADD MEMBER ASmith;

By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.

Before you can pass a table to a stored procedure or a function as a parameter, which statement do you use to create a user-defined table type?

CREATE

Which of the following statements creates a SQL Server login ID for a user named TomBrown with the password 'abc123XYZ'.

CREATE LOGIN TomBrown WITH PASSWORD = 'abc123XYZ';

Which of the following statements creates a login ID for a Windows user named AliceJackson in a domain named Sales:

CREATE LOGIN [Sales\AliceJackson] FROM WINDOWS;

Which statement can you use to create a user-defined database role?

CREATE ROLE

Which of the following statements can be coded in a batch with other statements?

CREATE TABLE

Which of the following statements creates a database user in the current database from a TomBrown SQL Server login ID?

CREATE USER TomBrown;

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

What statement do you use to execute a dynamic SQL statement?

EXEC

Which of the following statements executes a stored procedure named spInvoiceCount and stores its return value in a variable named @InvoiceCount? Assume that the @InvoiceCount variable has already been declared and that the stored procedure doesn't accept any parameters.

EXEC @InvoiceCount = spInvoiceCount;

Which of the following statements calls the stored procedure and passes the values '2015-12-01' and 122 to its input parameters? CREATE PROD spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar;

EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2015-12-01';

Which of the following statements calls for the following stored procedure, asses the value '2015-12-01' to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal? CREATE PROC spInvoiceTotal2 @DateVar smalldatetime, @InvoiceTotal money OUTPUT AS SELECT @InvoiceTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar; (Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position)

EXEC spInvoiceTotal2 '2015-12-01', OUTPUT;

USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(Inn=voiceDueDate) FROM Invoices WHERE InvoiceTotal-PaymentTotal-CreditTotal >0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; If the current date is 03/15/16, the earliest due date for invoices with unpaid balances is 02/09/16 and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?

Earliest past due date: 02/09/16

USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; If the current date is 04/04/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?

Earliest past due date: 02/09/16 Latest past due date: 03/20/16

What statement can you use to divide a script into multiple batches?

GO

Code a statement that tests whether the database named TestDB exists.

IF DB_ID ('TestDB') IS NOT NULL

Which statement can you use to control the flow of execution based on a true/false condition?

IF...ELSE

How would you code the INSTEAD OF clause for a trigger that's fired whenever a view is deleted?

INSTEAD OF DROP_VIEW

You typically use the return value of a stored procedure to

Indicate to the calling program whether the stored procedure completed successfully

This indicates that SQL Server plans to obtain a shared lock or an exclusive lock on a finer-grained resource.

Intent lock

Which option can you use to make SQL Server prompt the user for a new password the first time the login ID is used?

MUST_CHANGE

How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?

ON DATABASE

Which keyboard word can you use to pass parameter from a stored procedure back to the calling program?

OUTPUT

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

Which statement do you use to return a message to the client?

PRINT

Which of the following statements returns the value of a variable named @InvoiceCount

RETURN @InvoiceCount;

Which of the following is the most restrictive transaction isolation level?

SERIALIZABLE

Which utility can you use to execute Transact-SQL scripts from a command line?

SQLCMD

Which statement can you use to manually raise an error within a stored procedure?

THROW

Which statement can you use to handle errors caused by one or more SQL statements?

TRY...CATCH

The scope of a local variable is limited to what?

The batch in which it's defined

What is lock promotion?

The conversion of a less exclusive lock to a more exclusive lock

What is lock escalation?

The conversion of several finer-grained locks into a single coarse-grained lock

The scope of a temporary table is limited to what?

The database session in which it's defined

Each of the following is a valid reason to explicitly code a transaction except for one. Which one?

The results of a SELECT query will be used as a subquery

The scope of a derived table is limited to what?

The statement in which it's defined

If you don't group statements into explicit transactions, SQL Server automatically treats each SQL statement as a separate what?

Transaction

Code a statement that changes the database context to a database named TestDB

USE TestDB;

Each of the following techniques can help to prevent deadlocks except for one. Which one?

Use the highest transaction isolation level possible.

Which statement can you use to repeatedly execute a statement or set of statements?

WHILE

USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; DECLARE @Total money; SET @Total = 0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC; IF @InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); What can cause the WHILE loop in this script to end other than the expression on the statement becoming true?

When the value of the @InvoiceTotal variable becomes less than 1000

USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; SET @Total=0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 2000000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); When does the expression on the WHILE statement cause the loop to end?

When the value of the @Total variable plus the value of the largest invoice in the #InvoiceCopy table becomes greater than 200,000

The statement CREATE VIEW Example1 AS SELECT VendorName, SUM(InvoiceTotal) AS SumOfInvoices FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID GROUP BY VendorName ORDER BY VendorName;

Will fail because the ORDER BY clause isn't allowed in this view.

To log on to SQL Server using your Windows login ID, you use ____ authentication

Windows

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.

If you delete a stored procedure, or trigger and then create it again

You delete the security permissions assigned to the object

A user who's granted the EXECUTE object permission can execute what?

a stored procedure or function

A dirty read occurs when

a transaction selects data that isn't committed by another transaction

Which of the following is true about a transaction?

all of the above (By default, each SQL statement is treated as a separate transaction, you can combine any number of SQL statements into a single transaction, it is a group of database operations that are combined into a logical unit

With the ALTER LOGIN statement you can

all of the above (change the default database or language, enable or disable a login ID, change the name for a login ID

System stored procedures

all of the above (perform standard tasks on the current database, are stored in the Master database, can change with each version of SQL server)

The WITH SCHEMABINDING clause of the CREATE VIEW statement

all of the above (protects the view by binding it to the database schema, prevents the tables that the view is based on from being deleted, prevents the tables that the view is based on from being modified in a way that affects the view)

When you use Transact-SQL, you can store procedural code in

all of the above (stored procedures, scripts, user-defined functions)

Concurrency is

all of the above (the ability of a system to support two or more transactions working with the same data at the same time, more of a problem on larger systems with more users and more transactions, only a problem when data is being modified, not when two or more transactions simply read the data.

The dbcreator role

all the above (is intended for those users who need to be able to work with database objects, lets members create, alter, and drop databases, allows new members to be added to the role

You can use the GRANT statement to give users permission to use each of the following items except for one. Which one?

all the objects in a database

SQL Server's lock manager always tries to lock resources

at the highest possible level

A user-defined function

can return a single scalar value or a single table value

If you've declared save points, the COMMIT TRAN statements

commits the entire transaction

If neither of two transactions can be committed because they each have a lock on a resource needed by the other, it's called a/an

deadlock

If @@TRANCOUNT is equal to 1, then the COMMIT TRAN statement

decrements @@TRANCOUNT and commits the transaction

To make a parameter for a stored procedure optional, what do you assign to it?

default value

Before you can delete a server role, you must

delete all of its members

What kind of lock only allows one transaction to access a resource?

exclusive

Users who are involved in the administration of the server are typically assigned to one of the ____ roles that are built into SQL Server

fixed server

The relative amount of data that's included in a resource can be referred to as the resource's

granularity

One of the drawbacks of using the SERIALIZABLE isolation level is

it can cause severe performance problems

SQL Server automatically avoids some concurrency problems by using what?

locks

To allow users to log on using either type of authentication, you need to set the SQL Server authentication to ____ mode.

mixed

For each type of action query, a table can have

multiple AFTER triggers and one INSTEAD of trigger

Stored procedures execute faster than an equivalent SQL script because stored procedures are what?

precompiled

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

The users, groups, logins, and roles that have access to a server are called

principals

After you create a schema, you can create any object within that schema by

qualifying the object name with the schema name

What do you call a local variable that can store a single value?

scalar

Unlike most database objects, when you invoke a user-defined function, you must always preface the name of the function with the

schema name

A series of SQL statements that you can store in a file is called a

script

The entities that can be secured on a server are called ___?

securables

What's the name of the system database that stores temporary variables?

tempdb

Where would you go to create, modify, or delete logins using the Management Studio

the Security folder for the server

When you commit a transaction

the operations performed by the SQL statements become a permanent part of the database

A lost update occurs when

two transactions select the same row and then update the row based on the values originally selected

By default, SQL Server is in autocommit mode, this means

unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction

The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal-PaymentTotal-CreditTotal>0;

will fail because the SELECT statement returns two columns named VendorID

The statement CREATE VIEW Example2 AS SELECT InvoiceNumber, DATEDIFF(day, InvoiceDate, InvoiceDueDate) FROM Invoices;

will fail because the second column isn't named


Related study sets

CISS 230 Final Review Everything Combined

View Set

public- class 3-ch 27,28 and class 2-ch2

View Set

Management Belmont University Loes CH 5

View Set

AG Chemistry B - The Gas Laws Unit Test (100%)

View Set