How to retrieve data from two or more tables

Exercise 1

1. Write a SELECT statement that returns all columns from the Vendors table inner-joined with all columns from the Invoices table. This should return 114 rows. Hint: You can use an asterisk (*) to select the columns from both tables.

Solution 1

---

Exercise 2

1. Write a SELECT statement that returns these four columns:

   • vendor_name (The vendor_name column from the Vendors table)
   • invoice_number (The invoice_number column from the Invoices table)
   • invoice_date (The invoice_date column from the Invoices table)
   • balance_due (The invoice_total column minus the payment_total and credit_total columns from the Invoices table)

2. Use these aliases for the tables: v for Vendors and i for Invoices.

3. Return one row for each invoice with a non-zero balance. This should return 11 rows.

4. Sort the result set by vendor_name in ascending order.

Solution 2

---

Exercise 3

1. Write a SELECT statement that returns these three columns:

   • vendor_name (The vendor_name column from the Vendors table)
   • default_account (The default_account_number column from the Vendors table)
   • description (The account_description column from the General_Ledger_Accounts table)

2. Return one row for each vendor. This should return 122 rows.

3. Sort the result set by account_description and then by vendor_name.

Solution 3

---

Exercise 4

1. Write a SELECT statement that returns these five columns:

   • vendor_name (The vendor_name column from the Vendors table)
   • invoice_date (The invoice_date column from the Invoices table)
   • invoice_number (The invoice_number column from the Invoices table)
   • li_sequence (The invoice_sequence column from the Invoice_Line_Items table)
   • li_amount (The line_item_amount column from the Invoice_Line_Items table)

2. Use aliases for the tables. This should return 118 rows.

3. Sort the final result set by vendor_name, invoice_date, invoice_number, and invoice_sequence.

Solution 4

---

Exercise 5

1. Write a SELECT statement that returns three columns:

   • vendor_id (The vendor_id column from the Vendors table)
   • vendor_name (The vendor_name column from the Vendors table)
   • contact_name (A concatenation of the vendor_contact_first_name and vendor_contact_last_name columns with a space between)

2. Return one row for each vendor whose contact has the same last name as another vendor’s contact. This should return 2 rows.

3. Hint: Use a self-join to check that the vendor_id columns aren’t equal but the vendor_contact_last_name columns are equal.

4. Sort the result set by vendor_contact_last_name.

Solution 5

---

Exercise 6

1. Write a SELECT statement that returns these three columns:

   • account_number (The account_number column from the General_Ledger_Accounts table)
   • account_description (The account_description column from the General_Ledger_Accounts table)
   • invoice_id (The invoice_id column from the Invoice_Line_Items table)

2. Return one row for each account number that has never been used. This should return 54 rows.

3. Hint: Use an outer join and only return rows where the invoiceid column contains a null value.

4. Remove the invoice_id column from the SELECT clause.

5. Sort the final result set by the account_number column.

Solution 6

---

Exercise 7

1. Use the UNION operator to generate a result set consisting of two columns from the Vendors table:

   • vendor_name
   • vendor_state

2. If the vendor is in California, the vendor_state value should be “CA”; otherwise, the vendor_state value should be “Outside CA.”

3. Sort the final result set by vendor_name.

Solution 7

---