Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 111:

  Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The

  ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.

  You need to increase the values in the Price column of all products in the Products table by 5 percent.

  You also need to log the changes to the ProductsPriceLog table.

  Which Transact-SQL query should you use?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  A. OptionA

  ---

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

• Question 112:

  DRAG DROP

  You administer a Microsoft SQL Server database instance that uses transparent database encryption.

  You plan to move the database from the current server to a new server by using Backup and Restore.

  You need to ensure that the database can be restored to the new server. You also need to ensure that the database remains encrypted at all times.

  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 113:

  You are planning to install a Microsoft SQL Server database server that will be used by a web application.

  You need to minimize the attack surface area.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Use a single user account to start all database services.
  B. Install only the database engine and disable other features.
  C. Install the database server on a server core installation of Windows Server 2012 R2.
  D. Set the database service to start automatically.
  E. Install the database server as a named instance.
  B. Install only the database engine and disable other features.
  C. Install the database server on a server core installation of Windows Server 2012 R2.

  ---

  To reduce the attack surface area and to make sure you are not affected by undiscovered service vulnerabilities, disable any service that is not required

  Because Server Core has fewer system services running on it than a Full installation does, there's less attack surface (that is, fewer possible vectors for malicious attacks on the server). This means that a Server Core installation is more secure than a similarly configured Full installation.

  References: https://msdn.microsoft.com/en-us/library/dd184076.aspx https://msdn.microsoft.com/en-us/library/ff648664.aspx#c18618429_010

• Question 114:

  You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report.

  You need to display dates and times in several international formats.

  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.
  C. Use the FORMAT function.

  ---

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

• Question 115:

  You administer a Microsoft SQL Server database.

  Users report that a billing application is throwing errors during busy times of the day.

  You identify that the following error is regularly written to the application's error log every day between 4:00 pm and 5:30 pm:

  Error 1205: Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim.

  You need to determine the cause of deadlocks. You start a trace using SQL Server Profiler.

  Which event should you select?

  A. Lock: Timeout
  B. Lock: Escalation
  C. Lock: Deadlock
  D. Deadlock graph
  D. Deadlock graph

  ---

  When diagnosing these kinds of problems, it's worth considering that there are useful trace events such as Lock:Deadlock and Deadlock graph events. The deadlock graph reveals the deadlocked sessions, what statements were being run, the database, objects and pages on which the sessions were deadlocked, and more.

  References: https://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-deadlock

• Question 116:

  This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series.

  You administer a Microsoft SQL Server database 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:

  

  One of the hard disk drives that stores the transactional database fails at 23:32 hours. Attempts to create a tail log backup are unsuccessful.

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

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model?view=sql-server-2017

• Question 117:

  You develop a database for a travel application.

  You need to design tables and other database objects.

  You create the Airline_Schedules table.

  You need to store the departure and arrival dates and times of flights along with time zone information.

  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.
  I. Use the DATETIMEOFFSET data type.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ff848733.aspx http://msdn.microsoft.com/en-us/library/bb630289.aspx

• Question 118:

  You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.

  You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.

  You need to ensure that AgentPortalUser can send email messages.

  What should you do?

  A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.
  B. Disable the guest user in the msdb database.
  C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
  D. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.
  E. Configure the Microsoft SQL Server Agent to use Database Mail.
  A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.

  ---

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

• Question 119:

  You are the senior database administrator for a Microsoft SQL Server environment that uses Microsoft Azure SQL Database.

  You need to grant a junior database administrator only the permissions required to create new logins.

  Which Microsoft Azure SQL Database role should you assign the junior database administrator?

  A. setupadmin
  B. loginmanager
  C. securityadmin
  D. serveradmin
  B. loginmanager

  ---

  Members of the login manager role can create new logins in the master database. References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

• Question 120:

  You administer a Microsoft SQL Server server. You plan to deploy new features to an application.

  You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.

  What should you do?

  A. Query the sys.dm_db_index_usage_stats DMV.
  B. Query the sys.dm_db_missing_index_details DMV.
  C. Use the Database Engine Tuning Advisor.
  D. Query the sys.dm_db_missing_index_columns DMV.
  C. Use the Database Engine Tuning Advisor.

  ---

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