Your database contains sales information formillions of orders.
You need to identify the orders with the highest average unit price and an order total greater than 10,000. The list should contain no more than 20 orders.
Which query should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
The database contains a table named Products.
The database has two stored procedures named ModifyProduct and RetrieveProducts. ModifyProduct updates a single row in the Products table. RetrieveProducts returns all rows from the Products table.
RetrieveProducts is used by a report.Users who run the report experience contention problems. You discover that RetrieveProducts is being blocked by ModifyProduct.
The report must not include rows that are currently being modified.
You need to ensure that the report is executed as quickly as possible.
Which locking hint should you use in RetrieveProducts?

You are a database developer. You provide solutions by using SQL Server 2008 in an enterprise environment.
Your online transaction processing (OLTP) database contains a tabled named SalesOrders. Your data warehouse contains a table named factBuyingHabits. The factBuyingHabits table has no indexes.
You need to synchronize data between the two tables on a weekly basis. The synchronization process has the following requirements:
New records in the SalesOrders table are inserted in the factBuyingHabits table.
When a record is modified in the SalesOrders table,the modificationis updated in the factBuyingHabits table.
Records that are deleted from the SalesOrders table are also deleted from the factBuyingHabits table.
You need to design an appropriate synchronization solution.You want to achieve this goal by using minimum amount of coding and administrative efforts.
What should you do?

You have two tables named SalesPerson and SalesTerritory.
You need to create sample data by using a Cartesian product that contains the data from the SalesPerson and SalesTerritory tables.
Which code segment should you use?

You have two tables named Customers and Orders.
For customers that have placed at least one order, you need to produce a list of customer names and the number of orders for each customer.
Which query should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
You have a database that contains a table and a table-valued function. The table valued function accepts the primary key from the table as a parameter.
You plan to write a query that joins the table to the results of the table-valued function.
You need to ensure that only rows from the table that produce a result set from the table-valued function are returned.
Which join predicate should you use?

You have the two tables as shown in the following exhibit (Click the Exhibit button.).

The foreign key relationship between these tables has CASCADE DELETE enabled.
You need to remove all records from the Orders table.
Which Transact-SQL statement should you use?

You have a table named Sales.
You need to return sales data ordered by customer name. For each customer, the most recent sale must be listed first.
Which query should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
There are two schemas named Sales and Marketing. You are the owner of the Sales schema and the Marketing schema is owned by a user named MarketingUser.
Users of the Marketing schema do not have permissions to access the Sales schema. You have permissions to create objects in all schemas in the database.
The Sales schema has a table named Customers.
You plan to create a storedprocedure in the Marketing schema for the marketing team. The stored procedure will select data from the Customers table and will be owned by MarketingUser.
You need to ensure that the marketing team is able to execute the stored procedure.
What should you do?

You have a transaction that uses the repeatable read isolation level.
This transaction causes frequent blocking problems. You need to reduce blocking. You also need to avoid dirty reads and non-repeatable reads.
Which transaction isolation level should you use?