Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 9 discussion

Actual exam question from Microsoft's 70-761

Question #: 9
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 -

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

Show Suggested Answer

Suggested Answer: B 🗳️
We need SELECT TOP 1 @areacode =.. to ensure that only one value is returned.

by CristianCruz at May 1, 2020, 9:42 p.m.

Comments

Submit Cancel

CristianCruz

Highly Voted 5 years, 1 month ago

ANSWER B. We need SELECT TOP 1 @areacode =.. to ensure that only one value is returned.

upvoted 5 times

...

Anirudh_net

Most Recent 3 years, 3 months ago

B is the right answer

upvoted 1 times

...

Vermonster

4 years, 5 months ago

B but the answer should be that you need TOP(1) to ensure that the FIRST substring is returned vs. the last

upvoted 1 times

Vermonster

4 years, 5 months ago

Test it yourself: DECLARE @areacode nvarchar(max) SELECT @areacode = value FROM string_split('802-233-2003','-') PRINT @areacode

upvoted 1 times

...

TheDUdeu

4 years, 9 months ago

I actually thought this would be a No but it really is A. When you return a NVARCHAR(10) on a string_split it will return only the first string so A yes this will work.

upvoted 1 times

BabyBee

4 years, 7 months ago

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

upvoted 3 times

...

oredie

4 years, 9 months ago

Answer is A. Tested: DROP VIEW IF EXISTS dbo.AreaPhoneNumbers; DROP FUNCTION IF EXISTS dbo.areaCode DROP TABLE IF EXISTS sales.PhoneNumbers; GO CREATE TABLE sales.PhoneNumbers ( Id INT identity NOT NULL ,PhoneNumber NVARCHAR(20) NOT NULL ); INSERT INTO sales.PhoneNumbers (PhoneNumber) VALUES ('123-456-8900') ,('234-567-9000'); GO CREATE FUNCTION AreaCode (@phoneNumber NVARCHAR(20)) RETURNS NVARCHAR(10) WITH SCHEMABINDING AS BEGIN DECLARE @areaCode NVARCHAR(max) SELECT @areaCode = value FROM string_split(@phoneNumber, '-') RETURN @areaCode END; GO CREATE VIEW AreaPhoneNumbers WITH schemabinding AS SELECT dbo.AreaCode(PhoneNumbers.PhoneNumber) AreaCode ,PhoneNumbers.PhoneNumber FROM sales.PhoneNumbers; GO SELECT * FROM dbo.AreaPhoneNumbers

upvoted 2 times

eduardogtc

4 years, 9 months ago

I also tested and it returns the last part and not the first (area code). Eg. declare @aa nvarchar(max) select @aa = value from string_split('123-456-980', '-' ) select @aa The SQL above returns 980

upvoted 2 times

Braindripper

4 years, 8 months ago

actually seems that using the above logic, from function, returns only the last row. in your code add "order by value desc" and you should get "123". same code in query -without order, you get "123" like default descending ordering.

upvoted 1 times

...

getstoopid

4 years, 9 months ago

Actually it's A Select @areacode can only contain one value and the input value @phonenumber is a scalar too so how many values could there be? But even a "select @var = col1 from table1" will always contain exactly one value (the value from the last row selected).

upvoted 1 times

getstoopid

4 years, 9 months ago

My bad. it returns one value, but the wrong one. namely the phonenum and not the areacode Answer is B - sorry

upvoted 2 times

...

Anette

5 years, 1 month ago

Yes B. It return last 4 numbers

upvoted 3 times

...