TD1 SQL
Exercice 1 :
EMPLOYE (MatEmp, Nom, Grade, nb_rec)
RECLAMATION (Num_Rec, dat, Numero, MatEmp#)
PB_TECHNIQUE (NumPb,Zone, Résolut, Num_Rec#)
(Les clés primaires sont soulignées et les clés étrangères sont suivies du symbole #)
Chaque employé est identifié par son matricule de type number(5), possède un nom (de type
varchar2(15), un Grade (de type varchar2(15)) et un nombre de réclamations pris en charge (nb_rec de
type number(4) et prend par défaut 0). La présence de Grade est obligatoire. Le nom de l’employé est
unique.
Chaque réclamation est identifié par un numéro (Num_Rec de type number(5)) et est effectuée à une
date donnée (l’attribut dat est de type date et prend par défaut la date système). Une réclamation
concerne un numéro de téléphone (numéro de type number(8)) et est prise en charge par un employé.
Une réclamation est faite suite à l’apparition d’un problème technique. Ce dernier est identifié par un
numéro (NumPb de type number(3)) et est localisé à une zone bien déterminée (de type varchar2(10).
L’attribut Résolut est de type number(1). Il prend les valeurs de 0 à 2 ; 0 pour non résolut, 1 pour
résolut temporairement et 2 pour résolut.
Q1 : Créer la BD ci-dessus décrite en précisant seulement la contrainte not null et les valeurs par
défaut, si c’est demandé.
Q2 : En utilisant l’instruction alter, ajouter les contraintes nécessaires.
Q3 : Ajouter l’attribut Type (de type varchar2(1)) à la table PB_TECHNIQUE.
Q4 : Sachant que l’attribut Type ne prendre que les valeurs ‘A’, ‘B’, ‘C’ et ‘D’, ajouter la contrainte
nécessaire.
Q5 :
- Insérer l’employé Ali avec le matricule 1000, le grade OUVRIER.
- Insérer l’employé Fathi avec le matricule 1001, le grade CHEF et un nb_rec égale à 4.
- Insérer la réclamation numéro 1122 avec un numéro de portable 99123456 et un MatEmp égale à
1000.
- Faites les modifications nécessaires dans la table EMPLOYE.
- Insérer le problème technique numéro 111 localisé à Sfax concernant la réclamation 1122.
Q6 : Supprimer de la base toute trace de l’employé 1000.
Exercice 2 :
ENSEIGNANT (CIN, NomEns, Grade, DateRec, DirRech#, Sal)
FILIERE (NomF, PReussite, EnsResp#)
GROUPE (NumG, NomF#, NbEtud)
ENS_GROUPE (CIN#, NumG#, NomF# )
(Les clés primaires sont soulignées et les clés étrangères sont suivies du symbole #)
La table ENSEIGNANT contient les enseignants d’un établissement. Chaque enseignant est identifié
par le numéro de sa carte d’identité (CIN : composé de 8 chiffres), possède un nom (de type
varchar2(15)), un grade de type varchar2(2) (les grades possibles sont : A, MA, MC, P), une date de
recrutement (DateRec : qui prend par défaut la date système) et un salaire (de type décimale de la
forme : 4 chiffres à gauche de la virgule et 3 chiffres à droite). Chaque enseignant est dirigé, dans ces
recherches, par un directeur de recherche (DirRech), lui-même un enseignant.
Chaque filière est identifiée par son nom (de type varchar2(4)) et possède un enseignant responsable
(EnsResp). On calcule pour chaque filière le pourcentage de réussite (PReussite : de type décimale de
la forme : 3 chiffres à gauche de la virgule et 2 chiffres à droite). Le PReussite est entre 0 et 100.
Chaque groupe est identifié par le couple numéro groupe (NumG : de type number (2)) et NomF. Nous
avons dans chaque groupe un nombre d’étudiant (de type number(2)) qui ne doit pas passer 30.
Chaque enseignant peut enseigner plusieurs groupes et chaque groupe est enseigné par plusieurs
enseignants : cette relation est concrétisée dans la table ENS_GROUPE.
Q1 : Créer la table ENSEIGNANT et précisez toutes les contraintes nécessaires.
Q2 : En supposant que la table FILIERE est créée sans aucune contrainte. Ajouter les contraintes
nécessaires.
Q3 : En supposant que la table GROUPE est créée sans aucune contrainte. Ajouter les contraintes
nécessaires.
Q4 : Créer la table ENS_GROUPE et précisez toutes les contraintes nécessaires.
Exercice 3 :
PATIENT (NumPat, NomPat, PrenomPat, AdrPat)
MÉDECIN (NumMed, NomMed, PrenomMed, Spécialité)
MÉDICAMENT (NumMedic, NomMedic, Prix, QteStock)
ORDONNANCE (NumOrd, DateOrd, NumMed#, NumPat#)
VENTE (NumOrd#, NumMedic#, QteVendue)
(Les clés primaires sont soulignées et les clés étrangères sont suivies du symbole #)
De plus, on vous fournit les informations suivantes :
Un patient est caractérisé par les attributs suivants : NumPat (Clé primaire et nombre de taille 4),
NomPat, PrenomPat et AdrPat.
Un médecin est identifié par un numéro (Un nombre de taille 4), et il est caractérisé par un nom, un
prénom et une spécialité. Il est à noter que :
- NomMed et PrenomMed : Présence obligatoire.
- Si aucune valeur n’a été introduite pour l’attribut Spécialité, le système affecte automatiquement la
spécialité Généraliste.
- La Spécialité ne peut prendre que les valeurs suivantes : Généraliste, Dentiste, ORL.
Pour la table Médicament, on retient les informations et les contraintes suivantes :
- Les numéros des médicaments sont des nombres de taille 4.
- Les noms des médicaments doivent être tous différents.
- Les prix (4 chiffres à gauche du virgule et 3 à droite) doivent être >0 et <= 200D.
- Les quantités en stock sont des nombres de taille 3 et ne peuvent accepter que des valeurs positives.
Ces quantités peuvent être en rupture de stock.
Pour la table Ordonnance, la date prend par défaut la date système.
Pour la table Vente, la quantité vendue doit être strictement positif.
Il est à noter aussi que tous les attributs de type chaîne de caractères sont de taille 15.
Q1 : Créer la table Patient tout en considérant toutes les contraintes nécessaires.
Q2 : Créer la table Médecin sans aucune contrainte.
Q3 : Ajouter, en une seule instruction, toutes les contraintes nécessaires pour la table Médecin.
Q4 : Créer la table Médicament tout en considérant toutes les contraintes nécessaires.
Q5 : Créer la table Ordonnance tout en considérant toutes les contraintes nécessaires.
Q6 : Créer la table Vente tout en considérant toutes les contraintes nécessaires.
Q7 : Remplacer l’attribut AdrPat par VilPat dans la table Patient.
Q8 : Insérer le médecin Ahmed Tounsi Numéro 1234 avec la spécialité Dentiste.
Q9 : Insérer le patient Mahmoud Sfaxi Numéro 5678.
Q10 : La ville du patient n° 5678 est Gabes.
Q11 : Insérer le médicament Pivalone Numéro 1212, avec un prix de 15D et une quantité de 50.
Q12 : Insérer l’ordonnance numéro 12566 effectuée le 09/11/2013 par le médecin Ahmed Tounsi pour
le patient Mahmoud Sfaxi.
Q13 : Suite à l’ordonnance 12566, nous avons effectué la vente suivante : 17 unités du médicament n°
1212.
Q14 : Supprimer l’ordonnance 12566.