Exercises 4

1. write a select statement that returns all columns from the vendors table inner-joined with all columns from the Invoices table.

2. Write a SELECT that returns four columns:
Vendor_name vendor_name from the Vendors table
Invoice_number Invoice_number from the Invoices table
Invoice_date invoice_date from the Invoices table
Balance_due invoice_total minus the sum of payment_total minus credit_total from the invoices table
The result set should have one row for each invoice with a non-zero balance. Sort the result set by vendor_name in ascending order.

3. Write a SELECT statement that returns three columns:

vendor_name vendor_name from the Vendors table
default_account default_account_number from the Vendors table
description account_description from the General_Ledger_Accounts table
The result set should have one row for each vendor, and it should be sorted by account_description and then by vendor_name.

4. Write a SELECT statement that returns five columns from three tables:

vendor_name vendor_name from the Vendors table
invoice_date invoice_date from the Invoices table
invoice_number invoice_number from the Invoices table
li_sequence invoice_sequence from the Invoice_Line_Items table
li_amount line_item_amt from the Invoice_Line_Items table

Use these aliases for the tables: Ven for the Vendors table, Inv for the Invoices table, and LI for the Invoice_Line_Items table. Also, sort the final result set by vendor_name, invoice_date, invoice_number, and invoice_sequence.

5. Write a SELECT statement that returns three columns:
vendor_id vendor_id from the Vendors table
vendor_name vendor_name from the Vendors table
contact_name A concatenation of vendor_contact_first_name and vendor_contact_last_name with a space in between
The result set should have one row for each vendor whose contact has the same last name as another vendor’s contact, and it should be sorted by vendor_contact_last_name. Hint: Use a self-join.

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

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