This page was exported from Latest Lead2pass Dumps For Sharing [ https://www.ensurepass.net ] Export date:Wed Jan 22 5:02:52 2025 / +0000 GMT ___________________________________________________ Title: [2018-2-7] Easily Pass 70-761 Exam With Lead2pass Updated Microsoft 70-761 Dumps (116-122) --------------------------------------------------- 100% Valid Lead2pass Microsoft 70-761 New Questions Free Version.v.2018-2-7.135q: https://www.lead2pass.com/70-761.html QUESTION 116SIMULATION You 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 117SIMULATION You 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 null Note: On line 1 change to AverageReading and change to Mountain. QUESTION 118SIMULATION You 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 namesuse single quotes around literal valuesdo not use functionsdo not surround object names with square bracketsdo not use variablesdo 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 Canceled Example: 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-sql QUESTION 119SIMULATION You 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 dsec 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 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 desc Note: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.CustNo Answer: 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.aspx QUESTION 121Hotspot Question You 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.aspx QUESTION 122Hotspot Question You 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: Explanation:To compare char(5) and nchar(5) an implicit conversion has to take place.Explicit conversions use the CAST or CONVERT functions, as in line number 6.References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine#implicit-and-explicit-conversion 70-761 dumps full version (PDF&VCE): https://www.lead2pass.com/70-761.html Large amount of free 70-761 exam questions on Google Drive: https://drive.google.com/open?id=0B3Syig5i8gpDU2RSQnhzX2pIZVE You may also need: 70-762 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDMW9NcjJrQXlsMGs 70-764 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDUjBoM0pVQnlUTlU 70-765 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDejczeWp0aURaSnM 70-767 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDdTF0R0taLWgxSmc 70-768 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDZ2pRQkV6Vnc4dHc --------------------------------------------------- Images: http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1161_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1162_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1171_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1172_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1173_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1181_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1182_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1183_thumb.png http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1191_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1192_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1201_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1211_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1212_thumb.png http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1221_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1222_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1223_thumb.jpg http://examgod.com/l2pimage/0820e1ff0e7c_DA8D/1224_thumb.png --------------------------------------------------- --------------------------------------------------- Post date: 2018-02-08 07:50:52 Post date GMT: 2018-02-08 07:50:52 Post modified date: 2018-02-08 07:50:52 Post modified date GMT: 2018-02-08 07:50:52 ____________________________________________________________________________________________ Export of Post and Page as text file has been powered by [ Universal Post Manager ] plugin from www.gconverters.com