Q$1.$ Write a reference, in VBA code, to each of the following ranges. $($You can assume that each of these ranges is in the active worksheet of the active workbook, so that you don$$t have to qualify the references by worksheet or workbook.$)$ Here$$s an example. Question: The cell one column to the right and two rows below Cell A$1.$ Answer: Range $($A$1).$Offset$(2,1)$

a$.$ The fifth cell of the range B$1$:B$30.$

b$.$ The cell at the intersection of the $25$th row and $5$th column of the range A$1$:Z$100.$

c$.$ The cell at the intersection of the $25$th row and $5$th column of the range that has been set to a Range object variable named SalesRange.

d$.$ A range of employee names, assuming the first is in cell B$2$ and they extend down column B $($although you don$$t know how many there are$).$

Q$2.$ Write a VBA program $($consisting of a single subroutine$)$ to check existence of a given value in a list. Specifically, you are given a list of product IDs in column A$,$ as follows:

You don$$t know how many products there are. The user enters a value in an input box $($e$.$g$.$ X$-7942)$ and the code checks this against the list and returns a message box stating either that the product is in the list $($e$.$g$.$Product X$-7942$ was found$)$ or not $($Product X$-7942$ was NOT found$).$ Write the code here $(***$ no need to turn in any Excel file $***).$

Q$3.$ This question refers to the Sales Orders database in Week $2|$ Learning Materials & Resources. Write SQL queries below for each of the following. Note that I am asking for the SQL query only, not the results of that query. Here$$s an example. Question: List the entire customers table. Answer: Select $*$ From Customers

a$.$ List the names and cities of all customers

b$.$ List the different states the vendors come from $($unique values only, no duplicates$)$

c$.$ Find the number of customers in California

d$.$ List names of all employees who have sold to customer Rachel Patterson $($Note: You can only use the information given in the question, i$.$e$.$ you cannot first look up Rachel Patterson$$s Customer ID and use that ID in your query$).$

Q$4.$ What are the three main categories of analytics? Briefly describe a possible analytics application from each of these categories in the retail industry.

Q$5.$ Use the K$-$means clustering technique $($described in a video in Week $5|$ Learning Materials & Resources$)$ to form two clusters of the following data: $\{5,7,12,10,14,22,30,16,36\}$ starting with $\{5,7\}$ and $\{12,10,14,22,30,16,36\}$ as the two initial clusters.