In this assignment, you have been given five tables $($as $.$csv files$)$:

A sites table that includes a site ID$,$ the town, and the address in the town

A customer table that includes a customer ID$,$ first name, last name, town, and state

A flavor table that includes a flavor ID$,$ the flavor name, nut allergen, lactose allergen, gluten allergen, the type of dessert, and whether or not it is sugar$-$free

A toppings table that includes a topping ID$,$ the topping name, nut allergen, lactose allergen, and gluten allergen

An order table that includes the customer ID$,$ site ID$,$ flavors ordered, toppings ordered, and the date of the order

Your first step will be to load these tables using an R script, including the header information as given in the files.

Your next step will be to reason through the tables and columns needed to find the following information:

That each of the $500$ orders has a first scoop of dessert

The pecking order of the sites relative to number of sales $($by site name, not ID$)$

The number of purchases each specific customer made $($by name, not ID$)$

Which of the ice creams and toppings would be problematic for a person with a nut allergy

Find every order that counts as a sundae $($i$.$e$.,$ it has at least one topping$)$

Find all customers $($by name$)$ that are not from Maine or New Hampshire

Find how many different types of desserts that there are $($not the names of the flavors, but the types of the dessert themselves$)$ and how many of each type that there are

List out all of the possible orders consisting only of ice cream $($at most three scoops$)$

Find the most frequently ordered flavor

Find all of the places that people came from to order ice cream that do not have an ice cream stand in them.

Your final step will be to write the sqldf command that will answer the desired question $($or implicitly contain the answer directly$).$

Something to note:

IS NULL will help you with at least one of these tasks. You can say ID IS NULL to find whether or not there exists an ID field that is blank for a record.

Think about ways to combine tables to help you answer questions. This will help in a few cases.

There is at least one instance where you will need to use UNION and at least one place where you will need to use EXCEPT.