Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 91:

  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 CustomerId 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')
  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

• Question 92:

  You develop a database for a travel application.

  You need to design tables and other database objects.

  You create a stored procedure.

  You need to supply the stored procedure with multiple event names and their dates as parameters.

  What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  E. Use a user-defined table type.

• Question 93:

  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 your backup will continue if any invalid checksum is encountered.

  Which backup option should you use?

  A. STANDBY
  B. Differential
  C. FULL
  D. CHECKSUM
  E. BULK_LOGGED
  F. CONTINUE_AFTER_ERROR
  G. SIMPLE
  H. DBO_ONLY
  I. COPY_ONLY
  J. SKIP
  F. CONTINUE_AFTER_ERROR

• Question 94:

  You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named Node 1 and Node 2.

  Node 1 fails and the cluster fails over to Node 2.

  You need to replace Node 1 and add it to the cluster.

  Which four 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 95:

  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

• Question 96:

  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. On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version. You need to perform a full database backup that will be restored on the development server.

  Which backup option should you use?

  A. NORECOVERY
  B. FULL
  C. NO_CHECKSUM
  D. CHECKSUM
  E. Differential
  F. 8ULK_LOGGED
  G. STANDBY
  H. RESTART
  I. SKIP
  J. Transaction log
  L

• Question 97:

  You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

  

  Users are able to use single INSERT statements or INSERT...SELECT statements into this view.

  You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.

  Which Transact-SQL statement should you use?

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

• Question 98:

  You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers. The view has the following definition:

  

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

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

• Question 99:

  You administer a Microsoft SQL Server database that supports a banking transaction management application.

  You need to retrieve a list of account holders who live in cities that do not have a branch location.

  Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

  A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
  B. SELECT AccountHolderID FROM AccountHolder WHERE CityID ALL (SELECT CityID FROM BranchMaster)
  C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID SOME (SELECT CityID FROM BranchMaster)
  D. SELECT AccountHolderID FROM AccountHolder WHERE CityID ANY (SELECT CityID FROM BranchMaster)
  A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
  B. SELECT AccountHolderID FROM AccountHolder WHERE CityID ALL (SELECT CityID FROM BranchMaster)

• Question 100:

  You administer a Microsoft SQL Server 2012 database.

  You need to convert the database to a contained database. You also need to ensure that all users are converted to contained users.

  Which three 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: