7iKZb4Cq

1#
2# Skrypty przykładowe do przedmiotu RBD - Wykład06 (VIEWS & TRIGGERS)
3#      (c) Wojciech Mościbrodzki
4#      wyłącznie dla moich ulubionych studentów PJWSTK Gdańsk :-)
5#
6
7#
8# struktury do pracy z uniami
9# 
10
11drop table if exists faktury_historyczne;
12drop table if exists faktury_biezace;
13
14
15create table faktury_historyczne (
16  id int auto_increment primary key,  
17  data_zakupu date,
18  wartosc numeric(10,2),
19  VAT numeric(8,2),
20  id_kli int
21);
22
23create table faktury_biezace (
24  id int auto_increment primary key,  
25  data_zakupu date,
26  wartosc numeric(10,2),
27  VAT numeric(8,2),
28  id_kli int
29);
30
31insert into faktury_historyczne values (1,'2009-04-05',6000,7320,1);
32insert into faktury_historyczne values (2,'2010-05-10',3000,3660,1);
33insert into faktury_historyczne values (3,'2008-11-12',15000,18300,2);
34insert into faktury_historyczne values (4,'2009-10-10',2000,2440,3);
35
36insert into faktury_biezace values (1,'2011-05-09',7000,8610,1);
37insert into faktury_biezace values (2,'2011-06-01',1000,1230,4);
38
39select * from faktury_historyczne union select * from faktury_biezace;
40
41select sum(wartosc) from (select * from faktury_historyczne union select * from faktury_biezace) as sb;
42
43
44#
45# struktury do pracy z widokami
46#
47
48drop table if exists faktura;
49drop table if exists linia;
50drop table if exists towar;
51drop table if exists sprzedawca;
52drop table if exists kupujacy;
53drop table if exists miasto;
54
55create table faktura (
56  id int auto_increment primary key,
57  numer char(10),
58  data_zakupu date,
59  id_kup int,
60  id_spr int
61);
62
63create table kupujacy (
64  id int auto_increment primary key,
65  nazwa char(15),
66  id_mia int
67);
68
69create table sprzedawca (
70  id int auto_increment primary key,
71  imie char(15),
72  nazwisko char(25),
73  placa int,
74  id_mia int
75);
76
77create table miasto (
78  id int auto_increment primary key,
79  nazwa char(15)
80);
81
82create table linia (
83  id int auto_increment primary key,
84  ilosc numeric(10,2),
85  id_tow int,
86  id_fak int
87);
88
89create table towar (
90  id int auto_increment primary key,
91  nazwa char(15),
92  cena numeric(10,2)
93);
94
95insert into towar values (1,'Paliwo ON',4.78);
96insert into towar values (2,'Paliwo 98',5.01);
97insert into towar values (3,'Paliwo 95',4.92);
98insert into towar values (4,'Polityka',5.00);
99insert into towar values (5,'Przekroj',5.00);
100insert into towar values (6,'Batonik',3.99);
101insert into towar values (7,'Woda mineralna',2.10);
102insert into towar values (8,'Szmatka',2.00);
103insert into towar values (9,'Papierosy LM',5.50);
104insert into towar values (10,'Wino Malaga',52.00);
105insert into towar values (11,'Martini',33.00);
106insert into towar values (12,'Pianka',17.00);
107insert into towar values (13,'Ciastko',7.00);
108insert into towar values (14,'Kawa',4.50);
109insert into towar values (15,'Cappucino',3.50);
110insert into towar values (16,'Herbata',4.30);
111insert into towar values (17,'Hotdog',7.50);
112insert into towar values (18,'Pepsi',5.30);
113insert into towar values (19,'Coca-Cola',5.40);
114insert into towar values (20,'Tik-Tak',3.20);
115
116insert into miasto values (1,'Poznan');
117insert into miasto values (2,'Krakow');
118insert into miasto values (3,'Gdansk');
119insert into miasto values (4,'Warszawa');
120insert into miasto values (5,'Szczecin');
121insert into miasto values (6,'Tczew');
122insert into miasto values (7,'Sanok');
123insert into miasto values (8,'Radom');
124
125insert into kupujacy values (1,'Alfa SA',1);
126insert into kupujacy values (2,'Beta SA',1);
127insert into kupujacy values (3,'Gamma SA',2);
128insert into kupujacy values (4,'Dzeta SA',3);
129insert into kupujacy values (5,'Theta SA',4);
130insert into kupujacy values (6,'Alef SA',4);
131insert into kupujacy values (7,'Phi SA',5);
132insert into kupujacy values (8,'Ypsylon SA',6);
133insert into kupujacy values (9,'Sigma SA',7);
134
135insert into sprzedawca values (1,'Jan','Nowak',1800,1);
136insert into sprzedawca values (2,'Piotr','Kuna',1600,1);
137insert into sprzedawca values (3,'Ewa','Trus',1900,2);
138insert into sprzedawca values (4,'Iza','Pokora',2100,7);
139insert into sprzedawca values (5,'Kasia','Gisz',2100,7);
140insert into sprzedawca values (6,'Janusz','Wist',1900,4);
141insert into sprzedawca values (7,'Stefan','Kunera',1700,2);
142insert into sprzedawca values (8,'Marek','Pokora',1800,1);
143
144insert into faktura values (1, 'FV3434531',  '2003-10-12',1,1);
145insert into faktura values (2, 'FV3497971',  '2007-11-10',2,1);
146insert into faktura values (3, 'FV3543322',  '2004-11-13',3,2);
147insert into faktura values (4, 'FV2434531',  '2005-05-19',4,2);
148insert into faktura values (5, 'FV8076531',  '2013-03-22',5,3);
149insert into faktura values (6, 'FV3445337',  '2013-05-21',6,3);
150insert into faktura values (7, 'FV3434666',  '2012-08-10',1,4);
151insert into faktura values (8, 'FV4674531',  '2011-09-25',2,4);
152insert into faktura values (9, 'FV3457891',  '2010-10-21',3,5);
153insert into faktura values (10, 'FV3444461', '2008-12-19',3,5);
154insert into faktura values (11, 'FV3499991', '2006-12-17',4,6);
155insert into faktura values (12, 'FV0909031', '2003-10-05',5,7);
156insert into faktura values (13, 'FV9098981', '2007-12-23',6,8);
157insert into faktura values (14, 'FV3438881', '2008-10-14',7,9);
158insert into faktura values (15, 'FV3888531', '2008-07-17',8,5);
159insert into faktura values (16, 'FV3908888', '2004-12-10',9,1);
160insert into faktura values (17, 'FV1138881', '2007-10-14',7,9);
161insert into faktura values (18, 'FV2333531', '2012-07-17',8,5);
162insert into faktura values (19, 'FV3911188', '2012-12-10',9,1);
163
164insert into linia values (1,48.2,1,1);
165insert into linia values (2,30.5,2,2);
166insert into linia values (3,27.3,3,3);
167insert into linia values (4,15.3,1,4);
168insert into linia values (5,40.9,2,5);
169insert into linia values (6,52.4,3,6);
170insert into linia values (7,38.2,1,7);
171insert into linia values (8,29.2,2,8);
172insert into linia values (9,25.5,3,9);
173insert into linia values (10,1,5,1);
174insert into linia values (11,1,6,2);
175insert into linia values (12,1,6,2);
176insert into linia values (13,1,6,3);
177insert into linia values (14,1,7,3);
178insert into linia values (15,1,7,4);
179insert into linia values (16,1,8,5);
180insert into linia values (17,1,9,6);
181insert into linia values (18,2,10,6);
182insert into linia values (19,4,11,6);
183insert into linia values (20,1,11,7);
184insert into linia values (21,1,13,8);
185insert into linia values (22,1,13,9);
186insert into linia values (23,1,12,10);
187insert into linia values (24,1,12,10);
188insert into linia values (25,2,14,11);
189insert into linia values (26,2,14,11);
190insert into linia values (27,1,14,12);
191insert into linia values (28,1,14,12);
192insert into linia values (29,3,15,13);
193insert into linia values (30,3,15,13);
194insert into linia values (31,4,16,13);
195insert into linia values (32,1,17,14);
196insert into linia values (33,1,18,15);
197insert into linia values (34,4,19,15);
198insert into linia values (35,1,13,15);
199insert into linia values (36,1,13,16);
200insert into linia values (37,1,13,16);
201insert into linia values (38,12,14,17);
202insert into linia values (39,10,11,17);
203insert into linia values (40,11,11,18);
204insert into linia values (41,1,5,18);
205insert into linia values (42,7,6,19);
206insert into linia values (43,6,7,19);
207insert into linia values (44,1,8,19);
208
209drop view if exists kadra;
210drop view if exists kadra_agr;
211
212create view kadra as 
213  select imie, nazwisko, nazwa from 
214  sprzedawca left outer join miasto on (sprzedawca.id_mia=miasto.id);
215
216create view kadra_agr as 
217  select nazwa, count(sprzedawca.id) as ile, avg(placa) from 
218  sprzedawca left outer join miasto on (sprzedawca.id_mia=miasto.id) group by miasto.id;
219
220drop table if exists pracownik;  
221drop table if exists stanowisko;  
222  
223create table pracownik (
224  id int auto_increment primary key,
225  imie char(15),
226  nazwisko char(25),
227  placa int,
228  id_sta int
229);
230
231create table stanowisko (
232  id int auto_increment primary key,
233  nazwa char(25)
234);
235
236insert into stanowisko values (1, 'dyrektor');
237insert into stanowisko values (2, 'manager');
238insert into stanowisko values (3, 'specjalista');
239insert into stanowisko values (4, 'referent');
240
241insert into pracownik values (1,'Marek','Jackowski',5600,2);
242insert into pracownik values (2,'Roman','Rendyk',6000,2);
243insert into pracownik values (3,'Ewa','Tyrzyk',8000,1);
244insert into pracownik values (4,'Tomasz','Pass',6200,2);
245insert into pracownik values (5,'Bartosz','Nowacki',2000,3);
246insert into pracownik values (6,'Wojciech','Kurtyl',2200,4);
247insert into pracownik values (7,'Marek','Wiemanns',1500,4);
248
249drop view if exists junior_staff;
250
251create view junior_staff as select imie, nazwisko, nazwa, placa from pracownik left outer join stanowisko on (pracownik.id_sta = stanowisko.id) where not (nazwa like 'dyrektor' or nazwa like 'manager');
252
253create user bazak;
254grant all on rbd6.junior_staff to bazak;
255
256drop view if exists banal;
257create view banal as select * from pracownik;
258select * from banal;
259alter table pracownik add column plec char(1) default 'm';
260update pracownik set plec='k' where id=3;
261
262#
263# przykład widoku, który umożliwia aktualizację tabel bazowych (updateable view)
264#
265
266drop table if exists student; 
267drop table if exists miasto;  
268
269create table miasto (
270  idm int auto_increment primary key,
271  nazwa char(25)
272);
273  
274create table student (
275  ids int auto_increment primary key,
276  nazwisko char(25),
277  indeks char(5),
278  id_mia int,
279  FOREIGN KEY (id_mia) REFERENCES miasto(id) ON UPDATE CASCADE
280);
281
282insert into miasto values (1,'Warszawa');
283insert into miasto values (2,'Tczew');
284insert into student values (1,'Kowal','s3472',1);
285insert into student values (2,'Nowak','s1231',2);
286insert into student values (3,'Kopek','s2945',2);
287
288create view V1 as select * from student left outer join miasto on (student.id_mia=miasto.idm);
289
290#
291# struktury do analizy możliwości wstawiania (insertable view) 
292#    V2 - TEN WIDOK NIE UMOŻLIWIA WSTAWIENIA
293#
294
295drop view if exists V2;
296drop table if exists student; 
297drop table if exists miasto;  
298
299
300create table miasto (
301  idm int auto_increment primary key,
302  nazwa char(25)
303);
304  
305create table student (
306  ids int auto_increment primary key,
307  imie char(20),
308  nazwisko char(25),
309  id_mia int,
310  FOREIGN KEY (id_mia) REFERENCES miasto(idm) ON UPDATE CASCADE
311);
312
313insert into miasto values (1,'Warszawa');
314insert into miasto values (2,'Tczew');
315insert into student values (1,'Jan','Kowal',1);
316insert into student values (2,'Iza','Nowak',2);
317
318create view V2 as select nazwa, imie, nazwisko from student left outer join miasto on (student.id_mia=miasto.idm);
319
320insert into V2(nazwa, imie, nazwisko, id_mia) values ('Ewa','Peszek',1);
321
322
323#
324# struktury do analizy możliwości wstawiania (insertable view) 
325#    V3 - TEN WIDOK  UMOŻLIWIA WSTAWIANIE
326#
327
328drop view if exists V3;
329drop table if exists student; 
330drop table if exists miasto;  
331
332create table miasto (
333  idm int auto_increment primary key,
334  nazwa char(25)
335);
336  
337create table student (
338  ids int auto_increment primary key,
339  imie char(20),
340  nazwisko char(25),
341  id_mia int,
342  FOREIGN KEY (id_mia) REFERENCES miasto(idm) ON UPDATE CASCADE
343);
344
345insert into miasto values (1,'Warszawa');
346insert into miasto values (2,'Tczew');
347insert into student values (1,'Jan','Kowal',1);
348insert into student values (2,'Iza','Nowak',2);
349
350create view V3 as select imie, nazwisko from student;
351
352insert into V3(imie, nazwisko) values ('Ewa','Peszek');