Exercices corrigés de langage SQL

Exercices corrigés SQL

Présentation

Cette section regroupe une série d'exercices corrigés pour vous entraîner à écrire des requêtes SQL sur différents schémas de bases de données. Cliquez sur "Afficher la solution" pour voir la réponse.

Exercice 1

Festival de musique

Soit la base de données d'un festival de musique : Dans une représentation peut participer un ou plusieurs musiciens. Un musicien ne peut participer qu'à une seule représentation.

Representation (Num_Rep , titre_Rep , lieu)
Musicien (Num_mus , nom , #Num_Rep)
Programmer (Date , #Num_Rep , tarif)

La liste des titres des représentations.
La liste des titres des représentations ayant lieu au « théâtre allissa ».
La liste des noms des musiciens et des titres des représentations auxquelles ils participent.
La liste des titres des représentations, les lieux et les tarifs du 25/07/2008.
Le nombre des musiciens qui participent à la représentation n°20.
Les représentations et leurs dates dont le tarif ne dépasse pas 20DH.

Solutions de l'exercice 1

Liste des titres :
```
SELECT titre_Rep FROM Representation;
```

Titres au théâtre allissa :

SELECT titre_Rep FROM Representation WHERE lieu = "theatre allissa";

Noms des musiciens et titres :

SELECT M.nom, R.titre_Rep FROM Musicien M INNER JOIN Representation R ON R.Num_rep = M.Num_rep;

Titres, lieux et tarifs du 25/07/2008 :

SELECT R.titre_Rep, R.lieu, P.tarif FROM Programmer P INNER JOIN Representation R 
ON P.Num_rep = R.Num_rep WHERE P.date = "2008-07-25";

Nombre de musiciens pour la représentation 20 :

SELECT COUNT(*) FROM Musicien WHERE Num_rep = 20;

Représentations avec tarif ≤ 20 DH :

SELECT R.Num_Rep, R.titre_Rep, P.Date FROM Representation R INNER JOIN Programmer P 
ON R.Num_Rep = P.Num_Rep WHERE P.tarif <= 20;

Exercice 2

Départements et employés

Soit la base de données suivante :

Départements : ( DNO, DNOM, DIR, VILLE)
Employés : ( ENO, ENOM, PROF, DATEEMB, SAL, COMM, #DNO)

Donnez la liste des employés ayant une commission.
Donnez les noms, emplois et salaires des employés par emploi croissant, et pour chaque emploi, par salaire décroissant.
Donnez le salaire moyen des employés.
Donnez le salaire moyen du département Production.
Donnez les numéros de département et leur salaire maximum.
Donnez les différentes professions et leur salaire moyen.
Donnez le salaire moyen par profession le plus bas.
Donnez le ou les emplois ayant le salaire moyen le plus bas, ainsi que ce salaire moyen.

Solutions de l'exercice 2

Employés avec commission :

SELECT * FROM Employes WHERE COMM IS NOT NULL;

Noms, emplois, salaires triés :

SELECT ENOM, PROF, SAL FROM Employes ORDER BY PROF ASC, SAL DESC;

Salaire moyen :
```
SELECT AVG(SAL) FROM Employes;
```

Salaire moyen du département Production :

SELECT AVG(E.SAL) FROM Employes E INNER JOIN Departement D ON E.DNO = D.DNO WHERE D.DNOM = 'production';

Numéros de département et salaire max :

SELECT DNO, MAX(SAL) FROM Employes GROUP BY DNO;

Professions et salaire moyen :

SELECT PROF, AVG(SAL) FROM Employes GROUP BY PROF;

Salaire moyen par profession le plus bas :

SELECT PROF, AVG(SAL) as moy FROM Employes 
GROUP BY PROF 
ORDER BY moy ASC 
LIMIT 1;

Emplois avec salaire moyen le plus bas :

SELECT PROF FROM Employes GROUP BY PROF
HAVING AVG(SAL) = (SELECT AVG(SAL) as moy FROM Employes
GROUP BY PROF ORDER BY moy ASC LIMIT 1);

Exercice 3

Gestion des notes

Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d'une promotion d'étudiants :

ETUDIANT(NEtudiant, Nom, Prénom)
MATIERE(CodeMat, LibelléMat, CoeffMat)
EVALUER(#NEtudiant, #CodeMat, Date, Note)

Quel est le nombre total d'étudiants ?
Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ?
Quelles sont les moyennes de chaque étudiant dans chacune des matières ?
Quelles sont les moyennes par matière ? (avec la vue MOYETUMAT de la question 3)
Quelle est la moyenne générale de chaque étudiant ? (avec la vue MOYETUMAT)
Quelle est la moyenne générale de la promotion ? (avec la vue MGETU)
Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ?

Solutions de l'exercice 3

Nombre total d'étudiants :
```
SELECT COUNT(*) FROM ETUDIANT;
```

Note la plus haute et la plus basse :

SELECT MIN(Note) as 'plus basse note', MAX(Note) as 'plus haute note' FROM EVALUER;

Moyennes par étudiant et par matière :

SELECT E.NEtudiant, M.LibelléMat, AVG(EV.Note) AS MoyEtuMat 
FROM EVALUER EV, MATIERE M, ETUDIANT E 
WHERE EV.CodeMat = M.CodeMat AND EV.NEtudiant = E.NEtudiant 
GROUP BY E.NEtudiant, M.LibelléMat;

Moyennes par matière :

SELECT LibelleMat, AVG(MoyEtuMat) FROM MOYETUMAT GROUP BY LibelleMat;

Moyenne générale de chaque étudiant :

SELECT NEtudiant, SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT GROUP BY NEtudiant;

Moyenne générale de la promotion :
```
SELECT AVG(MgEtu) FROM MGETU;
```

Étudiants avec moyenne ≥ moyenne promo :

SELECT NEtudiant, Nom, Prenom, MgEtu FROM MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU);

Exercice 4

Gestion de projets

Soit la base de données intitulée "gestion_projet" permettant de gérer les projets relatifs au développement de logiciels. Elle est décrite par la représentation textuelle simplifiée suivante :

Developpeur (NumDev, NomDev, AdrDev, EmailDev, TelDev)
Projet (NumProj, TitreProj, DateDeb, DateFin)
Logiciel (CodLog, NomLog, PrixLog, #NumProj)
Realisation (#NumProj, #NumDev)

Afficher les noms et les prix des logiciels appartenant au projet ayant comme titre « gestion de stock », triés dans l'ordre décroissant des prix.
Afficher le total des prix des logiciels du projet numéro 10. Lors de l'affichage, le titre de la colonne sera « cout total du projet ».
Afficher le nombre de développeurs qui ont participé au projet intitulé « gestion de stock ».
Afficher les projets qui ont plus que 5 logiciels.
Les numéros et noms des développeurs qui ont participé dans tous les projets.
Les numéros de projets dans lesquels tous les développeurs y participent.

Solutions de l'exercice 4

Noms et prix des logiciels du projet "gestion de stock" :

SELECT L.NomLog, L.PrixLog FROM Logiciel L INNER JOIN Projet P
ON L.NumProj = P.NumProj WHERE P.TitreProj = "gestion de stock"
ORDER BY L.PrixLog DESC;

Total des prix des logiciels du projet 10 :

SELECT SUM(PrixLog) as "cout total du projet" FROM Logiciel WHERE NumProj = 10;

Nombre de développeurs du projet "gestion de stock" :

SELECT COUNT(*) FROM Developpeur D INNER JOIN Realisation R ON D.NumDev = R.NumDev 
INNER JOIN Projet P ON P.NumProj = R.NumProj WHERE P.TitreProj = "gestion de stock";

Projets avec plus de 5 logiciels :

SELECT P.NumProj, P.TitreProj FROM Projet P INNER JOIN Logiciel L ON P.NumProj = L.NumProj 
GROUP BY P.NumProj, P.TitreProj 
HAVING COUNT(*) > 5;

Développeurs ayant participé à tous les projets :

SELECT D.NumDev, D.NomDev FROM Developpeur D INNER JOIN Realisation R ON D.NumDev = R.NumDev
GROUP BY D.NumDev, D.NomDev
HAVING COUNT(*) = (SELECT COUNT(*) FROM Projet);

Projets auxquels tous les développeurs participent :

SELECT P.NumProj, P.TitreProj FROM Projet P INNER JOIN Realisation R ON P.NumProj = R.NumProj
GROUP BY P.NumProj, P.TitreProj
HAVING COUNT(*) = (SELECT COUNT(*) FROM Developpeur);

Exercice 5

Cycle de formation

Ci-après, on donne la représentation textuelle simplifiée d'une base de données concernant un cycle de formation destiné à des étudiants. Il regroupe un ensemble de matières. On considère que chaque enseignant n'enseigne qu'une seule matière et qu'à la fin du cycle de formation, une note par matière, est attribuée à chaque étudiant. D'autre par, les étudiants peuvent ne pas suivre les mêmes matières.

ETUDIANT(CodeEt, NomEt, DatnEt)
MATIERE(CodeMat, NomMat, CoefMat)
ENSEIGNANT(CodeEns, NomEns, GradeEns, #CodeMat)
NOTE(#CodeEt, #CodeMat, note)

Les informations relatives aux étudiants (Code, Nom et Date de naissance) selon l'ordre alphabétique croissant du nom.
Les noms et les grades des enseignants de la matière dont le nom est 'BD'.
La liste distincte formée des noms et les coefficients des différentes matières qui sont enseignées par des enseignants de grade 'Grd3'.
La liste des matières (Nom et Coefficient) qui sont suivies par l'étudiant de code 'Et321'.
Le nombre d'enseignants de la matière dont le nom est 'Informatique'.

Solutions de l'exercice 5

Informations des étudiants par ordre alphabétique :
```
SELECT * FROM ETUDIANT ORDER BY NomEt ASC;
```

Enseignants de la matière 'BD' :

SELECT E.NomEns, E.GradeEns FROM ENSEIGNANT E INNER JOIN MATIERE M
ON M.CodeMat = E.CodeMat WHERE M.NomMat = "BD";

Matières enseignées par des enseignants de grade 'Grd3' :

SELECT DISTINCT(M.NomMat), M.CoefMat FROM ENSEIGNANT E INNER JOIN MATIERE M 
ON M.CodeMat = E.CodeMat WHERE E.GradeEns = "Grd3";

Matières suivies par l'étudiant 'Et321' :

SELECT M.NomMat, M.CoefMat FROM MATIERE M INNER JOIN NOTE N
ON M.CodeMat = N.CodeMat INNER JOIN ETUDIANT E ON E.CodeEt = N.CodeEt
WHERE E.CodeEt = "Et321";

Nombre d'enseignants de la matière 'Informatique' :

SELECT COUNT(*) FROM ENSEIGNANT E INNER JOIN MATIERE M ON M.CodeMat = E.CodeMat 
WHERE M.NomMat = "Informatique";

Exercice 6

Compagnie aérienne

On considère la base de données BD_AIR_MAROC suivante :

PILOTE (NUMPIL, NOMPIL, VILLE, SALAIRE)
AVION (NUMAV, NOMAV, CAPACITE, VILLE)
VOL (NUMVOL, #NUMPIL, #NUMAV, VILLE_DEP, VILLE_ARR, H_DEP, H_ARR)

Donnez la liste des avions dont la capacité est supérieure à 350 passagers.
Quels sont les numéros et noms des avions localisés à Marrakech ?
Quels sont les numéros des pilotes en service et les villes de départ de leurs vols ?
Donnez toutes les informations sur les pilotes de la compagnie.
Quel est le nom des pilotes domiciliés à Meknès dont le salaire est supérieur à 20000 DH ?
Quels sont les avions (numéro et nom) localisés à Marrakech ou dont la capacité est inférieure à 350 passagers ?
Quels sont les numéros des pilotes qui ne sont pas en service ?
Donnez le numéro des vols effectués au départ de Marrakech par des pilotes de Meknès.
Quels sont les vols effectués par un avion qui n'est pas localisé à Marrakech ?
Quelles sont les villes desservies à partir de la ville d'arrivée d'un vol au départ de Guelmim ?

Solutions de l'exercice 6

Avions avec capacité > 350 :

SELECT * FROM AVION WHERE CAPACITE > 350;

Avions localisés à Marrakech :

SELECT NUMAV, NOMAV FROM AVION WHERE VILLE = 'Marrakech';

Pilotes en service et villes de départ :
```
SELECT NUMPIL, VILLE_DEP FROM VOL;
```
Tous les pilotes :
```
SELECT * FROM PILOTE;
```

Pilotes de Meknès avec salaire > 20000 :

SELECT NOMPIL FROM PILOTE WHERE VILLE = 'Meknes' AND SALAIRE > 20000;

Avions à Marrakech ou capacité < 350 :

SELECT NUMAV, NOMAV FROM AVION WHERE VILLE = 'Marrakech' OR CAPACITE < 350;

Pilotes pas en service :

SELECT NUMPIL FROM PILOTE WHERE NUMPIL NOT IN (SELECT DISTINCT NUMPIL FROM VOL);

Vols au départ de Marrakech par pilotes de Meknès :

SELECT DISTINCT V.NUMVOL FROM VOL AS V, PILOTE AS P 
WHERE V.NUMPIL = P.NUMPIL AND V.VILLE_DEP = 'Marrakech' AND P.VILLE = 'Meknes';

Vols par avion non localisé à Marrakech :

SELECT DISTINCT V.NUMVOL FROM VOL V, AVION A WHERE A.NUMAV = V.NUMAV AND A.VILLE != 'Marrakech';

Villes desservies depuis une ville d'arrivée :

SELECT DISTINCT VILLE_ARR FROM VOL WHERE VILLE_DEP = 'Guelmim' AND VILLE_DEP != VILLE_ARR;

Exercice 7

Gestion des employés et projets

Soit le schéma relationnel suivant :

Departement (NomD, N_Dep, Directeur)
Employe (Matricule, Nom, Prénom, DateNaissance, Adresse, Salaire, #N_dep, superieur)
Projet (NomP, N_pro, Lieu, #N_Dep)
Travaille (#Matricule, #N_Proj, Heures)

L'attribut superieur dans la relation Employe contient le matricule du supérieur direct de l'employé. Chaque employé appartient à un département et travaille sur zéro, un ou plusieurs projets. Chaque projet est rattaché à un département qui peut être différent de celui des employés travaillant sur ce projet.

Date de naissance et l'adresse de Taha Lamharchi.
Nom et adresse des employés qui travaillent au département de recherche.
Nom et Prénom des employés dont le supérieur est Taha Lamharchi.
Nom des employés qui travaillent plus de 10 heures sur un projet à Guelmim.
Nom des projets sur lesquels travaillent Taha Lamharchi et Dounia Mahmoud.
Nom et prénom des employés qui ne travaillent sur aucun projet.
Numéro des projets qui ont au moins un participant de chaque département.
Nom des employés qui ne travaillent pas sur un projet à Guelmim.

Solutions de l'exercice 7

Date de naissance et adresse de Taha Lamharchi :

SELECT DateNaissance, Adresse FROM Employe WHERE Nom = 'Lamharchi' AND Prenom = 'Taha';

Employés du département recherche :

SELECT E.Nom, E.Adresse FROM Employe as E, Departement as D 
WHERE E.N_dep = D.N_dep AND NomD = 'recherche';

Employés dont le supérieur est Taha Lamharchi :

SELECT Nom, Prenom FROM Employe 
WHERE superieur = (SELECT Matricule FROM Employe WHERE Nom = 'Lamharchi' AND Prenom = 'Taha');

Employés travaillant > 10h sur un projet à Guelmim :

SELECT E.Nom FROM Employe as E, Travaille as T, Projet P 
WHERE E.Matricule = T.Matricule AND T.N_proj = P.N_proj AND T.Heures >= 10 AND P.Lieu = 'Guelmim';

Projets communs à Taha Lamharchi et Dounia Mahmoud :

SELECT T.N_proj FROM Travaille as T, Employe as E 
WHERE T.Matricule = E.Matricule AND E.Nom = 'Lamharchi' AND E.Prenom = 'Taha'
INTERSECT
SELECT T.N_proj FROM Travaille as T, Employe as E 
WHERE T.Matricule = E.Matricule AND E.Nom = 'Mahmoud' AND E.Prenom = 'Dounia';

Employés sans projet :

SELECT Nom, Prenom FROM Employe 
WHERE Matricule NOT IN (SELECT Matricule FROM Travaille);

Projets avec au moins un participant de chaque département :

SELECT T.N_proj FROM Travaille as T, Projet as P, Employe as E 
WHERE T.N_proj = P.N_proj AND T.Matricule = E.Matricule 
GROUP BY T.N_proj 
HAVING COUNT(DISTINCT E.N_dep) = (SELECT COUNT(*) FROM Departement);

Employés ne travaillant pas sur un projet à Guelmim :

SELECT Nom FROM Employe 
WHERE Matricule NOT IN (SELECT T.Matricule FROM Travaille as T, Projet as P 
WHERE T.N_proj = P.N_proj AND P.Lieu = 'Guelmim');

Exercice 8

Agence de voyage en ligne

Soit le schéma relationnel suivant qui représente la base de données d'une agence de voyage en ligne.

CLIENT (NumCli, Nom, Prénom, e-mail, NumCB )
VOYAGE (CodeVoyage, Destination, Durée, Prix )
RESERVATION (#NumCli, #CodeVoyage, DateRes )

Nom, prénom et e-mail des clients ayant une réservation en cours.
Nom, prénom et e-mail des clients n'ayant aucune réservation en cours.
Destination et liste des clients ayant réservé pour un voyage de plus de 10 jours et coûtant moins de 1000 DH.
Numéros de tous les clients ayant réservé sur tous les voyages proposés.

Solutions de l'exercice 8

Clients avec réservation :

SELECT Nom, Prenom, e-mail FROM CLIENT 
WHERE NumCli IN (SELECT DISTINCT NumCli FROM RESERVATION);

Clients sans réservation :

SELECT Nom, Prenom, e-mail FROM CLIENT 
WHERE NumCli NOT IN (SELECT DISTINCT NumCli FROM RESERVATION);

Voyages de plus de 10 jours et moins de 1000 DH :

SELECT C.Nom, C.Prenom, V.Destination FROM CLIENT as C, VOYAGE as V, RESERVATION as R
WHERE C.NumCli = R.NumCli and V.CodeVoyage = R.CodeVoyage AND Duree >= 10 AND Prix < 1000;

Clients ayant réservé tous les voyages :

SELECT NumCli FROM RESERVATION 
GROUP BY NumCli 
HAVING COUNT(*) = (SELECT COUNT(*) FROM VOYAGE);

Exercice 9

Cinéma

Soit la base de données « cinéma » dont le schéma relationnel est donné ci-dessous :

VILLE (CodePostal, NomVille )
CINEMA (NumCine, NomCine, Adresse, #CodePostal )
SALLE (NumSalle, Capacité, #NumCine )
FILM (NumExploit, Titre, Durée)
PROJECTION (#NumExploit, #NumSalle, NumSemaine, Nbentrees)

Titre des films dont la durée est supérieure ou égale à deux heures.
Nom des villes abritant un cinéma nommé « RIF ».
Nom des cinémas situés à Meknès ou contenant au moins une salle de plus 100 places.
Nom, adresse et ville des cinémas dans lesquels on joue le film « Hypnose » la semaine 18.
Numéro d'exploitation des films projetés dans toutes les salles.
Titre des films qui n'ont pas été projetés.

Solutions de l'exercice 9

Films de durée ≥ 2 heures :

SELECT Titre FROM FILM WHERE Duree >= 2;

Villes avec cinéma RIF :

SELECT NomVille FROM VILLE
WHERE CodePostal IN (SELECT CodePostal FROM CINEMA WHERE NomCine = 'RIF');

Cinémas à Meknès ou avec salle de +100 places :

SELECT NomCine FROM CINEMA 
WHERE CodePostal = (SELECT CodePostal FROM VILLE WHERE NomVille = 'Meknes') 
OR NumCine IN (SELECT NumCine FROM SALLE WHERE Capacite >= 100);

Cinémas jouant "Hypnose" semaine 18 :

SELECT C.NomCine, C.Adresse, V.NomVille FROM CINEMA as C, VILLE as V
WHERE C.CodePostal = V.CodePostal AND 
C.NumCine IN (SELECT S.NumCine FROM SALLE as S, FILM as F, PROJECTION as P 
WHERE P.NumExploit = F.NumExploit AND P.NumSalle = S.NumSalle AND F.Titre = 'Hypnose' AND P.NumSemaine = 18);

Films projetés dans toutes les salles :

SELECT NumExploit FROM PROJECTION 
GROUP BY NumExploit 
HAVING COUNT(*) = (SELECT COUNT(*) FROM SALLE);

Films jamais projetés :

SELECT Titre FROM FILM WHERE NumExploit NOT IN (SELECT NumExploit FROM PROJECTION);

Exercice 10

Tour de France 97

Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du Tour de France 97, dont une des étapes de type "contre la montre individuel" se déroula à Saint-Etienne :

EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif)
COUREUR(NuméroCoureur, NomCoureur, #CodeEquipe, #CodePays)
PAYS(CodePays, NomPays)
TYPE_ETAPE(CodeType, LibelleType)
ETAPE(NuméroEtape, DateEtape, VilleDep, VilleArr, NbKm, #CodeType)
PARTICIPER(#NuméroCoureur, #NuméroEtape, TempsRealise)
ATTRIBUER_BONIFICATION(#NuméroEtape, #NuméroCoureur, km, Rang, NbSecondes)

Quelle est la composition de l'équipe Festina (Numéro, nom et pays des coureurs) ?
Quel est le nombre de kilomètres total du Tour de France 97 ?
Quel est le nombre de kilomètres total des étapes de type "Haute Montagne" ?
Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
Quel est le classement général des coureurs à l'issue des 13 premières étapes ?
Quel est le classement par équipe à l'issue des 13 premières étapes ?

Solutions de l'exercice 10

Composition de l'équipe Festina :

SELECT NumeroCoureur, NomCoureur, NomPays FROM EQUIPE A, COUREUR B, PAYS C 
WHERE A.CodeEquipe = B.CodeEquipe AND B.CodePays = C.CodePays AND NomEquipe = "FESTINA";

Kilométrage total du Tour :
```
SELECT SUM(NbKm) FROM ETAPE;
```

Kilométrage des étapes "Haute Montagne" :

SELECT SUM(NbKm) FROM ETAPE A, TYPE_ETAPE B 
WHERE A.CodeType = B.CodeType AND LibelleType = "HAUTE MONTAGNE";

Coureurs sans bonification :

SELECT NomCoureur FROM COUREUR
WHERE NumeroCoureur NOT IN (SELECT NumeroCoureur FROM ATTRIBUER_BONIFICATION);

Coureurs ayant participé à toutes les étapes :

SELECT NomCoureur FROM PARTICIPER A, COUREUR B 
WHERE A.NumeroCoureur = B.NumeroCoureur 
GROUP BY NomCoureur
HAVING COUNT(*) = (SELECT COUNT(*) FROM ETAPE);

Classement général après 13 étapes :

SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsRealise) AS Total
FROM PARTICIPER A, COUREUR B
WHERE A.NumeroCoureur = B.NumeroCoureur AND NumeroEtape <= 13 
GROUP BY NomCoureur, CodeEquipe, CodePays ORDER BY Total;

Classement par équipe après 13 étapes :

SELECT NomEquipe, SUM(TempsRealise) AS Total
FROM PARTICIPER A, COUREUR B, EQUIPE C
WHERE A.NumeroCoureur = B.NumeroCoureur AND B.CodeEquipe = C.CodeEquipe AND NumeroEtape <= 13
GROUP BY NomEquipe ORDER BY Total;

Exercice 11

Extrait HEC 2014

A partir du système d'information de l'entreprise, le service des ressources humaines peut extraire et analyser les informations relatives à tous les personnels. Celui-ci lui permet en particulier d'exercer un suivi dans le domaine de la formation. Un extrait de ce domaine est présenté sous forme d'un schéma relation :

Quel est le nombre de formations suivies par catégories de salariés ayant débuté au cours de la période du 01/06/2011 au 31/12/2011 ?
Quelles sont les catégories pour lesquelles le nombre d'heures de formation est supérieur à la moyenne du nombre d'heures des formations suivies par l'ensemble des personnels ?
Le responsable des ressources humaines souhaite intégrer dans la base de données une nouvelle formation liée au sertissage des boîtes de conserve. Les nouvelles données à insérer sont les suivantes : "FORM587, sertissage niveau 1, 25j, perfectionnement, 12, 525". Ecrire la requête permettant de mettre à jour la base.

Solutions de l'exercice 11

Nombre de formations par catégorie (période 01/06-31/12/2011) :

SELECT Libellecategorie, COUNT(distinct Codeform) FROM SUIVRE, SALARIE, CATEGORIE 
WHERE SUIVRE.Matriculesal = SALARIE.Matriculesal AND SALARIE.codecategorie = CATEGORIE.codecategorie
AND Datedebut BETWEEN "2011-06-01" AND "2011-12-31"
GROUP BY Libellecategorie;

Catégories avec heures de formation > moyenne :

SELECT Libellecategorie FROM SUIVRE, SALARIE, CATEGORIE, FORMATION
WHERE SUIVRE.Matriculesal = SALARIE.Matriculesal AND SALARIE.codecategorie = CATEGORIE.codecategorie 
AND FORMATION.Codeform = SUIVRE.Codeform
GROUP BY Libellecategorie
HAVING SUM(Dureeform) > (SELECT AVG(Dureeform) FROM SUIVRE, FORMATION WHERE SUIVRE.Codeform = FORMATION.Codeform);

Insertion d'une nouvelle formation :

INSERT INTO FORMATION VALUES ('FORM587', 'sertissage niveau 1', 600, 'perfectionnement', 12, 525);

Note: 25 jours = 600 heures (25 × 24h).

Exercice 12

Gestion des clients et représentants

La société X utilise le logiciel de gestion de base de données Access pour gérer ses clients et ses représentants. Voici la liste des tables créées dans Access :

Afficher la liste des clients appartenant à la catégorie tarifaire n°1, classée par ordre alphabétique.
Afficher la liste des clients (code, nom de client) rattachés au représentant HINAUD.
Afficher la liste des clients bénéficiant d'une remise de 10%.
Afficher la liste des représentants (Numéro et nom) dépendant du chef de secteur PONS.
Afficher la liste des départements (code, nom, chef de secteur).
Afficher la liste des chefs de secteur.

Solutions de l'exercice 12

Clients catégorie 1 par ordre alphabétique :

SELECT CODE_CLT, NOM_CLT FROM client WHERE NUM_CAT = 1 
ORDER BY NOM_CLT ASC;

Clients du représentant HINAUD :

SELECT CODE_CLT, NOM_CLT FROM client, representant 
WHERE client.NUM_REP = representant.NUM_REP AND NOM_REP = 'HINAUD';

Clients avec remise de 10% :

SELECT CODE_CLT, NOM_CLT FROM client, categorie_tarifaire 
WHERE client.NUM_CAT = categorie_tarifaire.NUM_CAT AND REMISE = '10%';

Représentants du chef de secteur PONS :

SELECT NUM_REP, NOM_REP FROM representant, couvrir, departement 
WHERE representant.NUM_REP = couvrir.NUM_REP AND couvrir.CODE_DEP = departement.CODE_DEP 
AND CHEF_SECTEUR = 'PONS';

Liste des départements :
```
SELECT * FROM departement;
```

Liste des chefs de secteur distincts :

SELECT DISTINCT CHEF_SECTEUR FROM departement;

Exercice 13

SAV - Gestion des interventions

Le responsable du SAV d'une entreprise d'électroménager a mis en place une petite base de données afin de gérer les interventions de ces techniciens. Le modèle relationnel à la source de cette base de données est le suivant :

Client (Codecl, nomcl, prenomcl, adresse, cp, ville)
Produit (Référence, désignation, prix)
Techniciens (Codetec, nomtec, prenomtec, tauxhoraire)
Intervention (Numéro, date, raison, #codecl, #référence, #codetec)

La liste des produits (référence et désignation) classées du moins cher au plus cher.
Le nombre d'intervention du technicien n°2381.
La liste des clients ayant demandé une intervention pour des produits d'un prix supérieur à 300 dhs.
Les interventions effectuées par le technicien 'Mestiri Mohamed' entre le 1er et le 31 août 2009.
Par ailleurs il vous informe que le produit référencé 548G a vu son prix augmenter (nouveau prix = 320 dhs).
Vous apprenez également par le directeur des ressources humaines qu'un nouveau technicien a été recruté : son code est le 3294, il s'appelle 'El Abed Ridha' et est rémunéré à un taux horaire de 15 dhs.

Solutions de l'exercice 13

Produits du moins cher au plus cher :

SELECT Reference, designation FROM produit ORDER BY prix ASC;

Nombre d'interventions du technicien 2381 :

SELECT COUNT(*) FROM Intervention WHERE codetec = 2381;

Clients avec intervention sur produit > 300 DH :

SELECT nomcl FROM Client clt, Produit prod, Intervention inter
WHERE clt.codecl = inter.codecl AND prod.Reference = inter.Reference AND prod.prix > 300;

Interventions de Mestiri Mohamed en août 2009 :

SELECT Numero, date, raison FROM Intervention int, Techniciens tec
WHERE int.codetec = tec.codetec AND tec.nomtec = 'Mestiri' AND tec.prenomtec = 'Mohamed' 
AND int.date BETWEEN '2009-08-01' AND '2009-08-31';

Ou avec les fonctions MONTH et YEAR :

SELECT Numero, date, raison FROM Intervention int, Techniciens tec 
WHERE int.codetec = tec.codetec AND tec.nomtec = 'Mestiri' AND tec.prenomtec = 'Mohamed' 
AND MONTH(int.date) = 8 AND YEAR(int.date) = 2009;

Mise à jour du prix du produit 548G :

UPDATE Produit SET prix = 320 WHERE Reference = '548G';

Insertion d'un nouveau technicien :

INSERT INTO Technicien VALUES (3294, 'El Abed', 'Ridha', 15);

Exercice 14

Gestion de facturation

La représentation textuelle suivante est une description simplifiée d'une base de données de gestion de facturation d'une entreprise commerciale.

Client (Numcli, Nomcli, Prenomcli, adressecli, mailcli)
Produit (Numprod, désignation, prix , qte_stock)
Vendeur (Idvendeur, Nomvendeur, adresse_vend)
Commande (Numcom, #Numcli, #Idvendeur, #Numprod, date_com, qte_com)

On suppose que Numcli, Numprod, Idvendeur et Numcom sont de type numérique. Le nom, le prénom et l'adresse des clients ainsi que les vendeurs sont des informations obligatoires, le mail peut ne pas être indiqué. La valeur par défaut de la quantité en stock des produits (qte_stock) est égale à 0.

Créer les tables : Client, Produit, Vendeur et Commande.
La liste des clients de Marrakech.
La liste des produits (Numprod, désignation, prix) classés du plus cher au moins cher.
Noms et adresses des vendeurs dont le nom commence par la lettre 'M'.
La liste des commandes effectuées par le vendeur "Mohammed" entre le 1er et 30 janvier 2020.
Le nombre des commandes contenant le produit n° 365.

Solutions de l'exercice 14

Création des tables :

CREATE TABLE Client (
    Numcli int primary key,
    Nomcli varchar(50) not null,
    Prenomcli varchar(50) not null,
    adressecli varchar(200) not null,
    mailcli varchar(100)
);

CREATE TABLE Produit (
    Numprod int primary key,
    designation varchar(100),
    prix float,
    qte_stock int default 0
);

CREATE TABLE Vendeur (
    Idvendeur int primary key,
    Nomvendeur varchar(50) not null,
    adresse_vend varchar(200) not null
);

CREATE TABLE Commande (
    Numcom int primary key,
    Numcli int,
    Idvendeur int,
    Numprod int,
    date_com date,
    qte_com int,
    FOREIGN KEY (Numcli) REFERENCES Client(Numcli),
    FOREIGN KEY (Idvendeur) REFERENCES Vendeur(Idvendeur),
    FOREIGN KEY (Numprod) REFERENCES Produit(Numprod)
);

Clients de Marrakech :

SELECT * FROM Client WHERE adressecli LIKE '%Marrakech%';

Produits du plus cher au moins cher :

SELECT Numprod, designation, prix FROM Produit ORDER BY prix DESC;

Vendeurs dont le nom commence par 'M' :

SELECT Nomvendeur, adresse_vend FROM Vendeur WHERE Nomvendeur LIKE 'M%';

Commandes du vendeur "Mohammed" en janvier 2020 :

SELECT Numcom, Numcli, Idvendeur, Numprod, date_com, qte_com
FROM Commande cmd, Vendeur vend
WHERE cmd.Idvendeur = vend.Idvendeur AND vend.Nomvendeur = 'Mohammed'
AND cmd.date_com BETWEEN '2020-01-01' AND '2020-01-30';

Nombre de commandes pour le produit 365 :

SELECT COUNT(*) FROM Commande WHERE Numprod = 365;

Exercice 15

Gestion des étudiants et notes

Soit la base de données suivante :

La liste de tous les étudiants.
Nom et coefficient des matières.
Les numéros des cartes d'identité des étudiants dont la moyenne entre 7 et 12.
La liste des étudiants dont le nom commence par 'ben'.
Le nombre des étudiants qui ont comme matière '12518'.
La somme des coefficients des matières.
Les noms des étudiants qui ont une note_examen > 10.
Afficher les noms et les coefficients des matières étudiées par l'étudiant "01234568".

Solutions de l'exercice 15

Liste de tous les étudiants :
```
SELECT * FROM Etudiant;
```

Nom et coefficient des matières :

SELECT nom_matiere, coefficient FROM Matiere;

Numéros des cartes des étudiants avec moyenne entre 7 et 12 :

SELECT numero_carte_etudiant FROM Note, Matiere mat 
WHERE Note.code_matiere = mat.code_matiere
GROUP BY numero_carte_etudiant 
HAVING (SUM(note_examen * coefficient) / SUM(coefficient)) BETWEEN 7 AND 12;

Étudiants dont le nom commence par 'ben' :

SELECT * FROM Etudiant WHERE Nom LIKE 'Ben%';

Nombre d'étudiants ayant la matière '12518' :

SELECT COUNT(*) FROM Note WHERE code_matiere = 12518;

Somme des coefficients des matières :
```
SELECT SUM(coefficient) FROM Matiere;
```

Noms des étudiants avec note_examen > 10 :

SELECT DISTINCT Nom FROM Note, Etudiant
WHERE Note.numero_carte_etudiant = Etudiant.numero_carte_etudiant AND note_examen > 10;

Matières étudiées par l'étudiant "01234568" :

SELECT nom_matiere, coefficient FROM Note, Matiere 
WHERE Note.code_matiere = Matiere.code_matiere 
AND Note.numero_carte_etudiant = '01234568';

Exercice 16

Gestion des incidents techniques

Afin d'assurer la qualité des produits attendues par les Clients, l'entreprise cherche à optimiser la gestion des pannes pouvant survenir dans les infrastructures de production nécessaires à la fabrication du Ciment. Voici un extrait de la base de données :

TECHNICIEN (idTech, nom, prénom, spécialité)
STATION (idstat, nom, Position, coordLat, coordLong, phase)
MACHINE (idmach, état, dateMiseEnService, dateDernièreRévision, #idStat)
TYPEINCIDENT (id, description, tempsRéparationPrévu)
INCIDENT (idInd, remarques, dateHeure, dateHeureCloture, #idmach, #idType)
INTERVENTION (idInterv, dateHeureDébut, dateHeureFin, #idInd, #idTech)

Rédiger la requête SQL permettant d'obtenir la liste par ordre alphabétique des noms et prénoms des techniciens ayant réalisé une intervention sur la Machine identifiée par Ber001.
Rédiger la requête SQL permettant d'obtenir la liste des phases ayant connu un incident de "sur-chauffage" pour le mois Mai 2019.
Rédiger la requête SQL permettant d'obtenir le nombre d'incidents non clôturés.
Rédiger la requête SQL permettant d'obtenir la liste des noms des stations ayant eu plus de dix incidents.

Solutions de l'exercice 16

Techniciens ayant intervenu sur la machine Ber001 :

SELECT nom, prenom FROM TECHNICIEN tec, INCIDENT inc, INTERVENTION int 
WHERE tec.idTech = int.idTech AND int.idInd = inc.idInd AND inc.idmach = 'Ber001' 
ORDER BY nom ASC, prenom ASC;

Phases avec incident "sur-chauffage" en Mai 2019 :

SELECT DISTINCT phase FROM STATION st, MACHINE mch, INCIDENT inc, TYPEINCIDENT type
WHERE inc.idmach = mch.idmach AND st.idstat = mch.idstat AND type.id = inc.idType 
AND type.description = 'sur-chauffage' AND MONTH(inc.dateHeure) = 5 AND YEAR(inc.dateHeure) = 2019;

Nombre d'incidents non clôturés :

SELECT COUNT(*) FROM INCIDENT WHERE dateHeureCloture IS NULL;

Stations ayant eu plus de dix incidents :

SELECT st.nom FROM STATION st, MACHINE mch, INCIDENT inc 
WHERE inc.idmach = mch.idmach AND st.idstat = mch.idstat
GROUP BY st.nom 
HAVING COUNT(*) > 10;

Exercice 17

Gestion des ventes et concurrents

Voici un extrait de la base de données gestion des ventes :

Produit (Ref, Designation, PrixUnitaire, Dimension, #code_Machine)
Vente (Ncom, #Ref, Qte, DateLiv)
Commande (Ncom, DateCmd, #CodeClt, #Code_Salarie)
Produit_concurrent(Ref, Designation, PrixUnitaire, Dimension, #code_Machine, Nom_Concurrent)

Donner la requête qui permet d'obtenir le chiffre d'affaire mensuel de l'année en cours.
Donner la requête qui calcule le taux de vente de chaque produit.
Donner la requête qui affiche le produit le plus vendu du mois en cours.
La table produit concurrent est composée des informations sur les produits vedettes des concurrents. Donner la requête qui permet d'ajouter tous les produits du concurrent GleenAlu à la table Produits.

Solutions de l'exercice 17

Chiffre d'affaire mensuel de l'année en cours :

SELECT SUM(V.Qte * P.PrixUnitaire) as CA, MONTH(C.DateCmd) as Mois 
FROM Produit P, Vente V, Commande C 
WHERE P.Ref = V.Ref AND V.Ncom = C.Ncom AND YEAR(C.DateCmd) = YEAR(NOW()) 
GROUP BY MONTH(C.DateCmd);

Taux de vente de chaque produit :

SELECT Ref, SUM(Qte) / (SELECT SUM(Qte) FROM Vente) as TauxVente 
FROM Vente 
GROUP BY Ref;

Produit le plus vendu du mois en cours :

SELECT P.Ref, P.Designation, SUM(V.Qte) as TotalVentes
FROM Produit P, Vente V, Commande C
WHERE P.Ref = V.Ref AND V.Ncom = C.Ncom AND MONTH(C.DateCmd) = MONTH(NOW()) 
AND YEAR(C.DateCmd) = YEAR(NOW())
GROUP BY P.Ref, P.Designation
ORDER BY TotalVentes DESC
LIMIT 1;

Ou avec une sous-requête :

SELECT Ref, Designation, tot 
FROM (SELECT P.Ref, P.Designation, SUM(V.Qte) as tot 
      FROM Produit P, Vente V, Commande C
      WHERE P.Ref = V.Ref AND V.Ncom = C.Ncom 
      AND MONTH(C.DateCmd) = MONTH(NOW()) AND YEAR(C.DateCmd) = YEAR(NOW())
      GROUP BY P.Ref, P.Designation) as TMP
ORDER BY tot DESC LIMIT 1;

Ajout des produits du concurrent GleenAlu :
```
INSERT INTO Produit (Ref, Designation, PrixUnitaire, Dimension, code_Machine) 
SELECT Ref, Designation, PrixUnitaire, Dimension, code_Machine 
FROM Produit_concurrent 
WHERE Nom_Concurrent = 'GleenAlu';
```
Note : Cette requête suppose que les références des produits concurrents n'existent pas déjà dans la table Produit. En cas de conflit, il faudrait utiliser INSERT IGNORE ou ON DUPLICATE KEY UPDATE selon le SGBD.

Récapitulatif

Ces 17 exercices couvrent les concepts fondamentaux du langage SQL :

Sélections simples : SELECT, WHERE, ORDER BY
Jointures : INNER JOIN, jointures implicites
Fonctions d'agrégation : COUNT, SUM, AVG, MIN, MAX
Regroupements : GROUP BY, HAVING
Sous-requêtes : IN, NOT IN, comparaisons avec agrégats
Mise à jour des données : INSERT, UPDATE
Création de tables : CREATE TABLE avec contraintes

Sortie

// La sortie apparaîtra ici…

Prêt · Ctrl+Entrée pour exécuter

SQL SELECT SQL GROUP BY SQL WHERE SQL Jointures SQL sous-requêtes SQL ORDER BY SQL DELETE SQL SUM SQL COUNT SQL MAX SQL Exercices SQL

Exercices corrigés SQL

Festival de musique

Départements et employés

Gestion des notes

Gestion de projets

Cycle de formation

Compagnie aérienne

Gestion des employés et projets

Agence de voyage en ligne

Cinéma

Tour de France 97

Extrait HEC 2014

Gestion des clients et représentants

SAV - Gestion des interventions

Gestion de facturation

Gestion des étudiants et notes

Gestion des incidents techniques

Gestion des ventes et concurrents

Discussion (0)

Laisser un commentaire

Structures de données

Algorithmes

Programmation

Bases de données

CPGE

Modules informatique

Exercices corrigés de langage SQL

Exercices corrigés SQL

Festival de musique

Départements et employés

Gestion des notes

Gestion de projets

Cycle de formation

Compagnie aérienne

Gestion des employés et projets

Agence de voyage en ligne

Cinéma

Tour de France 97

Extrait HEC 2014

Gestion des clients et représentants

SAV - Gestion des interventions

Gestion de facturation

Gestion des étudiants et notes

Gestion des incidents techniques

Gestion des ventes et concurrents

Discussion (0)

Laisser un commentaire