Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 4

You are a database developer for your company. The company is upgrading from SQL Server 2000 to SQL Server 2005. The companys new coding standards require conformance with ANSI standards. Your manager asks you to convert the following query to use ANSI standard wording. SELECT E.EmployeeID, E.Hiredate, J.Resume FROM HumanResources.Employee AS E, HumanResources.JobCandidate AS J WHERE E.EmployeeID *= J.EmployeeID The new query must always produce the same result set as the existing query. You need to write the query to fulfill these requirements. Which Transact-SQL query should you use?

You are a database developer for your company. A table named Articles contains newspaper articles and is designed as shown in the following display. The XML document in the ArticleXml column appears as shown in the following display.

... ... ... ... ...

You need to design a query that will display the total number of images per CategoryID value. Which query should you use?

You are a database developer for your company. Information for the Parts database is sent daily as an XML document and is placed in a single row in the ProductInfo column of the ProductXML table. A sample of the information in the XML document is shown in the following display. ... At the end of each day, a batch job is run. This batch imports the XML document into the Product table from the ProductXML table. The products ID, name, and number will be extracted from the XML document for each Product element and will be inserted into the Product tables ProductID, Name, and Number columns. The ProductXML table is truncated at the end of the batch. You need to design the query that retrieves the rows from the ProductXML table for insertion into the Product table. Which Transact-SQL query should you use to query the data from the ProductXML table?

You are a database developer for your company. You are writing a query that will search a resume text column for specified skills. The results must include both synonyms and exact matches. You need to write the query to fulfill the requirements. Which Transact-SQL function should you use?

You are a database developer for your company. A procedure exists that saves an order item to the OrderItems table. If the item does not exist, an insert should be performed. If the item exists, an update should be performed. The OrderItems table is designed as shown in the following display. You need to develop a routine that uses a minimum amount of resources. Which routine should you use?

You are a database developer for your company. You are developing a report that will return the customer name and phone number. Customers might have a business number, a mobile number, and a fax number. The report must return the first nonnull phone number for each customer. You need to write the query that returns the required customer information for the report. What should you use?

You are a database developer for your company. You are creating a stored procedure that will use a Transact- SQL cursor to share the result set with other statements in the stored procedure. This cursor must have a fixed membership where the order of the rows does not change. The data in the rows that are retrieved through the cursor should be updatable. These updates should be viewable after they are made. While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor. You need to create the stored procedure to fulfill these requirements. Which option should you use in the DECLARE CURSOR statement?

You are a database developer for your company. You are creating a report that displays the companys organizational structure. Several columns from the Employee table are shown in the following display. An example of the required output is shown in the following display. You need to create the query that can return the organizational chart for the requested report. Which code segment should you use?

You are a database developer for a local school district. You maintain information about teachers and staff in a column of the xml data type. You need to produce a tabular report that contains the values of the EmployeeID, Title, and YearsOfEmployment columns for each employee. This report should be ordered by Title in ascending order and YearsOfEmployment in descending order. What should you use to retrieve this data?

You are a database developer for your company. You are developing a sales report for a given time period. The report must list all sales representatives and their position based on their total sales for the period. If two sales representatives had the same total sales, they should be assigned the same position. A sample output is shown in the following display. You need to create the report to fulfill the requirements. Which operator should you use?