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