Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 27 discussion

Actual exam question from Microsoft's 70-761

Question #: 27
Topic #: 1

SIMULATION -
You have a database that contains the following tables.

You need to create a query that lists all complaints from the Complaints table, and the name of the person handling the complaints if a person is assigned. The
ComplaintID must be displayed first, followed by the person name.
Construct the query using the following guidelines:
✑ Use two-part column names.
✑ Use one-part table names.
✑ Do not use aliases for column names or table names.
✑ Do not use Transact-SQL functions.
✑ Do not use implicit joins.
✑ Do not surround object names with square brackets.
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.

Show Suggested Answer

Suggested Answer: Please see explanation

References:
https://technet.microsoft.com/en-us/library/ms190014(v=sql.105).aspx

by Dieter at Aug. 19, 2019, 11:48 a.m.

Comments

Submit Cancel

Bartek

Highly Voted 5 years, 9 months ago

EXAMPLE CODE AND TWO WAYS OF SOLUTION CREATE TABLE #PERSONS ( PERSONID INT, NAME VARCHAR(15) ) CREATE TABLE #CONTACTS ( PERSONID INT, COMPLAINTID INT ) CREATE TABLE #COMPLAINTS ( COMPLAINTID INT, COMPLAINT VARCHAR(10) ) INSERT INTO #PERSONS (PERSONID, NAME) VALUES (1, 'BARTEK'), (2,'KASIA'), (3,'TOMEK'),(4,'MARTA') INSERT INTO #CONTACTS (PERSONID, COMPLAINTID) VALUES (1,1),(2,2),(3,3),(4,4) INSERT INTO #COMPLAINTS (COMPLAINTID, COMPLAINT) VALUES (1,'SKARGA'),(2,'SKARGA'),(3,'SKARGA'),(3,'SKARGA'),(4,'SKARGA'),(5,'SKARGA'),(6,'SKARGA') SELECT A.COMPLAINTID,C.NAME FROM #COMPLAINTS AS A LEFT JOIN #CONTACTS AS B ON A.COMPLAINTID = B.COMPLAINTID LEFT JOIN #PERSONS AS C ON C.PERSONID = B.PERSONID SELECT C.COMPLAINTID,A.NAME FROM #PERSONS AS A JOIN #CONTACTS AS B ON A.PERSONID = B.PERSONID RIGHT JOIN #COMPLAINTS AS C ON B.COMPLAINTID = C.COMPLAINTID

upvoted 11 times

...

itdoesntmatter

Highly Voted 5 years, 9 months ago

I do not see any ComplaintID column in Contacts table.. So the only way to link Contact table to Complaints table is Complaint column

upvoted 11 times

...

Vermonster

Most Recent 4 years, 6 months ago

Joins are processed left-to-right so you need to maintain LEFT JOIN on the second join or you will lose the records you preserved on the first LEFT JOIN: SELECT COMPLAINT.COMPLAINTID,PERSONS.NAME FROM COMPLAINTS LEFT JOIN CONTACTS ON COMPLAINTS.COMPLAINTID = CONTACTS.COMPLAINT LEFT JOIN PERSONS ON CONTACTS.PERSONID = PERSONS.PERSONID

upvoted 1 times

...

Skc20

4 years, 6 months ago

This will give you the exact answer. Select Complaints.ComplaintID, Shivani.Persons.name from Complaints Left join Shivani.Contacts on (Complaints.ComplaintID = Contacts.ComplaintID) INNER join Persons on (Shivani.Contacts.PersonID = Persons.PersonID);

upvoted 1 times

...

geekeek1

4 years, 6 months ago

so what is the correct join to use? are the LEFT JOIN and INNER JOIN?

upvoted 1 times

...

kimalto452

4 years, 6 months ago

The joins are providen... you dont need invent the wheels with "its left join" "its right joins" bla bla...

upvoted 1 times

lh2607

4 years, 6 months ago

They're not provided. In the exam you will be expected to state the join.

upvoted 1 times

...

Andy7622

4 years, 7 months ago

should be inner join between person and contacts and right outer join between contacts and complaints

upvoted 1 times

...

Andy7622

4 years, 7 months ago

the question is : if there personID when there is no name? If yes we don't need outer joins, but if there is no personID then we need left outer join between Person and Contacts tables. Also there must be ComplaintID instead of Complaint in Contacts. I think it's just a mistake , besides the data type is INT for 'Complaint' so it's definitely 'ComplaintID

upvoted 1 times

...

KosteK

4 years, 7 months ago

No needed RIGHT/LEFT JOIN The table Contacts not allowing to put NULL values in both id (personid, complaintid) so JOIN (INNER JOIN) is ok for that. The answer is correct

upvoted 3 times

...

Aghie

4 years, 10 months ago

The Complaint field under Contacts has 'int' datatype. so its the actual link to the Complaints.ComplaintID. SELECT Complaints.ComplaintID, Persons.Name FROM Complaints LEFT JOIN Contacts ON Contacts.Complaint = Complaints.ComplaintID LEFT JOIN Persons ON Persons.PersonID = Contacts.PersonID

upvoted 1 times

...

AI_Cheong

4 years, 11 months ago

Isn't it the "Join" are already provided? we just need to fill in the rest right?

upvoted 1 times

julie2020

4 years, 11 months ago

1 SELECT Complaints.ComplaintID, Persons.Name 2 FROM Complaints 4 LEFT OUTER JOIN Contacts ON Complaints.ComplaintID = Contacts.ComplaintID 5 LEFT OUTER JOIN Persons ON Contacts.PersonID = Persons.PersonID

upvoted 2 times

...

Robintang0924

5 years, 5 months ago

This question seems a bit ambiguous especially for PK/FK naming convention. 1. Assume we have 1 to M relationship between Complaints and Contacts based on Complaints.ComplaintId(PK) and Contacts.Complaint(FK). 2. Assume we have 1 to M relationship between Persons and Contacts based on Persons.PersonID(PK) and Contacts.PersonID(FK). 3. You need to list all complaints from the Complaints table, and the name of the person handling the complaints if a person is assigned. Based on above assumption, below left join SQL start from Complaints table should work -- it will list every ComplaintID in Complaints table but correlated Persons name might be null: select Complaints.ComplaintID, Persons.name from Complaints left join Contacts on (Complaints.ComplaintID = Contacts.Complaint) left join Persons on (Contacts.PersonID = Persons.PersonID);

upvoted 7 times

Anette

5 years, 1 month ago

or INNER JOIN second join

upvoted 1 times

vermeilyn

5 years, 1 month ago

If you use INNER JOIN after an OUTER JOIN, it will nullifies the outer part of the join and convert it into an INNER JOIN.

upvoted 4 times

...

MarcusJB

5 years, 1 month ago

No, you are wrong Anette, sorry. It says: 1. "create a query that lists *all* complaints from the Complaints table". 2. "and ("if available") the name of the person handling the complaints if a person is assigned" (2) doesn't mean to filter the result from (1), but showing a NULL value when having no person assigned. A classical LEFT/RIGHT OUTER JOIN. Otherwise (1) could not be fulfilled.

upvoted 2 times

...

moehijawe

5 years, 5 months ago

select Complaints.ComplaintID, Persons.name from Complaints left join Contacts on (Complaints.ComplaintID = Contacts.Complaint) left join Persons on (Contacts.PersonID = Persons.PersonID)

upvoted 10 times

Anette

5 years, 1 month ago

the second join must be Inner join because it says "the name of the person handling the complaints if a person is assigned" so, only if a person is assigned, with left join it takes even if a person is not assigned

upvoted 1 times

Braindripper

4 years, 9 months ago

If the first left join matches lines means that a personid and complaint should be populated fields as are "not nulls" - so lets inner or left same records will showed...

upvoted 1 times

...

tzaganczyk

4 years, 9 months ago

"All compliants" should be listed even though person is not assigned, so it should remain LEFT JOIN in this case

upvoted 1 times

...

mlourinho

5 years, 7 months ago

The exercise says "All Complaints and the person if assigned". Meaning the first table should be Complaints and the connection to the others must be LEFT JOIN

upvoted 6 times

Anette

5 years, 1 month ago

yes and the second JOIN must be inner join

upvoted 2 times

...

fabzo

5 years, 8 months ago

Would this also be correct: SELECT Complaints.ComplaintID, Persons.Name From Complaints INNER JOIN Contacts ON Contacts.Complaints=ComplaintsID INNER JOIN Persons ON Persons.PersonID=Contacts.PersonID ORDER BY Complaints.ComplaintID, Persons.Name

upvoted 10 times

...

itdoesntmatter

5 years, 9 months ago

But i would rather say that this is a question error and Complaint column should be ComplaintID

upvoted 3 times

...

Dieter

5 years, 10 months ago

Update: First JOIN => INNER JOIN Second JOIN => RIGHT OUTER JOIN (since all complaints should be displayed)

upvoted 8 times

...

Load full discussion...