Chapter 14

Question 1

What’s the name of the system database that stores temporary tables?

Answer 1

tempdb

Question 2

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

Answer 2

script

Question 3

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

Answer 3

TRY…CATCH

Question 4

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

Answer 4

scalar

Question 5

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

Answer 5

SQLCMD

Question 6

Which statement changes the database context to a database named TestDB?

Answer 6

USE TestDB;

Question 7

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

Answer 7

PRINT

Question 8

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

Answer 8

@@IDENTITY

Question 9

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

Answer 9

GO

Question 10

Which statement assigns the value “Test” to a scalar variable named @Name that’s declared with the varchar data type?

Answer 10

SET @Name = ‘Test’;

Question 11

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

Answer 11

IF…ELSE

Question 12

Which statement creates a table variable named @TestTable?

Answer 12

DECLARE @TestTable table;

Question 13

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

Answer 13

EXEC

Question 14

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

Answer 14

WHILE

Question 15

Which statement tests whether the database named TestDB exists?

Answer 15

IF DB_ID (‘TestDB’) IS NOT NULL

Question 16

The scope of a local variable is limited to what?

Answer 16

the batch in which it’s defined

Question 17

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

Answer 17

CREATE TABLE

Question 18

The scope of a derived table is limited to what?

Answer 18

the statement in which it’s defined

Question 19

The scope of a temporary table is limited to what?

Answer 19

the database session in which it’s defined

Question 20

Given the following statements that declare a local variable and set its value, which of the following will cause an error?

DECLARE @Example1 varchar(128);
SET @Example1 = ‘Invoices’;

Answer 20

SELECT *

FROM @Example1;

Question 21

Consider the following code:

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?

Answer 21

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

Question 22

Consider the following code:

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?

Answer 22

Earliest past due date: 02/09/20