Création de liste

Version imprimable

Création de liste

Bonjour,

Je fais appel a vous car je reste bloquer sur un exercice sans toutefois voir par ou passer pour obtenir le résultat ...

Enoncé : Liste avec pour chaque produit la quantité en stock et les quantités commandées ( libellée quantité vendue à l'affichage )

Extrait d'affichage
Libelle produit QTESTK Quantité vendue
--------------- -------- ---------------
ACI141001 277 -
ACI141002 167 64
ACI141003 207 35
ACI141004 139 68
ACI14100X 37 30

Les tables sont :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table PRODUIT
(
    NUSINE                          char(3)               not null,
    NPROD                           char(5)               not null,
    LIB                             varchar(20)           ,
    PRIX                            decimal(7,2)          ,
    QTESTK                          smallint              ,
    constraint PK_PRODUIT primary key (NUSINE, NPROD)
)
go
 
create table COMMANDE
(
    NCDE                         int                   not null,
    DATECDE                         datetime              ,    
    NCLI                            int                   not null,
    NREP                            int                   not null,
    NUSINE                          char(3)               not null,
    NPROD                           char(5)               not null,
    QTE                          smallint              not null,
    MONTANT                decimal(9,2)      ,
    constraint PK_COMMANDE primary key (NCDE)
)
go
alter table COMMANDE
    add constraint FK_COMMANDER foreign key  (NUSINE, NPROD)
       references PRODUIT (NUSINE, NPROD)

Le contenu :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
/*****************************************************/
/*          Chargement  de  la  table  des  Produits */
/*****************************************************/
insert into   produit  values ('REI','2A45C','RATCHET LINK',79.00,210)
insert into   produit  values ('ACI','4100Y','WIDGET REMOVER',2750.00,25)
insert into   produit  values ('QSA','XK47','REDUCER',355.00,38)
insert into   produit  values ('BIC','41672','PLATE',180.00,0)
insert into   produit  values ('IMM','779C','900-LB BRACE',1875.00,9)
insert into   produit  values ('ACI','41003','SIZE 3 WIDGET',107.00,207)
insert into   produit  values ('ACI','41004','SIZE 4 WIDGET',117.00,139)
insert into   produit  values ('BIC','41003','HANDLE',652.00,3)
insert into   produit  values ('IMM','887P','BRACE PIN',250.00,24)
insert into   produit  values ('QSA','XK48','REDUCER',134.00,203)
insert into   produit  values ('REI','2A44L','LEFT HINGE',4500.00,12)
insert into   produit  values ('FEA','112','HOUSING',148.00,115)
insert into   produit  values ('IMM','887H','BRACE HOLDER',54.00,223)
insert into   produit  values ('BIC','41089','RETAINER',225.00,78)
insert into   produit  values ('ACI','41001','SIZE 1 WIDGET ',55.00,277)
insert into   produit  values ('IMM','775C','500-LB BRACE',1425.00,5)
insert into   produit  values ('ACI','4100Z','WIDGET INSTALLER',2500.00,28)
insert into   produit  values ('QSA','XK48A','REDUCER',177.00,37)
insert into   produit  values ('ACI','41002','SIZE 2 WIDGET',76.00,167)
insert into   produit  values ('REI','2A44R','RIGHT HINGE',4500.00,12)
insert into   produit  values ('IMM','773C','300-LB BRACE',975.00,28)
insert into   produit  values ('ACI','4100X','WIDGET ADJUSTER',25.00,37)
insert into   produit  values ('FEA','114','MOTOR MOUNT',243.00,15)
insert into   produit  values ('IMM','887X','BRACE RETAINER',475.00,32)
insert into   produit  values ('REI','2A44G','HINGE PIN',350.00,14)
/****************************************************************/
/*          Chargement  de  la  table  des  COMMANDES           */
/****************************************************************/
insert into   commande  values (112961,'17.12.1999',2117,106,'REI','2A44L',7,31500.00)
insert into   commande  values (113012,'11.01.2000',2111,103,'ACI','41003',35,3745.00)
insert into   commande  values (112989,'03.01.2000',2101,106,'FEA','114',6,1458.00)
insert into   commande  values (113051,'10.02.2000',2118,108,'QSA','XK47',4,1420.00)  
insert into   commande  values (112968,'12.10.1999',2102,101,'ACI','41004',34,3978.00)
insert into   commande  values (113036,'30.01.2000',2107,110,'ACI','4100Z',9,22500.00)
insert into   commande  values (113045,'02.02.2000',2112,108,'REI','2A44R',10,45000.00)
insert into   commande  values (112963,'17.12.1999',2103,105,'ACI','41004',28,3276.00) 
insert into   commande  values (113013,'14.01.2000',2118,108,'BIC','41003',1,652.00)
insert into   commande  values (113058,'23.02.2000',2108,109,'FEA','112',10,1480.00)
insert into   commande  values (112997,'08.01.2000',2124,107,'BIC','41003',1,652.00)
insert into   commande  values (112983,'27.12.1999',2103,105,'ACI','41004',6,702.00)  
insert into   commande  values (113024,'20.01.2000',2114,102,'QSA','XK47',20,7100.00)
insert into   commande  values (113062,'24.02.2000',2124,107,'FEA','114',10,2430.00)
insert into   commande  values (112979,'12.10.1999',2114,102,'ACI','4100Z',6,15000.00)
insert into   commande  values (113027,'22.01.2000',2103,105,'ACI','41002',54,4104.00)   
insert into   commande  values (113007,'08.01.2000',2112,108,'IMM','773C',3,2925.00)
insert into   commande  values (113069,'02.03.2000',2109,103,'IMM','775C',22,31350.00)  
insert into   commande  values (113034,'29.01.2000',2107,110,'REI','2A45C',8,632.00)
insert into   commande  values (112992,'04.11.1999',2118,108,'ACI','41002',10,760.00)
insert into   commande  values (112975,'12.10.1999',2111,103,'REI','2A44G',6,2100.00)
insert into   commande  values (113055,'15.02.2000',2108,109,'ACI','4100X',6,150.00) 
insert into   commande  values (113048,'10.02.2000',2120,102,'IMM','779C',2,3150.00)
insert into   commande  values (112993,'04.01.1999',2106,102,'REI','2A45C',24,1896.00)
insert into   commande  values (113065,'27.02.2000',2106,102,'QSA','XK47',6,2130.00)
insert into   commande  values (113003,'25.01.2000',2108,109,'REI','2A44L',7,31500.00)  
insert into   commande  values (113049,'10.02.2000',2118,108,'QSA','XK47',2,776.00)
insert into   commande  values (112987,'31.12.1999',2103,105,'ACI','4100Y',11,27500.00)
insert into   commande  values (113057,'18.02.2000',2111,103,'ACI','4100X',24,600.00)
insert into   commande  values (113042,'02.02.2000',2113,104,'REI','2A44R',5,22500.00)
insert into   commande  values (115055,'20.03.2000',2113,104,'REI','2A44R',10,45000.00)

Merci de votre aide, je ne cherche pas à avoir forcement la réponse mais surtout des éléments de compréhension pour pouvoir me débloquer si possible

02/12/2008, 19h18
elsuket
Bonjour,

Lorsque vous devez écrire une requête, ne vous focalisez pas sur les données ni sur les colonnes dont vous avez besoin, mais concentrez vous uniquement sur les tables dans lesquelles vous allez rechercher ce que l'on vous demande.
Cela va vous permettre de spécifier les clauses FROM et JOIN.
Ceci fait, recherchez les colonnes dont vous avez besoin pour effectuer la requête, sans chercher leur ordre ni les calculs que vous avez peut-être à faire dessus.
Faites ensuite usage des fonctions dont vous avez besoin pour arriver au résultat escompté (mais ne faites pas un alambic ! :mouarf:)

Vous pouvez lire l'article de SQLPro sur les requêtes et les autres aussi ! :D

Voici votre requête :
Code:

1 2 3 4 5 6 7 8 SELECT P.LIB, P.QTESTK, SUM(C.QTE) AS [Quantité vendue] FROM dbo.PRODUIT P JOIN dbo.COMMANDE C ON C.NPROD = P.NPROD AND C.NUSINE = P.NUSINE GROUP BY P.LIB, P.QTESTK
Comme je vous l'ai dit, j'ai commencé par écrire :
Code:

1 2 FROM dbo.PRODUIT P JOIN dbo.COMMANDE C
puis :
Code:

1 2 ON C.NPROD = P.NPROD AND C.NUSINE = P.NUSINE
Parce que la clé primaire de la table PRODUIT est constituée par le couple de colonnes NPROD et NUSINE.

J'ai terminé par le SELECT. Comme la fonction SUM est une fonction d'aggrégation, nous devons spécifier la clause GROUP BY.

@++ ;)

Ah j'aurai du joindre ou j'en étais soit :
Code:

1 2 3 4 5 SELECT (P.NUSINE+P.NPROD) 'libellé produit' , P.QTESTK, sum(CMD.QTE) 'quantité vendue' FROM PRODUIT P, COMMANDE CMD WHERE p.nusine = cmd.nusine and p.nprod = cmd.nprod GROUP BY P.NUSINE + P.NPROD, P.QTESTK, CMD.QTE ORDER BY (P.NUSINE+P.NPROD)
Donc j'ai pu voir que j'avais fait l'erreur d'utiliser GROUP BY sur CMD.QTE qui du coup me poser problème :(

J'étais pas loin en tout cas de la même première approche que tu as faite... Mais si tu regardes bien l'extrait d'affichage que j'ai donner c'était pour justement montrer la chose qui me bloquer le plus :(

C'est qu'en procédant ainsi on se retrouve uniquement avec la liste des produits commandées hors il y a aussi des produits non commandés mais qui sont visiblement supprimer à cause du

Code:

WHERE p.nusine = cmd.nusine and p.nprod = cmd.nprod

me semble t'il ...

P.S : Merci pour le cours ça complète le livre que j'ai acheté du même auteur en plus pour compléter les cours du formateur par toujours évident à comprendre

Bonjour,
Citation:
Code:

1 2 3 4 5 SELECT (P.NUSINE+P.NPROD) 'libellé produit' , P.QTESTK, sum(CMD.QTE) 'quantité vendue' FROM PRODUIT P, COMMANDE CMD WHERE p.nusine = cmd.nusine AND p.nprod = cmd.nprod GROUP BY P.NUSINE + P.NPROD, P.QTESTK, CMD.QTE ORDER BY (P.NUSINE+P.NPROD)
Attention : en aucun cas une colonne n'est une valeur.
N'écrivez donc pas 'libellé produit' mais [libelle produit].

Avez-vous essayé la requête suivante :
Code:

1 2 3 4 5 SELECT (P.NUSINE+P.NPROD) 'libellé produit' , P.QTESTK, sum(CMD.QTE) 'quantité vendue' FROM PRODUIT P, COMMANDE CMD WHERE p.nusine = cmd.nusine AND p.nprod = cmd.nprod GROUP BY P.NUSINE, P.NPROD, P.QTESTK, CMD.QTE ORDER BY P.NUSINE, P.NPROD
Elle devrait être plus rapide si vous aviez un jeu de données dont le cardinal est élevé ...

@++ ;)

Je pense que je me suis mal exprimé car la pseudo réponse semble sur la bonne voie pour répondre a l'exercice cependant on remarque qu'on n'affiche nul part les produits qui ont pas été commandé du coup on répond pas à l'extrait d'affichage soit la réponse car cette ligne n'apparaitra jamais

Citation:

Libelle produit QTESTK Quantité vendue
--------------- -------- ---------------
ACI141001 277 -

avec cette requête :
Code:

1 2 3 4 5 SELECT (P.NUSINE+P.NPROD) [libellé produit] , P.QTESTK, sum(CMD.QTE) 'quantité vendue' FROM PRODUIT P, COMMANDE CMD WHERE p.nusine = cmd.nusine AND p.nprod = cmd.nprod GROUP BY P.NUSINE + P.NPROD, P.QTESTK ORDER BY (P.NUSINE+P.NPROD)
Ah c'est bon trouver c'était encore tout bête comme d'habitude :s
Code:

1 2 3 4 5 6 SELECT (P.NUSINE+P.NPROD) [libellé produit], P.QTESTK, isnull(convert(char,sum(CMD.QTE)),'_') 'Quantité Vendue' FROM PRODUIT P LEFT JOIN COMMANDE CMD ON p.nusine = cmd.nusine AND p.nprod = cmd.nprod GROUP BY P.NUSINE + P.NPROD, P.QTESTK ORDER BY (P.NUSINE+P.NPROD)