· 8 years ago · Apr 19, 2018, 07:26 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