· 8 years ago · Apr 19, 2018, 08:50 AM
1CREATE DATABASE IF NOT EXISTS bdproduits;
2
3use bdproduits;
4
5DROP TABLE IF EXISTS t_taxe ;
6DROP TABLE IF EXISTS t_produit ;
7DROP TABLE IF EXISTS t_type ;
8DROP TABLE IF EXISTS t_qualite ;
9DROP TABLE IF EXISTS t_devise ;
10DROP TABLE IF EXISTS t_societe ;
11
12CREATE TABLE t_societe
13(
14id_societe char(30) NOT NULL,
15id_ville char(40),
16constraint PK_societe PRIMARY KEY(id_societe)
17);
18
19CREATE TABLE t_devise
20(
21id_devise char(3) NOT NULL,
22devise char(30),
23valeur_ff float,
24constraint PK_devise PRIMARY KEY(id_devise)
25);
26
27CREATE TABLE t_qualite
28(
29id_qualite char(3) NOT NULL,
30qualite char(30),
31constraint PK_qualite PRIMARY KEY(id_qualite)
32);
33
34CREATE TABLE t_type
35(
36id_type char(3) NOT NULL,
37type char(30),
38constraint PK_type PRIMARY KEY(id_type)
39);
40
41CREATE TABLE t_taxe
42(
43id_type char(3) NOT NULL,
44id_qualite char(3) NOT NULL,
45id_taxe float,
46constraint PK_taxe PRIMARY KEY(id_type, id_qualite),
47constraint FK_taxe_type FOREIGN KEY(id_type)
48REFERENCES t_type(id_type),
49constraint FK_taxe_qualite FOREIGN KEY(id_qualite)
50REFERENCES t_qualite(id_qualite)
51);
52
53CREATE TABLE t_produit
54(
55id_produit integer NOT NULL,
56nom char(30),
57version char(10),
58id_societe char(30) NOT NULL,
59id_type char(3) NOT NULL,
60id_qualite char(3) NOT NULL,
61valeur_ht float,
62id_devise char(3) NOT NULL,
63constraint PK_produit PRIMARY KEY(id_produit),
64constraint FK_produit_societe FOREIGN KEY(id_societe)
65REFERENCES t_societe(id_societe),
66constraint FK_produit_qualite FOREIGN KEY(id_qualite)
67REFERENCES t_qualite(id_qualite),
68constraint FK_produit_type FOREIGN KEY(id_type)
69REFERENCES t_type(id_type),
70constraint FK_produit_devise FOREIGN KEY(id_devise)
71REFERENCES t_devise(id_devise)
72);
73
74DELETE FROM t_taxe ;
75DELETE FROM t_produit ;
76DELETE FROM t_type ;
77DELETE FROM t_qualite ;
78DELETE FROM t_devise ;
79DELETE FROM t_societe ;
80
81INSERT INTO t_societe
82VALUES ('Borland', 'Austin');
83INSERT INTO t_societe
84VALUES ('IBMFR', 'Bois-Colombes');
85INSERT INTO t_societe
86VALUES ('Microsoft', 'Seattle');
87INSERT INTO t_societe
88VALUES ('Mozilla', 'Mountain View');
89
90
91INSERT INTO t_devise
92VALUES ('EUR', 'Euros', 6.56);
93INSERT INTO t_devise
94VALUES ('FRF', 'Francs Français', 1);
95
96INSERT INTO t_qualite
97VALUES ('LVP', 'Low Value Product');
98INSERT INTO t_qualite
99VALUES ('HVP', 'High Value Product');
100INSERT INTO t_qualite
101VALUES ('BST', 'Absolute Best-seller');
102
103INSERT INTO t_type
104VALUES ('DBS', 'Data Base Server');
105INSERT INTO t_type
106VALUES ('OFP', 'Office Product');
107INSERT INTO t_type
108VALUES ('RAD', 'Rapid Application Development');
109INSERT INTO t_type
110VALUES ('NET', 'Internet Applications');
111
112INSERT INTO t_taxe
113VALUES ('RAD', 'HVP', 15.2);
114
115INSERT INTO t_produit
116VALUES(1, 'C++ Builder', '10.2', 'Borland', 'RAD', 'HVP', 1599, 'EUR');
117INSERT INTO t_produit
118VALUES(2, 'Access', '2016', 'Microsoft', 'DBS', 'HVP', 64.9, 'EUR');
119INSERT INTO t_produit
120VALUES(3, 'Access', '2013', 'Microsoft', 'DBS', 'BST', 44.9, 'EUR');
121INSERT INTO t_produit
122VALUES(4, 'Visual Basic', '6', 'Microsoft', 'RAD', 'LVP', NULL, 'FRF');
123INSERT INTO t_produit
124VALUES(5, 'Firefox', '59.0.2', 'Mozilla', 'NET', 'HVP', 1, 'EUR');
125INSERT INTO t_produit
126VALUES(6, 'Outlook', '2016', 'Microsoft', 'NET', 'HVP', 134.9, 'EUR');
127INSERT INTO t_produit
128VALUES(7, 'Notes', '9.0.1', 'IBMFR', 'NET', 'LVP', 4600, 'FRF');
129INSERT INTO t_produit
130VALUES(8, 'Excel', '2016', 'Microsoft', 'OFP', 'BST', 79.9, 'EUR');
131INSERT INTO t_produit
132VALUES(9, 'Word', '2016', 'Microsoft', 'OFP', 'BST', 900, 'FRF');
133INSERT INTO t_produit
134VALUES(10, 'Visual C++', '2017', 'Microsoft', 'RAD', 'HVP', 1099, 'EUR');
135
136-- Q2
137
138DELETE FROM t_taxe ;
139
140-- Q3
141
142INSERT INTO t_taxe SELECT id_type, id_qualite,15.2 FROM t_type, t_qualite;
143
144-- Q4
145
146-- il doit y avoir 12 lignes dans la table t_taxe
147
148SELECT * FROM t_taxe;
149-- DBS BST 15.2
150-- DBS HVP 15.2
151-- DBS LVP 15.2
152-- NET BST 15.2
153-- NET HVP 15.2
154-- NET LVP 15.2
155-- OFP BST 15.2
156-- OFP HVP 15.2
157-- OFP LVP 15.2
158-- RAD BST 15.2
159-- RAD HVP 15.2
160-- RAD LVP 15.2
161
162-- Q5
163
164UPDATE t_taxe
165SET id_taxe = 19.2
166WHERE id_qualite = 'BST';
167
168SELECT * FROM t_taxe;
169
170-- DBS BST 19.2
171-- DBS HVP 15.2
172-- DBS LVP 15.2
173-- NET BST 15.2
174-- NET HVP 15.2
175-- NET LVP 15.2
176-- OFP BST 19.2
177-- OFP HVP 15.2
178-- OFP LVP 15.2
179-- RAD BST 19.2
180-- RAD HVP 15.2
181-- RAD LVP 15.2
182
183
184
185-- Q6
186
187UPDATE t_taxe
188SET id_taxe = 20.2
189WHERE t_taxe.id_type = (SELECT id_type FROM t_type WHERE type = 'Internet Applications');
190
191SELECT * FROM t_taxe;
192
193-- DBS BST 19.2
194-- DBS HVP 15.2
195-- DBS LVP 15.2
196-- NET BST 20.2
197-- NET HVP 20.2
198-- NET LVP 20.2
199-- OFP BST 19.2
200-- OFP HVP 15.2
201-- OFP LVP 15.2
202-- RAD BST 19.2
203-- RAD HVP 15.2
204-- RAD LVP 15.2
205
206
207-- Q7
208
209SELECT * FROM t_taxe ORDER BY id_type ASC, id_qualite ASC;
210
211-- DBS BST 19.2
212-- DBS HVP 15.2
213-- DBS LVP 15.2
214-- NET BST 20.2
215-- NET HVP 20.2
216-- NET LVP 20.2
217-- OFP BST 19.2
218-- OFP HVP 15.2
219-- OFP LVP 15.2
220-- RAD BST 19.2
221-- RAD HVP 15.2
222-- RAD LVP 15.2
223
224-- Q8
225
226SELECT t_produit.nom, t_produit.version, t_type.type, t_qualite.qualite,t_taxe.id_taxe
227FROM t_produit,t_type,t_qualite,t_taxe
228WHERE t_produit.id_type = t_type.id_type
229 AND t_produit.id_qualite = t_qualite.id_qualite
230 AND t_taxe.id_type = t_produit.id_type
231 AND t_taxe.id_qualite = t_produit.id_qualite
232ORDER BY t_produit.nom ASC , t_produit.version ASC ;
233
234-- Access 2013 Data Base Server Absolute Best-seller 19.2
235-- Access 2016 Data Base Server High Value Product 15.2
236-- C++ Builder 10.2 Rapid Application Development High Value Product 15.2
237-- Excel 2016 Office Product Absolute Best-seller 19.2
238-- Firefox 59.0.2 Internet Applications High Value Product 20.2
239-- Notes 9.0.1 Internet Applications Low Value Product 20.2
240-- Outlook 2016 Internet Applications High Value Product 20.2
241-- Visual Basic 6 Rapid Application Development Low Value Product 15.2
242-- Visual C++ 2017 Rapid Application Development High Value Product 15.2
243-- Word 2016 Office Product Absolute Best-seller 19.2
244
245
246-- Q9
247
248SELECT t_produit.id_type, COUNT(id_type) FROM t_produit GROUP BY id_type DESC ;
249
250-- RAD 3
251-- OFP 2
252-- NET 3
253-- DBS 2
254
255
256-- Q10
257
258
259SELECT t_qualite.qualite, AVG(t_produit.valeur_ht * t_devise.valeur_ff * (1 + (t_taxe.id_taxe/100)))
260FROM t_produit, t_devise,t_taxe,t_qualite
261WHERE t_produit.valeur_ht IS NOT NULL
262 AND t_produit.id_devise = t_devise.id_devise
263 AND t_produit.id_type = t_taxe.id_type
264 AND t_produit.id_qualite = t_taxe.id_qualite
265 AND t_produit.id_qualite = t_qualite.id_qualite
266GROUP BY t_produit.id_qualite;
267
268-- Absolute Best-seller 682.8920414878743
269-- High Value Product 4390.230880032827
270-- Low Value Product 5529.200035095214
271
272
273-- Q11
274
275SELECT t_produit.nom, t_produit.version, t_produit.id_societe , 'oui'
276FROM t_produit
277WHERE t_produit.id_qualite = 'LVP'
278UNION
279SELECT t_produit.nom, t_produit.version, t_produit.id_societe , 'non'
280FROM t_produit
281WHERE id_qualite != 'LVP';
282
283-- Visual Basic 6 Microsoft oui
284-- Notes 9.0.1 IBMFR oui
285-- C++ Builder 10.2 Borland non
286-- Access 2016 Microsoft non
287-- Access 2013 Microsoft non
288-- Firefox 59.0.2 Mozilla non
289-- Outlook 2016 Microsoft non
290-- Excel 2016 Microsoft non
291-- Word 2016 Microsoft non
292-- Visual C++ 2017 Microsoft non
293
294-- Q12
295
296ALTER TABLE t_produit
297 DROP FOREIGN KEY FK_produit_societe;
298
299UPDATE t_produit
300SET id_societe ='Inprise'
301WHERE id_societe ='Borland';
302
303UPDATE t_societe
304SET id_societe ='Inprise'
305WHERE id_societe ='Borland';
306
307SELECT * FROM t_produit;
308
309-- 1 C++ Builder 10.2 Inprise RAD HVP 1599 EUR
310-- 2 Access 2016 Microsoft DBS HVP 64.9 EUR
311-- 3 Access 2013 Microsoft DBS BST 44.9 EUR
312-- 4 Visual Basic 6 Microsoft RAD LVP FRF
313-- 5 Firefox 59.0.2 Mozilla NET HVP 1 EUR
314-- 6 Outlook 2016 Microsoft NET HVP 134.9 EUR
315-- 7 Notes 9.0.1 IBMFR NET LVP 4600 FRF
316-- 8 Excel 2016 Microsoft OFP BST 79.9 EUR
317-- 9 Word 2016 Microsoft OFP BST 900 FRF
318-- 10 Visual C++ 2017 Microsoft RAD HVP 1099 EUR
319
320SELECT * FROM t_societe;
321
322-- IBMFR Bois-Colombes
323-- Inprise Austin
324-- Microsoft Seattle
325-- Mozilla Mountain View
326
327ALTER TABLE t_produit
328ADD FOREIGN KEY(id_societe)REFERENCES t_societe(id_societe);
329
330-- Q13
331
332
333ALTER TABLE t_produit
334 DROP FOREIGN KEY FK_produit_type;
335ALTER TABLE t_taxe
336 DROP FOREIGN KEY FK_taxe_type;
337
338UPDATE t_produit
339SET id_type = 'MUL'
340WHERE id_type ='NET';
341
342UPDATE t_type
343SET id_type = 'MUL'
344WHERE id_type ='NET';
345
346UPDATE t_taxe
347SET id_type = 'MUL'
348WHERE id_type ='NET';
349
350UPDATE t_type
351SET type = 'Multimedia Tools'
352WHERE id_type ='MUL';
353
354SELECT * FROM t_type;
355
356-- DBS Data Base Server
357-- MUL Multimedia Tools
358-- OFP Office Product
359-- RAD Rapid Application Development
360
361SELECT * FROM t_produit;
362
363-- 1 C++ Builder 10.2 Inprise RAD HVP 1599 EUR
364-- 2 Access 2016 Microsoft DBS HVP 64.9 EUR
365-- 3 Access 2013 Microsoft DBS BST 44.9 EUR
366-- 4 Visual Basic 6 Microsoft RAD LVP FRF
367-- 5 Firefox 59.0.2 Mozilla MUL HVP 1 EUR
368-- 6 Outlook 2016 Microsoft MUL HVP 134.9 EUR
369-- 7 Notes 9.0.1 IBMFR MUL LVP 4600 FRF
370-- 8 Excel 2016 Microsoft OFP BST 79.9 EUR
371-- 9 Word 2016 Microsoft OFP BST 900 FRF
372-- 10 Visual C++ 2017 Microsoft RAD HVP 1099 EUR
373
374SELECT * FROM t_taxe;
375
376-- DBS BST 19.2
377-- DBS HVP 15.2
378-- DBS LVP 15.2
379-- MUL BST 20.2
380-- MUL HVP 20.2
381-- MUL LVP 20.2
382-- OFP BST 19.2
383-- OFP HVP 15.2
384-- OFP LVP 15.2
385-- RAD BST 19.2
386-- RAD HVP 15.2
387-- RAD LVP 15.2
388
389
390ALTER TABLE t_produit
391ADD FOREIGN KEY(id_type)REFERENCES t_type(id_type);
392
393ALTER TABLE t_taxe
394ADD FOREIGN KEY(id_type)REFERENCES t_type(id_type);