Exercices sur un système de commande

Nous allons réaliser des recherches sur un système de gestion de clients et de commandes.

Pour cela nous allons créer une table pour enregistrer les clients, une table pour enregistrer les commandes et une table de détail des commandes.

Création des tables

En utilisant SQL, vous allez créer les tables suivantes en respectant à la lettre les instructions qui suivent.

Table client

Créer une table nommée 'client' avec les champs suivants :

`id` de type INTEGER, ne peut pas être vide. Ce champ sera la clé primaire et AUTOINCREMENT
`prenom` de type varchar avec 255 caractères maxi, ne peut pas être vide
`nom` de type varchar avec 255 caractères maxi, ne peut pas être vide
`email` de type varchar avec 255 caractères maxi, ne peut pas être vide
`ville` de type varchar avec 255 caractères maxi, ne peut pas être vide
`password` de type varchar avec 255 caractères maxi, ne peut pas être vide

Table commandes

Créer une table nommée 'commande' avec les champs suivants :

`id` de type INTEGER, ne peut pas être vide. Ce champ sera la clé primaire et AUTOINCREMENT
`client_id` de type int(10, ne peut pas être vide
`date_achat` de type date, ne peut pas être vide
`reference` de type varchar avec 255 caractères maxi, ne peut pas être vide
`cache_prix_total` de type float, ne peut pas être vide

Table commande_ligne

Créer une table nommée 'commande_ligne' avec les champs suivants :

`id` de type INTEGER, ne peut pas être vide. Ce champ sera la clé primaire et AUTOINCREMENT
`commande_id` de type int(10), ne peut pas être vide
`nom` de type varchar avec 255 caractères maxi, ne peut pas être vide
`quantite` de type int(10), ne peut pas être vide
`prix_unitaire` de type float, ne peut pas être vide
`prix_total` de type float, ne peut pas être vide

Remplir les tables avec des données

Télécharger les fichiers suivants pour remplir vos tables avec des données.

• Listing clients
• Listing commandes
• détails des commandes

Ouvrez les fichers et récupérer le code SQL pour le coller dans votre invite de commande SQLite de Visual Studio Code.

Exercices du jour

1. Obtenir l’utilisateur ayant le prénom “Muriel” et le mot de passe “test11”, sachant que l’encodage du mot de passe est effectué avec l’algorithme Sha1.
2. Obtenir la liste de tous les produits qui sont présent sur plusieurs commandes.

3. SELECT nom, COUNT(*) AS nbr_items , GROUP_CONCAT(`commande_id`) AS liste_commandes
   FROM `commande_ligne` 
   GROUP BY nom 
   HAVING nbr_items > 1
   ORDER BY nbr_items DESC

4. Obtenir la liste de tous les produits qui sont présent sur plusieurs commandes et y ajouter une colonne qui liste les identifiants des commandes associées.
5. Enregistrer le prix total à l’intérieur de chaque ligne des commandes, en fonction du prix unitaire et de la quantité
6. Obtenir le montant total pour chaque commande et y voir facilement la date associée à cette commande ainsi que le prénom et nom du client associé

UPDATE commande AS t1 
INNER JOIN 
    ( SELECT commande_id, SUM(commande_ligne.prix_total) AS p_total 
      FROM commande_ligne 
      GROUP BY commande_id ) t2 
          ON  t1.id = t2.commande_id 
SET t1.cache_prix_total = t2.p_total

7. (difficulté très haute) Enregistrer le montant total de chaque commande dans le champ intitulé “cache_prix_total”

SELECT YEAR(`date_achat`), MONTH(`date_achat`), SUM(`cache_prix_total`) 
FROM `commande` 
GROUP BY YEAR(`date_achat`), MONTH(`date_achat`)
ORDER BY YEAR(`date_achat`), MONTH(`date_achat`)

8. Obtenir le montant global de toutes les commandes, pour chaque mois
9. Obtenir la liste des 10 clients qui ont effectué le plus grand montant de commandes, et obtenir ce montant total pour chaque client.
10. Obtenir le montant total des commandes pour chaque date
11. Ajouter une colonne intitulée “category” à la table contenant les commandes. Cette colonne contiendra une valeur numérique
12. Enregistrer la valeur de la catégorie, en suivant les règles suivantes :
    
    • “1” pour les commandes de moins de 200€
    • “2” pour les commandes entre 200€ et 500€
    • “3” pour les commandes entre 500€ et 1.000€
    • “4” pour les commandes supérieures à 1.000€
13. Créer une table intitulée “commande_category” qui contiendra le descriptif de ces catégories
14. Insérer les 4 descriptifs de chaque catégorie au sein de la table précédemment créée
15. Supprimer toutes les commandes (et les lignes des commandes) inférieur au 1er février 2019. Cela doit être effectué en 2 requêtes maximum

Correction

Pour vous aider à réviser et à mieux comprendre la conception des requêtes, je vous invite à consulter le fichier de correction des exercices après avoir étudier les questions.

• Correction Commandes