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
- 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 :
- Initialize the level to 1 when changing the name of a player.
- Add a level to the level when adding a mount to a player.
- Set the level to 70 when creating a death knight.
- Prohibit the deletion of a character of lvl 100 or more.
- Prohibit the choice of Druid for the Alliance and Paladin for the Horde.
- Offer a random mount when creating a character.
Part 3
Deliverable : warcraft/mission4.pdf et warcraft/mission4.sql
- 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 - Add the foreign key and the primary keys. We will take pet_id for the pets.
- Identify the reflexives and add the constraints.
- Allow players to have several pets.
- Make a dump
Part 4
Deliverable : warcraft/mission4.pdf
- 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:
- Randomly assign a pet to several players. (RAND)
- Randomly assign several mounts to several players.
Perform the following functions:
- Assign a pet to a player based on his id
- Assign an extra mount to the players who have the least mount.