Chapter 2, Problem 3AYK

Write a SELECT statement returns these columns from the orders table:   The invoice_id column as Invoice_ID The invoice_date column as Invoice_Date The invoice_due_date column as Invoice_Due_Date The payment_date column in the format 'Mon/DD/YY' as Formatted_Payment_Date The invoice_due_date column in the format 'Weekday, Month Day of month with suffix, YYYY' (ex: 'Tuesday, April 23rd, 2019')  as Long_Invoice_Due_Date The invoice_due_date plus 60 days as Expected_Payment_Date (Column must remain a date type) The number of days between the invoice_due_date and the payment_date as Time_To_Pay

9. Write a SELECT statement that uses aggregate window functions to calculate the total due for all vendors and the total due for each vendor. Return 3 Columns: The vendor id from the Invoices table. The balance due (invoice_total-payment_total-credit_total) for each invoice in the invoices table with a balance greater than 0. The total balance due for all vendors in the Invoices table. Modify the column that contains the balance due for each vendor so it contains a cumulative total by balance due.

On the Loan worksheet, in cell C9, enter a PMT function to calculate the monthly payment for the Altamonte Springs 2022 facilities loan. Ensure that the function returns a positive value and set the references to cells B5 and B6 as absolute references.

The tblCities table contains a numeric field named Population. Which of the following LINQ statements calculates the total population of all the cities in the table?a. Dim intTotal As Integer =            Aggregate city In CitiesDataSet.tblCities           Select city.Population Into Sum()b. Dim intTotal As Integer =            Sum city In CitiesDataSet.tblCities            Select city.Population Into totalc. Dim intTotal As Integer =           Aggregate CitiesDataSet.tblCities.city           Select city.Population Into Sum()d. Dim intTotal As Integer =            Sum city            In CitiesDataSet.tblCities.population

Write a SELECT statement that returns two columns basedon the EMPLOYEES table. Contact, is the employeelastname and the first initial of the first name. The secondcolumn, Phone, is the homephone without the area code.Only return rows for those employees in the 206 areacode, sort the results by the last name.

In cell B10, use a SUMIF formula to calculate the sum of AnnualSales where the value in the Regions named range is equal to the region listed in cell B9.

In cell B12, create a nested formula using the AVERAGE and ROUND functions to first find the average number of clothing sets sold in January (the range B6:B9), and then round the answer to 0 digits. Copy the formula from cell B12 to the range C12:N12.

On the Sales Data worksheet, calculate the commission rate and commission paid for each sales associate.  Beginning in cell F4, enter a formula using the IFS function to calculate the commission rate based on the following table. Hint: Do not include comma separators in numerical values in formulas. Excel interprets commas as function argument separators.

In the range C15:G15, insert a function to calculate the total daily revenue. In the range H11:H15, insert a function to calculate the weekly totals for the seating categories and grand total. What are the functions?