USING SQL FOR DATA ANALYSIS & TO ANSWER MARKETING QUESTIONS

This is an individual assignment [1.875%]

1. As demonstrated in class, usehttps://SQLLITEONLINE.comand import the following dataset (sqlite_avocado_student.db) found in the SLATE weekly content \MOD 3 - Unit 5\Activity5.
2. Create and then use SQL queries on the Avocado dataset to answer the questions seen below.
3. Answer the questions listed below in complete sentence form AND ALSO include the SQL query and the SQL output seen on your browser window. You may include screen captures.
4. Upload your Word document to the assignments folder in SLATE - '...\Assignment 5 - Using SQL'. You can also use screen captures of your work when

AVOCADO DATASET BACKGROUND

The original data for this activity was provided by the Haas Avocado Board (https://hassavocadoboard.com/) website. The dataset represents compiled weekly avocado sales for more than 3 years and comes from a sampling of retailer's cash registers based on retail sales of Hass avocados. The average price seen in the table is a 'per unit' price, even if multiple avocados are sold in a bag.

Below is a table summarizing all the fields contained the avocado database provided to you.

Variable Name and Type	Description
observationID (int)	A unique identifier or primary key
Region (string)	The geographic sales area/region
Year (date)	Year of the observation
Month (date)	Year of the observation
Quarter (date)	Year of the observation
Type (string)	Type of avocado - conventional or organic
AveragePrice (real)	The average price of a SINGLE avocado for a single observation
TotalVolume (real)	Total number of avocados sold
SupplierID (Text)	Identifier for the supplier of the avocado. This can be used to link/relate the avocado table to the supplier table.

STEPS TO IMPORT AND USE SQLITEONLINE.COM

1. GO TO SQLITEONLINE.COM
2. IMPORT SQLITE_AVOCADO_STUDENT.DB FROM SLATE
3. TYPE QUERY IN THE QUERY WINDOW
4. CLICK THE 'RUN' BUTTON TO RUN THE QUERY
5. RESULTS OF THE QUERY WILL APPEAR IN THE WINDOW BELOW.

QUESTIONS FOR ACTIVITY

You can use basic SQL queries to answer the following questions. The SQL tutorial at W3Schools.com (https://www.w3schools.com/sql/ ) is a great resource to review and practice SQL queries.

So, using SQLiteonline.com and the provided dataset (sqlite_avocado_student.db in SLATE) answer these questions. Remember to ALSO include the SQL query you used to find the answer.

1. What are the highest average prices customers are paying for a single avocado? Query to get you started: Select max(averageprice) from Avocado Still show your work in your submission.
2. What is the average price customers are paying per year?
3. List the average price customers are paying per region for all years?
4. List the highest average price for each region for all years? What region has the highest average price and what is that price?
5. What is the average price of organic avocados grouped by year? What has happened to the average price of organic avocados from 2016 to 2019?
6. How can the data be aggregated to obtain the volume of conventional avocados per quarter verses per week? Hint, you will need to incorporate the 'GROUPY BY [Field]' into the query.
7. How are the different regions performing by volume sales? Hint, like question 6 you will need to use the 'GROUPY BY' in your query.
8. BONUS: Create a list of the company and country of origin for avocados supplied to each region? (Hint: you will need to JOIN the supplier and avocado tables)

DON'T FORGET TO ASLO INCLUDE THE SQL QUERY YOU USED TO GET THE DATA.

\f