Database Query Exercise

This articles gives you a number of task to solve based on a Stendhal Database.

TODO: Explain how to get a Stendhal Database

Some tasks can be solved with one single query, other tasks may require a set of queries with human interaction in between. Please point out what to look for in the result set and anything else that should be kept in mind.

If possible explain how the results can be validated.

Example

Get the email address of the account bob:

<source lang="sql"> SELECT email FROM account WHERE username='bob' </source>

Task 1: Statistics (one table queries)

How many users have been online on 2011-09-11 12:00:00?

How many users have been online in average per hour on 2011-09-11?

Task 2: Logins (two table queries)

On which day and at which time has the user bob logged in last?

How many failed logged in attempts have there been since then?

Is it likely that someone else has logged into alice's account in the last 7 days?

If yes, what other accounts has that person logged in to?