Databases Final

When using a dynamic cursor, the value of the @@CURSOR_ROWS system function will be

-1

Describe the return values for the @@FETCH_STATUS system function.

0 - successful -1 - unsuccessful (end) -2 - unsuccessful (row deleted)

The legal values for a column of type bit are:_______________

0, 1

When using the DATEPART() function, what integer represents Sunday?

1

Errors that have a severity of ______ or lower are considered warnings and are not handled by a TRY/CATCH blocks

10

If you don't specify a time when storing a date value in a datetime column, the time defaults to _________ and date defaults to _________.

12:00 AM & Janurary 1, 1900

When storing a BLOB in SQL Server as a varbinary(max) datatype, what is the maximum size of the image?

2GB

How levels deep can views be nested?

32

The number of bytes used for bigint datatype is ____

8

Write the SQL that will create a trigger on the Invoices table (from the AP database) that will delete the relevant rows from the InvoiceLineItems table when a row from the Invoices table is deleted

???

Write the SQL that will create a trigger on the Invoices table (from the AP database) that will prevent an invoice from being inserted when the VendorID in not in the Vendors table

???

Write the SQL that will create a trigger that will ensure that all the VendorState values in the Vendors table (in the AP database) are lower case.

???

Code a statement that calls the following stored procedure and passes the values '2011-12-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;

????

Code a statement that 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

????

To determine the number of rows that are eligible for the most recently opened cursor, you use the ______________________________ system function

@@CURSOR_ROWS

The ______________________ system function tests whether the last FETCH statement from a cursor was successful.

@@FETCH_STATUS

To determine whether the most recent FETCH statement was successful, you use the _____________________________ system function

@@FETCH_STATUS

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

@@IDENTITY

To determine how many levels deep transactions are nested, you can use the ____________ system function

@@TRAN_COUNT

What is a savepoint in a database transaction?

A point to rollback to

Define what a database transaction is.

A unit of work performed within a database.

Describe the difference between a table and a view in SQL Server

A view doesn't store data

A FOR trigger is identical to a/an __________________ trigger

AFTER

To modify an existing view, you use the ______________________________ statement

ALTER VIEW

When using a 12-hour clock for time input, which is the default AM or PM?

AM

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

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

Describe what each of the letters in ACID means for relational databases

Atomicity - all or nothing Consistency - ensures transaction will bring the database from one valid state to another Isolation - concurrent execution of transactions Durability - ensures that once a transaction has been committed it remains

What does ACID stand for in relational databases?

Atomicity, Consistency, Isolation, Durability

To explicitly start a transaction, you code the ______________________________ statement.

BEGIN TRAN

If you need to execute 2 or more SQL statements within an IF or ELSE clause, you need to enclose the statements within a ________________

BEGIN....END

To exit from a while loop immediately without testing the condition, us the ________ statement

BREAK

In SQL Server, BLOB stands for _______

Binary Large Object

Which is an ANSI standard function, CAST or CONVERT?

CAST

Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function: ____________________

CAST(InvoiceAmount AS varchar)

The difference between the CAST function and the CONVERT function is that the ______________________________ function accepts an optional style argument that lets you specify a format for the result.

CONVERT

Write the SQL to create a view called Vendors_NE that will only display data from the Vendors table (in the AP database) that includes the states Connecticut, Massachusetts, Vermont, New Hampshire, and Maine

CREATE VIEW view_name AS Select statement stuff

Describe what lock-escalation is in SQL Server

Convert multiple fine-grain into a single course-grain

What are the 2 SQL statements that you use to free the resources used by a cursor?

DEALLOCATE & CLOSE

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

DECLARE @Name varchar SET @Name = test;

Code a statement that creates a table variable named @VendorT. It should contain 2 columns, VendorID of type int, and VendorName of type varchar(50)

DECLARE @VendorT table (VendorID INT, VendorName varchar)

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

DECLARE Dynamic_Vendor_Cursor CURSOR DYNAMIC SCROLL_LOCKS FOR SELECT * FROM Vendors ORDER BY VendorName; OPEN Dynamic_Vendor_Cursor; FETCH Dynamic_Vendor_Cursor; Write an SQL statement that will delete the row at the current cursor position

DELETE FROM Vendors WHERE CURRENT OF Dynamic_Vendor_Cursor

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

DELETE Vendors;

Which cursor type in SQL Server uses the most resources?

DYNAMIC

Which type of cursor is most aware of changes to the data source?

DYNAMIC

Identify and describe the 7 types of SQL Server cursors

DYNAMIC STATIC KEYSET FORWARD_ONLY FORWARD_ONLY KEYSET FORWARD_ONLY STATIC FAST_FORWARD

What does a commit do for a transaction?

Decrement @@TRANCOUNT and then IF AT ONE commits

Describe the FOR UPDATE clause of the cursor declaration statement

Defines updateable columns within the cursor

Which are generally faster, derived tables or temporary tables?

Derived tables

Describe the NTILE function

Divides the rows in a partition into the specified number of groups.

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

EXEC

How does SQL Server prevent deadlocks from tying up the system?

Errors???

The fastest type of cursor in SQL Server is the _________________________ cursor

FAST_FORWARD

Fill in the missing SQL statement that will step forward through the following cursor. FETCH LAST FROM Vendor_Cursor; WHILE @@FETCH_STATUS = 0 BEGIN ... <missing SQL statement> -- replace this line END;

FETCH NEXT FROM Vendor_Cursor;

Fill in the missing SQL statement that will step backward through the following cursor. FETCH LAST FROM Vendor_Cursor; WHILE @@FETCH_STATUS = 0 BEGIN ... <missing SQL statement> -- replace this line END;

FETCH PRIOR FROM Vendor_Cursor;

If a cursor is not scrollable, it is called a _____________________________ cursor

FORWARD_ONLY

T/F When using the SELECT-INTO SQL statement, the table into which you are inserting data, must exist before you execute the statement.

False

T/F When using the SELECT-INTO statement, all properties of the columns from the source table are copied along with data to the destination table

False

T/F When using the UPDATE SQL statement, you can update an identity column.

False

T/F You cannot use subqueries as part of an UPDATE statement.

False

True or False, A trigger will fire on a table even when the action query causes an error

False

True or False, A view cannot contain joins or aggregate functions

False

True or False, As you can rollback an implicit transaction, you can also commit an implicit transaction

False

True or False, In SQL Server, transactions cannot be nested

False

True or False, SQL Server TRY/CATCH cannot be nested

False

True or False, Stored procedures cannot make recursive calls

False

True or False, Views cannot be nested, you cannot reference a view from within another view

False

True or False, When the @@TRANCOUNT is greater than 1, a commit will increment @@TRANCOUNT and commit the current save point

False

True or False, When you commit a transaction, it only commits to the most recent save point

False

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

IF....ELSE

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

INTO

Describe the datatimeoffset datatype for SQL Server

It includes the time zone offset

Which type of cursor is aware only of updates and deletes to the source data?

KEYSET

Identify an advantage of using FILESTREAM for BLOB storage in SQL Server

Larger than 2GB

Describe the difference between a local temporary table and a global temporary table

Local - visible only within the current session Global - visiable to all sessions

Describe what the following SQL does: DECLARE @TableNameVar varchar(128); SET @TableNameVar = 'Invoice'; EXEC('select * from ' + @TableNameVar + '; ');

Makes a variable Assigns that variable Runs select statement on that variable

How many AFTER triggers can a table have?

Multiple

Describe the OPTIMISTIC option that can be used when creating a cursor with SQL Server

No lock placed

What return values are available for triggers?

None

Which of the concurrency problems is solved by the READ UNCOMMITTED isolation level?

None

Describe the 3 T-SQL cursor concurrency options

OPTIMISTIC - row can be modified SCROLL_LOCK - no process can modify until released READ_ONLY - can't update data through cursor

A parameter only passes a value from the calling program into the stored procedure, unless you code the ______________________________ keyword

OUTPUT

How many INSTEAD OF triggers can a table have?

One

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

PRINT

What does the Update (U) lock allow in SQL Server?

Prevents a common form of deadlock

Identify 2 advantages and 2 disadvantages of storing BLOBs in the SQL Server database

Pros: back up on DB & data secure control access Cons: smaller than 2GB & not fast

Identify 2 advantages and 2 disadvantages of storing BLOBs in files

Pros: no size limit & fast access Cons: not backed up & network security

Describe the RAND() function

Random floating point number between 0-1

What are the 5 isolation levels supported by SQL Server?

Read Uncommited Read Commited Repeatable Read Snapshot Serializable

Describe the LEAD and LAG functions

Refer to values in other rows of the result set.

Which of the isolation levels in SQL Server prevents all except the Phantom Reads problem?

Repeatable Read

DECLARE Vendor_Cursor CURSOR STATIC FOR SELECT VendorID, VendorName FROM Vendors; Describe the following SQL statement. FETCH RELATIVE 0 FROM Vendor_Cursor;

Retrieves the current row again

DECLARE Vendor_Cursor CURSOR STATIC FOR SELECT VendorID, VendorName FROM Vendors; Describe the following SQL statement. FETCH FROM Vendor_Cursor;

Retrieves the next row

DECLARE Vendor_Cursor CURSOR STATIC FOR SELECT VendorID, VendorName FROM Vendors; Describe the following SQL statement. FETCH FROM Vendor_Cursor INTO @VendorIDVar,@VendorNameVar;

Retrieves the next row and assigns the values to two local variables.

DECLARE Vendor_Cursor CURSOR STATIC FOR SELECT VendorID, VendorName FROM Vendors; Describe the following SQL statement. FETCH PRIOR FROM Vendor_Cursor;

Retrieves the previous row

DECLARE Vendor_Cursor CURSOR STATIC FOR SELECT VendorID, VendorName FROM Vendors; Describe the following SQL statement. FETCH RELATIVE 10 FROM Vendor_Cursor;

Retrieves the tenth row after the current row

DECLARE Vendor_Cursor CURSOR STATIC FOR SELECT VendorID, VendorName FROM Vendors; Describe the following SQL statement. FETCH ABSOLUTE 3 FROM Vendor_Cursor;

Retrieves third row

Describe the system stored procedure sp_Who

Return a result set of the users data

Describe SPACE( integer ) function.

Returns a string with the specified number of space characters.

Describe the @@ROWCOUNT system function

Returns number of rows affected by most recent SQL statement

Describe the system stored procedure sp_Help

Returns the text of the stored procedure

Define what a rollback is for a database transaction

Rolls back a transaction to the starting point.

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

SELECT

How do you change your isolation level in SQL Server?

SET TRANSACTION ISOLATION LEVEL

The Exclusive (X) lock in SQL Server is compatible with what other lock/locks?

Schema-shared

Describe what the following SQL does: If DB_ID('TestDB') is not null Drop database TestDB;

Sees it if exists and then deletes it

Which of the isolation levels supported by SQL Server requires the most system resources?

Serializable

Which of the isolation levels in SQL Server prevent all of the concurrency problems?

Serializable & Snapshot

Which generally provides better performance and uses fewer system resources, cursor based processing or set based operations?

Set based

How many CATCH blocks can you have in a SQL Server TRY/CATCH statement?

Single

Describe how the SNAPSHOT isolation level works

Specifies that data read by any statement in a transaction will be transactionally consistent version of the data that existed at the start of the transaction.

What are the 5 types of T-SQL table objects?

Standard Table Temporary Table Table Variable Derived Variable View

Which SQL Server datatype is/are considered the lowest order precedence when performing implicit datatype conversion?

String Category

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

THROW

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

TRY....CATCH

You can use the OBJECT_ID function to check for the existence of (5 things, identify them from page 424)

Table View Stored Procedure User-defined function Trigger

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 doesn't match the number in the VALUES list.

When using FILESTREAM storage for BLOBs in SQL Server, what is the file size limitation?

There is none

Describe the difference between transactional and procedural programming in SQL Server

Transactional - All or nothing executed in one go Procedural - Uses a cursor to emulate C++ style code but working with each piece of data individually

Procedures that execute when an action query, such as INSERT, UPDATE, or DELETE, are called _________

Triggers

T/F If a column is an identity column, you cannot insert a value into it when using the INSERT statement.

True

T/F Within a WHEN clause of a MERGE statement, you can code a simplified INSERT, UPDATE, or DELETE statement that does not include the table name or WHERE clause

True

True of False, Stored procedures execute faster than an equivalent SQL script

True

True or False, A trigger can be created for a view

True

True or False, Since an API-cursor is managed by API on the client machine, you don't need to write any SQL code to support this type of cursor

True

True or False, Stored procedures are precompiled

True

True or False, When you execute the ROLLBACK TRAN statement, all active transactions are rolled back

True

True or False, if you name the columns of a view in the CREATE VIEW clause, you have to name all of the columns

True

True or False, since an INSTEAD OF trigger is executed instead of the action query that caused it to fire, the action never occurs unless you code it as part of the trigger

True

True or False, since you cannot code a save point name in a COMMIT TRAN statement, the system always commits the entire transaction

True

True or False: A single SQL Server cursor can be created and then used in different stored procedures.

True

Describe the Lost Updates concurrency problem

Two+ transactions select the same row and then update the row based on the value originally selected.

DECLARE Dynamic_Vendor_Cursor CURSOR DYNAMIC SCROLL_LOCKS FOR SELECT * FROM Vendors ORDER BY VendorName; OPEN Dynamic_Vendor_Cursor; FETCH Dynamic_Vendor_Cursor; Write an SQL statement that will modify the row at the current cursor position. Change the value of the VendorName column to 'UNIX Networking'

UPDATE Vendors SET VendorName = 'UNIX Networking' WHERE CURRENT OF of Dynamic_Vendor_Cursor

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

USE TestDB;

Identify 3 scenarios when you want to use database transactions

User error Multiple user conflicts Atomic actions with ability of rollbacks

Describe the Non-repeatable Reads concurrency problem.

When a second transaction accesses the same row several times and reads different data each time.

Describe the Dirty Reads concurrency problem

When a second transaction selects a row that is being updated by another transaction. stuff with commited

Describe the Phantom Reads concurrency problem

When an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction.

If @@TRANCOUNT is equal to 1, then the COMMIT TRAN statement a) decrements @@TRANCOUNT and commits the transaction b) decrements @@TRANCOUNT but doesn't commit the transaction c) commits the transaction but doesn't decrement @@TRANCOUNT d) partially commits the transaction

a

If you delete a stored procedure, function, or trigger and then create it again a) you should use the ALTER statement b) you delete the tables on which the object is based c) you disable access to the tables on which the object is based d) you delete the initial security permissions assigned to the object

a

Which of the following is the most restrictive transaction isolation level? a) SERIALIZABLE b) READ COMMITTED c) REPEATABLE READ d) READ UNCOMMITTED

a

Which one of the following statements is true? a. Implicit data type conversion is attempted any time you mix values of different data types in an expression. b. Every data type can be implicitly converted to every other data type. c. Values are implicitly converted from the data type with higher precedence to the data type with lower precedence. d. You can convert a data type implicitly by using either the CAST or the CONVERT function.

a

A dirty read occurs when a) you perform an update on a set of rows when another transaction is performing an insert that affects one or more rows in that same set of rows b) a transaction selects data that isn't committed by another transaction c) two transactions select the same row and then update the row based on the values originally selected d) two SELECT statements that select the same data get different values because another transaction has updated the data in the time between the two statements

b

For each type of action query, a table can have a) one AFTER trigger and multiple INSTEAD OF triggers b) multiple AFTER triggers and one INSTEAD OF trigger c) one AFTER trigger and one INSTEAD OF trigger d) multiple AFTER triggers and multiple INSTEAD OF triggers

b

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)? a) ON database_name b) ON DATABASE c) ON DATABASE database_name d) ON CURRENT DATABASE

b

If a user fetches a row through a cursor with the SCROLL_LOCKS concurrency option and a second user tries to fetch the same row, a) the second user can't fetch the row b) the second user can fetch the row but can't update the row through the cursor until the first user has fetched a different row c) the second user can fetch the row but can't update the row through the cursor at all d) the second user can fetch the row but the first user can't update the row through the cursor until the second user has fetched a different row

b

Lock escalation is a) the conversion of an exclusive lock to an intent lock b) the conversion of several finer-grained locks to a single coarse-grain lock c) the conversion of a less exclusive lock mode to a more exclusive lock mode d) the conversion from a less restrictive transaction isolation level to a more restrictive transaction isolation level

b

Parameters for stored procedures and functions can be of any valid SQL Server data type except a) date/time b) xml c) table d) numeric

b

Which of the following is the default transaction isolation level for SQL Server? a) SERIALIZABLE b) READ COMMITTED c) REPEATABLE READ d) READ UNCOMMITTED

b

A lost update occurs when a) you perform an update on a set of rows when another transaction is performing an insert that affects one or more rows in that same set of rows b) a transaction selects data that isn't committed by another transaction c) two transactions select the same row and then update the row based on the values originally selected d) two SELECT statements that select the same data get different values because another transaction has updated the data in the time between the two statements

c

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

Each of the following statements about triggers is true except for one. Which one is it? a) A trigger can't be directly called or invoked. b) A trigger doesn't accept input or return output parameters. c) A trigger can have more than one batch. d) The code of a trigger can execute in place of the action query to which it's assigned.

c

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

If you've declared save points, then the COMMIT TRAN statement a) commits the changes since the most recent save point b) commits the changes since the save point named in the COMMIT TRAN statement c) commits the entire transaction d) doesn't commit the transaction

c

Lock promotion is a) the conversion of an exclusive lock to an intent lock b) the conversion of several finer-grained locks to a single coarse-grain lock c) the conversion of a less exclusive lock to a more exclusive lock d) the conversion from a less restrictive transaction isolation level to a more restrictive transaction isolation level

c

The scope of a local variable is limited to a. the statement in which it's defined b. the script in which it's defined c. the batch in which it's defined d. the database session in which it's defined

c

The scope of a temporary table is limited to a. the statement in which it's defined b. the script in which it's defined c. the batch in which it's defined d. the database session in which it's defined

c

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

You can use the WHERE CURRENT OF clause to a) fetch a row from the current cursor position b) insert rows through a cursor c) update or delete rows through a cursor d) lock a row retrieved through a cursor

c

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

catalog

To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by a/an ______________.

comma

When two or more users have access to the save database, it's possible for them to be working on the same data at the same time. This is called ___________

concurrancy

The IIF function determines the value it returns based on a/an ____________________ expression

conditional

Describe the sys.tables view

contains info on all tables in the database

A keyset-driven cursor is a) sensitive to all database changes b) sensitive to updates, but insensitive to insertions and deletions c) sensitive to insertions, but insensitive to updates and deletes d) sensitive to updates and deletes, but insensitive to insertions

d

All of the following values can be stored in a column that's defined as decimal(6,2), except a. -246.29 c. 2479.95 b. 0 d. 32492.05

d

If ExpirationDate contains a value that's equivalent to June 2, 2012 and the GetDate() function returns a value that's equivalent to July 17, 2012, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution a. 1 c. 30 b. 15 d. 45

d

Which uses the least amount of storage? a. 'example' stored in a column of type nchar(20) b. 'exam' stored in a column of type nchar(20) c. 'ex' stored in a column of type nchar(20) d. they all use the same amount of storage

d

The most course-grain resource locking level is _______

database

You can use the ____________________ data type to store a date that includes a time zone offset.

datetimeoffset

If neither of two transactions can be committed because they each have a lock on a resource needed by the other, it's called __________

deadlock

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

default value

A cursor that's sensitive to all changes to the database after the cursor is created is called a/an ______________________________ cursor

dynamic

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

The integer and decimal datatypes are considered ___________ datatypes

exact numeric

A lock that allows only one transaction to access a resource is called a/an ____________________ lock

exclusive

The CAST and CONVERT functions are used to perform _________________

explicit conversion

T/F When using the MERGE statement, you can have at most a single WHEN clause.

false

Describe the FETCH RELATIVE cursor call

fetches rows after the last row fetched

Describe the FETCH PRIOR cursor call.

fetches rows before the last row fetched

Describe the FETCH ABSOLUTE cursor call

fetches rows from the beginning of the result set

The simple CASE function returns the result expression associated with the ______ test that results in an equal condition

first

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

identity

Code a statement that tests if the database named TestDB exists

if DB_ID ('TestDB') IS NOT NULL

If you assign a value with one datatype to a column with another datatype, SQL Server converts the value to the column datatype using ____________________.

implicient conversion

What are the names of the 2 special tables that are available while an UPDATE trigger is active?

inserted & copy

When you perform a division operation on 2 integers, the result is a ________.

int

Global cursors, if not closed, continue to use server resources until _______

it's closed

Locking is automatically enabled and controlled by a SQL Server application called ______

lock-manager (or some bullshit like that)

SQL Server automatically avoids some concurrency problems by using ___________

locks

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

precompiled

To reverse an operation, a trigger can execute the ______________________ statement

rollback

The main difference between cursor-based processing and other SQL processing is that cursors work with one ______________________________ of a result set at a time

row

The most fine-grain resource locking level is ____________

row

A cursor that can be used to retrieve a row before or after the current row is called a/an ______________________________ cursor

scrollable

A cursor that is "aware" of changes to the database after the cursor is created is called ____________

sensitive

Identify the 2 categories of lock modes in SQL Server

shared & exlusive

A cursor that is insensitive to any changes to the database is called a/an ______________________________ cursor

static

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

system catalog

A local variable that can store an entire result set is called a/an _______________________ variable

table

SQL Server cursors can be implemented through standard database APIs and through __________

transact-SQL

If you don't group statements into explicit transactions, SQL Server automatically treats each SQL statement as a separate _________

transaction

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

triggers

Describe the Halloween Problem

update operation causes a change in the physical location of a row

The _____________________ of a decimal value indicates the total number of digits that can be stored in a decimal data type.

precision

The ROW_NUMBER, RANK, DENSE_RANK, and NTILE functions are known as _________ functions

ranking

The ranking functions make it easy to include a column in a result set that provides the sequential ranking number of each row within a/an ________________

result set (partition)

To delete an existing view, you use the ______________________________ statement.

DROP VIEW

Which SQL Server datatype is/are considered the highest order precedence when performing implicit datatype conversion?

Date/Time Category

T/F Failure to include a WHERE clause in an UPDATE statement is a syntax error.

False (All rows in the table will be updated)

Describe the CHARINDEX() function

Finds the find string in the search string starting at pos. Returns the position where it first occurs. CHARINDEX(find, search[ , pos]);

Describe how the following statements are different: delete Invoices; delete from Invoices where 1 > 0;

First deletes all rows from table Second deletes rows only where the statement is true

SQL Server does not support the ANSI TRIM function, how would you implement it using built-in SQL Server functions?

LTRIM & RTRIM

Describe the EOMONTH() function

Last of of the month

Describe the following SQL statement: MERGE BookInventory bi USING BookOrder bo ON bi.TitleID = bo.TitleID WHEN MATCHED THEN UPDATE SET bi.Quantity = bi.Quantity + bo.Quantity WHEN NOT MATCHED BY TARGET THEN INSERT (TitleID, Title, Quantity) VALUES (bo.TitleID, bo.Title,bo.Quantity);

Merges bi and bo together and then they are matched they they resulting table updates the quantity in bi, and when they are not matched then a new row is inserted with the specified values.

Describe the DATEDIFF() function

Number of dateparts between the dates DATEDIFF(datepart, startdate, enddate);

Write the code for an ORDER BY clause that sorts a table into numeric sequence by the data in the PartCode column if that column contains whole numbers that are stored with the varchar data type:

ORDER BY CAST (Partcode AS INT) ASC;

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

To repeatedly execute a statement or set of statements, you code a/an _________________________ statement

WHILE

If RegistrationDate contains a value that's equivalent to August 10, 2012, what will the Solution column contain when this code is executed? DATEPART(month, RegistrationDate) a. 8 c. August b. Aug d. August, 2012

a

Unlike scripts you write in SQL Server Management Studio, that can be stored as files, views are stored __________

as an object

If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? CASE WHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2) WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2) ELSE 0 END AS Solution a. 200.00 c. 25 b. 225.00 d. 0

b

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

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

b

To store a date value without storing a time value, you can use the a. time data type c. datetime data type b. date data type d. datetime2 data type

b

If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? LEN(LTRIM(RTRIM(CustomerAddress))) a. 23 c. 19 b. 21 d. 16

c

Which of the following statements is true about the CONVERT and TRY_CONVERT functions? a. CONVERT can format the result of a conversion, but TRY_CONVERT can't. b. CONVERT can be used with any data type, but TRY_CONVERT can't. c. CONVERT returns an error if the expression can't be converted, but TRY_CONVERT returns a NULL value. d. CONVERT is an ANSI-standard function, but TRY_CONVERT is not.

c

Which uses the least amount of storage? a. 'example' stored in a column of type varchar(20) b. 'exam' stored in a column of type varchar(20) c. 'ex' stored in a column of type varchar(20) d. they all use the same amount of storage

c

If FirstName contains "Edward" and LastName contains "Williams", what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution a. EWilliams c. ewilliams b. EWilliam d. ewilliam

d

If ZipCode is a varchar column that contains the value 93702, what will the Solution column contain when this code is executed? ISNUMERIC(ZipCode) a. true c. 1 b. false d. 0

d

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

The date/time data types can store a. dates only c. times only b. dates and times d. all of the above

d

The ____________________ data types are used to store whole numbers.

integer

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

null

Describe the difference between the varchar and nvarchar datatypes in SQL Server

nvarchar is Unicode varchar: 1-8000 nvarchar: 1-4000

Describe the sys.columns view

returns a row for all columns in the database

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

scalar

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

script

To insert several rows selected from another table into a table, you can code an INSERT statement with a/an ______________ in place of the VALUES clause.

subquery

A temporary table is stored in the system database named __________

tempdb

The MAX specifier can be used with the varchar, nvarchar, and varbinary datatypes to increase the capacity of a column up to _______ __________.

two gigs

____________________ characters can be used to encode the characters that are used in languages throughout the world.

unicode

Since the MERGE statement includes both a MATCH and NOT MATCHED clause, it can be used to both _________ data and to __________ data in a table.

update, insert

A view that can be used in INSERT, UPDATE, and DELETE statements is called _______

updateable

The MERGE statement is sometimes referred to as the _________________ statement.

upsert