# Création de la table robots

CREATE TABLE robots
(nom VARCHAR(6) PRIMARY KEY,
labo VARCHAR(6),
pays VARCHAR(8),
cat INT) ;

# Création de la table inscriptions

CREATE TABLE inscriptions
(nom VARCHAR(6) REFERENCES robots,
compet VARCHAR(4),
montant REAL,
PRIMARY KEY (nom, compet));

# Création de la table competitions

CREATE TABLE competitions
(compet VARCHAR(4) PRIMARY KEY,
ville VARCHAR(10),
pays VARCHAR(8),
datc date);

# Création de la table epreuves

CREATE TABLE epreuves
(numep int,
compet VARCHAR(4) REFERENCES competitions,
cat INT,
titre VARCHAR(12),
demij CHAR(2),
PRIMARY KEY(numep, compet));

# Création de la table resultats

CREATE TABLE resultats
(nom VARCHAR(6) REFERENCES robots,
numep INT REFERENCES epreuves,
compet VARCHAR(4),
nbpts INT,
gain REAL,
PRIMARY KEY(nom, numep, compet));



insert into robots values ('Bob', 'TIM', 'USA', 1);
insert into robots values ('Gaston', 'SAAL', 'France', 1);
insert into robots values ('Zoe', 'AIRNI', 'France', 1);
insert into robots values ('Pok', 'SISCAN', 'Japan', 2) ;
insert into robots values ('Tom', 'GRRL', 'Germany', 2) ;
insert into robots values ('Toto', 'TIM', 'USA', 2);
insert into robots values ('Pikpik', 'SISCAN', 'Japan', 1);
insert into robots values ('Pyroli', 'GRRL', 'Germany', 2) ;

insert into competitions values ('WRC', 'Boston', 'USA', '2020-03-03');
insert into competitions values ('IRM', 'Paris', 'France', '2020-06-20');
insert into competitions values ('RIR', 'Tokyo', 'Japan', '2020-03-15');

insert into inscriptions values ('Bob', 'WRC', 200);
insert into inscriptions values ('Bob', 'IRM', 50);
insert into inscriptions values ('Gaston', 'WRC', 100);
insert into inscriptions values ('Gaston', 'RIR', 600);
insert into inscriptions values ('Tom', 'WRC', 1000);
insert into inscriptions values ('Tom', 'IRM', 1000);
insert into inscriptions values ('Tom', 'RIR', 2000);
insert into inscriptions values ('Zoe', 'IRM', 1000);
insert into inscriptions values ('Pok', 'WRC', 500);
insert into inscriptions values ('Pok', 'RIR', 1500);
insert into inscriptions values ('Gaston', 'IRM', 200);
insert into inscriptions values ('Pikpik', 'WRC', NULL);
insert into inscriptions values ('Pyroli', 'WRC', NULL);
insert into inscriptions values ('Zoe', 'WRC', 250);
insert into inscriptions values ('Toto', 'WRC', 1500);


insert into epreuves values (31, 'WRC', 1, 'Le tennis', 'AM');
insert into epreuves values (32, 'WRC', 2, 'Le jardin', 'AM');
insert into epreuves values (41, 'WRC', 1, 'Le combat1', 'PM');
insert into epreuves values (42, 'WRC', 2, 'Le combat2', 'PM');
insert into epreuves values (31, 'RIR', 1, 'Toutterrain', 'AM');
insert into epreuves values (32, 'RIR', 2, 'Planif', 'PM');
insert into epreuves values (41, 'RIR', 1, 'Construct', 'AM');
insert into epreuves values (42, 'RIR', 2, 'Pugilat', 'PM');
insert into epreuves values (51, 'IRM', 1, 'Course1', 'AM');
insert into epreuves values (52, 'IRM', 2, 'Course2', 'PM');
insert into epreuves values (41, 'IRM', 1, 'Course1b', 'AM');


insert into resultats values ('Pok', 32, 'WRC', 30, 900);
insert into resultats values ('Pok', 42, 'WRC', 20, 150);
insert into resultats values ('Pok', 42, 'RIR', 40, 100);
insert into resultats values ('Gaston', 41, 'RIR', 40, 2000);
insert into resultats values ('Gaston', 41, 'WRC', 50, 800);
insert into resultats values ('Bob', 31, 'WRC', 60, 100); 
insert into resultats values ('Bob', 41, 'IRM', 100, 500);
insert into resultats values ('Tom', 42, 'WRC', 150, 900);
insert into resultats values ('Tom', 32, 'RIR', 90, 500);
insert into resultats values ('Toto', 42, 'WRC', 100, 500);
insert into resultats values ('Toto', 32, 'WRC', 400, 700);
insert into resultats values ('Zoe', 31, 'WRC', 90, 500);










-- QUESTION 1 : Nombre de compétitions par robot
SELECT nom, COUNT(DISTINCT compet)
FROM inscriptions
GROUP BY nom;


-- QUESTION 2 : Nombre d'épreuves par compétition
SELECT compet, COUNT(*)
FROM epreuves
GROUP BY compet;


-- QUESTION 3 : Nom, labo, catégorie et nombre de compétitions par robot
SELECT robots.nom, labo, cat, COUNT(DISTINCT compet)
FROM robots, inscriptions
WHERE robots.nom = inscriptions.nom
GROUP BY robots.nom, labo, cat;


-- QUESTION 4 : Total des points par épreuve et compétition
SELECT numep, compet, SUM(nbpts)
FROM resultats
GROUP BY numep, compet;


-- QUESTION 5 : Nombre de robots par pays inscrits à IRM
SELECT pays, COUNT(*)
FROM robots
WHERE nom IN (
    SELECT nom
    FROM inscriptions
    WHERE compet = 'IRM'
)
GROUP BY pays;


-- QUESTION 6 a : Gain total par robot
SELECT nom, SUM(gain)
FROM resultats
GROUP BY nom;


-- QUESTION 6 b : Moyenne des gains totaux des robots ayant un résultat
SELECT AVG(total)
FROM (
    SELECT nom, SUM(gain) AS total
    FROM resultats
    GROUP BY nom
) t;


-- QUESTION 7 : Montant maximal par compétition, s'il atteint 1000
SELECT compet, MAX(montant)
FROM inscriptions
GROUP BY compet
HAVING MAX(montant) >= 1000;


-- QUESTION 8 : Robots inscrits à plus de deux compétitions
-- et montant total payé
SELECT nom, SUM(montant)
FROM inscriptions
GROUP BY nom
HAVING COUNT(DISTINCT compet) > 2;


-- QUESTION 9 : Laboratoires ayant gagné plus de 1000
-- dans une compétition
SELECT labo, compet, SUM(gain)
FROM robots, resultats
WHERE robots.nom = resultats.nom
GROUP BY labo, compet
HAVING SUM(gain) > 1000;


-- QUESTION 10 : Total des gains par compétition et catégorie
SELECT compet, cat, SUM(gain)
FROM robots, resultats
WHERE robots.nom = resultats.nom
GROUP BY compet, cat;