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