17 Exercices corrigés SQL requêtes - Complex systems and AI

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

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.

Solution

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 :

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

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;

Solution

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;

Solution

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.

Solution

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.

Solution

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

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

Solution

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

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

Solution

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 ?

Solution

— 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

Combien y-a-t-il de cabriolets rouges ou bleus ?
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 ?
Liste (immat, marque, modèle) des modèles des marques Peugeot et Citroen affichés par marque et modèle
Liste des clients de Paris, de plus de 50 ans par ordre alphabétique
Liste (immat) des véhicules de couleur blanche sauf les cabriolets
Combien y-a-t-il de véhicules des catégories luxe et premium datant d’avant 2012 (2012 compris)
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)
Liste des voitures datant d’entre 2010 et 2012 (2010 et 2012 inclus)
Liste des voitures de marque Renault, Peugeot ou Citroen (utilisez IN).
Combien de propriétaires n’habitent ni Paris, ni Lyon, ni Nantes ?
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

Solution

— 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

Quel est le pseudo du propriétaire de la voiture ’56AA46′ ?
Combien de locations pour les peugeot 205 ?
Liste (marque,modèle, immat, n°location) des voitures louées en 4/2015.
Liste (marque,modèle, immat, n°location) des voitures louées l’année de leur achat.
Liste des immat des véhicules Peugeot
Liste des immat des véhicules Peugeot et des infos des locations correspondantes par année, mois et immat
La liste des clients qui ont loué un véhicule Ferrari ?
Quels sont les modèles de Peugeot ayant été loués.

Solution

— 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

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

Solution

— 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

Kms effectués en location par Alain Delon, voiture (immat) par voiture
Marques et moyennes des km effectués en location (par marques)
Quelle est la voiture la plus ancienne ? (avec MIN)
Quelle voiture a plus de km au compteur que toutes les Peugeot ? (avec ALL)
Quelle/s voiture/s a/ont un prix de journée supérieur à une Ferrarri ?
Afficher la liste des voitures ayant un km compteur supérieur à la moyenne générale des km compteur.
Liste des voitures ayant un km compteur supérieur à la moyenne des km compteur de leur modèle ?

Solution

— 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

Liste des modèles de voitures louées au moins 3 fois (avec nombre de locations) ?
Quelle est la voiture ayant parcouru le plus de km en location ?
Quel(s) client(s) a effectué le plus de locations ?
Quel client a loué le plus de véhicules différents ?
Quel est le total des km de location effectués pour le propriétaire P75 ?
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.

Solution

— 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.

Pour chaque n° de permis, afficher le nombre de fois où il est utilisé.
Liste des numéros de permis qui ont été utilisés plus d’une fois ?
Liste des noms des clients ayant un n° de permis utilisé plus d’une fois.
Liste des noms des clients dont le n° de permis est égal au n° de permis d’un autre client (avec IN)
Autre écriture pour (4) en utilisant les égalités de jointure explicites (SQL1).
S’il reste des doublons, comment les supprimer ?

Solution

— 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é.

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

Solution

— 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 ?

Solution

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

Solution

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

Solution

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;