Créer et remplir une base de donnés

create table etudiant ( numEtu integer auto_increment, nomEtu varchar(50), dtnaissance date, sexe varchar(50), Primary key (numEtu)) engine=innobd;

create table Matiere (NM integer auto_increment, nomMatiere varchar(50), coeff float, numEns integer, primary key(NM), foreign key (numEns) references Enseignant (NE)) engine=innodb;

 create table Notes (numEtu integer, NumM integer, note float not null, primary key (numEtu, NumM), foreign key (numEtu) references etudiant (numEtu), foreign key (NumM) references Matiere (NM)) engine=innodb;

 insert into Enseignant values (1,"Julie Boyer", "MCF", "2000-10-10"), (2,"Dupont", "PR", "1990-10-01"), (3,"Durand", "MCF", " 2010-10-02"), (4,"Durieux", "PR", "1990-10-03");

 insert into Matiere values (1, "Histoire", 1 , 1), (2, "Sociologie", 2, 3), (3, "Informatique", 1, 2 ), (4, "Mathematique", 3, 4 ),(5, "Français", 2, 1 );

 insert into Etudiant values (251, "Emmanuel Paire", "1989-12-03", "M" ), (252, "Jeanne Phili","1988-01-11", "F");

 insert into Notes values (251, 1, 18), (251, 2, 14), (251, 3, 17 ), (251, 4, 17), (251, 5, 17), (252, 1, 10), (252, 2, 13), (252, 3, 13), (252, 4, 11), (252, 5, 16) ;

 Insert into Etudiant values(271, "Rachel Chu", "1989-08-30", "F");

Insert into Etudiant values(272, "Nick Young", "1987-02-18", "M");

Insert into Notes  values (271,1,14), (271,2,12), (271,3,18), (271,4,20), (271,5,13.5), (272,1,20), (272,2,6.5), (272,3,9), (272,4,13), (272,5,12);

 insert into Etudiant values (101, "Martinez", "1994-09-04","f"),

 insert into Etudiant values(102,"Dumont","1986-02-03","m");

insert into Notes values (101,1,12.5), (101,2,2.5), (101,3,14), (101,4,20), (101,5,9.9), (102,1,12), (102,2,16), (102,3,4), (102,4,17), (102,5,11.75);

insert into Etudiant values(261,"Jean Bhon","2003-11-06","m");

insert into Etudiant values (262,"Elsa Preine","2005-09-25","f");

 Insert into Notes values (261,1,10), (261,2,15), (261,3,12), (261,4,18), (261,5,9), (262,1,17), (262,2,10), (262,3,12), (262,4,13), (262,5,14);

 1. select sexe, AVG(floor(datediff(date("2000-01-01"),dtnaissance)/365)) as age from Etudiant group by sexe ;

 2. select nom, grade from enseignant join matiere on matiere.numEns=Enseignant.NE where nomMatiere="histoire";

 3. select numEtu,nomEtu from etudiant join notes on note.numEtu=etudiant.numEtu join matiere on matiere.nomMatiere where nomMatiere="Sociologie" and notes.note=null ;  no

 select Etudiant.numEtu,nomEtu from etudiant join notes on notes.note=etudiant.numEtu join matiere on matiere.nomMatiere where matiere.nomMatiere="Sociologie" ; no

correction :

 Select numEtu, nomEtu from etudiant where numEtu not in (select numEtu from  matiere join notes on notes.NumM=matiere.NM where nomMatiere="sociologie");

 select numEtu, nomEtu form etudiant minus select etudiant.numEtu, nomEtu from etudiant join not on etudiant.numEtu=notes.numEtu join matiere on note.NumM=matiere.NM where matiere="sociologie"; marche pas

...