Reference no: EM13999633

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.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, 2008
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 12/1/2008; 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 ‘12/1/2008').

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, 2008

• (Column name- NewDate): use the function that will add 45 days to InvoiceDate and convert it to this format; Apr 18, 2008

• (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, 2008})

• Column name- YearPart): Use the function that will return the year from NewDate (i.e. 2008) 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'

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.

Attachment:- Assignment.rar