Question

I'm really struggling with these homework questions it's mainly 1 and 2, so if you can't help with all then please just one and two I've spent hours on those two and can't seem to get past them. SQL

That being said, if you could help with these 5 I would very much appreciate it:

INVOICES(table)

Columns

INVOICE_ID
VENDOR_ID
INVOICE_NUMBER
INVOICE_DATE
INVOICE_TOTAL
PAYMENT_TOTAL
CREDIT_TOTAL
TERMS_ID
INVOICE_DUE_DATE
PAYMENT_DATE

VENDORS(table)

Columns

VENDOR_ID
VENDOR_NAME
VENDOR_ADDRESS1
VENDOR_ADDRESS2
VENDOR_CITY
VENDOR_STATE
VENDOR_ZIP_CODE
VENDOR_PHONE
DEFAULT_TERMS_ID
DEFAULT_ACCOUNT_NUMBER

GENERAL_LEDGER_ACCOUNTS(table)

Columns

ACCOUNT_NUMBER
ACCOUNT_DESCRIPTION

1. Display the Vendor’s Name and their associated invoice Payment totals for all invoices with a dollar sign and 2 decimals for all invoices that have a payment total balance, listing the highest to lowest balance.

2. Display the vendor’s name if they have paid at least one invoice within 30 days. Show the vendor name only once in alphabetical order.

3. List all vendors name and the last day of the month for the associated invoice due dates. List the oldest date first.

4. Display the first 10 characters for the account description in alphabetical order, if the description is shorter than 10 place the necessary number of $ at the end of the description

5. Display all the account descriptions alphabetically which contain Taxes anywhere in the description.

Answer 1

1. Display the Vendor’s Name and their associated invoice Payment totals for all invoices with a dollar sign and 2 decimals for all invoices that have a payment total balance, listing the highest to lowest balance.

SELECT V.VENDOR_NAME , "$" + CAST( ROUND(I.PAYMENT_TOTAL , 2) AS VARCHAR(15)) I.PAYMENT_TOTAL

FROM VENDORS V , INVOICES I

WHERE V.VENDOR_ID = I.VENDOR_ID

ORDERED BY I.PAYMENT_TOTAL DESC ;

2. Display the vendor’s name if they have paid at least one invoice within 30 days. Show the vendor name only once in alphabetical order.

SELECT V.VENDOR_NAME

FROM VENDORS V , INVOICES I

WHERE V.VENDOR_ID = V.VENDOR_ID AND DATEDIFF(day, INVOICE_DUE_DATE , PAYMENT_DATE) <= 30

ORDERED BY V.VENDOR_NAME;