SQL Server Final

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

To return the value of the most recently assigned identity column, you can use the _____________________ system function,

@@IDENTITY

Each of the following statement about triggers is true except for one. Which one is it?

A trigger doesn't accept input or return output parameters

To modify an existing view, you use the ______________ statement

ALTER VIEW

Which of the following statements best describes what this INSERT statement does? INSERT INTO InvoiceArchive SELECT * FROM Invoices WHERE TermsID = 1

Adds all the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table

You can invoke a table-valued user-defined function

Anywhere you'd refer to a table or a view

A table that's used to create a view is called a/an __________________ table

Base

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

CREATE TABLE

A user-defined function

Can return a single scalar value or a single table value

One way to examine the system objects that define a database is to use the ___________________ views

Catalog

Code a statement that creates a variable named @TestTable

DECLARE @TestTable table

If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the ____________ keyword in the VALUES clause of the INSERT statement.

DEFAULT

Write the code for a DELETE statement the deletes every row in the Vendors table:

DELETE Vendors DELETE FROM Vendors

To delete an existing view you use the ______________________ statement

DROP VIEW

To make a parameter for a stored procedure optional, you assign it a/an _____________________

Default value

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 WITH ENCRYPTION

To execute a dynamic SQL statement, you code a/an _______________ statement

EXEC

Code a statement that executes a stored procedure named spInvoiceCount and store its return value in a variable name @InvoiceCount. Assume the @InVoiceCount variable has already been declare and that the stored procedure doesn't accept any parameters.

EXEC @InvoiceCount = spInvoiceCount

Code a statement that calls the following stored procedure, passes the value '2006-04-01' to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal. Assume that the @MyInvoiceTotal variable has already been declares, and pass the parameters by position CREATE PROC spInvoiceTotal2 @DateVar smalldatetime, @InvoiceTotal money OUTPUT AS SELECT @InvoiceTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar

EXEC spInvoice2 '2006-04-01', @MyInvoiceTotal OUTPUT

Code a statement that calls the following stored procedure and passes the values '2006-04-01' and 122 to its input parameters. Pass the parameters by name in the reverse order that they're declared in the procedure CREATE PROC spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar

EXEC spInvoiceTotal1 @Vendor = 122, @DateVar = '2006-04-01'

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 07/15/06, the earliest invoice due date for invoices with unpaid balances is 06/09/06, and the latest invoice due date for invoices with unpaid balances is 06/09/06, what will be printed by this script?

Earliest past due date 06/09/06

To divide a script into multiple batches, you use that _______________ command

GO

Code a statement that tests if the database named TestDB exists

IF DB_ID ('TestDB') IS NOT NULL

To control the flow of execution based on a true/false condition, you code a/an ___________________ statement

IF...... ELSE, IF/ELSE, IF, WHILE

How would you code the INSTEAD OF clause for a trigger that's fired whenever a view is deleted?

INSTEAD OF DROP_VIEW

To create a new table by using a SELECT statement, you code the ______________ clause

INTO

When you code a column list in the INTO clause of an INSERT statement, you can't include a/an ______________ column

Identity

You typically use the return value of a stored procedure to

Indicate to the calling program whether the stored procedure completed successfully

For each type of action query, a table can have

Multiple AFTER triggers and one INSTEAD OF trigger

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 08/04/06, the earliest invoice due date for invoices with unpaid balances is 06/09/06, and the latest invoice due date for invoices with unpaid balences is 07/20/06, what will be printed by this script

Nothing

When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow __________________ values

Null

How would you code the ON clause for a trigger that's fired after a table is deleted from the current database (assume that database_name is the name of the current database)?

ON DATABASE

A parameter passes a value from the calling program to the stored procedure, unless you code the __________________ keyword

OUTPUT

When passing a list of parameters to stored procedure by name, you can omit optional parameters by

Omitting the parameter name and value from the list

To return a message to the client, you use the ___________________ statement

PRINT

Stored procedures execute faster than an equivalent SQL script because stored procedures are __________________

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

To manually raise an error within a stored procedure, you use the _____________________ statement.

RAISERROR

Code a statement that returns the variable named @InvoiceCount from a stored procedure named spInvoiceCount

RETURN @InvoiceCount

A view is a/an ________________ statement that's stored as an object in the database

SELECT

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'

SELECT * FROM @Example1

Code a statement that assigns the "Test" to a scalar variable named @Name that's declared with the varchar data type

SET @Name = 'Test'

To execute Transact-SQL script from a command line, you use the ____________________ utility

SQLCMD

A local variable that can store a single value is called a/an ________________ variable

Scalar

Unlike other database objects, when you invoke a user-defined function you must always include the _______________________

Schema name

A series of SQL statement that you can store in a file is called a/an _____________________

Script

To insert several rows into a table with an INSERT statement, you code a/an _______________ in place of the VALUES clause

Subquery

All of the system objects that define a database are stored in the ____________________

System catalog

A local variable that an entire result set is called a/an _________________ variable

Table

Parameters for stored prcedures and functions can be of any SQL Server data type except

Table

A temporary table is stored in the system database named ______________________

Tempdb

The scope of a local variable is limited to

The batch in which it's defined

The scope of a temporary table is limited to

The database session in which it's defined

Assuming that all of the table and column names are spelled correctly, what's wrong with the INSERT statement that follows? INSERT INTO InvoiceCopy (VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDate, InvoiceDueDate) VALUES (97, '456789', 8344.50, 0, 0, 1, '2006-08-01')

The number of items in the column list does not match the numbers in the VALUES list

The scope of a derived table is limited to

The statement in which it's defined

Unless a database system supports declarative referential integrity, the only way to enforce referential integrity is to use ______________________

Triggers

To handle errors caused by one or more SQL statements, you can use the _______________ statement

Try.....CATCH, TRY/CATCH,TRY

Code a statement that changes the database context to a database named TestDB

USE TestDB

When you code a DELETE statement for one or more rows, the __________________ clause specifies which row or rows are to be deleted

WHERE

When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the ____________ clause specifies which row or rows are to be updated

WHERE

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 varible 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

The statement CREATE VIEW Example3 AS SELECT * FROM Invoices

Will create an update-able view

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

Each of the following is a benefit provided by the 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, function, or trigger and then create it again

You delete the security permissions assigned to the object

System stored procedures

perform standard tasks on the current database, are stored in the Master database, and can change with each version of SQL Server

The WITH ENCRYPTION clause of the CREATE VIEW statement

prevents users from seeing the code that defines the view

The WITH SCHEMABINDING clause of the CREATE VIEW statement

protect the view by binding it to the database schema, prevents the tables that view is based on from being deleted, and prevents the tables that the view is based on from being modified in a way that affects the view

Statement CREATE VIEW Example2 AS SELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate) FROM Invoices

will fail because the second column isn't named

The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0

will fail besause the SELECT statement returns two columns named VenderID