Propositions de corrections des travaux pratiques SQL Concession

Révision des notions abordées

Pour interroger une base de données relationnelle, vous avez découvert le langage SQL.

Vous allez vous entraîner en effectuant différentes requêtes sur une BD existante.

Vous utiliserez le SGBDR MySQL

Le schéma et les données sont détaillés ci-dessous.

Instructions qui seront utilisées

• SELECT
• DISTINCT
• WHERE
• AND, OR, IN
• INNER JOIN
• LEFT JOIN
• AS
• UNION
• ORDER BY
• GROUP BY
• SELECT imbriqué
• INSERT
• UPDATE
• DELETE

Base de données

Notes : Vous trouverez ici, une série d’exercices à résoudre (avec des requêtes simples puis plus complexes).

• Utilisez un client MySql comme DataGrip pour écrire et exécuter vos requêtes ou simplement votre interface de MySQL
• Pour chaque exercice, vous avez une solution proposée mais vous pouvez en écrire d’autres.
• Ces exercices de révisions font le tour de ce qu’il faut connaître en SQL.

• Si dans votre vie professionnelle, vous devez écrire des requêtes SQL, regardez s’il n’existe pas un cas similaire ici.

• sql.sh pour revoir les concepts SQL.
• w3schools pour s’entraîner

SELECT

• sql.sh
• w3schools

1. Afficher toutes les données de la table des vendeurs uniquement

select * from vendeur;

2. Afficher le nom et le prénom de tous les vendeurs

select nom, prenom from vendeur;

SELECT DISTINCT

• sql.sh
• w3schools

3. Afficher le prénom de tous les vendeurs

select prenom from vendeur

4. Afficher le prénom de tous les vendeurs sans doublon (une seule ligne par prénom)

select distinct prenom from vendeur

Les jointures

Il existe plusieurs sortes de jointures résumées par ce schéma que vous avez déjà aperçu dans le cours

Vous devez savoir écrire les jointures INNER JOIN et LEFT JOIN.

Jointure simple (INNER JOIN par défaut)

• sql.sh
• w3schools

5. Afficher les données des vendeurs et de sa ville rattachée

select * from vendeur join ville on ville.id = vendeur.id_ville

6. Afficher le nom, prénom et la ville des vendeurs

select vendeur.nom, vendeur.prenom, ville.nom 
from vendeur 
inner join ville on ville.id = vendeur.id_ville 

Jointure optionnelle gauche (LEFT JOIN)

• sql.sh
• w3schools

6bis. Afficher le nom, prénom et la ville des vendeurs.

select vendeur.nom, vendeur.prenom, ville.nom
from vendeur 
left join ville on ville.id = vendeur.id_ville

Alias

• sql.sh
• w3schools

7. Affiche le nom, prénom et la ville des vendeurs en affichant en entête de colonne : nom, prénom, ville

select vendeur.nom as 'nom', vendeur.prenom as 'prénom', ville.nom as 'ville' 
from vendeur 
join ville on ville.id = vendeur.id_ville

8. Afficher le nom, prénom et nom du responsable des vendeurs

select vendeur.nom, vendeur.prenom, responsable.nom as 'responsable'
from vendeur 
left join vendeur as responsable on responsable.id = vendeur.id_responsable

SELECT avec WHERE

Support

• sql.sh
• w3schools

9. Liste des véhicules rouges

select *
from vehicule
left join couleur on couleur.id = vehicule.id_couleur
where couleur.nom = "rouge"

10. Liste des véhicules rouges et des véhicules verts (plusieurs solutions)

select *
from vehicule
left join couleur on couleur.id = vehicule.id_couleur
where (couleur.nom = "rouge" or couleur.nom = "vert")

select *
from vehicule
left join couleur on couleur.id = vehicule.id_couleur
where (couleur.nom = "rouge")
union
select *
from vehicule
left join couleur on couleur.id = vehicule.id_couleur
where (couleur.nom = "vert")

select *
from vehicule
left join couleur on couleur.id = vehicule.id_couleur
where couleur.nom in ("rouge", "vert")

11. Liste des véhicules en stock

select vehicule.*
from vehicule
where vehicule.id_stock is not null

Dans cette solution ci-dessous, il n’y a pas de clause where, la selection se fait grâce au inner.

select vehicule.*, stock.entrepot
from vehicule
inner join stock on stock.id = vehicule.id_stock

12. Liste des véhicules en stock, ayant une couleur contenant le lettre u

select *
from vehicule
inner join stock on stock.id = vehicule.id_stock
left join couleur on couleur.id = vehicule.id_couleur
where couleur.nom like "%u%"

13. Liste des véhicules en stock à Nantes

select *
from vehicule
inner join stock on stock.id = vehicule.id_stock
inner join ville on ville.id = stock.id_ville
where ville.nom = "nantes"

SELECT avec la clef unique

Historique

Depuis quelques années, les développeurs utilisent un id généré automatiquement comme clef primaire.

C’est un id technique, très pratique pour la propagation des clefs étrangères.

Il y a quelques années, la clef primaire d’une table était composée de plusieurs champs (mais pas n’importe lesquels). Il s’agissait des champs qui rendent un enregistrement unique.

En effet, comme nous le verrons dans le cours sur la modélisation d’une base de données (UML et MCD), en 3ème forme normale, elle ne contient pas de données en double. Cette pratique est toujours d’actualité !

Inconvénient de l’id technique auto-générée

L’id, en tant que clef primaire, a un terrible inconvénient : le doublon.

L’id est technique, donc l’utilisateur ne le connaît pas.

Comment empêcher un utilisateur de créer des doublons dans une base ?

Solution : créer une nouvelle clef qui :

• est unique
• est composée des différents champs respectant cette unicité.

Note : Si cette clef ne contient qu’un seul champ, il suffit d’ajouter la contrainte UNIQUE sur le champ (ou colonne).

MySql génère automatiquement une clef unique.

Par exemple, le vendeur est identifié par son nom et son prénom.

Ici par convention, la clef unique est nommée uk_UNIQUE. A vous de choisir votre convention de nommage.

Malheureusement, cette clef n’est pas obligatoire. Si vous l’oubliez, vous aurez des enregistrements en double dans la base.

Lire un enregistrement sur sa clef unique

Pour trouver un enregistrement, il suffit de lire la table en renseignant après le WHERE les champs de cette clef.

14. Rechercher le carburant essence

select * from carburant where carburant.nom = 'essence'

15 Écrire une requête recherchant un unique vendeur

select * from vendeur where vendeur.nom = 'Cadoret' and vendeur.prenom = 'Marie'

16. Écrire une requête recherchant une unique motorisation

select * from motorisation
inner join carburant on carburant.id = motorisation.id_carburant
where motorisation.cylindree = 1600
and   motorisation.chevaux = 7
and   carburant.nom = 'essence'

17. Écrire une requête recherchant un unique véhicule

select *
	from vehicule
    left join modele on modele.id = vehicule.id_modele
    left join couleur on couleur.id = vehicule.id_couleur
    left join motorisation on motorisation.id = vehicule.id_motorisation
    left join carburant on carburant.id = motorisation.id_carburant
	where modele.nom = 'Kangoo'
    and   modele.marque = 'RENAULT'
    and   modele.annee = 2017
    and   couleur.nom = 'blanc'
    and   motorisation.cylindree = 1200
	and   motorisation.chevaux = 6
	and   carburant.nom = 'essence'
    and   vehicule.occasion = true
    and   vehicule.date_entree = '2018-03-18 12:52:28'

UNION

• sql.sh
• w3schools

18. Liste des modèles et de leur différent prix pour les véhicules essences et des véhicules rouges.

Indication : le SELECT des véhicules rouges est différent du SELECT des véhicules essence.

select distinct modele.marque as 'marque',
       modele.nom as 'modele',
       modele.annee as 'annee',
       vehicule.prix 
	from vehicule
    left join modele on modele.id = vehicule.id_modele
	left join couleur on couleur.id = vehicule.id_couleur
    where couleur.nom = "rouge"
union    
select distinct modele.marque as 'marque',
       modele.nom as 'modele',
       modele.annee as 'annee',
       vehicule.prix
	from vehicule
    left join modele on modele.id = vehicule.id_modele
    left join motorisation on motorisation.id = vehicule.id_motorisation
    left join carburant on carburant.id = motorisation.id_carburant
    where carburant.nom = "essence"

Entraînement avec SELECT

19 liste de tous les véhicules avec toutes leurs caractéristiques

select *
	from vehicule
	left join couleur on couleur.id = vehicule.id_couleur
    left join stock on stock.id = vehicule.id_stock
    left join ville on ville.id = stock.id_ville
    left join modele on modele.id = vehicule.id_modele
    left join motorisation on motorisation.id = vehicule.id_motorisation
    left join carburant on carburant.id = motorisation.id_carburant;

20. liste de tous les véhicules avec toutes leurs caractéristiques, sans afficher les id

select vehicule.prix, vehicule.occasion, vehicule.date_entree,
       couleur.nom,
       stock.entrepot,
       ville.nom,
       modele.marque,
       modele.nom,
       modele.annee,
       motorisation.Chevaux,
       motorisation.cylindree,
       carburant.nom
from vehicule
left join couleur on couleur.id = vehicule.id_couleur
left join stock on stock.id = vehicule.id_stock
left join ville on ville.id = stock.id_ville
left join modele on modele.id = vehicule.id_modele
left join motorisation on motorisation.id = vehicule.id_motorisation
left join carburant on carburant.id = motorisation.id_carburant
order by modele.marque,
			 modele.nom,
             modele.annee desc,
             couleur.nom,
             carburant.nom,
             vehicule.prix desc;

21. liste de tous les véhicules avec toutes leurs caractéristiques, sans afficher les id, et en utilisant les alias pour les colonnes

select vehicule.prix, vehicule.occasion, vehicule.date_entree,
       couleur.nom as 'couleur',
       stock.entrepot as 'stock',
       ville.nom as 'ville',
       modele.marque as 'marque',
       modele.nom as 'modele',
       modele.annee as 'annee',
       motorisation.Chevaux as 'chevaux',
       motorisation.cylindree as 'cylindree',
       carburant.nom as 'carburant'
from vehicule
left join couleur on couleur.id = vehicule.id_couleur
left join stock on stock.id = vehicule.id_stock
left join ville on ville.id = stock.id_ville
left join modele on modele.id = vehicule.id_modele
left join motorisation on motorisation.id = vehicule.id_motorisation
left join carburant on carburant.id = motorisation.id_carburant
order by prix desc,
             carburant,
			 modele.marque,
			 modele,
             annee desc,
             couleur;

22. Liste des véhicules vendus avec leurs caractéristiques et le nom du vendeur

select vendeur.nom, vendeur.prenom,
	   vehicule.prix, vehicule.occasion, vehicule.date_entree,
       couleur.nom as 'couleur',
       stock.entrepot as 'stock',
       ville.nom as 'ville',
       modele.marque as 'marque',
       modele.nom as 'modele',
       modele.annee as 'annee',
       motorisation.Chevaux as 'chevaux',
       motorisation.cylindree as 'cylindree',
       carburant.nom as 'carburant'
from vehicule
inner join vehicule_vendu on vehicule_vendu.id_vehicule = vehicule.id
inner join vente on vente.id = vehicule_vendu.id_vente
left join vendeur on vendeur.id = vente.id_vendeur
left join couleur on couleur.id = vehicule.id_couleur
left join stock on stock.id = vehicule.id_stock
left join ville on ville.id = stock.id_ville
left join modele on modele.id = vehicule.id_modele
left join motorisation on motorisation.id = vehicule.id_motorisation
left join carburant on carburant.id = motorisation.id_carburant
order by prix desc,
             carburant,
			 modele.marque,
			 modele,
             annee desc,
             couleur;

23. Liste des véhicules en vente (et donc non vendus)

select vehicule.prix, vehicule.occasion, vehicule.date_entree,
       couleur.nom as 'couleur',
       stock.entrepot as 'stock',
       ville.nom as 'ville',
       modele.marque as 'marque',
       modele.nom as 'modele',
       modele.annee as 'annee',
       motorisation.Chevaux as 'chevaux',
       motorisation.cylindree as 'cylindree',
       carburant.nom as 'carburant'
from vehicule
left join vehicule_vendu on vehicule_vendu.id_vehicule = vehicule.id
left join couleur on couleur.id = vehicule.id_couleur
left join stock on stock.id = vehicule.id_stock
left join ville on ville.id = stock.id_ville
left join modele on modele.id = vehicule.id_modele
left join motorisation on motorisation.id = vehicule.id_motorisation
left join carburant on carburant.id = motorisation.id_carburant
where vehicule_vendu.id is null
order by prix desc,
             carburant,
			 modele.marque,
			 modele,
             annee desc,
             couleur;

ORDER BY

• sql.sh
• w3schools

24. Liste des motorisations triées par carburant, puissance, cylindrée

select * from motorisation
inner join carburant on carburant.id = motorisation.id_carburant
order by carburant.nom, motorisation.Chevaux, motorisation.cylindree

GROUP BY

• sql.sh
• w3schools

25. Inventaire du stock : nombre de véhicules par stock/marque/modèle.

select stock.entrepot, modele.marque, modele.nom, COUNT(vehicule.id) as 'nb vehicules'
from stock
inner join vehicule on vehicule.id_stock = stock.id
inner join modele on modele.id = vehicule.id_modele
group by stock.entrepot, modele.marque, modele.nom

26 Liste des stocks et du prix du vehicule le plus cher dans chaque stock (sans afficher le véhicule)

SELECT
    cs.id AS stock_id,
    MAX(cv.prix) AS prix_le_plus_cher
FROM
    concession_stock cs
JOIN
    concession_vehicule cv ON cs.id = cv.id_stock
GROUP BY
    cs.id;

Requête imbriquée (sous SELECT)

• sql.sh
• OC

Note : Le sous-select (ou requête imbriquées) dans la condition WHERE est une pratique de moins en moins utilisée. Il faut toutefois la connaître. Ce type de requête nécessite beaucoup de ressources de la part du SGBD. Donc si vous avez des soucis de performance (temps de réponse très longs), regardez en priorité le temps de réponse de vos requêtes SQL qui contiennent un sous-select.
Elle peut être remplacée par une jointure ou un sous select dans le FROM.

27. Pour chaque stock, afficher la marque ayant le vehicule le moins cher, ainsi que sa valeur.

requêtes imbriquées dans la clause FROM

Cet exercice est plus difficile. On utilise la fonction min() avec join et requête dans un join.

SELECT
    s.id AS stock_id,
    m.marque,
    v.prix
FROM
    concession_stock s
JOIN concession_vehicule v ON s.id = v.id_stock
JOIN concession_modele m ON v.id_model = m.id
JOIN (
    SELECT
        id_stock,
        MIN(prix) AS min_prix
    FROM
        concession_vehicule
    GROUP BY
        id_stock
) min_prices ON v.id_stock = min_prices.id_stock AND v.prix = min_prices.min_prix;