SQL – Views, triggers and stored procedures

We can retrieve the following database : https://gitlab.com/cherryclass/wow7/blob/slam3/warcraft.sql


  • Create the list of views, triggers and procedures performed, indicating the parameters and the expected result.

Part 1

Deliverable : warcraft/mission4.pdf

  1. Create a race_view that displays all the fields of a race.
    Insert two new races with this view.
    Delete the last race with this view.

Part 2

Deliverable : warcraft/mission4.pdf

Réaliser les triggers suivants :

  1. Initialize the level to 1 when changing the name of a player.
  2. Add a level to the level when adding a mount to a player.
  3. Set the level to 70 when creating a death knight.
  4. Prohibit the deletion of a character of lvl 100 or more.
  5. Prohibit the choice of Druid for the Alliance and Paladin for the Horde.
  6. Offer a random mount when creating a character.

Part 3

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

  1. Propose a schema to add the pet and its type to the database.
    Insert the pet and their type in the database
  2. Add the foreign key and the primary keys. We will take pet_id for the pets.
  3. Identify the reflexives and add the constraints.
  4. Allow players to have several pets.
  5. Make a dump

Part 4

Deliverable : warcraft/mission4.pdf

  1. Save the following procedure :
DECLARE v1 INT unsigned DEFAULT 200;
  WHILE v1 > 0 DO
    insert into pets_members values (FLOOR(1+ RAND() * (963- 1+1)),FLOOR(1+ RAND() * (510- 1+1)));
    SET v1 = v1 - 1;
END //

2 – Call it to assign pets to members in the table pets_members (id_pet,id_members). Check the insertion.

call pet();

3 – Display the player who has the most pets
4 – Display the number of pet by player
5 – Display the number of pets per player and per type.

Part 5

Deliverable : warcraft/mission4.pdf

Perform the following procedures:

  1. Randomly assign a pet to several players. (RAND)
  2. Randomly assign several mounts to several players.

Perform the following functions:

  1. Assign a pet to a player based on his id
  2. Assign an extra mount to the players who have the least mount.