Excel

 

Instructions:

 

  • Go to the US Dept. of Energy site and download EPA Green Vehicle Guide Data excel file (2016 Guide). Also take a look at the metadata.

  • Prepare an excel dashboard (use pivot tables, filters, conditional formatting, grouping & slicers) with at least 5 charts/graphs and a report on word doc (less than 300 words) based on your data analysis. Questions following these instructions will help you formulate your analysis. Also refer to Day10 videos.

  • Lastly, enjoy this project. Do it at your own pace and experiment with your creativity. There is no right or wrong analysis as long as you have logical reasoning to support your work.

 

 

Assignment:

 

You are a data consultant who has been hired by a hypothetical company called UBAR. This company is interested in spreading awareness about pollution emitted by cars and needs your help in deciding which types of cars are biggest and smallest contributors of CO2 emissions. Your job is to create a dashboard and concise report with recommendations based on your findings. Here are some pointers to help you with the analysis (sky is the limit, so be as creative and crazy as you want- enjoy the analysis).

  • Thinking in terms of cert region can be a good idea, what differences did you observe between Federal and California regions. Is California greener indeed as supported by common perception?

  • Based on the air pollution score, Comb CO2, green-house score etc you can try to see which cars do the best and which do the worst.

  • You can also try seeing how averages are impacted based on vehicle class. for example- Do small cars do better than mid size cars?

  • Seeing the differences between Gasoline cars and diesel cars is also an option.

  • You can analyze in terms of car brands also- for example which brand (NOT model only) is the cleanest.

  • Pollution and greenhouse scores can be analyzed in many different ways -by vehicle class, fuel type, car model transmission etc.

  • Try to be creative and come up with anything you like. UBAR will be really excited to see out of the box thinking.

Now you are one of the data analysts who know their S*#!

SQL

 

Instructions:

  • First download MySQL from MySQL site and install it to your local machine (check out Day10 videos for installation help)

  • After installing MySQL, start working on the assignment below.

 

 

Assignment:

 

After seeing your analysis on Excel, UBAR executives are really impressed and want to give you another project. They are planning to start a chauffeur driven Limousine leasing service. They want to build an online reservation system for the limos and need your help to do the job. 

 

Your system should include information about the users of this service. Each user will have a unique ID number to prevent erroneous bookings and other info recorded will be user’s full name, address, cell phone number and email id.

 

UBAR will launch 3 types of Limos- fun cabin (capacity is 6 people), large cabin (9 people) and comfort cabin (12 people). When a user books a reservation, she has to provide the following information- type of limo requested, user info, trip origin & destination, date & time of the booking- remember your reservation system only handles reservation requests. You don’t need to worry about the actual trips for now.

 

Design and create a MySQL database (with one or more tables) for the reservation system described above. After creating the database, create three imaginary users and enter their information in the database. Then write SELECT queries to retrieve information about those users. This will help you test your database before presenting it to UBAR.