-- show engines;
DROP DATABASE IF EXISTS Pokemon; -- supprime la base avant
CREATE DATABASE Pokemon;
USE Pokemon; -- se connecter à la base

DROP TABLE IF EXISTS Defense;
DROP TABLE IF EXISTS Arene;
DROP TABLE IF EXISTS Apparition ;
DROP TABLE IF EXISTS Emplacement;
DROP TABLE IF EXISTS Pokemon;
DROP TABLE IF EXISTS Joueur ;
DROP TABLE IF EXISTS Equipe;

CREATE TABLE Equipe(
	id INT PRIMARY KEY,
	nom VARCHAR(9),
	couleur VARCHAR(5));
    
CREATE TABLE Joueur(
	pseudonyme VARCHAR(30) PRIMARY KEY,
	personnage VARCHAR(30) NOT NULL UNIQUE,
	sexe CHAR(1),
	niveau SMALLINT DEFAULT 0 NOT NULL,
	idEquipe INT,
	FOREIGN KEY fk_equipe(idEquipe) REFERENCES Equipe(id) ON DELETE CASCADE);
    
CREATE TABLE Pokemon(
	id INT PRIMARY KEY,
	nom VARCHAR(30),
	espece VARCHAR(20),
	pointCombat INT, -- déjà en contrainte donnee sur CHECK
	idJoueur VARCHAR(30),
	FOREIGN KEY fk_joueur(idJoueur) REFERENCES Joueur(pseudonyme),
	CONSTRAINT check_pointCombat CHECK (pointCombat >= 0));	
    
CREATE TABLE Emplacement(
	id INT PRIMARY KEY,
	latitude DECIMAL(12,10) NOT NULL,
	longitude DECIMAL(13,10) NOT NULL,
	CONSTRAINT check_latitude CHECK (latitude BETWEEN -90 AND 90),
	CONSTRAINT check_longitude CHECK (longitude BETWEEN -180 AND 180));
    
CREATE TABLE Apparition(
	idPokemon INT, 
	idEmplacement INT,
	horaire DATE DEFAULT(CURRENT_DATE()), -- fonction date par défaut mise entre parenthèse car 
	duree INT,
	CONSTRAINT pk_Apparition PRIMARY KEY (idPokemon, idEmplacement),
	FOREIGN KEY fk_pokemon(idPokemon) REFERENCES Pokemon(id),
	FOREIGN KEY fk_emplacement(idEmplacement) REFERENCES Emplacement(id));
    
CREATE TABLE Arene(
	id INT PRIMARY KEY, 
	nom VARCHAR(30), 
	idEmplacement INT,
	FOREIGN KEY fk_emplacement2(idEmplacement) REFERENCES Emplacement(id));	-- fk_emplacement deja utilisé
    
CREATE TABLE Defense(
	idEquipe INT, 
	idArene INT, 
	dateControle DATE,
	CONSTRAINT pk_Defense PRIMARY KEY (idEquipe, idArene, dateControle),
	FOREIGN KEY fk_equipe2(idEquipe) REFERENCES Equipe(id), -- fk_equipe déjà utilisé
	FOREIGN KEY fk_arene(idArene) REFERENCES Arene(id));
    
INSERT INTO Equipe VALUES (1, 'Intuition', 'Jaune');
INSERT INTO Equipe VALUES (2, 'Sagesse', 'Bleu');
INSERT INTO Equipe VALUES (3, 'Bravoure', 'Rouge');
INSERT INTO Joueur VALUES ('Shadow', 'Smith', 'F', 10, 1);
INSERT INTO Joueur VALUES ('Root', 'Alice', 'F', 20, 2);
INSERT INTO Joueur VALUES ('Admin', 'Bob', 'M', 1, 1);
INSERT INTO Pokemon VALUES (1, 'Bulbizarre', 'Graine', 1071, 'Shadow');
INSERT INTO Pokemon VALUES (25, 'Pikachu', 'Souris', 887, 'Root');
INSERT INTO Pokemon VALUES (107, 'Tygnon', 'Puncheur', 204, 'Shadow');
INSERT INTO Pokemon VALUES (103, 'Noadkoko', 'Fruitpalme', 190, 'Admin');
INSERT INTO Pokemon VALUES (150, 'Mewtwo', 'Génétique', 4144, 'Root');
INSERT INTO Emplacement VALUES (1, 49.0350369, 2.0696998);
INSERT INTO Emplacement VALUES (2, 48.857848, 2.295253);
INSERT INTO Emplacement VALUES (3, -74.0445, 40.6892);
INSERT INTO Apparition VALUES (1, 2, '2016-10-09', 10);
INSERT INTO Apparition VALUES (25, 1, '2016-09-01', 20);
INSERT INTO Apparition VALUES (107, 3, '2016-10-02', 5);
INSERT INTO Apparition VALUES (103, 1, '2016-10-25', 15);
INSERT INTO Apparition VALUES (25, 3, '2016-10-25', 3);
INSERT INTO Arene VALUES (1, 'Liberte', 3);
INSERT INTO Arene VALUES (2, 'Eisti', 1);
INSERT INTO Arene VALUES (3, 'Star', 2);
INSERT INTO Defense VALUES (1, 1, '2016-10-10');
INSERT INTO Defense VALUES (1, 2, '2016-09-01');


-- ------------------------------------- TD4 ---------------------------------------------

-- exercice 1 --
-- q°1 : insérer quelques données dans la table Pokemon : 19, Rattata, Souris, 20, Admin et 39, Rondoudou, Bouboule, 4145, Moustache
INSERT INTO Pokemon VALUES(19, 'Ratata', 'Souris', 20, 'Admin');
-- INSERT INTO Pokemon VALUES(39, 'Rondoudou', 'Bouboule', 4145, 'Moustache'); -- ne fonctionne pas car pas de personnage Moustache

-- q°2
INSERT INTO Defense VALUES (3, 2, '2016-10-10');

-- q°3
-- INSERT INTO Defense VALUES (2, 3); -- ne fonctionne pas car il manque des données (date)

-- q°4
INSERT INTO Joueur VALUES ('Flavius', 'Ruth', 'M', 20, 2);
-- INSERT INTO Joueur VALUES ('Asterix', 'Ruth', 'M', 5, 1); -- ne fonctionne pas car double entrée pour Ruth, or joueur configuré pour être unique

-- q°5
UPDATE Joueur SET niveau = 15 WHERE sexe = 'F';

-- q°6
-- DELETE FROM Pokemon WHERE espece LIKE '%fruit%' ;

-- q°7
-- DELETE FROM Joueur WHERE pseudonyme = 'Admin';



-- exo 2

-- q1 : Quel est l'arène dont le nom contient le mot "eisti" ?
SELECT *
FROM Arene
WHERE LOWER(nom) LIKE '%eisti%'
;

-- q2 : Quels sont les Pokémons dont le nom commence par la lettre p sans tenir compte de la casse ?
SELECT *
FROM Pokemon
-- WHERE LOWER(nom) LIKE 'p%' -- Analyse de l'expression rationnelle plus lente
-- WHERE LOWER(SUBSTR(nom, 1, 1)) = 'p' -- plus lent que LEFT
WHERE LOWER(LEFT(nom, 1)) = 'p'
;

-- q3 : Quels sont les pseudonymes des joueurs qui ne contiennent pas la lettre a ?
SELECT *
FROM Joueur
WHERE LOWER(pseudonyme) NOT LIKE 'personnage%a%'
;

-- q4 : Trier les Pokémons selon le point de combat en ordre décroissant.
SELECT *
FROM Pokemon
ORDER BY pointCombat DESC
;

-- q5 : Calculer la durée moyenne d'apparition des Pokémons.
SELECT AVG(duree)
FROM Apparition
;

-- q6 : Compter le nombre d'apparitions des Pokémons en octobre 2016.
SELECT COUNT(idPokemon)
FROM Apparition
-- WHERE horaire LIKE "2016-10-%" -- moins rapide
WHERE YEAR(horaire)=2016 AND MONTH(horaire)=10
;

-- q7 : Donner les noms des Pokémons qui sont de même espèce que Pikachu.
SELECT nom
FROM Pokemon
WHERE espece IN (SELECT espece FROM Pokemon WHERE LOWER(nom)="pikachu")
	AND LOWER(nom) <> "pikachu" -- != n'est pas standard SQL		pikachu ne doit pas être ré-affiché
;

-- q8 : Quels sont les joueurs dont le niveau est supérieur que le niveau moyen des joueurs ?
SELECT *
FROM Joueur
WHERE niveau > (SELECT AVG(niveau) FROM Joueur)
;

-- q9 : Quel est le Pokémon le plus fort ?
SELECT nom
FROM Pokemon
-- ORDER BY pointCombat DESC LIMIT 1 -- si 2 pokemon ont même nombre de pc , n'en affiche qu'un
WHERE pointCombat = (SELECT MAX(pointCombat) FROM Pokemon)
;

-- q10 : Augmenter de 2 le niveau de Shadow
UPDATE Joueur SET niveau=niveau+2
WHERE LOWER(pseudonyme)="shadow"
;

-- q11 : Sélectionner les pokémons qui ont apparu en septembre. Ecrire la requête sans utiliser le LIKE
SELECT idPokemon
FROM Apparition
WHERE MONTH(horaire)=9
;

SELECT nom
FROM Apparition A, Pokemon P
WHERE P.id = A.idPokemon
	AND MONTH(horaire)=9
;



-- ------------------------------------ TD 5 ------------------------------------






-- q1 Quelle est la durée de l'apparition la plus récente du Pokémon ?
SELECT duree
FROM apparition
WHERE horaire = (SELECT max(horaire)
				FROM apparition)
;

-- q2 Donner les noms des Pokémons qui ont le même point de combat et le même maître que 'Pikachu'.
SELECT nom
FROM pokemon
WHERE (pointCombat, idJoueur) = (SELECT pointCombat, idJoueur
								FROM pokemon
								WHERE LOWER(nom) ='pikachu')
AND LOWER(nom)<>'pikachu'
;

-- q3 Quel est le joueur dont le pseudonyme est plus long que tous les noms du personnage ?
SELECT pseudonyme
FROM joueur
WHERE length(pseudonyme) > (SELECT max(length(personnage))
							FROM joueur)
;

-- q4 fficher toutes les dates de prise de contrôle des arènes, ainsi que les horaires des apparitions des Pokémons.

SELECT DISTINCT dateControle
FROM defense
	UNION SELECT horaire
	FROM apparition
;

-- q5 Quelles sont les dates où il y a à la foi l'apparition d'un Pokémon et la prise de contrôle d'une arène ?
SELECT dateControle
FROM defense AS dateControleUnionHoraire UNION SELECT horaire
												FROM apparition
;

-- q6 Quels sont les joueurs dont le pseudonyme commence par la lettre A ou S et le personnage ne s'appelle pas Bob ?

SELECT pseudonyme
FROM joueur
-- WHERE (lower(pseudonyme) LIKE 'a%' OR lower(pseudonyme) LIKE '%s')
WHERE substr(lower(pseudonyme),1,1) IN ('a','s')
AND lower(personnage) <> 'bob'
;


-- q7 Quel sont les noms des Pokémons du joueur Admin ?

SELECT nom
FROM Pokemon
WHERE LOWER(idjoueur) = 'admin'
;

-- q8 Quels sont les joueurs qui n'ont jamais eu des Pokémons de type souris ?
SELECT pseudonyme
FROM joueur
WHERE pseudonyme NOT IN (SELECT DISTINCT idjoueur
						FROM pokemon
                        WHERE lower(espece) = 'souris')
;


-- q9 Quel est l'emplacement de l'arène EISTI ?
-- les jointures sont mieux que les requêtes imbriquées, plus rapides à executer
-- FROM A, B, C, D
-- WHERE A-B
-- 		B-C
-- 		C-D

SELECT latitude, longitude
FROM emplacement e, arene
WHERE e.id = idemplacement
AND LOWER(nom) = 'eisti'
;

-- q10 Quelle est l'équipe du joueur Shadow?
SELECT nom
FROM Equipe e, Joueur
WHERE e.id = idEquipe
AND LOWER(pseudonyme) = 'shadow'
;

-- q11 Quels sont les pokémons qui n'ont pas encore apparu ?
SELECT id, nom
FROM Pokemon
WHERE id NOT IN (SELECT DISTINCT(idPokemon)
				FROM Apparition)
;

-- q12 Quel est la latitude et la longitude de l'endroit où un Pokémon d'espèce Fruitpalme est apparu le 25 octobre 2016 ?
SELECT latitude, longitude
FROM emplacement e, apparition a, pokemon p
WHERE e.id = a.idEmplacement
AND a.idPokemon = p.id
AND LOWER(p.espece) = 'fruitpalme'
AND LOWER(a.horaire) = '2016-10-25'
;


-- q13 Quels sont les pokémons meilleurs que ceux de l'équipe Jaune ?
SELECT P.nom
FROM Pokemon P
WHERE P.pointCombat > (SELECT MAX(P2.pointCombat)
						FROM Pokemon P2, Joueur J, equipe e
                        WHERE e.id = J.pseudonyme
                        AND lower(e.couleur) = 'jaune')
;


-- q14 Combien de pokémons ont apparu dans l'arène EISTI ?
SELECT COUNT(DISTINCT AP.idPokemon) NbrPokemonApparus
FROM Apparition AP, Arene AR
WHERE AP.idEmplacement = AR.idEmplacement
AND LOWER(AR.nom) = 'eisti'
;


-- ------------------------------- TD6 ----------------------------------------

-- q1 Combien d'arènes l'équipe Intuititon a pris le contrôle ?
SELECT COUNT(DISTINCT D.idEquipe) NbArenePrises
FROM Defense D, Equipe E
WHERE D.idEquipe = E.id
AND lower(E.nom) = 'intuition'
;

-- q2 Quelles est la date de la première apparition de tygnon ?
SELECT MIN(A.horaire) TygnonAppar
FROM Apparition A, Pokemon P
WHERE P.id = A.idPokemon
AND LOWER(P.nom) = 'tygnon'
;

-- q3 Quels sont les joueurs qui ont participés à la défense de l'arène EISTI ?
SELECT pseudonyme
FROM Joueur j, Defense d, Arene a
WHERE j.idEquipe = d.idEquipe
AND a.id = d.idArene
AND LOWER(a.nom) = 'eisti'
;

-- q4 Compter le nombre de Pokémon de chaque joueur; Le pseudonyme du joueur
SELECT p.idJoueur Pseudonyme, COUNT(*) NbrPokemon
FROM Pokemon p
GROUP BY p.idJoueur;

-- q5 Quelle est la moyenne des points de combat de chaque espèce de pokémon ?
SELECT espece, CAST(AVG(pointcombat) AS DECIMAL(10,2))
FROM pokemon
GROUP BY espece
;

-- q6 Pour chaque joueur, afficher son pseudonyme, son équipe et le nombre de pokémon
SELECT j.pseudonyme, e.nom, COUNT(p.id)
FROM joueur j, equipe e, pokemon p
WHERE j.idEquipe = e.id
AND p.idJoueur = j.pseudonyme -- ne permet pas d'afficher les joueurs qui n'ont aucun pokémons
GROUP BY j.pseudonyme
;

-- q7 Pour chaque Pokémon, donner le nom et la durée totale d'apparition.
SELECT id, nom, SUM(duree) AS "totalTime"
FROM pokemon LEFT JOIN apparition ON id = idPokemon -- left = gauche, retourne toutes le lignes de la table à gauche du JOIN (pokemon), mais dans la table de droite (apparition), retourne uniquement les lignes correspondante à la condition
GROUP BY id
;
-- FROM apparition, pokemon
-- WHERE a.idPokemon = p.id -- n'affiche pas les pokemon qui ne sont pas apparus



-- q8Donner pour chaque arène son nom et la date de prise de contrôle la plus récente.
SELECT nom, MAX(dateControle)
FROM arene LEFT JOIN defense ON id = idArene -- arene et id ne contiennt pas de variables de même noms -> pas besoin de renommer avec a.nom, etc
GROUP BY nom
;


-- q9 Pour chaque Pokémon, donner son nom, son espèce et le nombre d'apparitions. Nous sommes intéressés seulement aux Pokémons qui ont apparu au moins 2 fois.
SELECT p.nom, p.espece, COUNT(a.idPokemon) AS "nbrapparition"
FROM pokemon p,apparition a
WHERE p.id=a.idPokemon
GROUP BY a.idPokemon
HAVING nbrapparition>=2;
;



-- 10. Pour chaque Pokémon, donner son nom, son espèce et le nombre d'apparitions dont la durée est supérieure à 5 minutes.
SELECT p.nom, p.espece, COUNT(a.idPokemon) as "nbrApparition"
FROM pokemon p, apparition a  -- les données utilisées dans le from doivent apparaitre dans le group by (l'inverse peut être faux)
WHERE p.id=a.idPokemon
AND a.duree>5
GROUP BY p.id, p.nom, p.espece 
;



-- 11. Pour chaque pokémon de plus de 1000 points de combat, donner son nom, son espèce et la durée totale d'apparition. Nous sommes intéressés seulement aux pokémons qui ont apparu au moins 10 minutes.
SELECT p.nom, p.espece, SUM(a.duree) AS "dureeTotal"
FROM pokemon p, apparition a
WHERE p.id=a.idPokemon
AND p.pointCombat>1000
GROUP BY p.nom, p.espece
HAVING dureeTotal >= 10
;





--  q12. Donner le temps moyen d'apparition des pokémons appartenant aux joueurs de l'équipe ayant contrôlé le plus d'arène.

-- 1) Cpt de prise d'arene
-- 2) MAX(Cpt)
-- 3) IdEquipe tq
-- 4) AVG(duree) des pokémons de idEquipe

SELECT p.id, p.nom, AVG(a.duree) as TempApp
FROM Pokemon p, Apparition a, Joueur j
WHERE p.id = a.idPokemon
AND p.idJoueur = j.pseudonyme
AND idEquipe IN (SELECT idEquipe
					FROM defense
					GROUP BY idEquipe
					HAVING COUNT(DISTINCT idArene) = (SELECT MAX(compte)
														FROM (SELECT COUNT(DISTINCT idArene) compte
														FROM defense
														GROUP BY idEquipe) tmp
														)
						)
GROUP BY p.id, p.nom
;






-- -----------------------------------------------------------------  TD7  -------------------------------------------------------

-- q1 Classer les pokémonsen fonction du nombre d'apparition. (y comris ceux qui sont apparu 0 fois)
SELECT p.nom, COUNT(a.idPokemon) AS nbrApparition
FROM pokemon P LEFT JOIN apparition a ON P.id = a.idPokemon
GROUP BY p.nom
;

-- q2. Quels sont les Pokémons dont le nombre d'apparitions est supérieur au nombre moyen d'apparitions ?
SELECT nom, COUNT(idPokemon) as NbApparition
FROM Pokemon JOIN Apparition ON id = idPokemon
GROUP BY nom
HAVING NbApparition > (SELECT AVG(cntPokemon)
						FROM (SELECT COUNT(idPokemon) as cntPokemon
								FROM Pokemon LEFT JOIN Apparition ON id = idPokemon
								GROUP BY id -- si GROUP BY idPokemon on aura un seul 0 pour tous les pokemons qui ne sont pas apparus
								) as avgTable
						)
ORDER BY NbApparition DESC
;

-- q3. On veut obtenir le pseudonyme, le sexe, le niveau et le nombre de Pokémons de tous les joueurs, y compris ceux qui n'ont capturé aucun Pokémon. Triez votre résultat.
SELECT j.pseudonyme, j.sexe, j.niveau, COUNT(p.id) as NbPokemon
FROM Joueur j LEFT JOIN Pokemon p ON j.pseudonyme = p.idJoueur
GROUP BY j.pseudonyme, j.sexe, j.niveau
ORDER BY NbPokemon DESC
;

-- q4 Quels sont les Pokémons qui ont apparu dans tous les emplacements différents ?
SELECT id, COUNT( DISTINCT idEmplacement) AS nbrEmplacement-- quand on a un count() il faut utiliser having parce que where ne marche pas
FROM pokemon p INNER JOIN apparition ON id = idPokemon
GROUP BY id
HAVING nbrEmplacement = (SELECT COUNT(id) FROM Emplacement)
;


-- q5 Quels sont les joueurs qui ont capturé toutes les espèces de Pokémon ?
SELECT pseudonyme
FROM joueur, pokemon
WHERE idJoueur = pseudonyme
GROUP BY pseudonyme 
HAVING COUNT(DISTINCT espece) = (SELECT COUNT(DISTINCT espece) -- un HAVING se fait toujours après le GROUP BY
								FROM pokemon)
;

-- q6 Combien de joueurs possèdent des Pokémons qui sont placés dans l'emplacement avec la latitude la plus septentrionale (la plus haute) ?
SELECT COUNT(idJoueur) AS nbrJoueur
FROM pokemon p, apparition, emplacement e
WHERE p.id = idEmplacement
AND latitude  = (SELECT MAX(latitude) FROM emplacement)
;


-- q7 Quelle est l'équipe qui a pris le contrôle d'une arène plus souvent ?
SELECT id, e.nom
FROM defense d, equipe e
WHERE e.id = d.idEquipe
GROUP BY id, e.nom
HAVING COUNT(idArene) = (SELECT MAX(controle)
						FROM (SELECT COUNT(idArene) AS controle
								FROM defense
                                GROUP BY idEquipe) AS defCount
						)
;


-- q8 Quelle est la plage des dates auxquelles les Pokémons de l'équipe de la question précédente ont apparu ?
SELECT MIN(horaire) debut, MAX(horaire) fin
FROM joueur, pokemon, apparition
WHERE pseudonyme = idJoueur
AND idPokemon = id
AND idEquipe = (SELECT id
				FROM equipe, defense
                WHERE idEquipe = id
                GROUP BY idEquipe
                HAVING COUNT(idEquipe) = (SELECT MAX(controle)
											FROM (SELECT COUNT(*) controle
													FROM defense
                                                    GROUP BY idEquipe
                                                    ) defenseCount
										)
				)
;


-- q9 Affichez les joueurs dont le niveau est le plus élevé de leur équipe.
SELECT pseudonyme
FROM joueur, (SELECT MAX(niveau) lvlmax, idEquipe eqMax
				FROM joueur, equipe
                WHERE id = idEquipe
                GROUP BY idEquipe) AS MaxTable
WHERE eqMax = idEquipe
AND niveau = lvlMax
;


-- q10 Affichez les noms des pokémons dont les points de combat sont supérieur à  la moyenne de ceux de leur joueur.
SELECT idJoueur, nom
FROM pokemon p1
WHERE pointCombat > (SELECT AVG(pointCombat)
						FROM pokemon p2
						WHERE p1.idJoueur = p2.idJoueur
					)
GROUP BY idJoueur
;