EXERCICE 1
Soit la base de données d’un festival de musique : Dans une représentation
peut participer un ou plusieurs musiciens. Un musicien ne peut participer
qu’à une seule représentation.
Representation (Num_Rep , titre_Rep , lieu)
Musicien (Num_mus , nom , #Num_Rep)
Programmer (Date , #Num_Rep , tarif)
La liste des titres des représentations.
SELECT titre_Rep FROM Representation
La liste des noms des musiciens et des titres et les titres des représentations auxquelles ils
participent.
SELECT [Link], R.titre_Rep FROM Musicien M INNER JOIN Representation R ON
R.Num_rep=M.Num_rep
La liste des titres des représentations ayant lieu au « théâtre allissa »
SELECT titre_Rep FROM Representation WHERE lieu="theatre␣allissa"
La liste des titres des représentations, les lieux et les tarifs du 25/07/2008
SELECT R.titre_Rep, [Link],[Link] FROM Programmer P INNER JOIN Representation R
ON P.Num_rep=R.Num_rep WHERE [Link]="25-07-2008"
Le nombre des musiciens qui participent à la représentations n°20
SELECT COUNT (*) FROM Musicien WHERE Num_rep =20
Les représentations et leurs dates dont le tarif ne dépasse pas 20DH
SELECT R.Num_Rep , R.titre_Rep , [Link] FROM Representation R INNER JOIN Programmer P
ON R.Num_Rep=P.Num_Rep WHERE [Link]<=20
EXERCICE 2
Soit la base de données suivante :
Départements :( DNO, DNOM, DIR, VILLE)
Employés : ( ENO, ENOM, PROF, DATEEMB, SAL, COMM, #DNO)
Donnez la liste des employés ayant une commission
SELECT * FROM Employes WHERE COMM NOT NULL
Donnez les noms, emplois et salaires des employés par emploi croissant, et pour chaque
emploi, par salaire décroissant
SELECT ENOM,PROF, SAL FROM Employes ORDER BY PROF ASC, SAL DESC
Donnez le salaire moyen des employés
SELECT AVG(SAL) FROM Employes
Donnez le salaire moyen du département Production
SELECT AVG([Link]) FROM Employes E INNER JOIN Departement D
ON [Link]=[Link] WHERE [Link]="production"
Donnes les numéros de département et leur salaire maximum
SELECT DNO, MAX(SAL) FROM Employes GROUP BY DNO
Donnez le salaire moyen par profession le plus bas
SELECT PROF, AVG(SAL) as moy FROM Employes
GROUP BY PROF
ORDER BY moy ASC
LIMIT 1
Donnez le ou les emplois ayant le salaire moyen le plus bas, ainsi que ce salaire moyen
SELECT PROF FROM Employes GROUP BY PROF
HAVING AVG(SAL)=(SELECT AVG(SAL) as moy FROM Employes
GROUP BY PROF ORDER BY moy ASC LIMIT 1)
EXERCICE 3
Soit le modèle relationnel suivant relatif à la gestion des notes annuelles
d’une promotion d’étudiants :
ETUDIANT(NEtudiant, Nom, Prénom)
MATIERE(CodeMat, LibelléMat, CoeffMat)
EVALUER(#NEtudiant, #CodeMat, Date, Note)
Quel est le nombre total d’étudiants ?
SELECT count(*) FROM ETUDIANT
Quelles sont, parmi l’ensemble des notes, la note la plus haute et la note la plus basse ?
SELECT MIN(Note) as ’plus basse note’, MAX(Note) as ’plus haute note’ FROM EVALUER
Quelles sont les moyennes de chaque étudiant dans chacune des matières ?
SELECT [Link], [Link]éMat, AVG([Link]) AS MoyEtuMat
FROM EVALUER EV, MATIERE M, ETUDIANT E WHERE [Link] = [Link] AND
[Link] = [Link]
GROUP BY [Link], [Link]éMat
Quelles sont les moyennes par matière ? Avec la vue MGETU de la question 3 ( MOYETUMAT)
SELECT LibelleMat, AVG(MoyEtuMat) FROM MOYETUMAT GROUP BY LibelleMat
Quelle est la moyenne générale de chaque étudiant ? Avec la vue MGETU de la question 3
( MOYETUMAT)
SELECT NEtudiant, SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT GROUP BY NEtudiant
Quelle est la moyenne générale de la promotion ? Avec la vue MGETU de la question 5
SELECT AVG(MgEtu) FROM MGETU
Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne
générale de la promotion? Avec la vue MGETU de la question 5
SELECT NEtudiant , Nom , Prenom , MgEtu FROM MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU)
EXERCICE 4
Soit la base de données intitulée "gestion_projet" permettant de gérer les
projets relatifs au développement de logiciels. Elle est décrite par la
représentation textuelle simplifiée suivante :
Developpeur (NumDev, NomDev, AdrDev, EmailDev, TelDev)
Projet (NumProj, TitreProj, DateDeb, DateFin)
Logiciel (CodLog, NomLog, PrixLog, #NumProj)
Realisation (#NumProj, #NumDev)
D’afficher les noms et les prix des logiciels appartenant au projet ayant comme titre « gestion
de stock », triés dans l’ordre décroissant des prix .
SELECT [Link], [Link] FROM Logiciel L INNER JOIN Projet P
ON [Link]=[Link] WHERE [Link]="gestion␣de␣stock"
ORDER BY [Link] DESC
D’afficher le total des prix des logiciels du projet numéro 10. Lors de l’affichage, le titre de la
colonne sera « cours total du projet ».
SELECT SUM(PrixLog) as "cout␣total␣du␣projet" FROM Logiciel WHERE NumPRoj=10
Afficher le nombre de développeurs qui ont participé au projet intitulé « gestion de stock »
SELECT count(*) FROM Developpeur D INNER JOIN Realisation R
ON [Link]=[Link] INNER JOIN Projet P ON [Link]=[Link]
Afficher les projets qui ont plus que 5 logiciels
SELECT NumProj, TitreProj FROM PRojet P INNER JOIN Logiciel L ON [Link]=[Link]
GROUP BY NumProj, TitreProj
HAVING count(*)>5
Les numéros et noms des développeurs qui ont participés dans tout les projets.
SELECT NumDev, NomDev FROM Developpeur D INNER JOIN Realisation R ON
[Link]=[Link]
GROUP BY NumDev, NomDev
HAVING count(*)=(SELECT COUNT(*) FROM Projet)
Les numéros de projets dans lesquelles tous les développeurs y participent dans sa réalisation.
SELECT NumProj, TitreProj FROM Projet P INNER JOIN Realisation R ON
[Link]=[Link]
GROUP BY NumProj, TitreProj
HAVING count(*)=(SELECT COUNT(*) FROM Developpeur)
