Exercices

Exercice 1

Exprimer en SQL les requêtes suivantes :

1. Afficher la liste des villes dans lesquelles il existe au moins un client.

  SELECT DISTINCT ville FROM clients;

Résultat de la requête :

Clients
ville
Meylan
Grenoble
Paris
Vizille
Lille
Lyon

2. Afficher le numéro, le nom et la ville des clients de catégorie B2 n’habitant pas Paris

  SELECT numero, nom, ville
  FROM Clients
  WHERE categorie = 'B2' AND ville != 'Paris';

Résultat de la requête :

Clients
numero	nom	ville
2	Vasseur	Grenoble
5	Weiss	Lille

3. Donner le numéro, le nom et le compte des clients des villes Paris et Grenoble dont le compte est positif

  SELECT numero, nom, compte
  FROM Clients
  WHERE ville IN ('Paris', 'Grenoble') AND compte > 0;

Résultat de la requête :

Clients
numero	nom	compte
2	Vasseur	1903
6	Berlioz	123094

4. Quelles catégories de clients trouve-t-on dans la ville Paris ?

  SELECT DISTINCT categorie, ville
  FROM Clients
  WHERE ville = 'Paris';

Résultat de la requête :

Clients
categorie	ville
C3	Paris
	Paris
C1	Paris

Exercice 2

Exprimer en SQL les requêtes suivantes :

1. Afficher le numéro, le nom et la ville des clients dont le nom précède alphabétiquement la ville où ils résident

  SELECT c.numero, c.nom, c.ville
  FROM Clients c
  WHERE c.nom < c.ville

Résultat de la requête :

Clients
numero	nom	ville
1	Lebreton	Meylan
3	Da Costa	Paris
4	Rey	Vizille
6	Berlioz	Paris
7	Lefevre	Lyon
9	Barda	Paris
8	Jacques	Paris

2. Afficher les villes des clients qui commandent le produit 1

SELECT C.ville FROM Clients C WHERE C.numero IN (
  SELECT COM.numeroClient FROM Commandes COM WHERE COM.numero IN (
      SELECT D.numeroCommande FROM Details D WHERE D.numeroProduit = 1
  )
);

Résultat de la requête :

Clients
ville
Meylan
Paris
Vizille
Lille

3. Quels sont les produits dont le nom commence par la lettre c ou C qui font l’objet d’une commande ?

  SELECT P.numero, P.nom
  FROM Produits P
  WHERE
      ( P.nom LIKE 'c%' OR P.nom LIKE 'C%' )
      AND P.numero IN ( SELECT D.numeroProduit FROM Details D );

Résultat de la requête :

Produits
numero	nom
3	Chaussures
4	Casquette

4. Donner la valeur totale des stocks sans tenir compte des commandes en cours.

  SELECT SUM(P.stock * P.prix) AS 'Valeur du stock'
  FROM Produits P;

Résultat de la requête :

Produits
Valeur du stock
16300

Exercice 3

Exprimer en SQL les requêtes suivantes :

Conseil : Utilisation de sous requêtes, de COUNT, de IN et NOT IN, de LIKE

1. Combien y a-t-il de commandes spécifiant un (ou plusieurs) produit(s) dont le nom commence par la lettre c ou C ?

  SELECT COUNT(DISTINCT D.numeroCommande) AS "Nombre de commandes"
  FROM Details D
  WHERE D.numeroProduit IN (
      SELECT P.numero FROM Produits P WHERE P.nom LIKE 'c%' OR P.nom LIKE 'C%'
  );

Résultat de la requête :

Details
Nombre de commandes
4

2. Afficher le numéro et le nom des clients qui n’ont pas commandé de produits dont le nom commence par la lettre c ou C

  SELECT C.numero, C.nom
  FROM Clients C
  WHERE C.numero NOT IN (
      SELECT DISTINCT COM.numeroClient
      FROM Commandes COM
      WHERE COM.numero IN (
          SELECT D.numeroCommande FROM Details D WHERE D.numeroProduit IN (
              SELECT P.numero FROM Produits P WHERE P.nom LIKE 'c%' OR P.nom LIKE 'C%'
          )
      )
  );

Résultat de la requête :

Clients
numero	nom
2	Vasseur
4	Rey
6	Berlioz
7	Lefevre
9	Barda
8	Jacques

3. Quels sont les produits (numéros et noms) qui n’ont pas été commandés en 2024 ?

  SELECT P.numero, P.nom
  FROM Produits P
  WHERE P.numero NOT IN (
      SELECT D.numeroProduit
      FROM Details D
      WHERE D.numeroCommande IN (
          SELECT C.numero
          FROM Commandes C
          WHERE C.dateCommande LIKE '2024%'
      )
  );

Résultat de la requête :

Produits

Exercice 4

Exprimer en SQL les requêtes suivantes :

Conseil : Utilisation de sous requêtes, de IN et NOT IN, de EXISTS et NOT EXISTS, et de références multiplee à une même table (voir le cours)

1. Rechercher les clients qui ont commandé tous les produits.

  SELECT C.numero, C.nom
  FROM Clients C
  WHERE NOT EXISTS (
      SELECT P.numero FROM Produits P
      WHERE P.numero NOT IN (
          SELECT D.numeroProduit FROM Details D
          WHERE D.numeroCommande IN (
              SELECT COM.numero FROM Commandes COM
              WHERE COM.numeroClient = C.numero
          )
      )
  );

Résultat de la requête :

Clients
numero	nom
1	Lebreton
3	Da Costa

2. Rechercher les villes dont aucun client n’a passé de commande.

  SELECT DISTINCT ville FROM Clients
  WHERE ville NOT IN (
      SELECT DISTINCT ville FROM Clients C
      WHERE C.numero IN (
          SELECT COM.numeroClient FROM Commandes COM
      )
  );

Résultat de la requête :

Clients
ville
Grenoble
Lyon

3. Rechercher les villes dont tous les clients ont passé au moins une commande

  SELECT DISTINCT ville FROM Clients C1
      WHERE NOT EXISTS (
          SELECT C2.numero FROM Clients C2
          WHERE C2.ville = C1.ville
          AND NOT EXISTS (
              SELECT COM.numeroClient FROM Commandes COM
              WHERE COM.numeroClient = C2.numero
          )
      );

Résultat de la requête :

Clients
ville
Meylan
Vizille
Lille