Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 131:

  You have a table named Inventory. You open a Microsoft Windows PowerShell session at the following location by using the SQL Server Windows PowerShell provider. PS

  SQLSERVER:\SQL\CONTOSO\DEFAULT\Databases\ReportServer\Tables\dbo.Inventory\Col umns> Using the SQL Server Windows PowerShell provider, you need to query all the columns in the table.

  Which cmdlet should you use?

  A. Get-Item
  B. Get-Location
  C. Get-ChildItem
  D. Get-ItemProperty
  C. Get-ChildItem

• Question 132:

  You have a table named Customer.

  You need to ensure that customer data in the table meets the following requirements:

  credit limit must be zero unless customer identification has been verified.

  credit limit must be less than 10,000.

  Which constraint should you use?

  A. CHECK (CreditLimt BETWEEN 1 AND 10000)
  B. CHECK (Verified = 1 AND CreditLimt BETWEEN 1 AND 10000)
  C. CHECK ((CreditLimt = 0 AND Verified = 0) OR (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
  D. CHECK ((CreditLimt = 0 AND Verified = 0) AND (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
  C. CHECK ((CreditLimt = 0 AND Verified = 0) OR (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))

• Question 133:

  You create a database named AdventureWorks. You want to create a new table to store customer reviews for all products within the database.

  The table must meet the following requirements:

  Stores information for the Product ID, Customer ID, Rating, and Reviews columns.

  The Reviews column can contain NULL values.

  The Reviews column is optimized to store NULL values.

  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 134:

  You have two tables named Customers and Orders. They are related by a foreign key constraint on the CustomerID on each table. You need to generate the following XML structure that includes all customers and their related orders.

  Customer1

  1/1/2008422 4/8/2008300 ...

  ...

  Which query should you use?

  A. SELECT CustomerName, OrderDate, OrderValue FROM Customers c JOIN Orders o ON o.CustomerID = c.CustomerID FOR XML AUTO, TYPE
  B. SELECT * FROM (SELECT CustomerName, NULL AS OrderDate, NULL AS OrderValue FROM Customers UNION ALL SELECT NULL, OrderDate, OrderValue FROM Orders) CustomerOrders FOR XML AUTO, ROOT('Root')
  C. SELECT CustomerName, (SELECT OrderDate, OrderValue FROM Orders FOR XML PATH('Order')) FROM Customers FOR XML PATH('Customer'), ROOT('Root'), TYPE
  D. SELECT CustomerName, (SELECT OrderDate, OrderValue FROM Orders WHERE Orders.CustomerId = Customers.CustomerId FOR XML PATH('Order'), TYPE) Orders FROM Customers FOR XML PATH('Customer'), ROOT('Root')
  D. SELECT CustomerName, (SELECT OrderDate, OrderValue FROM Orders WHERE Orders.CustomerId = Customers.CustomerId FOR XML PATH('Order'), TYPE) Orders FROM Customers FOR XML PATH('Customer'), ROOT('Root')

• Question 135:

  You have a table named Employee. You document your company's organizational hierarchy by inserting the EmployeeID of each employee's manager in the ReportsTo column.

  You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee's level in the hierarchy.

  You write the following code segment. (Line numbers are included for reference only.)

  01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level) 02 AS (

  03 .........

  04 )

  05 SELECT EmployeeID, FullName, ManagerName, Level 06 FROM EmployeeList;

  Which code segment should you insert at line 3?

  A. SELECT EmployeeID, FullName, '' AS [ReportsTo], 1 AS [Level] FROM Employee WHERE ReportsTo IS NULL UNION ALL SELECT emp.EmployeeID, emp.FullNName mgr.FullName, 1 + 1 AS [Level] FROM Employee emp JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID
  B. SELECT EmployeeID, FullName, '' AS [ReportsTo], 1 AS [Level] FROM Employee WHERE ReportsTo IS NULL UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1 FROM EmployeeList mgr JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeId
  C. SELECT EmployeeID, FullName, '' AS [Reports To], 1 AS [Level] FROM Employee UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, 1 + 1 AS [Level] FROM Employee emp LEFT JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID
  D. SELECT EmployeeID, FullName, '' AS [ReportsTo], 1 AS [Level] FROM Employee UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1 FROM EmployeeList mgr JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeID
  B. SELECT EmployeeID, FullName, '' AS [ReportsTo], 1 AS [Level] FROM Employee WHERE ReportsTo IS NULL UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1 FROM EmployeeList mgr JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeId

• Question 136:

  You administer a Microsoft SQL Server 2008 database that contains a table named dbo.[order].

  There are no triggers on the table. You plan to create a stored procedure that will have the following parameters:

  @ProdId int

  @Custld int

  You need to ensure that the following requirements are met:

  The OrderlD and ProdID values of each modified row are captured into a local table variable before data is modified.

  The ProdID is modified to @ProdID where CustID is equal to @CustId.

  Which Transact-SQL statement should you use?

  

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

• Question 137:

  You administer a Microsoft SQL Server 2008 database that contains a table named Sales.SalesOrderHeader. The Sales.SalesOrderHeader table has the following definition:

  

  You plan to perform the following tasks:

  Display all Sales.SalesOrderHeader records that contain orders that have not been shipped.

  Generate an execution plan of XML output document for the query.

  You need to ensure that the following requirements are met:

  Data is returned for all fields in the Sales.SalesOrderHeader table where the shipping date is unknown.

  An XML document is provided only for this query.

  An XML document that contains the actual execution plan is returned. 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 138:

  You have two tables. A table named Student.CurrentStudents contains the names of all students enrolled for the current year. Another table named Student.NewYearRoster contains the names of students who have enrolled for the upcoming

  year.

  You have been tasked to write a MERGE statement to:

  Insert into Student.CurrentStudents the names of students who are enrolled for the upcoming year but not for the current year. Update information in Student.CurrentStudents for students who are enrolled both in the current year and in the

  upcoming year.

  Delete from Student.CurrentStudents the names of students who are not enrolled for the upcoming year.

  You need to write the appropriate MERGE statement.

  Which Transact-SQL statement should you use?

  A. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED THEN UPDATE SET Address = S.Address, Age = S.Age WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age) WHEN NOT MATCHED BY SOURCE THEN DELETE;
  B. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET Address = T.Address, Age = T.Age;
  C. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED AND NOT T.Address = S.Address OR NOT T.Age = S.Age THEN UPDATE SET T.Address = S.Address, T.Age = S.Age WHEN NOT MATCHED THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age) WHEN MATCHED THEN DELETE;
  D. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED AND NOT T.Address = S.Address AND NOT T.Age = S.Age THEN UPDATE SET T.Age = S.Age, T.Address = S.Address WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age) WHEN NOT MATCHED BY SOURCE THEN DELETE;
  A. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED THEN UPDATE SET Address = S.Address, Age = S.Age WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age) WHEN NOT MATCHED BY SOURCE THEN DELETE;

• Question 139:

  You have the following table named Sales.

  You need to return sales data ordered by customer name and date of sale. For each customer, the most recent sale must be listed first.

  Which query should you use?

  A. SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomerName, SalesDate;
  B. SELECT CustomerName, SalesDate FROM Sales ORDER BY SalesDate DESC, CustomerName;
  C. SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomerName, SalesDate DESC;
  D. SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomerName DESC;
  C. SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomerName, SalesDate DESC;

• Question 140:

  You administer a Microsoft SQL Server 2008 database that contains tables named Sales.Customer and Sales.SalesOrder. A diagram of the tables is shown in the exhibit. (Click the Exhibit button.)

  

  You need to execute a query to update the value of the Customer-Value field to HV when a customer has more than 5 orders for a total sales amount of more than 5,000 U.S. dollars. Which Transact-SQL statement should you use?

  

  A. B.
  C. D.
  B