Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 191 discussion

Actual exam question from Microsoft's 70-761

Question #: 191
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.

A.

B.

C.

D.

Show Suggested Answer

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

by M4x at Sept. 16, 2019, 1:10 p.m.

Comments

Submit Cancel

M4x

Highly Voted 5 years, 9 months ago

as well as complaints where similar phrases occur. Answer A

upvoted 5 times

flashed

5 years, 4 months ago

where the exact phrase "defective product" ...

upvoted 1 times

...

flashed

5 years, 4 months ago

FREETEXTTABLE http://msdn.microsoft.com/en-us/library/ms177652(v=SQL.90).aspx Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

upvoted 2 times

Jiacheng

5 years, 4 months ago

then at least it should be C, not D, since that it is 'defective product' not 'defective' & 'product'

upvoted 2 times

Patty_attack

5 years, 3 months ago

"The report must include complaints where the exact phrase "defective product" occurs, as well as complaints where similar phrases occur." C would only look for the exact phrase "defective product" D would contain similar phrases and also "defective product"

upvoted 2 times

...

jonasdv

4 years, 8 months ago

it says: 'but not just the exact wording'. so the exact wording is included. Hence, A is true

upvoted 1 times

lh2607

4 years, 6 months ago

It doesn't say "just", you've just added that word in which changes the whole meaning of it. It says 'but not the exact wording' therefore it can't be A. https://docs.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql?view=sql-server-ver15

upvoted 1 times

lh2607

4 years, 6 months ago

Ignore my above link, the above URL is for FREETEXT which isn't involved in this question. Question A relates to FREETEXTTABLE where I reference the following: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/freetexttable-transact-sql?view=sql-server-ver15

upvoted 1 times

lh2607

4 years, 6 months ago

Sorry for all the comments guys. Don't mean to add confusion. It seems Jonasdv is in fact correct. When I was quoting "but not the exact wording" it was from the below link which seems to be an older version and therefore deprecated: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms177652(v=sql.90) The link I posted above is more up to date, and that includes "not just the exact wording" So I'd go for A in this instance.

upvoted 1 times

...

Billybob0604

Most Recent 4 years, 5 months ago

if object_ID('complaints','U') IS NOT NULL drop table complaints go create table complaints ( complaintID int not null ,CustomerTranscript nvarchar(max) ) create unique clustered index PK_complaint on complaints (complaintid) insert into complaints values (1, 'In this string you will find defective product') ,(2,'defective product') ,(3, 'not present in this string') ,(4, 'present in this string defective product') select * from complaints CREATE FULLTEXT CATALOG fulltextCatalog2 AS DEFAULT; CREATE FULLTEXT INDEX ON dbo.complaints(CustomerTranscript) KEY INDEX PK_complaint WITH STOPLIST = SYSTEM; --ANSWER A SELECT complaintid, customertranscript, KEY_TBL.RANK FROM complaints AS C INNER JOIN FREETEXTTABLE(complaints, CustomerTranscript, 'defective product') AS KEY_TBL ON c.complaintid = KEY_TBL.[KEY] GO --ANSWER D SELECT complaintid, customertranscript from complaints where contains (customertranscript, 'defective') and contains (CustomerTranscript , 'product')

upvoted 1 times

...

Billybob0604

4 years, 5 months ago

A and D do what is required

upvoted 1 times

...

sssshhhh

4 years, 5 months ago

It must be B, although there are some syntax errors (assuming it's a typo not being made on the exam). https://docs.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-ver15 clearly states: -To match words and phrases, use CONTAINS and CONTAINSTABLE. -To match the meaning, but not the exact wording, use FREETEXT and FREETEXTTABLE. Only B would meet the requirements here.

upvoted 1 times

...

Aghie

4 years, 10 months ago

Tested all. B and C return syntax error A and D returns the requirements

upvoted 2 times

...

stm22

4 years, 11 months ago

A)B)C) can't find similar phrases like "product is defective'. D) can find that.

upvoted 1 times

...

Anette

5 years ago

Only A fits here. I guess that similar phrases include 'product' or 'defective'. Also for B and C execution gives error: Syntax error near 'product' in the full-text search condition 'defective product'/'defective% product%'. Must put the Word NEAR between 2 words in order to execute without errors. And even after putting NEAR it gives results only for complaints which contain 'defective' and 'products' words, sot similar with only one of the words. As conclusion, I think it is answer A.

upvoted 3 times

...