Base de données 1

Laboratoire 2

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 pertinentes 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 |
+-------------------------+------------------------------+------------+----------+----------+-------+-------+
| 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 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$ |
...
+--------------------------------------------------------+---------+-------+--------+----------+-----------+-------------------------+

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$

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

+----------------+
| *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

Mettre à jour le nom des produits en normalisant le texte en minuscule, sauf la première lettre en majuscule, 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

9 octobre, 8h AM via LÉA

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

Inscrire votre nom en commentaire

Base de données 1

Laboratoire 2 - 5%

/ 32

Nom:

Exécution répétée	0 -0.5 -1 -2
Qualité de rédaction	0 -0.5 -1 -2
Qualité de l'implémentation, arrondis	0 -0.5 -1 -2
Serveur
Base de donnée shop	1 0
Utilisateur alice, mot de passe, privilèges, externe	1 0.5 0
Table products, colonnes, types, attributs	1 0.5 0
Table sales, colonnes, types, attributs	1 0.5 0
Insertions minimales
products	1 0.5 0
sales	1 0.5 0
Produits
Le mot chat dans le nom ou description: name, description, sale_start, sale_end, quantity, cost, price	2 1.5 1 0
Sommaire, tri par date de fin, quantité, profit: product, years, cost, price, profit, margins, status	3 2.5 2 1 0
Mise à jour du prix 40% de rabais, si marge > 50%, min 1$, pas déjà meilleur	2 1.5 1 0
Supprimer archives, > 5 ans: nom	2 1.5 1 0
Ventes
Clients différents: client	1 0.5 0
Durant les Fêtes, année précédente, tri anciens à récent: id, product, date	2 1.5 1 0
Les ventes, tri récente à ancienne: date, product, quantity, price, discount, subtotal, total	2 1.5 1 0
Les ventes par produit, rabais moyen > 25%: product, avg discount	2 1.5 1 0
Sommaire par produit: product, $amount, #items, #transactions	2 1.5 1 0
Sommaire pour chaque année récente, par client alphabétique: year, client, $amount, #products	2 1.5 1 0
Meilleur client, année dernière: client, $ amount	2 1.5 1 0
Mise à jour rabais à 0 si nul	1 0.5 0
Mise à jour nom, majuscule, minuscule, espaces	1 0.5 0
Supprimer les ventes sans articles: product, date, client	2 1.5 1 0