Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 141:

  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. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
  B. 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.
  C. Use the ALTER DATABASE...SET LOGFILE command along with the midsize parameter.
  D. In SQL Server Management Studio, expand the Storage leaf under the database. Select the transaction log file and set the maximum size of the file.
  B. 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 142:

  You administer a Microsoft SQL Server 2012 instance.

  The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week.

  You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:

  No more than 5 minutes worth of transactions are lost. Data can be recovered by using the minimum amount of administrative effort.

  What should you do? Choose all that apply.

  A. Configure the database to use the SIMPLE recovery model.
  B. Create a DIFFERENTIAL database backup every 4 hours.
  C. Create a LOG backup every 5 minutes.
  D. Configure the database to use the FULL recovery model.
  E. Create a FULL database backup every 24 hours.
  F. Create a DIFFERENTIAL database backup every 24 hours.
  B. Create a DIFFERENTIAL database backup every 4 hours.
  C. Create a LOG backup every 5 minutes.
  D. Configure the database to use the FULL recovery model.
  E. Create a FULL database backup every 24 hours.

• Question 143:

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

• Question 144:

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

  

  You need to create a view named uv_CustomerFullName to meet the following requirements:

  *The code must NOT include object delimiters.

  *The view must be created in the Sales schema.

  *Columns must only be referenced by using one-part names. *The view must return the first name and the last name of all customers. *The view must prevent the underlying structure of the customer table from being changed. *The view must

  be able to resolve all referenced objects, regardless of the user's default schema.

  Which code segment should you use?

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

  A. CREATE VIEW Sales.uv_CustomerFullName with Schemabinding AS SELECT FirstName, LastName FROM Customers
  B. CREATE VIEW Sales.uv_CustomerFullName AS SELECT FirstName, LastName FROM Customers
  A. CREATE VIEW Sales.uv_CustomerFullName with Schemabinding AS SELECT FirstName, LastName FROM Customers

• Question 145:

  You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

  A. ENCRYPTBYKEY
  B. ENCRYPTION
  C. ENCRYPTBYPASSPHRASE
  D. ENCRYPTBYCERT
  B. ENCRYPTION

• Question 146:

  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 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is 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. Perform a point-in-time restore.
  D. Restore the latest full backup.
  E. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  F. Perform a page restore.
  G. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  H. Restore the latest full backup. Then, restore the latest differential backup.
  F. Perform a page restore.

• Question 147:

  You create the following stored procedure. (Line numbers are included for reference only.)

  

  You need to ensure that the stored procedure performs the following tasks:

  If a record exists, update the record.

  If no record exists, insert a new record.

  Which four Transact-SQL statements should you insert at line 07? (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:

  

  

• Question 148:

  You develop a database application for a university.

  You need to create a view that will be indexed that meets the following requirements:

  Displays the details of only students from Canada.

  Allows insertion of details of only students from Canada.

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

  You administer a Microsoft SQL Server 2012 instance. After a routine shutdown, the drive that contains tempdb fails. You need to be able to start the SQL Server.

  What should you do?

  A. Modify tempdb location in startup parameters.
  B. Start SQL Server in minimal configuration mode.
  C. Start SQL Server in single-user mode.
  D. Configure SQL Server to bypass Windows application logging.
  B. Start SQL Server in minimal configuration mode.

• Question 150:

  You administer a Microsoft SQL Server 2012 database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo

  owned by UserB. GetCustomerOrderInfo selects data from both tables.

  You create a new user named UserC.

  You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC.

  Which permission or permissions should you assign to UserC? Choose all that apply.

  A. The Select permission on Customers
  B. The Execute permission on GetCustomerOrderInfo
  C. The Take Ownership permission on Customers
  D. The Control permission on GetCustomerOrderInfo
  E. The Take Ownership permission on Orders
  F. The Select permission on Orders
  A. The Select permission on Customers
  B. The Execute permission on GetCustomerOrderInfo