# 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);


SELECT * FROM robots;
PRAGMA table_info(robots);
PRAGMA table_info(inscriptions);
PRAGMA table_info(competitions);
PRAGMA table_info(epreuves);
PRAGMA table_info(resultats);


SELECT * FROM robots;
SELECT * FROM inscriptions;
SELECT * FROM competitions;
SELECT * FROM epreuves;
SELECT * FROM resultats;


SELECT nom
FROM robots;


SELECT nom
FROM robots
WHERE pays = 'France';


SELECT DISTINCT pays
FROM robots;


SELECT *
FROM competitions
WHERE ville = 'Boston';


SELECT montant
FROM inscriptions
WHERE nom = 'Bob'
AND compet = 'WRC';


SELECT numep, compet
FROM epreuves
WHERE demij = 'AM';


SELECT numep, compet, nbpts
FROM resultats
WHERE nom = 'Pok'
ORDER BY nbpts DESC;


SELECT nom, montant
FROM inscriptions
WHERE compet = 'IRM'
AND montant > 200
ORDER BY montant ASC;


SELECT robots.nom, robots.cat
FROM robots
JOIN inscriptions
ON robots.nom = inscriptions.nom
WHERE inscriptions.compet = 'RIR'
AND inscriptions.montant > 500;


SELECT competitions.datc
FROM competitions
JOIN inscriptions
ON competitions.compet = inscriptions.compet
WHERE inscriptions.nom = 'Tom';


SELECT epreuves.numep
FROM epreuves
JOIN robots
ON epreuves.cat = robots.cat
WHERE robots.nom = 'Tom'
AND epreuves.compet = 'RIR';


SELECT DISTINCT robots.labo
FROM robots
JOIN resultats
ON robots.nom = resultats.nom
WHERE resultats.compet = 'WRC'
AND resultats.gain > 1000;


SELECT robots.labo, resultats.nom, resultats.gain
FROM robots
JOIN resultats
ON robots.nom = resultats.nom
WHERE resultats.compet = 'WRC';


SELECT DISTINCT competitions.pays
FROM robots
JOIN inscriptions
ON robots.nom = inscriptions.nom
JOIN competitions
ON inscriptions.compet = competitions.compet
WHERE robots.pays = 'France';


SELECT DISTINCT r1.nom
FROM resultats r1
JOIN resultats r2
ON r2.nom = 'Tom'
AND r2.numep = 42
AND r2.compet = 'WRC'
WHERE r1.compet = 'WRC'
AND r1.gain > r2.gain;


SELECT nom, numep, compet, gain
FROM resultats
WHERE nom = 'Tom'
AND numep = 42
AND compet = 'WRC';


SELECT nom, gain
FROM resultats
WHERE compet = 'WRC'
ORDER BY gain DESC;


SELECT resultats.nom, resultats.numep, resultats.compet,
       resultats.gain, inscriptions.montant
FROM resultats
JOIN inscriptions
ON resultats.nom = inscriptions.nom
AND resultats.compet = inscriptions.compet
WHERE resultats.gain > inscriptions.montant;


SELECT DISTINCT resultats.nom, resultats.numep, resultats.compet,
       resultats.gain, inscriptions.compet, inscriptions.montant
FROM resultats
JOIN inscriptions
ON resultats.nom = inscriptions.nom
WHERE resultats.gain > inscriptions.montant;




SELECT nom, cat
FROM robots
WHERE nom IN (
    SELECT nom
    FROM inscriptions
    WHERE compet = 'RIR'
    AND montant BETWEEN 500 AND 700
);
select * from robots ;
select * from resultats ;
select * from epreuves ;
select * from robots ;