· 5 years ago · Mar 26, 2020, 07:24 AM
1CREATE VIEW entreesAll AS
2SELECT p.id AS idProduit, o.idUnite, SUM(quantite*volume) as vol
3FROM operation o JOIN produit p ON o.idProduit = p.id
4WHERE nature = 'entree'
5GROUP BY o.idUnite,p.id;
6
7
8
9CREATE VIEW V1 AS
10SELECT ville.nom
11FROM ville join pays on ville.id_pays=pays.id
12WHERE pays.nom='Italie';
13
14CREATE VIEW V2 AS
15SELECT ville.nom, pays.nom
16FROM pays join ville on ville.id_pays=pays.id
17JOIN riviere on pays.id_riviere=riviere.id
18JOIN border on border.id_pays = ville.id_pays
19join mer on border.id_mer=mer.id
20WHERE mer.nom='manche';
21
22CREATE VIEW V3 AS
23select riviere.nom, count(ville.id)
24FROM ville join riviere
25on ville.id_riviere=riviere.id
26group by riviere.nom;
27
28CREATE VIEW V4 AS
29select riviere.nom
30from riviere join ville on ville.id_riviere=riviere.id
31having count(ville.id)>2;
32
33CREATE VIEW V5 AS
34SELECT pays.nom
35from pays join ville on ville.id_pays=pays.id
36WHERE nbHabitants>1000000;
37
38CREATE VIEW V6 AS
39SELECT mer.nom, count(riviere.id)
40FROM mer join riviere on riviere.id_riviere=riviere.id
41join border on border.id_mer=riviere.id_mer
42GROUP BY mer.nom
43HAVING (riviere.longueur)>400;
44
45CREATE VIEW V7 AS
46select nom
47from pays
48where not exist
49(select * from ville where id_pays = id);
50
51CREATE VIEW V8 AS
52select r2.nom
53from riviere as r1 join riviere as r2 on r1.id_riviere = r2.id
54where r1.longueur > 200
55group by r2.id;
56
57
58------------
59-- faire grant, fonction?, insert update delete etc
60
61GRANT USAGE
62ON SCHEMA géographie
63TO toto;
64
65GRANT INSERT(nom)
66ON TABLE mer
67TO toto;
68
69GRANT SELECT
70on V4
71to toto;
72
73grant update(longueur)
74on TABLE riviere
75to toto;
76
77REVOKE SELECT
78ON V4
79FROM toto;
80
81grant SELECT (nom,nbHabitants)
82on TABLE ville
83to toto;
84
85grant delete
86ON TABLE riviere
87to toto;
88
89REVOKE DELETE,UPDATE(longueur)
90ON TABLE riviere
91FROM toto;
92
93REVOKE SELECT(nom,nbHabitants)
94ON TABLE ville
95FROM toto;
96
97REVOKE INSERT(nom)
98ON TABLE mer
99FROM toto;
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141--------- help
142
143//Gestion de table
144
145CREATE TABLE nom_table(
146type nom attributs);
147
148DROP TABLE nom_table CASCADE;
149
150INSERT INTO nom_table VALUES(valeur1, valeur2, valeurn);
151INSERT INTO nom_table requete_SQL;
152
153UPDATE TABLE
154 SET colonne = expression
155 WHERE condition;
156
157DELETE FROM TABLE
158WHERE condition;
159
160TRUNCATE nom_table RESTART IDENTITY CASCADE;
161
162//Gestion des privilèges
163
164GRANT CONNECT
165ON database nom_base
166TO nom_utilisateur;
167
168GRANT USAGE, CREATE
169ON SCHEMA nom_schema
170TO nom_utilisateur;
171
172GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
173ON TABLE nom_table
174TO nom_utilisateur;
175
176GRANT USAGE
177ON sequence nom_sequence
178TO nom_utilisateur;
179
180REVOKE <privilege>
181ON <object>
182FROM nom_utilisateur
183
184//Gestion des vues
185
186CREATE view nom_vue AS
187requete_SQL;
188
189DROP VIEW nom_vue;
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204-----gestion données
205
206insert into module values (default, 'INFO', '2018-09-05','2019-01-31');
207
208insert into prof values (default,'Dupond','Kevin','titulaire');
209
210insert into matiere values
211 (default, 'algo',null,1,1),
212 (default, 'bd',null,1,1);
213
214-- 3.2 : multiplier par 1.2 les notes des examens ayant eu lieu le 25-oct-2018
215update evaluation
216set note = note * 1.2
217where idExamen in (select idExamen from examen where dateExamen = '2018-10-25');
218
219-- 3.3 : supprimer les étudiants sans matiere ni évaluation.
220delete from etudiant
221where idEtudiant not in
222 (select idEtudiant from suivre
223 union
224 select idEtudiant from evaluation
225);
226
227
228
229
230
231
232
233
234
235
236
237
238
239----------creation table
240
241
242drop schema if exists geographie cascade;
243create schema geographie;
244set search_path to geographie;
245
246create table mer
247(
248 id serial primary key,
249 nom varchar(50) not null unique
250);
251
252
253create table riviere
254(
255 id serial primary key,
256 nom varchar(50) not null,
257 longueur decimal not null,
258 id_riviere int references riviere(id)
259 on delete set null
260 on update cascade,
261 id_mer int references mer(id)
262 on update cascade,
263 check (id_mer is not null or id_riviere is not null),
264 check (id_riviere != id),
265 check (longueur > 0)
266);
267
268
269create table pays
270(
271 id serial primary key,
272 nom varchar(50) not null,
273 superficie int not null,
274 check (superficie > 0)
275);
276
277
278create table voisin
279(
280 id_pays1 int references pays(id)
281 on delete cascade
282 on update cascade,
283 id_pays2 int references pays(id)
284 on delete cascade
285 on update cascade,
286 primary key (id_pays1, id_pays2),
287 check (id_pays1 != id_pays2) -- => id_pays1 < id_pays2
288);
289
290
291create table border
292(
293 id_pays int references pays(id)
294 on delete cascade
295 on update cascade,
296 id_mer int references mer(id)
297 on delete cascade
298 on update cascade,
299 primary key (id_pays, id_mer)
300);
301
302create table ville
303(
304 id serial primary key,
305 nom varchar(50) not null,
306 nbHabitants int not null,
307 id_riviere int references riviere(id)
308 on delete set null
309 on update cascade,
310 id_pays int references pays(id)
311 on update cascade,
312 check (nbHabitants > 0),
313 unique (nom, id_pays)
314);
315
316\i data.sql