IST-278 Test 1

Which system function can you use to return the value of the most recently assigned identity column? A. @@IDENTITY B. @@ERROR C. @@SERVERNAME D. @@ROWCOUNT

A. @@IDENTITY

Which statement creates a table variable named @TestTable? A. DECLARE @TestTable table; B. DECLARE @table TestTable; C. CREATE @TestTable; D. SET @TestTable = table;

A. DECLARE @TestTable table;

Which statement can you use to control the flow of execution based on a true/false condition? A. IF...ELSE B. TRY...CATCH C. BEGIN...END D. EXEC

A. IF...ELSE

Which of the following statements returns the value of a variable named @InvoiceCount? A. RETURN @InvoiceCount; B. OUTPUT @InvoiceCount; C. THROW @InvoiceCount; D. none of the above

A. RETURN @InvoiceCount;

Which statement can you use to repeatedly execute a statement or set of statements? A. WHILE B. CONTINUE C. EXEC D. GO

A. WHILE

To make a parameter for a stored procedure optional, what do you assign to it? A. a default value B. a null value C. a user-defined value D. a PROC

A. a default value

All of the system objects that define a database are stored in A. a system catalog B. a catalog view C. an information schema view D. a derived view

A. a system catalog

One way to examine the system objects that define a database is to use A. catalog views B. base views C. derived views D. none of the above

A. catalog views

When passing a list of parameters to a stored procedure by name, you can omit optional parameters by A. omitting the parameter name and value from the list B. inserting an extra comma C. declaring the optional parameters after the required parameters D. using the OUTPUT keyword

A. omitting the parameter name and value from the list

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 A. THROW B. ENCRYPTION C. HIDE D. PRIVATE

B. ENCRYPTION

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.) A. @InvoiceCount = spInvoiceCount; B. EXEC @InvoiceCount = spInvoiceCount; C. EXEC @spInvoiceCount = InvoiceCount; D. CREATE @InvoiceCount = spInvoiceCount;

B. EXEC @InvoiceCount = spInvoiceCount;

Which statement do you use to modify an existing view? A. UPDATE B. WITH SCHEMABINDING C. CREATE VIEW D. ALTER VIEW

D. ALTER VIEW

Which of the following statements can be coded in a batch with other statements? A. CREATE VIEW B. CREATE PROCEDURE C. CREATE FUNCTION D. CREATE TABLE

D. CREATE TABLE

Which statement tests whether the database named TestDB exists? A. IF TestDB EXISTS B. IF DB_ID ('TestDB') EXISTS C. IF TestDB IS NOT NULL D. IF DB_ID ('TestDB') IS NOT NULL

D. IF DB_ID ('TestDB') IS NOT NULL

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

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); 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

A view is a/an ________________ statement that's stored as an object in the database. A. SELECT B. UPDATE C. INSERT D. DELETE

A. SELECT

Which of the following should you use to view the code that's generated for a view in the View Designer? A. SQL pane B. Results pane C. Diagram pane D. Criteria pane

A. SQL pane

Which statement can you use to manually raise an error within a stored procedure? A. THROW B. TRY C. ERROR D. none of the above

A. THROW

What do you call a local variable that can store a single value? A. scalar B. single-local C. global D. temporary

A. scalar

What's the name of the system database that stores temporary tables? A. tempdb B. temptabledb C. catalogdb D. sys

A. tempdb

The scope of a derived table is limited to what? A. the statement in which it's defined B. the batch in which it's defined C. the script in which it's defined D. the database session in which it's defined

A. the statement in which it's defined

The statement CREATE VIEW Example3 AS SELECT * FROM Invoices; A. will create an updatable view B. will create a read-only view C. will fail because the * operator isn't allowed D. will create a view through which you can delete rows, but not insert or update rows

A. will create an updatable view

What statement can you use to divide a script into multiple batches? A. DECLARE B. GO C. SET D. EXEC

B. GO

Which statement can you use to handle errors caused by one or more SQL statements? A. CONTINUE B. TRY...CATCH C. IF...ELSE D. BEGIN...END

B. TRY...CATCH

Which statement changes the database context to a database named TestDB? A. GO TestDB; B. USE TestDB; C. EXEC TestDB; D. none of the above

B. USE TestDB;

A table that's used to create a view is called A. a view table B. a base table C. a temporary table D. an OFFSET table

B. a base table

By default, A. columns from different tables with the same name do not have to be renamed B. columns in a view are given the same names as the columns in the base tables C. calculated columns do not need to be named in the SELECT statement D. all of the above

B. columns in a view are given the same names as the columns in the base tables

You typically use the return value of a stored procedure to A. return an output parameter to the calling program B. indicate to the calling program whether the stored procedure completed successfully C. receive an input parameter from the calling program D. return a zero value

B. indicate to the calling program whether the stored procedure completed successfully

The WITH CHECK option of the CREATE VIEW statement A. prevents users from using the view without the appropriate authorization B. prevents an update from being performed through the view if it causes a row to no longer be included in the view C. prevents rows from being deleted through the view D. prevents rows from being inserted through the view

B. 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 A. prevents users from modifying the view B. prevents users from seeing the code that defines the view C. prevents users from using the view without the appropriate authorization D. causes the data that's returned by the view to be encrypted

B. prevents users from seeing the code that defines the view

The scope of a local variable is limited to what? A. the statement in which it's defined B. the batch in which it's defined C. the script in which it's defined D. the database session in which it's defined

B. the batch in which it's defined

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? A. GO B. EXEC C. CREATE D. SELECT

C. CREATE

Which statement do you use to delete an existing view? A. CREATE VIEW B. ALTER VIEW C. DROP VIEW D. DELETE VIEW

C. DROP VIEW

Which of the following should you use to select the columns for a view in the View Designer? A. SQL pane B. Results pane C. Diagram pane D. Criteria pane

C. Diagram pane

What statement do you use to execute a dynamic SQL statement? A. SET B. GO C. EXEC D. CONTINUE

C. EXEC

Which of the following statements calls the stored procedure and passes the values '2019-10-01' and 122 to its input parameters? CREATE PROC spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar; A. SELECT spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01'; B. CREATE spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01'; C. EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01'; D. none of the above

C. EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01';

Which of the following statements calls the following stored procedure, passes the value '2019-10-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.) A. EXEC spInvoiceTotal2 '2019-10-01', OUTPUT; B. CREATE spInvoiceTotal2 '2019-10-01', @MyInvoiceTotal OUTPUT; C. EXEC spInvoiceTotal2 '2019-10-01', @MyInvoiceTotal OUTPUT; D. EXEC spInvoiceTotal2 '2019-10-01', @MyInvoiceTotal;

C. EXEC spInvoiceTotal2 '2019-10-01', @MyInvoiceTotal OUTPUT;

Which statement do you use to return a message to the client? A. DECLARE B. EXEC C. PRINT D. SEND

C. PRINT

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'; A. IF @Example1 = 'Invoices' SELECT * FROM Invoices; B. PRINT 'Table name is: ' + @Example1; C. SELECT * FROM @Example1; D. SELECT * FROM sys.tables WHERE name = @Example1;

C. SELECT * FROM @Example1;

Which utility can you use to execute Transact-SQL scripts from a command line? A. SQL B. Management Studio C. SQLCMD D. Eclipse

C. SQLCMD

Which of the following can you use to create or modify a view in SQL Server Management Studio? A. Diagram pane B. Criteria pane C. View Designer D. Query Designer

C. View Designer

Each of the following is a benefit provided by using views except for one. Which one is it? A. You can simplify data retrieval by hiding multiple join conditions. B. You can provide secure access to data by creating views that provide access only to certain columns or rows. C. You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view. D. You can create custom views to accommodate different needs.

C. You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.

The SELECT statement for a view A. can't use an ORDER BY clause B. can include the INTO keyword C. can use the ORDER BY clause if it also uses the TOP clause D. can't use the ORDER BY clause with the OFFSET and FETCH clauses

C. can use the ORDER BY clause if it also uses the TOP clause

Stored procedures execute faster than an equivalent SQL script because stored procedures are what? A. constraints B. triggers C. precompiled D. subqueries

C. precompiled

Data validation is the process of A. using the THROW statement to raise a custom error message B. preventing errors due to incorrect Transact-SQL syntax C. preventing errors due to invalid data D. trapping SQL Server errors so the user doesn't see the system error message

C. preventing errors due to invalid data

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; A. will fail because the GROUP BY clause isn't allowed in this view B. will fail because the column alias SumOfInvoices is invalid C. will fail because the ORDER BY clause isn't allowed in this view D. will succeed

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

The statement CREATE VIEW Example2 AS SELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate) FROM Invoices; A. will create an updatable view B. will create a read-only view because of a calculated value C. will fail because the second column isn't named D. will create a view through which you can delete rows, but not insert or update rows

C. will fail because the second column isn't named

If you delete a stored procedure, function, or trigger and then create it again A. you delete the tables on which the object is based B. you disable access to the tables on which the object is based C. you delete the security permissions assigned to the object D. none of the above

C. you delete the security permissions assigned to the object

Which keyword can you use to pass a parameter from a stored procedure back to the calling program? A. PASS B. EXEC C. INPUT D. OUTPUT

D. OUTPUT

Which statement assigns the value "Test" to a scalar variable named @Name that's declared with the varchar data type? A. DECLARE varchar @Name = 'Test'; B. DECLARE 'Test' @varchar; C. SET Test @Name; D. SET @Name = 'Test';

D. SET @Name = 'Test';

A view A. is like a virtual table B. consists only of the rows and columns specified in its CREATE VIEW statement C. doesn't store any data itself D. all of the above

D. all of the above

System stored procedures A. perform standard tasks on the current database B. are stored in the Master database C. can change with each version of SQL Server D. all of the above

D. all of the above

The View Designer allows you to A. display the results of a view B. specify the selection criteria and sort order for a view C. edit the design of an existing view D. all of the above

D. all of the above

The WITH SCHEMABINDING clause of the CREATE VIEW statement A. protects the view by binding it to the database schema B. prevents the tables that the view is based on from being deleted C. prevents the tables that the view is based on from being modified in a way that affects the view D. all of the above

D. all of the above

When you use Transact-SQL, you can store procedural code in A. scripts B. stored procedures C. user-defined functions D. all of the above

D. all of the above

You can code views that A. join tables B. summarize data C. use subqueries and functions D. all of the above

D. all of the above

A series of SQL statements that you can store in a file is called a A. view B. catalog view C. subquery D. script

D. script

The scope of a temporary table is limited to what? A. the statement in which it's defined B. the batch in which it's defined C. the script in which it's defined D. the database session in which it's defined

D. the database session in which it's defined

The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; A. will create an updatable view B. will create a read-only view C. will create a view through which you can update or insert rows, but not delete rows D. will fail because the SELECT statement returns two columns named VendorID

D. will fail because the SELECT statement returns two columns named VendorID