Exam 70-761 topic 1 question 110 discussion

Actual exam question from Microsoft's 70-761

Question #: 110
Topic #: 1

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 -

Your company is developing a new social application that connects customers to each other based on the distance between their delivery locations.
You need to write a query that returns the nearest customer.
Solution: You run the following Transact-SQL statement:

The variable @custID is set to a valid customer.
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️

by Scooter123 at Nov. 8, 2019, 1:33 p.m.

Comments

Submit Cancel

mlourinho

Highly Voted 5 years, 7 months ago

The right answer is A. IF OBJECT_ID ('TEMPDB..#Customer') IS NOT NULL DROP TABLE #Customer; GO CREATE TABLE #Customer( CustomerID INT IDENTITY (1,1) , DeliveryLocation GEOGRAPHY , LocationName VARCHAR(150) ); GO INSERT INTO #Customer(DeliveryLocation, LocationName) VALUES (geography::Point(38.7071, -9.13549, 4326), 'Lisboa') , (geography::Point(41.15, -8.61024, 4326), 'Porto') , (geography::Point(40.4165000, -3.7025600, 4326), 'Madrid') , (geography::Point(41.3887901, 2.1589899, 4326), 'Barcelona') -- SELECT * FROM #Customer DECLARE @CustID INT = 4 SELECT TOP 1 A.CustomerID, A.LocationName , A.DeliveryLocation.STDistance(B.DeliveryLocation) AS Distancia , B.CustomerID, B.LocationName FROM #Customer A CROSS JOIN #Customer B WHERE A.CustomerID = @CustID AND A.CustomerID <> B.CustomerID ORDER BY Distancia

upvoted 20 times

New_user

5 years, 6 months ago

B is correct answer. Name "Dist" can't be used on 'order by' clause cause it isn't a table's column name or function applied to it

upvoted 4 times

flashed

5 years, 4 months ago

Wrong. Logical order: 1 FROM; 2 WHERE 3 GROUP BY 4 HAVING 6 ORDER BY. So you can order by select alias.

upvoted 5 times

flashed

5 years, 4 months ago

i forgot 5. SELECT

upvoted 3 times

...

anonimdom

5 years, 5 months ago

It can't be used with GROUP by, but can be used with ORDER by.

upvoted 11 times

...

Aghie

4 years, 10 months ago

This is correct. TESTED. Alias can be used in the ORDER BY.

upvoted 3 times

...

Billybob0604

Most Recent 4 years, 5 months ago

The answer should be A. Not only aliasses of aggregations can be place in an order by, also plain aliasses from colums can. See below select Department, employeenumber as emp_number from tblemployee order by emp_number select department, count(employeenumber) as number_of_emps_per_dep from tblemployee group by department order by number_of_emps_per_dep

upvoted 1 times

...