Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 91:

  Your database contains two tables named Order and OrderDetails that store order information. They relate to each other using the OrderID column in each table. Your business requires that the LastModifiedDate column in the Order table must reflect the date and time when a change is made in the OrderDetails table for the related order. You need to create a trigger to implement this business requirement.

  Which Transact-SQL statement should you use?

  A. CREATE TRIGGER [uModDate] ON [OrderDetails] INSTEAD OF UPDATE FOR REPLICATION AS UPDATE [Order] SET [LastModifiedDate] = GETDATE() FROM inserted WHERE inserted.[OrderID] = [Order].[OrderID];
  B. CREATE TRIGGER [uModDate] ON [Order] INSTEAD OF UPDATE NOT FOR REPLICATION AS UPDATE [Order] SET [LastModifiedDate] = GETDATE() FROM inserted WHERE inserted.[OrderID] = [Order].[OrderID];
  C. CREATE TRIGGER [uModDate] ON [Order] AFTER UPDATE FOR REPLICATION AS UPDATE [Order] SET [LastModifiedDate] = GETDATE() FROM inserted WHERE inserted.[OrderID] = [Order].[OrderID];
  D. CREATE TRIGGER [uModDate] ON [OrderDetails] AFTER UPDATE NOT FOR REPLICATION AS UPDATE [Order] SET [LastModifiedDate] = GETDATE() FROM inserted WHERE inserted.[OrderID] = [Order].[OrderID];
  D. CREATE TRIGGER [uModDate] ON [OrderDetails] AFTER UPDATE NOT FOR REPLICATION AS UPDATE [Order] SET [LastModifiedDate] = GETDATE() FROM inserted WHERE inserted.[OrderID] = [Order].[OrderID];

• Question 92:

  You have two tables named dbo.Products and dbo.PriceChange. Table dbo.Products contains ten products. Five products are priced at $20 per unit and have PriceIncrease set to 1. The other five products are priced at $10 per unit and have

  PriceIncrease set to 0.

  You have the following query:

  INSERT dbo.PriceChange (ProductID, Change, ChangeDate) SELECT ProductID, inPrice - delPrice, SYSDATETIME() FROM (

  UPDATE dbo.Products

  SET Price *= 1.1

  OUTPUT inserted.ProductID, inserted.Price, deleted.Price WHERE PriceIncrease = 1

  ) p (ProductID, inPrice, delPrice);

  You need to predict the results of the query.

  Which results should the query produce?

  A. Five rows are updated in dbo.Products. Five rows are inserted into dbo.PriceChange.
  B. Five rows are updated in dbo.Products. No rows are inserted into dbo.PriceChange.
  C. No rows are updated in dbo.Products. Five rows are inserted into dbo.PriceChange.
  D. No rows are updated in dbo.Products. No rows are inserted into dbo.PriceChange.
  A. Five rows are updated in dbo.Products. Five rows are inserted into dbo.PriceChange.

• Question 93:

  You administer a Microsoft SQL Server 2008 database.

  The database contains tables named Customer, Subscriptions, and Orders that have the following definitions:

  

  Customers are considered active if they meet the following requirements:

  Placed an order for a subscription that is recorded in the Subscriptions table. Placed an order for an individual product that is recorded in the Orders table.

  You need to create a view that shows unique rows where the customer either has made an Order or has a Subscription.

  Which Transact-SQL statement should you use?

  

  A. B.
  C. D.
  B

• Question 94:

  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?

  A. SELECT c.CustomerName, SUM(o.OrderID) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName
  B. SELECT COUNT(o.OrderId) AS [OrderCount] FROM CUSTOMERS c JOIN ORDERS o ON c.CUSTOMERID = o.CUSTOMERID
  C. SELECT c.CustomerName, COUNT(o.OrderID) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 1
  D. SELECT c.CustomerName, COUNT(o.OrderId) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId GROUP BY c.CustomerName
  D. SELECT c.CustomerName, COUNT(o.OrderId) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId GROUP BY c.CustomerName

• Question 95:

  You are creating a new table in a database. Your business requires you to store data in the table for only seven days. You need to implement a partitioned table to meet this business requirement.

  Which tasks should you complete?

  A. Create the partition function Create the partition scheme Create the table
  B. Create the partition function Create the table Create a filtered index
  C. Add a secondary file to the primary filegroups Create the table Create the distributed partitioned view
  D. Create the partition function Create the partition scheme Create the distributed partitioned view
  A. Create the partition function Create the partition scheme Create the table

• Question 96:

  You have a table named Sales.SalesOrderHeader and a table named Person.Person.

  You are tasked to write a query that returns SalesOrderID and SalesPersonName that have an OrderDate greater than 20040101. SalesPersonName should be made up by concatenating the columns named FirstName and LastName from the table named Person.Person. You need to write a query to return data, sorted in alphabetical order, by the concatenation of FirstName and LastName.

  Which Transact-SQL statement should you use?

  A. SELECT SalesOrderID, FirstName + ' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H JOIN Person.Person P on
  B. BusinessEntityID = H.SalesPersonID WHERE OrderDate > '20040101' ORDER BY FirstName ASC, LastName ASC
  C. SELECT SalesOrderID, FirstName + ' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H JOIN Person.Person P on
  D. BusinessEntityID = H.SalesPersonID WHERE OrderDate > '20040101' ORDER BY FirstName DESC, LastName DESC
  E. SELECT SalesOrderID, FirstName +' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H JOIN Person.Person P on
  F. BusinessEntityID = H.SalesPersonID WHERE OrderDate > '20040101' ORDER BY SalesPersonName ASC
  G. SELECT SalesOrderID, FirstName + ' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H JOIN Person.Person P on
  H. BusinessEntityID = H.SalesPersonID WHERE OrderDate > '20040101' ORDER BY SalesPersonName DESC
  C. SELECT SalesOrderID, FirstName + ' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H JOIN Person.Person P on

• Question 97:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance that hosts an application.

  You are designing some new tables.

  You need to choose the most appropriate data types.

  Which data types should you use? (To answer, drag the appropriate data type to the correct data in the answer area. Each data type may be used once, more than once, or not at all. Each data may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

• Question 98:

  You have a table named Subcategories that contains subcategories for socks, vests and helmets. You have another table named Products that contains products only from the subcategories socks and vests. You have the following query:

  SELECT s.Name, p.Name AS ProductName FROM Subcategories s OUTER APPLY (SELECT * FROM Products pr WHERE pr.SubcategoryID = s.SubcategoryID) p WHERE s.Name IS NOT NULL;

  You need to predict the results of the query.

  What results should the query produce?

  A. Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L
  B. Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L Helmets NULL
  C. Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L Helmets NULL NULL NULL
  D. Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L NULL Mountain Bike Socks, NULL Mountain Bike Socks, NULL Racing Socks, M NULL Racing Socks, L NULL Classic Vest, S NULL Classic Vest, M NULL Classic Vest, L Helmets NULL NULL NULL
  B. Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L Helmets NULL

• Question 99:

  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 identify all customers who have ordered for an average amount of more than 500 or more from September 01, 2011. Which SQL query should you use?

  

  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
  E. Option E

• Question 100:

  You have the following two tables.

  

  You execute the following statement. MERGE Products USING ProductChanges ON (Products.ProductID = ProductChanges.ProductID) WHEN MATCHED AND Products.VendorID = 0 THEN DELETE WHEN MATCHED THEN UPDATE SET Products.ProductName = ProductChanges.ProductName Products.VendorID = ProductChanges.VendorID;

  You need to identify the rows that will be displayed in the Products table. Which rows will be displayed?

  

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