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:

SELECT email
FROM account
WHERE username='bob'

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?

Task 3: Passwords

Alice claims that someone else changed her password. Was her password changed? Was it done by someone else?

Someone claims to be Bob and asks via support for a password reset. Is the request valid? (Hint: You have the ip-address of the requester)