Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 361:

  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.

  

  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 use an identity specification to include a seed of 1,000 and an increment of 1?

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

• Question 362:

  Your manager has asked you to check the index stats for a particular table called Address. You have executed the following query (see below) and it reported that the avg_fragmentation_in_percent is 15%. What should you do? SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'Test'), OBJECT_ID(N'Address'), NULL, NULL , 'DETAILED');

  A. Run ALTER INDEX REBUILD WITH (ONLINE = ON)
  B. Run ALTER INDEX REORGANIZE
  C. Rebuild the table
  D. Nothing, the index fragmentation is at a safe level
  B. Run ALTER INDEX REORGANIZE

  ---

  Generally, you should reorganize indexes if the fragmentation is between 5 and 30 percent. You should rebuild the index if the fragmentation is over 30 percent. Remember, these values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment.

• Question 363:

  You are maintaining a Microsoft SQL Server database. You run the following query:

  

  You observe performance issues when you run the query. You capture the following query execution plan:

  

  You need to ensure that the query performs returns the results as quickly as possible. Which action should you perform?

  A. Add a new index to the ID column of the Person table.
  B. Add a new index to the EndDate column of the History table.
  C. Create a materialized view that is based on joining data from the ActiveEmployee and History tables.
  D. Create a computed column that concatenates the GivenName and SurName columns.
  A. Add a new index to the ID column of the Person table.

  ---

  Cost is 53% for the Table Scan on the Person (p) table. This table scan is on the ID column, so we should put an index on it.

• Question 364:

  You are working with a table that has an XML column that contains information about books. Each book may have an associated price.

  You need to write with a query that returns the price of each book as a non-xml value.

  Which XML method should you use?

  A. Exist()
  B. Nodes()
  C. Query()
  D. Value()
  D. Value()

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-2017

• Question 365:

  You have a database named Sales 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:

  1. UnitPrice must be returned in descending order.

  2. The query must use two-part names to reference the table.

  3. The query must use the RANK function to calculate the results.

  4. The query must return the ranking of rows in a column named PriceRank.

  5. The list must display the columns in the order that they are defined in the table.

  PriceRank must appear last.

  Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

  Check the answer below

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

  You develop a Microsoft SQL Server database that contains tables as shown in the exhibit. (Click the Exhibit button.)

  

  You need to retrieve a list of clients for whom there is no corresponding information in the Projects table. Which Transact-SQL statements should you run?

  

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

  ---

  References: https://www.dofactory.com/sql/select-distinct

  https://www.dofactory.com/sql/right-outer-join

• Question 367:

  You are a database developer of a Microsoft SQL Server server that hosts a business-critical high-activity OLTP system.

  The database contains a table named Customer that has more than one million records. The table has the following defintion:

  

  Customer data is updates in an external system. These changes are copied into a staging table on a weekly basis. This table has the following definition.

  

  You need to write a Transact-SQL statement that will update the records in the Customer table by using the values in the Customer_Staging table that has matching CustomerID values. You must ensure that only one record is updated at a time.

  Which Transact-SQL query should you run?

  

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

• Question 368:

  DRAG DROP

  You are creating queries for a shopping cart application.

  

  You have the following requirements:

  Query1 must list products where less than 100 units have been sold in total.

  Query2 must list products where more than 10 units have been sold in a single order.

  You need to identify the correct sub-queries to complete the outer queries that you have written.

  Which Transact-SQL statement or statements should you use? To answer, drag the appropriate SQL statements to the correct locations. Each SQL statement 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 369:

  DRAG DROP

  When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

  When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

  You develop a Microsoft SQL Server Database. The database contains a table named Status that is defined by the following Transact-SQL statement:

  

  There are thousands of rows in the Status table, with significant duplication of data in the Color column. Ninety percent of the rows in the table have Color="Red", and the remaining 10 percent have Color="Green". You want to normalize the Color information in this table. You create a table named Colors that is defined by the following DDL:

  

  You populate the new Colors table by using the following Transact-SQL statement:

  INSERT Colors (ColorName) SELECT DISTINCT Color FROM Status

  You need to ensure that the following requirements are met:

  The Status table uses only colors that exist in the Colors table.

  Data redundancy in the Status table is reduced.

  Data integrity is enforced during the normalization process.

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

  Select and Place:

  

  

  ---

  First update the new column ColorID, and drop the old Column Color.

  Add a check constraint on the new ColorID column, and finally add a foreign key constraint.

• Question 370:

  You have a Microsoft SQL Server database that includes two tables named EmployeeBonus and BonusParameters. The tables are defined by using the following Transact-SQL statements.

  

  The tables are used to compute a bonus for each employee. The EmployeeBonus table has a non- null value in either the Quarterly, HalfYearly or Yearly column. This value indicates which type of bonus an employee receives. The BonusParameters table contains one row for each calendar year that stores the amount of bonus money available and a company performance indicator for that year. You need to calculate a bonus for each employee at the end of a calendar year. Which Transact-SQL statement should you use?

  A. SELECT CAST(CHOOSE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20, (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS `Bonus' FROM EmployeeBonus, BonusParameters
  B. SELECT "Bonus" = CASE EmployeeBonus WHEN Quarterly=1 THEN (Quarterly * AvailableBonus *CompanyPerformance)/40 WHEN HalfYearly=1 THEN (HalfYearly * AvailableBonus * CompanyPerformance)/20 WHEN Yearly=1 THEN (Yearly * AvailableBonus * CompanyPerformance)/10 END FROM EmployeeBonus,BonusParameters
  C. SELECT CAST(COALESCE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20, (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS `Bonus' FROM EmployeeBonus, BonusParameters
  D. SELECT NULLIF(NULLIF((Quarterly * AvailableBonus * CompanyPerformance)/40,(HalfYearly * AvailableBonus * CompanyPerformance)/20), (Yearly * AvailableBonus * CompanyPerformance)/10) AS `Bonus' FROM EmployeeBonus, BonusParameters
  B. SELECT "Bonus" = CASE EmployeeBonus WHEN Quarterly=1 THEN (Quarterly * AvailableBonus *CompanyPerformance)/40 WHEN HalfYearly=1 THEN (HalfYearly * AvailableBonus * CompanyPerformance)/20 WHEN Yearly=1 THEN (Yearly * AvailableBonus * CompanyPerformance)/10 END FROM EmployeeBonus,BonusParameters