Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 81:

  You have the following two tables.

  

  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?

  A. DROP TABLE Orders
  B. DELETE FROM Orders
  C. TRUNCATE TABLE Orders
  D. DELETE FROM OrderDetails
  B. DELETE FROM Orders

• Question 82:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance.

  You want to add functionality to an existing application that uses the Microsoft SQL Server Profiler tool to capture trace information.

  You need to ensure that the following requirements are met:

  Users are able to use the SQL Server Profiler tool. Users are able to capture trace information and read saved trace files. Users are granted the minimum permissions to achieve these goals.

  Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 83:

  You are a developer of a Microsoft SQL Server 2008 R2 database instance that supports a web- based order-entry application.

  You need to create a server-side trace that meets the following requirements:

  Captures performance information from 05:00 hours to 05:30 hours daily. Stores trace information in a format that can be used even while users are disconnected from the network.

  Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 84:

  You use a table that stores movie information in your database.

  The table has the following schema:

  

  You plan to insert a new record for a movie that will be released by using the following information:

  

  You need to ensure that the following requirements are met:

  Only this movie record gets a Rating of 0.

  Other movie records that will be inserted in the future must get a Rating of 1 through 7.

  Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange

  them in the correct order.)

  Select and Place:

  

  

• Question 85:

  You have a table named ProductCounts that contains 1000 products as well as the number of units that have been sold for each product. You need to write a query that displays the top 5% of products that have been sold most frequently.

  Which Transact-SQL code segments should you use?

  A. WITH Percentages AS ( SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM percentages WHERE groupingColumn =1;
  B. WITH Percentages AS ( SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 5;
  C. WITH Percentages AS ( SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 1;
  D. WITH Percentages AS ( SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 20;
  D. WITH Percentages AS ( SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 20;

• Question 86:

  You have the following XML document that contains Product information.

  DECLARE @prodList xml ='

  Price="5.1" /> ...

  ';

  You need to return a list of products that contains the Product Name, Category, and Price of each product.

  Which query should you use?

  A. SELECT prod.value('.[1]/@Name','varchar(100)'), prod.value('.[1]/@Category','varchar(20)'), prod.value('.[1]/@Price','money') FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
  B. SELECT prod.value('@Name','varchar(100)'), prod.value('@Category','varchar(20)'), prod.value('@Price','money') FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
  C. WITH XMLNAMESPACES(DEFAULT 'urn;Wide_World_Importers/schemas/Products' as o) SELECT prod.value('Name[1]','varchar(100)'), prod.value('Category[1]','varchar(20)'), prod.value('Price[1]','money') FROM @prodList.nodes('/o:ProductList/o:Product') ProdList(prod);
  D. WITH XMLNAMESPACES(DEFAULT 'urn:Wide_World_Importers/schemas/Products') SELECT prod.value('./@Name','varchar(100)'), prod.value('./@Category','varchar(20)'), prod.value('./@Price','money') FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
  D. WITH XMLNAMESPACES(DEFAULT 'urn:Wide_World_Importers/schemas/Products') SELECT prod.value('./@Name','varchar(100)'), prod.value('./@Category','varchar(20)'), prod.value('./@Price','money') FROM @prodList.nodes('/ProductList/Product') ProdList(prod);

• Question 87:

  You create and populate a table named SiteNavigation by using the following statements:

  CREATE TABLE SiteNavigation

  (

  SiteNavigationId INT PRIMARY KEY,

  Linktext VARCHAR(10),

  LinkUrl VARCHAR(40),

  ParentSiteNavigationId INT NULL REFERENCES SiteNavigation(SiteNavigationId) )

  INSERT INTO SiteNavigation

  VALUES (1,'First','http://first',NULL)

  ,(2,'Second','http://second',1)

  ,(3,'Third','http://third',1)

  ,(4,'Fourth','http://fourth',2)

  ,(5,'Fifth','http://fifth',2)

  ,(6,'Sixth','http://sixth',2)

  ,(7,'Seventh','http://seventh',6)

  ,(8,'Eighth','http://eighth',7)

  You are tasked to write a query to list all site references that are more than two levels from the root node. The query should produce the following results:

  

  You have written the following query:

  WITH DisplayHierarchy AS (SELECT LinkText, LinkUrl, SiteNavigationId, ParentSiteNavigationId, 0 AS DistanceFromRoot FROM SiteNavigation WHERE ParentSiteNavigationId IS NULL UNION ALL SELECT SiteNavigation.LinkText, SiteNavigation.LinkUrl, SiteNavigation.SiteNavigationId, SiteNavigation.ParentSiteNavigationId, dh.DistanceFromRoot + 1 AS DistanceFromRoot FROM SiteNavigation INNER JOIN DisplayHierarchy dh ON SiteNavigation.ParentSiteNavigationId = dh.SiteNavigationId) SELECT LinkText, LinkUrl, DistanceFromRoot FROM DisplayHierarchy You need to append a WHERE clause to the query.

  Which clause should you use?

  A. WHERE DistanceFromRoot =2
  B. WHERE DistanceFromRoot < 2
  C. WHERE DistanceFromRoot >= 2
  D. WHERE DistanceFromRoot IN (2,3)
  C. WHERE DistanceFromRoot >= 2

• Question 88:

  You have a table named Books that has columns named BookTitle and Description. There is a full-text index on these columns.

  You need to return rows from the table in which the word 'computer' exists in either column.

  Which code segment should you use?

  A. SELECT * FROM Books WHERE FREETEXT(*,'computer')
  B. SELECT * FROM Books WHERE BookTitle LIKE '%computer%'
  C. SELECT * FROM Books WHERE BookTitle = '%computer%' OR Description = '%computer%'
  D. SELECT * FROM Books WHERE FREETEXT(BookTitle,'computer')
  A. SELECT * FROM Books WHERE FREETEXT(*,'computer')

• Question 89:

  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?

  A. SNAPSHOT
  B. SERIALIZABLE
  C. READ COMMITTED
  D. READ UNCOMMITTED
  A. SNAPSHOT

• Question 90:

  You use the same Service Broker configuration to support a Web site and an internal application. The Web site generates a greater workload than the internal application. You need to configure Service Broker to ensure that messages sent by the internal application are processed before those sent by the Web site.

  Which Transact-SQL statement should you use?

  A. ALTER SERVICE
  B. CREATE CONTRACT
  C. CREATE BROKER PRIORITY
  D. ALTER QUEUE WITH ACTIVATION
  C. CREATE BROKER PRIORITY