Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 1:

  You have a column named TelephoneNumber that stores numbers as varchar(20). You need to write a query that returns the first three characters of a telephone number.

  Which expression should you use?

  A. LEFT(TelephoneNumber, 3)
  B. SUBSTRING(TelephoneNumber, 3, 3)
  C. SUBSTRING (TelephoneNumber, 3, 1)
  D. CHARINDEX('[0-9][0-9][0-9]', TelephoneNumber, 3)
  A. LEFT(TelephoneNumber, 3)

• Question 2:

  You are writing a batch that contains multiple UPDATE statements to modify existing products. You have placed these updates into one explicit transaction. You need to set an option at the beginning of the transaction to roll back all changes if any of the updates in the transaction fail.

  Which option should you enable?

  A. ARITHABORT
  B. XACT_ABORT
  C. IMPLICIT_TRANSACTIONS
  D. REMOTE_PROC_TRANSACTIONS
  B. XACT_ABORT

• Question 3:

  Your company stores vendor and price information in a database. All items in the database have a list price. You need to increase the list price for all products of only the vendor named Fabrikam by 20.00.

  Which query should you use?

  A. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE NOT EXISTS (SELECT VendorId FROM Purchasing.Vendor); WHERE VendorName = 'Fabrikam');
  B. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE VendorId NOT IN (SELECT VendorId FROM Purchasing.Vendor); WHERE VendorName = 'Fabrikam');
  C. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE EXISTS (SELECT VendorId FROM Purchasing.Vendor WHERE VendorName = 'Fabrikam');
  D. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE VendorId IN (SELECT VendorId FROM Purchasing.Vendor WHERE VendorName = 'Fabrikam');
  D. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE VendorId IN (SELECT VendorId FROM Purchasing.Vendor WHERE VendorName = 'Fabrikam');

• Question 4:

  You are the administrator of a SQL Server database. Database table modifications and additions must occur only between 11:00 P.M. and midnight. You need to ensure that if database table modifications or additions are attempted at any other time, an error is raised and the attempt is not successful.

  Which Transact-SQL statement should you use?

  

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

• Question 5:

  You have a table named Subscribers. You receive an updated list of subscribers.

  You need to remove subscribers that are no longer on the list.

  Which clause should you use to remove the subscribers from the table?

  A. WHEN NOT MATCHED
  B. WHEN MATCHED
  C. WHEN NOT MATCHED BY SOURCE
  D. WHEN NOT MATCHED BY TARGET
  C. WHEN NOT MATCHED BY SOURCE

• Question 6:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance used to support a customer service application. You create tables named complaint, customer, and product as follows:

  

  You need to write a query to sum the monthly sales of each product. Which SQL query should you use?

  

  A. B. C. D. E. F. G.
  H. I. J.
  I

• Question 7:

  You have a server named Contoso with multiple databases.

  You have been tasked to write a PowerShell script to determine which databases on the server are larger than 100GB. You open PowerShell from SQL Server Management Studio. You create two variables as follows:

  PS SQLSERVER:\SQL\Contoso> $MultipleOfGB = 1024 * 1024 PS SQLSERVER:\SQL\Contoso> $Server = Get-Item You need to determine which script will produce the desired list of databases.

  What script should you use?

  A. $Server.Databases | Where-Object{($_.Size * $MultipleOfGB) -gt 100GB\} | Select-Object Name, Size
  B. $Server | Where-Object{($_.DatabaseSize * $MultipleOfGB) -match 100GB\} | Select-Object Name, DatabaseSize
  C. $Server | Where-Object{($_.DatabaseSize * $MultipleOfGB) -gt 100GB\} | Select-Object Name, DatabaseSize
  D. $Server.Databases | Where-Object{($_.Size * $MultipleOfGB) -match 100GB\} | Select-Object Name, Size
  A. $Server.Databases | Where-Object{($_.Size * $MultipleOfGB) -gt 100GB\} | Select-Object Name, Size

• Question 8:

  You are required to modify a table named Sales.SalesOrder. The table has change tracking enabled on it. You need to disable change tracking prior to modifying the Sales.SalesOrder table.

  Which Transact-SQL statement should you use?

  A. EXEC sys.sp_cdc_disable_db
  B. ALTER DATABASE Contoso SET CHANGE_TRACKING = OFF
  C. ALTER TABLE Sales.SalesOrder DISABLE CHANGE_TRACKING
  D. EXEC sys.sp_cdc_disable_table @source_schema = N'Sales', @source_name = N'SalesOrder', @capture_instance = N'Sales_SalesOrder'
  C. ALTER TABLE Sales.SalesOrder DISABLE CHANGE_TRACKING

  ---

  Enabling Change Tracking for a Database

  Before you can use change tracking, you must enable change tracking at the database level.

  ALTER DATABASE AdventureWorks2008R2

  SET CHANGE_TRACKING = ON

  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

  Enabling Change Tracking for a Table

  Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.

  ALTER TABLE Person.Person

  ENABLE CHANGE_TRACKING

  WITH (TRACK_COLUMNS_UPDATED = ON);

  Disabling Change Tracking

  Change tracking must first be disabled for all change-tracked tables before change tracking can be set to OFF for the database. To determine the tables that have change tracking enabled for a database, use the sys.

  change_tracking_tables catalog view.

  ALTER TABLE Person.Person

  DISABLE CHANGE_TRACKING;

  When no tables in a database track changes, you can disable change tracking for the database.

  ALTER DATABASE AdventureWorks2008R2

  SET CHANGE_TRACKING = OFF;

• Question 9:

  You have a table named Orders. You have been tasked to modify your company's main database to remove all inactive order rows. You are developing a stored procedure that will enable you to delete these rows. You have written the following code segment to accomplish this task. (Line numbers are included for reference only.)

  01 BEGIN TRY 02 DECLARE @RowCount INT = 1000 03 WHILE @RowCount = 1000 04 BEGIN 05 DELETE TOP (1000) FROM Orders WHERE Status = 'Inactive'; 06 SET @RowCount = @@ROWCOUNT 07 ... 08 END 09 END TRY 10 BEGIN CATCH 11 PRINT ERROR_MESSAGE() 12 END CATCH

  You need to insert a Transact-SQL statement that will notify you immediately after each batch of rows is deleted.

  Which Transact-SQL statement should you insert at line 07?

  A. RAISERROR ('Deleted %i rows', 6, 1, @RowCount)
  B. RAISERROR ('Deleted %i rows', 16, 1, @RowCount)
  C. RAISERROR ('Deleted %i rows', 10, 1, @RowCount) WITH NOWAIT
  D. RAISERROR ('Deleted %i rows', 11, 1, @RowCount) WITH NOWAIT
  C. RAISERROR ('Deleted %i rows', 10, 1, @RowCount) WITH NOWAIT

  ---

  RAISERROR can be used as an alternative to PRINT to return messages to calling applications. Because RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block, specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block. NOWAIT option sends messages immediately to the client.

• Question 10:

  You administer a Microsoft SQL Server 2008R2 database that hosts a customer relationship management (CRM) application. The application supports the following two types of customers as shown in the exhibit. (Click the Exhibit button.)

  

  Business customers who have shipments sent to their office locations Residential customers who have shipments sent to their home address

  You need to generate a list of residential customers who live outside North America.

  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: