Projet SQL

Soumis à :
Grand Apprentissage

Soumis par :
Manoj Kumbhare
PGPDSBA En ligne Juillet_C 2021
Science des données et analyse commerciale.
Contenu
Introduction.....................................................................................................................................4
1. SQLite...........................................................................................................................................4
1. Écrivez une requête pour afficher les détails du produit (code_classe_produit, id_produit, description_produit)
prix_produit,) selon les critères suivants et les trier par ordre décroissant de catégorie : a. Si
la catégorie est 2050, augmentez le prix de 2000 b. Si la catégorie est 2051, augmentez le prix de
500 c. Si la catégorie est 2052, augmentez le prix de 600. Indice : Utilisez une instruction case. pas permanent.
changer intable requis. (60 LIGNES) [REMARQUE : TABLE DES PRODUITS] ....................................................4
2. Écrivez une requête pour afficher (description_classe_produit, identifiant_produit, description_produit,

Quantité de produits disponibles) et afficher l'état des stocks des produits comme ci-dessous selon leur disponibilité
a. Pour les catégories Électronique et Informatique, si la quantité disponible est <= 10, afficher 'Faible
stock', 11 <= qty <= 30, afficher 'En stock', >= 31, afficher 'Assez de stock' b. Pour la papeterie et les vêtements
categories, if qty <= 20, show 'Low stock', 21 <= qty <= 80, show 'In stock', >= 81, show 'Enough
stock' c. Restofthe categories, if qty <= 15 – 'Low Stock', 16 <= qty <= 50 – 'In Stock', >= 51 –
'Assez de stock' Pour toutes les catégories, si la quantité disponible est 0, affichez 'En rupture de stock'. Conseil : Utilisez la casse

déclaration. (60 LIGNES) [REMARQUE : TABLES À UTILISER – produit, classe_de_produit].............................5

3. Rédigez une requête pour montrer le nombre de villes dans tous les pays autres que les États-Unis et la Malaisie, avec
plus d'une ville, dans l'ordre décroissant des VILLES. (2 lignes) [REMARQUE : TABLE ADRESSE, ne pas utiliser]
Distnct]........................................................................................................................................7
4. Écrivez une requête pour afficher l'identifiant du client, le nom complet du client, la ville, le code postal et les détails de la commande.

numéro de commande
prix_du_produit)) pour les commandes expédiées vers des villes dont les codes postaux ne contiennent aucun 0. Trier
la sortie sur le nom du client, la date de la commande et le sous-total. (52 LIGNES) [REMARQUE : TABLE À UTILISER -
client_en_ligne, adresse, en_tête_de_commande, articles_de_commande, produit, classe_de_produit].........................8
5. Écrivez une requête pour afficher l'identifiant du produit, la description du produit, la quantité totale (somme de la quantité du produit)
pour un article qui a été acheté le plus grand nombre de fois avec l'identifiant du produit 201. (UTILISER SOUS-
REQUÊTE) (1 LIGNE) [REMARQUE: TABLE ORDER_ITEMS, TABLE PRODUIT]...............................................9
6. Écrivez une requête pour afficher l'identifiant client, le nom du client, l'email et les détails de la commande (identifiant de commande,

description du produit
ils n'ont commandé aucun article. (225 LIGNES) [REMARQUE : TABLE À UTILISER - client_en_ligne,
en-tête_de_commande
2. MySQL........................................................................................................................................11
7. Écrivez une requête pour afficher l'ID du carton, (longueur * largeur * hauteur) comme volume_carton et identifier l'optimum

carton (carton avec le volume le plus petit dont le volume est supérieur au volume total de tous les articles
(longueur * largeur * hauteur * quantite_produit) pour une commande donnée dont l'identifiant de commande est 10006, Supposons que tout

les articles d'une commande sont emballés dans un seul carton (boîte). (1 LIGNE) [REMARQUE : CARTON
TABLE, TABLE DE PRODUIT].............................................................................................................11
8. Rédigez une requête pour afficher les détails (identifiant du client, nom complet du client, identifiant de la commande, quantité du produit)

clients qui ont acheté plus de dix produits (c'est-à-dire la quantité totale de la commande) avec une carte de crédit ou un paiement sur Internet
La banque comme mode de paiement par commande expédiée. (6 LIGNES) [REMARQUE : TABLES À UTILISER -
client_en_ligne, en-tête_de_commande, articles_de_commande,]..........................................................................12
9. Écrivez une requête pour afficher l'identifiant de la commande, l'identifiant du client et le nom complet des clients.

commençant par la lettre "A" ainsi que (product_quantty) comme quantité totale de produits
 expédié pour les identifiants de commande > 10030. (5 LIGNES) [REMARQUE : TABLES À UTILISER - client_en_ligne,
en-tête_de_commande, éléments_de_commande].......................................................................................................13
10. Écrivez une requête pour afficher la description de la classe de produit, la quantité totale.

(somme(quantité_produit), Valeur totale (quantité_produit * prix_produit) et montrer quelle classe de

les produits ont été expédiés en plus grande quantité vers des pays en dehors de l'Inde autre que les États-Unis ? Aussi
montrez la valeur totale de ces articles. (1 LIGNE)[REMARQUE : TABLE DES PRODUITS, ADRESSE
TABLE, TABLE_CLIENT_EN_LIGNE, TABLE_EN_TÊTE_DE_COMMANDE, ARTICLES_DE_COMMANDE
TABLE,CLASSE_DE_PRODUIT TABLE]..................................................................................................14

LISTE DES TABLEAUX

Entrées de figures notables trouvées.

LISTE DES ÉQUATIONS

Entrées de figures notables trouvées.

LISTE DES FIGURES

Figure 1 : Sortie 1.................................................................................................................................5
Figure 2 : Sortie 2.................................................................................................................................6
Figure 3 : Sortie 3.................................................................................................................................7
Figure 4 : Sortie 4.................................................................................................................................9
Figure 5 : Sortie 5...............................................................................................................................10
Figure 6 : Sortie 6...............................................................................................................................11
Figure 7 : Sortie 7...............................................................................................................................12
Figure 8 : Sortie 8...............................................................................................................................13
Figure 9 : Sortie 9...............................................................................................................................14
Figure 10 : Sortie 10...........................................................................................................................15
Introduction :
Ce document décrit le scénario de la fonctionnalité simple de gestion des commandes d'un commerce de détail en ligne.
magasin. Scénario d'achat typique : Un client passe une commande pour N produits en spécifiant la quantité pour
chaque ligne de la commande. Chaque produit appartient à une classe de produit (ou catégorie). Tous les produits
commandé dans une seule commande, sont expédiés à l'adresse du client (en Inde ou à l'étranger) par un expéditeur en une seule
expédition. La commande peut être payée par espèces, carte de crédit ou banque en ligne. Il peut y avoir des clients.
qui n'ont peut-être passé aucune commande. Quelques clients auraient annulé leurs commandes (Dans l'ensemble
commande, aucune annulation d'article individuel autorisée). Quelques commandes peuvent être en statut 'En cours'. Il peut y avoir
il peut également s'agir de produits qui n'ont jamais été achetés. Les expéditeurs utilisent des cartons (boîtes) de taille optimale pour expédier un

commande, basée sur le volume total de tous les produits et leurs quantités. Dimensions de chaque produit (L,
W, H) est également stocké dans la base de données. Pour rester simple, tous les produits d'une commande sont regroupés dans un seul
carton de taille appropriée pour l'expédition.

Vous êtes engagé par une chaîne de magasins de vente au détail en ligne « Reliant Retail Limited ». Ils vous ont fourni
base de données 'commandes' et rechercher des réponses aux requêtes suivantes, car les résultats de ces requêtes seront
aider l'entreprise à prendre des décisions basées sur les données qui auront un impact sur la croissance globale de l'en ligne
magasin de détail. 1ère partie - Q1-Q6 relèvent de SQLite et les requêtes doivent être exécutées dans DB Browser.
(Base de données - [Link]) 2ème partie - Q7-Q10 relève de MYSQL et les requêtes doivent être exécutées dans
MYSQL. (Script SQL -[Link])

SQLite
1. Écrire une requête pour afficher les détails du produit (code_classe_produit, id_produit, description_produit)
prix_produit,) selon les critères suivants et les trier par ordre décroissant de catégorie : a. Si
la catégorie est 2050, augmentez le prix de 2000 b. Si la catégorie est 2051, augmentez le prix de
500
c. Si la catégorie est 2052, augmentez le prix de 600. Indice : utilisez une instruction case. pas permanent
changement de tableau requis. (60 LIGNES) [REMARQUE : TABLE DES PRODUITS]

SÉLECTIONNER

CODE_DE_CLASSE_DE_PRODUIT

IDENTIFIANT_PRODUIT

PRODUCT_DESC

PRIX_DU_PRODUIT
CAS CODE_DE_CLASSE_DE_PRODUIT

QUAND 2050 ALORS PRIX_DU_PRODUIT + 2000

QUAND 2051 ALORS PRIX_PRODUIT + 500

QUAND 2052 ALORS PRIX_DU_PRODUIT + 600

AUTRE PRIX_PRODUIT

FIN COMME AUGMENTER_PRIX

DE

PRODUIT
ORDER BY PRODUCT_CLASS_CODE DESC;

Figure 1 : Sortie 1

2. Écrivez une requête pour afficher (description_classe_produit, id_produit, description_produit, quantité_produit_disponible

) et afficher l'état des stocks des produits comme ci-dessous en fonction de leur quantité disponible :
a. Pour les catégories Électronique et Informatique, si la quantité disponible est <= 10, afficher 'Stock faible', 11
En stock
b. Pour les catégories Fournitures de bureau et Vêtements, si qty <= 20, afficher 'Stock faible', 21 <= qty <= 80, afficher
En stock
c. Resto des catégories, si qty <= 15 – 'Stock Faible', 16 <= qty <= 50 – 'En Stock', >= 51 – 'Assez'
actions
Pour toutes les catégories, si la quantité disponible est de 0, afficher 'Rupture de stock'.

Indice : Utilisez une instruction case.

(60 LIGNES) [REMARQUE : TABLEAUX À UTILISER – produit, classe_de_produit]

SÉLECTIONNER

pc.DESCRIPTION_DE_LA_CLASSE_DE_PRODUIT

ID_DE_PRODUIT

PRODUCT_DESC

QUANTITÉ_DE_PRODUIT_DISPONIBLE

CAS

QUAND

Électronique

OU PRODUCT_CLASS_DESC = 'Ordinateur'

ALORS

CAS
LORSQUE PRODUCT_QUANTITY_AVAIL = 0 ALORS 'Rupture de Stock'

QUAND PRODUCT_QUANTITY_AVAIL <= 10 ALORS 'Stock Faible'

QUAND PRODUCT_QUANTITY_AVAIL ENTRE 11 ET 30 ALORS 'En Stock'

 QUAND PRODUCT_QUANTITY_AVAIL >= 31 ALORS 'Stock Suffisant'

FIN

QUAND

Vêtements

OU PRODUCT_CLASS_DESC = 'Papeterie'

ALORS

CAS
QUAND PRODUCT_QUANTITY_AVAIL = 0 ALORS 'En rupture de stock'

QUAND PRODUCT_QUANTITY_AVAIL <= 20 ALORS 'Stock Bas'

QUAND PRODUCT_QUANTITY_AVAIL ENTRE 21 ET 80 ALORS 'En Stock'

QUAND PRODUCT_QUANTITY_AVAIL >= 81 ALORS 'Suffisamment de Stock'

FIN

CAS AUTRE
QUAND PRODUCT_QUANTITY_AVAIL = 0 ALORS 'En Rupture de Stock'

QUAND PRODUCT_QUANTITY_AVAIL <= 15 ALORS ' faible stock '

QUAND PRODUCT_QUANTITY_AVAIL ENTRE 16 ET 50 ALORS 'En Stock'

QUAND PRODUCT_QUANTITY_AVAIL >= 51 ALORS 'Stock Suffisant'

FIN
FIN EN tant que niveau_d'inventaire

DE

produit

JOIN INTERNE

CLASSE_PRODUIT pc ON prod.CODE_CLASSE_PRODUIT = pc.CODE_CLASSE_PRODUIT;

Figure 2 : Sortie 2
3. Écrivez une requête pour afficher le nombre de villes dans tous les pays autres que les États-Unis et la Malaisie, avec
plus d'une ville, dans l'ordre décroissant des VILLES. (2 lignes) [REMARQUE : TABLE ADRESSE, Ne pas
utilisez Distinct

SÉLECTIONNER * DE L'ADRESSE;

SÉLECTIONNER

COUNT(VILLE) AS COMPTE_VILLE, PAYS

DE

ADRESSE

OÙ

PAYS NON IN ('USA', 'Malaisie')

REGROUPER PAR PAYS

AVOIR COUNT(CITY) > 1

ORDER BY CITY_COUNT DESC;

Figure 3 : Sortie 3
4. Écrivez une requête pour afficher l'identifiant du client, le nom complet du client, la ville, le code postal et les détails de la commande.

id de commande
prix_du_produit)) pour les commandes expédiées à des villes dont les codes PIN n'ont pas de 0. Trier
la sortie sur le nom du client, la date de la commande et le sous-total. (52 LIGNES) [REMARQUE : TABLE À UTILISER -
client_en_ligne

SÉLECTIONNER

cust.ID_CLIENT,

cust.PRENOM_CLIENT

|| cust.CUSTOMER_LNAME COMME CUSTOMER_FULLNAME,

[Link]

[Link] POSTAL

ord.ORDER_ID

prod_class.PRODUCT_CLASS_DESC

prod.DESCRIPTION_DU_PRODUIT
prod.PRIX_DU_PRODUIT * prod.PRIX_DU_PRODUIT AS SOUS_TOTAL

DE

CLIENT_EN_LIGNE AS cust

JOIN INTERNE

ADRESSE COMME addr SUR cust.ADDRESS_ID = addr.ADDRESS_ID

JOINTURE INTERNE

ORDER_HEADER ord ON cust.CUSTOMER_ID = ord.CUSTOMER_ID

ET ord.ORDER_STATUS = 'Expédié'

JOIN INTERNE

ORDER_ITEMS OI SUR ord.ORDER_ID = OI.ORDER_ID

JOIN INTERNE

PRODUIT prod SUR OI.PRODUCT_ID = prod.PRODUCT_ID

JOIN INTERNE

PRODUCT_CLASS prod_class ON prod_class.PRODUCT_CLASS_CODE =

prod.CODE_CLASSE_PRODUIT
OÙ

[Link] NE LIKE '%0%'

ORDONNER PAR NOM_COMPLET_CLIENT, ord.DATE_COMMANDE, SOUS_TOTAL;
Figure 4 : Résultat 4

5. Rédigez une requête pour afficher l'identifiant du produit, la description du produit, la quantité totale (somme de la quantité de produit) pour

un article qui a été acheté le plus grand nombre de fois avec l'identifiant produit 201. (UTILISER SOUS-
REQUÊTE) (1 LIGNE) [REMARQUE : TABLE ORDER_ITEMS, TABLE PRODUIT]

SÉLECTIONNER

prod.PRODUCT_ID

PRODUCT_DESC
SOMME(QANTITE_PRODUIT) COMME QUANTITE_TOTALE

DE
COMMANDES_ARTICLES oi,

PRODUIT prod

OÙ
IDENTIFIANT_DE_COMMANDE DANS (SÉLECTIONNER

NUMÉRO_DE_COMMANDE

DE

ÉLÉMENTS_DE_COMMANDE

OÙ

PRODUCT_ID = 201)

ET prod.PRODUCT_ID != 201

ET oi.PRODUCT_ID = prod.PRODUCT_ID

GROUPE PAR prod.PRODUCT_ID, prod.PRODUCT_DESC

ORDER BY TOTAL_QUANTITY DESC

LIMITER 1;
Figure 5 : Sortie 5

6. Écrivez une requête pour afficher l'identifiant du client, le nom du client, l'e-mail et les détails de la commande (identifiant de la commande,

description du produit
ils n'ont commandé aucun article. (225 LIGNES) [REMARQUE : TABLE À UTILISER - client_en_ligne,
en-tête de commande

SÉLECTIONNER

oc.ID_CLIENT,

oc.NOM_CLIENT || oc.PRENOM_CLIENT COMME NOM_CLIENT,

EMAIL_CLIENT

[Link]ÉRO_DE_COMMANDE

prod.DESCRIPTION_PRODUIT

quantité du produit
[Link]É_PRODUIT * prod.PRIX_PRODUIT EN tant que sous-total

DE

CLIENT_EN_LIGNE
JOINDRE À GAUCHE

ORDER_HEADER ord ON oc.CUSTOMER_ID = ord.CUSTOMER_ID

JOINTURE À GAUCHE

ORDER_ITEMS oi ON ord.ORDER_ID = oi.ORDER_ID

JOIN GAUCHE

PRODUIT prod SUR oi.PRODUCT_ID = prod.PRODUCT_ID

ORDER BY oc.CLIENT_ID, ord.COMMANDE_ID, prod.DESCRIPTION_PRODUIT;

Figure 6 : Sortie 6

2. MySQL

7. Écrire une requête pour afficher l'identifiant du carton, (longueur * largeur * hauteur) comme volume_carton et identifier l'optimum

carton (carton ayant le volume le plus faible dont le volume est supérieur au volume total de tous les articles
(len * largeur * hauteur * quantité_produit) pour une commande donnée dont l'identifiant de commande est 10006, supposons que tout

Les articles d'une commande sont emballés dans un seul carton.

TABLE, TABLE DE PRODUIT

SÉLECTIONNER

CARTON_ID

DE

CARTON

OÙ
(LONGUEUR * LARGEUR * HAUTEUR) >= (SÉLECTIONNER

SOMME(LONGUEUR * LARGEUR * HAUTEUR * QUANTITÉ_PRODUIT)

DE
articles_de_commande

JOINTURE INTERNE

produit PROD ON OI.PRODUCT_ID = PROD.PRODUCT_ID

OÙ

ORDER_ID = 10006)

TRIER PAR CARTON_VOL

LIMITER 1;
Figure 7

8. Écrivez une requête pour afficher les détails (identifiant client, nom complet du client, identifiant de commande, quantité de produit) de

clients ayant acheté plus de dix (c'est-à-dire une quantité totale de commande) produits avec une carte de crédit ou par virement
Banque comme mode de paiement par commande expédiée. (6 LIGNES) [REMARQUE : TABLEAUX À UTILISER -
client_en_ligne

SÉLECTIONNER

OC.ID_CLIENT,

CONCAT(NOM_CLIENT, ' ', PRENOM_CLIENT) AS NOM_COMPLET_CLIENT

[Link]ÉRO_DE_COMMANDE
SOMME(OI.QUANTITE_PRODUIT) COMME QUANTITE_TOTAL_DE_COMMANDE

DE

client_en_ligne AS OC

INNER JOIN

order_header COMME OH SUR OC.CUSTOMER_ID = OH.CUSTOMER_ID

JOINTURE INTERNE

order_items EN OI SUR OH.order_id = OI.order_id

OÙ

Expédié
ET OH.PAYMENT_MODE DANS ('carte de crédit', 'Banque en ligne')

ET OH.order_id DANS (SÉLECTIONNER

ID_DE_COMMANDE

DE

articles_de_commande
 GROUPE PAR ORDER_ID

AYANT SOMME(PRODUCT_QUANTITY) > 10)

GROUPE PAR ORDER_ID;

Figure 8 : Sortie 8

9. Écrivez une requête pour afficher l'identifiant de commande, l'identifiant du client et le nom complet du client.

commençant par la lettre "A" avec (quantité_produit) comme quantité totale de produits
expédié pour les identifiants de commande > 10030. (5 LIGNES) [REMARQUE : TABLES À UTILISER - client_en_ligne,
en-tête_de_commande

SÉLECTIONNER

OH.ORDER_ID

CUST.ID_CLIENT

CONCAT(NOM_CLIENT, ' ', PRENOM_CLIENT) AS NOM_COMPLET_CLIENT

SOMME([Link]É_DE_PRODUIT) COMME TOTAL_QUANTITÉ_DE_PRODUITS

DE
en-tête_de_commande EN OH

REJOINDRE

client_en_ligne CUST ON OH.customer_id = CUST.customer_id

JOINTURE GAUCHE

articles_commandés AS OI ON OH.id_commande = OI.id_commande

OÙ

OH.ORDER_ID > 10030

 ET CUSTOMER_FNAME LIKE 'A%'

ET OH.order_status = 'Expédié'

GROUPE PAR OH.ORDER_ID;

Figure 9 : Sortie 9

10. Écrivez une requête pour afficher la description de la classe de produit, la quantité totale (somme(product_quantty), Total

valeur (quantité_produit * prix_produit) et montrer quelle classe de produits a été expédiée

les pays recevant la quantité la plus élevée en dehors de l'Inde, à l'exception des États-Unis ? Montrez également la valeur totale de ceux-ci.
articles. (1 LIGNES)[REMARQUE :TABLE DES PRODUITS, TABLE DES ADRESSES, CLIENT EN LIGNE
TABLE, EN-TÊTE_DE_COMMANDE TABLE, ARTICLES_DE_COMMANDE TABLE, CLASSE_DE_PRODUIT TABLE

SÉLECTIONNER

DESCRIPTION_DE_LA_CLASSE_DE_PRODUIT

SUM(OI.PRODUCT_QUANTITY) AS TOTAL_QUANTITY,

SOMME([Link]é_produit * PROD.prix_produit) AS VALEUR_TOTALE

DE

adresse A

JOIN INTERNE

client_en_ligne OC ON OC.ADDRESS_ID = A.ADDRESS_ID

JOIN INTERNE
en-tête_de_commande OH ON OH.CUSTOMER_ID = OC.CUSTOMER_ID

JOINTURE INTERNE

order_items OI ON OH.ORDER_ID = OI.ORDER_ID

JOINDRE INTERNE

produit PROD ON PROD.PRODUCT_ID = OI.PRODUCT_ID

 JOIN INTERNE

product_class PC ON PROD.PRODUCT_CLASS_CODE = PC.PRODUCT_CLASS_CODE

OÙ

[Link] != 'Inde'

ET [Link] != 'USA'

ET ÉTAT_COMMANDE = 'Expédié'
REGROUPER PAR DESCRIPTION_DE_CLASSE_DE_PRODUIT

ORDONNER PAR QUANTITÉ_TOTALE DESC

LIMIT 1;

Figure 10 : Sortie 10