UMGC IFSM 330 Candy Assignment
Attachments:
2017_product_data_students-final.csv
2018_product_data_students-final.csv
2019_product_data_students-final.csv
Candy_part_1_skeleton_for_students.SQL
Your company wants to merge its old product order data into a new data mart to facilitate analysis. You have been tasked with
writing an ETL (extract, transform, and load) code sequence, and executing it on three years’ worth of order data.
In this assignment, you will produce SQL code which scrubs and imports each of the three years’ worth of data, and produces an
output file called stagingTable.
Along with these instructions, there is another document, ‘Additional Clarification on the Week 6 Candy Assignment’. Please read
that document carefully.
You should also read the ‘Data Notes’ in part 3 of this document. It is very important that you understand the data and how the
data changes over the three years, so you can create a ‘stagingTable’ the effectively combines the data that might have been
captured in different ways over the years.
Let’s get started!
Part A: Upload all the files you will need to SQLlite:
1. Import the file called “2017_product_data_students.csv” to SQLiteonline.com. When you import it, give it the table name
“pd2017” (no quotes) and set the column name to “First line.”
1
, 2. Import “2018_product_data_students.csv” as “pd2018”
3. Import “2019_product_data_students.csv” as “pd2019”
4. If you SELECT * FROM pd2017, you should see something like the below screenshot. Note you should see all three of the
import tables on the left, and the pd2017 data should match what is shown as selected.
2
Attachments:
2017_product_data_students-final.csv
2018_product_data_students-final.csv
2019_product_data_students-final.csv
Candy_part_1_skeleton_for_students.SQL
Your company wants to merge its old product order data into a new data mart to facilitate analysis. You have been tasked with
writing an ETL (extract, transform, and load) code sequence, and executing it on three years’ worth of order data.
In this assignment, you will produce SQL code which scrubs and imports each of the three years’ worth of data, and produces an
output file called stagingTable.
Along with these instructions, there is another document, ‘Additional Clarification on the Week 6 Candy Assignment’. Please read
that document carefully.
You should also read the ‘Data Notes’ in part 3 of this document. It is very important that you understand the data and how the
data changes over the three years, so you can create a ‘stagingTable’ the effectively combines the data that might have been
captured in different ways over the years.
Let’s get started!
Part A: Upload all the files you will need to SQLlite:
1. Import the file called “2017_product_data_students.csv” to SQLiteonline.com. When you import it, give it the table name
“pd2017” (no quotes) and set the column name to “First line.”
1
, 2. Import “2018_product_data_students.csv” as “pd2018”
3. Import “2019_product_data_students.csv” as “pd2019”
4. If you SELECT * FROM pd2017, you should see something like the below screenshot. Note you should see all three of the
import tables on the left, and the pd2017 data should match what is shown as selected.
2
