1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
|
drop table examen;
drop table matiere;
drop table eleve;
drop table classe;
create table classe (id int primary key identity, nom varchar(50) not null unique);
create table eleve (id int primary key identity, nom varchar(50) not null, classe_id int not null references classe(id));
create table matiere (id int primary key identity, nom varchar(50) not null unique);
create table examen (id int primary key identity, eleve_id int not null references eleve(id), matiere_id int not null references matiere(id), note decimal(3,1) null);
insert into classe (nom) values ('Seconde');
insert into classe (nom) values ('Première');
insert into classe (nom) values ('Terminale');
insert into eleve (nom, classe_id) values ('Alain', 1);
insert into eleve (nom, classe_id) values ('Alfred', 1);
insert into eleve (nom, classe_id) values ('Bruno', 2);
insert into eleve (nom, classe_id) values ('Benoît', 2);
insert into eleve (nom, classe_id) values ('Alain', 3);
insert into eleve (nom, classe_id) values ('Bruno', 3);
insert into matiere (nom) values ('Mathématiques');
insert into matiere (nom) values ('Histoire');
insert into matiere (nom) values ('Anglais');
insert into examen (eleve_id, matiere_id, note) values (1, 1, 15);
insert into examen (eleve_id, matiere_id, note) values (1, 2, NULL);
insert into examen (eleve_id, matiere_id, note) values (1, 3, 8.5);
insert into examen (eleve_id, matiere_id, note) values (2, 1, 9.5);
insert into examen (eleve_id, matiere_id, note) values (2, 2, 16);
insert into examen (eleve_id, matiere_id, note) values (2, 3, 12);
insert into examen (eleve_id, matiere_id, note) values (3, 1, 6);
insert into examen (eleve_id, matiere_id, note) values (3, 2, 7.5);
insert into examen (eleve_id, matiere_id, note) values (3, 3, 4);
insert into examen (eleve_id, matiere_id, note) values (4, 1, 12);
insert into examen (eleve_id, matiere_id, note) values (4, 2, 20);
insert into examen (eleve_id, matiere_id, note) values (4, 3, 14);
insert into examen (eleve_id, matiere_id, note) values (5, 1, 16);
insert into examen (eleve_id, matiere_id, note) values (5, 3, 14);
insert into examen (eleve_id, matiere_id, note) values (6, 1, 2);
insert into examen (eleve_id, matiere_id, note) values (6, 3, 4);
select m.nom, count(*) -- 6 examens pour mathématiques et anglais, et 4 pour histoire
from classe c
inner join eleve e on e.classe_id = c.id
inner join examen x on x.eleve_id = e.id
inner join matiere m on m.id = x.matiere_id
group by m.nom;
select m.nom, count(x.note) -- 6 notes pour l'anglais et les maths, 3 pour l'histoire (Alain de seconde absent : NULL)
from classe c
inner join eleve e on e.classe_id = c.id
inner join examen x on x.eleve_id = e.id
inner join matiere m on m.id = x.matiere_id
group by m.nom;
select m.nom, count(distinct e.nom) -- 4 noms d'élève différents ont passé un examen dans chacune des trois matières
from classe c
inner join eleve e on e.classe_id = c.id
inner join examen x on x.eleve_id = e.id
inner join matiere m on m.id = x.matiere_id
group by m.nom; |
Partager