15,16,17
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';
To log on to SQL Server using your SQL Server login ID, you use ________________ authentication.
SQL Server
When you use Transact-SQL, you can store procedural code in
Stored procedures, scripts, or user-defined functions
You can use the GRANT statement to give users permission to use each of the following items except for one. Which one?
all the objects in a database (a database object, a DDL statement, all the objects in a schema)
SQL Server's lock manager always tries to lock resources
at the highest possible granularity
A user who's granted the REFERENCES object permission can do what?
create objects that refer to the object
To make a parameter for a stored procedure optional, what do you assign to it?
default value
Before you can delete a server role, you must
delete all of its members
Users who are involved in the administration of the server are typically assigned to one of the ________________ roles that are built into SQL Server.
fixed server
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 on INSTEAD OF trigger
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
qualifying the object name with the schema name
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
Where would you go to create, modify, or delete logins using the Management Studio?
the Security folder for the server
What is lock promotion?
the conversion of a less exclusive lock to a more exclusive lock
What is lock escalation?
the conversion of several finer-grained locks to a single coarse-grain lock
A lost update occurs when
two transactions select the same row and then update the row based on the values originally selected
By default, SQL Server is in autocommit mode, this means
unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction
If you delete a stored procedure, function, or trigger and then create it again
you delete the security permissions assigned to the object.
Which statement is true about the DENY and REVOKE statements?
A denied permission can't be granted by role membership, but a revoked permission can
To transfer a database object from one schema to another, you use the which statement?
ALTER SCHEMA
Which statement can you use to explicitly start a transaction?
BEGIN TRAN
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;
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.
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 SQL Server login ID for a user named TomBrown with the password 'abc123XYZ'.
CREATE LOGIN TomBrown WITH PASSWORD = 'abc123XYZ';
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 statement can you use to create a user-defined database role?
CREATE ROLE
Which of the following statements creates a database user in the current database from a TomBrown SQL Server login ID?
CREATE USER TomBrown;
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;
Which option can you use to make SQL Server prompt the user for a new password the first time the login ID is used?
MUST_CHANGE
How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?
ON DATABASE
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
All of the following statements about application roles are true except for one. Which one?
Unlike a standard database role, an application role can contain only one member.
The dbcreator role
all of the above (is intended for those users who need to be able to work with database objects, lets members create, alter, and drop databases, allows new members to be added to the role)
To log on to SQL Server using your Windows login ID, you use ________________ authentication.
Windows
A user who's granted the EXECUTE object permission can execute what?
a stored procedure or function
Concurrency is:
all of the above
With the ALTER LOGIN statement, you can
all of the above (enable or disable a login ID, change the name for a login ID, change the default database or language)