This page was exported from Braindump2go Free Exam Dumps with PDF and VCE Collection [ https://www.mcitpdump.com ] Export date:Fri Nov 22 4:18:22 2024 / +0000 GMT ___________________________________________________ Title: [New 70-761 Dumps!]70-761 VCE and PDF Free Download in Braindump2go[115-122] --------------------------------------------------- 2018 February New Microsoft 70-761 Exam Dumps with PDF and VCE Free Updated Today! Following are some new 70-761 Real Exam Questions:1.|2018 New 70-761 Exam Dumps (PDF & VCE) 135Q&As Download:https://www.braindump2go.com/70-761.html2.|2018 New 70-761 Exam Questions & Answers Download:https://drive.google.com/drive/folders/0B75b5xYLjSSNZG9yTW9reVdkZG8?usp=sharing QUESTION 115SIMULATIONYou create a table named Sales.Categories by running the following Transact-SQL statement: You add the following data to the table. You need to create a query that uses a common table expression (CTE) to show the parent category of each category. The query must meet the following requirements: Return all columns from the Categories table in the order shown. Exclude all categories that do not have a parent category.Construct the query using the following guidelines: Name the expression ParentCategories. Use PC as the alias for the expression. Use C as the alias for the Categories table. Use the AS keyword for all table aliases. Use individual column names for each column that the query returns. Do not use a prefix for any column name. 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. You may check syntax as many times as needed.Answer: 1 WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS (SELECTA. categoryID,c.name,c.parentcategoryid2 FROM sales.categories c3 WHERE parentcategoryid is not null4 )5 SELECT * FROM parentcategoriesNote: On Line 1 replace c with WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS Note: The basic syntax structure for a CTE is:WITH expression_name [ ( column_name [,...n] ) ]AS( CTE_query_definition )References: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspxQUESTION 116SIMULATIONYou have a database that includes the following tables. All of the tables are in the Production schema. You need to create a query that returns a list of product names for all products in the Beverages category.Construct the query using the following guidelines: Use the first letter of the table name as the table alias. Use two-part column names. Do not surround object names with square brackets. Do not use implicit joins. Do not use variables. Use single quotes to surround literal values.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. You may check syntax as many times as needed.Answer: 1 SELECT p.productname2 FROM Production.categories AS c3 inner join production.products as p on c.categoryid=p.categoryid 4 WHERE c.categoryname = 'Beverages'Note: On line 3 change * to =QUESTION 117SIMULATIONYou work for an organization that monitors seismic activity around volcanos. You have a table named GroundSensors. The table stored data collected from seismic sensors. It includes the columns describes in the following table: The database also contains a scalar value function named NearestMountain that accepts a parameter of type geography and returns the name of the mountain that is nearest to the sensor.You need to create a query that shows the average of the normalized readings from the sensors for each mountain. The query must meet the following requirements: Return the average normalized readings named AverageReading. Return the nearest mountain name named Mountain. Do not return any other columns. Exclude sensors for which no normalized reading exists.Construct the query using the following guidelines: Use one part names to reference tables, columns and functions. Do not use parentheses unless required. Define column headings using the AS keyword. 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.Answer: 1 SELECT avg (normalizedreading) as AverageReading, location as Mountain 2 FROM GroundSensors3 WHERE normalizedreading is not nullNote: On line 1 change to AverageReading and change to Mountain.QUESTION 118SIMULATIONYou create a table named Sales.Orders by running the following Transact-SQL statement: You need to write a query that removes orders from the table that have a Status of Canceled.Construct the query using the following guidelines: use one-part column names and two-part table names use single quotes around literal values do not use functions do not surround object names with square brackets do not use variables do not use aliases for column names and table names 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.Answer: 1. DELETE from sales.orders where status='Canceled'Note: On line 1 change calceled to CanceledExample: Using the WHERE clause to delete a set of rowsThe following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 1000.00.DELETE FROM Production.ProductCostHistoryWHERE StandardCost > 1000.00;References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sqlQUESTION 119SIMULATIONYou have a database that contains the following tables. You need to create a query that lists the highest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements: Return the LastName and SalesYTD for the three salespersons with the highest year-to-date sales values. Exclude salespersons that have no value for TerritoryID.Construct the query using the following guidelines: Use the first letter of a table name as the table alias. Use two-part column names. Do not surround object names with square brackets. Do not use implicit joins. Use only single quotes for literal text. Use aliases only if required. 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.1 SELECT top 3 lastname,salesYTD2 FROM Person AS p INNER JOIN SalesPerson AS s3 ON p.PersonID = s.SalesPersonID4 WHERE territoryid is null5 order by salesytd dsecUse the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.Answer: 1 SELECT top 3 lastname,salesYTD2 FROM Person AS p INNER JOIN SalesPerson AS s3 ON p.PersonID = s.SalesPersonID4 WHERE territoryid is not null5 order by salesytd descNote:On line 4 add a not before null.On line 5 change dsec to desc.QUESTION 120Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply to that question.You have a database for a banking system. The database has two tables named tblDepositAcct and tblLoanAcct that store deposit and loan accounts, respectively. Both tables contain the following columns: You need to determine the total number of deposit and loan accounts.Which Transact-SQL statement should you run?A. SELECT COUNT(*)FROM (SELECT AcctNoFROM tblDepositAcctINTERSECTSELECT AcctNoFROM tblLoanAcct) RB. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNIONSELECT CustNoFROM tblLoanAcct) RC. SELECT COUNT(*)FROM (SELECT CustNoFROMtblDepositAcctUNION ALLSELECT CustNoFROM tblLoanAcct) RD. SELECT COUNT (DISTINCT D.CustNo)FROM tblDepositAcct D, tblLoanAcct LWHERE D.CustNo = L.CustNoE. SELECT COUNT(DISTINCT L.CustNo)FROM tblDepositAcct DRIGHT JOIN tblLoanAcct L ON D.CustNo =L.CustNoWHERE D.CustNo IS NULLF. SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctEXCEPTSELECT CustNoFROM tblLoanAcct) RG. SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON D.CustNo =L.CustNoWHERE D.CustNo IS NULL OR L.CustNo IS NULLH. SELECT COUNT(*)FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON D.CustNo = L.CustNoAnswer: CExplanation:Would list the customers with duplicates, which would equal the number of accounts.Incorrect Answers:A: INTERSECT returns distinct rows that are output by both the left and right input queries operator.B: Would list the customers without duplicates.D: Number of customers.F: EXCEPT returns distinct rows from the left input query that aren't output by the right input query.References:https://msdn.microsoft.com/en-us/library/ms180026.aspxQUESTION 121Hotspot QuestionYou need to develop a Transact-SQL statement that meets the following requirements:- The statement must return a custom error when there are problems updating a table.- The error number must be the value 50555.- The error severity level must be 14.- A Microsoft SQL Server alert must be triggered when the error condition occurs.Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area. Answer: Explanation:RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. New applications should use THROW instead.Note: RAISERROR syntax:RAISERROR( { msg_id | msg_str | @local_variable }{ ,severity ,state }[ ,argument [ ,...n ] ] )[ WITH option [ ,...n ] ]The LOG option logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine.References:https://msdn.microsoft.com/en-us/library/ms178592.aspxQUESTION 122Hotspot QuestionYou have two tables as shown in the following image: You need to analyze the following query. (Line numbers are included for reference only.) Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. Answer: !!!RECOMMEND!!!1.|2018 New 70-761 Exam Dumps (PDF & VCE) 135Q&As Download:https://www.braindump2go.com/70-761.html2.|2018 New 70-761 Study Guide Video: YouTube Video: YouTube.com/watch?v=7Q-ah51thwA --------------------------------------------------- Images: --------------------------------------------------- --------------------------------------------------- Post date: 2018-02-13 03:01:00 Post date GMT: 2018-02-13 03:01:00 Post modified date: 2018-02-11 03:03:27 Post modified date GMT: 2018-02-11 03:03:27 ____________________________________________________________________________________________ Export of Post and Page as text file has been powered by [ Universal Post Manager ] plugin from www.gconverters.com