search

fasterthanlime / cs322

CS-323 project
2 stars 1 forks source link

Implement query I #18

Closed nddrylliog closed 12 years ago

nddrylliog commented 12 years ago

List the average weight, average height and average age, of teams of coaches with more than XXX season career wins and more than YYY win percentage, in each season they coached. (XXX and YYY are parameters. Try with combinations: {XXX,YYY}={<1000,70%>,<1000,60%>,<1000,50%>,<700,55%>,<700,45%>}. Sort the result by year in ascending order.

nddrylliog commented 12 years ago

I've started it (see 38beef45dcf49447ad4b7f4faf2ae001827e4434 ), but I'm going to bed for now. After all, the deadline is still far along, and I'm making good progress on these queries.

nddrylliog commented 12 years ago

Finished in 70b1e863c1ff682bca47866fe65825d35472d31e, closing.

greut commented 12 years ago

It seems it's computing the data (avg and age) from the coaches instead of the team members. Or did I misunderstood the query we have to solve?

nddrylliog commented 12 years ago

"of teams of coaches" -> I thought this meant precisely that it was the average of weight/height etc. of the players in the teams managed by (coaches who had more then XXX/YYY career/wins percentage). Don't you think? We could argue either way imho...

greut commented 12 years ago

Apparently we agree on that, the SQL query doesn't seem to do that. Since I changed it to use the coaches table, it might be empty on your side until we find what's going on.

nddrylliog commented 12 years ago

Yeah but still using only the coaches tables how can you find the average weight/height/age of the players of these teams?

greut commented 12 years ago

I'd use best_coaches.year + best_coaches.team_id (to add) and join that with player_seasons. The coaches table is only useful for the XXX param which doesn't have to be recomputed.

nddrylliog commented 12 years ago

By "you would" you mean "in the final version of the query which is not yet done", correct?

greut commented 12 years ago

Indeed. When the average age is above 60, something is wrong :-)

nddrylliog commented 12 years ago

My average age seemed correct btw, (using to_date(year, 'YYYY') instead of SYSDATE) On May 22, 2012 10:07 PM, "Yoan Blanc" < reply@reply.github.com> wrote:

Indeed. When the average age is above 60, something is wrong :-)

Reply to this email directly or view it on GitHub: https://github.com/nddrylliog/cs322/issues/18#issuecomment-5857344

nddrylliog commented 12 years ago

I wanted to work on that some more, but it returns 0 rows, I guess because 'coaches' is empty because of triggers. I'll try debugging them now on my side.

nddrylliog commented 12 years ago

Hypothesis: my user doesn't have the permission to create triggers :D

nddrylliog commented 12 years ago

Aaand that was it. coaches table is correctly populated now. Finishing up this query

nddrylliog commented 12 years ago

Welp, I think something's wrong:

nddrylliog commented 12 years ago

It's giving very good results now :)

greut commented 12 years ago

Sweet!

greut commented 12 years ago

The reason why I redid the HTML view:

In query (c) we expect from you to list the asked statistics (avg height, avg weight, avg age) for every season of a coach who in total in his career he had at least XXX wins and YYY win percentage. For example, for a "good coach" who coached for 10 seasons you will have 10 rows in your resultset. — http://moodle.epfl.ch/mod/forum/discuss.php?d=167271#p375511