Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 21:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You are creating indexes in a data warehouse.

  You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.

  The reports join a column that is the primary key.

  The execution plan contains bookmark lookups for Table1.

  You discover that the reports run slower than expected.

  You need to reduce the amount of time it takes to run the reports.

  Solution: You create a nonclustered index on the primary key column that does NOT include columns.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017

• Question 22:

  SIMULATION

  You have a table named Cities that has the following two columns: CityID and CityName. The CityID column uses the int data type, and CityName uses nvarchar(max).

  You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that responded to that question from each of four cities. The table contains the following representative data:

  

  A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount is the value from the RawSurvey table.

  You need to write a Transact-SQL query to meet the following requirements: Retrieve data from the RawSurvey table in the format of the SurveyReport table. The CityID must contain the CityID of the city that was surveyed. The order of cities in all SELECT queries must match the order in the RawSurvey table. The order of cities in all IN statements must match the order in the RawSurvey table.

  Construct the query using the following guidelines: Use one-part names to reference tables and columns, except where not possible. ALL SELECT statements must specify columns. Do not use column or table aliases, except those provided. Do not surround object names with square brackets.

  

  Part of the correct Transact-SQL has been provided in the answer area 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.

  Correct Answer. explanation
  explanation

  ---

  Please see explanation 1 SELECT Rawcount 2 from (select cityid,questioned,rawcount) AS t1 3 unpivot 4 (rawcount for questioned in (QuestionID)) AS t2 5 JOIN t2

  6. ON t1.CityName = t2.cityName

  UNPIVOT must be used to rotate columns of the Rawsurvey table into column values.

  References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

• Question 23:

  HOTSPOT

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of thescenario is exactly the same in each question in this series.

  Start of repeated scenario

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

  

  You review the Employee table and make the following observations:

  1. Every record has a value in the ManagerID except for the Chief Executive Officer (CEO).

  2. The FirstName and MiddleName columns contain null values for some records.

  3. The valid values for the Title column are Sales Representative, Manager, and CEO.

  You review the SalesSummary table and make the following observations:

  1. The ProductCode column contains two parts: The first five digits represent a product code, and the last seven digits represent the unit price. The unit price uses the following pattern: ####.##.

  2. You observe that for many records, the unit price portion of the ProductCode column contains values.

  3. The RegionCode column contains NULL for some records.

  4. Sales data is only recorded for sales representatives.

  You are developing a series of reports and procedures to support the business. Details for each report or procedure follow.

  Sales Summary report: This report aggregates data by year and quarter. The report must resemble the following table.

  

  Sales Manager report: This report lists each sales manager and the total sales amount for all employees that report to the sales manager.

  Sales by Region report: This report lists the total sales amount by employee and by region. The report must include the following columns: EmployeeCode, MiddleName, LastName, RegionCode, and SalesAmount.

  If MiddleName is NULL, FirstName must be displayed.

  If both FirstName and MiddleName have null values, the world Unknown must be displayed. If RegionCode is NULL, the word Unknown must be displayed.

  Report1: This report joins data from SalesSummary with the Employee table and other tables. You plan to create an object to support Report1. The object has the following requirements:

  1. be joinable with the SELECT statement that supplies data for the report

  2. can be used multiple times with the SELECT statement for the report

  3. be usable only with the SELECT statement for the report

  4. not be saved as a permanent object Report2: This report joins data from SalesSummary with the Employee table and other tables. You plan to create an object to support Report1. The object has the following requirements:

  1. be joinable with the SELECT statement that supplies data for the report

  2. can be used multiple times for this report and other reports

  3. accept parameters

  4. be saved as a permanent object

  Sales Hierarchy report. This report aggregates rows, creates subtotal rows, and super-aggregates rows over the SalesAmount column in a single result-set. The report uses SaleYear, SaleQuarter, and SaleMonth columns as a hierarchy. The result set must not contain a grand total or cross-tabulation aggregate rows.

  Current Price Stored Procedure: This stored procedure must return the unit price for a product when a product code is supplied. The unit price must include a dollar sign at the beginning. In addition, the unit price must contain a comma every three digits to the left of the decimal point, and must display two digits to the left of the decimal point. The stored procedure must not throw errors, even if the product code contains invalid data.

  End of Repeated Scenario

  You need to create the stored procedure that returns the current unit price.

  How should you complete the stored procedure definition? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings?redirectedfrom=MSDN#the-currency-c-format-specifier

• Question 24:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a database named DB1 that contains two tables named Sales.Customers and Sales.Orders. Sales.Customers has a foreign key relationship to a column named CustomerID in SalesOrders.

  You need to recommend a query that returns all the customers. The query must also return the number of orders that each customer placed in 2016.

  Solution: You recommend the following query:

  

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

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

• Question 25:

  HOTSPOT

  You create a table to store sales information for an online sales application by running the following Transact-SQL statement:

  

  You have a historical report that summarizes the sales for each quarter and year. The query that generated the data for the report is no longer available. A representative report contains the following data:

  

  You need to recreate the query for the report.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  WHEN expression is a simple expression to which input_expression is compared when the simple CASE format is used and is any valid expression.

  ELSE is the expression returned if no comparison operation evaluates as TRUE.

  DATEPART returns an integer which represents the specified datepart of the date, such as day, month, year, quarter etc.

  References:

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

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

• Question 26:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a database that tracks orders and deliveries for customers in North America. The database contains the following tables:

  Sales.Customers

  

  Application.Cities

  

  Sales.CustomerCategories

  

  The company's development team is designing a customer directory application. The application must list customers by the area code of their phone number. The area code is defined as the first three characters of the phone number. The

  main page of the application will be based on an indexed view that contains the area and phone number for all customers.

  You need to return the area code from the PhoneNumber field.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  The function should return nvarchar(10) and not a TABLE.

  References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/

• Question 27:

  DRAG DROP

  You are a database administrator for an online retail store. You create a table to track orders by running the following Transact-SQL statement:

  

  You need to create a report that includes the following information: Total sales for each year Total sales for each category Total sales for each category per year How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segment to the correct locations. Each Transact-SQL segment 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. NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Box 1: GROUP BY Box 2: CUBE GROUP BY CUBE creates groups for all possible combinations of columns. For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL). Example: This code runs a GROUP BY CUBE operation on Country and Region. SELECT Country, Region, SUM(Sales) AS TotalSales FROM Sales GROUP BY CUBE (Country, Region); References: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

• Question 28:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

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

  

  You need to create a Transact-SQL query that returns the following information: the customer number the customer contact name the date the order was placed, with a name of DateofOrder a column named Salesperson, formatted with the employee first name, a space, and the employee last name orders for customers where the employee identifier equals 4 The output must be sorted by order date, with the newest orders first. The solution must return only the most recent order for each customer. Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We cannot use the column alias Salesperson in the GROUP BY clause, since in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

  References: https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by/3841804

• Question 29:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  A database has two tables as shown in the following database diagram:

  

  You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns: tblProvince.ProvinceId tblProvince.ProvinceName a derived column named LargeCityCount that presents the total count of large cities for the province

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We need to list all provinces that have at least two large cities. There is no reference to this in the code.

• Question 30:

  You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)

  

  You need to create a list of all customers, the order ID for the last order that the customer placed, and the date that the order was placed. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date.

  Which Transact-SQL statement should you run?

  

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

  ---

  ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:"Luxemburg, Rosa"

  The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression.

  References: https://msdn.microsoft.com/en-us/library/ms184325.aspx