InfoDistribribution Select Corrige 2021
InfoDistribribution Select Corrige 2021
InfoDistribribution Select Corrige 2021
BASES DE DONNEES
ORACLE : SQL – PL/SQL
On se propose d’automatiser la gestion des ventes d’une société de distribution de matériel informatique.
Pour cela nous avons conçu une base de données dont le Modèle Conceptuel des Données (MCD) (ou
Modèle Entity Association(EA)) est :
1,1 Subalterne
EstSuperieurDe
Employe
NumEmploye
0,n Superieur
Nom
Prenom
Fonction
EstRattache
DateEmbauche Departement
1,1
Salaire 1,n NumDept
ComplementSalaire NomDept
NatureComplementSalaire
Gerer >
DateNaissance 0,n
Genre
SitFam 1,1
NbEnfants
Telephone
Email Commande
NumCommande 1,n
DateCommande Contenir >
Client MontantRegle QteCommandee
CodeClient EtatCommande PrixVente
NomClient DateLivraison TauxRemise
Adresse
Ville 1,1 0,n
CodePostal 1,n
Pays Passer >
Telephone
Email
SiteWeb
Produit
Reference
Designation
Depot 0,n Stocker 1,n PrixAchat
CodeDepot MargeMin
QteEnStock
AdresseDepot MargeMax
SeuilStock
VilleDepot
1,1 1,1
Fournisseur
CodeFournisseur 1,n
RaisonSociale Fournir >
Contact
Adresse
Ville
CodePostal
Pays Categorie
1,n
Telephone CodeCategorie Appartenir
Email LibelleCategorie
SiteWeb Description
Illustration
1
Oracle SQL – PL/SQL
2
Oracle SQL – PL/SQL
Requêtes sélection
=========== Extraction d’un ensemble de colonnes d’une table ===========
SELECT *
FROM depot;
3-Extraire les informations NumEmploye, Nom et prenom des employés dont la fonction est « ingénieur »
4-Extraire les informations NumEmploye, Nom, Prenom et salaire des employes dont le salaire est supérieur à 25000.
5-Extraire les informations NumEmploye, Nom, Prenom et salaire des employes dont le salaire n’est pas inférieur à 50000.
6-Extraire les informations NumEmploye, Nom, Prenom et salaire des employes dont le salaire se trouve dans l’intervalle [15000, 25000].
7- Extraire les informations NumEmploye, Nom, Prenom et salaire des employes dont le salaire se trouve en dehors de l’intervalle
[15000, 25000]
3
Oracle SQL – PL/SQL
8- Extraire les informations NumEmploye, Nom, Prenom et salaire des employes dont le salaire est strictement supérieur à 15000 et
strictement inférieur à 25000.
9- Extraire les informations NumEmploye, Nom, Prenom et salaire des employes dont le salaire est égal à 15000 ou à 25000.
10- Extraire les informations NumEmploye, Nom, Prenom et NumDept des employes dont NumDept appartient à la liste (10, 30, 50).
11- Extraire les informations NumEmploye, Nom, Prenom et NumDept des employes dont NumDept n'appartient pas à la liste (10, 30, 50).
12- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le prénom commence par ‘Mo’.
13- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le prénom se termine par ‘id'.
14- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le prénom contient la sous chaîne ‘ou’
4
Oracle SQL – PL/SQL
SELECT NumEmploye, Nom, Prenom, Salaire
FROM Employe
WHERE Prenom LIKE '%ou%';
15- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le prénom est formé de six caractères et dont les cinq
derniers forment la chaîne ‘atine’.
16- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le prénom commence par la lettre F ou T et se termine
par la lettre d ou k.
17- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le prénom commence par la lettre comprise entre L et
T.
18- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le prénom ne commence pas par une lettre comprise
entre L et T.
19- Afficher les informations NumEmploye, Nom, Prenom et salaire dont le prénom contient le caractère joker « _».
21- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes du Departement N°20 et ayant un salaire supérieur 15000.
22- Extraire les informations NumEmploye, Nom, Prenom, Salaire, NumDept et Fonction des employés qui font partie du département N°20, ou
qui sont de fonction Ingénieur.
23- Extraire les informations NumEmploye, Nom, Prenom, Salaire, NumDept et Fonction des employes qui font partie du département N°20 et
dont le salaire est supérieur à 15000 ou qui sont de fonction Ingenieur.
24- Extraire les informations NumEmploye, Nom, Prenom, Salaire, NumDept et Fonction des employes dont le salaire est supérieur à 15000 et
qui soit font partie du département N°20, soit sont de fonction Ingenieur.
SELECT Fonction
FROM Employe;
6
Oracle SQL – PL/SQL
========== Tri des lignes à l'aide de ORDER BY=============
27- Extraire les informations NumDept, Fonction, NumEmploye, Nom, Prenom, et Salaire des employes.
28- Extraire les informations NumDept, Fonction, NumEmploye, Nom, Prenom, et Salaire des employes en classant les lignes par ordre croisant
de Numdept
29- Extraire les informations NumDept, Fonction, NumEmploye, Nom, Prenom, et Salaire des employes en classant les lignes par ordre croisant
de Numdept, puis par ordre décroissant des fonctions.
30- Extraire les informations NumDept, Fonction, NumEmploye, Nom, Prenom, et Salaire des employes en classant les lignes par ordre croisant
de Numdept, puis par ordre décroissant des fonctions, et en fin par ordre croissant des noms.
===== Attribution de nom aux colonnes du jeu de résultats : Utilisez la clause AS =====
31- Extraire les informations Numemploye et Nom en attribuant aux colonnes NumEmploye et Nom du jeu des résultats respectivement l'intitulé
"Numéro de l'employé" , et "Nom de l'employé"
32- Extraire Pour chaque employé son Numéro et la somme de son salaire et de son ComplementSalaire s'il existe.
33- Extraire pour chaque employé son numéro et son année d’embauche.
34- Extraire pour chaque employé son nom et prénom affichés en une même colonne
36- Calculer le montant de ventes pour chaque produit vendu. Montant=PrixVente*(1-Remise)* QteCommandee (Table DetailsCommande).
37- Extraire pour chaque employé son numéro, son nom, son prénom et son ancienneté.
38- Extraire pour chaque employé son numéro, son nom, son prénom et son âge d'embauche.
39- Extraire pour chaque employé toutes ses informations ainsi que celles du département dont il fait partie.
40- Extraire pour chaque employé les informations NumEmploye, Nom , Prenom et NumDept ainsi que le nom de son département.
======== Jointures utilisant des opérateurs autres que « égal » (=) =========
41- Extraire les informations NumClient, Nom, Ville pour lesquels l’ordre alphabétique de la ville vient après celui de la ville ou se trouve le
fournisseur ‘SOS INFO’
42-Extraire les informations Reference, Designation, PrixAchat, et PrixVente sur chaque produit qu’il soit vendu on non.
43- Extraire les informations NumCommande, DateCommande, NumClient, NomClient pour chaque client qu'il ait passé ou nom de commandes
44- Extraire pour chaque employe son numero, nom prenom ainsi que le numéro, nom et prénom de son supérieur.
45- Afficher pour chaque dépôt la liste des produits qui y sont stockés. Les informations à afficher sur les produits sont : CodeDepot,
AdresseDepot, Reference, Designation, et QteEnStock.
46- Afficher pour chaque employé son numéro, nom et prénom ainsi que les codes et les noms des clients avec lesquels il a traité.
47- Afficher pour chaque client son numéro, son nom et la liste des produits qu’ils ont commandés en chaque commande. Les informations à
afficher sur les produits sont CodeProduit, Designation, QteCommandee et PrixVente.
48- Afficher pour chaque client son numéro, son nom, et les noms des catégories des produits qu’ils ont commandés
9
Oracle SQL – PL/SQL
49- calculer la somme des salaires de tous les employés de la table Employe.
51- Calculer le salaire moyen et la somme des salaires de tous les employés
===Utilisation de COUNT(*)====
58- Calculer le salaire moyen par fonction des employés qui ne sont pas du Département Numéro 30 (Direction)
SELECT Fonction, ROUND(AVG(Salaire),2) SalaireMoyen
FROM Employe
WHERE NumDept <>30 --Le filtre est effectué avant le regroupement
GROUP BY Fonction;
10
Oracle SQL – PL/SQL
===Sélection de lignes à l'aide de la clause HAVING===
59- Quels sont les fonctions dont le salaire moyen des employés n’appartenant pas au département 30, dépassent 20000.
SELECT Fonction, ROUND(AVG(Salaire),2) SalaireMoyen
FROM Employe
WHERE NumDept <>30 --Le filtre est effectué avant le regroupement
GROUP BY Fonction
HAVING AVG(Salaire)>20000; --Le filtre est effectué après le regroupement
60- Calculer le salaire moyen des employés de chaque département ayant au moins 5 employés.
60Bis-Calculer la masse salariale maximale des départements ainsi que la masse salariale globale de tous les employés.
62- Afficher les noms des employés, clients et fournisseurs. Spécifier pour chaque nom son origine
/*
La requête ci-dessous ne s'exécute car les champs numEmploye
et CodeFornisseur ne sont pas de ^type compatible
SELECT Ville
FROM Client
INTERSECT
SELECT Ville
FROM Fournisseur;
65- Afficher Les villes où on trouve des clients mais pas de Fournisseurs
SELECT Ville
FROM Client
MINUS
SELECT Ville
FROM Fournisseur;
66- Afficher pour chaque commande les informations NumCommande, DateComande et le plus grand prix de vente des produits qui en font
partie.
--Avec Sous-Requête
SELECT NumCommande, DateCommande,
(SELECT Max(PrixVente)FROM DetailCommande DC
WHERE Cmd.NumCommande = DC.NumCommande) AS PrixVenteMax
FROM Commande Cmd;
--Avec Jointure
SELECT Cmd.NumCommande, DateCommande, Max(PrixVente) AS PrixVenteMax
FROM Commande Cmd INNER JOIN DetailCommande DC
ON Cmd.NumCommande = DC.NumCommande
GROUP BY Cmd.NumCommande, DateCommande
ORDER BY Cmd.NumCommande;
67- Afficher la désignation des produits dont le prix d’achat est égal au prix d’achat du produit dont la désignation est « Imprimante LexMark
500 »
===Types de sous-requête===
12
Oracle SQL – PL/SQL
===Sous-requêtes et alias====
68- Afficher les informations NumEmploye, Nom, et DateEmbauche des employés qui ont été embauché la même année que l’employé de
numéro 16712.
69-Afficher la designation des produits qui sont stockés dans le dépôt ‘DEP001’
SELECT Designation
FROM Produit
WHERE Reference IN
(SELECT Reference
FROM Stock
WHERE CodeDepot = 'DEP001');
70-- Afficher la designation des produits qui ne sont pas stockés dans le dépôt ‘DEP001’
SELECT Designation
FROM Produit
WHERE Reference NOT IN
(SELECT Reference
FROM Stock
WHERE CodeDepot = 'DEP001');
71-Afficher pour Chaque employe son Numero et nom ainsi que le salaire moyen de tous les employés
SELECT NumEmploye, Nom, Salaire,
(SELECT AVG(Salaire)
FROM Employe) AS SalaireMoyen
FROM Employe;
72-Afficher pour Chaque employe sa reference, son nom , Salaire, NumDept ainsi que le salaire moyen des employés de son département.
71-72Combinés
SELECT NumEmploye, Nom, Salaire, NumDept,Round((SELECT AVG(Salaire)
FROM Employe E2
WHERE E1.NumDept = E2.NumDept),2)SalaireMoyenDept,
ROUND((SELECT AVG(Salaire)
FROM Employe),2) SalaireMoyenGlobal
FROM Employe E1;
13
Oracle SQL – PL/SQL
==Sous-requêtes dans les instructions UPDATE, DELETE ==
73- Modifier les prix d’achat des produits fournis par des fournisseur Marocain en les réduisant de 20%.
UPDATE Produit
SET PrixAchat = PrixAchat -PrixAchat*20/100
WHERE Reference IN (SELECT Reference
FROM Fournisseur
WHERE UPPER(Pays) = 'MAROC');
ROLLBACK;
75- Afficher les informations NumClient, Nom et ville des clients qui se trouvent dans la même ville que le fournisseur ‘SOS INFO’.
76- Afficher les informations NumEmploye, Nom, Prenom, Salaire, et NumDept des employés dont le salaire est inférieur au salaire de tous les
employés du département N°20.
Ou
14
Oracle SQL – PL/SQL
77- Afficher les informations NumEmploye, Nom, Prenom, Salaire, et NumDept des employés dont le salaire est supérieur au salaire d’au moins
un employé du département N°20.
78- Afficher les informations CodeDepot et Ville des dépôts qui se trouvent dans une ville où il ya au moins un fournisseur.
79- Afficher les informations CodeDepot et Ville des dépôts qui se trouvent dans une ville où il n'y a aucun fournisseur.
--ou
15
Oracle SQL – PL/SQL
FROM Employe
WHERE Salaire > (SELECT MAX(Salaire)
FROM Employe
WHERE NumDept=20);
81- Extraire les informations NumEmploye, Nom, Prenom et Salaire des employes dont le salaire est supérieur au salaire d’au moins
un employé du Departement Numéro 10 .
SELECT NumEmploye, Nom, Prenom, Salaire
FROM Employe
WHERE Salaire > ANY(SELECT Salaire
FROM Employe
WHERE NumDept=10);
--ou
SELECT NumEmploye, Nom, Prenom, Salaire
FROM Employe
WHERE Salaire > (SELECT MIN(Salaire)
FROM Employe
WHERE NumDept=10);
82- Afficher les noms des clients qui ont passé des commandes auprès de l’employé dont le numéro est 16034.
SELECT Nom
FROM Client
WHERE CodeClient IN(SELECT CodeClient
FROM Commande
WHERE NumEmploye = 16034);
84- Afficher la désignation des produits fourni par des fournisseurs qui se trouvent dans une ville qui commence par la lettre F.
85- Afficher les noms des fournisseurs qui n’ont fourni aucun produit de la catégorie ORD001.
SELECT RaisonSociale
FROM Fournisseur F
WHERE NOT EXISTS(SELECT Reference
FROM Produit P
WHERE F.CodeFournisseur = P.CodeFournisseur AND P.CodeCategorie='ORD001');
86- Afficher la désignation des produits qui n’ont pas été vendus. (La reference du produit n'existe pas dans la table DetailCommande)
SELECT Designation
FROM Produit P
16
Oracle SQL – PL/SQL
WHERE NOT EXISTS (SELECT *
FROM DetailCommande DC
WHERE P.Reference = DC.Reference);
===Utilisation de EXISTS et NOT EXISTS pour la recherche des intersections et des différences===
87- Afficher les villes où sont implantés des clients et des fournisseurs
SELECT Ville
FROM Client C
WHERE EXISTS (SELECT *
FROM Fournisseur F
WHERE C.Ville = F.Ville);
SELECT Ville
FROM Client C
WHERE NOT EXISTS (SELECT *
FROM Fournisseur F
WHERE C.Ville = F.Ville);
89- Afficher pour chaque produit les informations Désignation, PrixAchat, Prix achat moyen, et l’écart entre le prix achat et le prix achat moyen .
SELECT Designation, PrixAchat,
(SELECT ROUND(AVG(PrixAchat),2)FROM Produit) PrixAchatMoyen,
PrixAchat-(SELECT ROUND(AVG(PrixAchat),2)FROM Produit) Ecart
FROM Produit;
90- Afficher les noms des clients qui ont acheté des produits stocké dans le dépôt ‘DEP002’.
SELECT Nom
FROM Client
WHERE CodeCLIENT NOT IN (SELECT CodeClient
FROM Commande
WHERE NumCommande IN (SELECT NumCommande
FROM DetailCommande DC
WHERE REFERENCE IN(
SELECT REFERENCE
FROM Stock
WHERE CodeDepot = 'DEP002')));
Remarque : Oracle ne contient ni les clauses TOP et TOP PERCENT de Microsoft ACCESS ni la clause
LIMIT de MySQL. IL faut utiliser la pseudo-colonne ROWNUM
SELECT SALAIRE
FROM Employe
ORDER BY SALAIRE DESC
FETCH FIRST 5 ROWS ONLY;
93- Extraire les informations NumEmploye, nom, prénom, salaire et fonction des employés dont on saisit la fonction
au clavier.
94-Extraire les informations NumEmploye, nom, prénom, salaire et DateEmbauche embauchés entre une date de
début et date de fin.
18
Oracle SQL – PL/SQL
Annexe1 :
Oracle : Expressions régulières
Les quantificateurs
Les quantifiateurs permettent de dire combien de fois un caractère doit être recherché
Quantifier Description
19
Oracle SQL – PL/SQL
c* N'importe quelle string qui contient zero ou plusieurs occurrences de c
c{n,} N'importe quelle string qui contient une séquence d'au moins n c.
?!s N'importe quelle string qui n'est pas suivie par une string spécifique s
20
Oracle SQL – PL/SQL
Oracle SQL
Oracle PL/SQL
21
Oracle SQL – PL/SQL
22