You work as the database administrator at an investment company named Domain.com. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that contains a large database named CK_Sales. The Testing.com personnel in the Accounts department require the ability to view customer order information. You need to create a query that will allow Accounts department personnel the ability to view CustomerID values for all customers as well as: the orders that are placed by individual customers, and customers that has not yet placed any orders.
There is a foreign constraint on the CustomerID column of the CK_Sales.[Order Head] table that references the CustomerID column in the CK_Sales.Customer table. You want to create a single query that returns the CustomerID and OrderID.
What query should you use?
A.
SELECT c.CustomerID, o.OrderID
FROM CK_Sales.Customer c LEFT OUTER JOIN CK_Sales.[Order Head] c ON c.CustomerID = o.CustomerID
B.
SELECT o.CustomerID, o.OrderID
FROM CK_Sales.Customer c LEFT OUTER JOIN CK_Sales.[Order Head] c ON c.CustomerID = o.CustomerID
C.
SELECT c.CustomerID, o.OrderID
FROM CK_Sales.Customer c RIGHT OUTER JOIN CK_Sales.[Order Head] c ON c.CustomerID = o.CustomerID
D.
SELECT c.CustomerID, o.OrderID
FROM CK_Sales.Customer c JOIN CK_Sales.[Order Head] c ON c.CustomerID = o.CustomerID
Explanation:
This option shows a LEFT OUTER JOIN. All rows are thus returned from the left table, CK_Sales.Customer, whether there are matching rows in the right table or not. This means that all CustomerID values from CK_Sales.Customer will be returned. In the event of the customer having an order, those OrderID values are also returned.
Incorrect Answers:
B: Although this option represents a LEFT OUTER JOIN, the result will only return values from the CK_Sales.[Order Head] table because these are the only values that are specified in the SELECT clause.
C: This option shows that it is a RIGHT OUTER JOIN. This will result in all rows in CK_Sales.[Order Head] being returned regardless of whether there are matching rows in CK_Sales.Customer or not. Due to the foreign key relationship between CK_Sales.[Order Head] and CK_Sales.Customer, there shouldn’t be CustomerID values in Sales.[Order Head] that does not appear in CK_Sales.Customer.
D: Although not explicitly identified, this option represents an INNER JOIN. Only rows meeting the qualifying condition are returned. Thus if a CustomerID does not appear in the CK_Sales.[OrderHead] table, noting will be returned for that particular customer.