Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 1:

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

  

  You have the following query:

  

  You need to recreate the query to meet the following requirements:

  Reference columns by using one-part names only.

  Sort aggregates by SalesTerritoryID, and then by ProductID. Order the results in descending order from SalesTerritoryID to ProductID. The solution must use the existing SELECT clause and FROM clause.

  Which code segment should you use?

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

  A. SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty), MAX(DiscountAmount) FROM Sales.Details GROUP BY SalesTerritoryID,ProductID ORDER BY SalesTerritoryID DESC, ProductID DESC
  B. SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty), MAX(DiscountAmount) FROM Sales.Details ORDER BY SalesTerritoryID DESC, ProductID DESC
  A. SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty), MAX(DiscountAmount) FROM Sales.Details GROUP BY SalesTerritoryID,ProductID ORDER BY SalesTerritoryID DESC, ProductID DESC

• Question 2:

  You administer all the deployments of Microsoft SQL Server 2012 in your company. A database contains a large product catalog that is updated periodically.

  You need to be able to send the entire product catalog to all branch offices on a monthly basis. Which configuration should you use?

  A. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby B SQL Server that includes an application database configured to perform transactional replication
  B. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary D Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
  C. SQL Server that includes an application database configured to perform snapshot replication
  D. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  F. Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
  E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode

• Question 3:

  You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. The view has the following definition:

  

  You need to ensure that users can update only the phone numbers by using this view. What should you do?

  A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
  B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
  C. Create an AFTER UPDATE trigger on the view.
  D. Create an INSTEAD OF UPDATE trigger on the view.
  D. Create an INSTEAD OF UPDATE trigger on the view.

• Question 4:

  You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.

  The recovery model and backup schedule are configured as shown in the following table:

  

  At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database.

  You need to ensure that the databases are restored. You also need to ensure that data loss is minimal.

  What should you do?

  A. Perform a partial restore.
  B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  C. Restore the latest full backup.
  D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  E. Perform a page restore.
  F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  G. Perform a point-in-time restore.
  H. Restore the latest full backup. Then, restore the latest differential backup.
  H. Restore the latest full backup. Then, restore the latest differential backup.

• Question 5:

  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 CustomerIdvalue 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, Orderld, 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')
  C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

• Question 6:

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

  

  You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date.

  The rows for Procedure1 are not sorted.

  Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1.

  A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime.

  You need to update the SELECT statement to meet the following requirements:

  *The code must NOT use aliases.

  *The code must NOT use object delimiters.

  *The objects called in Procedure1 must be able to be resolved by all users. *OrderDate must be compared to Parameter1 after the data type of Parameter1 is changed to datetime.

  Which SELECT statement should you use?

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

  A. SELECT OrderID FROM Orders WHERE OrderDate>CONVERT(datetime,@Parameter1)
  B. SELECT Orders.OrderID FROM Orders WHERE Orders.OrderDate>CONVERT(datetime,@Parameter1)
  A. SELECT OrderID FROM Orders WHERE OrderDate>CONVERT(datetime,@Parameter1)

• Question 7:

  You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).

  The financial database has the following characteristics:

  A data file of 2 terabytes is located on a dedicated LUN (drive D). A transaction log of 10 GB is located on a dedicated LUN (drive E).

  Drive D has 1 terabyte of free disk space.

  Drive E has 5 GB of free disk space.

  The database is continually modified by users during business hours from Monday through Friday between

  09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

  These data load operations must occur in the minimum amount of time.

  A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that the minimum amount of data is lost.

  Which recovery model should the database use?

  A. FULL
  B. DBO_ONLY
  C. CONTINUE_AFTER_ERROR
  D. CHECKSUM
  E. NO_CHECKSUM
  F. SIMPLE
  G. Transaction log
  H. SKIP
  I. RESTART
  J. COPY_ONLY
  L

• Question 8:

  You administer a Microsoft SQL Server 2012 database.

  You need to ensure that the size of the transaction log file does not exceed 2 GB.

  What should you do?

  A. Execute sp_configure 'max log size', 2G.
  B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
  C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
  D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.
  D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

• Question 9:

  You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions.

  

  Which five Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 10:

  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. SQL user without login
  B. User mapped to an asymmetric key
  C. Domain user
  D. login mapped to a virtual account
  C. Domain user