Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 151:

  You need to implement a common table expression (CTE).

  Which code segment should you use?

  A. CREATE VIEW SalesByYear AS SELECT Year, Region, SUM(OrderTotal) FROM Orders GROUP BY Year, Region; GO SELECT Year, Region, Total FROM SalesByYear;
  B. WITH SalesByYear(Year,Region,Total) AS (SELECT Year, Region, SUM(OrderTotal) FROM Orders GROUP BY Year,Region) SELECT Year, Region, Total FROM SalesByYear;
  C. SELECT Year, Region, Total FROM (SELECT Year, Region, SUM(OrderTotal) AS Total FROM Orders GROUP BY Year, Region) AS [SalesByYear];
  D. SELECT DISTINCT Year, Region, (SELECT SUM(OrderTotal) FROM Orders SalesByYear WHERE Orders.Year = SalesByYear.YEAR AND Orders.Region = SalesByYear.Region) AS [Total] FROM Orders;
  B. WITH SalesByYear(Year,Region,Total) AS (SELECT Year, Region, SUM(OrderTotal) FROM Orders GROUP BY Year,Region) SELECT Year, Region, Total FROM SalesByYear;

• Question 152:

  You have two tables named MainTable and ArchiveTable.

  You need to move data older than 30 days from MainTable into ArchiveTable.

  Which code segment should you use?

  A. DELETE FROM MainTable OUTPUT deleted.* WHERE RecordDate < DATEADD(D,-30,GETDATE())
  B. DELETE FROM MainTable OUTPUT DELETED.* INTO ArchiveTable WHERE RecordDate < DATEADD(D,-30,GETDATE())
  C. INSERT INTO ArchiveTable SELECT * FROM MainTable WHERE RecordDate < DATEADD(D,-30,GETDATE())
  D. INSERT INTO ArchiveTable SELECT * FROM MainTable WHERE RecordDate < DATEADD(D,-30,GETDATE()) DELETE FROM MainTable
  B. DELETE FROM MainTable OUTPUT DELETED.* INTO ArchiveTable WHERE RecordDate < DATEADD(D,-30,GETDATE())

• Question 153:

  You have tables named Sales.SalesOrderDetails and Sales.SalesOrderHeader.

  You have been tasked to update the discount amounts for the sales of a particular salesperson.

  You need to set UnitPriceDiscount to .1 for all entries in Sales.SalesOrderDetail that only correspond to SalesPersonID 290.

  Which Transact-SQL statement should you use?

  A. UPDATE d SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d INNER JOIN Sales.SalesOrderHeader h ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesPersonID = 290;
  B. UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 FROM Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290;
  C. UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 WHERE EXISTS ( SELECT * FROM Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290);
  D. UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d WHERE EXISTS ( SELECT * FROM Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290);
  A. UPDATE d SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d INNER JOIN Sales.SalesOrderHeader h ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesPersonID = 290;

• Question 154:

  A table named Shirts includes an XML column named SizeAndColors that contains the sizes and colors of each shirt, as shown in the following code segment.

  

  You need to return a list of shirt colors available in a specific size. Which code segment should you use?

  

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

• Question 155:

  You are using SQL Server Profiler to gather deadlock information.

  You need to capture an XML description of a deadlock.

  Which event should you use?

  A. Lock:Deadlock
  B. Showplan XML
  C. Deadlock Graph
  D. Lock:Deadlock Chain
  C. Deadlock Graph

  ---

  The Lock:Deadlock event class is produced when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim. Use the Lock:Deadlock event class to monitor when deadlocks

  occur and which objects are involved. You can use this information to determine if deadlocks are significantly affecting the performance of your application.

  You can then examine the application code to determine if you can make changes to minimize deadlocks. The Deadlock Graph event class provides an XML description of a deadlock. This class occurs simultaneously with the Lock:Deadlock

  event class. The Lock:Deadlock Chain event class is produced for each participant in a deadlock.

• Question 156:

  You administer a Microsoft SQL Server 2008 database for an inventory management system.

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

  

  You want to add a new field to the Product table to meet the following requirements:

  Allows user-specified information that will be added to records in the Product table. Supports the largest storage size needed for the field. Uses the smallest data type necessary to support the domain of values that will be entered by users.

  You need to add a field named User_Data_1 to support only values that are 1 or 0.

  A. ALTER TABLE [Production].[Product] ADD [User_Data_1] TINYINT
  B. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLINT
  C. ALTER TABLE [Production].[Product] ADD [User_Data_1] INT
  D. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIGINT
  E. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIT
  F. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(11, 6)
  G. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(6, 11)
  H. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(5,6)
  I. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLMCNEY
  J. ALTER TABLE [Production].[Product] ADD [User_Data_1] MONEY
  E. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIT

• Question 157:

  Your company manufactures and distributes bowling balls. You have a full-text catalog named ftCatalog which contains the ftInventory index on the Products table. Your marketing department has just inserted a new bowling ball into the Inventory table. You notice only the new bowling ball is not being included in the results of the full-text searches. You have confirmed that the row exists in the Products table. You need to update the full-text catalog in the least amount of time.

  Which Transact-SQL statement should you use?

  A. ALTER FULLTEXT INDEX ON ftInventory START FULL POPULATION
  B. ALTER FULLTEXT INDEX ON ftInventory RESUME POPULATION
  C. ALTER FULLTEXT INDEX ON ftInventory START UPDATE POPULATION
  D. ALTER FULLTEXT CATALOG ftCatalog REBUILD
  C. ALTER FULLTEXT INDEX ON ftInventory START UPDATE POPULATION

• Question 158:

  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 Transact-SQL statement should you use?

  A. RANK
  B. NTILE(10)
  C. DENSE_RANK
  D. ROW_NUMBER
  D. ROW_NUMBER

  ---

  ROW_NUMBER returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

• Question 159:

  You are developing a new database. The database contains two tables named SalesOrderDetail and Product. You need to ensure that all products referenced in the SalesOrderDetail table have a corresponding record in the Product table.

  Which method should you use?

  A. JOIN
  B. DDL trigger
  C. Foreign key constraint
  D. Primary key constraint
  C. Foreign key constraint

• Question 160:

  You have two partitioned tables named Transaction and TransactionHistory. You need to archive one of the partitions of the Transaction table to the TransactionHistory table.

  Which method should you use?

  A. ALTER TABLE ... SWITCH ...
  B. INSERT ... SELECT ...; TRUNCATE TABLE
  C. ALTER PARTITION FUNCTION ... MERGE ...
  D. ALTER PARTITION FUNCTION ... SPLIT ...
  B. INSERT ... SELECT ...; TRUNCATE TABLE