CSE581_quiz7-11

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

.CREATE ROLE

To modify an existing view, you use which statement?

ALTER VIEW

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

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;

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

CREATE TABLE

To delete an existing view, you use which statement?

DROP VIEW

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;

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

INSTEAD OF DROP_VIEW

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

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

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

SQLCMD

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

TRY...CATCH

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;

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

Windows

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

a begin tran

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 (#)

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

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

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

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

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

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

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

alter

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 invoke a table-valued user-defined function

anywhere you'd refer to a table or a view

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

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

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

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

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

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

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

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

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

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

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

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.

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.

A user-defined function

can return a single scalar value or a single table value

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

catalog

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

column

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

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

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

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

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

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

d

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

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;

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

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

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

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

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

granularity

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

intent lock

SQL Server automatically avoids some concurrency problems by using what?

locks

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

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

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

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

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

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

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

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

server

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

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

throw

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

transaction

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

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

you delete the security permissions assigned to the object