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?

You are configuring Service Broker to process messages within a single database. You have performed the following steps.
1. CREATE MESSAGE TYPE
2. CREATE CONTRACT
3. CREATE QUEUE
You need to complete the Service Broker configuration.
What should be the next step ?

You have a table named Inventory.
You open a Microsoft Windows PowerShell session at the following location by using the SQL Server Windows PowerShell provider.
PS
SQLSERVER:\SQL\CONTOSO\DEFAULT \Databases\ReportServer\Tables\dbo.Inventory\Columns>
Using the SQL Server Windows PowerShell provider, you need to query all the columns in the table.
Which cmdlet 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 will store customer data as XML data.The data supports an application that cannot be altered.
You plan to prevent the following types of errors in the XML data.
NULL values in the Customer Name field
Non-numeric values in the Customer Telephone field.
Invalid values in the Gender field
You need to implement the plan without modifying the application.
What should you do?

You use SQL Server 2008 to design a database that will hold incoming XML responses for an EDI system.
You have the following requirements:
The data is accessible to heterogeneous platforms.
The database stores various types of reports from multiple sources.
The solution allows search by keywords.
The database stores large amounts of data.
The database is scalable.
You need to design the database to meet the given requirements.
What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
A stored procedurein a database uses a transaction to retrieve data from a table and produces aggregations.
You must design a solution that meets the following requirements:
Update operations cannot be performed on the retrieved data while the stored procedure is being executed.
Insert operations in the table can be performed while the stored procedure is being executed.
You need to ensure that the solution meets the requirements.
What isolation level should you use?

You need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Which TransactSQL statement should you use?

You are a database developer. You plan todesign a database solution by using SQL Server 2008.
A f requently used query takes very long to execute.
You discover that the query frequently uses full-table scans instead of indexes. This causes other queries that modify the table to be blocked.
The indexing strategy on the underlying tables that the query uses can change.
You need to design a solution that performs the following tasks:
Removes fulltable scans
Allows the query optimizer to select the appropriate index.
What should you do?

You have a table named Employee. You document your company’s organizational hierarchy by inserting the EmployeeID of each employee’s manager in the ReportsTo column.
You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy.
You write the following code segment. (Line numbers are included for reference only.)
01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
02 AS (
03
04 )
05 SELECT EmployeeID, FullName, ManagerName, Level
06 FROM EmployeeList
Which code segment should you insert a t line 3?