Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 173 discussion

Actual exam question from Microsoft's 70-761

Question #: 173
Topic #: 1

You have a database that tracks customer complaints.
The database contains a table named Complaints that includes the following columns:

You need to create a query that lists complaints about defective products. The report must include complaints where the exact phrase "defective product" occurs, as well as complaints where similar phrases occur.
Which Transact-SQL statement should you run?

A. SELECT ComplaintID, ComplaintTranscript FROM Complaints WHERE CONTAINS(CustomerTranscript, 'defective') AND CONTAINS(CustomerTranscript, 'product')
B. SELECT ComplaintID, CustomerTranscript FROM Complaints WHERE SOUNDEX('defective') = SOUNDEX('product')
C. SELECT ComplaintID, CustomerTranscript FROM Complaints WHERE FREETEXT(CustomerTranscript, 'defective product')
D. SELECT ComplaintID, Customer Transcript FROM Complaints WHERE CustomerTranscript like '%defective product%'

Show Suggested Answer

Suggested Answer: A 🗳️
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017

by M4x at Sept. 16, 2019, 9:04 a.m.

Comments

Submit Cancel

mlourinho

Highly Voted 5 years, 7 months ago

FREETEXT » this predicate searches for values that match the meaning and not just the exact wording of the words in the search condition. https://docs.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql?view=sql-server-ver15

upvoted 6 times

...

Billybob0604

Most Recent 4 years, 5 months ago

The answer is D : "where the exact phrase "defective product" occurs, as well as complaints where similar phrases occur." The like statement in this case includes both, which is what we want.

upvoted 1 times

...

geekeek1

4 years, 6 months ago

cannot be A because it has "ComplaintTranscript" in the select clause. There is NO such column name. so answer is C.

upvoted 2 times

...

lh2607

4 years, 6 months ago

C https://docs.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql?view=sql-server-ver15

upvoted 1 times

...

BabyBee

4 years, 7 months ago

I think answer is C. Cuz' A is adding separate terms, and we are looking for the phrase and similar phrases.

upvoted 3 times

...

TheDUdeu

4 years, 9 months ago

D is the answer none of the columns is a Full Text index.If it was then the answer is A. First off you need to turn Full Text Search on in the designer and then make an index. If this question assumes that then it is A.

upvoted 2 times

...

Aghie

4 years, 10 months ago

as the question says, the exact phrase "defective product" occurs as well as complaints where similar phrases occur. "defective product" is a phrase, so it should be the one with wild card on both side of this phrase. So D

upvoted 1 times

...

p0lskiy

4 years, 10 months ago

Task requirements is: "where the exact phrase "defective product" occurs". Like works with spaces, therefore correct answer is D.

upvoted 1 times

...

stm22

4 years, 11 months ago

C) and D) only return exact phrase; they fail the 'similar phrase' requirement

upvoted 1 times

lh2607

4 years, 6 months ago

Not true. Freetext doesn't only return exact phrases. Answer = C

upvoted 1 times

...

stm22

4 years, 11 months ago

A) will return all records that contain both words, including records with the exact phrase

upvoted 3 times

...

Anette

5 years ago

D is the correct one BUT it must not have space

upvoted 1 times

Anette

5 years ago

Well, D is for exactly 'Defective product' but in case of "similar phrases" I assuem it stand for 'defective' or 'product'. So the right answer is C. Tested

upvoted 2 times

...

eggzamtaker

4 years, 5 months ago

This was retarded. Smh.

upvoted 2 times

...

M4x

5 years, 9 months ago

What mean 'similar' ? If is a free-text column the answer is C (verified) otherwise is D the like

upvoted 4 times

anonimdom

5 years, 4 months ago

The D searches only for exact phrase, so it looks like the answer is C or A.

upvoted 1 times

...

daniel_yes23

5 years, 2 months ago

To be A or C you suppose to create a full text index. Error message: "Cannot use a CONTAINS or FREETEXT predicate on table or indexed view '#Complaint' because it is not full-text indexed." In this case without full text index the best option is D.

upvoted 3 times

daniel_yes23

5 years, 1 month ago

Sorry, D is not going to work because of the column name there is space between two words

upvoted 1 times

...