You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database will contain information on retail sales transactions of more than 500 stores.
The marketing department uses the solution to analyze daily sales patterns for each store.Users report that the solution takes a long time to retrieve the required data.
You need to ensure that the solution provides results in the minimum possible time.
What should you do?

You need to capture the execution plan for a query.
Which statement should you use?

You have the following XML document that contains Product information.
DECLARE @prodList xml =’

…
‘;
You need to return a list of products that contains the Product Name, Category, and Price of each product.
Which query should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
A database contains a table named Person. The structure of the table is as shown in the following exhibit. ( Click the Exhibit button.)

The table has the following indexes:
A unique clustered index on the PersonID column named IX_Perso n_PersonID
A nonclustered index on the FirstName and LastName columns named IX_Person_FirstName_LastName
A nonclustered index on the PersonType column named IX_Person_PersonType that has FirstNameand LastName as included columns
The table contains approximately 700,000 records. The approximate number of records for each PersonType is 3,000.
You execute the following query.
SELECT P.FirstName, P.LastName
FROM Person P
WHERE P.PersonType = ‘DR’
You plan to analyze the performance of the query by using an execution plan.
You need to ascertain that the indexes are used optimally.
What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database application has a table named Transactions that contains millions of rows. The table has multiple columns that include transaction_id and transaction_date. There is a clustered index on the transaction_id column. There is a nonclustered index on the transaction_date column.
You discover that the following query takes a long time to execute.
SELECT transaction_id, transaction_date, transaction_notes
FROM transactions WHERE transaction_type_id =’FXO’
AND transaction_date between @start_date and @end_date
The summary of the execution plan is as shown in the following code segment.
|–Filter(WHERE:([transactio n_type_id]=’FXO’)
|–Nested Loops(Inner Join)
|–Index Seek(OBJECT:([transactions]. [nc_transactions_transaction_date])
|–Clustered Index Seek(OBJECT:([transactions]. [PK_transactions_transaction_id])
You need to ensure that the query retrieves data in minimum possible time.
What should you do?

You have a table named Customer that has anXML column named Locations. This column stores anX ML fragment that contains details of one or more locations, as show in the following examples.

You need to write a query that returns a row for each of the customer’s locations. Each resulting row must include the customer name, city, and an XML fragment that contains the location details.
Which query should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database will contain a table that has a parent child relation ship to it self. Each child might also be aparent. This might exist up to 10 levels deep.
You need to retrieve all levels by using a single Transact-SQL query.
What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database includes a table that contains the following product inventory information:
Department
Class
Item
Quantity
You plan to write a query that produces the sum of quantity data broken into the following groups.
Department
Department and Class
Department and Item
Department, Class, and Item
You need to write the query byusing the minimum possible number of TransactSQL statements.
What should you recommend?

You have two tables namedCustomers and Orders. They are related by a foreign key constraint on the CustomerIDs on each table.
You need to generate the following XML structure that includes all customers and their related orders.

Customer1

1/1/2008422
4/8/ 2008300
…

…

Which query should you use?