Categories
Internet Marketing

Learning Goal: I’m working on a internet marketing exercise and need an explanat

Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates

For This or a Similar Paper Click To Order Now

Learning Goal: I’m working on a internet marketing exercise and need an explanation and answer to help me learn.
follow the instructions and do the SQL assignment.
It is a framework made of tables where each row (a record) represents a set of related data describing a customer or a productEach column (feature, predictor, variable) represents data such as company name, region, street address, quantity sold, and so on
Exhibit 2-9 shows a subset of data in the avocado datasetColumns include region, average price, and type
Rows contain the data for this tableThe first row shows Phoenix, 0.77, conventional
Manipulate the data by using a query language, the most popular is SQLTo demonstrate how SQL can be used, we will use SQLITE, a web version
This introductory tool allows us to explore the basics of SQL programming
Understanding the Dataset
Download data from the student’s resources page
Original data was downloaded from the Hass Avocado Board in June 2019, and is compiled of weekly sales from 2016 to March 2019Data comes from actual retail sales of Hass avocados
The Average Price (of avocados) in the table reflects a per unit cost, even when multiple avocados are sold in bags
To get started, let’s review the data elements in the table – Exhibit 2-10After reviewing the data, what questions can you ask to better understand avocado sales since 2016? Some ideas includeWhat are the highest average prices customers are paying for a single avocado?
What is the average price customers are paying per region?
Where are avocados being sold over certain average prices?
What is the average price that customers are paying in specific geographic regions?
How can the data be aggregated to obtain the volume of conventional avocados per quarter versus per week?
How are regions performing by volume sales?
What is the company and country origin for avocados supplied to each region?
Use SQL for basic data analysis to answer these questions and more
Applying the Concepts
There are several options for exploring SQL, but here we use an online platform
Step 1: Visit the website https://sqliteonline.comYour screen should look like Exhibit 2-11
Step 2: To explore data, it must be imported into the SQLite platform – Exhibit 2-12Click on “Import” and then “Open”
Step 3: Click “Open” and browse for the csv file “AvocadoData” you downloaded from the student’s resources pageExhibit 2-13
Step 4: After selecting “Open,” the data import specification will appearUpdate “Table name” to “tmp” (we rename late) and update “Column name” from the drop-down menu to “First line”
Your selected options should match Exhibit 2-14
Step 5: After selecting “Ok,” the data will uploadYour screen should appear similar to Exhibit 2-15
Step 6: You can now request certain information to begin exploring the dataIn the open text area, enter your SQL code to view all the data
Accomplish this by using the asterisk * symbol
Perform this task by using the following query statement and then clicking “Run” in the menu bar – see Exhibit 2-16SELECT * FROM tmp;
Step 7: Next, you must modify the avocado table so each column is given the appropriate data typeBecause the Avocado table was created by importing the csv file, SQLite Online does not know the data type of each column
Instead, it defaults each column to a string data type, which can be a problem for numerical values that need to be sorted
This can be fixed by creating the Avocado table with the appropriate column data types and then placing all the original data into the new table
Step 7a: Use the following DDL (data definition language) query statement to create the Avocado table with each column’s appropriate data type – Exhibit 2-17CREATE TABLE avocado(
observationid INT PRIMARY KEY,
region TEXT,
year INT,
month INT,
quarter INT,
type TEXT,
averageprice REAL,
totalvolume REAL,
supplierid TEXT
);
Step 7b: Click “Run” in the menu barYou will see a new avocado entry created under “Table” (Exhibit 2-18)
Step 7c: Finally, copy all the data from the original tmp table into the Avocado tableUse the following query statement and click “Run” – Exhibit 2-19INSERT INTO avocado(
observationid, region, year, month, quarter, type, averageprice, totalvolume, supplierid
)
SELECT observationid, region, year, month, quarter, type, averageprice, totalvolume, supplierid
FROM tmp;
Step 7d: Use the following query statement and clicking “Run” – Exhibit 2-20SELECT * FROM avocado;
Step 8: In this step, you will learn how to drop a table from the databaseUse the following query statement and click “Run” – Exhibit 2-21Drop TABLE tmp;
Step 9: You can view selected fields and sort your dataSuppose you are interested in just looking at the average price paid for an avocado by region but want to sort results by average price from high to low
Use the following query statement and click “Run” – Exhibit 2-22SELECT averageprice, region, year FROM avocado ORDER BY averageprice DESC;From sorting the data from high to low, we see that San Francisco has the highest average price from 2016
Step 10: What if you want to focus on reviewing a smaller subset of the data?To create smaller subsets, use a conditional statement (WHERE) to meet a conditionFor example, you would like to examine sales where the average price is a certain value
You can include additional criteria (for example, region) to further narrow your query
To include the region criterion, use what is referred to as a logical statement (AND, OR)
Maybe you want to examine the average price of organic avocados in Charlotte, NC that are greater than $2.50Use the following query statement and click “Run” – Exhibit 2-23SELECT year, month, averageprice, region, type
FROM avocado WHERE averageprice > ‘2.5’ AND type = ‘Organic’ AND region = ‘Charlotte’
ORDER BY averageprice DESC;As you see from the results, the highest average price of organic avocados sold in Charlotte, NC was $2.83 in August 2017
Step 11: You need to better understand the total volume of conventional avocados in quarter 1 of the last three yearsYou then want to sort by year from high to low
Use the following query statement and click “Run” – Exhibit 2-24SELECT region, Round((totalvolume),0) as totalvolume, averageprice, month, year FROM avocado
WHERE quarter = ‘1’ AND type = ‘Conventional’ ORDER BY year DESC;We see that in the first quarter of 2019, a total of 79,041 conventional avocados were sold in the Syracuse region at an average price of $1.16 in January
Step 12: Another way to return a selected set of data that meets multiple criteria is using the statement INFor example, if you want to understand only two cities from the results in step 9, consider returning all the fields to limit the focus on just those two cities – Boston and San Francisco
Use the following query statement and click “Run” – Exhibit 2-25SELECT * FROM avocado WHERE region IN (“Boston”, “San Francisco”);Now you see only results from Boston and San Francisco in the first quarter of 2019
Aggregation
Most times when you get a dataset, you will need to roll it up to a higher level
For example, you can calculate the maximum average avocado price in our dataset, or the sum of total Volume by quarter or year
To do so, you will need to add a function to the variable you would like to roll upFor numeric type variables, you can use the SQL aggregate function for a set of values: sum, min, max, average, and so on – Exhibit 2-26
For categorical data, you can use functions such as count
When using aggregate functions, results will be produced on a single row
Step 13: the averageprice column contains the average price of avocados by city and date – what if you want to search for the highest average price customers have paid to date?Use the following query statement and click “Run” – Exhibit 2-27SELECT MAX(averageprice) FROM avocado;You will see that the highest average price customers paid was $3.25
You can use the MIN(averageprice) statement to find the lowest average price customers paid to date
Step 14: Suppose your data contains the month/day/year in a single column, but you want to examine data over each quarter insteadYou can aggregate the total sales volume by summing the values by quarter
Use the following query statement and click “Run” – Exhibit 2-28SELECT quarter, Round(SUM(totalvolume),0) as sum_totalvolume FROM avocado GROUP BY quarter Order by quarter;Using the Round function allows us to manage decimal places for totalvolume
As you see during quarter 1, customers from all regions purchased 354,984,036 avocados
E.Build Your Own Supplier Table
Step 15: The dataset also contains supplier information
If you need to know which supplier is providing avocados to certain cities, you might want to develop another table with the supplier information
Use the following query statement and click “Run” – Exhibit 2-29CREATE TABLE supplier (supplierid TEXT PRIMARY KEY, name TEXT, country TEXT);
F.Add Data to Your Table
Step 16: To add data to your data, you can insert supplier id, name, and country location by using the following query statement and clicking “Run” – Exhibit 2-30
INSERT INTO supplier (supplierid, name, country) VALUES (“A”, “Valex”, “Spain”);
INSERT INTO supplier (supplierid, name, country) VALUES (“B”, “Cresco Produce”, “Thailand”);
INSERT INTO supplier (supplierid, name, country) VALUES (“C”, “Shinesun Industry”, “Viet Nam”);
G.Join the Two Tables (MERGE)
Step 17: Currently, you have two tables
Table Avocado consists of observationid, region, year, month, quarter, type, averageprice, totalvolume, and supplierid
Table Supplier includes supplierid, name, and countryNote that supplierid is a common key between the tablesA primary key in the Supplier table
A foreign key in the Avocado table
We can select attributes from both tables by joining the tables using the common key (supplierid)
Use the following query statement – Exhibit 2-31SELECT avocado.region, avocado.averageprice, supplier.name, supplier.country
FROM avocado JOIN supplier ON avocado.supplierid = supplier.supplierid;This table provides data in a cohesive form
We now see that Valex from Spain supplied the product to the Miami/Ft. Lauderdale region where the average customer price was $1.58
H.Update the Data
Step 18: Using SQL, you can change the values in any row and column
For example, Supplier A has started a new operation in Mexico that it will use to provide avocados to the U.S. market
Here, the company’s location will need to be changed from Spain to Mexico
Use the following query statement – Exhibit 2-32UPDATE supplier SET country = “Mexico” WHERE name = “Valex”;Review your changes by using the following query statementSelect * FROM supplier;
I.Delete Values
Step 19: You might find that you no longer need to retain a row of data
You must delete it to prevent it from being included in future analyses
To illustrate, we start with displaying observationid 5Perform this task by using the following query statement – Exhibit 2-33Select * from avocado WHERE observationid = “5”;You now see observation 5 is visible on screen
Step 20: You want to delete this observation from the dataUse the following query statement – Exhibit 2-34DELETE FROM avocado WHERE observationid = ‘5’;
SELECT * FROM avocado;As you see, observation 5 no longer exists in the table
In this section, you learned how to query data using SELECT in one table and across tables using JOIN
Also how to create a table using CREATE and INSERT
How to modify data using UPDATE
Each query is helpful in identifying different pieces of information
By experiencing SQL, you have discovered how databases work
You are encouraged to find more ways to work with SQL, here are a optionsGoogle’s BigQueryAllows you to use SQL-like language to query and manipulate data
Allows for analysis in real time
Size limits to data allowed for free each monthhttps://cloud.google.com/bigquery
MYSQL on the server-sideYou can set up a server-side database and access it using a client-based MySQL workbench used for data modeling, SQL development, and user administration
Review the MySQL documentation at www.mysql.com/products/workbench
Amazon AuroraThis relational database on Amazon Web Services is faster than MySQL
Data can be loaded into Aurora from MySQL and PostgreSQL
Data is backed up continuously on Amazon S3 servers to ensure reliability disaster recoveryhttps://aws.amazon.com/rds/aurora/getting-started

For This or a Similar Paper Click To Order Now

Leave a Reply