Teaching databases and SQL with AzerothCore

[lhumblet]

Hi everybody,

I am a teacher in computer science in secondary school (students between 14 yo and 16 yo) and I would like to use the AzerothCore database as an existing game server to teach RMDBS and SQL.

Unfortunately I do not know your project much (I played WoW ages ago tho) and diving into it feels a bit overwhelming as there are a lot of things in there to analyse and understand.

So my request is the following one: what cool queries could I use onto your database to teach RBDBS and SQL to my students?

Some examples of such queries would be:

• display all the regions names for the given continent;
• display all the NPCs for a given town;
• show how many NPCs are in area X;
• display quests for a given subject/level/type of player;
• ...

The queries would be simple (SELECT, INSERT, UPDATE and DELETE with some LEFT/RIGHT/INNER JOIN) and it could involve the creation of new tables.

The idea would be to work with my students to analyse and create precise queries to return data that would help them make sense and understand the broad principles behind databases.

So could anybody please help me build a set of beginner-friendly queries to apply to the AzerothCore database to show and teach the magic of SQL?

Many thanks and happy new year!
Laurent

[55Honey]

Hello Laurent,
thanks for reaching out. That's a fabulous idea and I am sure your pupils will prefer the choice over the average company or class database.

Region names aren't stored in the DB, those are in DBC files. NPCs in a certain area/location would require providing the map and min/max x/y coordinates to simulate a rectangle. Here's an example with gameobjects (chests, mining/herb nodes, etc):

SELECT * FROM gameobject WHERE map = 1 AND position_x > -100 AND position_x < 100 AND position_y > 200 AND position_y < 300;

I can think of some other simple examples from my head:

• select all creatures of a given entry in a certain map:

SELECT * FROM creature WHERE 385 IN (id1, id2, id3) AND map = @map;

• display quests for a given level of player which requires reputation with a faction:

SELECT * FROM quest_template WHERE MinLevel < 60 AND RequiredFactionId1 = 509;

Feel free to look into https://www.azerothcore.org/wiki/database-world for an explanation of how tables interact.

[elthehablo]

Hi! Seconding what Honey mentioned. The loot tables are also quite interesting and you can use some interesting joins with e.g. creature template to get some interesting information from it

[elthehablo]

These kind of joins can also be interesting when looking for certain loot inside instances

[Nefertumm]

You could also make them look for the name of a spawned creature in world. This could be done by doing a join between the creature table and creature_template using creature.id1 = creature_template.entry as join condition. You could use the following query:

SET @GUID := XXXX;
SELECT c.guid, cr.entry, cr.name FROM `creature` AS c
INNER JOIN creature_template AS cr ON c.id1 = cr.entry
WHERE c.guid = @GUID;

In case you don't know, the table creature holds information about all the npcs in world (spawned) with an unique identifier (guid).
The table creature_template as the name might suggest, contains information about the description of each creature. For a creature to be spawned, it must exists in creature_template.

This is a pretty fun project to work with, specially for teenagers, as every change they may do, they could see it applied on the server. I'm glad you chose, or you're gathering information to work with it.
You could find a lot of information about the world database in the wiki. You also have the characters and auth database, that could be interesting to work with. For contributions, we also have SQL Standards that are pretty good if you want to take a look.