· 6 years ago · Apr 21, 2019, 10:44 AM
1
2
3Nous avons utilisé Mysql donc nous avons changé dans les fichiers la syntaxe ainsi que une ligne qui avait une clé primaire redondante dans l’insertion de la table VENTE.
4
5
6
7Q2 )
8CREATE VIEW VENTE_B AS SELECT V.Idp, V.Idm, V.DateV, B.Prix
9FROM vente V , bareme B
10WHERE V.IdP = B.IdP AND V.IdM = B.IdM AND
11B.DateB = (SELECT MAX(DateB)
12 FROM bareme B2
13 WHERE V.IdP = B2.IdP AND V.IdM = B2.IdM
14 AND DateB <= V.DateV)
15
16Q3) MINUS n’existant pas sous Mysql on le simule
17
18SELECT v.IdP,v.IdM,v.DateV
19FROM vente v LEFT JOIN vente_b vb on v.IdP = vb.Idp AND v.IdM = vb.Idm AND v.DateV = vb.DateV
20WHERE Prix IS NULL
21Q4 )
22INSERT INTO bareme
23SELECT idP , idM, "1000-01-01","0"
24FROM (SELECT DISTINCT v.IdP,v.IdM
25FROM vente v LEFT JOIN vente_b vb on v.IdP = vb.Idp AND v.IdM = vb.Idm AND v.DateV = vb.DateV
26WHERE Prix IS NULL
27) bs
28Q5)
29
30SELECT SUM(Quantite * Prix) as chiffre
31FROM vente_b v JOIN magasin m on v.idM = m.IdM
32WHERE v.idM="123"
33Resultat = 4579.86
34
35
36Q6)
37
38SELECT v.idm, SUM(Quantite * Prix) as chiffre
39FROM vente_b v JOIN magasin m on v.idM = m.IdM
40GROUP BY v.Idm
41
42Q7)
43SELECT v.idm,m.Nom,p.CodeCat, SUM(Quantite * Prix) as chiffre
44FROM vente_b v JOIN magasin m on v.idM = m.IdM JOIN produit p on v.Idp = p.IdP
45GROUP BY v.Idm,p.CodeCat
46
47Q8)
48SELECT CONCAT(REPEAT(' ', level - 1), idl) AS fils, parent, level
49FROM (
50 SELECT _id AS idl,parent,
51 @cl := @cl + 1 AS level
52 FROM (
53 SELECT @r AS _id,
54 (
55 SELECT @r := idLrattach
56 FROM lieu
57 WHERE idl = _id
58 ) AS parent,
59 @l := @l + 1 AS level
60 FROM (
61 SELECT @r := (SELECT idl from magasin WHERE idm="123"),
62 @l := 0,
63 @cl := 0
64 ) vars,
65 lieu h
66 WHERE @r <> 0
67 ORDER BY
68 level DESC
69 ) qi
70 ) qo
71
72Q9) à faire en 3 étape
731 ere étape
74DROP PROCEDURE IF EXISTS fdesidl;
75DELIMITER //
76
77
78
79SET @@GLOBAL.max_sp_recursion_depth = 255//
80CREATE PROCEDURE fdesidl(IN id INT)
81BEGIN
82
83 DECLARE ccount INT;
84 DECLARE nb INT;
85 DECLARE i INT DEFAULT 0;
86 SET i =0;
87 SELECT COUNT(*) INTO ccount FROM lieu WHERE idlrattach=id;
88
89 CREATE TABLE IF NOT EXISTS tempo(
90 descendant INT PRIMARY KEY
91 );
92 INSERT INTO tempo VALUES(id);
93 IF ccount >0 THEN
94 -- SELECT * FROM lieu WHERE idlrattach=id;
95
96
97 WHILE i < ccount DO
98 SET nb=(SELECT IdL FROM lieu WHERE idlrattach=id LIMIT i,1);
99 CALL fdesidl(nb);
100
101 SET i=i+1;
102 END WHILE;
103 END iF;
104
105END//
106
107DELIMITER ;
108
109
110CALL fdesidl(3);
111
1122 ème étape
113
114SELECT SUM(quantite)
115FROM vente
116WHERE idp="20" AND idm IN (SELECT idm FROM magasin WHERE idl IN (SELECT * from tempo))
1173 ème étape
118TRUNCATE TABLE tempo;
119Q10)
120SELECT v.idm
121FROM vente v,produit p
122WHERE v.datev="2013/10/10" AND v.idp=p.idp
123GROUP by v.idm
124HAVING count(DISTINCT codecat)=(SELECT count( codecat) FROM categorie WHERE codecat NOT IN (SELECT codecatgen from categorie))
125
126
127Q11)
128
129
130SELECT marge
131FROM(SELECT b.idm,b.idp,(Max(b.prix)-p.prixachat) as marge,Max(b.prix),p.prixachat
132FROM bareme b,produit p
133WHERE b.idp=p.idp AND b.idp="13"
134GROUP by b.idm, b.idp
135ORDER by marge DESC) as margeT
136LIMIT 10
137
138
139
140Q12)
141
142Donne la plus petite marge de chaque produit
143(SELECT b.idp,min(b.prix - p.prixachat) as marge
144FROM bareme b JOIN produit p on b.idp=p.IdP
145GROUP by b.idp)
146
147Marge par produit par magasin
148SELECT b.idm, b.idp,b.prix,p.prixachat,min(b.prix - p.prixachat) as marge
149FROM bareme b JOIN produit p on b.idp=p.IdP
150GROUP by idp,idm
151
152
153Tout :
154SELECT allp.idp, allp.idm, allp.marge
155FROM (SELECT b.idp,min(b.prix - p.prixachat) as marge
156FROM bareme b JOIN produit p on b.idp=p.IdP
157GROUP by b.idp) as bp , (SELECT b.idm, b.idp,b.prix,p.prixachat,min(b.prix - p.prixachat) as marge
158FROM bareme b JOIN produit p on b.idp=p.IdP
159GROUP by idp,idm) allp
160WHERE allp.idp = bp.idp AND allp.marge=bp.marge
161
162
163Q13)
164Plus petite marge sur un produit par magasin
165(SELECT b.idM,min(b.prix - p.prixachat) as marge
166FROM bareme b JOIN produit p on b.idp=p.IdP
167GROUP by b.idM)
168
169Marge par produit par magasin
170SELECT b.idm, b.idp,b.prix,p.prixachat,min(b.prix - p.prixachat) as marge
171FROM bareme b JOIN produit p on b.idp=p.IdP
172GROUP by idp,idm
173
174Le produit qui à la plut petite marge pour chaque magasin
175SELECT allp.idp, allp.idm, allp.marge
176FROM (SELECT b.idM,min(b.prix - p.prixachat) as marge
177FROM bareme b JOIN produit p on b.idp=p.IdP
178GROUP by b.idM
179) as bp , (SELECT b.idm, b.idp,b.prix,p.prixachat,min(b.prix - p.prixachat) as marge
180FROM bareme b JOIN produit p on b.idp=p.IdP
181GROUP by idp,idm) allp
182WHERE allp.idm = bp.idm AND allp.marge=bp.marge
183
184Q14)
185
186SELECT idm,avg(marge)
187FROM(SELECT b.idm, b.idp,b.prix,p.prixachat,min(b.prix - p.prixachat) as marge
188FROM bareme b JOIN produit p on b.idp=p.IdP
189GROUP by idm,idp) ap
190GROUP BY idm
191
192Q15)
193
194SELECT v.idp,p.libelle,SUM(v.Quantite) as nb
195FROM vente v JOIN produit p on v.IdP=p.IdP
196GROUP BY v.IdP,p.Libelle
197ORDER by nb DESC
198LIMIT 1
199
200
201
202Q16) à faire en 3 étape
203
2041 ere étape
205
206SELECT @code :=codecat FROM categorie WHERE designation="Mobilier";
207SELECT @code;
208
209DROP PROCEDURE IF EXISTS fdesidl;
210DELIMITER //
211
212
213
214SET @@GLOBAL.max_sp_recursion_depth = 255//
215CREATE PROCEDURE fdesidl(IN id INT)
216BEGIN
217
218 DECLARE ccount INT;
219 DECLARE nb INT;
220 DECLARE i INT DEFAULT 0;
221 SET i =0;
222 SELECT COUNT(*) INTO ccount FROM categorie WHERE CodeCatGen=id;
223
224 CREATE TABLE IF NOT EXISTS tempo(
225 descendant INT PRIMARY KEY
226 );
227 INSERT INTO tempo VALUES(id);
228 IF ccount >0 THEN
229
230
231
232 WHILE i < ccount DO
233 SET nb=(SELECT CodeCat FROM categorie WHERE CodeCatGen=id LIMIT i,1);
234 CALL fdesidl(nb);
235
236 SET i=i+1;
237 END WHILE;
238 END iF;
239
240END//
241
242DELIMITER ;
243
244CALL fdesidl(@code);
2452 eme étape
246
247SELECT SUM(quantite)
248FROM vente v JOIN produit p on v.IdP=p.IdP
249WHERE p.CodeCat IN (SELECT * FROM tempo);
250
2513 eme étape
252
253TRUNCATE TABLE tempo;
254
255
256Q17)
257SELECT idl
258FROM magasin
259WHERE idl NOT IN
260(SELECT DISTINCT id
261FROM(SELECT *
262FROM (SELECT DISTINCT Type FROM magasin ORDER BY Type) t INNER JOIN ( SELECT DISTINCT idl as id FROM magasin m ) l) pc
263WHERE (id,type) NOT IN (SELECT idl,type FROM magasin))
264
265Q18)
266Q19)
267SELECT v.idm,p.codecat,SUM(v.Prix*v.Prix)
268FROM vente_b v JOIN produit p on v.Idp=p.IdP
269GROUP BY idm,p.CodeCat
270Q20)
271
272Q21) le critère de meilleur vente choisis est la quantite
273
274
275
276Le plus de vente d’un produit (par magasin) :
277SELECT unitmag.idm, unitmag.idp,unitmag.nb
278FROM(SELECT Idp,Max(nb) as m
279FROM(SELECT v.idm,v.idp,SUM(v.Quantite) as nb
280FROM vente_b v
281GROUP BY idp,idm) as unitmag
282GROUP by Idp) as bigipd ,(SELECT v.idm,v.idp,SUM(v.Quantite) as nb
283FROM vente_b v
284GROUP BY idp,idm) as unitmag
285WHERE unitmag.idp=bigipd.Idp AND unitmag.nb=bigipd.m
286
287
288Les magasin qui on atteint la meilleure vente journaliere ( par praduit/un produit) :
289SELECT v.Idp,v.Idm
290FROM(SELECT idp, max(Quantite) as nb
291FROM vente_b v
292GROUP BY idp) as mv JOIN vente_b v on mv.idp = v.Idp AND mv.nb=v.Quantite
293
294Etant donné (la redondance)le fait qu’il y ai à la fois plusieurs meilleur magasin vendeur en journalier comme en quantité total tout faire en une requête donnerais un résultat laid ( plein de répétition)
295
296Q22)interne : convaincre un client pour des futurs contrat ou gagner en réputation,gagner de l’argent, gagner des parts de marcher
297Externe : gagner de l’argent , prendre les parts d’un marché,meilleur gestion des prix
298
299Q23) plus de bénéfices
300Q24) avoir des ventes sans prix dans les données,des nœuds non rataché au reste de l’arbre comme pour lieu,magasin,categorie,codecat
301Q25) le temps (variation des prix )
302 Les lieux
303 Les catégories
304Q26) tout les jours de préférence la nuit
305Q27)variation de prix , nombre de ventes, lieu, temps( saison)