Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 221:

  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

  ---

  http://technet.microsoft.com/en-us/library/bb510663.aspx http://technet.microsoft.com/en-us/library/ms174361.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx http://technet.microsoft.com/en-us/library/ms190357.aspx http://technet.microsoft.com/en-us/library/ms188061.aspx

• Question 222:

  You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

  

  Check the answer below

  CREATE VIEW sales.uv_CustomerFullNames WITH SCHEMABINDING AS SELECT FirstName, LastName FROM Sales.Customers

• Question 223:

  You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB that has a table named Inventory.

  The Inventory table has three columns named ProductID, InStore and InWarehouse. The ProductID column is the primary key and is linked to the Products table. The InStore column stores the quantity of a product that is held at ABC.com's

  retail shop, while the InWarehouse column stores the quantity of a product that is held at ABC.com's warehouse.

  You need to add a computed column that is the sum of values in the InStore and InWarehoue columns for each product.

  What Transact SQL statement would accomplish this task?

  A. ALTER TABLE Inventory ADD TotalProducts AS (InStore + InWarehouse)
  B. ALTER TABLE Inventory ADD TotalProducts int SPARSE NOT NULL
  C. ALTER TABLE Inventory ADD TotalProducts AS SUM (ALL) OVER (GROUP BY InStore, InWarehouse)
  D. DROP TABLE Inventory GO Exam CREATE TABLE Inventory ( ProductID int NOT NULL PRIMARY KEY, InStore int NOT NULL, InWarehouse int NOT NULL, TotalProducts AS SUM (InStore, InWarehouse) )
  A. ALTER TABLE Inventory ADD TotalProducts AS (InStore + InWarehouse)

  ---

  Ref: http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx

• Question 224:

  What is the scan called when SQL Server scans a clustered index in logical order of the index?

  A. Allocation order scan
  B. Clustered index scan
  C. Index order scan
  D. Index order seek
  C. Index order scan

• Question 225:

  Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

  A. SELECT COUNT (*) FROM SaLeaOrders WHERE OrderTime = CONVERT(DATE, GETDATE ())
  B. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime - GETDATE()
  C. SELECT COUNT(-) FROM SaLesCrders WHERE CONCERT(VARCHAR, OrderTime, 112) = CONVERT (VARCHAR, GETDATE(I, 112)
  D. SELECT CCUNT(*) FROM SalesCrder3 WHERE CrderTime >= CONVERT(DATE, GETDATE()) AND CrderTime < DATEADD(DAY, CONVERT(DATS, GETDATE()))
  D. SELECT CCUNT(*) FROM SalesCrder3 WHERE CrderTime >= CONVERT(DATE, GETDATE()) AND CrderTime < DATEADD(DAY, CONVERT(DATS, GETDATE()))

  ---

  Two answers will return the correct results: the "WHERE CONVERT..." and "WHERE ... AND ... " answers. The correct answer for Microsoft would be the answer that is most "efficient". Anybody have a clue as to which is most efficient? In the execution plan, the one that I've selected as the correct answer is the query with the shortest duration. Also, the query answer with "WHERE CONVERT..." threw warnings in the execution plan... something about affecting CardinalityEstimate and SeekPlan. I also found this article, which leads me to believe that I have the correct answer. http://technet.microsoft.com/en-us/library/ms181034.aspx

• Question 226:

  You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named ProductsDB. The relevant part of the ProductsDB is shown in the following database diagram:

  

  You need to write a Transact-SQL query that display a single row in the following XML format:

  Which of the following SELECT statement would you write?

  A. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML RAW
  B. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML
  C. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML AUTO
  D. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML AUTO, RAW
  A. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML RAW

• Question 227:

  You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for a database driven Web application that allows visitors to vote for the cricket player of the week. The number of votes is stored in a table named WeeklyVotes that has columns named Week, PlayerName, Votes.

  You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to have a rank of 2, and the last 10 to have a rank of 3.

  Which of the following SELECT statement would you use?

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

  A. check the answer below

  A

  SELECT TOP 30 PlayerName, NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes FROM WeeklyVotes GROUP BY PlayerName

• Question 228:

  You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB with tables named Customer and Orders. The Customer and Orders tables were created using the following Transact-SQL code:

  CREATE TABLE SalesDB.Customers

  (

  CustomerID int NOT NULL PRIMARY KEY,

  CustomerName nvarchar (250) NOT NULL,

  Address1 nvarchar (100) NOT NULL,

  Address2 nvarchar (100) NULL,

  City nvarchar (50) NOT NULL,

  State nvarchar (50) NOT NULL,

  Zip varchar (5) NOT NULL,

  Phone varchar (10) NOT NULL

  )

  GO

  CREATE TABLE SalesDB.Orders

  (

  OrderID int NOT NULL PRIMARY KEY,

  CustomerID int NOT NULL,

  OrderDate datetime NOT NULL,

  ShipDate datetime NOT NULL,

  CustomerID int NOT NULL,

  SalesRepID int NOT NULL

  )

  GO

  You are developing a stored procedure named OrdersByDate that returns the OrderID, CustomerID, CustomerName and OrderDate. The stored procedure will take a parameter named @date that uses the int datatype.

  The @date parameter will be used to filter the result set based on the OrderDate column in the Orders table.

  How would you create the stored procedure?

  A. CREATE PROCEDURE OrdersByDate @date int AS SELECT OrderID, CustomerID, CustormerName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE OrderDate = CONVERT(datetime,@date)
  B. CREATE PROCEDURE OrdersByDate @date int AS SELECT OrderID, CustomerID, CustormerName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE OrderDate =@date
  C. CREATE PROCEDURE OrdersByDate @date int AS SELECT OrderID, CustomerID, CustormerName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE OrderDate = CAST(@date AS datetime)
  D. CREATE PROCEDURE OrdersByDate @date int AS SELECT OrderID, CustomerID, CustormerName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE OrderDate = PARSE(@date AS datetime)
  A. CREATE PROCEDURE OrdersByDate @date int AS SELECT OrderID, CustomerID, CustormerName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE OrderDate = CONVERT(datetime,@date)

• Question 229:

  You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that the root cause is a query against a frequently updated table that has a clustered index. The query returns four columns: three columns in its WHERE clause contained in a non-clustered index and one additional column. You need to optimize the statement. 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.
  C. Add a FORCESEEK hint to the query.

• Question 230:

  DRAG DROP

  You develop a Microsoft SQL Server database for a sales ordering application.

  You want to create a report that displays the increase of order quantities over the previous year for each product.

  You need to write a query that displays:

  Product name,

  Year of sales order,

  Sales order quantity,

  Increase of order quantity over the previous year.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Box 1: FROM ..

  Box 2: LAG (not LEAD)

  Lag accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic

  function in a SELECT statement to compare values in the current row with values in a previous row.

  Not lead: Lead accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row.

  Box 3: GROY BY PRO.NAME, YEAR (OrderDate)

  References: https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017