Question
Answered step-by-step

ETL exercise I was able to import 3 excel files and create tables....

ETL exercise


I was able to import 3 excel files and create tables. I was also able to figure out question 1 and 2 . My problem is writing the query for question 3 and 4 because it needs transformation. There is more to the exercise but i can do the rest once i get question 3 and 4 working. Below are the screen shots of the excel tables after that is the question exercise.


2017

Image transcription text

A B C D F F G H Month Country Region State Product Per-Unit Price Quantity Order Total 0 7 USA West CA Orange Cre 300 49 14700 1 9 USA Northeast RI Farm Fresh 365 49 17885 2 10 USA South TN Farm Fresh 365 10 3650 W 12 USA South FL Organiks 257 27 6939 4 12 USA South MD PearApple 363 83 30129 5 8 USA South KY Big Waffle 268 5 1340 6 4 USA South SC Farm Fresh 365 82 29930

... Show more


2018

Image transcription text

A B C D E F G H Month Region Customer_ID Product Per-Unit Price Quantity_1 Quantity_2 0 1 Midwest 280 PearApple 363 25 92 5 West 545 Orange Creepies 300 87 79 2 8 Northeast 131 Future Toast 253 90 6 3 4 Midwest 920 Farm Fresh 365 33 74 12 South 358 Rotpunkt 220 4 13 1 South 855 GMO Guardian 176 17 45

... Show more


2019

Image transcription text

A B C D E F G H K Month Country Region State Product Per-Unit Price Quantity Order Subtotal Quantity Discount O 9 USA South FL Only Pancakes 413 95 39235 3924 6 USA West HI Big Waffle 268 93 24924 2492 5 USA Northeast RI Grey Gummies 446 95 42370 4237 9 USA Midwest NE Funky Pops 380 100 38000 3800 11 USA South NC Future Toast 253 31 7843 0 5 8 USA West WA Mr Greens 447 76 33972 0 6 8 USA South MD Giant Gummies 347 93 32271 3227 7 1 USA South TN Power Gels 193 11 2123 8 1 USA South FL Red Hot Chili PE 233 24 5592 9 12 USA South DE Nap Be Gone 500 67 33500 10 2 USA Northeast MA Cheezos 372 63 23436 11 1 USA South NC GMO Guardian 176 77 13552

... Show more


Your job is to use SQL to perform an ETL which will accomplish the following:

1.    Start with the skeleton starter script we give you, attached to this assignment. Modify the CREATE TABLE command so the schema is as follows:


Image transcription text

Computer code Notes about what you need to do DROP TABLE IF EXISTS stagingTable; CREATE TABLE stagingTable [ xearlnt INT [4) , monthInt INT [2} , Leave this code alone — it ensures you a fresh start Right now this creates stagingTable with onlyr two fields, the yearlnt and monthlnt. Modify the code where highlighted in yellow to correspond to the schema below. [FSM 330 Week 6 Candy Part 1 v03 ——there will be more you need to fill in here And of course, when ygufre done coding, remove the '—— there will be more you need to fih' in here' comment @

... Show more

Image transcription text

staging Table Column yearInt INT(4) I monthInt INT(2) state CHAR(2) I country CHAR(3) Iregion VARCHAR(25) I customer_id INT(5) Product Name VARCHAR(25) I unitPrice INT(5) I quantityDiscount INT(5) quantity INT(6) I order Total INT(6)

... Show more

Image transcription text

Get the 2017 bit of the script working. Computer code ——In5ert 201? Data Notes about what you need to do This is a comment telling you the 2017 data is going to be inserted here INSERT INTO stagingTablel"monthInt", "state", "country", "region", "Product Name", "unitPrice" —— you need to fill this in here} After you have created stagingTable, this is the first half of a command that will insert the data into stagingTable. You need to [FSM 330 Week 6 Candy Part 1 v03 replace the yellow highlighted material with your own code to complete it. SELECT "Month", "State", "Country", "Region", "Product", "Per—Unit Erice", "Quantity", "Order Total" FROM pd201? - r This is the second half of a command that will insert data into stagingTable. You _(_i_o_n_'_t_ need to change anything here.

... Show more


Image transcription text

UPDATE stagingTable SET xearInt=20 1 7 ; This sets the year to 2017 for this data. You 9191):"; need to change this.

... Show more



Image transcription text

3. Get the 2018 part of the script working. Computer code Notes about what you need to do ——Insert 2018 Data Comment. INSERT INTO stagingTable_l——you need to fill this in This is the first half of a command here} that will insert the data into stagingTable. You need to replace the yellow highlighted material with your own code to complete it. See the rules below for more details. SELECT FROM 9d2 0 13 This is the second half of a command that will insert data into stagingTable. IFSM 330 Week 6 Candy Part 1 v03 You need to replace the yellow highlighted material. UPDATE stagingTable SET xearInt=2018 WHERE xearInt ISNULL; This sets the year to 2018 for any new entries which do_n_'_t yet have a year. You d_o_n_'_t_ need to change this.

... Show more


Image transcription text

4. Get the 2019 part of the script working. Computer code Notes about what you need to do ——Insert 2019 Data Comment. INSERT INTO stagingTabl i——you need to fill this in here) You need to replace the yellow highlighted material with your own code to complete it. See the rules below for more details. SELECT m FROM pd2019 r UPDATE stagingTab 1e ...: You need to replace the yellow highlighted material. You need to replace the yellow highlighted material.

... Show more

Answer & Explanation
Verified Solved by verified expert
Rated Helpful

dictum vitaeec aliquet.m risusor nec facilisis. Pell

gue

facilisis, consectetur adipiscing elit.Fusce dui lectus, congue vel

gue

, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacin

Unlock full access to Course Hero

Explore over 16 million step-by-step answers from our library

Subscribe to view answer
Step-by-step explanation

tricesrem ipsum dolor sit amet,lestie conseqe vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor si

20551137

gue

gue

gue

, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit am

gue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur

congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur

1 Attachment
image.png
png
Student reviews
60% (5 ratings)
Thorough explanation
Easy to follow
Clear formatting

"Thank you very much. I now understand."