Question

Class-In Assignment 3:

Chapters 4&5 How to retrieve data from two or more tables Exercises

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

Chapter 5 How to code summary queries Exercises

1. Write a SELECT statement that returns one row for each vendor that contains these columns from the Invoices table: The vendor_id column The sum of the invoice_total column The result set should be sorted by vendor_id.

2. Write a SELECT statement that returns one row for each vendor that contains these columns: The vendor_name column from the Vendors table The sum of the payment_total column in the Invoices table. The result set should be sorted in descending sequence by the payment total sum for each vendor.

3. Write a SELECT statement that returns one row for each vendor that contains three columns: The vendor_name column from the Vendors table The count of the invoices for each vendor in the Invoices table The sum of the invoice_total column for each vendor in the Invoices table Sort the result set so the vendor with the most invoices appears first.

4. Write a SELECT statement that returns one row for each general ledger acQcount number that contains three columns: The account_description column from the General_Ledger_Accounts table The count of the entries in the Invoice_Line_Items table that have the same account_number The sum of the line item amounts in the Invoice_Line_Items table that have the same account-number Filter the result set to include only those rows with a count greater than 1; group the result set by account description; and sort the result set in descending sequence by the sum of the line item amounts.

5. Modify the solution to exercise 4 to filter for invoices dated in the second quarter of 2008 (April 1, 2008 to June 30, 2008). Hint: Join to the Invoices table to code a search condition based on invoice_date.

6. Write a SELECT statement that answers this question: What is the total amount invoiced for each general ledger account number? Use the ROLLUP operator to include a row that gives the grand total. Hint: Use the line_item_amt column of the Invoice_Line_Items table.

7. Write a SELECT statement that answers this question: Which vendors are being paid from more than one account? Return two columns: the vendor name and the total number of accounts that apply to that vendor’s invoices. Hint: Use the DISTINCT keyword to count the account_number column in the Invoice_Line_Items table.

Answer 1

4.

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

2. 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;

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

4. 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;

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

6. 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;

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

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

========================================================

/*1 - select statement that returns one row for each vendor in the invoices table that contains these columns
#the vendor_id column from the vendors table
#the sum of the invoice_total columns in the invoices table for that vendor */

select distinct v.vendor_id, i.invoice_total
from vendors v, invoices i
where v.vendor_id = i.vendor_id
group by i.vendor_id;

/*2 - select statement that returns one row for each vendor that contains these columns
# the vendor_name column from the vendors table
# the sum of the payment_total columns in the invoices table for that vendor
#sort the result set in descending sequence by the payment total sum for each vendor */

select v.vendor_name, sum(i.payment_total)
from vendors v, invoices i
where v.vendor_id = i.vendor_id
group by v.vendor_id
order by sum(i.payment_total) desc;

/*3 - select statement that returns one row for each vendor that contains three columns:
# vendor_name column from the vendors table
# count of the invoices in the invoices table for each vendor
# sum of the invoice_total columns in the invoices table for each vendor
#sort the result set so teh vendor with the most invoices appears first */

select vendor_name as "vendor name", count(i.vendor_id) as "num of invoices",
        sum(invoice_total) as "invoice total"
from vendors v, invoices i
where v.vendor_id = i.vendor_id
group by vendor_name
order by count(i.vendor_id) desc;

/*4 - select statement that returns one row for each general ledger account number and contains
# account_description from the gen ledger table
# count of items in the line items table that have the same account_number
# sum of the line_item_amount columns in the invoice line items table w/ same account number
#return rows with more than 1 line item
#group by account description
#sort by sum of line item amounts in desc sequence */

select gl.account_description as "account description", count(li.account_number) as "num of line items", 
        sum(li.line_item_amount) as "total line items"
from general_ledger_accounts gl, invoice_line_items li
where gl.account_number = li.account_number
group by gl.account_description
having count(li.account_number) > 1
order by sum(li.line_item_amount) desc;

-- modified solution to 4 that returns only invoices dated in the 2nd quarter of 2011
select gl.account_description as "account description", count(li.account_number) as "num of line items", 
        sum(li.line_item_amount) as "total line items", i.invoice_date as "invoice date"
from general_ledger_accounts gl, invoice_line_items li, invoices i
where gl.account_number = li.account_number
        and i.invoice_id = li.invoice_id
group by gl.account_description
having count(li.account_number) > 1
        and i.invoice_date >= '2011-04-01'
        and i.invoice_date <= '2011-06-30'
order by sum(li.line_item_amount) desc;

/*6 - question: what is the total amount invoiced for each general ledger account number? return these columns:
# account number from the invoice_line_items table
# sum of the line item amounts */

select li.account_number as "account number", sum(li.line_item_amount) as "line item total"
from invoice_line_items li
group by li.account_number
with rollup;

/*7 - question: which vendors are being paid from more than one account.
# returns: vendor name, count of distinct general ledger accounts */

select distinct v.vendor_name as "vendor name", count( distinct gl.account_number) as "num of distinct general ledger accounts"
from general_ledger_accounts gl
        join invoice_line_items li
                on gl.account_number = li.account_number
        join invoices i
                on li.invoice_id = i.invoice_id
        join vendors v                                          
                on i.vendor_id = v.vendor_id
group by v.vendor_id
        having count(distinct gl.account_number) > 1;