CIS$276$DB

Module $8$ Assignment

$1.$ Write a select statement based on the InvoiceTotal column of

the Invoices table:

$-$ Use the CAST function to return the first column as an integer

value.

Name it IntTotal. Name it IntTotal.

$-$ Use the CAST function to return the second column as datatype

decimal

with one digit to the right. Name it DecimalTotal.

$-$ Use the CONVERT function to return the third column as a

datatype that

outputs $2$ digits to the right of the decimal point and all comma$$s

to the

left $($i$.$e$.3,106\mathrm{.}34).$ Name it FormatTotal.

$2.$ Write a select statement that returns $4$ columns based on the

Vendors table:

$-($Column name$-$ Name$)$: this column should be formatted in the

following

way; VendorContactFName followed by the last initial and a

period

$($example: $$John S$.).$

$-($Column name$-$ StateInitial$)$: the VendorState first initial in

lowercase.

$-($Column name$-$ Phone$)$: VendorPhone without the area code

$-($Column name$-$ TodaysDate$)$: the current date formatted like$-$

Apr $18,$

$2016$

Filter the results to only return rows where the VendorPhone

prefix is equal to

$(800).$ Sort the results by VendorState and LastName.

$3.$ Business Case: The current date is $10/10/2016$; the accounting

department

would like to know which invoices with a balance due are still

outstanding and

the current age in days their invoice is beyond the invoice date.

Write a select statement that returns $4$ columns: VendorName,

InvoiceTotal,

InvoiceDate and InvoiceAge $($use the appropriate function that will

return the

number of days between the InvoiceDate and $10/1/2016).$

Filter the results to only return rows where there is a balance due

and the

InvoiceAge is greater than $132.$ Sort the results by VendorName.

$4.$ Write a select statement that returns $7$ columns:

$-$ InvoiceDate

$-($Column name$-$ WrittenDate$)$: use the function that will convert

InvoiceDate to this format; Apr $18,2016$

$-($Column name$-$ NewDate$)$: use the function that will add $45$ days

to

InvoiceDate and convert it to this format; Apr $18,2016$

$-($Column name$-$ DayOfWeek$)$: Use the function that will return

the name

of the day of NewDate $($i$.$e$.$ Saturday$)$

$-($Column name$-$ MonthPart$)$: Use the function that will return the

name

of the month of NewDate $($i$.$e March$)$

$-($Column name$-$ DatePart$)$: Use the function that will return the

day date

of NewDate $($i$.$e$.18\{$of Apr $18,2016\})$

$-$ Column name$-$ YearPart$)$: Use the function that will return the

year from

NewDate $($i$.$e$.2016)$

Sort the results by InvoiceDate.

$5.$ Business Case: The executive committee is implementing a

purchase discount

program based on the invoice total for a vendor. As such, they

need to gauge how

many invoices might qualify for a discount. Invoices that are

below $$100$ will NOT

qualify for a discount. Invoices between $101$ and $$500$ are a low

consideration,

invoices between $501$ and $$1000$ are a higher consideration and

invoices above

$$1000$ are the highest consideration.

Write a select statement that returns $4$ columns: VendorName,

InvoiceNumber,

InvoiceTotal, and PotentialDiscount.

PotentialDiscount is a column that will contain the result

expression from a

CASE statement that contains $4$ conditionals based on the

InvoiceTotal column;

Conditionals Result expression

InvoiceTotal $<100$No discount consideration$$

InvoiceTotal $101-500$Discount potential $3$

InvoiceTotal $501-1000$Discount potential $2$

InvoiceTotal $>1000$Discount potential $1$

Sort the results by InvoiceTotal.

$6.$ Business Case: The accounting department would like to know

the current

balances for vendors that owe money on their accounts. They

would like to

categorize vendors who owe over $$11,000$ as having a Very High

debt level, those

who owe between $$11,000$ and over $$500$ as having a High debt

level, those who

owe between $$500$ and over $$200$ as having a Medium debt level

and anyone

else as a Low debt level.

Write a select statement that returns $3$ columns:

$-$ VendorName

$-$ BalanceDue: balance due calculated column using the SUM

function

$-$ DebtLevel: nested IIF function that does the following:

o Sum of Balances greater than $$11,000=$Very High$$

o Sum of Balances between $$11,000$ and greater than $$500=$

$$High$$

o Sum of Balances between $$500$ and greater than $$200=$

$$Medium$$

o Sum of Balances equal to $$200$ or less $=$Low$$

Filter the results to only include vendors where a balance is due

and sort the

results from the sum of largest balance to smallest.