Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 421:

  You work as a SQL Server 2012 database developer at ABC.com. TesABCig.com has a database named DataDB.

  You are developing a complex stored procedure named sp_updater that will use a single transaction to update several tables in the DataDB database.

  You are concerned about data integrity and incomplete updates should the sp_updater stored procedure cause a run- time error.

  To mitigate this potential problem you want the transaction to terminate and the transaction to be rolled back if the sp_updater stored procedure raises a run-time error.

  How would you accomplish this task?

  A. You should make use of the SET XACT_ABORT ON statement in the stored procedure.
  B. You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.
  C. You should make use of a LOOP hint in the stored procedure.
  D. You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.
  E. You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.
  A. You should make use of the SET XACT_ABORT ON statement in the stored procedure.

  ---

  Ref: http://msdn.microsoft.com/en-us/library/ms188792

• Question 422:

  What is the default frame window functions use when a window order clause is specified but an explicit window frame clause isn't? (Choose all that apply.)

  A. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  B. ROWS UNBOUNDED PRECEDING
  C. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  D. RANGE UNBOUNDED PRECEDING
  C. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  D. RANGE UNBOUNDED PRECEDING

  ---

  C. Default frame

  D.This is an abbreviated form of the default frame, having the same meaning.

• Question 423:

  You develop a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerId. You need to create a query that meets the following requirements:

  -

  Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.

  -

  Results must not include customers who have not placed any orders. Which Transact-SQL query should you use?

  A. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId
  B. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId
  C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerId
  D. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId
  D. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId

  ---

  http://msdn.microsoft.com/en-us/library/ms177634.aspx

• Question 424:

  You use a Microsoft Azure SQL Database instance. The instance contains a table named Products that has columns named Code, Name, and IsPerishable. You need to create a view named VwPerishableProducts that:

  1.

  returns rows from Products that have a value of True in the IsPerishable column;

  2.

  does not allow columns to be altered or dropped in the underlying table. Which Transact-SQL statement should you run?

  

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

  ---

  SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

• Question 425:

  You develop a Microsoft SQL Server database. You design a table to store product information. The table is defined as follows:

  

  You need to meet the following requirements:

  If a product has a product category value "REGULAR", the product should have a sub-category with a length of two characters.

  If a product has the product category other than "REGULAR", the product may or may not have a sub-category.

  Which Transact-SQL statement should you use?

  

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

  ---

  Either the Category column is not REGULAR, or it is REGULAR and the length of the column is exactly 2.

• Question 426:

  What levels can an index have? (Choose all that apply.)

  A. Intermediate level
  B. Heap level
  C. Root level
  D. Leaf level
  A. Intermediate level
  C. Root level
  D. Leaf level

• Question 427:

  You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:

  -Always starts with 1.

  -

  Starts again from 1 after it reaches 100. Which Transact-SQL statement should you use?

  A. CREATE SEQUENCE CustomerSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALOE FOR CustomerSequence DROP SEQUENCE CustomerSequence
  B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence
  C. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence
  D. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100 CYCLE UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence
  B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence

  ---

  http://msdn.microsoft.com/en-us/library/ff878091.aspx

• Question 428:

  You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:

  -Does not return an error.

  -

  Closes all opened transactions. Which Transact-SQL statement should you use?

  A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH
  B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERRCR != 0 ROLLBACK TRANSACTION; THROW; END CATCH
  C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH
  D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ZRROR !- 0 ROLLBACK TRANSACTION; END CATCH
  D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ZRROR !- 0 ROLLBACK TRANSACTION; END CATCH

  ---

  Option A and C are wrong: IF @@ TRANCOUNT = 0, means that no transaction was executed. @@TRANCOUNT indicates the number of BEGIN TRANSACTION encountered before the @@TRANCOUNT code. Option B is also wrong because of the THROW clause: the requirement says "should not return any error".

• Question 429:

  You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The SalesDB database is shown in the following database diagram: You need to write a Transact-SQL query that displays all Customers, whether they have invoices or not. The query must also display the InvoiceDate for a Customer that has an Invoice. How would you accomplish this task?

  

  A. You should make use of a UNION.
  B. You should make use of an INNER JOIN.
  C. You should make use of a CROSS JOIN.
  D. You should make use of an OUTER JOIN.
  E. You should make use of a CROSS APPLY.
  D. You should make use of an OUTER JOIN.

• Question 430:

  You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output. Which Transact-SQL operator should you use?

  A. CROSS JOIN
  B. CROSS APPLY
  C. PIVOT
  D. UNPIVOT
  C. PIVOT

  ---

  http://technet.microsoft.com/en-us/library/ms177634.aspx