Quiz 9 - Chapter 14: How to code scripts

अब Quizwiz के साथ अपने होमवर्क और परीक्षाओं को एस करें!

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?

PRINT

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


संबंधित स्टडी सेट्स

The Courts and the Government Process (2)

View Set

Unit 1 Fundamental Concepts Vocabulary

View Set

Telecom Systems - Chapter 10 - Wireless Systems

View Set

Chapter 47 - Incident Management and MCI

View Set