MIS 2502 Data Analytics (Fall 2018)

instructor: Aaron Zhi Cheng, Section 003

Assignment #4: ETL and Pivot Tables in Excel

Deadline: Wednesday, 10/17/2018, at 11:59 pm.

  • The assignment instruction is posted on the Assignment Page of the Community site (link).
  • Complete and submit the assignment as an Excel document (the completed ETL Workbook.xlsx) to the Canvas.
  • There is no answer sheet for this assignment.
  • If you do not follow the instructions, your assignment will be counted late.
    • Late Assignment policy: All assignments will be assessed a 50% penalty (subtracted from that assignment’s score) for the first day (i.e. 24 hours) they are late. No credit will be given for assignments turned in more than 24 hours past the deadline.
  • Work needs to be done individually.

Agenda for Week 7 (the week of 10/8)

Class Schedule:

  • Monday (10/8)               Exam 1
  • Wednesday (10/10)       Dimensional Data Modeling Overview
  • Friday (10/12)                ICA #5: Pivot Tables in Excel


  • Assignment #3 (SQL#2): Due Wednesday 10/10, 11:59 pm

Resources for Exam #1

  • Study guide page (Link)
  • Checklist for drawing an ERD (Link)
  • Class capture for exam review (Link)
  • Office Hour: Monday, 10/8, 1:30-2:30 pm (before Exam #1), @ Speakman 201

Agenda for Week 6 (the week of 10/1)

Class Schedule:

  • Monday (10/1)                Extract, Transform, Load (ETL) (Slides Link)
  • Wednesday (10/3)          ICA #4 – ETL – Excel Basics (Instructions Link)
  • Friday (10/5)                   Review for Exam 1 (Study Guide Link)


  • Bonus Assignment [Optional]: Due Monday (10/1), 11:59 pm

Group Project:

The group project starts from Monday, 10/1/18.

  • You are going to analyze a data set from the Temple Analytics Challenge (Link) and to visualize your findings.
  • You should work in a team of 2 to 4 members.
  • Members of the same team will receive the same grade.

Due: Wednesday, 10/31/2018, 11:59 pm

Check more information here.

About Assignment #2 (Due 9/28/18 (Tomorrow) before Class by 3 pm)

If you do have technical issues (constant error reports, no results grid, etc.), do the following,

  1. Finish writing all queries and save them in a SQL or document (.docx, .txt, …) file before actually running them. You will save time and energy.
  2. Check again your code thoroughly.
  3. Common mistakes are typos, wrong spacings, and inappropriate punctuation marks (“, ”, (), [], etc. ). Check correct examples for SQL syntax in the lecture slides.
  4. Type but not copy queries from other sources as there may be subtle differences in formatting.
  5. Read the error report carefully and see what’s wrong.
  6. If it is an unsolvable problem due to the idiosyncratic nature of your computer, operating system, version of MySQL, try another computer, you can (1) download and use MySQL Workbench in a different computer at the school’s computer lab, or (2) borrow and use your friend’s or classmate’s computer if they do not have problems.

For assignment #2, the grading rule is the following,

  1. Points (i.e., 5 points, 10 points, 15 points) vary across questions.
  2. Full points for each question will be given if your SQL statement is COMPLETELY correct. No point even if there is a small mistake.
  3. 20% off for each question if the SQL statement is correct, but the results/answer from MySQL Workbench is missing or incorrect.

BTW, I will not write you a checklist of all possible SQL statements this time for Assignment #2.

  1. Overview lecture slides, ICAs, and course captures (Cavas->Modules).
  2. Come to my office hour tomorrow 12:30-1:30 pm at Alter 604F if you have any question.

Agenda for Week 5 (the week of 9/24)

Class Schedule:

  • Monday (9/24)                ICA #2.2 – Working with SQL, part 1 (Retrieving information from the database)
  • Wednesday (9/26)          Creating and updating the database (SQL CREATE, DROP, ALTER, INSERT, UPDATE, and DELETE)
  • Friday (9/28)                   ICA #3 – Working with SQL, part 2 (Creating and updating the database)


  • Assignment #2: Due Wednesday (9/26), 11:59 pm
  • Bonus Assignment [optional]: Due next Monday (10/1), 11:59 pm

Office Hours:

  • Monday (9/24): Speakman 201F, 3:50-4:50 pm
  • Tuesday (9/25): Speakman 209, 11 am-2 pm  (hold by ITA Nathaly).
  • Wednesday (9/26): Speakman 201F, 3:50-4:50 pm
  • Friday (9/28): Alter 604F, 12:30-1:30 pm

Examples of SQL statements For SQL Out (Advanced Queries) in the Powerpoint 2.2

SQL advanced queries are the most difficult ones in this course.

If you want to master SQL, you need to pay more attention, take more efforts, and do more practices.

I attached the SQL queries examples (Check SQL Format / Word Format) covered in the Slides 2.2. SQL 1 – Out – Advanced. You can also have access to them from the community site at Slides Deck. Please take a look and try those queries by yourself!!!

A few notes below.

  • Your ICA #2.2, Assignment #2, and Exam 1 will directly rely on your skills in writing SQL queries.
  • If you have not installed MySQL workbench, please do so as early as possible.
  • If you have difficulty in setting up your MySQL, please contact me or ITA Nathaly.

Come to the following office hours if you have any questions regarding SQL queries:

  • Monday/Wednesday: Speakman 201F,  3:50-4:50 pm;
  • Tuesday (Nathaly): Speakman 209, 9/25/18, 11am-2pm.

Assignment #2: SQL 1 – Getting Data out of the Database [Due Wednesday, 9/26/18, 11:59 pm]

Here are the instructions: Assignment #2 – SQL Part 1.

And here is the answer sheet: [Answer Sheet].

Due date: Wednesday, 9/26/2018, 11:59 pm.

  • Complete and submit the answer sheet as a Word or PDF document through Canvas.
  • You will need to use MySQL Workbench to complete the assignment.
  • Please do note that equipment failure is not an acceptable excuse.
  • Slides and ICAs will be your best materials for review. Also, check class captures if you miss something in the class.
  • Please come to my regular office hours (M&W: 3:50 – 4:50 pm) if you have any questions.
  • Do not procrastinate and start your assignment early!