exercice SQL les requêtes avec corrigé
Exercice
On considère le schéma relationnel suivant qui
recense une liste de professeurs rattachés à des départements et leurs dépenses
de mission (nature : carburant, hôtellerie, repas, etc.) :
·
PROFESSEUR (CodeProf, NomProf, Département)
·
DEPENSE (CodeDépense, DateDépense, MontantDépense,
Nature, CodeProf*)
Exprimez en SQL, les requêtes suivantes :
1. Donnez les noms des
professeurs ayant au moins une dépense d’un montant supérieur à 1000
2. Donnez le nom des
professeurs du département d’informatique ayant effectué une mission le
15/02/2016 avec une dépense d’hôtellerie supérieure à 5000 Da
3. Donnez les montants
dépensés par les professeurs du département de chimie qui ont un numéro
supérieur à celui des professeurs « Talbi » et « Mahi ».
4. Donnez le nom du
professeur ayant effectué la plus grande dépense de carburant.
corrigé
·
PROFESSEUR (CodeProf, NomProf, Département)
·
DEPENSE (CodeDépense, DateDépense, MontantDépense,
Nature, CodeProf*)
Exprimez en SQL, les requêtes suivantes :
1.
Donnez les noms des professeurs ayant au moins une
dépense d’un montant supérieur à 1000
SELECT NomProf
FROM PROFESSEUR
WHERE CodeProf IN
SELECT CodeProf
FROM DEPENSE
WHERE MontantDepense > 1000
OU
SELECT NomProf
FROM PROFESSEUR P,
DEPENSE D
WHERE P.CodeProf=D.CodeProf
AND MontantDepense
> 1000
2.
Donnez le nom des professeurs du département
d’informatique ayant effectué une mission le 15/02/2016 avec une dépense
d’hôtellerie supérieure à 5000 Da
SELECT NomProf
FROM PROFESSEUR
WHERE Departement = 'Informatique'
AND CodeProf IN
SELECT CodeProf
FROM DEPENSE
WHERE MontantDepense
> 5000
AND Nature =
'Hotellerie'
OU
SELECT NomProf
FROM PROFESSEUR
P, DEPENSE S
WHERE P.CodeProf=D.CodeProf
AND Departement
= 'Informatique'
AND MontantDepense
> 5000
AND Nature = 'Hotellerie'
3. Donnez les montants
dépensés par les professeurs du département de chimie qui ont un numéro
supérieur à celui des professeurs « Talbi » et « Mahi »
SELECT NomProf
FROM PROFESSEUR
WHERE Departement =
'Informatique'
AND CodeProf >ALL (SELECT
CodeProf
FROM PROFESSEUR
WHERE NomProf IN ('Talbi',
'Mahi') )
OU
WHERE NomProf = 'Tabli' OR
NomProf='Mahi')
4. Donnez le nom du
professeur ayant effectué la plus grande dépense de carburant
SELECT NomProf
FROM PROFESSEUR
WHERE CodeProf IN
SELECT codeProf
FROM DEPENSE
WHERE Nature = ‘carburant’
AND MontantDépense = (SELECT
MAX (MontantDépense)
FROM DEPENSE
WHERE Nature = 'Carburant')
OU
SELECT NomProf
FROM PROFESSEUR P, DEPENSE
D
WHERE P.CodeProf = D.CodeProf
AND Nature = ‘carburant’ AND MontantDépense = (SELECT MAX(MontantDépense)
FROM DEPENSE
WHERE Nature = 'Carburant')
تعليقات