WK3-In-class Exercise Show in class for credit. Name the file “wk3-single-table-

Question

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.

Explanation / Answer

Hi,

1. Vendors TABLE
Ans a) select VendorID, VendorAddress1||","||VendorAddress2||","||VendorCity||","||VendorState||VendorZipCode as "Vendor Address" from Vendors;

Ans b) Data which is inside the table is not shown here. When we see rows where "Vendor Address" is null, this means all the 5 columns which are the part of this column are null.
As the data set is not shown so we cannot say what will happen if we remove VendorAddress2. It totally depends on the data.
Below is the query that can be used-

select VendorID, VendorAddress1||","||VendorCity||","||VendorState||VendorZipCode as "Vendor Address" from Vendors;

2. Invoices Table

Ans a) SELECT * from(Select InvoiceID, InvoiceDate, InvoiceTotal, (InvoiceTotal – PaymentTotal- CreditTotal) as "Balance"
from Invoices order by Balance desc ) where rownum<=10;

Ans b) Select InvoiceTotal, InvoiceTotal*0.20, (InvoiceTotal – PaymentTotal- CreditTotal) as "Balance"
from Invoices order by Balance desc ;

Ans c) Select PaymentDate, (InvoiceTotal – PaymentTotal- CreditTotal) as "Balance"
from Invoices order by Balance desc ;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.