Question

Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?

A) SELECT Referrer.FirstName, Referrer.LastName, Referred.CustomerID FROM Customers AS Referrer JOIN Referrals ON Referrer.CustomerID = Referrals.ReferrerID JOIN Customers AS Referred ON Referred.CustomerID = Referrals.ReferredID;
B) SELECT FirstName, LastName, CustomerID FROM Customers WHERE ReferredBy IS NOT NULL;
C) SELECT Referrer.FirstName, Referrer.LastName, Referred.CustomerID FROM Customers AS Referrer LEFT JOIN Referrals ON Referrer.CustomerID = Referrals.ReferrerID LEFT JOIN Customers AS Referred ON Referred.CustomerID = Referrals.ReferredID;
D) SELECT FirstName, LastName, CustomerID FROM Customers WHERE ReferrerID IS NOT NULL;

Answer 1

The correct SQL query is option A, which uses table aliases and JOIN operations to fetch the first and last names of referrers alongside the referred customers' numbers.

Step-by-step explanation:

Among the provided queries, the correct query to display the first and last name of the individual who referred another customer, along with the referred customer's number (Customer#), is:

A) SELECT Referrer.FirstName, Referrer.LastName, Referred.CustomerID
FROM Customers AS Referrer
JOIN Referrals ON Referrer.CustomerID = Referrals.ReferrerID
JOIN Customers AS Referred ON Referred.CustomerID = Referrals.ReferredID;

This SQL query uses table aliases and the JOIN operation to combine rows from the Customers table as well as the Referrals table. The ON clause specifies the condition that ties the referrer to the referred customer. The SELECT statement retrieves the names from the Referrer alias and the CustomerID from the Referred alias.