KAAEMLOTT BDD Atelier

request.sql

# Ecrire les requêtes qui permettent d afficher 

# Le prénom, nom et âge des personnages

SELECT firstname, lastname, age FROM person;
+-------------+---------------+-----+
| firstname   | lastname      | age |
+-------------+---------------+-----+
| Arthur      | Pendragon     |  35 |
| Guenièvre   | NULL          |  30 |
| Merlin      | NULL          | 850 |
| Perceval    | NULL          |  36 |
| Caradoc     | NULL          |  32 |
| Calogrenant | NULL          |  44 |
| Leodagan    | NULL          |  47 |
| Lancelot    | Du Lac        |  33 |
| Elias       | De Kelliwic h |  52 |
| Mevanwi     |               |  28 |
| Yvain       |               |  23 |
+-------------+---------------+-----+
11 rows in set (0,00 sec)

# Le prénom, nom des personnages ainsi que leur royaume, uniquement pour ceux étant reliés à un royaume

SELECT firstname, lastname, name AS kingdom FROM person JOIN kingdom ON kingdom.id=person.kingdom_id;
+-------------+-----------+-----------+
| firstname   | lastname  | kingdom   |
+-------------+-----------+-----------+
| Arthur      | Pendragon | Logre     |
| Calogrenant | NULL      | Caledonie |
| Guenièvre   | NULL      | Carmelide |
| Leodagan    | NULL      | Carmelide |
| Yvain       |           | Carmelide |
| Caradoc     | NULL      | Vannes    |
| Mevanwi     |           | Vannes    |
| Perceval    | NULL      | Galles    |
+-------------+-----------+-----------+
8 rows in set (0,00 sec)

# La même chose en incluant tous les personnages

SELECT firstname, lastname, name AS kingdom FROM person LEFT JOIN kingdom ON kingdom.id=person.kingdom_id;
+-------------+---------------+-----------+
| firstname   | lastname      | kingdom   |
+-------------+---------------+-----------+
| Arthur      | Pendragon     | Logre     |
| Guenièvre   | NULL          | Carmelide |
| Merlin      | NULL          | NULL      |
| Perceval    | NULL          | Galles    |
| Caradoc     | NULL          | Vannes    |
| Calogrenant | NULL          | Caledonie |
| Leodagan    | NULL          | Carmelide |
| Lancelot    | Du Lac        | NULL      |
| Elias       | De Kelliwic h | NULL      |
| Mevanwi     |               | Vannes    |
| Yvain       |               | Carmelide |
+-------------+---------------+-----------+
11 rows in set (0,00 sec)


# La moyenne de l âge des personnages

SELECT AVG(age) FROM person;
+----------+
| AVG(age) |
+----------+
| 110.0000 |
+----------+
1 row in set (0,01 sec)

# La moyenne est un peu haute non ? Récupérer alors la moyenne de tous les personnages n’ayant pas le rôle de magicien

SELECT AVG(age) FROM person JOIN role ON role.id = person.role_id WHERE role != 'magicien';
+----------+
| AVG(age) |
+----------+
|  35.7143 |
+----------+
1 row in set (0,00 sec)

# Le nombre de personnage par royaume (inclure les royaumes n’ayant pas de personnage)

SELECT name AS kingdom, COUNT(person.id) AS nb_person FROM kingdom LEFT JOIN person ON person.kingdom_id = kingdom.id GROUP BY name;
+-----------+-----------+
| kingdom   | nb_person |
+-----------+-----------+
| Logre     |         1 |
| Caledonie |         1 |
| Carmelide |         3 |
| Vannes    |         2 |
| Galles    |         1 |
| Aquitaine |         0 |
+-----------+-----------+
6 rows in set (0,00 sec)

# La moyenne de l’âge par rôle

SELECT AVG(age) AS average, role FROM person JOIN role ON role.id=person.role_id GROUP BY role;
+----------+-----------+
| average  | role      |
+----------+-----------+
|  42.0000 | roi       |
|  31.0000 | chevalier |
| 451.0000 | magicien  |
+----------+-----------+
3 rows in set (0,00 sec)

# La liste de tous les personnages avec leur rôle et royaume éventuels

SELECT firstname, lastname, role, name AS kingdom FROM person LEFT JOIN role ON role.id=person.role_id LEFT JOIN kingdom ON kingdom.id=person.kingdom_id; 

+-------------+---------------+-----------+-----------+
| firstname   | lastname      | role      | kingdom   |
+-------------+---------------+-----------+-----------+
| Arthur      | Pendragon     | roi       | Logre     |
| Guenièvre   | NULL          | NULL      | Carmelide |
| Merlin      | NULL          | magicien  | NULL      |
| Perceval    | NULL          | chevalier | Galles    |
| Caradoc     | NULL          | chevalier | Vannes    |
| Calogrenant | NULL          | roi       | Caledonie |
| Leodagan    | NULL          | roi       | Carmelide |
| Lancelot    | Du Lac        | chevalier | NULL      |
| Elias       | De Kelliwich  | magicien  | NULL      |
| Mevanwi     |               | NULL      | Vannes    |
| Yvain       |               | chevalier | Carmelide |
+-------------+---------------+-----------+-----------+
11 rows in set (0,00 sec)

# Les royaumes ayant au moins deux sujets
SELECT name AS kingdom, COUNT(person.id) AS nb_person FROM kingdom LEFT JOIN person ON person.kingdom_id = kingdom.id GROUP BY name HAVING nb_person >= 2;
+-----------+-----------+
| kingdom   | nb_person |
+-----------+-----------+
| Carmelide |         3 |
| Vannes    |         2 |
+-----------+-----------+
2 rows in set (0,00 sec)