JoinsChap4

Write a SELECT statement that returns all columns from the Vendors table inner-joined with the Invoices table.

SELECT * FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID;

Write a SELECT statement that returns three columns: VendorID From the Vendors table VendorName From the Vendors table Name A concatenation of VendorContactFName and VendorContactLName, with a space in between The result set should have one row for each vendor whose contact has the same first name as another vendor's contact. Sort the final result set by Name. Hint: Use a self-join

SELECT DISTINCT v1.VendorID, v1.VendorName, v1.VendorContactFName + ' ' + v1.VendorContactLName AS Name FROM Vendors AS v1 JOIN Vendors AS v2 ON (v1.VendorID <> v2.VendorID) AND (v1.VendorContactFName = v2.VendorContactFName) ORDER BY Name;

Write a SELECT statement that returns two columns from the GLAccounts table: AccountNo and AccountDescription. The result set should have one row for each account number that has never been used. Sort the final result set by AccountNo. Hint: Use an outer join to the InvoiceLineItems table.

SELECT GLAccounts.AccountNo, AccountDescription FROM GLAccounts LEFT JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo WHERE InvoiceLineItems.AccountNo IS NULL ORDER BY GLAccounts.AccountNo;

Write a SELECT statement that returns five columns from three tables, all using column aliases: Vendor VendorName column Date InvoiceDate column Number InvoiceNumber column # InvoiceSequence column LineItem InvoiceLineItemAmount column Assign the following correlation names to the tables: v Vendors table i Invoices table li InvoicesLineItems table Sort the final result set by Vendor, Date, Number, and #.

SELECT VendorName AS Vendor, InvoiceDate AS Date, InvoiceNumber AS Number, InvoiceSequence AS [#], InvoiceLineItemAmount AS LineItem FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID JOIN InvoiceLineItems AS li ON i.InvoiceID = li.InvoiceID ORDER BY Vendor, Date, Number, [#];

Write a SELECT statement that returns three columns: VendorName From the Vendors table DefaultAccountNo From the Vendors table AccountDescription From the GLAccounts table The result set should have one row for each vendor, with the account number and account description for that vendor's default account number. Sort the result set by AccountDescription, then by VendorName.

SELECT VendorName, DefaultAccountNo, AccountDescription FROM Vendors JOIN GLAccounts ON Vendors.DefaultAccountNo = GLAccounts.AccountNo ORDER BY AccountDescription, VendorName;

Write a SELECT statement that returns four columns: VendorName From the Vendors table InvoiceNumber From the Invoices table InvoiceDate From the Invoices table Balance InvoiceTotal minus the sum of PaymentTotal and CreditTotal The result set should have one row for each invoice with a non-zero balance. Sort the result set by VendorName in ascending order.

SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal - PaymentTotal - CreditTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName;

Generate the same result set described in exercise 2, but with the implicit join syntax.

SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal - PaymentTotal - CreditTotal AS Balance FROM Vendors, Invoices WHERE Vendors.VendorID = Invoices.VendorID AND InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName;

Use the UNION operator to generate a result set consisting of two columns from the Vendors table: VendorName and VendorState. If the vendor is in California, the VendorState value should be "CA"; otherwise, the VendorState value should be "Outside CA". Sort the final result set by VendorName

SELECT VendorName, VendorState FROM Vendors WHERE VendorState = 'CA' UNION SELECT VendorName, 'Outside CA' FROM Vendors WHERE VendorState <> 'CA' ORDER BY VendorName;