Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 61:

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

  

  You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:

  *UnitPrice must be returned in descending order.

  *The query must use two-part names to reference the table. *The query must use the RANK function to calculate the results. *The query must return the ranking of rows in a column named PriceRank. *The list must display the columns in the

  order that they are defined in the table. PriceRank must appear last.

  Which code segment should you use?

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

  A. SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC
  B. SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog. UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC
  A. SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC

• Question 62:

  You develop a Microsoft SQL Server 2012 database.

  You need to create a batch process that meets the following requirements:

  Status information must be logged to a status table.

  If the status table does not exist at the beginning of the batch, it must be created.

  Which object should you use?

  A. Scalar user-defined function
  B. Inline user-defined function
  C. Table-valued user-defined function
  D. Stored procedure
  D. Stored procedure

• Question 63:

  You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server.

  You need to create a server role named SpecialDBARole that can perform the following functions:

  View all databases.

  View the server state.

  Assign GRANT, DENY, and REVOKE permissions on logins.

  You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary.

  Which SQL statement or statements should you use? Choose all that apply.

  A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
  B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
  C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
  D. GRANT VIEW DEFINITION TO [SpecialDBARole];
  E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
  F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
  B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
  C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
  F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];

• Question 64:

  You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:

  

  Global customers place orders from several countries.

  You need to view the country from which each customer has placed the most orders.

  Which Transact-SQL query do you use?

  A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1
  B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
  C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderAmount DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1
  D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID ORDER BY OrderAmount DESC
  A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1

• Question 65:

  You administer a Microsoft SQL Server database that is used by an application.

  Users of the application report performance issues.

  You need to choose the appropriate tool for performance-tuning of SQL Server databases.

  Which tool or tools should you use? (To answer, drag the appropriate tool or tools to their corresponding task or tasks in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

• Question 66:

  You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.

  You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.

  You need to configure the SQL Server environment to support these packages.

  What should you do?

  A. Create SSIS configuration files for the packages.
  B. Create an Integration Services catalog.
  C. Install Data Quality Services.
  D. Install Master Data services.
  B. Create an Integration Services catalog.

• Question 67:

  You administer a Microsoft SQL Server 2012 database.

  Your database is experiencing deadlock issues. You need to be able to monitor deadlocks.

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

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

  You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.

  What should you create?

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

• Question 69:

  You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3.

  You have permissions on both Database1 and Database2.

  You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.

  You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.

  Which Transact-SQL statement should you use?

  A. USE Database2
  B. EXECUTE AS OWNER
  C. USE Database1
  D. EXECUTE AS CALLER
  B. EXECUTE AS OWNER

• Question 70:

  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 and the Sales role is granted the Select permission on the Customers schema.

  You need to ensure that the Sales role, including UserA, is disallowed to select from the Regions table.

  Which Transact-SQL statement should you use?

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