CSE581_quiz1-12

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

null

After you identify and subdivide all of the data elements for a database, you should group them by the entities with which they're associated. The entities will become the tables of the database, and the elements will be come the a. columns b. cells c. indexes d. rows

columns

What is used to structure the data in an XML document? a. XML tags b. root tags c. start tags d. parent tags

a xml tags

Write an aggregate expression to find the VendorName column that's last in alphabetical order

a. MAX(VendorName)

Which statement can you use to manually raise an error within a stored procedure?

throw

When you use weekday with the DATEPART function, it returns an integer that indicates the day of the week where

1=Sunday, 2=Monday, etc

A well-formed XML document can have only one of which type of element? a. child b. EventType c. root d. parent

c

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

You specify the conditions that must be met for a row to be deleted in the which clause?

WHERE

A subquery that's used in a WHERE or HAVING clause is called what?

a subquery search condition

Which statement is true about the DENY and REVOKE statements? a. A denied permission can't be granted by role membership, but a revoked permission can b. Both DENY and REVOKE work exactly the same c. Neither a revoked permission or a denied permission can override a permission granted by role membership d. A denied permission can be granted by role membership, but a revoked permission cannot

a. A denied permission can't be granted by role membership, but a revoked permission can

To normalize a data structure, what do you apply in sequence? a. none of the above b. composite indexes c. indexes d. normal forms

normal forms

A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.

table

To model a database on a real-world system, you typically represent each real-world entity as a

table

A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with.

diagram

Code example 14-2 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); (Refer to code example 14-2.) When does the expression on the WHILE statement in this script cause the loop to end? a. When the value of the @Total variable plus the value of the @InvoiceTotal variable becomes greater than 200,000 b. When the value of the @Total variable becomes greater than 200,000 c. When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000 d. When the value of the @InvoiceTotal variable becomes less than 1000

c. When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000

If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, which is the default setting? a. NOT NULL b. This will throw an error c. none of the above d. NULL

null

The COALESCE function provides one way to substitute constant values for which values?

null

The GROUPING function lets you substitute another value for a/an ____________________ value when you use the ROLLUP or CUBE operator.

null

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

omitting the parameter name and value from the list

The most common type of relationship between two tables is called what?

one-to-many relationship

To override the order of precedence in an arithmetic expression, you can use

parentheses

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

partition

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

precompiled

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

preventing errors due to invalid data

What do you use to uniquely identify each row in a table?

primary key

What uniquely identifies each row in a table?

primary key

What do you typically use to relate two tables that have a one-to-one relationship? a. primary keys b. composite keys c. indexes d. foreign keys

primary keys

The users, groups, logins, and roles that have access to a server are called

principals

After you create a schema, you can create any object within that schema by a. getting permission from one of the principals b. defining a role with you in it c. qualifying the object name with the schema name d. restarting the server

qualifying the object name with the schema name

When you use the Management Studio to create a foreign key constraint, you specify the relationship between two tables as well as the rules for enforcing what?

referential integrity

Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it.

restore

A full outer join includes rows that satisfy the join condition, plus

rows in both tables that don't satisfy the join condition

In a cross join, all of the rows in the first table are joined with all of the

rows in the second table

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

scalar

Unlike most database objects, when you invoke a user-defined function, you must always preface the name of the function with the

schema name

To express the value of a floating-point number, you can use

scientific notation

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

script

Whenever you use the Management Studio to create, alter, or delete database objects, you can save the ________________ that it used for doing that. a.log file b.date c.schema d.script

script

An index improves performance when SQL Server ______________________.

searches a table

The entities that can be secured on a server are called

securables

Which of the following types of statements isn't an action query?

select

The highest level at which you can grant permissions is the ________________ level.

server

SQL Server supports ________________ of the ANSI-standard data types

some, but not all

In an UPDATE statement, the WHERE clause will

specifiy the condition a row must meet to be updated

A SELECT statement that includes aggregate functions is often called a/an ________________ query.

summary

If introduced as follows, the subquery can return which of the values listed below?FROM (subquery)

table

This is typically modeled after a real-world entity, such as an invoice or a vendor.

table

When you use the Check Constraints dialog box, all of the constraints are at the which level so they can refer to any of the columns in the table? a. column b. database c. value d. table

table

A relational database consists of one or more what?

tables

If you define a column with a default value, that value is used whenever a row

that doesn't include a value for that column is added to the table

If you delete a stored procedure, function, or trigger and then create it again

you delete the security permissions assigned to the object

Which of the following diagrams best represents the relationship between a table of customers and a table of orders placed by customers?

| Customers / CustomerID, CustomerName |------<| Orders / OrderID, CustomerID, OrderDate |

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

225.00

If ExpirationDate contains a value that's equivalent to June 2, 2016 and the GetDate function returns a value that's equivalent to July 17, 2016, what will the Solution column contain when this code is executed?DATEDIFF(day, ExpirationDate, GetDate()) AS Solution

45

SQL statements that define the tables in a database are referred to as ________________ statements.

Data Definition Language (DDL)

Which of the following isn't a common error when entering and executing SQL statements?

Forgetting to attach the required database

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

INSTEAD OF DROP_VIEW

Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?

InvoiceTotal - CreditTotal - PaymentTotal / 10

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?

InvoiceTotal IN (0,1000)

Which function would you use to retrieve data from a subsequent row in a result set? And which function would you use to retrieve data from a previous row?

LEAD, LAG

After locating the characters that separate the components of a string you wish to parse, you can use which functions to extract the individual components?

LEFT, RIGHT, SUBSTRING, and LEN

Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?

LIKE

Which function returns the string with any leading spaces removed?

LTRIM

Which clause specifies the number of rows that should be skipped before rows are returned from the result set?

OFFSET

How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?

ON DATABASE

Which keyword can you use to pass parameter from a stored procedure back to the calling program?

OUTPUT

Which column or columns in each table should be defined as the primary key?

Orders: OrderID OrderLineItems: OrderID and OrderSequence Products: ProductID

Which function would you use to calculate the rank of the values in a sorted set of values as a percent?

PERCENT_RANK

You can code one or more ________________ within the start tag of an element. a. contents b. definitions c. documents d. attributes

attributes

When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

Which of the following is the default transaction isolation level for SQL Server?

READ COMMITTED

Which of the following statements returns the value of a variable named @InvoiceCount?

RETURN @InvoiceCount;

In Management Studio, if a statement returns data, that data is displayed in the __________ tab.

Result tab

Which of the following diagrams best represents the relationship between a table of students and a table of classes for which the student is registered?

Students: StudentID, StudentName; StudentToClasses: StudentID, ClassID Classes:studentID, studentName

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?

TOP 10 InvoiceNumber

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

TRY...CATCH

You use the UPDATE statement to modify one or more rows in the table named in the ________________ clause.

UPDATE

Code a statement that changes the database context to a database named TestDB. a. EXEC TestDB; b. none of the above c. GO TestDB; d. USE TestDB;

USE TestDB;

___________________ characters can be used to encode most of the characters in most of the languages of the world.

Unicode

Which of the following recommendations won't improve the readability of your SQL statements?

Use comments to describe what each statement does.

When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause?

WHERE

Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause?

WHERE

A search condition in the ________________ clause is applied before the rows are grouped while a search condition in the _________________ clause isn't applied until after the grouping.

WHERE, HAVING

What is lock escalation? a. the conversion from a less restrictive transaction isolation level to a more restrictive transaction isolation level 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 of an exclusive lock to an intent lock

b. the conversion of several finer-grained locks to a single coarse-grain lock

What kind of constraint limits the values that can be stored in a column? a. integrity constraint b. check constraint c. values constraint d. size constraint

b check

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

b. A trigger can have more than one batch.

Which of the following is true about a transaction? a. By default, each SQL statement is treated as a separate transaction. b. all of the above c. You can combine any number of SQL statements into a single transaction. d. It is a group of database operations that are combined into a logical unit.

b. all of the above

The CREATE TABLE statement a. creates a new table in the specified database b. creates a new table in the current database c. specifies attributes for an existing table d. all of the above

b. creates a new table in the current database

Which is not one of the four types of concurrency problems? a. dirty reads b. integrity reads c. phantom reads d. nonrepeatable reads

b. integrity reads

To maintain ________________, if you delete a row in a primary key table, you must also delete any related rows in foreign key tables. a. foreign key values b. referential integrity c. primary key restraints d. foreign key restraints

b. referential integrity

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

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

Some database designers write their own SQL statements for creating a database, its tables, and its indexes instead of using the Management Studio. Why? a. They want to have complete control over how the database is created. b. The scripts generated by the Management Studio are harder to understand. c. All of the above d. It's easier to modify your own script if you want to use it to create the same database for another database management system later on.

c. all

The three main hardware components of a client/server system

clients, server, and network

When you need to code multiple conditions in a join, it's best to

code only join conditions in the ON clause

When you code a table-level check constraint, the constraint can refer to data in more than one

column

Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.

column alias

To run a SELECT statement from an application program, you store the statement in the ________________ object for the database connection.

command

To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like

commands, connections, and data readers

SELECT VendorName, InvoiceNumberFROM Invoices LEFT JOIN VendorsON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal

d a.the number of rows in the Invoices table b.the number of rows in the Vendors table c.the number of rows in the Invoices table plus the number of rows in the Vendors table d.none of the above

Which method would you use to parse an xml data type that has been stored in a database into multiple columns? a. query b. nodes c. modify d. value

d value

What can you use to create an XML Schema Definition for an XML document? a. Table Designer b. Subquery Editor c. Query Editor d. XML Editor

d xml editor

Which of the following statements creates a database user in the current database from a TomBrown SQL Server login ID? a. CREATE DB USER TomBrown; b. EXEC USER TomBrown; c. USER TomBrown; d. CREATE USER TomBrown;

d. CREATE USER TomBrown;

Code example 5-1SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,SUM(InvoiceTotal) AS InvoiceAvgFROM Invoices JOIN VendorsON Invoices.VendorID = Vendors.VendorIDWHERE VendorState < 'e'GROUP BY VendorState, VendorCity, VendorNameHAVING SUM(InvoiceTotal) > 500ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) When this summary query is executed, the result set will contain one summary row for

each vendor with invoice totals over $500

If you try to move a database file that's attached to a server, you'll get an error message that indicates the file is in use. To get around this, you need to ________________ the database from the server.

detach

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

ewilliam

Users who are involved in the administration of the server are typically assigned to one of the ________________ roles that are built into SQL Server. a. fixed server b. dbcreator c. fixed database d. sysadmin

fixed server

To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table

foreign key

The relative amount of data that's included in a resource can be referred to as the resource's

granularity

You can use the OVER clause with an aggregate function to

include the rows used to calculate the summary in the result set

When you code a union with the INTERSECT keyword to combine two result sets, the union

includes only rows that occur in both result sets

Which data types are used to store whole numbers?

integer

This indicates that SQL Server plans to obtain a shared lock or an exclusive lock on a finer-grain resource.

intent lock

The CUBE operator is similar to the ROLLUP operator except that

it adds summary rows for every combination of groups

Which method would you use to split the nodes of the current xml data type into rows? a. value b. nodes c. modify d. query

nodes

Which statement can you use to create a user-defined database role?

CREATE ROLE

Which function returns the specified number of characters from the string starting at the specified position?

SUBSTRING

If introduced as follows, the subquery can return which of the values listed below?WHERE InvoiceTotal > ALL (subquery)

.a column of one or more rows

Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) The total number of rows returned by this query must equal

.the number of rows in the Invoices table

You can invoke a table-valued user-defined function

anywhere you'd refer to a table or a view

To return all of the columns from the base table, which wildcard character do you include in the SELECT clause?

*

To concatenate character strings in a string expression, which operator do you use?

+

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values

AVG(InvoiceTotal)

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

CREATE TABLE

___________________ names can be used when you want to assign a temporary name to a table.

Correlation

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

DELETE Vendors

To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause?

DESC

By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?

DISTINCT

To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause?

DISTINCT

To delete an existing view, you use which statement?

DROP VIEW

Which of the following statements creates a login ID for a Windows user named AliceJackson in a domain named Sales?

CREATE LOGIN [Sales\AliceJackson] FROM WINDOWS;

If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed?LEN(LTRIM(RTRIM(CustomerAddress))) AS Solution

19

What would 7,800,000,000 be using scientific notation?

7.8 X 109 or 7.8E+9

If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed?DATEPART(month, RegistrationDate) AS Solution

8

To modify an existing view, you use which statement?

ALTER VIEW

The search condition of a WHERE clause consists of one or more

Boolean expresssions

If a string consists of one or more components, you can parse it into its individual components. To locate the characters that separate the components, you would use which function?

CHARINDEX

To locate the index of the first character of the first occurence of a substring within another string, you would use which function?

CHARINDEX

Write an aggregate expression for the number of entries in the VendorName column, including null values

COUNT(*)

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?

CREATE

The varchar and nvarchar data types to store variable-length strings. Which of the statements below is true? a.Data stored using these data types occupies only the number of bytes needed to store the stringb.all of the abovec.In general, they are less efficient than fixed-length strings.d.They are typically used to define columns with a fixed number of characters.

Data stored using these data types occupies only the number of bytes needed to store the string

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

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.

EXEC @InvoiceCount = spInvoiceCount;

Which of the following statements calls the stored procedure and passes the values '2015-12-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; ______________________________________________

EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2015-12-01';

Which of the following statements calls the following stored procedure, passes the value '2015-12-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.)

EXEC spInvoiceTotal2 '2015-12-01', @MyInvoiceTotal OUTPUT;

To test whether one or more rows are returned by a subquery, you can use which operator?

EXISTS

If you want to use the Management Studio to modify the data for a table, you can right-click on the table and select what?

Edit Top 200 Rows command

What can you use to convert relational data that's stored in a database to XML? a. OPENXML statement b. FOR XML clause of the SELECT statement c. OPEN XML clause of the SELECT statement d. FORXML statement

FOR XML clause of the SELECT statement

Correlation names are temporary table names assigned in which clause?

FROM

The six clauses of the SELECT statement must be coded in the following order:

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Which of the following is the most restrictive transaction isolation level?

SERIALIZABLE

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

SET @Name = 'Test';

A graphical tool that you can use to start and stop the database server is called what?

SQL Server Configuration Manager

When using the Query Designer, where is the generated SQL statement displayed?

SQL pane

The Query Editor of the Management Studio lets you enter and execute all types of

SQL statement

The Query Editor of the Management Studio lets you enter and execute all types of

SQL statements

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

SQLCMD

In the INSERT statement that follows, assume that all of the table and column names are spelled correctly, that none of the columns are identity columns, and that none of them have default values or accept null values. What's wrong with the statement?INSERT INTO InvoiceCopy (VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal,TermsID, InvoiceDate, InvoiceDueDate) VALUES(97, '456789', 8344.50, 0, 0, 1, '2016-08-01');

The number of items in the column list doesn't match the number in the VALUES lis

Which of the statements below best describes the result set returned by this SELECT statement?SELECT VendorState, COUNT(*) AS Column2FROM VendorsGROUP BY VendorStateHAVING COUNT(*) > 1;

The number of vendors in each state having more than one vendor

Which of the statements below best describes the result set returned by this SELECT statement?SELECT VendorID, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2FROM InvoicesWHERE InvoiceTotal - PaymentTotal - CreditTotal > 0GROUP BY VendorID;

The total unpaid balance due for each VendorID

What SQL dialect does Microsoft SQL Server use?

Transact-SQL

To log on to SQL Server using your Windows login ID, you use ________________ authentication.

Windows

Code example 18-1 <Error ErrorNumber = 6901> <ErrorSeverity>16</ErrorNumber> <ErrorMessage>XML Validation: XML instance must be a document.</ErrorMessage> </Error> (Refer to code example 18-1.) Which of the following SELECT clauses could you use to retrieve the value of the ErrorNumber attribute? Assume that the XML document in the example is stored in a column named ErrorData. a. SELECT ErrorData.value('(Error/@ErrorNumber)[1]', 'int') AS ErrorNumber b. SELECT ErrorData.value('(Error/ErrorNumber)', 'int') AS ErrorNumber c. SELECT ErrorData.value('(Error/ErrorNumber)[1]', 'int') AS ErrorNumber d. SELECT ErrorData.value('(Error/@ErrorNumber)', 'int') AS ErrorNumber

a

The highest-level element in an XML document is called the what? a. root element b. parent element c. child element d. super element

a

What can you use to convert XML data into relational data? a. OPENXML statement b. FORXML statement c. OPEN XML clause of the SELECT statement d. FOR XML clause of the SELECT statement

a

Which statement can you use to explicitly start a transaction? a. BEGIN TRAN b. GO c. START d. EXEC

a begin tran

When a column in a table is defined, what determines the kind of data it can store?

a data type

If you define a column as an identity column,

a number is generated for that column whenever a row is added to the table

To be in the first normal form, each cell in a table must contain

a single, scalar value

Each of the outcomes listed below is a result of executing the following script except for one. Which one? CREATE ROLE ExampleRole; ALTER ROLE db_datareader ADD MEMBER ExampleRole; GRANT INSERT,UPDATE ON Vendors TO ExampleRole; DENY INSERT ON Vendors TO ASmith; ALTER ROLE ExampleRole ADD MEMBER ASmith; a. By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables. b. The user named ASmith is granted UPDATE permission to the Vendors table and SELECT permission to all user tables. c. By being assigned to the role ExampleRole, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables. d. The user named ASmith can't be granted INSERT permission to the Vendors table by being assigned to the role ExampleRole.

a. By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.

A dirty read occurs when a. a transaction selects data that isn't committed by another transaction b. two transactions select the same row and then update the row based on the values originally selected c. 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 d. 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

a. a transaction selects data that isn't committed by another transaction

An identifier a. can contain a number sign (#) b. all of the above c. can contain spaces d. can be a Transact-SQL reserved keyword

a. can contain a number sign (#)

A user who's granted the EXECUTE object permission can execute what? a. a stored procedure or function b. a DELETE query through dynamic SQL c. a GRANT statement d. a SELECT query

a. a stored procedure or function

You use data definition language (DDL) to create, modify, and delete the ________________ of a database. a. all of the above b. objects c. tables d. sequences

a. all

In the View Designer, you can a. all of the above b. edit the design of an existing view c. display the results of a view d. specify the selection criteria and sort order for a view

a. all of the above

SQL Server's lock manager always tries to lock resources a. at the highest possible granularity b. with an exclusive lock c. with a shared lock d. at the lowest possible granularity

a. at the highest possible granularity

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This a. reduces redundancy and makes maintenance easier b. increases redundancy but makes maintenance easier c. reduces redundancy but makes maintenance more difficult d. increases redundancy but makes the data more consistent

a. reduces redundancy and makes maintenance

Code example 18-1 <Error ErrorNumber = 6901> <ErrorSeverity>16</ErrorNumber> <ErrorMessage>XML Validation: XML instance must be a document.</ErrorMessage> </Error> (Refer to code example 18-1.) Which of the following statements could you use to determine if the @ErrorData variable contains the ErrorMessage element? Assume that the @ErrorData variable is declared with the xml data type and that it contains the XML data shown in the example. a. IF @ErrorData.exist('/Error/ErrorMessage') = 1 b. IF @ErrorData('ErrorMessage').exist c. IF @ErrorData.exist('ErrorMessage') d. IF @ErrorData('/Error/ErrorMessage').exist = 1

a.IF @ErrorData.exist('/Error/ErrorMessage') = 1

What kind of lock only allows one transaction to access a resource? a. exclusive b. fine-grain c. coarse-grain d. combination

a.excluisive

Where would you go to create, modify, or delete logins using the Management Studio? a. the Security folder for the server b. the Users folder for the database c. the Users folder for the server d. the Security folder for the database

a.the Security folder for the server

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

a.the conversion of a less exclusive lock to a more exclusive lock

To be in the third normal form, a. each non-key column must depend on the entire primary key b. each non-key column must depend only on the primary key c. the non-key columns must not contain repeating values d. all of the above

all

When you use Transact-SQL, you can store procedural code in a. scripts b. user-defined functions c. all of the above d. stored procedures

all

The char and nchar data types are used to store fixed-length strings. Which of the statements below is true? a. all of the above b. Data stored using these data types occupies the same number of bytes regardless of the actual length of the string. c. The char(2) data type will always contain two characters d. They are typically used to define columns that have a fixed number of characters

all of the abve

Which statement is used to modify the structure of an existing table? a. UPDATE b. CHANGE TABLE c. ALTER TABLE d. CREATE TABLE

alter

A combination of column names and operators that evaluate to a single value is called

an expression

The first character of an identifier must be a. a letter as defined by the Unicode Standard 2.0 b. an underscore (_) c. an at sign (@) d. any of the above

any

You can create a database diagram for

any combination of the tables in a database

If you have the files for an existing SQL Server database, the easiest way to create the database is to ________________ those files to the database server.

attach

Which of the following INSERT statements inserts a row into a table named ErrorLog that consists of an identity column and a column that contains XML data? a. INSERT INTO ErrorLog ('<Error>An error occurred</Error>'); b. INSERT INTO ErrorLog VALUES('<Error>An error occurred</Error>'); c. UPDATE ErrorLog VALUES('<Error>An error occurred</Error>'); d. INSERT INTO ErrorLog VALUES('<Error>An error occurred</Error>' xml);

b

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

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

Which statement do you use to add an XML Schema Definition to a database? a. CREATE XML SCHEMA b. CREATE XML SCHEMA COLLECTION c. CREATE XML COLLECTION d. CREATE XML

b. CREATE XML SCHEMA COLLECTION

Which of the following is not a good guideline for deciding when to create an index for a column? a. The column is a foreign key. b. The column is frequently updated. c. The column contains a large number of unique values. d. The column is frequently used in search conditions or joins.

b. The column is frequently updated.

Each of the following techniques can help to prevent deadlocks except for one. Which one? a. If you code two transactions that update the same resources, code the updates in the same order in each transaction. b. Use the highest transaction isolation level possible. c. Don't leave transactions open any longer than necessary. d. Schedule transactions that modify a large number of rows to run when no other transactions will be running.

b. Use the highest transaction isolation level possible.

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

b. decrements @@TRANCOUNT and commits the transaction

Code example 14-1 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'; (Refer to code example 14-1.) If the current date is 04/04/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script? a. Earliest past due date: 02/09/16 b. Earliest past due date: 02/09/16 Latest past due date: 03/20/16 c. Nothing d. No invoices past due

b.Earliest past due date: 02/09/16 Latest past due date: 03/20/16

Code a statement that tests whether the database named TestDB exists.

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

Which statements are true about the ROUND function? a.To truncate the function rather than round it, code a zero value for the function b.It returns the number rounded to the specified precision c.If the length is positive, the digits to the left of the decimal point are rounded d.all of the above

b.It returns the number rounded to the specified precision

If neither of two transactions can be committed because they each have a lock on a resource needed by the other, it's called a/an a. schema lock b. intent lock c. deadlock d. update (U) lock

b.deadlock

One of the drawbacks of using the SERIALIZABLE isolation level is a. it can cause security problems b. it can cause severe performance problems c. it allows too many transactions to be executed at the same time d. it can result in lost updates

b.it can cause severe performance problems

In a/an ________________, a table can contain information about two or more entities. a.none of the above b.unnormalized data structure c.foreign key constraint d.normalized data structure

b.unnormalized data structure

Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the datetime data type and which might include time values? a.convert each datetime value to a date value b.use the DatePart function to extract just the date from each datetime value c.search for datetime values that fall between the day before and the day after the date you want d.use the Day, Month, and Year functions to do the search

b.use the DatePart function to extract just the date from each datetime value

Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID;(Refer to code example 4-1.) This join is coded using what syntax?

both b and c explicit SQL-92

An XML schema can prevent all but one of the following from being stored in a column or variable declared with the xml type. Which one? a. a document with tags that don't match the tags specified by the schema b. a string that doesn't use XML tags c. a null value d. a document that doesn't contain all the tags specified by the schema

c

An element that is contained within another element is called what? a. super element b. parent element c. child element d. root element

c

Check constraints you create using DDL can be defined at the a. table level b. column level c. both a and b d. none of the above

c

What can you call an element that contains other elements? a. sub element b. child element c. parent element d. super element

c

What kind of constraint enforces referential integrity between tables? a. reference constraint b. foreign key constraint c. both a and b d. none of the above

c

Which of the following defines a table column named ErrorData that can contain XML data and that will be validated using an XML schema named ErrorDataSchema? Assume that the column can't contain null values. a. Error xml (ErrorDataSchema) NOT NULL b. ErrorData xml = NOT NULL c. ErrorData xml (ErrorDataSchema) NOT NULL d. ErrorData xml (ErrorDataSchema) NULL

c

When you define a foreign key constraint, you can specify all but one of the following. Which one is it? a. that no action should be taken and an error should be returned when a related row in a related table is deleted or updated b. that the deletion of a related row in a primary key table should be cascaded down to the rows in the foreign key table c. that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table d. that the updating of a primary key in a primary key table should be cascaded down to the rows in the foreign key table

c. that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table

Which of the following does not violate the referential integrity of a database? a.inserting a new row into a table with a foreign key that doesn't match a primary key in the related table b. updating a primary key in a primary key table without also updating the foreign keys for the related rows in all related tables c. deleting a row in a foreign key table without deleting the related row in the related primary key table d.updating a foreign key with a value that doesn't match a primary key in the related table

c. deleting a row in a foreign key table without deleting the related row in the related primary key table

The basic code structure for many SQL statements and objects can be found in which section of the SQL Server Management Studio? a. DDL b. Code Warehouse c. Transact-SQL snippets d. Query Editor

c. transact-sql

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 column alias SumOfInvoices is invalid b. will succeed c. will fail because the ORDER BY clause isn't allowed in this view d. will fail because the GROUP BY clause isn't allowed in this view

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

To log on to SQL Server using your SQL Server login ID, you use ________________ authentication. a. Windows b. mixed c. SQL Server d. server

c.SQL Server

If you want to make sure that the data you attempt to store in the xml data type is valid, you can a. convert the data to the varchar type b. none of the above c. specify an XSD for the xml data type when you define a column or variable d. use the ISVALID function to check the data before storing it as XML

c.specify an XSD for the xml data type when you define a column or variable

A user-defined function

can return a single scalar value or a single table value

Expressions coded in the HAVING clause

can use either aggregate search conditions or non-aggregate search conditions

One way to examine the system objects that define a database is to use which views? a. catalog b. derived c. none of the above d. base

catalog

The intersection of a row and a column is commonly called what?

cell

Code example 6-2WITH Top5 AS(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoiceFROM InvoicesGROUP BY VendorIDORDER BY AvgInvoice DESC)SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoiceFROM Invoices JOIN Top10ON Invoices.VendorID = Top10.VendorIDGROUP BY Invoices.VendorIDORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top5 is coded as a

common table expression (CTE)

In Management Studio, the Query Editor uses the IntelliSense feature to automatically display ________________ lists that you can use to enter parts of the SQL statement.

completion

When you subdivide a data element, you can easily rebuild it when necessary by ________________ the individual components.

concatenating

The IIF function determines the value it returns based on what type of expression?

conditional

When you use the CREATE TABLE statement to create a table, you can also define the attributes and ______________ for the columns. a. values b. data types c. all of the above d. constraints

constriants

When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is ________________ a new table.

copied into

All of the following statements about application roles are true except for one. Which one? a. Once a connection activates an application role, its security profile can only be changed back to that of the login ID if a cookie is created when the role was activated. b. Once a connection activates the application role, its security profile changes from that of the login ID to that of the application role. c. An application role is activated by executing a stored procedure. d. Unlike a standard database role, an application role can contain only one member.

d. Unlike a standard database role, an application role can contain only one member.

By default, SQL Server is in autocommit mode, this means a. all of the above b. if a statement does not commit an error, it is automatically rolled back c. if a statement causes an error, it's automatically committed d. unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction

d. unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction

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

d. 'ex' stored in a column of type varchar(20)

The dbcreator role a. allows new members to be added to the role b. lets members create, alter, and drop databases c. is intended for those users who need to be able to work with database objects d. all of the above

d. all of the above

A union combines the rows from two or more what? a.queries b.SELECT statements c.result tables d.all of the above

d. all of the above (queries + SELECT + result tables)

The WITH SCHEMABINDING clause a. prevents a row in a view form being updated if that would cause the row to be excluded from the view b. protects a view by binding it to the database structure c. prevents underlying base tables from being deleted or modified in any way that affects the view d. both b and c

d. both b and c b.protects a view by binding it to the database structure c.prevents underlying base tables from being deleted or modified in any way that affects the view

Before you can delete a server role, you must a. create the DROP SERVER ROLE b. delete the server c. DROP AUTHORIZATION d. delete all of its members

d. delete all of its members

Code a statement that creates a table variable named @TestTable. a. SET @TestTable = table; b. DECLARE @table TestTable; c. CREATE @TestTable; d. DECLARE @TestTable table;

d.DECLARE @table TestTable;

What is the name of the language that's designed to query an XML document? a. XML DML b. QUERYX c. QUERYX-TREME d. XQUERY

d.XQUERY

Concurrency is: a. the ability of a system to support two or more transactions working with the same data at the same time b. more of a problem on larger systems with more users and more transactions c. only a problem when data is being modified, not when two or more transactions simply read the same data d. all of the above

d.all

Which of the following statements about the SPARSE attribute is true? a. optimizes the storage of null values for a column b. requires more overhead to retrieve non-null values c. you should only use it when a column contains a high percentage of null values d. all of the above

d.all

You can use the GRANT statement to give users permission to use each of the following items except for one. Which one? a. a DDL statement b. all the objects in a schema c. a database object d. all the objects in a database

d.all the objects in a database

A user who's granted the REFERENCES object permission can do what? a. refer to the object in a subquery b. select but not insert, update, or delete data in the object c. all of the above d. create objects that refer to the object

d.create objects that refer to the object

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

d.prevents an update from being performed through the view if it causes a row to no longer be included in the view

To store and manage the databases of the client/server system, each server requires what?

database management system (DBMS)

To store a date value without storing a time value, you can use the

date date type

You can use what data type to store a date that includes a time zone offset?

datetimeoffset

In many cases, a subquery can be restated as a/an ______________.

join

You can use a ________________ in the ________________ clause if you need to specify column values or search conditions other than the one named in the UPDATE clause.

join, from

When you use the Query Editor to run a query that returns an xml type, the Management Studio displays the XML data in blue with underlining to indicate that it is what? a. XML blue b. XML data c. misspelled d. a link

link

If two tables have a many-to-many relationship, you need to define a _____________ table that relates their records.

linking

SQL Server automatically avoids some concurrency problems by using what?

locks

A SQL Server database consists of two files: a database file and a ___________.

log file

To allow users to log on using either type of authentication, you need to set the SQL Server authentication to ________________ mode.

mixed

For each type of action query, a table can have

multiple AFTER triggers and one INSTEAD OF trigger

When you identify the data elements in a new database, you typically subdivide data elements into

the smallest practical components

Typically, what form do most database designers consider a database structure to be normalized?

third

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

transaction

If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaludate to?ISNUMERIC(ZipCode) AS Solution

true

When you use the Management Studio to create a check constraint, you can specify whether you want the constraint enforced for insert or ________________ operations. a. invoice b. update c. delete d. log

update

When you use Windows authentication to connect to a database, SQL Server

uses the login name and password that you use for your PC to authorize your connection

Which choice below will increase the storage capacity of a varchar column so it can store up to 2 gigabytes of data?

varchar(max)

In a join, column names only need to be qualified where?

when the same names are used in both tables