Réaliser les manipulations suivantes dans le fichier fournit en répondant dans la section correspondante.

• Le fichier doit pouvoir être exécuté plusieurs fois en donnant exactement les mêmes résultats
• Portez une attention particulière à l'utilisation judicieuse des mécanismes et fonctions MariaDB

Serveur

• Créer la base de données shop
• Créer l'utilisateur alice avec le mot de passe pwd possédant les accès complets sur shop via une connexion externe
• Créer les tables ci-dessous
  • sales.id identifie la transaction selon la date et le client avec le format nom du client-yyyymmdd, ex: mylène citron-20121224

Insertions

• Effectuer une insertion minimale dans chaque table
• Ajouter les insertions pertinentes selon vos besoins pour tester vos requêtes

Les montants monétaires sont arrondis à 2 décimales, les autres valeurs numériques à l'entier le plus près

Produits

Récupérer les produits pour chat, donc qui contiennent le mot chat dans le nom ou la description.

+-------------------------+------------------------------+------------+----------+----------+-------+-------+
| name                    | description                  | sale_start | sale_end | quantity | cost  | price |
+-------------------------+------------------------------+------------+----------+----------+-------+-------+
| Croquettes pour chatons | pour les chat de 3 à 9 mois  | 2022-07-17 | NULL     |       33 | 20.00 | 30.00 |
| Jouet pour chat         | NULL                         | 2022-11-05 | NULL     |      156 |  3.00 |  7.99 |
...
+-------------------------+------------------------------+------------+----------+----------+-------+-------+

Récupérer le sommaire des produits, trié par date de fin de vente, quantité en inventaire et profit, en calculant les valeurs suivantes

• product contient le nom du produit ET s'il possède une description, séparée par :
• years est le nombre d'années de ventes du produit, par rapport à aujourd'hui s'il est toujours en vente, si le produit est encore en vente on ajoute +
• profit est le montant généré par la vente du produit, considérant sont prix coûtant
• margins est le pourcentage correspondant au profit par rapport au prix de vente
• status est l'état du produit soit
  • selling: {quantity} si en vente et quantité > 0
  • out of stock si en vente et quantité <= 0
  • clearance: {quantity} => {valeur de l'inventaire} si pas en vente et quantité > 0
  • archived sinon, donc pas en vente et quantité <= 0

+--------------------------------------------------------+---------+-------+--------+----------+-----------+-------------------------+
| *product*                                              | *years* | cost  | price  | *profit* | *margins* | *status*                |
+--------------------------------------------------------+---------+-------+--------+----------+-----------+-------------------------+
| Croquettes pour chatons: pour les chats de 3 à 9 mois  | 1+      | 20.00 |  30.00 |    10.00 | 33%       | selling: 33             |
| Sifflet ultrason: Pour appel et dressage des chiens    | 2       |  9.99 |  14.99 |     5.00 | 33%       | archived                |
| Sac de transport                                       | 1       | 66.00 | 129.49 |    63.49 | 49%       | clearance: 6 => 396.00$ |
...
+--------------------------------------------------------+---------+-------+--------+----------+-----------+-------------------------+

Mettre à jour à 40% de rabais le prix des produits dont la marge est supérieure à 50%, en vous assurant que le profit minimal est 1$

Mettre à jour le prix des produits en liquidation à la valeur minimale arrondie à 2 décimale pour obtenir une marge de 5%

Supprimer les produits archivés depuis plus de 5 ans, en affichant le nom des items affectés

+------------------+
| name             |
+------------------+
| Sifflet ultrason |
...
+------------------+

Ventes

Récupérer le nom des différents clients des ventes

+----------------+
| *client*       |
+----------------+
| alice cooper   |
| bob dylan      |
| dave foster    |
...
+----------------+

Récupérer les ventes réalisés durant le temps des Fêtes de l'année précédente(15 novembre au 31 décembre), triés du plus anciens au plus récent

• Afficher la date au format aaaa-mm-jj

+-----------------------+------------+------------+
| id                    | product    | *date*     |
+-----------------------+------------+------------+
| alice cooper-20221123 | sifflet    | 2022-11-23 |
| bob dylan-20221207    | croquettes | 2022-12-07 |
...
+-----------------------+------------+------------+

Récupérer les ventes de la plus récente à la plus ancienne en calculant

• date au format texte Mois jour(indicateur st, nd, rd) Année
• subtotal montant de la vente sans le rabais
• total montant de la vente AVEC le rabais

+---------------------+------------------+----------+-------+----------+------------+---------+
| *date*              | product          | quantity | price | discount | *subtotal* | *total* |
+---------------------+------------------+----------+-------+----------+------------+---------+
| September 23rd 2023 | croquettes       |       10 | 22.49 |       30 |     224.90 |  157.43 |
| September 22nd 2023 | sac de transport |        1 | 10.00 |     NULL |      10.00 |   10.00 |
...
| November 23rd 2022  | sifflet          |        1 |  2.99 |     NULL |       2.99 |    2.99 |
+---------------------+------------------+----------+-------+----------+------------+---------+

Récupérer les ventes par produit dont le rabais moyen est de plus de 25%

+------------+----------------+
| product    | *avg discount* |
+------------+----------------+
| biscuits   | 50%            |
| croquettes | 35%            |
...
+------------+----------------+

Récupérer le sommaire des ventes pour chaque produit en calculant

• $ amount montant des ventes en considérant le rabais
• # items quantité totale vendue
• # transactions nombre de ventes dans lesquelles on retrouve le produit

+------------------+------------+-----------+------------------+
| product          | *$ amount* | *# items* | *# transactions* |
+------------------+------------+-----------+------------------+
| biscuits         |     148.01 |        99 |                1 |
| collier          |       0.00 |         0 |                1 |
| croquettes       |     472.32 |        27 |                3 |
...
+------------------+------------+-----------+------------------+

Récupérer le sommaire des ventes pour chaque année de la plus récente à la plus ancienne, par client en ordre alphabétique, en calculant

• year l'année
• client le nom du client
• $ amount montant total des achats, en considérant le rabais
• # products nombre de produits différents achetés

+--------+----------------+------------+--------------+
| *year* | *client*       | *$ amount* | *# products* |
+--------+----------------+------------+--------------+
|   2023 | alice cooper   |      10.00 |            7 |
|   2023 | bob dylan      |     100.00 |            1 |
...
|   2022 | alice cooper   |       2.99 |            1 |
|   2022 | bob dylan      |      44.98 |            1 |
+--------+----------------+------------+--------------+

Récupérer le meilleur client de l'année dernière

• client le nom du client
• $ amount montant total des achats durant l'année dernière, en considérant le rabais

+-----------+------------+
| *client*  | *$ amount* |
+-----------+------------+
| bob dylan |      44.98 |
+-----------+------------+

Mettre à jour le rabais des ventes à 0 s'il est nul

Mettre à jour le nom des produits en normalisant le texte en minuscule et en retirant les espaces superflus au début et à la fin

Supprimer les ventes ne contenant aucuns articles en retournant le produit, le nom du client et la date correctement formatée

+---------+------------+----------------+
| product | *date*     | *client*       |
+---------+------------+----------------+
| collier | 2023-07-29 | franck sinatra |
...
+---------+------------+----------------+

Remise

19 octobre, 8h AM via LÉA

Envoyer uniquement le fichier .sql fournis contenant vos requêtes

• Inscrire votre nom en commentaire

Critères d'évaluation

Laboratoire 2 - 5% / 38.5

Nom: ____________________________________