WK3-In-class Exercise
Show in class for credit. Name the file “wk3-single-table-query.sql”
Keep a copy of all in-class work completed on Box/GoogleDrive/DropBox/FlashDrive. Box is available through UC. You will not have access to these from home. Organize now and save time later.
Use the Vendors Table
Write a SELECT statement that shows any four columns and sort the result set by any one of those columns.
Re-write #1 but use aliases to name the four columns.
a) Write a SELECT statement that returns two columns – VendorID and a column named “Vendor Address” which has the VendorName and the full address (VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode) in this format:
Zylka Design, 23 S Smith Road, Fresno, CA 93711
b) Why are there so many “Vendor Address” rows with NULL values? Remove VendorAddress2 from the select statement and see what happens. Can you guess why the NULL values were shown?
Use the Invoices Table
Write a SELECT statement to calculate the balance due for each Invoice. It should show four columns: InvoiceID, InvoiceDate, InvoiceTotal and Balance (InvoiceTotal – PaymentTotal- CreditTotal). Sort them by Balance in DESC order. Then show only the TOP 10 Invoices.
Write a SELECT statement that returns three columns- InvoiceTotal, 20% of InvoiceTotal and the Balance. Sort the result set by Balance.
Write a SELECT statement that returns the Payment Date and Balance columns from the Invoices table.
