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
- sales.discount est un pourcentage, de 0 à 100 inclusivement

classDiagram class products { name varchar[100] nn un description varchar[100] sale_start date nn = curdate sale_end date quantity int unsigned nn = 0 cost double[5,2] unsigned nn price double[5,2] unsigned nn } class sales { id varchar[200] nn product varchar[100] nn quantity tinyint unsigned nn price double[10,2] unsigned nn discount tinyint unsigned }

Insertions

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

À l'affichage, les valeurs monétaires sont arrondies à 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 |
+-------------------------+------------------------------+------------+----------+----------+-------+-------+
...
+-------------------------+------------------------------+------------+----------+----------+-------+-------+

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 n'est plus 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$ |
...
+--------------------------------------------------------+---------+-------+--------+----------+-----------+-------------------------+

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

Trier en order alphabétique.

+----------------+
| *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, ...) 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 à 0 s'il est nul

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

Énoncé de travail sur LÉA

Envoyer uniquement le fichier .sql fournis contenant vos requêtes
Inscrire votre nom en commentaire
AVANT la date limite de remise, vous devez passer me voir pour recevoir votre rétroaction

James Hoffman

Base de données 1