[2018-2-7] Lead2pass 2018 100% Real 70-761 Exam Questions (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 116
SIMULATION

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.productname
2 FROM Production.categories AS c
3 inner join production.products as p on c.categoryid=p.categoryid 4 WHERE c.categoryname = ‘Beverages’

Note: On line 3 change * to =

QUESTION 117
SIMULATION

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.

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 GroundSensors
3 WHERE normalizedreading is not null

Note: On line 1 change to AverageReading and change to Mountain.

QUESTION 118
SIMULATION

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

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 rows
The 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.ProductCostHistory
WHERE StandardCost > 1000.00;

References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql

QUESTION 119
SIMULATION

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.

1 SELECT top 3 lastname,salesYTD
2 FROM Person AS p INNER JOIN SalesPerson AS s
3 ON p.PersonID = s.SalesPersonID
4 WHERE territoryid is null
5 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,salesYTD
2 FROM Person AS p INNER JOIN SalesPerson AS s
3 ON p.PersonID = s.SalesPersonID
4 WHERE territoryid is not null
5 order by salesytd desc

Note:
On line 4 add a not before null.
On line 5 change dsec to desc.

QUESTION 120
Note: 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 AcctNo
FROM tblDepositAcct
INTERSECT
SELECT AcctNo
FROM tblLoanAcct) R
B.    SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
UNION
SELECT CustNo
FROM tblLoanAcct) R
C.    SELECT COUNT(*)
FROM (SELECT CustNo
FROMtblDepositAcct
UNION ALL
SELECT CustNo
FROM tblLoanAcct) R
D.    SELECT COUNT (DISTINCT D.CustNo)
FROM tblDepositAcct D, tblLoanAcct L
WHERE D.CustNo = L.CustNo
E.    SELECT COUNT(DISTINCT L.CustNo)
FROM tblDepositAcct D
RIGHT JOIN tblLoanAcct L ON D.CustNo =L.CustNo
WHERE D.CustNo IS NULL
F.    SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
EXCEPT
SELECT CustNo
FROM tblLoanAcct) R
G.    SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo =L.CustNo
WHERE D.CustNo IS NULL OR L.CustNo IS NULL
H.    SELECT COUNT(*)
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo

Answer: C
Explanation:
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 121
Hotspot 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 122
Hotspot 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: