Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 31:

  You have two tables named dbo.CurrentProducts and dbo.ArchiveProducts.

  You have the following query: SELECT ProductID, Name FROM dbo.CurrentProducts UNION ALL SELECT ProductID, Name FROM dbo.ArchiveProducts;

  You need to predict the list of products that the query will produce.

  Which list of products should the query return?

  A. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts but not in both.
  B. Products that have a matching ProductID and Name in dbo.CurrentProducts or dbo.ArchiveProducts.
  C. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed only once.
  D. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed multiple times.
  D. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed multiple times.

• Question 32:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance. You create tables named order, customer, and product as follows:

  

  You need to write a query to return all customer names and total number of orders for customers who have placed more than 10 orders. Which SQL query should you use?

  

  A. B. C. D. E. F. G. H.
  I. J.
  D

• Question 33:

  You are the database administrator for an order management system.

  The database has the following two schemas:

  The dbo schema that is used by the main data processing group A Reporting schema that is used by the reporting group

  The application contains a product table that has the following definition:

  

  You want to load a subset of data from the product and order tables in the dbo schema to a new ProductOrderHistory table in the Reporting schema. You need to ensure that the table meets the following requirements: Includes rows for every product that has been manufactured by "Contoso". Includes rows for every product that was ordered from March 1, 2011 to May 31, 2011.

  The Reporting.ProductOrderHistory columns have the following types:

  

  Which Transact-SQL query should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

• Question 34:

  You have a table named Product.

  You need to increase product prices for only the vendor named Coho Winery by 10 percent and then return a list of the products and updated prices.

  Which code segment should you use?

  A. UPDATE Product SET Price = Price * 1.10, ProductName = ProductName WHERE Product.VendorName = 'Coho Winery'
  B. UPDATE Product SET Price = Price * 1.10 OUTPUT inserted.ProductName, deleted.Price WHERE Product.VendorName = 'Coho Winery'
  C. UPDATE Product SET Price = Price * 1.10 OUTPUT inserted.ProductName, inserted.Price WHERE Product.VendorName = 'Coho Winery'
  D. UPDATE Product SET Price = Price * 1.10, VendorName = 'Coho Winery' OUTPUT inserted.ProductName, inserted.Price
  C. UPDATE Product SET Price = Price * 1.10 OUTPUT inserted.ProductName, inserted.Price WHERE Product.VendorName = 'Coho Winery'

• Question 35:

  You have an XML schema that you must use to validate XML data in your database. You need to store this XML schema.

  Which code segment should you use?

  A. CREATE SCHEMA CustomerSchema
  B. CREATE DEFAULT CustomerSchema AS 'XML'
  C. CREATE PRIMARY XML INDEX CustomerSchema
  D. CREATE XML SCHEMA COLLECTION CustomerSchema
  D. CREATE XML SCHEMA COLLECTION CustomerSchema

• Question 36:

  Click the exhibit button.

  

  You have the following query:

  SELECT EmployeeID, ManagerID, LoginID FROM dbo.Employees WHERE ManagerID = 1500 ORDER BY ManagerID;

  You have been tasked to force the query to use the execution plan in the exhibit. You need to use an appropriate hint to perform the task.

  Which hint should you use?

  A. INDEX(0)
  B. INDEX(1)
  C. INDEX(PK_Employees)
  D. INDEX(IX_Employees)
  D. INDEX(IX_Employees)

• Question 37:

  You have a database that uses stored procedures to perform INSERT, UPDATE, DELETE, and SELECT statements. You are tasked with providing a recommendation of indexes to be created and dropped from the database. You need to select the appropriate method to accomplish the task.

  Which method should you use?

  A. Index Usage DMVs
  B. Missing Index DMVs
  C. SQL Server Profiler
  D. Database Engine Tuning Advisor
  D. Database Engine Tuning Advisor

• Question 38:

  You are troubleshooting query performance on SQL Server 2008. You have profiler trace data in a table named PerfData. You need to determine which events are taking longer than one second of CPU time or run for more than two seconds.

  Which Transact-SQL statement should you use?

  A. SELECT TextData, Duration, CPU FROM PerfData WHERE EventClass = 12 AND ( CPU > 1000 OR Duration > 2000 )
  B. SELECT TextData, Duration, CPU FROM PerfData WHERE EventClass = 12 AND ( CPU > 1000 OR Duration > 2000000 )
  C. SELECT TextData, Duration, CPU FROM PerfData WHERE EventClass = 12 AND ( CPU > 1000000 OR Duration > 2000 )
  D. SELECT TextData, Duration, CPU FROM PerfData WHERE EventClass = 12 AND ( CPU > 1000000 OR Duration > 2000000 )
  B. SELECT TextData, Duration, CPU FROM PerfData WHERE EventClass = 12 AND ( CPU > 1000 OR Duration > 2000000 )

  ---

  Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

• Question 39:

  You administer a Microsoft SQL Server 2008 database named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition.

  The table has the following definition:

  

  The index has the following definition:

  

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  G. Option G
  H. Option H
  I. Option I
  J. Option J
  O

• Question 40:

  You are given a database design to evaluate. All of the tables in this database should have a clustered index. You need to determine the tables that are missing a clustered index by using the system catalog views.

  Which Transact-SQL statement should you use?

  A. SELECT name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0 ORDER BY name;
  B. SELECT name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 0 ORDER BY name;
  C. SELECT name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0 AND OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 1 ORDER BY name;
  D. SELECT name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 1 AND OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 1 ORDER BY name;
  A. SELECT name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0 ORDER BY name;