Ateliers BD
Ateliers BD
Ateliers BD
Atelier
Oracle:
SQL
O bjectifs
Savoir créer des données avec le langage SQL (table).
Comprendre l'impact des types de données.
Savoir mettre à jour les données d'une base Oracle (insert, update, delete) en SQL.
Savoir extraire les données d'une base ORACLE avec le SQL (select, where, like, ...).
Savoir écrire des requêtes SQL complexes (jointure, tri, ...).
Comprendre le principe de transaction (commit, rollback).
Pré-requis
Connaissance des concepts base de données, modèle relationnel et algèbre relationnelle.
Soit une table des employés d'une entreprise avec la structure suivante:
Table : Employé
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
Requêtes de sélection
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
Remarque: L’opérateur * permet d'afficher tous les champs définis dans la table.
Les critères de sélection constituent une expression logique; qui peut prendre la valeur 'Vrai' ou
'Faux'. Les critères de sélection sont appliqués à chaque enregistrement d'une table. Lorsque pour
un enregistrement donné, l'expression logique prend la valeur 'Vrai', cet enregistrement :
Comparaison à une valeur donnée.
Pour chaque enregistrement, la valeur d'un champ donné est comparée à une valeur fixe. Cette
valeur fixe est généralement une valeur numérique, une date ou un texte.
Voici les opérateurs de comparaison: =, >, <, >=, <=, <>
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
13. Afficher le prénom, le nom et l'âge de tous les employés plus jeunes que 50 ans.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
Comparaison à un filtre
Parfois, on ne connaît pas la valeur exacte à laquelle on veut comparer la valeur d'un champ.
Dans ce cas on peut utiliser un filtre. Un filtre est une expression qui peut contenir des lettres, des
chiffres et en plus les 2 caractères spéciaux suivants:
% représente n'importe quelle séquence de 0 ou plusieurs caractères;
_ représente un seul caractère quelconque.
Exemple: Pour rechercher des personnes dont le nom est 'Mohamed' ou 'Mahmoud' ou
'Mahamadou' etc. on définit par exemple le filtre suivant : ‘Mah%'
Exemple: Le filtre 'BL__' sélectionne par exemple les valeurs 'BLEU' ou 'BLUE' mais pas
'BLANC'
Les filtres sont utilisés ensemble avec le mot réservé LIKE. Voici la syntaxe:
...
WHERE <Nom du champ> LIKE <Filtre>
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
15. Afficher le nom, le prénom et l'âge des employés dont le nom commence par 'W', est composé
de 5 lettres et se termine par 'R'.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
16. Afficher le prénom et le nom de tous les employés qui ne travaillent pas dans le service
"Personnel", en utilisant l’operateur NOT.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
Formulez une requête qui affiche exactement le même résultat, sans utiliser l'opérateur NOT.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
17. Afficher le numéro d'employé, le prénom et le nom de tous les employés dont le nom ne
commence pas par la lettre 'T'.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
18. Afficher le numéro de l'employé, le prénom et le nom pour les employés du service
Informatique qui ont moins de 30 ans.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
19. Afficher le prénom et nom des employés féminins (code=F) qui ne travaillent pas au service
personnel.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
20. Afficher tous les champs pour les employés de nationalité tunisienne (Code=TUN) ou
algérienne (Code=ALG).
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
Généralement, chaque champ défini dans une table possède une valeur bien définie. Il existe
pourtant des situations dans lesquelles cette valeur est inconnue ou n'existe pas temporairement.
Les SGBD nous offrent en général 3 valeurs pour ces types de situations:
Le nombre 0 ;
La chaîne de caractères vide ('') ;
La valeur prédéfinie NULL (Valeur indéterminée, Champ vide).
22. Vous devez ajouter un nouvel employé dans la BD. Voici les informations dont vous disposez:
Sachant que A. Bouallem n'est pas du tout affecté à un service spécifique, puisqu'il est le réviseur
interne de l'entreprise, formulez la requête d'insertion.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
L'opérateur BETWEEN ... AND ... permet de déterminer si la valeur d'un champ donné
appartient à un intervalle bien défini. L'intervalle est généralement un intervalle numérique ou un
intervalle du type Date.
24. Afficher le numéro d'employé, le nom et l'âge des employés âgés entre 30 et 50 ans.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
Formulez une requête qui affiche exactement le même résultat, sans utiliser l'opérateur BETWEEN
... AND ....
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
25. Afficher tous les champs pour les employés masculins âgés entre 20 et 30 ans et les employés
féminins âgés entre 40 et 50 ans.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
28. Afficher tous les champs pour les employés masculins, âgés d'au moins 45 ans qui ne sont pas
de nationalité Tunisienne (Code='TUN'), Algérienne (Code='ALG'), ou italienne (Code='ITA').
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
Dans une requête on a la possibilité de définir des champs à valeur calculée. Un tel champ ne
fait pas partie d'une table, mais contient une valeur, qui est calculée sur base d'un ou de plusieurs
champs existants.
29. On veut retirer une commission de 20% du salaire pour chaque employé. Afficher le prénom et
le nom de tous les employés ainsi que sa commission.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
L'ordre obtenu dans la réponse d'une requête de sélection a été laissé jusqu'à maintenant au
pur hasard.
L'expression ORDER BY nous permet de définir convenablement l'ordre d'apparition des
enregistrements qui vérifient les critères de sélection de la requête. Voici la syntaxe:
SELECT <Nom des champs>
FROM <Nom de la table>
WHERE <Critères de sélection>
ORDER BY <Nom d'un champ>[ASC/DESC], <Nom d'un champ>[ASC/DESC], … ;
Par défaut l'ordre de tri est ascendant (ASC), donc vous n'avez pas nécessairement besoin
d'indiquer le mot ASC. Cependant, lorsque vous voulez trier les enregistrements en ordre
descendant, le mot DESC est indispensable.
30. Afficher les employés masculins. Triez la liste en ordre descendant sur le salaire.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
31. Afficher les employés. Triez la liste en ordre croissant sur la nationalité et en ordre descendant
sur le salaire.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
La clause GROUP BY
La clause GROUP BY <Liste des champs de groupe> intervient sur le résultat d'un SELECT. En fait,
les enregistrements résultant d'une requête de sélection sont groupés, de façon qu'à l'intérieur de
chaque groupe, les valeurs pour la liste des champs de groupe soient identiques.
Généralement, on applique une fonction d'agrégation à un ou plusieurs champs, ne faisant pas
partie de la liste des champs de groupe.
Attention: La clause SELECT peut uniquement contenir des champs faisant partie de la liste des
champs de groupe ou des fonctions d'agrégation appliquées à un des autres champs.
La requête de sélection peut bien sûr contenir des critères de sélection (WHERE ...), qui éliminent
un certain nombre d'enregistrements déjà avant la création des groupes.
On a la possibilité d'appliquer la clause ORDER BY au résultat d'un GROUP BY
Syntaxe:
SELECT <Liste des champs de groupe>,[COUNT/SUM...]
FROM <Nom de la table>
WHERE <Critères de sélection>
GROUP BY <Liste de champs de groupe>
ORDER BY <Nom d'un champ>[ASC/DESC], <Nom d'un champ>[ASC/DESC], …;
37. Classez les employés par ordre descendant des services et affichez pour chaque service la
moyenne du salaire.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
39. Affichez pour chaque service, le nombre des employés, ainsi que le salaire total.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
La clause HAVING
La clause HAVING
La clause HAVING <Critères de sélection des groupes> est uniquement spécifiée en relation avec
un GROUP BY. Une fois les groupes créés, cette clause en élimine certains, basés sur les critères de
sélection des groupes. Les critères de sélection des groupes portent bien entendu sur la valeur
d'une ou de plusieurs des fonctions d'agrégation calculées pour chaque groupe.
Syntaxe:
SELECT <Liste des champs de groupe>,[COUNT/SUM...]
FROM <Nom de la table>
WHERE <Critères de sélection>
GROUP BY <Liste de champs de groupe>
HAVING <Critères de sélection des groupes>
ORDER BY <Nom d'un champ>[ASC/DESC], <Nom d'un champ>[ASC/DESC], …;
40. Affichez pour chaque nationalité, le nombre d’employés, ainsi que le salaire total, en tenant
uniquement compte des nationalités pour lesquelles le nombre d’employés est supérieur à 1.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
41. Même question, mais en éliminant dès le début les employés Algériens.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
D e v o i r à l a ma i s o n
Matière : Atelier Système d’Information I Durée : Trois heures
Enseignant : Mohamed Fadhel SAAD Classe : 2ième Année LA
15. Afficher une liste qui contient toutes les langues dont il existe au moins un roman plus cher
que 1300.
16. Affichez le prix moyen des romans anglais.
Indiquez la requête correcte pour ajouter ce client lorsque vous ignorez le numéro de fax.
Indiquez la requête correcte pour ajouter ce client lorsque vous savez que le client ne possède pas
de fax.
3. Affichez la liste de toutes les localités présentes dans la table des clients.
4. Tous les clients habitant à Gafsa, dans la Cité des jeunes, auront le nouveau code postal 2112.
5. Nous voulons "nettoyer" notre BD. En fait, il y a un certain nombre de clients dont l'adresse, le
code postal, la localité le numéro de téléphone et le numéro de fax sont indéterminés. Ces
enregistrements sont sans aucune valeur commerciale pour nous. Formulez une requête qui garde
uniquement les clients pour lesquels on connaît:
soit le numéro de téléphone;
soit le numéro de fax;
soit l'adresse complète (Adresse, CP, Localité).
Tous les autres clients sont effacés de la BD.
6. Comptez le nombre de clients masculins nés après le 1/1/1978.
7. Afficher le numéro client, le nom, le prénom, l'adresse, le code postal et la localité pour les
bons clients féminins, à l'exception de ceux habitant à Sousse, Sfax et Tunis.
8. Affichez par sexe, le nombre de clients nés après le 31/12/1969. Le champ qui affiche le
Nous allons différencier 2 méthodes pour lier plusieurs tables dans une requête:
1. La jointure, qui lie plusieurs tables via des champs communs;
2. Les requêtes imbriquées, qui utilisent le résultat d'une requête comme source d'une autre.
Voici trois tables qui représentent une gestion (très simplifiée) des comptes d'une banque:
Clients(Idf_client, Nom, Prenom, Adresse, localite)
Comptes(Idf_compte, Idf_agence#, Valeur, Idf_client #)
Agences(Idf_agence, Adresse, CP, Localité)
Comptes
Idf_compte Idf_agence Valeur Idf_Client
101 12 20000 3
106 24 48000 2
112 12 9000 3
125 30 5000 1
Clients
Idf_client Nom Prénom Adresse Localité
1 Ktarri Amir 50 AVENUE DE PARIS Gafsa
2 Alimi Taher 18 RUE HOUSSIN BOUZAYANNE Sfax
3 Mohamed Sarra 57 AVENUE DE LIBERTE Tunis
Agences
Idf_agence Adresse CP Localité
12 12 RUE MILOUD 2100 GAFSA
24 31 AVENUE DE FRANCE 9000 BEJA
30 10 AV.SALEM HARZALLAH ,BAB BHAR 3047 SFAX
En principe, la présence d'une relation (clé étrangère/clé primaire) entre deux tables est une
condition nécessaire pour effectuer une jointure sur les tables.
42. Affichez pour tous les comptes, le numéro de compte, la valeur actuelle, ainsi que le nom du
client correspondant.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
44. Affichez pour tous les comptes, le numéro de compte, la valeur actuelle, le nom du client ainsi
que sa localité et la localité de l'agence. Renommez les en-têtes de façon à ce qu'il n'y ait pas de
confusion entre les données du client et celles de l'agence.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
45. Affichez le numéro de compte, le nom et prénom du client ainsi que le numéro d'agence pour
les comptes dont l'agence se trouve dans la même localité ou habite le client correspondant.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
48. Affichez les numéros des comptes et la valeur actuelle pour les comptes dont la valeur est
supérieure à la moyenne des valeurs.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
49. Affichez le nom, le prénom, l'adresse, le code postal et la localité du client, qui possède le
compte avec la plus petite valeur. Nous supposons qu'il existe uniquement un seul compte avec la
plus petite valeur.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
50. Pour effectuer des statistiques, on vous demande la requête suivante. Affichez le numéro de
compte et la valeur actuelle pour les comptes dont la valeur est plus petite que la moyenne des
valeurs pour les comptes dont les clients habitent au Gafsa, mais plus grande que la moyenne des
valeurs pour les comptes dont les clients habitent à Beja ou Sfax.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
51. Affichez les numéros des comptes qui sont gérés par une agence située à Gafsa.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
___________
52. Affichez le nom et le prénom de tous les clients ayant un compte géré par une agence située à
Gafsa ou à Beja.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
53. Affichez le nom et le prénom de tous les clients n'ayant pas de compte.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
54. Affichez le nom et le prénom des clients ayant au moins un compte avec une valeur de 9000.
.
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
D e v o i r à l a ma i s o n
Matière : Atelier Système d’Information I Durée : Trois heures
Enseignant : Mohamed Fadhel SAAD Classe : 2ième Année LA
Exercice 1:
Soit le schéma de la base de données CINEMA :
FILM (NumF, Titre, Date, Longueur, Budget, Réalisateur, SalaireR)
GENERIQUE (Film, Acteur, Rôle, Salaire)
PERSONNE (NumP, PrénomP, NomP, Date_Naiss, Sexe, Nationalité, Adresse, TéléphoneP)
ACTEUR (NumA, Agent, Spécialité, Taille, Poids)
CINEMA (NumC, Nom, Adresse, TéléphoneC, Compagnie)
PASSE (NumF, Cinéma, Salle, Date_Deb, Date_Fin, Horaire, Prix)
SALLE (NumS, Cinéma, Taille_Ecran, Places)
RECOMPENSE (NumR, Catégorie, Festival)
RECOMPENSE_FILM (Film, Récompense, Année)
RECOMPENSE_ACTEUR (Acteur, Récompense, Année)
Tout nom de relation utilisé comme attribut est une clé étrangère qui renvoie à l’identifiant (clé
primaire) de la relation correspondante. Par exemple, dans GENERIQUE, Film correspond à NumF
de FILM et est défini sur le même domaine. Réalisateur dans FILM et NumA dans ACTEUR sont
définis sur le domaine des NumP.
Exprimez les requêtes ci-dessous en SQL :
1. Quelle est le poids moyen des acteurs
2. Dans quel cinéma, on a fait passer le plus de films
3. Pour chaque film réalisé par Roman Polanski, trouver le nom et le prénom de l’acteur qui a eu
le plus gros salaire
4. Quels sont les acteurs qui se sont vus discernés plus de 4 récompenses
5. Quels sont les acteurs qui ont joué dans au moins tous les films où l’acteur Jean Paul Belmondo
a joué
Exercice 2:
Soit la base de données, décrite par les relations suivantes :