SQL Requests Game

[Habilis]

Hiya all.

Let's play a SQL game.

You from your Experience tell me what Data from SQL you would need to have.

I will provide the SQL code of the query to extract that information.

(A lot of fun game)

For example :

You : "I want to spy on my Event GMs and Higher GMs to control the abuse of @commands"

Habilis :

SELECT
ac.`atcommand_date`
,ac.`account_id`
,ac.`char_id`
,ac.`char_name`
,l.`group_id`
,ac.`map`
,ac.`command`
FROM `atcommandlog` ac
LEFT JOIN `login` l ON l.`account_id` = ac.`account_id`
WHERE
l.`group_id` >= 30
AND ac.`command` NOT LIKE "@emotion%"
AND ac.`command` NOT LIKE "@reload%"
AND ac.`command` NOT LIKE "@refresh%"
AND ac.`command` NOT LIKE "@go %"
AND ac.`command` NOT LIKE "@jump%"
AND ac.`command` NOT LIKE "@warp%"
ORDER BY ac.`atcommand_id` DESC
LIMIT 200;




Selects :

• DateTime (when copmmand was executed)
  
• Account & Char Ids (If not needed remove them)
  
• Char name
  
• GM level (if not needed remove it)
  
• map on which command was executed
  
• command
  

Features :

• Extracts 200 last @commands executed
  
• Extracts only for GM of 30 level and above (No normal players)
  
• Ignores commans (@Emotion, @reload, @refresh, @go, @jump, @warp)
  

If you like You may make this query focus only on particular comamnds to track particularly abusable commands

WHERE `command` LIKE "@item%"


Don't be afraid to ask lika anything Habilis's level of knowlege in SQL is simply GURU

Queries of any complexity and any difficulty   :B):

Let's play

 

[bWolfie]

You: I want to delete my entire server while it is running.
Habilis: DROP DATABASE habilisro_rag;

 

[Habilis]

You: I want to delete my entire server while it is running.
Habilis: DROP DATABASE habilisro_rag;

Nah, If I ever Hack into someones game server database.

I would spawn cards and sell them for real money $$$  (Not like Habilis already did such an awfull atrocity  :B): )

But, it is way more profitable than being destructive to someone's database  :blush:

Without making my account a GM, without spawning them through @comamnd, without logs or traces  :B):

Anyways, feel like playing???

 

[Habilis]

On teh Russian Forum, I've been requested a query to find a particular item

and to find someone who has the most of that item

DELIMITER //
set @nameid = 601;


SELECT
c.account_id
,c.name
,inv.inventory_amnt
,crt.cart_amnt
,mail.mail_amnt
,stor.storage_amnt
,(ifnull(inv.inventory_amnt, 0) + ifnull(crt.cart_amnt, 0) + ifnull(mail.mail_amnt, 0) + ifnull(stor.storage_amnt, 0)) AS tot_amnt
FROM `char` c
LEFT JOIN (SELECT char_id, SUM(amount) AS inventory_amnt FROM inventory WHERE nameid=@nameid GROUP BY char_id) inv
ON inv.char_id=c.char_id
LEFT JOIN (SELECT char_id, SUM(amount) AS cart_amnt FROM cart_inventory WHERE nameid=@nameid GROUP BY char_id) crt
ON crt.char_id=c.char_id
LEFT JOIN (SELECT dest_id AS char_id, SUM(amount) AS mail_amnt FROM mail WHERE nameid=@nameid GROUP BY dest_id) mail
ON mail.char_id=c.char_id
LEFT JOIN (SELECT account_id, SUM(amount) AS storage_amnt FROM storage WHERE nameid=@nameid GROUP BY account_id) stor
ON stor.account_id=c.account_id

ORDER BY tot_amnt DESC;

//
DELIMITER ;




Here is an example of what is being selected

+------------+------------+----------------+-----------+-----------+--------------+----------+
| account_id | name | inventory_amnt | cart_amnt | mail_amnt | storage_amnt | tot_amnt |
+------------+------------+----------------+-----------+-----------+--------------+----------+
| 2000002 | Wiwrtas | 19 | NULL | NULL | NULL | 19 |
| 2000002 | Strelok | 9 | NULL | NULL | NULL | 9 |
| 2000000 | test | NULL | NULL | NULL | NULL | 0 |
| 2000002 | aaaaa | NULL | NULL | NULL | NULL | 0 |
| 2000003 | ladyAdA | NULL | NULL | NULL | NULL | 0 |
| 2000000 | toto | NULL | NULL | NULL | NULL | 0 |
| 2000000 | Habilis | NULL | NULL | NULL | NULL | 0 |
| 2000001 | tata | NULL | NULL | NULL | NULL | 0 |
| 2000002 | asdasfasfd | NULL | NULL | NULL | NULL | 0 |
| 2000004 | tatatatata | NULL | NULL | NULL | NULL | 0 |
+------------+------------+----------------+-----------+-----------+--------------+----------+
10 rows in set (0.01 sec)




Item 601 (flywing) in Inventory, Cart, Mail, Kafra storage(Attention Kafra storage is per account, there fore all Characters under same account will have SAME value there) and a total, I'm using it to sort and find the leader (Who has the most of that item)..

I didn't include Guild storage sincie it should be a separate query

Attention doesn't work 100% with cards, cards may be in slots (not taken into account here). Maybe will create separate query for cards....

UPD : I just though you may wan't to extract only users that have this ite? Then use:

WHERE tot_amnt > 0