DBA-221 part 2
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?
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