Unité d'Enseignement Bases de Données
TP7/8
|
Exercice 1
On considère le modèle relationnel suivant :
- Employé(eid:numérique,
nom:texte, age:numérique,
salaire:numérique)
Un employé a un certain age, il possède
un numéro (eid), il a un nom
et touche un certain salaire.
- Travaille(eid:numérique,
did:numérique, pourcentage:numérique)
eid référence Employé.eid
et did référence Département.did
Un employé de numéro
eid, travaille un certain pourcentage
de son temps dans le département de numéro did.
- Département(did:numérique,
nom:texte, budget:numérique,
managerid:numérique)
managerid référence Employé.eid
Un département de numéro did possède un
nom, dispose d'un certain budget
de fonctionnement et est dirigé par un manager possédant un numéro d'employé
(managerid)
Recopiez la base de données entreprise.mdb
dans un dossier tp7 que vous aurez créé. Sur les tables de cette
base, construisez les requêtes SQL ci-dessous (donnez un nom différent à chacune
des requêtes et enregistrez la dans votre base) :
- Donner
le noms et l'age des employés qui travaillent à la fois au département ordinateurs
et au département logiciels (visualiser
le résultat)
- Pour chaque
département ayant plus de 2 équivalent-employés à temps plein (la somme des
pourcentages des employés d'un tel département doit être supérieure à 200)
donner le numéro de département et le nombre d'employés travaillant dans ce
département. (visualiser
le résultat)
- Donner le nom
de chaque employé dont le salaire excède le budget de fonctionnement de chacun
des départements dans lequel il travaille.
(visualiser
le résultat)
- Donner le numéro
des managers qui dirigent uniquement des départements dont le budget est supérieur
à 30 000 euros.
(visualiser
le résultat)
- Donner
le(s) nom(s) du ou des manager(s) qui dirige(nt) le(s) département(s) ayant
le plus gros budget.
(visualiser
le résultat)
- Lorsqu'un manager
dirige plus d'un département, il contrôle la somme de tous les budgets de
ses départements. Donner les numéros des managers qui contrôlent plus de 200
000 euros.
(visualiser
le résultat)
- Pour chaque
département, donner l'age moyen, le plus gros, le plus petit salaire et le
salaire moyen des employés qui y travaillent. (visualiser
le résultat)
Exercice 2
On considère le modèle relationnel suivant :
- Vol(volid:numérique,
vdepart:texte, varrivee:texte,
distance:numérique, hdepart:heure,
harrivee:heure, prix:numérique)
Un vol, identifié par volid, relie
une ville de départ (vdepart) à une ville
d'arrivée (varrivee). Les villes sont
séparées d'une certaine distance (en km).
Le vol part à une certaine heure (hdepart)
pour arriver à une autre heure (harrivee)
et coute un certain prix.
- Avion(aid:numérique,
nom:texte, distance:numérique)
Un avion, identifié par aid, est désigné
par un nom et est capable de voler une
certaine distance (en km) sans faire escale
(rayon d'action).
- Employe(eid:numérique,
nom:texte, salaire:numérique)
Un employé, identifié par eid, possède
un nom et touche un certain salaire.
- Habilité(eid:numérique,
aid:numérique)
eid référence Employé.eid
et aid référence Avion.aid
Un employé, identifié par eid est habilité
à piloter les avions identifiés par aid.
Recopiez la base de données aeroport.mdb
dans le dossier tp8. Sur les tables de cette base, construisez les requêtes
SQL ci-dessous (donnez un nom différent à chacune des requêtes et enregistrez
la dans votre base) :
- Donner les noms des avions tels
que tous les pilotes habilités à les piloter gagnent plus de 100 000 euros.
(visualiser
le résultat)
- Pour chaque pilote qui est habilité
pour au moins trois avions, afficher le numéro d'employé (eid) et le rayon
d'action maximum des avions pour lesquels il est habilité.
(visualiser
le résultat)
- Donner le nom des pilotes dont le
salaire est plus petit que 50 fois le prix du vol sans escale le moins cher
entre Paris et Sydney.
(visualiser
le résultat)
- Pour chaque nom d'avion, donner le
salaire moyen et la somme des salaires des pilotes habilités à piloter des
avions de ce nom.
(visualiser
le résultat)
- Pour chaque nom d'avion, compter le
nombre de pilotes habilités à piloter des avions de ce nom.
(visualiser
le résultat)
- Donner le nom et le salaire des pilotes
habilités à piloter des avions capables de voler sur des distances supérieures
ou égales à la distance maximale des vols sans escale entre Paris et Sydney.
(visualiser
le résultat)
- Donner les noms des pilotes habilités
à piloter des Airbus A340.
(visualiser
le résultat)
- Donner les numéros d'avions (aid)
pour tous les avions pouvant être utilisés pour tous les vols sans escale
de Paris à Sydney.
(visualiser
le résultat)
- Donner les numéros des pilotes (eid)
habilités à piloter des avions dont le rayon d'action est supérieur à 5000
km mais ne sont pas habilités à piloter des Airbus A340.
(visualiser
le résultat)
- Un client veut voyager de Paris à
Dakar avec pas plus d'un changement d'avion. Lister les choix d'heure de départ
depuis Paris, si le client veut arriver avant 20h.
(visualiser
le résultat)
- Donner le nom et le salaire de tous
les employés non pilote dont le salaire est supérieur au salaire moyen des
pilotes.
(visualiser
le résultat)
- Donner le nom d'employé pour les employés
habilités uniquement pour des avions dont le rayon d'action est supérieur
à 5000 km mais sur au moins deux avions de ce type. (visualiser
le résultat)