REVISIONS
Manipulation des données et langage de définition de données
Travaux préliminaires :
Le travail est à réaliser sous Oracle ou MySql.
- Démarrer Oracle. Auparavant démarrer le service si nécessaire.
- Se connecter avec le nom d’utilisateur scott et le mot de passe tiger
Ou
- Démarrer PhpMyAdmin (version MySql)
Remarque importante :
Le texte de chaque requête doit être placé dans un fichier (ex : fichier R1.sql pour la
requête 1). Utiliser un répertoire pour chacune des deux parties de ce travail.
Ecrire et exécuter les requêtes ci-dessous :
1) Obtenir les départements (numéro, nom) ayant des employés.
2) Donner la liste des employés (numéro, nom) non vendeurs.
3) Donner les employés embauchés entre le 01-01-1981 et le 30-06-81.
4) Donner le salaire le plus élevé et le salaire le plus bas par département et job.
5) Donner pour chaque département : son nom, le nombre d’employés et la moyenne des salaires.
6) Donner le montant total des commissions des vendeurs.
7) Donner pour chaque employé : son nom, sa fonction et le nom de son département. Le résultat devra être ordonné sur le nom de l'employé.
8) Donner le nom et le job de la dernière personne embauchée dans le département ‘ACCOUNTING’.
9) Lister les employés (no, nom) dont le salaire est supérieur à la moyenne des salaires.
10) Donner le nombre d’employés n’ayant pas de responsable.
11) Donner la liste des employés (numéro, nom et job) avec le nom de leur responsable.
12) On peut constater que le chef ne figure pas dans la liste de la question précédente (c'est logique car il n'a pas de responsable), il est demandé de l'ajouter dans cette liste (penser à UNION).
13) Donner la liste des employés dont le nom commence par A.
14) Donner la liste des employés (numéro, nom) avec leur salaire total ordonnée sur le nom de l'employé ; attention si on ajoute NULL à un nombre, le résultat vaut NULL.
15) Augmenter les salaires des vendeurs de 1%.
16) Insérer un département (la seule contrainte est que le numéro doit être égal au dernier + 10).
Le café "Au père tranquille" veut disposer d’une base de données relationnelle sous Oracle.
Le modèle "physique" des données (MPD) sous AMC Designor se présente ainsi :
_fichiers/image001.gif) |
Le contenu des différentes tables figure en annexe 2.
TRAVAIL A REALISER
Création des tables :
Ecrire les requêtes de création des tables.
Attention à l'ordre pour l'exécution.
Penser aux contraintes de clés primaires et étrangères et à la contrainte NOT NULL (seules l'adresse et la date de naissance du serveur peuvent être à NULL). Le prix de la consommation doit être compris entre 1 et 10 euros. Si les factures sont supprimées, on souhaite que les lignes de facture soient également supprimées.
Respecter impérativement les noms des attributs.
Le code postal est de type CHAR(5).
Les chaînes sont à déclarer avec le type VARCHAR(longueur maximale).
Alimentation des tables :
Pour remplir les tables : il faut copier le fichier insercafe.sql (situé echange/phl/IG2/ALSI-BD/BD/Atelier1 (Révisions)) en local, l’ouvrir et l'exécuter.
Ecrire et exécuter les requêtes ci-dessous :
1) Ajouter un attribut commentaire, d'une longueur de 80, dans la table des serveurs.
2) Ajouter la contrainte "quantité facturée obligatoirement positive" dans la table des lignes de facture.
3) Dans la table des serveurs, faire passer la longueur de la ville à 35 caractères.
4) Interdire la création de doublons sur les noms des serveurs.
5) Obtenir la liste des factures avec pour chaque facture le numéro de la facture, le nom de la table et la date de la facture.
6) Obtenir les noms des consommations servies le 22/02/1997.
7) Connaître le nombre de serveurs.
8) Ajouter le serveur n°54, il s'appelle Lulu, habite 35, rue des Lilas 69000 Lyon et est né le 20/06/76.
9) Obtenir les numéros et nombre de places des tables qui n'ont pas été occupées.
10) Connaître le nom et le prix de la consommation la moins chère.
11) Obtenir le numéro, la date et le montant total de chaque facture.
12) Augmenter de 10% le prix du café, café double et café crème.
13) Connaître les noms des serveurs ayant effectué plus de 25 euros de chiffre d'affaires.
14) Dans la facture 1207, la consommation 108 a été saisie par erreur, faire le nécessaire.
15) Connaître le nom et l'âge de chaque serveur. Pour extraire l'année, il faudra utiliser la fonction YEAR.
Table dept (table des départements)
deptno numeric(2) clé primaire
dname varchar(30)
loc varchar(20)
Tous les champs sont obligatoires.
Exemple :
10 ACCOUNTING NEW YORK
Table emp (table des employés)
empno numeric(4) clé primaire
ename varchar(30)
job varchar(30)
mgr numeric(4)
hiredate date
sal numeric (8,2)
comm numeric(8,2)
deptno numeric(2) clé étrangère
mgr désigne le numéro du responsable.
sal est le salaire.
comm est la commission (uniquement renseignée pour les vendeurs).
Le lien entre les 2 tables se fait par deptno.
Tous les champs sont obligatoires sauf le numéro de responsable (mgr) et la commission (comm).
Exemple :
7369 SMITH CLERK 7902 17/12/80 800 20
VOICI LE CONTENU (FICTIF) DES DIFFERENTES TABLES :
Table des consommations : CONSO Clé : NumCons
|
NumCons |
LibCons |
PrixCons |
|
100 |
Café |
1.20 € |
|
101 |
Café double |
2.00 € |
|
102 |
Café crème |
1.50 € |
|
105 |
Chocolat |
2.00 € |
|
106 |
Bière pression |
2.50 € |
|
107 |
Bière 25cl |
3.00 € |
|
108 |
Bière 33cl |
4.00 € |
|
110 |
Bière 50cl |
5.00 € |
|
120 |
Jus de fruits |
2.50 € |
|
121 |
Jus de fruit pressé |
3.00 € |
|
122 |
Perrier |
2.50 e |
|
124 |
Orangina |
2.40 € |
|
130 |
Coca Cola |
2.40 € |
Table des factures : FACTURE Clé : NumFact
|
NumFact |
NumTable |
NumServ |
DateFact |
|
1200 |
1 |
53 |
21/02/2006 |
|
1201 |
5 |
53 |
21/02/2006 |
|
1202 |
3 |
52 |
21/02/2006 |
|
1203 |
5 |
50 |
21/02/2006 |
|
1204 |
4 |
52 |
22/02/2006 |
|
1205 |
1 |
53 |
22/02/2006 |
|
1206 |
3 |
52 |
22/02/2006 |
|
1207 |
5 |
53 |
22/02/2006 |
Table des tables du café : TABLES Clé : NumTable
|
NumTable |
NomTable |
NbPlaces |
|
1 |
entree-gche |
6 |
|
2 |
entree-dte |
10 |
|
3 |
fenetre1 |
3 |
|
4 |
fenetre2 |
8 |
|
5 |
fenetre3 |
4 |
|
6 |
fond-gche |
4 |
|
7 |
fond-drt |
2 |
Table des lignes factures : LIGNE_FACT Clé : NumFact + NumCons
|
NumFact |
NumCons |
Qte |
|
1200 |
101 |
3 |
|
1200 |
106 |
1 |
|
1200 |
120 |
1 |
|
1201 |
105 |
2 |
|
1201 |
106 |
2 |
|
1202 |
100 |
2 |
|
1202 |
122 |
1 |
|
1203 |
102 |
1 |
|
1203 |
108 |
1 |
|
1203 |
121 |
1 |
|
1203 |
130 |
1 |
|
1204 |
122 |
4 |
|
1204 |
124 |
2 |
|
1205 |
100 |
2 |
|
1206 |
108 |
3 |
|
1207 |
108 |
1 |
|
1207 |
110 |
2 |
Table des serveurs : SERVEUR Clé : NumServ
|
NumServ |
NomServ |
RueServ |
CpServ |
VilleServ |
DateNaisss |
|
50 |
Pizzi |
3, rue des lilas |
90000 |
BELFORT |
10/12/1986 |
|
51 |
Cathy |
25, av Roosevelt |
90100 |
DELLE |
05/06/1985 |
|
52 |
Totof |
46, grande rue |
90500 |
BAVILLIERS |
03/09/1986 |
|
53 |
Pilou |
5, impasse Martin |
90000 |
BELFORT |
09/08/1986 |