Travaux Pratiques: Base de Données SQL
TP N°1
SQL*PLUS
Soit la base de données relationnelle suivante relative à la gestion des vols dans un aéroport.
PILOTE (NumPilote numérique de taille 10, Nom caractère variable de taille 20,
DateEmbauche date , #NumCompagnie numérique de taille 6)
Vol (#NumAvion numérique de taille 15, #NumPilote numérique de taille 10, DateVol date,
#AéroportDépart caractère variable de taille 15, #AéroportArrivée caractère
variable de taille 15, NbrePassagers numérique sur 3 positions, Coût numérique sur
9 positions dont 3 après la virgule)
Aéroport(NomAéroport caractère variable de taille 15, pays caractère variable de taille 20,
surface numérique sur 8 positions)
Avion (NumAvion numérique de taille 15, TypeAvion caractère variable sur 10 positions,
#NumCompagnie numérique de taille 6, DatePremierVol date, NbrePlaces numérique de
taille 4)
Compagnie (NumCompagnie numérique de taille 6, NomCompagnie caractère variable
sur 20 positions, Pays caractère variable de taille 20)
Créer les tables ci-dessus en prenant en considération les contraintes d’intégrité
correspondantes et sachant que :
1. Le coût du vol doit être strictement positif.
2. Le nom de la compagnie doit être saisi.
3. Deux compagnies ne peuvent pas avoir le même nom.
4. Pour une compagnie, le pays vaut ‘par défaut Tunisie’.
5. La date du vol correspond par défaut à la date d’aujourd’hui.
6. La surface d’un aéroport est comprise entre 20000 et 40000.
7. Pour le même vol, les aéroports de départ et d’arrivée ne peuvent pas être les mêmes.
8. Le nombre de places dans un vol doit dépasser 40.
Travaux Pratiques: Base de Données SQL
TP N°2
SQL*PLUS
On considère la base de données relationnelle relative à la gestion des vols dans un aéroport.
(Voir TP 1)
1. Ajouter le champ Téléphone de type numérique de longueur 12 à la table Pilote
2. Ajouter le champ Grade dans la table Pilote de type numérique sur une position, ne
pouvant prendre que les values 1, 2, ou 3 et valant par défaut 1.
3. Modifier le champ Nom de manière qu’il soit caractère de taille 30
4. Supprimer la clé primaire de la table Aéroport.
5. Remettre la base de données à son état initial avant la question précédente.
6. Créer un synonyme P pour la table pilote.
7. Supprimer la contrainte clé étrangère de la table Pilote.
8. Remettre la clé étrangère de la table Pilote.
9. Renommer la table Aéroport en A
10. Supprimer la table Pilote.
11. Modifier la table AVION de manière que le nombre de places dans un vol soit > 30.
12. Donner la commande permettant d’afficher toutes les contraintes de la table Vol.
Combien de contraintes seront Affichées ?
Travaux Pratiques: Base de Données SQL
TP N°3
SQL*PLUS
Soit la base de données relationnelle suivante :
PRODUIT ( NumPROD : numérique de taille 8, DesProd : caractère variable de
taille 10, QteStock : numérique de taille 8, NumCat : numérique de taille 8)
MACHINE ( NumMach : numérique de taille 10, Fonction : caractère de taille 15,
CodAtelier : numérique de taille 8)
ATELIER ( CodAtelier : numérique de taille 8, CodeResp : caractère variable de
type 10, Superficie : numérique de taille 7 dont 3 après la virgule, Spécialité : caractère
variable de taille 15)
CATEGORIE ( NumCat : numérique de taille 8, LibCat : caractère variable de
taille 15)
EMPLOYE (CodEmp : caractère variable de type 10, NomEmp : caractère variable
de taille 20, PrenEmp : caractère variable de taille 20, Grade : caractère de taille 1)
TACHE (CodEmp : caractère variable de type 10, NumMach : numérique de taille
10, NumPROD : numérique de taille 8, DateJour : date, NbrePiece : numérique de
taille 6)
Cette base concerne les données d’une entreprise de confection. Cette entreprise est
composée de plusieurs ateliers contenant chacun différentes machines. Pour atelier, on
affecte un responsable parmi les employés de la société.Cette entreprise fabrique des
produits appartenant chacun à une catégorie.
Chaque jour, chaque employé est affecté à une machine pour fabriquer un produit
particulier.
1. Créer les tables ci-dessus en prenant en considération les contraintes d’intégrité
correspondantes et sachant que :
a) Les catégories possibles sont 1, 2, 3 et 4.
b) La quantité en stock d’un produit doit être positive et non nulle.
c) La superficie d’un atelier doit être comprise entre 10m² et 100m²
d) La désignation de produit ne peut pas avoir la même valeur pour deux produits
différents.
e) Le champ grade de la table employé ne peut pas être vide.
2. Elaborer les modifications suivantes :
a) Ajouter le champ Etat à la table MACHINE de type caractère variable de taille
10 ayant comme valeur par défaut ‘VALIDE’.
Travaux Pratiques: Base de Données SQL
b) Ajouter une contrainte sur la le champ Etat pour ne pourvoir contenir que
‘VALIDE’ ou ‘NONVALIDE’
c) Modifier le champ DesProd de la table PRODUIT qui deviendra de type
caractère de taille 15.
d) Supprimer la contrainte clé primaire de TACHE.
e) Remettre la contrainte clé primaire de TACHE à son état initial.
f) Changer le nom de la table PRODUIT en ARTICLE.
g) Supprimer la contrainte clé étrangère de la table ATELIER.
h) Créer un synonyme P1 de la table EMPLOYE
i) Supprimer le champ Grade de EMPLOYE
3- Afficher toutes les contraintes de la table TACHE.
Travaux Pratiques: Base de Données SQL
TP N°4
SQL*PLUS
On considère la base de données du TP3 concernant l’entreprise de confection.
Partie1 :
Remplir les tables de la base par les données des tableaux suivants :
Produit :
NumProd DesProd QteStock Numcat
1 Chemise10 10 1
2 chemiseF 15 2
3 PantalonM10 25 1
4 PullF128 5 1
5 JupeF114 100 3
6 pullG204 15 4
7 chemiseH 25 3
Machine :
NumMach Fonction CodAtelier Etat
10 Couture 100 NonValide
20 coupe 100 Valide
30 repassage 100 Valide
40 couture 200 Valide
50 couture 100 Valide
Atelier :
CodAtelier CodeResp Superficie Spécialité
100 1000 20 Habillement
200 2000 60 Combinaisons
300 3000 20 Habillement
400 4000 Habillement
Catégorie :
NumCat LibCat
1 Homme
2 Femme
3 Garçon
4 Fillette
Travaux Pratiques: Base de Données SQL
Employé :
CodEmp Nom Prénom
1000 chebbi ali
2000 ameur lilia
3000 ameur mounir
4000 Bahri slim
5000 badri Ahmed
Tâche :
CodEmp NumMach Numprod Date NbrePieces
1000 10 1 12/04/04 20
1000 10 1 20/04/04 5
1000 10 2 12/04/04 15
2000 10 1 13/04/04 25
2000 20 3 12/04/04 30
3000 20 4 12/03/03 40
4000 30 4 01/02/03 2
4000 40 4 25/12/02 30
5000 20 2 30/03/03 100
2000 30 5 20/02/03 20
Partie 2 :
Ecrire les commandes SQL permettant :
1- Mettre le champ Etat à « valide » pour la machine n°1
2- Supprimer l’atelier n° 300.
3- Remplacer le prénom de l’employé n° 3000 par mounira.
4- Modifier la table machine de manière à notifier une panne de la machine numéro 2
Partie 3 :
Formuler les requêtes suivantes en SQL :
1- Afficher tous les employés de l’entreprise.
2- Afficher les numéros des machines valides.
3- Afficher les codes des ateliers dont la superficie est comprise entre 50 et 100.
4- Afficher les numéros et les désignations des produits ayant comme catégorie 1, 2 ou 3.
5- Afficher la liste des employés dont le nom commence par « a » et que le prénom contient
« n ».
6- Afficher les codes des employés qui ont travaillé sur la machine N°10 entre le 1/4/04 et
aujourd’hui.
7- Afficher les codes des ateliers dont la superficie est indéterminée.
8- Afficher les codes des employés qui sont responsables d’ateliers ainsi que les codes de
leurs ateliers respectifs.
9- Créer la table Machine10 contenant la liste des tâches faites sur la machine 10.
10- Créer la table Tache04 contenant la liste des tâches effectuées pendant le mois d’avril
2004
Travaux Pratiques: Base de Données SQL
TP N°5
SQL*PLUS
On considère la base de données du TP3 concernant l’entreprise de confection.
1- Afficher les numéros et les quantités en stock de toutes les chemises.
2- Créer les tables Cat1 et Cat2 ayant pour schéma (codProd, Desig, Qte) contenant
respectivement les produits de catégorie 1 et 2.
3- Afficher les numéros des produits fabriqués en 2003.
4- Afficher les employés de manière que leurs noms soient en majuscule et leur prénom avec
la première lettre uniquement en majuscule.
5- Afficher la liste des employés dont le nom et le prénom ne commencent pas par la même
lettre.
6- Afficher la liste des employés dont le prénom se termine par la première lettre de leurs
noms.
7- Afficher par employé son numéro et son pseudonyme composé par les trois premières
lettres de son nom en minuscule exceptée la première suivie par les trois premières lettres
de son prénom en mettant la première d’entre eux en majuscule. (Exemple le pseudonyme
du premier employé est CheAli).
8- Afficher les numéros des produits fabriqués pendant l’année en cours.
9- Afficher la liste des tâches effectuées pendant les deux derniers mois.
10- Afficher les numéros des employés qui sont actifs pendant les trois derniers mois.
11- Ajouter le champ DateEmb dans employé contenant par défaut (1/1/1991).
12- Ajouter le champ DateNaissance dans employé contenant les valeurs mentionnées par le
tableau suivant :
NumEmp Date de naissance
1000 12/04/70
2000 20/02/65
3000 30/01/40
4000 10/09/65
5000 1/12/63
13- Afficher tous les employés et les dates d’édition de leurs premières fiches de paie.
14- Afficher pour chaque employé son âge lors de son embauche.
15- Afficher pour chaque employé son numéro, son nom, son prénom et le nombre d’années
de travail qu’il a effectué.
16- Afficher pour chaque employé sa date de retraite prévue à l’âge de 60 ans ou après 30 ans
de service.
Travaux Pratiques: Base de Données SQL
TP N°6
SQL*PLUS
On considère la base de données du TP3 concernant l’entreprise de confection.
Formuler les requêtes suivantes sous SQL :
1. Afficher le nombre des catégories des produits existants en stock.
2. Afficher la quantité de produits fabriqués par l’employé n°1000 entre le 1/1/2004 et
aujourd’hui.
3. Afficher le numéro de l’employé ayant fabriqué en un jour un maximum de produits.
4. Afficher les numéros des produits de même catégorie que le produit n°1.
5. Afficher les numéros des machines de même fonction qu’une machine de l’atelier n°100.
6. Afficher la liste des produits fabriqués avec une quantité supérieure à toutes les quantités
de fabrications du produit n° 1.
7. Afficher les noms et prénoms des employés actifs entre 1/1/2004 et 31/5/2004.
8. Afficher les désignations des produits fabriqués par l’employé n°1.
9. Afficher les désignations des produits fabriqués par l’employé SLIM.
10. Afficher les noms et prénoms des employés qui sont des chefs d’ateliers triés par ordre
alphabétique croissant des noms.
11. Afficher les noms des employés qui ont fabriqué des vêtements pour Homme triés par
ordre décroissant.
12. Afficher la liste des employés ayant assuré la tâche de couture durant les six derniers mois
triés selon leurs noms croissants et leurs prénoms croissant.
13. Afficher le nombre de machines par atelier.
14. Afficher la totalité des quantités fabriquées par numéro d’employé.
15. Afficher le nombre de produits fabriqués par machine.
16. Afficher le nombre de produits par catégorie.
17. Afficher le nombre de produits par catégorie, on affiche le libellé de la catégorie.
18. Afficher le nombre de produits par catégorie, on affiche par catégorie son numéro et son
libellé.
19. Afficher les numéros des produits ayant été fabriqués avec une quantité totale >100.
20. Afficher par employé, le nombre d’articles différents qu’il a fabriqué entre le 1/1/2004 et
31/5/2004.
