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.
Answer: See the explanation
Explanation:
SELECT Complaints.ComlaintID, Persons.Name
FROM Persons
JOIN Contacts
ON Persons.PersonID=Contacts.PersonID
JOIN Complaints
ON Contacts.ComplaintID=Complaints.ComplaintID
https://technet.microsoft.com/en-us/library/ms190014(v=sql.105).aspx
If the question says ‘all’ complaints, should we not do a left outer join starting from Complaints? Since sometimes there will not be a person assigned, and those complaints would get left out by an inner join.
5
0
Answer is wrong all complaint and your first join is Person to contact ? and yes it says all Complaints so should be left joinSSS
2
2
Answer is correct -because of the following statement all complaints from the Complaints table, and the name of the personhandling the complaints if a person is assigned
1
1
Answer is wrong!
As we have to show all complaints (“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 table complaints has to be the base table. And, in case a person is assigned, also the persons name has to be shown but the persons name may be NULL. Hence the statement needs to look like:
SELECT Complaints.ComplaintID, Persons.Name
FROM Complaints
LEFT OUTER JOIN Contacts ON Complaints.ComplaintID = Contacts.ComplaintID
LEFT OUTER JOIN Persons ON Contacts.PersonID = Persons.PersonID
37
0
agree
7
0
agree
6
0
Agree.
4
0
It’s possible that Complains is not the base table with a RIGHT JOIN:
SELECT Complaints.ComplaintID, Persons.Name
FROM Persons
INNER JOIN Contacts ON Contacts.PersonID = Persons.PersonID
RIGHT OUTER JOIN Complaints ON Complaints.ComplaintID = Contacts.ComplaintID
2
0