ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam
Go to Exam

Exam 70-761 topic 1 question 69 discussion

Actual exam question from Microsoft's 70-761
Question #: 69
Topic #: 1
[All 70-761 Questions]

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

You need to create a query that returns each complaint, the names of the employees handling the complaint, and the notes on each interaction. The Complaint field must be displayed first, followed by the employee's name and the notes. Complaints must be returned even if no interaction has occurred.
Construct the query using the following guidelines:
✑ Use two-part column names.
✑ Use one-part table names.
✑ Use the first letter of the table name as its alias.
✑ Do not 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.

1 SELECT c.Complaint, e.Name, i.Notes
2 FROM Complaints c
3 JOIN __________________
4 JOIN __________________

Show Suggested Answer Hide Answer
Suggested Answer: Please see explanation
1 SELECT c.Complaint, e.Name, i.Notes
2 FROM Complaints c
3 JOIN Interactions i ON c.ComplaintID = i.ComplaintID
4 JOIN Employees e ON i.EmployeeID = E.EmployeeID
by Dieter at Aug. 19, 2019, 2:10 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
supermario
Highly Voted 5 years, 1 month ago
SELECT c.Complaint, e.Name, i.Notes FROM Complaints c LEFT OUTER JOIN Interactions i ON c.ComplaintID = i.ComplaintID LEFT OUTER JOIN Employees e ON i.EmployeeID = e.EmployeeID
upvoted 15 times
...
Dieter
Highly Voted 5 years, 8 months ago
first JOIN => LEFT OUTER JOIN ("show all complaints...") second JOIN => INNER JOIN
upvoted 14 times
avramov
5 years, 5 months ago
no, if you use first left join, then inner join, you will not fulfill the requirements. you need to use two left joins or one left join with a subquery
upvoted 21 times
tz_123
4 years, 10 months ago
avramov is correct, because you can only use two INNER JOINs if *all* ComplaintIDs in Complaints table have a corresponding entry in the Interactions table, and all those corresponding entries in the Interactions table have a corresponding entry in the Employees table.
upvoted 4 times
...
...
Andy7622
4 years, 4 months ago
Using inner join you exclude complaints that have assigned employees
upvoted 1 times
Andy7622
4 years, 4 months ago
Sorry I meant, inner join excludes complaints that have no assigned employees.
upvoted 1 times
...
...
mch185
4 years, 3 months ago
if we see 'all' keyword we should use left ?! are you serious? we don't memorize here we use logic . join keys are not null so we use inner join. period
upvoted 1 times
...
...
Billybob0604
Most Recent 4 years, 3 months ago
the thing about referential integrity is that a foreign key cannot exist without a corresponding primary key in the table it references. The keys have not been checked 'Allow null' so this means for every compliantid and employeeid, no matter FK or PK there is a value. So no need to left join.
upvoted 1 times
...
mch185
4 years, 3 months ago
FKs are not null . so inner join is the right answer. There is no complaint who has no employee
upvoted 1 times
...
Vermonster
4 years, 3 months ago
2 left joins OR Interactions INNER JOIN Employees RIGHT JOIN Complaints both ensure that we get all complaints.
upvoted 2 times
...
Oooo
4 years, 7 months ago
Tested both left joins.
upvoted 1 times
...
Kiruu23
4 years, 7 months ago
it should not be join coz only join means INNER JOIN so, if we use INNER JOIN it will only return the data which have corresponding entries in all 3 tables but we have to return all the complaints so, there might be the case when we have data in complaint table but not in interaction so, we have to return that data also. we have to use LEFT JOIN for this. So, Correct Join would be LEFT JOIN
upvoted 1 times
...
sadkiss
4 years, 11 months ago
If I read the question correctly, this is a database to handle complaints. Sometimes, complains are entered with no one yet assigned to handle a complaint. So, complains could exist with no assigned employees or interactions. The question states that we need to show all complaints regardless if someone is assigned to handle them or not. Interactions are entered by employees, so if there is no employee assigned, there will be no interactions. What if there was no employee assigned to handle the complaints yet? With INNER JOIN (JOIN alone is an INNER JOIN) the rows with no employee assigned or interactions wont show if I understand the logic correctly! Thus, I am actually tempted to go with LEFT JOIN.
upvoted 2 times
sadkiss
4 years, 11 months ago
LEFT JOIN for both joins the first and the second.
upvoted 1 times
...
...
Olayemi87
4 years, 12 months ago
The given answer is correct, you could make it a little more explicit by adding inner join like below. Select c.complaint, e.name, i.notes From Complaints C Inner Join Interactions i on c.complaintID = i.complaintID Inner Join employees e on i.employeeID = e.employeeID The reason you do not need to put a left outer join anywhere is because the joining colums do not allow nulls so there is no chance of leaving any data out if you use an inner join, therefore the given answer is correct although using a left o uter join will still give the same result as an inner join in this scenario.
upvoted 3 times
tz_123
4 years, 10 months ago
This is incorrect. It doesn't matter whether the joining columns allow NULLs. You can only use two INNER JOINs if *all* ComplaintIDs in Complaints table have a corresponding entry in the Interactions table, and all those corresponding entries in the Interactions table have a corresponding entry in the Employees table.
upvoted 1 times
...
BabyBee
4 years, 5 months ago
Olayemi87, your statement is incorrect. Due to the Complains Table do not have an Employee ID column on it, so there will be complains without Employees assigned to it.
upvoted 1 times
...
...
moehijawe
5 years, 3 months ago
select c.complaint,e.name,i.notes from compliants as c left outer join interactions as i on(c.compliantId = i.complaintId) left out join employees as e on (i.employeeid = e.employeeId)
upvoted 10 times
tz_123
4 years, 10 months ago
This is correct because you can only use two INNER JOINs if *all* ComplaintIDs in Complaints table have a corresponding entry in the Interactions table, and all those corresponding entries in the Interactions table have a corresponding entry in the Employees table.
upvoted 1 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago