Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

JoinsChap4

memorize.ai (lvl 286)

(0)

8 cards

()

Section 1

Preview this deck

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.

Front

1 / 8

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

4 years ago

Date created

Mar 1, 2020

Cards (8)

Section 1

(8 cards)

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.

Front

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

Back

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 #.

Front

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, [#];

Back

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

Front

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

Back

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

Front

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;

Back

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

Front

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;

Back

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.

Front

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;

Back

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

Front

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

Back

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.

Front

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

Back