Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 431:

  You work as a database administrator at manufacturing company named ABC.com. ABC.com has a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table named Sites that was created using the following Transact-SQL code:

  CREATE TABLE Sites (

  SiteID int NOT NULL PRIMARY KEY,

  Location int NOT NULL,

  Manager nvarchar(200) NOT NULL,

  Week smallint NOT NULL,

  ItemsProduced int NOT NULL )

  You want to write the Transact-SQL query that returns the number of items produced at each location for each week. In addition, you want the result set to include a column named PrevItemsProduced that holds the number of items produced

  at each location for the week before.

  What Transact SQL statement would accomplish this task?

  A. SELECT Location, Week, ItemsProduced, LEAD(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
  B. SELECT Location, Week, ItemsProduced, FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
  C. SELECT Location, Week, ItemsProduced, LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
  D. SELECT Location, Week, ItemsProduced, LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
  E. SELECT Location, Week, ItemsProduced, CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
  C. SELECT Location, Week, ItemsProduced, LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites

• Question 432:

  DRAG DROP

  You use a Microsoft SQL Server database.

  You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.

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

  

  

  ---

  References: http://msdn.microsoft.com/en-us/library/ms191432.aspx http://stackoverflow.com/questions/12419330/how-to-create-indexed-view-with-select-distinct-statement-insql-2005

• Question 433:

  You are developing a Microsoft SQL Server database.

  You write a query that returns the median value of SalesYTD for each TerritoryID.

  You need to ensure that the query returns the most accurate median value, even if there is no value of SalesYTD that is the exact median.

  Which Transact-SQL statement should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

  ---

  PERCENTILE_CONT returns the appropriate value, even if it doesn't exist in the data set.

  When specifying 0.5 as the percentile, you are calculating the median value.

  Example:

  MedianRate = PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY [BaseRate]) OVER (PARTITION BY [DepartmentName])

  Incorrect Answers:

  PERCENTILE_DISC returns an actual set value.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/functions/percentile-disc-transact-sql

• Question 434:

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

  

  You need to write a Transact-SQL query that displays rows of data in the following XML format:

  Company Name

  Contact Name

  453 3545 5224

  10

  Product Name

  559.00

  12

  132

  Product Name

  59.00

  102

  259

  Product Name

  599.00

  6

  Which of the following SELECT statement would you write?

  A. SELECT s.SupplierID, s.CompanyName, s.ContactName, s.Phone, p.ProductID, p.UnitPrice,UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID FOR XML AUTO, ELEMENTS
  B. SELECT s.SupplierID, s.CompanyName, s.ContactName, s.Phone, p.ProductID, p.UnitPrice,UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID FOR XML
  C. SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.Phone, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID FOR XML AUTO
  D. SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.Phone, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID FOR XML AUTO, RAW
  A. SELECT s.SupplierID, s.CompanyName, s.ContactName, s.Phone, p.ProductID, p.UnitPrice,UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID FOR XML AUTO, ELEMENTS

• Question 435:

  You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.

  

  Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a self-reference foreign key constraint?

  A. DateHired
  B. DepartmentID
  C. EmployeelD
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  I. ReportsToID

• Question 436:

  SIMULATION

  You plan to deploy a stored procedure for a database named TICKETS.

  You need to implement error handling for the stored procedure to ensure that the system-defined error messages are returned if an error occurs upon insert.

  Part of the correct Transact-SQL has been provided in the answer are below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided

  as well as below it.

  

  

  Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Check the answer below

  1 CREATE PROCEDURE AdCase 2 @CustomerId INT, 3 @ProblemId INT, 4 @Comment NVARCHAR (MAX) 5 AS 6 7 BEGIN TRY 8 INSERT INTO Cases 9 (CustomerId, ProblemId, Comment) 10 VALUES 11 (@CustomerId, @ProblemId, @Comment) 12 END TRY 13 BEGIN CATCH 14 INSERT INTO AppLog 15 (CurrentTime, ErrorNumber, CustomerId) 16 VALUES 17 (getdate(), ERROR_NUMBER(), @CustomerId); 18 THROW; 19 20 END CATCH 21

  Explanation: Make changes and additions in the above lines. 7 BEGIN TRY 12 END TRY 13 BEGIN CATCH 18 THROW; 20 END CATCH

  ---

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-2017

• Question 437:

  Which of the following statements are true about an INSTEAD OF trigger? (Choose all that apply.)

  A. INSTEAD OF triggers can be created on views.
  B. INSTEAD OF triggers execute instead of AFTER triggers.
  C. INSTEAD OF triggers can only be declared for UPDATE statements.
  D. INSTEAD OF triggers execute code in place of the original DML statement
  A. INSTEAD OF triggers can be created on views.
  D. INSTEAD OF triggers execute code in place of the original DML statement

• Question 438:

  What is wrong with the T-SQL batch statement below.

  @find varchar(30);

  SET @find = 'Man';

  SELECT LastName FROM Person WHERE LastName = @find;

  A. You can't use the @find parameter in a query
  B. DECLARE is missing
  C. Nothing, it's fine
  D. You should use SELECT instead of SET
  B. DECLARE is missing

  ---

  Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement.

• Question 439:

  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 stores the total number of a product that ABC.com has.

  What Transact-SQL statements would accomplish this task?

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

  ---

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

• Question 440:

  DRAG DROP

  You need to create a query to execute a stored procedure for each row in a record set.

  How should you complete the query? To answer, drag the appropriate values to the correct locations. Each value 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: