Quiz 9 - Chapter 14: How to code scripts
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 of the following statements can be coded in a batch with other statements?
CREATE TABLE
Which statement creates a table variable named @TestTable?
DECLARE @TestTable table;
What statement do you use to execute a dynamic SQL statement?
EXEC
USE AP; DECLARE @Date1 date; DECLARE @Date2 date; 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 03/15/20, the earliest invoice due date for invoices with unpaid balances is 02/09/20, and the latest invoice due date for invoices with unpaid balances is 03/20/20, what will be printed by this script?
Earliest past due date: 02/09/20
USE AP; DECLARE @Date1 date; DECLARE @Date2 date; 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/20, the earliest invoice due date for invoices with unpaid balances is 02/09/20, and the latest invoice due date for invoices with unpaid balances is 03/20/20, what will be printed by this script?
Earliest past due date: 02/09/20 Latest past due date: 03/20/20
What statement can you use to divide a script into multiple batches?
GO
Which statement 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
Which statement do you use to return a message to the client?
Given the following statements that declare a local variable and set its value, which of the following will cause an error?
SELECT * FROM @Example1;
Which statement assigns the value "Test" to a scalar variable named @Name that's declared with the varchar data type?
SET @Name = 'Test';
Which utility can you use to execute Transact-SQL scripts from a command line?
SQLCMD
Which statement can you use to handle errors caused by one or more SQL statements?
TRY...CATCH
Which statement changes the database context to a database named TestDB?
USE TestDB;
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; 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); When does the expression on the WHILE statement in this script cause the loop to end?
When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000
What do you call a local variable that can store a single value?
scalar
A series of SQL statements that you can store in a file is called a
script
What's the name of the system database that stores temporary tables?
tempdb
The scope of a local variable is limited to what?
the batch in which it's defined
The scope of a temporary table is limited to what?
the database session in which it's defined
The scope of a derived table is limited to what?
the statement in which it's defined
