Exercice 7 BDD SQLSERVER

use master create database exerice7
go
use GESTION_NOTES_STAGIAIRES
go
---------------
create table STAGIAIRES	
(
NumS varchar(3) primary key,NomS varchar(30), PrenomS varchar(30), tel varchar(10))
go
create table EXAMENS
(NumE varchar(3) primary key, Salle varchar(3), DateE date, TypeE char(1))
go
create table PASSEREXAMEN
(NumS varchar(3) foreign key references STAGIAIRES(NumS), NumE varchar(3) foreign key references EXAMENS(NumE),
Note real, primary key(NumS, NumE))
go
------------------
insert into STAGIAIRES values('S01', 'Aoubaida', 'Mbarek', '0622332313'),('S02', 'Bellahsaouia', 'Hassan', '0622222222'),('S03', 'belkas', 'Brahim', '0633333333'),
('S04', 'Baba', 'Soumia', '0644444444'),('S05', 'Chaimae', 'Chaimae', '0655555555'),('S06', 'Nejli', 'Fatima zahra', '0666666666')
go

insert into EXAMENS values
('E01', 'A01', '01/01/2020','P'),('E02', 'A01', '02/01/2020','P'),('E03', 'A01', '03/01/2020','P'),
('E04', 'A02', '04/01/2020','T'),('E05', 'B01', '05/01/2020','T'),('E06', 'B02', '06/01/2020','S'),
('E07', 'A01', '01/01/2020','P'),('E08', 'A01', '01/01/2020','P'),('E09', 'A01', '03/01/2020','P')
GO


insert into PASSEREXAMEN values('S01', 'E01', 8),('S01', 'E02', 9),('S01', 'E03', 10),('S01', 'E04', 13),('S01', 'E05', 14),('S01', 'E06', 15),
('S02', 'E01', 7),('S02', 'E02', 11),('S02', 'E03', 12),('S02', 'E04', 13),('S02', 'E05', 16),('S02', 'E06', 17),
('S03', 'E01', 8),('S03', 'E02', 11),('S03', 'E03', 12),('S03', 'E04', 13),('S03', 'E05', 14),('S03', 'E06', 16),
('S04', 'E01', 10),('S04', 'E02', 11),('S04', 'E03', 12),('S04', 'E04', 13),('S04', 'E05', 14),('S04', 'E06', 15),
('S05', 'E01', 10),('S05', 'E02', 11),('S05', 'E03', 12),('S05', 'E04', 13),('S05', 'E05', 14),('S05', 'E06', 15),
('S06', 'E01', 10),('S06', 'E02', 11),('S06', 'E03', 12),('S06', 'E04', 13),('S06', 'E05', 14),('S06', 'E06', 15)
go
---------------------
--1. La liste des stagiaires ;
select * from STAGIAIRES 
GO
SELECT  (NomS +' ' +PrenomS) as 'Liste de stagiaires' from STAGIAIRES
go
--2. La liste des examens ;
SELECT * FROM EXAMENS
GO

--3. Les numéros de tous les stagiaires ;
select NumS AS 'Numero de stagiaire' from STAGIAIRES
go

--4. Les numéros des examens munis de la date de réalisation ;
select NumE as 'Numero d examen' , DateE as 'Date de realisation' from EXAMENS
GO 

--5. La liste des stagiaires triée par nom dans un ordre décroissant ;
select * from STAGIAIRES 
ORDER BY NomS desc
GO

--6. La liste des examens réalisés dans les salles 'A2' ou 'A3';
select * from EXAMENS where Salle like 'A01' or Salle like 'A02'
go

--7. La liste des examens pratiques ;
select * from EXAMENS where TypeE like 'P'
go

--8. La liste précédente triée par date de passation de l'examen ;
select * from EXAMENS where TypeE like 'P' order by DateE
go

--9. La liste des examens triée par salle dans un ordre croissant et par date dans un ordre décroissant ;
select * from EXAMENS  order by Salle asc, DateE desc
go

--10. Les numéros et les notes des examens passé par le stagiaire 'S01';
select NumE, Note from PASSEREXAMEN WHERE NumS like 'S01'
go

--11. Les numéros et les notes des examens passé par le stagiaire 'S01' et dont la note est supérieure ou égale à 15 ;
select NumE, Note from PASSEREXAMEN WHERE NumS like 'S01' and Note>=15
go

--12. Les stagiaires dont le nom contient la lettre 'u' ;
SELECT  (NomS +' ' +PrenomS) as 'Liste de stagiaires' from STAGIAIRES where NomS like '%u%'
go

--13. Les prénoms des stagiaires dont le prénom se termine par la lettre 'm' ;
SELECT  (PrenomS) as 'Prenoms des stagiaires' from STAGIAIRES where PrenomS like '%m'
go

--14. Les prénoms des stagiaires dont le prénom se termine par la lettre 'm' ou 'd' ;
SELECT  (PrenomS) as 'Prenoms des stagiaires' from STAGIAIRES where PrenomS like '%[md]'
go

--15. Les noms et prénoms des stagiaires dont le nom se termine par la lettre 'i' et le prénom par 'm' ;
SELECT  NomS ,PrenomS  from STAGIAIRES where NomS like '%i' and PrenomS like '%m'
go

--16. Les noms des stagiaires dont la deuxième lettre est 'a' ;
SELECT  NomS from STAGIAIRES where NomS like '_a%' 
go

--17. Les noms des stagiaires dont la deuxième lettre n'est pas 'a' ;
SELECT  NomS from STAGIAIRES where NomS not like '_a%' 
go

--18. La liste des examens pratiques réalisés dans une salle commençant par la lettre 'A';
select * from EXAMENS where TypeE like 'P' and Salle like 'A%'
go

--19. Toutes les salles dont on a réalisé au moins un examen ;******************************
select Salle from EXAMENS
group by Salle
having count(Salle) >=1
go

--20. La liste précédentes sans doublons ;*************************************************
select distinct Salle from EXAMENS


--21. Pour chaque examen, la première et la dernière note ;
select PE.NumE, max(PE.Note) as 'Note max', min(PE.Note) as 'Note min' 
from PASSEREXAMEN as PE
group by PE.NumE

--22. Pour l'examen 'E05', la première et la dernière note ;
select PE.NumE, max(PE.Note) as 'Note max', min(PE.Note) as 'Note min' 
from PASSEREXAMEN as PE
group by PE.NumE
having PE.NumE like 'E05'

--23. Pour chaque examens, l'écart entre la première et la dernière note ;
select PE.NumE, (max(PE.Note)- min(PE.Note) )as 'ecart' 
from PASSEREXAMEN as PE
group by PE.NumE

--24. Le nombre d'examens pratiques ;
select count(E.NumE) as 'numero des examens pratique'
from EXAMENS as E
where E.TypeE like 'P'

--25. La date du premier examen effectué ;
select min(DateE)
from EXAMENS as E

--26. Le nombre de stagiaires dont le nom contient 'b' ou 's' ;
select count(S.NumS)
from STAGIAIRES S
where S.NomS like '%[bs]%'

--27. Pour chaque stagiaires la meilleure note dans tous les examens ;
select (S.NomS +' '+ S.PrenomS) as 'nom de stagiaire', max(PE.Note) as 'note maximal'
from STAGIAIRES S inner join PASSEREXAMEN PE
on S.NumS=PE.NumS
group By (S.NomS +' '+ S.PrenomS)


--28. Pour chaque date enregistrée dans la base de données le nombre d'examens ;
select DateE, count(NumE) as 'Nombre des examens passé' from EXAMENS group by DateE

--29. Pour chaque salle le nombre d'examens réalisés ;
select Salle, count(NumE) as 'Nombre des examens realise' from EXAMENS group by Salle

--30. Le nombre d'examens réalisés dans la salle 'B5';
select Salle, count(NumE) as 'Nombre des examens realise' from EXAMENS group by Salle having Salle like'B5'

--31. Toutes les salles dont on a effectué au moins deux examens ;
select Salle, count(NumE) as 'Nombre des examens realise' from EXAMENS group by Salle having count(NumE)>=2

--32. Toutes les salles dont on a effectué exactement 3 examens ;
select Salle, count(NumE) as 'Nombre des examens realise' from EXAMENS group by Salle having count(NumE)=3

--33. Le nombre d'examens réalisés dans les salle commençant par la lettre 'A' ;
select count(NumE) as 'Nombre des examens realise' from EXAMENS where Salle like 'A%'

--34. Pour chaque salle commençant par la lettre 'A', le nombre d'examens ;
select Salle, count(NumE)as 'Nombre des examens realise' from EXAMENS group by Salle having Salle like'A%'

--35. Les salles qui commence par 'A' et dont on a effectué deux examens.
select Salle, count(NumE)as 'Nombre des examens realise' from EXAMENS group by Salle having Salle like'A%' and count(NumE)=2