CIS336
Week 2 Lab
Correct table joins and join fields:
Vendors to Invoices (on VendorID)
Invoices to InvoiceLineItems (on InvoiceID)
InvoiceLineItems to GLAccounts (on AccountNo)
Correct table joins and join fields:
1.Write a select statement that returns 2 columns from the Invoices table: InvoiceCount and AvgInvAmount. InvoiceCount is the count of the number of invoices and AvgInvAmount is the average of the InvoiceTotal column.
2.Write a select statement that returns 2 columns from the Invoices table: VendorID and BalanceDue. BalanceDue is the sum of invoices with a balance due. Filter the results to only return rows where a balance is due. Group the results by VendorID.
3.Write a select statement that returns 3 columns from the Invoices table: VendorID, InvoiceCount, and TotalPaid. InvoiceCount is the count of the number of invoices, and TotalPaid is the sum of the PaymentTotal and CreditTotal columns added together. Filter the results to only return rows where the balance due is equal to zero. Group the results by VendorID.
4.Write a select statement that returns 6 columns: VendorName, InvCount, LargestInv, SmallestInv, AverageInv and TotalInv:
InvCount:Count of the number of invoices
LargestInv: Highest value in the InvoiceTotal column
SmallestInv:Lowest value in the InvoiceTotal column
AverageInv:Average value in the InvoiceTotal column
TotalInv: Sum of the InvoiceTotal column
Group the results by VendorName.
5.Write a select statement that returns 4 columns: VendorState, VendorCity, TotalInvoices, and LargestInvoice:
TotalInvoices: Count of the number of invoices
LargestInvoice: Highest value in the InvoiceTotal column
Filter the results (using a HAVING clause) to only return rows where the LargestInvoice is greater than $500. Group and sort the results by VendorState and VendorCity.
6.Write a select statement that returns 4 columns: VendorCity, InvoiceDate, InvoiceCount and InvoiceSum:
InvoiceCount: Count of the number of invoices
InvoiceSum: Sum of the InvoiceTotal column
Add a compound condition in a HAVING clause that filters the results to only return rows that match these conditions:
•The InvoiceDate is in the range of 5/1/2008 to 8/1/2008
•The InvoiceSum is greater than $300
•The InvoiceCount is greater than 1
7.Write a select statement that returns 6 columns: VendorName, InvoiceCount, InvoiceTotal, TotalPayments, TotalCredits, and BalanceDue:
InvoiceCount: / Count of the number of invoicesInvoiceTotal: / Sum of the InvoiceTotal column
TotalPayments: / Sum of the PaymentTotal column
TotalCredits: / Sum of the CreditTotal column
BalanceDue: / Sum of Invoices with a balance due
Group the results by VendorNameandinclude the operator necessary to add a summary row for each of the columns.
8.Write a select statement that returns 4 columns: VendorName, TotalInvAmt, LineItemTotal and LineItemCount:
TotalInvAmt: Sum of the InvoiceTotal column
LineItemTotal: Sum of the InvoiceLineItemAmount column
LineItemCount: Count of entries in InvoiceLineItemAmount column
Filter the results to only return rows where VendorNamestarts with the letters D through U. Group the results by VendorName and using a HAVING clause, further filter the results to only return rows where the TotalInvAmt is greater than $400.
Subqueries in Search Conditions (within the WHERE Clause):
9.Restate the following select statement in subquery form within the WHERE clause:
SELECT DISTINCT InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Invoices JOIN InvoiceLineItems
ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
WHERE InvoiceLineItemAmount > 50
ORDER BY InvoiceTotal
10.Business case: The executive committee needs to find out which vendors in Ohio haven’t used the company’s services (no active/previous billing). Specifically, the company, their phone and the contact person for the company.
Write a select statement that returns 3 columns: VendorName, VendorPhone and Contact. Contact is a column that concatenates VendorContactFName and VendorContactLName with a space in between. Filter the results to only return records where VendorID is not equal to the result(s) of the subquery.
Subquery portion:
Select statement that returns VendorID from Invoices table.
Further filter the results to only return records where the vendor resides in Ohio. Sort the records by VendorName.
11.Business case: The accounting department would like a report of all paid invoices that are less than the average amount of all invoices in the database.
Write a select statement that returns 3 columns: InvoiceNumber, InvoiceDate and InvoiceTotal. Filter the results to only return records where the balance due is zero and InvoiceTotal is less than the result(s) of the subquery.
Subquery portion:
Select the average InvoiceTotal from the Invoices table.
Sort the results by InvoiceTotal.
12.Business case: The accounting department would like a report of all invoices in the database that are larger than Zylka design’s largest invoice. Specifically, the company, invoice date, number and total.
Write a select statement that returns 4 columns: VendorName, InvoiceDate, InvoiceNumber and InvoiceTotal. Filter the results to only return records where the InvoiceTotal is more than the result(s) of the subquery.
Select the largest InvoiceTotal and filter the results to only return record(s) where VendorID is equal to 121.
Sort the results by VendorName and InvoiceTotal from largest to smallest.
Correlated Subqueries (within WHERE clause)
13.5.Business Case: The Accounting department would like a report showing all vendor invoices that are less than that vendor’s largest invoice. Specifically, the CompanyID, Invoice number, date and total.
Write a select statement that returns 4 columns: VendorID, InvoiceNumber, InvoiceDate and InvoiceTotal. Filter the results to only return records where the InvoiceTotal is less than the result(s) of the query.
Subquery portion:
*Select the largest InvoiceTotal for each vendor.
Sort the results by VendorID and InvoiceTotal.
*You will need to alias both the outer query and subquery tables. Within the subquery, you will need to filter the results where the VendorID’s are equal in both correlated tables.
Derived Tables (Subqueries within the FROM clause)
14.Business Case: The executive committee would like a list of all vendors located in California. Specifically the CompanyID, Company name and state.
Write a select statement that returns 3 columns: VendorID, VendorName and VendorState. Join the appropriate table on the subquery.
Subquery portion:
Select VendorState from the appropriate table and filter the results to only return records where VendorState equals CA. Create an alias for the subquery (in the FROM clause, a subquery is a “derived table” so you must treat it like a table) and join both tables on the only field they both share.
Sort the results by VendorID.
15.Business Case: The executive committee would like a list of their top 10 vendors based on average invoice total. Specifically the Vendors ID, and each vendor’s largest, smallest and average invoice total.
Write a select statement that returns 4 columns: VendorID, MaxInvoice, MinInvoice and AvgInvoice.
MaxInvoice (maximum of the InvoiceTotal column)
MinInvoice (minimum of the InvoiceTotal column)
AvgInvoice (average of the InvoiceTotal column) Join the appropriate table on the subquery.
Subquery portion:
Select the top ten VendorID and AvgInvoice (average of the InvoiceTotal column) from the appropriate table. Group the results by the appropriate column and sort the results by AvgInvoice from largest to smallest. Create an alias for the subquery (in the FROM clause, a subquery is a “derived table” so you must treat it like a table) and join both tables on the only field they both share.
Group the outer query results by the appropriate column (hint- needs to be aliased) and sort the results by AvgInvoice from largest to smallest.
Correlated Subquery (within the SELECT Clause)
16.8.Restate the following select statement in subquery form within the SELECT clause:
SELECT InvoiceNumber, InvoiceDate, InvoiceLineItemAmount
FROM Invoices JOIN InvoiceLineItems
ON Invoices.InvoiceID = Invoicelineitems.InvoiceID
WHERE VendorID = 122
ORDER BY InvoiceDate