mysql menagerie databasemysql.emoryce.com/data/menageriedatabase.pdf · make any changes needed to...

© 2015 Consort Institute, LLC. All right reserved. This material may not be reproduced, displayed, modified or distributed in any forms by any means without the express prior written permission of Consort Institute, LLC

MySQLMenagerie Database

by Robert Joseph, Ph.D.

ece.emory.edu | 404.727.6000 | ece@emory.edu

Menagerie Database

2

his exercise is designed to give you a real world experience of doing data analytics. In the realworld you are often times facedwith having to figure out how to get the data in the right format to be able to analyze it. The database that you will download will need to be read in in sections. Please look at the files and figure out how to get the two tables: pet and events into your database along with all the data. NOTE: \N needs to be changed to NULL.

You have a new consulting job at a vets. The prior developer was not able to finish the data analysis and so you have been hiredto continue the development.

1. Download menagerie database files at location https://dev.mysql.com/doc/index-other.html2. Make any changes needed to create the tables and get the data into the mysql environment3. Develop a set of questions that you want to answer based on the data that you are given and combine those questions

with the onces that are listed.

ece.emory.edu | 404.727.6000 | ece@emory.edu

One Method To AnswersThere is more than one way to get to the answer. What follows is just one method for getting the answers.

3

ece.emory.edu | 404.727.6000 | ece@emory.edu

Loading The Tables

1. Import the sql statements to create the new tables pet and event

2. Import the .txt files into their respective table

4

ece.emory.edu | 404.727.6000 | ece@emory.edu

Questions 1 - 141. How many different pets are there2. What is the average number of events per pet3. What are the frequencies of events.4. How old is each animal5. What is the average age of each animal6. What is the standard deviation of the age of each animal.7. What is the average age each animal type8. What is the average number of pets for an owner9. How many pets does each owner have10. How many events did each animal have11. How many events did each owner have12. What is the time between events for those animals that have have had

multiple events13. What is the number of visits each species is making to the vet14. What is the number of visits each sex is making to the vet

5

ece.emory.edu | 404.727.6000 | ece@emory.edu

Answer 1 - 31. How many diiferent pets are there

SELECT count(*) FROM `pet`

2. What is the average number of events for a petSELECT (Select count(*) from event)/(Select count(distinct(name)) from event)

3. What are the frequencies of events for each pet.Select name, count(*) from event group by name

6

ece.emory.edu | 404.727.6000 | ece@emory.edu

Answer 4 - 64. How old is each animal

Select name, datediff(Now(), birth)/365 from pet

5. What is the average age of the animalsSelect (sum(datediff(Now(), birth))/(select count(*) from pet))/365 from petSelect avg(datediff(Now(), birth))/365 from petSelect avg(datediff(Now(), birth)/365) from pet

6. What is the standard deviation of the age of each animal.Select STDDEV(datediff(Now(), birth)/365) from pet

7

ece.emory.edu | 404.727.6000 | ece@emory.edu

Answer 7 - 97. What is the average age each animal type

Select species, avg(datediff(Now(), birth)/365) from pet group by species

8. What is the average number of pets for an ownerSelect (select count(*) from pet)/(select count(distinct(owner)) from pet)

9. How many pets does each owner haveSelect owner, count(*) from pet group by owner

8

ece.emory.edu | 404.727.6000 | ece@emory.edu

Answer 10 - 1210. How many events did each animal have

SELECT name, count(*) FROM `event` GROUP BY name

11. How many events did each owner haveSELECT owner, count(*) FROM `event`, pet where event.name = pet.name GROUP BY owner

12. What is the time between events for those animals that have have had multiple events

9

ece.emory.edu | 404.727.6000 | ece@emory.edu

Answer 13 - 1413. What is the number of visits each species makes to the vet

SELECT species, count(*) FROM `event`, pet where event.name = pet.name GROUP BY species

14. What is the number of visits each sex is makes to the vetSELECT sex, count(*) FROM `event`, pet where event.name = pet.name GROUP BY sex

10

© 2015 Consort Institute, LLC. All right reserved. This material may not be reproduced, displayed, modified or distributed in any forms by any means without the express prior written permission of Consort Institute, LLC

Live Long and Prosper