SQL – Views, triggers and stored procedures

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

Intro

  • 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
    https://gitlab.com/cherryclass/wow7/blob/master/pet.sql
    https://gitlab.com/cherryclass/wow7/blob/master/petype.sql
  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 :
DELIMITER //
CREATE PROCEDURE pet()
BEGIN
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 WHILE;
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.