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 __________________
Answer: See the explanation
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
it s a tricky one the case says Complaints must be returned even if no interaction has occurred so it sounds like a left join is needed somewhere…
12
1
The answer is wrong!
If you just write “JOIN” it is an inner join. Hence Complaints without interaction are filtered out.
Correct statment would be
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
51
0
https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join
0
0
Because not INNER JOIN?
0
0
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
1
11
¿Why not left join instead of left outer join?
2
0
that is exactly correct Peter and you need 2 Left joins for that
0
0
I have latest dumps mslatestdumps100@gmail.com for 70-761, 70-762, 70-764, 70-767
0
4
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
12
0
verified.
0
0
I agree, the question would not specify no implicit joins (join vs inner join), if that simple clause would make it ambiguous. LEFT OUTER JOIN papa!
0
0
Hi Guys,
I have passed the exam this week. I had almost 100% and that would not be possible without studying hard but also without all the efforts of all the people behind this site, all the people that contribute to spot errors on questions, all the people that tries to give good answers and help other. My victory is also your victory.
Thank you very much,
overkill
14
0
congratulations!!!
İ wrote you about my exam. Please send me your contact/email, I have some questions.
2
0
Thank you ssrs919! I’ve Gathered feedback about my exam, hope it helps you and all the candidates for the exam.
Cheers
0
0
Hi there Guys,
Here’s the feedback of the exam I took, last month.
Hope it helps.
Cheers,
overkill
>>STUDY:
MeasureUP
-Excellent to study and understand, presents some errors in one or another question. You have no questions with queries from scratch
BriefMeNow
-Eventually 30-40% of exam questions
Miscellaneous
-I often went to the microsoft website, redgate and google in general to improve my understanding.
BriefmeNow was the last phase of the study. It was positive because the time for the Exam is short and I did not have to reread the questions N times to realize what was being asked. Which can sometimes be tricky, and other times may simply be a very extensive question.
>>FORMAT:
47 questions
I Got drag and drop issues, questions of multiple choice, questions to write queries.
Time is short, I was in the middle of a question that I knew how to do but didn’t have time to complete.
The first questions were case studies that took me 7 to 15 questions maybe.
>>COVERED TOPICS/SUB-TOPICS:
Questions to distinguish UNION from UNION ALL
Questions where it was important to realize the difference between EXCEPT, INTERSECT, and Joins
Several questions with LEFT JOINS/ RIGHT / FULL OUTER JOIN / CROSS JOIN (Questions for each of these joins, it is convenient to understand the differences well)
At least one drag and drop question for the expression CASE
There were 1 or 2 questions about CTEs, where at least one was to set up a recursive scenario
There were at least 1 question where we had to distinguish deterministic functions from non-deterministic functions
There were 1 or 2 questions where we had to understand Indexed Views and its limitations, some of which related to non-deterministic functions
There were a question for writing from scratch a query with functions
There were questions that encompass understanding of CROSS APPLY and OUTER APPLY
There were questions where it was important to understand the difference between DenseRank and Rank (maybe 1 question)
There was at least 1 question that dealt with OFFSET FETCH
There were 1 or 2 questions about temporal tables, where at least one of them was important to note the difference of the various expressions AS OF, BETWEEN, CONTAINED and FROM
There were 1 or 2 questions where it was important to know the difference between ISNULL and COALESCE
There were a question where it was important to realize the difference between IIF and CHOOSE
There were 2 or 3 questions about error handling
There was 1 question where you had to create a table from scratch based on the requirements
There was at least 1 question about JSON where the purpose was to distinguish JSON AUTO from JSON PATH
There was at least 1 question about PIVOT with dropdowns
There was at least one question about XML where it was important to realize the differences from an attribute centric approach to an element centric approach.
There was a question to build a stored procedure through drag and drop
There was at least one question where you had to set up a function
NO Merge Join questions (Which doesn’t mean that you can’t catch one)
There was a question where you had to understand how many times an index would be read, for a certain query (I found this one particularly out of scope, but I answered it too)
3
0
I was informed by a friend that you can get questions that are out of scope, those questions meant to assess the public that is performing the exam, and improve the exam.
https://www.microsoft.com/en-us/learning/certification-exam-policies.aspx
On “scoring and results” >> “how are questions scored? …”
“Some questions on the exam may not be included in the calculation of your score. To gather data to update and improve the quality of each exam, we present new content to candidates without counting the results toward their score. However, as soon as we have the necessary data to evaluate the quality of the question, items that meet Microsoft’s psychometric standards will be scored. Microsoft will not inform candidates which questions are unscored; as a result, you should answer every question as if it will be scored.”
0
0