Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 51:

  You administer a Microsoft SQL Server 2012 database.

  All database traffic to the SQL Server must be encrypted by using secure socket layer (SSL) certificates or the connection must be refused.

  Network administrators have deployed server certificates to the Windows store of all Windows servers on the network from a trusted Certificate Authority. This is the only Certificate Authority allowed to distribute certificates on the network.

  You enable the Force Encryption flag for the MSSQLServer protocols, but client computers are unable to connect. They receive the following error message:

  "A connection was successfully established with the server, but then an error occurred during the pre- login handshake, (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server)"

  You notice the following entry in the SQL Server log:

  "A self-generated certificate was successfully loaded for encryption."

  You need to configure SQL Server to encrypt all client traffic across the network. You also need to ensure that client computers are able to connect to the server by using a trusted certificate. 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:

  

  

• Question 52:

  You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.

  You need to be notified immediately when fatal errors occur on Server01.

  What should you create?

  A. an Alert
  B. a Server Audit Specification
  C. an Extended Event session
  D. a Resource Pool
  E. a Policy
  F. a SQL Profiler Trace
  G. a Database Audit Specification
  A. an Alert

• Question 53:

  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.
  A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.

• Question 54:

  You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries.

  Which isolation level should you use?

  A. Create a user-defined type on the master database.
  B. Create a user-defined data type on the model database.
  C. Create a user-defined type on the model database.
  D. Create a user-defined data type on the master database.
  C. Create a user-defined type on the model database.

• Question 55:

  You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.

  You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.

  You need to ensure that the following requirements are met:

  Future modifications to the table definition will not affect the applications' ability to access data.

  The new object can accommodate data retrieval and data modification.

  You need to achieve this goal by using the minimum amount of changes to the existing applications.

  What should you create for each application?

  A. views
  B. table partitions
  C. table-valued functions
  D. stored procedures
  A. views

• Question 56:

  You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.

  You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2.

  Which Transact-SQL query should you use?

  A. SELECT StudentCode as Code, RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
  B. SELECT Id, Name, Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS Rank FROM StudentMarks
  C. SELECT StudentCode as Code, DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
  D. SELECT StudentCode as Code, NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
  E. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
  F. SELECT StudentCode AS Code,Marks AS Value FRCM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM StudentMarks) tmp WHERE Rank = 1
  G. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
  H. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks, RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
  D. SELECT StudentCode as Code, NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

• Question 57:

  Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.

  You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.

  Which approach should you use?

  A. a SELECT statement that includes CASE
  B. BULK INSERT
  C. A user-defined function
  D. Cursor
  E. view
  D. Cursor

• Question 58:

  You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected.

  You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer.

  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.
  K

• Question 59:

  You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.

  UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.

  You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema.

  Which Transact-SQL statement should you use?

  A. DENY SELECT ON Object::Regions FROM UserA
  B. DENY SELECT ON Object::Regions FROM Sales
  C. REVOKE SELECT ON Schema::Customers FROM Sales
  D. REVOKE SELECT ON Schema::Customers FROM UserA
  E. REVOKE SELECT ON Object::Regions FROM Sales
  F. REVOKE SELECT ON Object::Regions FROM UserA
  G. DENY SELECT ON Schema::Customers FROM Sales
  H. DENY SELECT ON Schema::Customers FROM UserA
  I. EXEC sp_addrolemember 'Sales', 'UserA'
  J. EXEC sp droprolemember 'Sales', 'UserA'
  H. DENY SELECT ON Schema::Customers FROM UserA

• Question 60:

  Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence.

  Which approach should you use?

  A. a recursive stored procedure
  B. Trigger
  C. An UPDATE statement that includes CASE
  D. Cursor
  E. The foreach SQLCLR statement
  D. Cursor