etape3.sql

-- Obtenir la liste des monsters capturés classé par la date de capture (Du plus récent au plus ancien)
SELECT * FROM Monster WHERE capturee = TRUE ORDER BY date_capture DESC;
SELECT monster_id, capturee, date_capture FROM Monster WHERE capturee = TRUE ORDER BY date_capture DESC;

-- Obtenir le nombre de monsters par type, vos deux champs devront être nommé respectivement “Type” et “Nombre”
SELECT Type.name AS Type, COUNT(Monster.monster_id) AS Nombre
FROM Monster
LEFT JOIN Type ON Monster.type_id = Type.type_id
GROUP BY Type.name;

-- Obtenir pour chaque monster, son dresseur en affichant son nom et prénom dans le même champ nommé “dresseur” :  si le monster n’est associé à aucun dresseur, vous devez afficher “aucun” dans ce même champ.
SELECT Monster.nom, 
       IFNULL(CONCAT(Dresseur.nom, ' ', Dresseur.prenom), 'aucun') AS dresseur
FROM Monster
LEFT JOIN Dresseur ON Monster.dresseur_id = Dresseur.dresseur_id;

-- Obtenir la liste des ventes : chaque vente doit mentionner le magasin concerné, le nom du monster vendu, le dresseur avec son nom et prénom, le prix et la date de vente classée par la date de vente décroissant (Du plus récent au plus ancien)
SELECT Magasin.nom AS Magasin, 
       Monster.nom AS Monster_vendu, 
       CONCAT(Dresseur.nom, ' ', Dresseur.prenom) AS Dresseur, 
       Vente.prix, 
       Vente.date_vente
FROM Vente
LEFT JOIN Magasin ON Vente.magasin_id = Magasin.magasin_id
LEFT JOIN Monster ON Vente.monster_id = Monster.monster_id
LEFT JOIN Dresseur ON Vente.dresseur_id = Dresseur.dresseur_id
ORDER BY Vente.date_vente DESC;

-- Obtenir le chiffre d'affaires total par magasin : vous devez afficher les résultats avec un champ “Magasin” et un autre champ nommé “CA”.
SELECT Magasin.nom AS Magasin, SUM(Vente.prix) AS CA
FROM Vente
JOIN Magasin ON Vente.magasin_id = Magasin.magasin_id
GROUP BY Magasin.nom;

-- Obtenir les monsters ayant effectué ou non des quêtes. Vous devez obtenir également la date à laquelle elle a été effectuée tout cela classé par de la plus récente à la plus plus ancienne.
SELECT 
    Monster.nom AS nom_monster,
    ParticipationQuete.date_participation AS date_participation,
    IFNULL(ParticipationQuete.id_quete, 'Non participé') AS a_participe_quete
FROM Monster
LEFT JOIN ParticipationQuete ON Monster.monster_id = ParticipationQuete.id_monster
ORDER BY ParticipationQuete.date_participation DESC;


-- Obtenir la liste de tous les événements même sans participant et combats : pour chaque évènement on souhaite avoir le nombre de monster qui y participe, l'arène , le nombre d'équipement disponible pour ces évènement et le nombre de combat effectués. Tout cela devra être classé par le nombre de monster participant croissant (du plus petit au plus grand)
SELECT 
    Evenement.evenement_id,
    Evenement.nom AS nom_evenement,
    COUNT(DISTINCT ParticipationEvenement.monster_id) AS nombre_monsters_participants,
    Arene.nom AS nom_arene,
    COUNT(DISTINCT Equipement.equipement_id) AS nombre_equipements_disponibles,
    COUNT(DISTINCT Combat.combat_id) AS nombre_combats_effectues
FROM Evenement
LEFT JOIN ParticipationEvenement ON Evenement.evenement_id = ParticipationEvenement.evenement_id
LEFT JOIN Combat ON Evenement.evenement_id = Combat.evenement_id
LEFT JOIN Arene ON Evenement.arene_id = Arene.arene_id
LEFT JOIN Equipement ON Evenement.arene_id = Equipement.arene_id
GROUP BY Evenement.evenement_id, Evenement.nom, Arene.nom
ORDER BY nombre_monsters_participants ASC;



-- Obtenir la liste des monsters de type feu n’ayant pas été capturés, disposant d’un niveau compris entre 2 et 5 et lui restant plus de 400 de point de vie. Vous devez classer ce résultat du plus petit nombre de points de vie au plus grand.

--Remarque , je modifie la terre en feu
select type_id from monster where monster_id = 6;
update monster set type_id = 1 where monster_id = 6;

SELECT Monster.monster_id, Monster.nom, Monster.capturee, Monster.niveau, Monster.point_vie,Type.name
FROM Monster
LEFT JOIN Type ON Monster.type_id = Type.type_id
WHERE Type.name = 'Feu' AND
      capturee = FALSE AND
      niveau BETWEEN 2 AND 5 AND
      point_vie > 400
ORDER BY point_vie;

--enable Secrète

-- Obtenir la liste des espèces ayant au moins 4 monsters.
SELECT Espece.*, COUNT(Monster.monster_id) AS Nombre_monsters
FROM Espece
LEFT JOIN Monster ON Espece.espece_id = Monster.espece_id
GROUP BY Espece.espece_id
HAVING COUNT(Monster.monster_id) >= 4;

-- Les requetes supp

-- Obtenir le nombre moyen de points de vie des monsters capturés par type :
SELECT 
    Type.name AS Type,
    AVG(Monster.point_vie) AS Moyenne_Points_Vie
FROM Monster
JOIN Type ON Monster.type_id = Type.type_id
WHERE capturee = TRUE
GROUP BY Type.name;

-- Obtenir le prix minimum de vente pour chaque type de monster :
SELECT 
    Type.name AS Type,
    MIN(Vente.prix) AS Prix_Minimum
FROM Vente
JOIN Monster ON Vente.monster_id = Monster.monster_id
JOIN Type ON Monster.type_id = Type.type_id
GROUP BY Type.name;

-- Obtenir le nombre de quêtes effectuées par chaque monster, classé par ordre décroissant du nombre de quêtes :
SELECT 
    CONCAT(monster.nom, ' ') AS monster,
    COUNT(ParticipationQuete.participation_quete_id) AS Nombre_Quetes
FROM monster
LEFT JOIN ParticipationQuete ON monster.monster_id = ParticipationQuete.id_monster
GROUP BY monster.nom
ORDER BY Nombre_Quetes DESC;


-- Obtenir la somme totale des points de vie de tous les monsters :
SELECT SUM(point_vie) AS Somme_Points_Vie
FROM Monster;

-- Obtenir les dresseurs qui sont professeurs
SELECT * FROM Dresseur WHERE est_professeur;


--Obtenir qui a gagné le combat avec le nombre de point de vie
SELECT
    Combat.date_debut,
    Monster1.nom AS Nom_Monster_1,
    Monster2.nom AS Nom_Monster_2,
    CASE
        WHEN Monster1.point_vie > Monster2.point_vie THEN CONCAT(Monster1.nom, ' a gagné')
        WHEN Monster1.point_vie < Monster2.point_vie THEN CONCAT(Monster2.nom, ' a gagné')
        ELSE 'Match nul'
    END AS Resultat
FROM Combat
INNER JOIN Monster AS Monster1 ON Combat.id_monster_1 = Monster1.monster_id
INNER JOIN Monster AS Monster2 ON Combat.id_monster_2 = Monster2.monster_id;

--Obtenir qui a gagné le combat avec la date_fin
SELECT
    Combat.date_debut,
    Combat.date_fin,
    Monster1.nom AS Nom_Monster_1,
    Monster2.nom AS Nom_Monster_2,
    CASE
        WHEN Combat.date_fin IS NOT NULL THEN
            CASE
                WHEN Monster1.date_capture < Monster2.date_capture THEN CONCAT(Monster1.nom, ' a gagné')
                WHEN Monster1.date_capture > Monster2.date_capture THEN CONCAT(Monster2.nom, ' a gagné')
                ELSE 'Match nul'
            END
        ELSE 'En cours'
    END AS Resultat
FROM Combat
INNER JOIN Monster AS Monster1 ON Combat.id_monster_1 = Monster1.monster_id
INNER JOIN Monster AS Monster2 ON Combat.id_monster_2 = Monster2.monster_id;