17 Exercices corrigés SQL requêtes

Ces exercices corrigés de requêtes SQL sont pour des débutants à confirmés.

requêtes SQL

Exercice flashback sur modèle entité-association

Une agence immobilière veut gérer son parc immobilier. Elle veut stocker dans sa base tout logement voué à la location.

Pour cela, elle attribue un identifiant à chaque logement, son adresse, sa superficie et le loyer. Un logement se situe dans un quartier auquel on attribue un numéro et un libellé. A chaque logement correspond un type de logement auquel sont associées des charges forfaitaires. Par ailleurs, l’agence stocke également les locataires de son parc. Ceux-ci sont identifiés par un numéro, sont caractérisés par leurs noms, prénoms, date de naissance et numéro de téléphone. A un logement peut être associé plusieurs locataires. En revanche, un individu ne peut être référencé que dans un seul logement.

Vous proposerez le modèle entité-association de ce cahier des charges

Et vous en déduirez le modèle logique en identifiant les clés primaires et étrangères.

Exercice préliminaire pour comprendre le SQL

Une petite entreprise s’est constituée une base de données relationnelle pour conserver les informations sur ses employés et les départements auxquels ils appartiennent. La base de données contient les tables suivantes (les clés primaires sont soulignées) :

EMPLOYE(id_employe, nom, prenom, profession, date_embauche, salaire, commission, id_departement) contient la liste des employés, définis par un identifiant interne au système, leur nom (unique), leur prénom, leur profession (exécutif, gestionnaire, ingénieur, vendeur, technicien et stagiaire), leur date d’embauche, leur salaire (entier positif, un stagiaire peut ne pas être rémunéré), leur commission (un employé peut ne pas avoir de commission) et l’identifiant du département auquel appartient l’employé.

DEPARTEMENT(id_departement, nom, directeur, ville) contient la liste des départements définis par un identifiant unique, leur nom (commercial, production, développement et ressources humaines), l’identifiant de l’employé directeur du département et la ville, lieu du département.

Voici le schéma de la base de données sous une forme graphique :

entité association

La base de données contient les données suivantes :

entité association

Exercice 1

Pour chacune des requêtes suivantes, donnez-en le sens en français (attention à ne pas paraphraser le code SQL) et indiquez-en le résultat.

 

1)            SELECT DEPARTEMENT.Id_departement, DEPARTEMENT.nom

FROM DEPARTEMENT

WHERE DEPARTEMENT.Id_departement NOT IN (SELECT EMPLOYE.Id_departement FROM EMPLOYE);

2)            SELECT E2.nom AS “Nom Directeur”, COUNT(E1.nom) AS “Nombre d’Employés”

FROM EMPLOYE E1, EMPLOYE E2, DEPARTEMENT D

WHERE E1.Id_departement = D.Id_departement

AND E2.Id_employe = D.Directeur

GROUP BY E2.nom

ORDER BY 1;

3)    SELECT nom, salaire FROM EMPLOYE

WHERE salaire > ALL (SELECT salaire

FROM EMPLOYE

WHERE profession = ‘Ingénieur’);

4)    SELECT E1.nom AS “Nom Employé”, E1.date_embauche, E2.nom AS “Nom Directeur”, E2.date_embaucheFROM EMPLOYE E1, EMPLOYE E2, DEPARTEMENT DWHERE E2.Id_employe = D.DirecteurAND E1.Id_departement = D.Id_departementAND E1.Date_embauche < E2.Date_embauche;

1) Quels sont les départements qui n’ont pas d’employés?

No rows selected

2) Quel est le nombre d’employés par directeur?

Nom Directeur                        Nombre d’Employés

——————————       ————————–

Jackson                                                                   3

Lavergne                                                                 1

McDonald                                                               4

Vaillant                                                                    2

3) Quel est le salaire et le nom des employés gagnant plus que tous les ingénieurs?

Nom                                                 Salaire

—————————-          ————–

Furnon                                               6000

Babin                                                 7000

McDonald                                       10000

4) Quel est le nom et la date d’embauche des employés embauchés avant leur directeur; donner également le nom et la date d’embauche de leur directeur.

Nom Employé                     Date_embauche                    Nom Directeur              Date_embauche

———————-                ————————-                          ————————                    ———————-

Sutton                           01-04-1989               McDonald                        06-05-2001

Furnon                          11-06-1989              McDonald                          06-05-2001

Babin                       08-08-2000                     McDonald                        06-05-2001

Exercice 2

Pour chaque requête, indiquez si (Oui ou Non) elle donne le résultat demandé (plusieurs requêtes peuvent donner le bon résultat)

6)            Donner le salaire et le nom des employés gagnant plus que le plus bas salaire des ingénieurs:.

6a       SELECT DISTINCT E1.nom, E1.salaireFROM EMPLOYE E1, EMPLOYE E2WHERE E2.Profession =  ‘Ingénieur’AND E1.salaire > MIN(E2.salaire);

6b       SELECT nom, salaire FROM EMPLOYEWHERE salaire > ALL (SELECT MIN(salaire)FROM EMPLOYEWHERE profession = ‘Ingénieur’);

6c        SELECT nom, salaire FROM EMPLOYEWHERE salaire < ANY (SELECT MIN(salaire)FROM EMPLOYE

WHERE profession = ‘Ingénieur’);

  • Donner les noms des employés ayant la même profession et le même directeur que ‘Furnon’:

7a       SELECT nom, professionFROM EMPLOYEWHERE  nom <> ‘Furnon’AND  Id_departement  IN  (SELECT D2.Id_departementFROM EMPLOYE E, DEPARTEMENT D1, DEPARTEMENT D2WHERE E.nom = ‘Furnon’AND D1.Id_departement = E.Id_departementAND D1.Directeur = D2.Directeur);

7b       SELECT E.nomFROM EMPLOYE E, DEPARTEMENT DWHERE E.nom <> ‘Furnon’ AND E.Id_departement = D.Id_departementAND (profession, directeur) = (SELECT profession, D1.directeurFROM EMPLOYE E1, DEPARTEMENT D1, DEPARTEMENT D2WHERE E1.nom = ‘Furnon’AND E1.Id_departement = D1.Id_departementAND D1.Directeur = D2.Directeur);

8)            Donner les noms des employés du département ‘Commercial’ embauchés le même jour qu’un employé du département ‘Production’:

8a         SELECT  DISTINCT  E1.nom FROM  EMPLOYE  E1,  DEPARTEMENT  D1,  EMPLOYE  E2,  DEPARTEMENT  D2WHERE  E1.Id_departement  =  D1.Id_departementAND  E2.Id_departement  =  D2. Id_departement AND  D1.nom  =  ‘Commercial’AND  D2.nom  =  ‘Production’AND  E1.Date_embauche  =  E2.Date_embauche; 

 8b         SELECT  E.nomFROM  EMPLOYE  E,  DEPARTEMENT  DWHERE  E.Id_departement  =  D.Id_departementAND  D.nom  =  ‘Commercial’AND  E.Date_embauche  IN        (SELECT  E1.Date_embaucheFROM  EMPLOYE  E1, DEPARTEMENT  D1WHERE  E1. Id_departement  =  D1. Id_departementAND  D1.nom  =  ‘Production’);

8c          SELECT  E.nomFROM  EMPLOYE  E,  DEPARTEMENT  DWHERE  E.Id_departement  =  D.Id_departementAND  D.nom  =  ‘Commercial’AND  E.Date_embauche   EXISTS      (SELECT  E1.Date_embaucheFROM  EMPLOYE  E1, DEPARTEMENT  D1WHERE  E1. Id_departement  =  D1. Id_departementAND  D1.nom  =  ‘Production’);

9)            Donner les noms des employés ayant le salaire maximum de chaque département:

9a         SELECT  nomFROM  EMPLOYEWHERE  (Id_departement, salaire)  IN  (SELECT Id_departement, MAX(salaire)FROM EMPLOYEGROUP BY Id_departement); 

 9b           SELECT  nomFROM  EMPLOYE  EWHERE  salaire  =   (SELECT MAX(salaire)FROM EMPLOYE  E1WHERE  E1. Id_departement  =  E. Id_departement);

9c            SELECT  E1.nom,  E1.id_departement,  MAX(E1.salaire)

                FROM  EMPLOYE  E1,  EMPLOYE  E2

                WHERE  E1. Id_departement  =  E2. Id_departement

                GROUP BY  E1.nom, E1.id_departement;

6a       NON

6b    OUI

6c    NON

7a    NON

7b   OUI

8a    OUI

 8b     OUI

8c    NON

9a     OUI

9b     OUI

9c    NON

Exercice 3

11) Veuillez donner les commandes SQL permettant d’afficher la liste de tous les départements avec le nom, prénom, salaire et identifiant des employés qui y sont affectés. Attention: un département peut ne pas avoir d’employé(s).

12) Veuillez donner les commandes SQL permettant d’afficher la liste des emplois ayant le salaire moyen le plus bas; donner aussi leur salaire moyen.

13)          Veuillez donner les commandes SQL permettant d’afficher les employés du département de ‘Production’ dont le salaire est inférieur à 5000 $ et les employés du département ‘Commercial’ dont le salaire dépasse la moyenne des salaires.

11)

SELECT  D.nom,  E.Id_employe,  E.nom,  E.prenom,  E.salaire

FROM  EMPLOYE  E,  DEPARTEMENT D

WHERE  D.Id_departement  =  E.Id_departement(+)

ORDER BY D.nom, E.nom, E.prenom;

12) 

 SELECT profession, AVG(salaire) FROM EMPLOYEGROUP BY professionHAVING AVG(salaire) = (SELECT MIN(AVG(salaire)) FROM EMPLOYE

GROUP BY profession);

13)    

SELECT  E1.Id_employe, E1.Nom, E1.Salaire

FROM  EMPLOYE E1,  DEPARTEMENT D1

WHERE  D1.Id_departement = E1.Id_departement

AND  D1.Nom   =  ‘production’

AND  E1.Salaire < 5000

UNION

SELECT  E2.Id_employe, E2.Nom, E2.Salaire

FROM  EMPLOYE E2,  DEPARTEMENT D2

WHERE  D2.Id_departement = E2.Id_departement

AND  D2.Nom  =  ‘commercial’

AND E2.Salaire > (SELECT AVG(E3.Salaire) FROM  EMPLOYE E3)

ORDER BY 1, 2;

Exercice 4

Vous donnerez les commandes SQL de création des tables de la base de données, en incluant l’ensemble des contraintes énoncées qui peuvent être précisées dans la commande de création de table. Notez que la suppression d’un département doit entraîner la suppression de tous ses employés.

CREATE TABLE DEPARTEMENT (

Id_departement NUMERIC (3)    PRIMARY KEY,

Nom                     VARCHAR2 (30) CHECK (Nom IN (‘Commercial’, ‘Développement’, ‘Production’, ‘Ressources Humaines’)),

Directeur              NUMERIC (6)  NOT NULL UNIQUE,

Ville                      VARCHAR2 (20) NOT NULL

);

CREATE TABLE EMPLOYE (

Id_employe          NUMERIC (6)    PRIMARY KEY,

Nom                    VARCHAR2 (30) NOT NULL UNIQUE,

Prenom                VARCHAR2 (30) NOT NULL,

Profession            VARCHAR2 (30) NOT NULL CHECK (Profession IN (‘Exécutif’, ‘Gestionnaire’, ‘Ingénieur’, ‘Vendeur’, ‘Technicien’, ‘Stagiaire’)),

Date_embauche    DATE  NOT NULL,

Salaire                  INT   NOT NULL CHECK (Salaire >= 0),

Commission         INT   DEFAULT 0,

Id_departement     NUMERIC (3) CONSTRAINT fk_employe_departement REFERENCES DEPARTEMENT(Id_departement) ON DELETE CASCADE

);

BDD Voitures.txt

La base de données utilisée modélise une activité de location de voitures entre particuliers : voitures

Les informations conservées dans la base sont les informations concernant les voitures, les propriétaires de ces voitures, les clients et les locations effectuées.

Vous reprenez le schéma relationnel suivant :

VOITURE (immat, modele, marque, categorie, couleur, places, achatA, compteur, prixJ, #codeP )

PROPRIETAIRE (codeP, pseudo, email, ville, anneeI)

CLIENT (codeC, nom, prenom, age, permis, adresse, ville)

LOCATION (#CodeC, #immat, annee, mois,numLoc, km, duree, villeD, villeA, dateD, dateF)

Les clés primaires sont en gras/souligné ; les clés étrangères sont précédées par un#

Exercice 1

  1. De quelle marque est le véhicule de modèle T550 ?
  2. Quels sont les pseudos des propriétaires de Paris ?
  3. Dans quelles marques trouve-t-on des véhicules en catégorie `luxe’ ? Que faut-il utiliser pour éviter les doublons ?
  4. Quelles sont les immatriculations des cabriolets 4 places ?
  5. Combien y-a-t-il de catégories de voitures différentes ?
  6. Quelles sont les catégories de voitures différentes ?
  7. Quels sont les modèles de Peugeot proposés en location ?
  8. Liste (pseudo) des propriétaires ayant une adresse mail null 
  9. Quel est le kilométrage du véhicule `75AZ92′
  10. Quels clients (le nom seulement) habitent à `Nantes’ ?
  11. L’utilisation de DISTINCT était-elle une bonne option ? et pourquoi ?

select marque from voiture where modele = ‘T550’; — Ferrari
select pseudo from proprietaire where ville = ‘Paris’; — (4 rows returned)
select marque from voiture where categorie = ‘Luxe’; — (3 rows returned, 1 avec distinct)
select immat from voiture where categorie = ‘cabriolet’ and places = 4; — (1 row returned)
select count(distinct categorie) from voiture; — (7)
select distinct categorie from voiture; — (7 rows returned)
select modele from voiture where marque = ‘Peugeot’; — (5 rows returned)
select pseudo from proprietaire where email is null; — (2 rows returned)
select compteur from voiture where immat =’75AZ92′; — 17560
select nom from client where ville =’Nantes’;
Attention DISTINCT efface les homonymes

Exercice 2

  1. Liste des véhicules (immat) par année d’achat (du plus récent au plus ancien).
  2. Vérifiez en ajoutant à la liste l’année d’achat
  3. Liste des marques et modèles ordonnée par marque et modèle.
  4. Liste des voitures d’au moins 4 places (marque,modele,immat,places) par ordre de nombre de places décroissant.
  5. Liste des villes des propriétaires (sans doublons et dans l’ordre alphabétique).
  6. Liste des locations (liste des immatriculations des véhicules) classées par ordre décroissant de km effectués

select immat from voiture order by achatA; — (20 rows returned)
select immat, achatA from voiture order by achatA; — (20 rows returned)

select distinct marque, modele from voiture order by marque, modele; — (15 rows returned)
select marque, modele, immat, places from voiture where places >= 4 order by places DESC; — (10 rows returned)
select distinct ville from proprietaire order by ville ASC; — (6 rows returned)
select immat, km from location order by km DESC; — (133 rows returned)

Exercice 3

1) Liste des noms des clients à Lyon ?

idem sans les doublons.

Combien y-a-t-il de clients lyonnais ? ( count(*) , count(nom) , count(distinct nom))

notez les résultats et expliquez les différences entre les 3 résultats obtenus ?

2) Mêmes questions mais avec les prénoms

Combien y-a-t-il de couples différents (nom,prenom) dans la base de données (toutes villes confondues ?

— 1
select nom from client where ville =’Lyon’;
select distinct nom from client where ville =’Lyon’;
select count(nom) from client where ville =’Lyon’;
select count(*) from client where ville =’Lyon’;
select count(distinct nom) from client where ville =’Lyon’;
— 2
select nom, prenom from client where ville = ‘Lyon’;
select count(*) from client where ville =’Lyon’;
select count(prenom) from client where ville =’Lyon’;
select count(distinct nom) from client where ville =’Lyon’;
select nom, prenom from client where prenom is NULL and ville =’Lyon’ ;

Exercice 4

  1. Combien y-a-t-il de cabriolets rouges ou bleus ?
  2. Combien y-a-t-il de voitures dans la catégorie (familiale de moins de 50 000 km, ou utilitaire), de couleur noir ou blanc ?
  3. Liste (immat, marque, modèle) des modèles des marques Peugeot et Citroen affichés par marque et modèle
  4. Liste des clients de Paris, de plus de 50 ans par ordre alphabétique
  5. Liste (immat) des véhicules de couleur blanche sauf les cabriolets 
  6. Combien y-a-t-il de véhicules des catégories luxe et premium datant d’avant 2012 (2012 compris)
  7. Liste des locations (numéro de location, avec ville, année, mois) ayant débuté et terminé dans la même ville par ordre chronologique (la plus récente en premier)
  8. Liste des voitures datant d’entre 2010 et 2012 (2010 et 2012 inclus)
  9. Liste des voitures de marque Renault, Peugeot ou Citroen (utilisez IN).
  10. Combien de propriétaires n’habitent ni Paris, ni Lyon, ni Nantes ? 
  11. Liste des clients qui ont fait des locations sans permis

                1 Liste des clients (codeC) qui ont fait des locations.

                2 Liste des clients (codeC) qui ont un n° de permis 

                3 Liste des clients (codeC) qui n’ont pas de n° de permis 

                4 Liste des clients (codeC) qui ont fait une location sans n° de permis

12 Liste (nom et prénom) des clients habitant dans la même ville qu’un propriétaire 

13 Liste (immat) des voitures qui ont été loués (au moins une fois)

14 Liste (immat) des voitures qui n’ont jamais été loués

— 1
select count(*) from voiture where categorie= « cabriolet » and (couleur = ‘rouge’ or couleur = ‘bleue’);  — 1
— 2
select distinct categorie from voiture; — (7 rows returned)
select count(*) from voiture where ( (categorie =’familiale’ and compteur <= 50000) or (categorie =’utilitaire’) ) and (couleur = ‘noir’ or couleur =’blanc’); — 4
— 3
select distinct marque from voiture order by marque;– (6 rows returned)
select marque, modele, immat from voiture where marque = ‘Peugeot’ or ‘Citroen’ order by marque, modele; — (8 rows returned)
— 4
select nom, prenom, age from client where ville =’Paris’ and age > 50 order by nom; — (1 rows returned)
— 5
select immat from voiture where couleur = ‘blanc’ and categorie <> ‘cabriolet’; — (8 rows returned)
— 6
select count(*) from voiture where (categorie = ‘luxe’ or categorie = ‘premium’) and achatA <= 2012; — (6 rows returned)
— 7
select numLoc, villeD, annee, mois from location  where villeD = villeA order by annee DESC , mois DESC;– (67 rows returned)
— 8-9-10
select immat from voiture where achatA between 2010 and 2012; — (8 rows returned)
select immat from voiture where marque in (‘Renault’, ‘Peugeot’ , ‘Citroen’); — (16 rows returned)
select pseudo from proprietaire where ville not in (‘Paris’, ‘Lyon’, ‘Nantes’); — (5 rows returned)
— 11
select codeC from location;
select codeC from client where permis is not null;
select codeC from client where permis is null;
select distinct codeC , permis from client 
where codeC in ( select codeC from location)
and permis is null ; — (2 rows returned)
— autre version
 
select distinct codeC , permis from client 
where exists (select codeC from location)
and permis is null ; 
— autre version mais sans le n° de permis
select distinct codeC from location 
where codeC in ( select codeC from client where permis is null) ; 
 
— 12
select nom, prenom , ville from client 
where ville in (select ville from proprietaire); — (18 rows returned)
— 13
select distinct immat from location; — (20 rows returned)
 
— 14
select distinct immat from voiture 
where immat not in (select distinct immat 
from location); — (1 rows returned)
 
 select distinct immat from voiture where not exists (
select * from location 
where location.immat = voiture.immat); — (1 rows returned)

Exercice 5

  1. Quel est le pseudo du propriétaire de la voiture ’56AA46′ ?
  2. Combien de locations pour les peugeot 205 ?
  3. Liste (marque,modèle, immat, n°location) des voitures louées en 4/2015.
  4. Liste (marque,modèle, immat, n°location) des voitures louées l’année de leur achat. 
  5. Liste des immat des véhicules Peugeot
  6. Liste des immat des véhicules Peugeot et des infos des locations correspondantes par année, mois et immat 
  7. La liste des clients qui ont loué un véhicule Ferrari ?
  8. Quels sont les modèles de Peugeot ayant été loués.
— 1
select pseudo 
from proprietaire p, voiture v — Jules
where immat = ’56AA46′ 
and p.codeP = v.codeP ;
— 2
select count(*) 
from location l , voiture v — 11
where l.immat = v.immat
and v.marque= ‘Peugeot’ 
and v.modele = ‘205’;
— 3
select v.marque, v.modele , v.immat, l.numLoc– (6 rows returned)
from voiture v, location l 
where v.immat=l.immat
and l.annee = ‘2015’ 
and l.mois = ‘4’;
— 4
select v.marque, v.modele , v.immat, l.numLoc– (11 rows returned)
from voiture v, location l 
where v.immat = l.immat
and l.annee = v.achatA ;
— 5
select distinct immat — (5 rows returned)
from voiture 
where marque =’Peugeot’;
— 6
select l.* — (30 rows returned)
from voiture v , location l
where   v.immat = l.immat
and marque = ‘Peugeot’
order by l.annee, l.mois,l.immat;
— 7
Select c.nom, c.prenom, v.marque, v.modele — (13 rows returned)
from location l , voiture v , client c  
where c.codeC = l.codeC
and l.immat = v.immat
and v.marque = ‘Ferrari’;

Exercice 6

  1. Liste des marques ayant plus de 3 voitures disponibles à la location
  2. Liste des immat des véhicules ayant effectué plus de 800 km en location
  3. Liste des modèles des voitures ayant été louées au moins 3 fois

— 1: liste des marques ayant plus de 3 (>) voitures disponibles à la location
select marque — (2 rows returned)
from voiture
group by marque
having count(*) > 3;

— 2: liste des immat des voitures ayant effectuées plus de 800km en location
select immat — (15 rows returned)
from location
group by immat
having sum(km) > 800;

— 3: les modèles de voitures ayant été louées au moins 3 fois (avec nombre de locations)
— affichés par ordre alphabétique de modele
select v.modele, count(l.numloc) — (15 rows returned)
from location l JOIN voiture v ON l.immat = v.immat
group by v.modele
having count(l.numloc) >= 3
order by v.modele ;

Exercice 7

  1. Kms effectués en location par Alain Delon, voiture (immat) par voiture
  2. Marques et moyennes des km effectués en location (par marques)
  3. Quelle est la voiture la plus ancienne ? (avec MIN)
  4. Quelle voiture a plus de km au compteur que toutes les Peugeot ? (avec ALL)
  5. Quelle/s voiture/s a/ont un prix de journée supérieur à une Ferrarri ?
  6. Afficher la liste des voitures ayant un km compteur supérieur à la moyenne générale des km compteur.
  7. Liste des voitures ayant un km compteur supérieur à la moyenne des km compteur de leur modèle ?
— 1: KMs effectué en location par Alain Delon voiture (immat) par voiture
select immat, sum(km) — (4 rows returned)
from client c JOIN location l on c.codeC = l.codeC 
where nom = ‘Delon’
group by immat; 
 
— 2a: marques et moyennes de Km effectués en location par marque
select v.marque, avg(l.km) — (6 rows returned)
from location l, voiture v 
where l.immat = v.immat 
group by v.marque;
 
— 2b: idem par ordre décroissant des moyennes
— utilisation du AS
select v.marque, avg(l.km) as moyenne — (6 rows returned)
from location l, voiture v 
where l.immat = v.immat 
group by v.marque 
order by moyenne DESC;
 
— 3: quelle est la voiture la plus ancienne
select immat — (3 rows returned)
from voiture 
where achatA = (select min(achatA) 
from voiture) ;
 
— 4: Quelle/s voiture/s a/ont plus de km au compteur que toutes les Peugeot (ALL)
select immat — (7 rows returned)
from voiture
where compteur >= ALL (select compteur
from voiture
where marque = ‘Peugeot’); 
 
— 5: Quelles voitures ont un prix de journée supérieur à une Ferrari
select immat — (20 rows returned)
from voiture
where prixJ >= ANY (select prixJ
from voiture 
where marque =’Ferrari’);
 
— 6: liste des voitures ayant un km compteur supérieur à la moyenne générale des km compteur
select immat– (6 rows returned)
from voiture
where compteur > (select avg(compteur) 
from voiture) ;
 
— 7: liste des voitures ayant un km compteur supérieur à la moyenne des km compteur de leur modèle
select v1.immat — (5 rows returned)
from voiture v1
where v1.compteur > (select avg(v2.compteur) 
from voiture v2 
where v2.modele = v1.modele) ;

Exercice 8

  1. Liste des modèles de voitures louées au moins 3 fois (avec nombre de locations) ?
  2. Quelle est la voiture ayant parcouru le plus de km en location ?
  3. Quel(s) client(s) a effectué le plus de locations ?
  4. Quel client a loué le plus de véhicules différents ?
  5. Quel est le total des km de location effectués pour le propriétaire P75 ?
  6. Quel est le total des km de location effectués pour le propriétaire P75 voiture par voiture ?

                idem mais pour en prenant en compte que les locations de plus de 300 Km. 

                idem mais pour les véhicules ayant effectué un total de plus de 300 Km en location. 

Pour vérification, liste des locations effectuées par les véhicules de P75. 

— 1: les modèles de voitures ayant été louées au moins 3 fois (avec nombre de locations)
— affichés par ordre alphabétique de modele
select v.modele , count(l.numloc) — (15 rows returned)
from location l , voiture v
where l.immat = v.immat
group by v.modele
having count(l.numloc) >=  3
order by v.modele ; 
 
— 2: quelle est la voiture ayant parcouru le plus de km en location
select immat , sum(km) — 11RS75, 3697km
from location 
group by immat 
having sum(km) >= ALL( select sum(km) 
from location 
group by immat );
 
— 3: le client ayant effectué le plus de location
select C.nom, C.prenom — (1 rows returned)
from client c JOIN location l ON c.codeC = l.codeC
group by C.nom, C.prenom  
having count(*)
>=  ALL (select count(*) 
from location lo 
group by lo.codeC) ;
 
— 4: le client ayant loué le plus de véhicules différents
select C.nom, C.prenom — (2 rows returned)
from client c, location l 
where c.codeC = l.codeC
group by C.nom, C.prenom
having count(distinct immat)  
>=  ALL (select count(distinct immat) 
from location l2 
group by l2.codeC) ;
 
— ————————
 
— 5: total des km de location effectués pour les voitures du propriétaire P75
select sum(l.km) — 7192
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′;
 
— 6a: total des km de location effectué pour le propriétaire P75 voiture par voiture
select l.immat, sum(l.km) — (4 rows returned)  
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
group by l.immat;
 
— 6b: idem à (b) mais en ne prenant en compte uniquement les locations de plus de 300 km
select l.immat, sum(l.km)– (3 rows returned) 
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
and l.km > 300
group by l.immat;
 
— 6c idem à(b) mais uniquement pour les véhicules ayant effectué un total plus de 300 km en location
select l.immat, sum(l.km) — (4 rows returned) 
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
group by l.immat
having sum(l.km) > 300 ;
 
— 6d: pour vérification, liste de locations effectuées par le véhicules de P75 
select l.immat, l.km  — (25 rows returned)
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
order by l.immat, l.km DESC ;

Exercice 9

 La requête visée est : quels clients (les noms) utilisent le même numéro de permis ?

Pour vous guider une série de requêtes progressives.

  1. Pour chaque n° de permis, afficher le nombre de fois où il est utilisé.
  2. Liste des numéros de permis qui ont été utilisés plus d’une fois ?
  3. Liste des noms des clients ayant un n° de permis utilisé plus d’une fois. 
  4. Liste des noms des clients dont le n° de permis est égal au n° de permis d’un autre client (avec IN)
  5. Autre écriture pour (4) en utilisant les égalités de jointure explicites (SQL1). 
  6. S’il reste des doublons, comment les supprimer ?
— 1: nombre de fois où un n° de permis a été utilisé
select c1.permis, count(*)  — (8 rows returned)
from client c1 
group by c1.permis
order by c1.permis ;
 
— 2: nombre de permis utilisés plus d’une fois 
select c1.permis  — (5 rows returned)
from client c1 
group by c1.permis
having count(*)> 1
order by c1.permis ;
 
— 3: clients ayant un n° de permis (afficher aussi le n° de permis) utilisé plus d’une fois
select c2.nom  — (14 rows returned)
from client c2
where c2.permis IN ( select c1.permis
from client c1 
group by c1.permis
having count(*)> 1 ) 
order by c2.nom ;
 
— 4: liste des noms des clients utilisant le numéro de permis (afficher le n° de permis) d’un autre client 
select c1.nom, c1.permis — (14 rows returned)
from client c1
where c1.permis IN ( select c2.permis
from client c2 
where  c2.permis = c1.permis
and c2.nom != c1.nom)
order by c1.nom ;
 
— 5: autre écriture avec les égalités de jointure explicites 
— (l’autojointure est maintenant mieux visible)
select c1.nom, c2.nom , c1.permis — (38 rows returned)
from client c1, client c2
where c2.permis = c1.permis and c2.nom != c1.nom 
order by c1.nom ;
 
— 6: autre écriture avec les égalités de jointure explicites 
— (l’autojointure est maintenant plus visible)
select c1.nom, c2.nom , c1.permis — (19 rows returned)
from client c1, client c2
where c2.permis = c1.permis and c2.nom > c1.nom 
order by c1.nom ;

Exercice 10

 La requête visée est : quel clients (les noms) ont loué toutes les voitures ?

(a) Liste des voitures qui n’ont pas été louées ; à écrire en utilisant NOT EXISTS. (reformulation : les voitures pour lesquelles il n’existe pas de location avec ce n° d’immat).

(b) Liste des noms de clients pour lesquels il existe des véhicules qu’il (le client) n’ait pas loué.

(c) Liste des noms des clients qui ont loué tous les véhicules du parc.

 (reformulation : liste des noms de clients pour lesquels il n’existe pas de véhicule qu’ils n’aient pas loué.

— La requête visée est : quel clients (les noms) ont loué toutes les voitures ?
— (a) Liste des voitures qui n’ont pas été louées ; à écrire en utilisant NOT EXISTS.

SELECT voiture.immat — 1 row
from voiture
where not exists (select * from location where location.immat=voiture.immat);

— (b) Liste des noms de clients pour lesquels il existe des véhicules qu’il (le client) n’ait pas loué.
SELECT DISTINCT nom, prenom
from client, voiture
where not exists (select codeC from location where location.immat=voiture.immat and client.codeC=location.codeC);

— (c) Liste des noms des clients qui ont loué tous les véhicules du parc.
— (reformulation : liste des noms de clients pour lesquels il n’existe pas de véhicule qu’ils n’aient pasloué.

select client.nom
from location, client
where location.codeC= client.codeC
group by client.codeC
having count(distinct immat)>= (select (count(distinct immat)) from voiture);

BDD Vins.txt

Nous souhaitons utiliser le schéma relationnel suivant  pour gérer une cave à vin. La base de données représentant cette cave possède le schéma relationnel suivant: vins

VIN (numV, cru, annee, degre)

PRODUCTEUR ( numP, nom, prenom, region)

RECOLTE ( nprod#, nvin#, quantite)

Et les csv suivants : vin, recolte, producteur.

A l’aide de l’instruction suivante, vous traduisez chaque ligne du fichier .csv en tuple dans la table correspondante

LOAD DATA INFILE ’chemin absolu/XXX.csv’ INTO TABLE XXX FIELDS TERMINATED BY ’;’ LINES TERMINATED BY ‘\r\n’;

 

LOAD DATA INFILE ‘C :/XXX.csv’ INTO TABLE XXX FIELDS TERMINATED BY ’;’ LINES TERMINATED BY ‘\r\n’; si le fichier XXX.csv est sous la racine

Un vin est caractérisé par un numéro entier, un cru, une année de production et un degré. L’ensemble des vins est représenté par la relation VIN. La clé de la relation VIN est l’attribut numV.

Un producteur est caractérisé par un numéro entier, un nom, un prénom et une région. L’ensemble des producteurs est représenté par la relation PRODUCTEUR. La clé de la relation PRODUCTEUR est l’attribut numP. Un producteur produit un ou plusieurs vins. Réciproquement, un vin est produit par un ou plusieurs producteurs (éventuellement par aucun !).

L’ensemble des productions est représenté par la relation RECOLTE. Un tuple de la relation RECOLTE représente une production particulière d’un vin de numéro nvin par un producteur de numéro nprod en une certaine quantité. La clé de la relation RECOLTE est le groupe d’attributs (nvin, nprod).

Exercice 1

1- Créer la base de données à partir des fichiers fournis.

Par défaut les fichiers ne sont lisibles qu’à partir du répertoire précisé dans la variable secure_file_priv.

SHOW VARIABLES LIKE « secure_file_priv »;

Déposer vos fichiers dans ce répertoire et les charger pour peupler vos tables.

LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/producteur.csv’

    INTO TABLE producteur

    FIELDS

        TERMINATED BY ‘;’

    LINES

        STARTING BY  »  

        TERMINATED BY ‘\r\n’;

2- Afficher les informations contenues dans la relation VIN.

3- Donner la liste des producteurs qui n’ont pas de prénom.

4- Donner la liste des régions distinctes de production des vins.

5- Donner la liste des vins de 1980.

6- Donner la liste des noms et des prénoms des producteurs de vins n’appartenant pas aux régions suivantes : Corse, Beaujolais, Bourgogne et Rhône.

7- Quelle est la liste des crus récoltés en 1979? Afficher le cru, le numéro du producteur et la quantité produite.

8- Quels sont les noms des producteurs du cru Etoile, leurs régions et la quantité de vins récoltés ?

9- Quel est le nombre de récoltes ?

10-Combien y-a-t-il de producteurs de vin dans la région Savoie et Jura ?

11-Combien y-a-t-il de producteurs de vin ayant récoltés au moins un vin dans la région Savoie et Jura ?

12-Quelles sont les quantités de vin produites par région. Donner la liste ordonnée par quantité décroissante.

13- Quelle est la quantité de vin produite de degré >12 par numéro de vin ?

14- Quel est le cru ou les crus au plus fort degré ?

15- Donner la liste ordonnée des crus.

16- Donner la liste ordonnée des crus récoltés.

17- Quel est donc le cru non récolté ?

18- Donner la liste ordonnée des crus et la quantité par cru ?

19- Quel est le degré moyen des crus ?

20- Quel sont les crus (ordonnés par degré et année) de degré supérieur au degré moyen des crus ?

select * from vin; # 101 rows
select * from producteur; # 200 rows
select * from recolte; # 200 rows

#Question 2 – 7
select distinct nom from producteur where prenom is null; # 0 rows
select distinct nom from producteur where prenom =  » or prenom is null; # 12 rows

select distinct region from producteur; # 11 rows
select degre,cru from vin where annee=1980 order by degre desc; # 18 rows
select nom,prenom,region from producteur where region not in (‘corse’ , ‘beaujolais’ , ‘bourgogne’ , ‘rhone’) order by nom asc; # 164 rows
select nprod, cru,quantité from vin v,recolte r where annee=1979 and v.numV=r.nprod order by nprod; # 5 rows

#Question 8- 13
select cru,nom,region,quantité FROM VIN v,PRODUCTEUR p,RECOLTE r WHERE cru=’Etoile’ AND p.numP=v.numV AND p.numP=r.nprod; #1 row
select COUNT(*) FROM RECOLTE ; # –> 665
select COUNT(*) FROM PRODUCTEUR WHERE region in (‘Savoie’,’Jura’); # –> 57
select COUNT(*), quantité FROM PRODUCTEUR p,RECOLTE r WHERE region in(‘Savoie’,’Jura’) AND p.numP=r.nprod AND quantité>0 GROUP BY quantité; # 22 rows
select SUM(quantité) FROM RECOLTE R, PRODUCTEUR P WHERE R.nprod=P.numP GROUP BY region ORDER BY quantité DESC; # 10 rows
select SUM(quantité) from recolte r, vin v where v.numV=r.nvin and v.degre>12 group by nvin; # 14 rows

# Question 14-20
select cru FROM VIN WHERE degre in (select MAX(degre) FROM VIN); # –> Saint Amour
select cru FROM VIN ORDER BY cru ASC; #101 rows
select distinct cru from vin v ,recolte r where v.numV=r.nvin order by cru; # 61 rows
select cru from vin v where v.cru not in (select cru from vin v ,recolte r where v.numV=r.nvin); # –> Saint Veran
select cru, SUM(quantité) from vin v ,recolte r where v.numV=r.nvin group by cru order by cru; # 61 rows
select avg(degre) from vin v ; # –> 11.35
select cru,degre,annee from vin where degre > (select avg(degre) from vin v) order by cru,degre,annee; # 48 rows

Exercice 2

1 – Donnez tous les vins dont le cru commence par la lettre ‘S’

2 – Donnez tous les vins distincts dont la date de production est entre 1980 et 1981

3 – Sélectionnez toutes les productions du producteur numéro 606

4 – Donnez par numéro de producteur, la quantité totale de vin produite. Vérifiez avec la question 2

5 – Donnez par numéro de producteur, la quantité totale de vin produite si celle-ci est supérieure à 400 et le tout ordonnancé par ordre croissant des quantités calculées en utilisant un alias sur la somme

6 – Recherchez le cru Pommard le plus ancien. Affiche le avec le commentaire :  « celui que je préfère »

7 –  Quels sont les numéros des producteurs ayant récolté le plus de vin différents ?

8 – Ajoutez une colonne à la table Producteur correspondant à sa date de naissance,

Modifier les producteurs suivants existants dans la base en leur assignant leur date de naissance

datenaissance = ‘1960-12-11′  nom=’Six’;

datenaissance = ‘1962-12-11′  nom=’Lasnier’;

datenaissance = ‘1970-12-11′  nom=’Moniot’;

datenaissance = ‘1970-12-11′  nom=’Boxler’;

datenaissance = ‘1980-12-11′  nom=’Jayer’;

datenaissance = ‘1961-05-10′  nom=’Grivot’;

datenaissance = ‘1980-12-11′  nom=’Tortochot’;

9 – Sélectionnez les producteurs pour lesquels la date de naissance n’est pas nulle

10 – Calculez l’âge de Grivot

11 – Calculez le nombre de producteurs par âge

1- select cru from vin where cru like ‘s%’;

2- select distinct cru, annee from vin where annee between 1980 and 1981;

3- select * from recolte where nprod = 606;

4- select nprod, sum(qte) from recolte group by
nprod ;

5- select nprod, sum(qte) as somme from recolte group by
nprod having sum(qte) > 400 order by somme;

6- select cru, annee from vin where cru=’Pommard’;

select concat (cru, ‘ celui que je préfère ‘), annee
from vin where cru = ‘Pommard’ and annee >= (select max(annee) from vin where cru=’Pommard’);

7- select nprod, nom from recolte, producteur where recolte.nprod = producteur.num
group by nprod
having count(distinct nvin) >= All
(select count(distinct nvin) as nbvindifferent from recolte group by nprod
);


select nprod, nom from recolte, producteur where recolte.nprod = producteur.num
group by nprod
having count(distinct nvin) >=
(select max(nbvindifferent) from
(select count(distinct nvin) as nbvindifferent from recolte group by nprod) P);


select max(nbvindifferent) from
(select count(distinct nvin) as nbvindifferent from recolte group by nprod) P;

select nprod, count(distinct nvin) as nbvindifferent
from recolte group by nprod order by nbvindifferent desc;

select * from recolte where nprod=355;


8- alter table producteur add column naissance datetime;

9- select * from producteur where num < 5;
update producteur set naissance = ‘1990/10/10’ where num = 1;

10- SELECT nom , Year(current_date()) – YEAR(producteur.naissance) as AGE FROM producteur
where naissance is not null;

11- select count(*) as nombre , Year(current_date()) – year(producteur.naissance)as age
from producteur where naissance is not null group by age;

Exercice 3

Reprendre la base de données VINs et répondre aux questions suivantes :

1 – Quels sont les noms des producteurs du vin N° 5 ;

2 – Quels sont les producteurs qui ont produit le vin ‘Pommard’ ?

3 – Quels sont les crus récoltés ?

4 – Quelle est la liste des crus récoltés en 1979? Afficher le cru, le numéro du producteur et la quantité produite.

5- Quels sont les noms des producteurs du cru Etoile, leurs régions et la quantité de

vins récoltés?

6 – Quel est le nombre producteurs de crus récoltés en Savoie et dans le Jura ?

7 – Quel est donc le cru non récolté?

8 – Donner la liste ordonnée des crus et la quantité par cru?

9 – Donnez les noms des producteurs qui ont récolté le plus de vins différents

10 – Donnez la liste des noms des producteurs qui n’ont pas récolté

11 – Quels sont les noms des personnes portant le même prénom

1- select nom from producteur , recolte where producteur.num = recolte.nprod and nvin=5;
 
2- select nom, prenom from producteur, recolte, vin where producteur.num = recolte.nprod and recolte.nvin=vin.num and cru = ‘Pommard’;
 
3- select distinct cru from recolte, vin where recolte.nvin= vin.num;
 
4- select 
    p.num, p.nom, cru, qte
from
    vin v,
    recolte r,
    producteur p
where
    date = 1979 and v.num = r.nvin
        and p.num = r.nprod
order by nprod;
 
5- select 
    nom, cru, region, qte
from
    recolte r,
    producteur p,
    vin v
where
    p.num = r.nprod and v.num = r.nvin
        and v.cru = ‘Etoile’;
 
6- select 
    count(*)
from
    producteur p,
    recolte r
where
    region in (‘Savoie’ , ‘Jura’)
        and p.num = r.nprod;
 
select 
    count(*)
from
    producteur p,
    recolte r
where
     p.num = r.nprod and (p.region=’Jura’ or p.region=’Savoie’);
 
7- select 
    cru
from
    vin
where
    vin.num not in (select 
            recolte.nvin
        from
            recolte);
 
8- select 
     cru, sum(qte) 
from
    recolte,
    vin
where
   vin.num = recolte.nvin
group by nvin;
 
 
select 
    cru, sum(qte)
from
    vin v
        LEFT OUTER JOIN
    recolte r ON v.num = r.nvin
group by cru
order by cru;
 
/*requete 9 et 10 voir plus haut*/
 
11- select P1.nom, P2.nom from Producteur P1, Producteur P2
where P1.prenom = P2.prenom;
Partager