You work for a company that sells books. You are creating a report for a SQL Server 2005 database. The report will list sales representatives and their total sales for the current month. The report must include only those sales representatives who met their sales quota for the current month. The monthly sales quota is $2,000. The date parameters are passed in variables named @FromDate and @ToDate. You need to create the report so that it meets these requirements. Which SQL query should you use?

Your company uses a SQL Server 2005 database. This database contains a trigger named trg_InsertOrders, which fires when order data is inserted into the Orders table. The
trigger is responsible for ensuring that a customer exists in the Customers table before data is inserted into the Orders table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort. Which two Transact-SQL statements can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month. You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?

You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL
Server instance. You need to identify the most likely cause of the connection issues. What should you do first?

Application developers in your company create an assembly that contains a CLR function. This CLR function reads data from a spreadsheet, performs some calculations, and returns the data to a SQL Server 2005 computer. You need to register the assembly with SQL Server 2005 by using the CREATE ASSEMBLY statement and the least privileged security permission set.
Which permission set should you use?

Your company uses SQL Server 2005. A user reports that an order processing application stopped responding during a transaction. You investigate and discover that the user’s session, which is session ID 85, is being blocked by an errant process. The session ID of the errant process is 101. You need to solve this blocking situation quickly so that the user can continue using the application without losing any work. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

Your department is responsible for creating a list of customer e-mail addresses from data in a SQL Server 2005 database. The list must include the date on which each customer was last contacted. The data in the result set must have column names. The list must be ordered by date of last contact, with the most recent contact first. The LastContact column is stored as a datetime. The date should be displayed as MM/DD/YYYY. A coworker develops the following query.

SELECT email_address AS EmailAddress, CONVERT(nvarchar, lastcontact, 101) AS LastContact FROM Contact ORDER BY LastContact DESC You test this query and find that the data is displayed
in the following order. EmailAddress LastContact andrew@contoso.com 01/24/2003
marc@contoso.com 06/12/2005 stefan@contoso.com 07/13/2004 You need to modify the query so that
the data is listed in the proper order. You want to achieve this goal without negatively affecting performance. What should you do?

You work with a database named DB1, which is located on a SQL Server 2005 computer. You discover that a table scan on Table1 in DB1 causes a slow query. Table1 is a very large table that is used frequently. You ascertain that the query contains the following statement.

SELECT col1, col2 FROM table1 WHERE col3 = <value> You need to provide maximum query performance. Table1 must remain available to users. What should you do?

Your company uses SQL Server 2005. Users report with increasing frequency that they receive deadlock error messages in an order processing application. You need to monitor which objects and SQL Server session IDs are involved when deadlock conditions occur. You want information about each participant in the deadlock. What should you do?