Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 101:

  You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

  

  You need to display rows from the Orders table for the Customers row having the Customerld value set to 1 in the following XML format.

  

  Which Transact-SQL query should you use?

  A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
  B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS
  C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
  D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId- 1 FOR XML AUTO
  F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')

• Question 102:

  You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.

  Which type of user should you create?

  A. User mapped to a certificate
  B. SQL user without login
  C. Domain user
  D. SQL user with login
  C. Domain user

• Question 103:

  You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

  

  You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:

  Write the results to a disk.

  Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.

  The code must NOT use any object delimiters.

  The solution must ensure that LineItemTotal is stored as the last column in the table.

  Which code segment should you use?

  To answer, type the correct code in the answer area.

  A. CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity) PERSISTED)
  B. CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity))
  A. CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity) PERSISTED)

• Question 104:

  You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that a large amount of memory is consumed by single-use dynamic queries. You need to reduce procedure cache usage from these statements without creating any additional indexes.

  What should you do?

  A. Add a HASH hint to the query.
  B. Add a LOOP hint to the query.
  C. Add a FORCESEEK hint to the query.
  D. Add an INCLUDE clause to the index.
  E. Add a FORCESCAN hint to the Attach query.
  F. Add a columnstore index to cover the query.
  G. Enable the optimize for ad hoc workloads option.
  H. Cover the unique clustered index with a columnstore index.
  I. Include a SET FORCEPLAN ON statement before you run the query.
  J. Include a SET STATISTICS PROFILE ON statement before you run the query.
  G. Enable the optimize for ad hoc workloads option.

• Question 105:

  You administer a Microsoft SQL Server 2012.

  A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60. You need to find out whether the process is being blocked.

  Which Transact-SQL statement should you use?

  A. SELECT ~ FROM sys.dm_exec_sessions WHERE session_id = 60
  B. DBCC OPENTRAN
  C. EXEC sp_helpdb 60
  D. SELECT * FROM sys.dm_exec_requests WHERE session_id = 60
  C. EXEC sp_helpdb 60

• Question 106:

  Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct

  from one another.

  Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.

  You need to ensure that your query executes in the minimum possible time.

  Which query should you use?

  

  A. B. C.
  A. B. C.

• Question 107:

  You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.

  You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.

  You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.

  Which Transact-SQL statement should you use?

  A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
  B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
  C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END
  D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END
  A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END

• Question 108:

  You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA.

  Currently, Server01l/HA is the primary replica.

  You need to ensure that the following requirements are met:

  Backup operations occur on Server02/HA.

  If Server02/HA is unavailable, backup operations occur on Server03/HA.

  Backup operations do not occur on Server01/HA.

  How should you configure HaContoso?

  A. ꞏ set the backup preference of HaContoso to Prefer Secondary. ꞏ Set the backup priority of Server02/HA to 20. ꞏ Set the backup priority of Server03/HA to 10.
  B. ꞏ Set the backup preference of HaContoso to Secondary only. ꞏ Set the backup priority of Server02/HA to 20. ꞏ Set the backup priority of Server03/HA to 10.
  C. ꞏ Set the backup preference of HaContoso to Secondary only. ꞏ Set the backup priority of Server02/HA to 10. ꞏ Set the backup priority of Server03/HA to 20.
  D. ꞏ set the exclude replica of Server01/HA to true. ꞏ Set the backup priority of Server02/HA to 10. ꞏ Set the backup priority of Server03/HA to 20.
  B. ꞏ Set the backup preference of HaContoso to Secondary only. ꞏ Set the backup priority of Server02/HA to 20. ꞏ Set the backup priority of Server03/HA to 10.

• Question 109:

  You administer a Microsoft SQL Server 2012.

  A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.

  You need to find out whether the process is being blocked.

  Which Transact-SQL statement should you use?

  A. EXEC sp_who 60
  B. SELECT * FROM sys.dm_exec_sessions WHERE sessionjd = 60
  C. EXEC sp_helpdb 60
  D. DBCC INPUTBUFFER (60)
  A. EXEC sp_who 60

• Question 110:

  You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:

  CREATE TABLE Inventory (

  ItemID int NOT NULL PRIMARY KEY,

  ItemsInStore int NOT NULL,

  ItemsInWarehouse int NOT NULL)

  You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.

  The new column is expected to be queried heavily, and you need to be able to index the column.

  Which Transact-SQL statement should you use?

  A. ALTER TABLE Inventory ADD TotalItems AS ItemslnStore + ItemsInWarehouse
  B. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
  C. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
  D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
  B. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED