Advanced Database Query Exercise: Difference between revisions
imported>Hendrik Brummermann Created page with "This is an advanced version of the basic Database Query Exercise. You should probably do that one first. The same instructions apply: : Some tasks can be solved with one si..." |
imported>Hendrik Brummermann No edit summary |
||
| (15 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
This is an advanced version |
This is an advanced version focusing on RPG related queries. You should probably do basic [[Database Query Exercise]] first |
||
The same instructions apply: |
The same instructions apply: |
||
| Line 10: | Line 10: | ||
== Example == |
== Example == |
||
The itemids and names of all items that have been touched by alice and malice in the last two days? |
|||
<source lang="sql"> |
|||
SELECT DISTINCT i3.itemid, i3.param1 |
|||
FROM itemlog As i1, itemlog As i2, itemlog i3 |
|||
WHERE i1.itemid = i2.itemid AND i1.itemid = i3.itemid |
|||
AND i1.source = 'alice' AND i2.source='malice' AND i3.event = 'register' |
|||
AND i1.timedate > '2011-09-11' AND i2.timedate > '2011-09-11' |
|||
ORDER BY i1.param1 |
|||
</source> |
|||
(Assuming today is 2011-09-14) |
|||
== Task 1: Players == |
|||
What is the account name of the character alice? |
|||
Which other characters belong to that account? |
|||
What player created a new character looking like alice? |
|||
== Task 2: Kills == |
|||
Who was directly or indirectly involved in the recent player killing of alice? |
|||
In which zones were black dragons killed in the last 3 days? |
|||
== Task 3: Items == |
|||
Which items did alice drop after 2011-09-14 12:56:31? |
|||
Who owns those items now? |
|||
Who owned the items in between? |
|||
Have those items been given away or was something trade against them using the trade system? |
|||
Was something traded against the contraband using the old trading style of putting items on the floor instead? |
|||
Were third players used to conceal trades? |
|||
== Task 4: Be creative == |
|||
Explain two approaches how to detect bots and how to verify the discovery. |
|||
Latest revision as of 10:30, 11 September 2011
This is an advanced version focusing on RPG related queries. You should probably do basic Database Query Exercise first
The same instructions apply:
- 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.
With the following addition: Make sure that you use the existing indices on large tables such as gameEvents and itemlog.
Example
The itemids and names of all items that have been touched by alice and malice in the last two days?
<source lang="sql"> SELECT DISTINCT i3.itemid, i3.param1 FROM itemlog As i1, itemlog As i2, itemlog i3 WHERE i1.itemid = i2.itemid AND i1.itemid = i3.itemid AND i1.source = 'alice' AND i2.source='malice' AND i3.event = 'register' AND i1.timedate > '2011-09-11' AND i2.timedate > '2011-09-11' ORDER BY i1.param1 </source>
(Assuming today is 2011-09-14)
Task 1: Players
What is the account name of the character alice?
Which other characters belong to that account?
What player created a new character looking like alice?
Task 2: Kills
Who was directly or indirectly involved in the recent player killing of alice?
In which zones were black dragons killed in the last 3 days?
Task 3: Items
Which items did alice drop after 2011-09-14 12:56:31?
Who owns those items now?
Who owned the items in between?
Have those items been given away or was something trade against them using the trade system?
Was something traded against the contraband using the old trading style of putting items on the floor instead?
Were third players used to conceal trades?
Task 4: Be creative
Explain two approaches how to detect bots and how to verify the discovery.