Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 11:

  You have a table named Sales. You are tasked to list products that have been sold to less than ten customers. You need to write a query to achieve the task.

  Which Transact-SQL statement should you use?

  A. SELECT ProductID, COUNT(*) AS CustomerCount FROM Sales GROUP BY ProductID, CustomerID HAVING COUNT(*) < 10;
  B. SELECT ProductID, COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID HAVING COUNT(DISTINCT CustomerID) < 10;
  C. SELECT ProductID, CustomerID, COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID, CustomerID HAVING COUNT(DISTINCT CustomerID) < 10;
  D. SELECT * FROM (SELECT ProductID, RANK() OVER (ORDER BY CustomerID DESC) AS Rnk FROM Sales) s WHERE s.Rnk
  B. SELECT ProductID, COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID HAVING COUNT(DISTINCT CustomerID) < 10;

• Question 12:

  You have a SQL Server 2008 database named Contoso with a table named Invoice. The primary key of the table is InvoiceId, and it is populated by using the identity property. The Invoice table is related to the InvoiceLineItem table. You remove all constraints from the Invoice table during a data load to increase load speed. You notice that while the constraints were removed, a row with InvoiceId = 10 was removed from the database. You need to re-insert the row into the Invoice table with the same InvoiceId value.

  Which Transact-SQL statement should you use?

  A. INSERT INTO Invoice (InvoiceId, ...VALUES (10, ...
  B. SET IDENTITY_INSERT Invoice ON; INSERT INTO Invoice (InvoiceId, ... VALUES (10, ... SET IDENTITY_INSERT Invoice OFF;
  C. ALTER TABLE Invoice; ALTER COLUMN InvoiceId int; INSERT INTO Invoice (InvoiceId, ... VALUES (10, ...
  D. ALTER DATABASE Contoso SET SINGLE_USER; INSERT INTO Invoice (InvoiceId, ... VALUES (10, ... ALTER DATABASE Contoso SET MULTI_USER;
  B. SET IDENTITY_INSERT Invoice ON; INSERT INTO Invoice (InvoiceId, ... VALUES (10, ... SET IDENTITY_INSERT Invoice OFF;

• Question 13:

  You create a stored procedure that contains proprietary formulas.

  You need to ensure that no users are able to access Microsoft SQL Server Management Studio to view the definition of the stored procedure.

  Which code segment should you use?

  

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

• Question 14:

  You have a table named Employees.

  You want to identify the supervisor to which each employee reports. You write the following query.

  SELECT e.EmloyeeName AS [EmployeeName],

  EmployeeName AS [SuperVisorName]

  FROM Employees e

  You need to ensure that the query returns a list of all employees and their respective supervisor.

  Which join clause should you use to complete the query?

  A. LEFT JOIN Employees s ON e.ReportsTo = s.EmployeeId
  B. RIGHT JOIN Employees s ON e.ReportsTo = s.EmployeeId
  C. INNER JOIN Employees s ON e.EmployeeId = s.EmployeeId
  D. LEFT JOIN Employees s ON e.EmployeeId = s.EmployeeId
  A. LEFT JOIN Employees s ON e.ReportsTo = s.EmployeeId

• Question 15:

  You administer a Microsoft SQL Server database named AdventureWorks2008R2. The database has a table that has the following definition:

  

  You plan to implement custom error handling for INSERT commands. The error number for a duplicate key is 2627. The error number for a NULL violation is 515.

  You need to ensure that an INSERT statement meets the following requirements:

  If a duplicate row is detected during insertion, no error message must be raised. For NULL errors, the prefix "NULL:" must be added to the message; all others errors must begin with "OTHER:"

  Return all errors as error number 50000.

  Which Transact-SQL statement should you use?

  

  B. C. D.
  C

• Question 16:

  You administer a Microsoft SQL Server 2008 R2 database that has a table named Customer. The table has the following definition:

  

  The database also has a table named CustomerExclusionList. Data will be added to the CustomerExclusionList table regularly. The CustomerExclusionList table has the following definition:

  

  You need to create a view that returns all records and columns of the Customer table that are not present in the CustomerExclusionList table. Which Transact-SQL statement should you use?

  

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

• Question 17:

  You administer a Microsoft SQL Server 2008 R2 database instance named AdventureWorks.

  A user who has the db_datareader permissions on the AdventureWorks database wants to view detailed information about how the following query will be

  executed:

  

  You need to ensure that the user can view the following information in a data grid without executing the query: Estimated number of rows of output Estimated I/O cost Estimated CPU cost Which two 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 18:

  You are a database developer for your organization. You have an application hosted on Microsoft SQL Server 2008 R2. One of the tables in the application database has the following definition:

  

  Users report that the response time of the stored procedure has decreased.

  You need to ensure that the response time of the stored procedure is improved. 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 19:

  You attempt to query sys.dm_db_index_usage_stats to check the status on the indexes in the Contoso database. The query fails and you receive the following error:

  "The user does not have permission to perform this action."

  You need to have the least amount of permissions granted to access the dynamic management views.

  Which permissions should be granted?

  A. SELECT
  B. CONTROL
  C. VIEW SERVER STATE
  D. CREATE EXTERNAL ACCESS ASSEMBLY
  C. VIEW SERVER STATE

• Question 20:

  You are tasked with creating a workload that will be used by the Database Engine Tuning Advisor (DTA). You need to create a workload in an appropriate format.

  Which format should you choose? (Each correct answer represents a complete solution. Choose three.)

  A. XML File
  B. Transact-SQL Script
  C. SQL Server Event Log
  D. SQL Server Transaction Log
  E. SQL Server Profiler Trace File
  F. Performance Counter Log File
  A. XML File
  B. Transact-SQL Script
  E. SQL Server Profiler Trace File

  ---

  Database Engine Tuning Advisor uses trace files, trace tables, Transact-SQL scripts, or XML files as workload input when tuning databases.