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