mysql menagerie databasemysql.emoryce.com/data/menageriedatabase.pdf · make any changes needed to...
TRANSCRIPT
© 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 | [email protected]
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 | [email protected]
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 | [email protected]
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 | [email protected]
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 | [email protected]
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 | [email protected]
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 | [email protected]
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 | [email protected]
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 | [email protected]
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