SQL – Data query

Intro

  • Create the deployment diagram

Part 1

Deliverable : warcraft/mission3.pdf

  1. Deploy your database on an AWS MariaDB or AlwaysData instance.
  2. Use the adminer interface to view the tables of your wow database.
  3. Display all classes.
  4. Display the race that has the id 5.
  5. Show the power type of the Mage and Shaman class.
  6. Show the available classes for each race.
  7. Show available races for each class.
  8. Show the races of the horde.
  9. Show the races that are not of the horde.
  10. Show the classes available in the alliance.
  11. Show the names of the members sorted by increasing level.
  12. Show the names of the different classes and races of the members in your database.

 

Part 2

Deliverable : warcraft/mission3.pdf et warcraft/mission3.sql

  1. Show characters who registered between 12/04/2021 and 15/04/2023
  2. Show characters who registered in 2022
  3. Show characters who registered more than 6 months ago.
  4. Show the number of classes
  5. Show the number of races
  6. Show the number of members
  7. Show the average level of the members
  8. Show the name of the different guilds.
  9. Show the average level of the members who are above level 15.
  10. Display the total achievement_points by member role (DPS, TANK..).
  11. Display the total achievement_points by guild, sorted by highest score.
  12. Insert player mounts in a new mount_members table. see link below.
  13. Replace the name column by the id of the corresponding member.
  14. Display the number of mounts per player with the name of the player, for those who have at least two.
  15. Make a dump of the database.

https://gitlab.com/cherryclass/wow7/blob/master/mount1.sql
https://gitlab.com/cherryclass/wow7/blob/master/mount2.sql
https://gitlab.com/cherryclass/wow7/blob/master/mount3.sql