
CREATE TABLE "EtudiantB"("nEtu" TEXT,"nom" TEXT,"pren" TEXT, "groupe" TEXT); -- etudiant et son groupe
CREATE TABLE "CoeffB"("cours" TEXT,"groupe" TEXT); -- groupe cours
CREATE TABLE "ExamenB"("examen" TEXT,"cours" TEXT); -- liste exam cours
CREATE TABLE "NdomB"("nom" TEXT,"num" INTEGER);  --- nom de domaine

CREATE TABLE "Cours"("cours" TEXT,"matiere" TEXT); -- cours par matière

---{{{ nom de domaine
INSERT INTO "NdomB" VALUES (  'megalo.supercom'   ,0           );
INSERT INTO "NdomB" VALUES (  'gogol.spidernet'   ,1           );
INSERT INTO "NdomB" VALUES (  'marron.fr'         ,2           );
INSERT INTO "NdomB" VALUES (  'cestgratuit.gro'   ,3           );
INSERT INTO "NdomB" VALUES (  'startup.arnaque'   ,4           );
INSERT INTO "NdomB" VALUES (  'bottomend.lent'    ,5           );
INSERT INTO "NdomB" VALUES (  'anonyme.emynona'   ,6           );
INSERT INTO "NdomB" VALUES (  'ackermann.dix'     ,7           );
---}}}

--{{{ liste des examens numero-cours (chaque examen est sûr un cours)
INSERT INTO "ExamenB" VALUES (  'AC11'  ,'AnalyseComplexe'           );
INSERT INTO "ExamenB" VALUES (  'AC12'  ,'AnalyseComplexe'           );
INSERT INTO "ExamenB" VALUES (  'AC13'  ,'AnalyseComplexe'           );
INSERT INTO "ExamenB" VALUES (  'AC14'  ,'AnalyseComplexe'           );
INSERT INTO "ExamenB" VALUES (  'AL15'  ,'AlgebreLineaire'           );
INSERT INTO "ExamenB" VALUES (  'AL16'  ,'AlgebreLineaire'           );
INSERT INTO "ExamenB" VALUES (  'AL17'  ,'AlgebreLineaire'           );
INSERT INTO "ExamenB" VALUES (  'AL18'  ,'AlgebreLineaire'           );
INSERT INTO "ExamenB" VALUES (  'Ag19'  ,'Algorithmique'             );
INSERT INTO "ExamenB" VALUES (  'Ag21'  ,'Algorithmique'             );
INSERT INTO "ExamenB" VALUES (  'Ag22'  ,'Algorithmique'             );
INSERT INTO "ExamenB" VALUES (  'Ag23'  ,'Algorithmique'             );
INSERT INTO "ExamenB" VALUES (  'Ag24'  ,'Algorithmique'             );
INSERT INTO "ExamenB" VALUES (  'Dy25'  ,'Dynamique'                 );
INSERT INTO "ExamenB" VALUES (  'Dy26'  ,'Dynamique'                 );
INSERT INTO "ExamenB" VALUES (  'Dy27'  ,'Dynamique'                 );
INSERT INTO "ExamenB" VALUES (  'Cr28'  ,'Cryptographie'             );
INSERT INTO "ExamenB" VALUES (  'Cr29'  ,'Cryptographie'             );
INSERT INTO "ExamenB" VALUES (  'Cr31'  ,'Cryptographie'             );
INSERT INTO "ExamenB" VALUES (  'Cr32'  ,'Cryptographie'             );
INSERT INTO "ExamenB" VALUES (  'El33'  ,'Electrodynamique'          );
INSERT INTO "ExamenB" VALUES (  'El34'  ,'Electrodynamique'          );
INSERT INTO "ExamenB" VALUES (  'El35'  ,'Electrodynamique'          );
INSERT INTO "ExamenB" VALUES (  'In36'  ,'InfoFondamentale'          );
INSERT INTO "ExamenB" VALUES (  'In37'  ,'InfoFondamentale'          );
INSERT INTO "ExamenB" VALUES (  'In38'  ,'InfoFondamentale'          );
INSERT INTO "ExamenB" VALUES (  'Me39'  ,'MecaniqueDesFluides'       );
INSERT INTO "ExamenB" VALUES (  'Me41'  ,'MecaniqueDesFluides'       );
INSERT INTO "ExamenB" VALUES (  'Me42'  ,'MecaniqueDesFluides'       );
INSERT INTO "ExamenB" VALUES (  'Op43'  ,'OptiqueGeometrique'        );
INSERT INTO "ExamenB" VALUES (  'Op44'  ,'OptiqueGeometrique'        );
INSERT INTO "ExamenB" VALUES (  'Op45'  ,'OptiqueGeometrique'        );
INSERT INTO "ExamenB" VALUES (  'Op46'  ,'OptiqueGeometrique'        );
INSERT INTO "ExamenB" VALUES (  'Pr47'  ,'ProgrammationDeclarative'  );
INSERT INTO "ExamenB" VALUES (  'Pr48'  ,'ProgrammationDeclarative'  );
INSERT INTO "ExamenB" VALUES (  'Pr49'  ,'ProgrammationDeclarative'  );
INSERT INTO "ExamenB" VALUES (  'TD50'  ,'TheorieDesDistributions'   );
INSERT INTO "ExamenB" VALUES (  'TD51'  ,'TheorieDesDistributions'   );
INSERT INTO "ExamenB" VALUES (  'TD52'  ,'TheorieDesDistributions'   );
INSERT INTO "ExamenB" VALUES (  'TD53'  ,'TheorieDesDistributions'   );
INSERT INTO "ExamenB" VALUES (  'TG54'  ,'TheorieDesGroupes'         );
INSERT INTO "ExamenB" VALUES (  'TG55'  ,'TheorieDesGroupes'         );
INSERT INTO "ExamenB" VALUES (  'TG56'  ,'TheorieDesGroupes'         );
INSERT INTO "ExamenB" VALUES (  'TG57'  ,'TheorieDesGroupes'         );
INSERT INTO "ExamenB" VALUES (  'Te58'  ,'Thermodynamique'           );
INSERT INTO "ExamenB" VALUES (  'Te59'  ,'Thermodynamique'           );
INSERT INTO "ExamenB" VALUES (  'Te60'  ,'Thermodynamique'           );
INSERT INTO "ExamenB" VALUES (  'Te61'  ,'Thermodynamique'           );
INSERT INTO "ExamenB" VALUES (  'Th62'  ,'TheorieDeLaRelativite'     );
INSERT INTO "ExamenB" VALUES (  'Th63'  ,'TheorieDeLaRelativite'     );
INSERT INTO "ExamenB" VALUES (  'Th64'  ,'TheorieDeLaRelativite'     );
INSERT INTO "ExamenB" VALUES (  'Th65'  ,'TheorieDeLaRelativite'     );
--}}}

--{{{ liste des cours
INSERT INTO "Cours" VALUES ('AnalyseComplexe'          , 'Mathematiques' );
INSERT INTO "Cours" VALUES ('AlgebreLineaire'          , 'Mathematiques' );
INSERT INTO "Cours" VALUES ('TheorieDesDistributions'  , 'Mathematiques' );
INSERT INTO "Cours" VALUES ('TheorieDesGroupes'        , 'Mathematiques' );
INSERT INTO "Cours" VALUES ('Cryptographie'            , 'Mathematiques' );
INSERT INTO "Cours" VALUES ('InfoFondamentale'         , 'Informatique'  );
INSERT INTO "Cours" VALUES ('Algorithmique'            , 'Informatique'  );
INSERT INTO "Cours" VALUES ('ProgrammationDeclarative' , 'Informatique'  );
INSERT INTO "Cours" VALUES ('OptiqueGeometrique'       , 'Physique'      );
INSERT INTO "Cours" VALUES ('Electrodynamique'         , 'Physique'      );
INSERT INTO "Cours" VALUES ('Thermodynamique'          , 'Physique'      );
INSERT INTO "Cours" VALUES ('TheorieDeLaRelativite'    , 'Physique'      );
INSERT INTO "Cours" VALUES ('Dynamique'                , 'Mecanique'     );
INSERT INTO "Cours" VALUES ('MecaniqueDesFluides'      , 'Mecanique'     );
--}}}

--{{{ % quelle classe quel cours
INSERT INTO "CoeffB" VALUES ( 'AnalyseComplexe'                   , 'G1L3EA'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'InfoFondamentale'                  , 'G1L3EA'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Dynamique'                         , 'G1L3EA'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'TheorieDesDistributions'           , 'G1L3EA'     ); -- 4

INSERT INTO "CoeffB" VALUES ( 'AlgebreLineaire'                   , 'G1L3EB'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'Algorithmique'                     , 'G1L3EB'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'AnalyseComplexe'                   , 'G1L3EB'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'Cryptographie'                     , 'G1L3EB'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'Dynamique'                         , 'G1L3EB'     ); -- 5

INSERT INTO "CoeffB" VALUES ( 'AlgebreLineaire'                   , 'G2L3EC'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Algorithmique'                     , 'G2L3EC'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'AnalyseComplexe'                   , 'G2L3EC'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Cryptographie'                     , 'G2L3EC'     ); -- 4

INSERT INTO "CoeffB" VALUES ( 'TheorieDesGroupes'                 , 'G2L3ED'     ); -- 6
INSERT INTO "CoeffB" VALUES ( 'InfoFondamentale'                  , 'G2L3ED'     ); -- 6
INSERT INTO "CoeffB" VALUES ( 'ProgrammationDeclarative'          , 'G2L3ED'     ); -- 6
INSERT INTO "CoeffB" VALUES ( 'Cryptographie'                     , 'G2L3ED'     ); -- 6
INSERT INTO "CoeffB" VALUES ( 'MecaniqueDesFluides'               , 'G2L3ED'     ); -- 6
INSERT INTO "CoeffB" VALUES ( 'Electrodynamique'                  , 'G2L3ED'     ); -- 6

INSERT INTO "CoeffB" VALUES ( 'TheorieDeLaRelativite'             , 'G3L3EE'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'AnalyseComplexe'                   , 'G3L3EE'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'ProgrammationDeclarative'          , 'G3L3EE'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'TheorieDesDistributions'           , 'G3L3EE'     ); -- 4
 
INSERT INTO "CoeffB" VALUES ( 'AnalyseComplexe'                   , 'G4L2EF'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'AlgebreLineaire'                   , 'G4L2EF'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'TheorieDesDistributions'           , 'G4L2EF'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'TheorieDesGroupes'                 , 'G4L2EF'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'Cryptographie'                     , 'G4L2EF'     ); -- 5

INSERT INTO "CoeffB" VALUES ( 'AnalyseComplexe'                   , 'G4L2EG'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'AlgebreLineaire'                   , 'G4L2EG'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'TheorieDesDistributions'           , 'G4L2EG'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'TheorieDesGroupes'                 , 'G4L2EG'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'Cryptographie'                     , 'G4L2EG'     ); -- 5

INSERT INTO "CoeffB" VALUES ( 'Dynamique'                         , 'G5L2EH'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Algorithmique'                     , 'G5L2EH'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Cryptographie'                     , 'G5L2EH'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'MecaniqueDesFluides'               , 'G5L2EH'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Algorithmique'                     , 'G5L2EI'     ); -- 3
INSERT INTO "CoeffB" VALUES ( 'OptiqueGeometrique'                , 'G5L2EI'     ); -- 3
INSERT INTO "CoeffB" VALUES ( 'ProgrammationDeclarative'          , 'G5L2EI'     ); -- 3

INSERT INTO "CoeffB" VALUES ( 'AlgebreLineaire'                   , 'G6L1EJ'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'TheorieDesDistributions'           , 'G6L1EJ'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'Thermodynamique'                   , 'G6L1EJ'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'ProgrammationDeclarative'          , 'G6L1EJ'     ); -- 5
INSERT INTO "CoeffB" VALUES ( 'TheorieDesGroupes'                 , 'G6L1EJ'     ); -- 5

INSERT INTO "CoeffB" VALUES ( 'ProgrammationDeclarative'          , 'G6L1EK'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'InfoFondamentale'                  , 'G6L1EK'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'TheorieDesDistributions'           , 'G6L1EK'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Electrodynamique'                  , 'G6L1EK'     ); -- 4

INSERT INTO "CoeffB" VALUES ( 'Algorithmique'                     , 'G7L1EL'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Electrodynamique'                  , 'G7L1EL'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'MecaniqueDesFluides'               , 'G7L1EL'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Thermodynamique'                   , 'G7L1EL'     ); -- 4

INSERT INTO "CoeffB" VALUES ( 'AlgebreLineaire'                   , 'G7L1EM'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'Thermodynamique'                   , 'G7L1EM'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'TheorieDesGroupes'                 , 'G7L1EM'     ); -- 4
INSERT INTO "CoeffB" VALUES ( 'TheorieDeLaRelativite'             , 'G7L1EM'     ); -- 4
--}}}

--{{{ EtudiantB
INSERT INTO "EtudiantB" VALUES ('NU0000' , 'Jean'       , 'Bryan'    , 'G7L1EM');
INSERT INTO "EtudiantB" VALUES ('NU0001' , 'Bernard'    , 'Louise'   , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0002' , 'Thomas'     , 'Thaïs'    , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0003' , 'Petit'      , 'Alix'     , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0004' , 'Robert'     , 'Gael'     , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0005' , 'Richard'    , 'Léna'     , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0006' , 'Durand'     , 'Margaux'  , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0007' , 'Dubois'     , 'Theo'     , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0008' , 'Moreau'     , 'Mathis'   , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0009' , 'Laurent'    , 'Kyllian'  , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0010' , 'Simon'      , 'Tristan'  , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0011' , 'Michel'     , 'Sofia'    , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0012' , 'Lefebvre'   , 'Victoria' , 'G7L1EM'); 
INSERT INTO "EtudiantB" VALUES ('NU0013' , 'Leroy'      , 'Adrien'   , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0014' , 'Roux'       , 'Ewan'     , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0015' , 'David'      , 'Gabin'    , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0016' , 'Bertrand'   , 'Benjamin' , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0017' , 'Morel'      , 'Aurelien' , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0018' , 'Fournier'   , 'Lina'     , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0019' , 'Girard'     , 'Mael'     , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0020' , 'Bonnet'     , 'Samuel'   , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0021' , 'Dupont'     , 'Lyana'    , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0022' , 'Lambert'    , 'Guillaume', 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0023' , 'Fontaine'   , 'Etienne'  , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0024' , 'Rousseau'   , 'Kilian'   , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0025' , 'Vincent'    , 'Matteo'   , 'G7L1EL'); 
INSERT INTO "EtudiantB" VALUES ('NU0026' , 'Garnier'    , 'Nora'     , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0027' , 'Chevalier'  , 'Matheo'   , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0028' , 'Francois'   , 'Agathe'   , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0029' , 'Legrand'    , 'Alexandre', 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0030' , 'Gauthier'   , 'Matthieu' , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0031' , 'Garcia'     , 'Manon'    , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0032' , 'Perrin'     , 'Alya'     , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0033' , 'Robin'      , 'Clémence' , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0034' , 'Clement'    , 'Camille'  , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0035' , 'Morin'      , 'Jordan'   , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0036' , 'Nicolas'    , 'Éva'      , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0037' , 'Henry'      , 'Bastien'  , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0038' , 'Roussel'    , 'Axel'     , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0039' , 'Mathieu'    , 'Lana'     , 'G6L1EK'); 
INSERT INTO "EtudiantB" VALUES ('NU0040' , 'Gautier'    , 'Ethan'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0041' , 'Masson'     , 'Maëlys'   , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0042' , 'Marchand'   , 'Lucas'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0043' , 'Duval'      , 'Arthur'   , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0044' , 'Denis'      , 'Robin'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0045' , 'Dumont'     , 'Assia'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0046' , 'Marie'      , 'Thibaut'  , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0047' , 'Lemaire'    , 'Aaron'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0048' , 'Noel'       , 'Anaïs'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0049' , 'Meyer'      , 'Antonin'  , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0050' , 'Dufour'     , 'Tiago'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0051' , 'Meunier'    , 'Esteban'  , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0052' , 'Brun'       , 'Louis'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0053' , 'Blanchard'  , 'Valentine', 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0054' , 'Giraud'     , 'Adèle'    , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0055' , 'Joly'       , 'Rafael'   , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0056' , 'Riviere'    , 'Lily'     , 'G6L1EJ'); 
INSERT INTO "EtudiantB" VALUES ('NU0057' , 'Lucas'      , 'Célia'    , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0058' , 'Brunet'     , 'Romy'     , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0059' , 'Gaillard'   , 'Nicolas'  , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0060' , 'Barbier'    , 'Constance', 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0061' , 'Arnaud'     , 'Alba'     , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0062' , 'Martinez'   , 'Ilan'     , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0063' , 'Gerard'     , 'Luca'     , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0064' , 'Roche'      , 'Clara'    , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0065' , 'Renard'     , 'Lukas'    , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0066' , 'Schmitt'    , 'Kevin'    , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0067' , 'Roy'        , 'Anna'     , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0068' , 'Leroux'     , 'Mathias'  , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0069' , 'Colin'      , 'Charlie'  , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0070' , 'Vidal'      , 'Amelia'   , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0071' , 'Caron'      , 'Gabriel'  , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0072' , 'Picard'     , 'Maxime'   , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0073' , 'Roger'      , 'Julia'    , 'G5L2EI'); 
INSERT INTO "EtudiantB" VALUES ('NU0074' , 'Fabre'      , 'Jade'     , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0075' , 'Aubert'     , 'Luna'     , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0076' , 'Lemoine'    , 'Kylian'   , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0077' , 'Renaud'     , 'Mathilde' , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0078' , 'Dumas'      , 'Martin'   , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0079' , 'Lacroix'    , 'Capucine' , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0080' , 'Olivier'    , 'Emy'      , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0081' , 'Philippe'   , 'Nour'     , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0082' , 'Bourgeois'  , 'Nathan'   , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0083' , 'Pierre'     , 'Jeremy'   , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0084' , 'Benoit'     , 'Erwan'    , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0085' , 'Reivilard'  , 'Margot'   , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0086' , 'Leclerc'    , 'Albane'   , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0087' , 'Payet'      , 'Léana'    , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0088' , 'Rolland'    , 'Lucie'    , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0089' , 'Leclercq'   , 'Lya'      , 'G5L2EH'); 
INSERT INTO "EtudiantB" VALUES ('NU0090' , 'Guillaume'  , 'Giulia'   , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0091' , 'Lecomte'    , 'Bilal'    , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0092' , 'Lopez'      , 'Gabrielle', 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0093' , 'Jean'       , 'Romain'   , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0094' , 'Dupuy'      , 'Lou'      , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0095' , 'Guillot'    , 'Charles'  , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0096' , 'Hubert'     , 'Noe'      , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0097' , 'Berger'     , 'Elsa'     , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0098' , 'Carpentier' , 'Léa'      , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0099' , 'Sanchez'    , 'Éléna'    , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0100' , 'Dupuis'     , 'Sarah'    , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0101' , 'Moulin'     , 'Lorenzo'  , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0102' , 'Louis'      , 'Mila'     , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0103' , 'Deschamps'  , 'Romane'   , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0104' , 'Huet'       , 'Lise'     , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0105' , 'Vasseur'    , 'Gaspard'  , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0106' , 'Perez'      , 'Evan'     , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0107' , 'Boucher'    , 'Eliott'   , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0108' , 'Fleury'     , 'Julien'   , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0109' , 'Royer'      , 'Aya'      , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0110' , 'Klein'      , 'Salomé'   , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0111' , 'Jacquet'    , 'Diego'    , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0112' , 'Adam'       , 'Aymeric'  , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0113' , 'Paris'      , 'William'  , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0114' , 'Poirier'    , 'Julian'   , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0115' , 'Marty'      , 'Alma'     , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0116' , 'Aubry'      , 'Enzo'     , 'G4L2EG'); 
INSERT INTO "EtudiantB" VALUES ('NU0117' , 'Guyot'      , 'Alban'    , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0118' , 'Carre'      , 'Luka'     , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0119' , 'Charles'    , 'Apolline' , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0120' , 'Renault'    , 'Anthony'  , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0121' , 'Charpentier', 'Emmy'     , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0122' , 'Menard'     , 'Dylan'    , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0123' , 'Maillard'   , 'Yasmine'  , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0124' , 'Baron'      , 'Oscar'    , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0125' , 'Bertin'     , 'Rose'     , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0126' , 'Bailly'     , 'Maya'     , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0127' , 'Herve'      , 'Livia'    , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0128' , 'Schneider'  , 'Nina'     , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0129' , 'Fernandez'  , 'Héloïse'  , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0130' , 'Lenormand'  , 'Adam'     , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0131' , 'Collet'     , 'Ayoub'    , 'G4L2EF'); 
INSERT INTO "EtudiantB" VALUES ('NU0132' , 'Leger'      , 'Alice'    , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0133' , 'Bouvier'    , 'Zoé'      , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0134' , 'Julien'     , 'Ava'      , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0135' , 'Prevost'    , 'Soline'   , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0136' , 'Millet'     , 'Chloé'    , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0137' , 'Perrot'     , 'Tom'      , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0138' , 'Daniel'     , 'David'    , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0139' , 'Le Drian'   , 'Pierre'   , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0140' , 'Cousin'     , 'Alicia'   , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0141' , 'Germain'    , 'Valentin' , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0142' , 'Breton'     , 'Noémie'   , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0143' , 'Besson'     , 'Morgan'   , 'G3L3EE'); 
INSERT INTO "EtudiantB" VALUES ('NU0144' , 'Langlois'   , 'Marie'    , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0145' , 'Remy'       , 'Victor'   , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0146' , 'Le Breton'  , 'Mia'      , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0147' , 'Pelletier'  , 'Quentin'  , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0148' , 'Leveque'    , 'Victoire' , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0149' , 'Perrier'    , 'Juliette' , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0150' , 'Leblanc'    , 'Emma'     , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0151' , 'Barre'      , 'Alan'     , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0152' , 'Lebrun'     , 'Emilien'  , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0153' , 'Marchal'    , 'Ambre'    , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0154' , 'Weber'      , 'Mateo'    , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0155' , 'Mallet'     , 'Marius'   , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0156' , 'Hamon'      , 'Remi'     , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0157' , 'Boulanger'  , 'Jules'    , 'G2L3ED'); 
INSERT INTO "EtudiantB" VALUES ('NU0158' , 'Jacob'      , 'Arnaud'   , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0159' , 'Monnier'    , 'Théa'     , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0160' , 'Michaud'    , 'Lisa'     , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0161' , 'Rodriguez'  , 'Corentin' , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0162' , 'Guichard'   , 'Tony'     , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0163' , 'Gillet'     , 'Inaya'    , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0164' , 'Etienne'    , 'Loic'     , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0165' , 'Grondin'    , 'Ella'     , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0166' , 'Poulain'    , 'Lyna'     , 'G2L3EC'); 
INSERT INTO "EtudiantB" VALUES ('NU0167' , 'Tessier'    , 'Thibault' , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0168' , 'Chevallier' , 'Charlotte', 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0169' , 'Collin'     , 'Baptiste' , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0170' , 'Chauvin'    , 'Maria'    , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0171' , 'Da Silva'   , 'Paul'     , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0172' , 'Bouchet'    , 'Inès'     , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0173' , 'Gaillard'   , 'Gaetan'   , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0174' , 'Lemaitre'   , 'Yann'     , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0175' , 'Benard'     , 'Iris'     , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0176' , 'Marechal'   , 'Lila'     , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0177' , 'Humbert'    , 'Lilou'    , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0178' , 'Reynaud'    , 'Leo'      , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0179' , 'Antoine'    , 'Damien'   , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0180' , 'Hoarau'     , 'Roxane'   , 'G1L3EB'); 
INSERT INTO "EtudiantB" VALUES ('NU0181' , 'Perret'     , 'Eliot'    , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0182' , 'Barthelemy' , 'Elouan'   , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0183' , 'Cordier'    , 'Simon'    , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0184' , 'Pichon'     , 'Augustin' , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0185' , 'Lejeune'    , 'Léonie'   , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0186' , 'Gilbert'    , 'Lola'     , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0187' , 'Lamy'       , 'Vincent'  , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0188' , 'Delaunay'   , 'Jeanne'   , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0189' , 'Pasquier'   , 'Maëlle'   , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0190' , 'Carlier'    , 'Zélie'    , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0191' , 'Laporte'    , 'Raphael'  , 'G1L3EA'); 
INSERT INTO "EtudiantB" VALUES ('NU0192' , 'Muller'     , 'Mya'      , 'G1L3DZ'); 
INSERT INTO "EtudiantB" VALUES ('NU0193' , 'Lefevre'    , 'Elias'    , 'G1L3DZ'); 
INSERT INTO "EtudiantB" VALUES ('NU0194' , 'Faure'      , 'Élise'    , 'G1L3DZ'); 
INSERT INTO "EtudiantB" VALUES ('NU0195' , 'Andre'      , 'Louna'    , 'G1L3DZ'); 
INSERT INTO "EtudiantB" VALUES ('NU0196' , 'Mercier'    , 'Dorian'   , 'G1L3DZ'); 
INSERT INTO "EtudiantB" VALUES ('NU0197' , 'Blanc'      , 'Killian'  , 'G1L3DZ'); 
INSERT INTO "EtudiantB" VALUES ('NU0198' , 'Guerin'     , 'Olivia'   , 'G1L3DZ'); 
INSERT INTO "EtudiantB" VALUES ('NU0199' , 'Boyer'      , 'Alexis'   , 'G1L3DZ'); 
-- }}}


CREATE TABLE "Etudiant" AS SELECT 
   "nEtu","nom","pren",
   abs(random()%7)+16 AS age 
   FROM "EtudiantB";

CREATE TABLE "Groupe" AS SELECT 
   "nEtu" ,"groupe"
   FROM "EtudiantB";


CREATE TABLE "Mail" AS 
   WITH cxmail AS (
   SELECT nEtu,
         replace(replace(replace(replace(replace(replace(nom,
               'É','E'),'é','e'),'ë','e'),'ï','i'),'è','e'),' ','') AS nomEt,
         replace(replace(replace(replace(replace(replace(pren,
               'É','E'),'é','e'),'ë','e'),'ï','i'),'è','e'),' ','') AS prenEt,
         abs(random()%8) AS numT FROM  EtudiantB
   ) 
   SELECT "nEtu"
      ,lower(prenEt) || lower(nomEt) || '@' || NdomB.nom AS mailP
      ,lower(nomEt) || '.' || lower(prenEt) || '@' ||'ecoleAn.sup' AS mailS
   FROM cxmail,"NdomB"
   WHERE NdomB.num==numT
;

CREATE TABLE "Coeff"  AS SELECT 
   cours,groupe AS classe, abs(random()%3)+3 AS coef 
   FROM CoeffB;


CREATE TABLE "Examen" AS SELECT examen,cours, abs(random()%15)+18 AS pointMax FROM ExamenB;
CREATE TABLE "Note" AS 
   SELECT 
         EtudiantB.nEtu,
         Examen.examen,
         abs(random()%(Examen.pointMax+1))  AS point 
      FROM EtudiantB,CoeffB,Examen 
      WHERE 
         EtudiantB.groupe=CoeffB.groupe AND
         Examen.cours=CoeffB.cours
      ORDER BY random() ASC 
      LIMIT  -1 OFFSET 20;  -- certains étudiants n'ont pas passé leur examen.


DROP TABLE EtudiantB                      ;
DROP TABLE                  CoeffB        ;
DROP TABLE                         ExamenB;
DROP TABLE                         NdomB  ;


-- les requetes suivantes ne doivent rien afficher

--- sans doublon de mail
SELECT count(*) as s
FROM  (
   SELECT MailP as T FROM Mail
   UNION
   SELECT MailS as T FROM Mail
   )
GROUP BY T
HAVING s>1;
--

-- sans doublon d'examen
SELECT count(*) as s --
FROM examen
GROUP BY examen
HAVING s>1;

-- sans doublon d'examen
SELECT count(*) as s --
FROM Coeff
GROUP BY cours,classe
HAVING s>1;

-- sans doublon numEtu
SELECT count(*) as s --
FROM Etudiant
GROUP BY nEtu
HAVING s>1;

-- sans doublon examen,etudiant
SELECT count(*) as s
FROM Note
GROUP BY nEtu,examen
HAVING s>1;

--- ne doit rien afficher vérifie les mail
SELECT count(*) as s
FROM Mail
WHERE length(mailP)!=length(hex(mailP))/2 or
      length(mailS)!=length(hex(mailS))/2 
HAVING s>1;


                      
                      
