· 8 years ago · May 06, 2018, 07:52 PM
1DROP TABLE Hrac CASCADE CONSTRAINTS;
2DROP TABLE Klan CASCADE CONSTRAINTS;
3DROP TABLE Vybaveni CASCADE CONSTRAINTS;
4DROP TABLE Vybaveni_hrace CASCADE CONSTRAINTS;
5DROP TABLE Hraci_klanu CASCADE CONSTRAINTS;
6DROP TABLE Zamereni_hracu CASCADE CONSTRAINTS;
7DROP TABLE Hraci_tymu CASCADE CONSTRAINTS;
8DROP TABLE Zamereni_tymu CASCADE CONSTRAINTS;
9DROP TABLE Zapasy_hracu CASCADE CONSTRAINTS;
10DROP TABLE Zapas CASCADE CONSTRAINTS;
11DROP TABLE Asistence CASCADE CONSTRAINTS;
12DROP TABLE Umrti CASCADE CONSTRAINTS;
13DROP TABLE Archiv_umrti CASCADE CONSTRAINTS;
14DROP TABLE Zapasy_tymu CASCADE CONSTRAINTS;
15DROP TABLE Tym CASCADE CONSTRAINTS;
16DROP TABLE Hra CASCADE CONSTRAINTS;
17DROP TABLE Mody_hry CASCADE CONSTRAINTS;
18DROP TABLE Herni_mod CASCADE CONSTRAINTS;
19DROP TABLE Turnaj CASCADE CONSTRAINTS;
20DROP TABLE Sponzor CASCADE CONSTRAINTS;
21DROP TABLE Sponzori_turnaje CASCADE CONSTRAINTS;
22
23DROP SEQUENCE hrac_seq;
24
25DROP TRIGGER bi_hrac;
26DROP TRIGGER bu_hra_nazev;
27
28DROP PROCEDURE archivace_umrti;
29DROP PROCEDURE smazani_tymu;
30
31DROP INDEX index_hraci_tymu;
32
33
34-- Z hlediska zjednoduseni navrhu neuvazujeme v teto databazi
35-- mozne vyuziti ciselniku jako napriklad pro atributy narodnost hrace,
36-- znacka vybaveni, typ vybaveni, typ klanu, typ tymu,
37-- typ asistence, vydavatel hry, zanr hry, typ sponzora, typ modu hry, apod...
38
39CREATE TABLE Hrac (
40 prezdivka VARCHAR2(50) PRIMARY KEY,
41 jmeno VARCHAR2(50) NOT NULL,
42 prostredni_jmeno VARCHAR2(50),
43 prijmeni VARCHAR2(50) NOT NULL,
44 narodnost VARCHAR2(50) NOT NULL
45 -- CONSTRAINT narodnost_min CHECK (length(narodnost) > 3)
46);
47
48CREATE TABLE Klan (
49 nazev VARCHAR2(50) NOT NULL PRIMARY KEY,
50 logo LONG RAW NOT NULL,
51 hymna VARCHAR2(2000) UNIQUE,
52 typ VARCHAR2(50) NOT NULL,
53 vedouci VARCHAR2(50) NOT NULL
54);
55
56CREATE TABLE Vybaveni (
57 nazev VARCHAR2(50) NOT NULL PRIMARY KEY,
58 typ VARCHAR2(50) NOT NULL,
59 vyrobce VARCHAR2(50) NOT NULL,
60 cena NUMERIC(6,2) NOT NULL CHECK (cena > 0), -- ukladana cena je v dolarech, prepocty na aplikacni urovni
61 znacka VARCHAR2(50) NOT NULL
62);
63
64
65CREATE TABLE Tym (
66 nazev VARCHAR2(50) NOT NULL PRIMARY KEY,
67 logo LONG RAW NOT NULL,
68 typ VARCHAR2(50) NOT NULL,
69 klan VARCHAR2(50) NOT NULL,
70 vedouci VARCHAR2(50) NOT NULL
71);
72
73CREATE TABLE Asistence (
74 id_umrti INTEGER NOT NULL,
75 asistujici_hrac VARCHAR2(50) NOT NULL,
76 typ VARCHAR2(50) NOT NULL
77);
78
79CREATE TABLE Umrti (
80 id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
81 cas_v_zapase_H SMALLINT NOT NULL CHECK (cas_v_zapase_H >= 0),
82 cas_v_zapase_M SMALLINT NOT NULL CHECK (cas_v_zapase_M >= 0 AND cas_v_zapase_M < 60),
83 cas_v_zapase_S SMALLINT NOT NULL CHECK (cas_v_zapase_S >= 0 AND cas_v_zapase_S < 60),
84 zesnuly VARCHAR2(50) NOT NULL,
85 vrah VARCHAR2(50),
86 id_zapasu INTEGER NOT NULL
87);
88
89CREATE TABLE Archiv_umrti (
90 id NUMBER PRIMARY KEY,
91 cas_v_zapase_H SMALLINT NOT NULL CHECK (cas_v_zapase_H >= 0),
92 cas_v_zapase_M SMALLINT NOT NULL CHECK (cas_v_zapase_M >= 0 AND cas_v_zapase_M < 60),
93 cas_v_zapase_S SMALLINT NOT NULL CHECK (cas_v_zapase_S >= 0 AND cas_v_zapase_S < 60),
94 zesnuly VARCHAR2(50) NOT NULL,
95 vrah VARCHAR2(50),
96 id_zapasu INTEGER NOT NULL
97);
98
99CREATE TABLE Hra (
100 nazev VARCHAR2(50) NOT NULL PRIMARY KEY,
101 zanr VARCHAR2(50) NOT NULL,
102 datum_vydani DATE NOT NULL CHECK (datum_vydani >= TO_DATE('1990-01-01','YYYY-MM-DD')),
103 vydavatel VARCHAR2(50) NOT NULL
104);
105
106CREATE TABLE Turnaj (
107 nazev VARCHAR2(255) NOT NULL PRIMARY KEY,
108 hlavni_cena NUMERIC(12,2) NOT NULL CHECK (hlavni_cena > 0),
109 datum_zacatku DATE NOT NULL,
110 datum_konce DATE NOT NULL
111);
112
113CREATE TABLE Sponzor (
114 nazev VARCHAR2(50) NOT NULL PRIMARY KEY
115);
116
117CREATE TABLE Sponzori_turnaje (
118 nazev_turnaje VARCHAR2(50) NOT NULL,
119 nazev_sponzora VARCHAR2(50) NOT NULL,
120 typ VARCHAR2(50) NOT NULL,
121 hodnota_daru NUMERIC(12,2) NOT NULL CHECK (hodnota_daru > 0)
122);
123
124CREATE TABLE Herni_mod (
125 id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
126 nazev VARCHAR2(50) NOT NULL,
127 typ VARCHAR2(50) NOT NULL,
128 pocet_hracu_tymu INTEGER NOT NULL CHECK (pocet_hracu_tymu > 0)
129);
130
131CREATE TABLE Vybaveni_hrace (
132 nazev_vybaveni VARCHAR2(50) NOT NULL,
133 prezdivka_hrace VARCHAR2(50) NOT NULL
134);
135
136CREATE TABLE Hraci_klanu (
137 prezdivka_hrace VARCHAR2(50) NOT NULL,
138 nazev_klanu VARCHAR2(50) NOT NULL
139);
140
141CREATE TABLE Hraci_tymu (
142 prezdivka_hrace VARCHAR2(50) NOT NULL,
143 nazev_tymu VARCHAR2(50) NOT NULL
144);
145
146CREATE TABLE Zapas (
147 id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
148 datum DATE NOT NULL,
149 delkaH SMALLINT NOT NULL CHECK (delkaH >= 0),
150 delkaM SMALLINT NOT NULL CHECK (delkaM >= 0 AND delkaM < 60),
151 delkaS SMALLINT NOT NULL CHECK (delkaS >= 0 AND delkaS < 60),
152 nazev_hry VARCHAR2(50) NOT NULL,
153 nazev_turnaje VARCHAR2(50) NOT NULL
154);
155
156CREATE TABLE Zamereni_tymu (
157 nazev_tymu VARCHAR2(50) NOT NULL,
158 hra_zamereni VARCHAR2(50) NOT NULL
159);
160
161CREATE TABLE Zamereni_hracu (
162 prezdivka_hrace VARCHAR2(50) NOT NULL,
163 hra_zamereni VARCHAR2(50) NOT NULL
164);
165
166CREATE TABLE Zapasy_tymu (
167 tymA VARCHAR2(50) NOT NULL,
168 tymB VARCHAR2(50) NOT NULL,
169 vyherce VARCHAR2(50) NOT NULL,
170 id_zapasu INTEGER NOT NULL UNIQUE
171);
172
173CREATE TABLE Zapasy_hracu (
174 prezdivka_hrace VARCHAR2(50) NOT NULL,
175 id_zapasu INTEGER NOT NULL
176);
177
178CREATE TABLE Mody_hry (
179 nazev_hry VARCHAR2(50) NOT NULL,
180 id_modu INTEGER NOT NULL
181);
182
183ALTER TABLE Klan ADD CONSTRAINT FK_klan_vedouci
184FOREIGN KEY (vedouci) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
185
186ALTER TABLE Tym ADD CONSTRAINT FK_tym_vedouci
187FOREIGN KEY (vedouci) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
188
189ALTER TABLE Tym ADD CONSTRAINT FK_tym_klan
190FOREIGN KEY (klan) REFERENCES Klan(nazev) ON DELETE CASCADE;
191
192ALTER TABLE Asistence ADD CONSTRAINT FK_asistence_id_umrti
193FOREIGN KEY (id_umrti) REFERENCES Umrti(id) ON DELETE CASCADE;
194
195ALTER TABLE Asistence ADD CONSTRAINT FK_asistence_asistujici_hrac
196FOREIGN KEY (asistujici_hrac) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
197
198ALTER TABLE Umrti ADD CONSTRAINT FK_umrti_zesnuly
199FOREIGN KEY (zesnuly) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
200
201ALTER TABLE Umrti ADD CONSTRAINT FK_umrti_vrah
202FOREIGN KEY (vrah) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
203
204ALTER TABLE Umrti ADD CONSTRAINT FK_umrti_id_zapasu
205FOREIGN KEY (id_zapasu) REFERENCES Zapas(id) ON DELETE CASCADE;
206
207
208
209ALTER TABLE Sponzori_turnaje ADD CONSTRAINT FK_sponzori_turnaje_nazev_turnaje
210FOREIGN KEY (nazev_turnaje) REFERENCES Turnaj(nazev) ON DELETE CASCADE;
211
212ALTER TABLE Sponzori_turnaje ADD CONSTRAINT FK_sponzori_turnaje_nazev_sponzora
213FOREIGN KEY (nazev_sponzora) REFERENCES Sponzor(nazev) ON DELETE CASCADE;
214
215ALTER TABLE Vybaveni_hrace ADD CONSTRAINT FK_vybaveni_hrace_nazev_vybaveni
216FOREIGN KEY (nazev_vybaveni) REFERENCES Vybaveni(nazev) ON DELETE CASCADE;
217
218ALTER TABLE Vybaveni_hrace ADD CONSTRAINT FK_vybaveni_hrace_prezdivka_hrace
219FOREIGN KEY (prezdivka_hrace) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
220
221ALTER TABLE Hraci_klanu ADD CONSTRAINT FK_hraci_klanu_prezdivka_hrace
222FOREIGN KEY (prezdivka_hrace) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
223
224ALTER TABLE Hraci_klanu ADD CONSTRAINT FK_hraci_klanu_nazev_klanu
225FOREIGN KEY (nazev_klanu) REFERENCES Klan(nazev) ON DELETE CASCADE;
226
227ALTER TABLE Hraci_tymu ADD CONSTRAINT FK_hraci_tymu_prezdivka_hrace
228FOREIGN KEY (prezdivka_hrace) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
229
230ALTER TABLE Hraci_tymu ADD CONSTRAINT FK_hraci_tymu_nazev_tymu
231FOREIGN KEY (nazev_tymu) REFERENCES Tym(nazev) ON DELETE CASCADE;
232
233ALTER TABLE Zapas ADD CONSTRAINT FK_zapas_nazev_hry
234FOREIGN KEY (nazev_hry) REFERENCES Hra(nazev) ON DELETE CASCADE;
235
236ALTER TABLE Zapas ADD CONSTRAINT FK_zapas_nazev_turnaje
237FOREIGN KEY (nazev_turnaje) REFERENCES Turnaj(nazev) ON DELETE CASCADE;
238
239ALTER TABLE Zamereni_tymu ADD CONSTRAINT FK_zamereni_tymu_nazev_tymu
240FOREIGN KEY (nazev_tymu) REFERENCES Tym(nazev) ON DELETE CASCADE;
241
242ALTER TABLE Zamereni_tymu ADD CONSTRAINT FK_zamereni_tymu_hra_zamereni
243FOREIGN KEY (hra_zamereni) REFERENCES Hra(nazev) ON DELETE CASCADE;
244
245ALTER TABLE Zamereni_hracu ADD CONSTRAINT FK_zamereni_hracu_prezdivka_hrace
246FOREIGN KEY (prezdivka_hrace) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
247
248ALTER TABLE Zamereni_hracu ADD CONSTRAINT FK_zamereni_hracu_hra_zamereni
249FOREIGN KEY (hra_zamereni) REFERENCES Hra(nazev) ON DELETE CASCADE;
250
251ALTER TABLE Zapasy_tymu ADD CONSTRAINT FK_zapasy_tymu_tymA
252FOREIGN KEY (tymA) REFERENCES Tym(nazev) ON DELETE CASCADE;
253
254ALTER TABLE Zapasy_tymu ADD CONSTRAINT FK_zapasy_tymu_tymB
255FOREIGN KEY (tymB) REFERENCES Tym(nazev) ON DELETE CASCADE;
256
257ALTER TABLE Zapasy_tymu ADD CONSTRAINT FK_zapasy_tymu_vyherce
258FOREIGN KEY (vyherce) REFERENCES Tym(nazev) ON DELETE CASCADE;
259
260ALTER TABLE Zapasy_tymu ADD CONSTRAINT FK_zapasy_tymu_id_zapasu
261FOREIGN KEY (id_zapasu) REFERENCES Zapas(id) ON DELETE CASCADE;
262
263ALTER TABLE Zapasy_hracu ADD CONSTRAINT FK_zapasy_hracu_prezdivka_hrace
264FOREIGN KEY (prezdivka_hrace) REFERENCES Hrac(prezdivka) ON DELETE CASCADE;
265
266ALTER TABLE Zapasy_hracu ADD CONSTRAINT FK_zapasy_hracu_id_zapasu
267FOREIGN KEY (id_zapasu) REFERENCES Zapas(id) ON DELETE CASCADE;
268
269ALTER TABLE Mody_hry ADD CONSTRAINT FK_mody_hry_nazev_hry
270FOREIGN KEY (nazev_hry) REFERENCES Hra(nazev) ON DELETE CASCADE;
271
272ALTER TABLE Mody_hry ADD CONSTRAINT FK_mody_hry_id_modu
273FOREIGN KEY (id_modu) REFERENCES Herni_mod(id) ON DELETE CASCADE;
274
275-- vytvoreni hracu
276 -- vymyslni hraci
277INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Hrac_1','Burak','Arrashid','Kongo');
278INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Hrac_2','Tomas','Jedno','Kongo');
279INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Hrac_3','Blanka','Protrzena','Kongo');
280INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Hrac_4','Rani','Srani','Izrael');
281
282 -- opravdovi hraci
283 -- San Francisco Shock - Overwatch
284INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('iddqd','André','Dahlström','Švédsko');
285INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('babybay','Andrej','Francisty','USA');
286INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Danteh','Dante','Cruz','USA');
287INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('super','Matthew','DeLisi','USA');
288INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('nomy','David','Ramirez','Mexiko');
289INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('sleepy','Nikola','Andrews','USA');
290INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('sinatraa','jay','Won','USA');
291INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Nevix','Andreas','Karlsson','Švédsko');
292INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Architect','Min-ho','Park','JÞnà Korea');
293INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('moth','Grant','Espe','USA');
294INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('ChoiHyoBin','Hyo-bin','Choi','JÞnà Korea');
295-- vedouci tymu
296INSERT INTO Hrac VALUES('dhaK','Daniel','MartÃnez','Paz','Å panìlsko');
297-- vedouci klanu
298INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Brettbox','Brett','Lautenbach','USA');
299
300
301-- Dallas Fuel - Overwatch
302INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Taimou','Timo','Kettunen','Finsko');
303INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('EFFECT','Hyeon','Hwang','JÞnà Korea');
304INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Seagull','Brandon','Larned','USA');
305INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Mickie','Pongphop','Rattanasangchod','Thajsko');
306INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('cocco','Christian','Jonsson','Švédsko');
307INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('chipshajen','Sebastian','Widlund','Švédsko');
308INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('aKm','Dylan','Bignet','Francie');
309INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Rascal','Dong-jun','Kim','JÞnà Korea');
310INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('uNKOE','Benjamin','Chevasson','Francie');
311-- vedouci tymu
312INSERT INTO Hrac VALUES('HarryHook','Jonathan','Tejedor','Rua','Španìlsko');
313 -- vedouci klanu
314INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('TazMo','Mat','Taylor','USA');
315
316 -- Fnatic - League of Legends
317INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Bwipo','Gabriël','Rau','Belgie');
318INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Broxah','Mads','Brock-Pedersen','Dánsko');
319INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Caps','Rasmus','Winther','Dánsko');
320INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Rekkles','Martin','Larsson','Švédsko');
321INSERT INTO Hrac VALUES('Hylissang','Zdravets', 'Iliev ', 'Galabov','Bulharsko');
322INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('sOAZ','Paul','Boyer','Francie');
323 -- Fnatic - vedouci LoL tymu
324INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('YoungBuck','Joey','Steltenpool','Nizozemsko');
325-- Fnatic - vedouci klanu
326INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Sam','Samuel','Mathews','Anglie');
327
328
329 -- Team Vitality - League of Legends
330INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Cabochard','Lucas','Simon-Meslet','Francie');
331INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Gilius','Erberk','Demir','Nìmecko');
332INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Jiizuke','Daniele','di Mauro','Itálie');
333INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Minitroupax','Amadeu','Carvalho','Portugalsko');
334INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Jactroll','Jakub','Skurzyñski','Polsko');
335INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Nji','Cantoursna', 'An','Francie');
336 -- vedouci tymu
337INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('YamatoCannon','Jakob', 'Mebdi','Švédsko');
338 -- vedouci klanu
339INSERT INTO Hrac (prezdivka, jmeno, prijmeni, narodnost) VALUES('Nico','Nicolas', 'Maurer','Francie');
340
341
342-- vytvoreni klanu
343 -- vymyslene klany
344INSERT INTO Klan VALUES('Pokusny klan 1','101010101010100010101', NULL ,'vesnicky', 'Hrac_1');
345INSERT INTO Klan VALUES('Pokusny klan 2','101010101010100010101', NULL,'vesnicky', 'Hrac_3');
346
347 -- opravdove klany
348INSERT INTO Klan VALUES('Team Envy','101010101010100010101', NULL,'mezinárodnÃ', 'TazMo');
349INSERT INTO Klan VALUES('NRG Esports','101010101010100010101', NULL,'mezinárodnÃ', 'Brettbox');
350INSERT INTO Klan VALUES('Fnatic','101010101010100010101','This is Fnatic','mezinárodnÃ', 'Sam');
351INSERT INTO Klan VALUES('Team Vitality','101010101010100010101', NULL, 'mezinárodnÃ', 'Nico');
352
353-- Tymy
354-- Neexistujici
355INSERT INTO Tym VALUES('Pokusny tym 1','101011010101010110','sklepni','Pokusny klan 1','Hrac_1');
356INSERT INTO Tym VALUES('Pokusny tym 2','101011010101010110','sklepni','Pokusny klan 2','Hrac_4');
357
358-- Skutecne
359INSERT INTO Tym VALUES('Dallas Fuel','101011010101010110','jednoklanové','Team Envy','HarryHook');
360INSERT INTO Tym VALUES('San Francisco Shock','101011010101010110','jednoklanové','NRG Esports','dhaK');
361INSERT INTO Tym VALUES('Fnatic','101011010101010110','jednoklanové','Fnatic','YoungBuck');
362INSERT INTO Tym VALUES('Team Vitality','101011010101010110','jednoklanové','Team Vitality','YamatoCannon');
363
364-- Existujici herni mody
365INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('Deathmatch','2v2','2');
366INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('Payload','6v6','6');
367INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('Double points','6v6','6');
368INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('Point to Payload','6v6','6');
369INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('Triple round','6v6','6');
370INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('Summoner''s Rift','5v5','5');
371INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('The Twisted Treeline','3v3','3');
372INSERT INTO Herni_mod (nazev, typ, pocet_hracu_tymu) VALUES('Howling Abyss','5v5','5');
373
374-- Sponzori
375INSERT INTO Sponzor VALUES('Psychiatricka lecebna Bohnice');
376INSERT INTO Sponzor VALUES('Fnatic Gear');
377INSERT INTO Sponzor VALUES('Monster');
378INSERT INTO Sponzor VALUES('BenQ');
379INSERT INTO Sponzor VALUES('MSI');
380INSERT INTO Sponzor VALUES('AMD');
381INSERT INTO Sponzor VALUES('Twitch');
382INSERT INTO Sponzor VALUES('DXseat');
383INSERT INTO Sponzor VALUES('Newzoo');
384INSERT INTO Sponzor VALUES('Intel');
385INSERT INTO Sponzor VALUES('Mercedez-Benz');
386INSERT INTO Sponzor VALUES('Logitech');
387INSERT INTO Sponzor VALUES('T-Mobile');
388
389
390INSERT INTO Turnaj
391VALUES('PC Summergames 2018',1,TO_DATE('1998-12-25','YYYY-MM-DD'),TO_DATE('1998-12-26','YYYY-MM-DD'));
392INSERT INTO Turnaj
393VALUES('2018 EU LCS Spring Playoffs',94400,TO_DATE('2018-03-23','YYYY-MM-DD'),TO_DATE('2018-04-08','YYYY-MM-DD'));
394INSERT INTO Turnaj
395VALUES('2017 EU LCS Summer Playoffs',94400,TO_DATE('2017-08-19','YYYY-MM-DD'),TO_DATE('2017-09-03','YYYY-MM-DD'));
396INSERT INTO Turnaj
397VALUES('2017 League of Legends World Championship',1723721,
398 TO_DATE('2017-09-23','YYYY-MM-DD'),TO_DATE('2017-11-04','YYYY-MM-DD'));
399INSERT INTO Turnaj
400VALUES('IEM Season X - World Championship',50000,TO_DATE('2016-03-04','YYYY-MM-DD'),TO_DATE('2016-03-06','YYYY-MM-DD'));
401INSERT INTO Turnaj
402VALUES('All-Star Paris 2014',50000,TO_DATE('2014-05-08','YYYY-MM-DD'),TO_DATE('2014-05-11','YYYY-MM-DD'));
403
404
405INSERT INTO Hra VALUES('Overwatch','FPS',TO_DATE('2016-05-24','YYYY-MM-DD'),'Blizzard Entertainment');
406INSERT INTO Hra VALUES('League of Legends','MOBA',TO_DATE('2009-10-27','YYYY-MM-DD'),'Riot Games');
407INSERT INTO Hra VALUES('Rocket League','Sport',TO_DATE('2015-07-07','YYYY-MM-DD'),'Psyonix');
408INSERT INTO Hra
409VALUES('Counter-Strike: Global Offensive','Sport',TO_DATE('2012-08-21','YYYY-MM-DD'),'Valve Corporation');
410INSERT INTO Hra VALUES('Dota 2','MOBA',TO_DATE('2013-07-09','YYYY-MM-DD'),'Valve Corporation');
411
412
413-- Vybaveni
414-- Mysi
415INSERT INTO Vybaveni VALUES('Bloody9','mys','Bloody i.n.c.',10,'Bloody');
416INSERT INTO Vybaveni VALUES('Razer DeathAdder Elite','mys','Razer Inc.',100,'Razer');
417INSERT INTO Vybaveni VALUES('Logitech G403 Prodigy Gaming Mouse','mys','Logitech International S.A.',80,'Logitech');
418INSERT INTO Vybaveni VALUES('Razer Naga Chroma','mys','Razer Inc.',130,'Razer');
419
420-- Klavesnice
421INSERT INTO Vybaveni
422VALUES('Corsair Gaming K70 RGB Rapidfire Cherry MX Speed','klavesnice','Corsair Components, Inc',270,'Corsair');
423INSERT INTO Vybaveni
424VALUES('Corsair Gaming K95 RGB Platinum Cherry MX Speed','klavesnice','Corsair Components, Inc',230,'Corsair');
425INSERT INTO Vybaveni
426VALUES('Logitech G413 Carbon','klavesnice','Logitech International S.A.',130,'Logitech');
427
428-- Sluchatka
429INSERT INTO Vybaveni VALUES('Koss-porta-pro','sluchatka','KOSS i.n.c.',8,'KOSS');
430INSERT INTO Vybaveni VALUES('HyperX Cloud Flight','sluchatka','Kingston Technology',200,'HyperX');
431INSERT INTO Vybaveni VALUES('Logitech G933 Artemis Spectrum','sluchatka','Logitech International S.A.',250,'Logitech');
432INSERT INTO Vybaveni VALUES('Logitech G633 Artemis Spectrum','sluchatka','Logitech International S.A.',180,'Logitech');
433INSERT INTO Vybaveni VALUES('Razer Electra V2','sluchatka','Razer Inc.',70,'Razer');
434
435-- Podlozky
436INSERT INTO Vybaveni VALUES('Razer Firefly Chroma','podlozka','Razer Inc.',85,'Razer');
437INSERT INTO Vybaveni VALUES('SteelSeries QcK + Limited Gaming Mouspad','podlozka','SteelSeries',40,'SteelSeries');
438
439
440-- Vybaveni hracu
441-- sOAZ vybaveni
442INSERT INTO Vybaveni_hrace VALUES('Razer Firefly Chroma','sOAZ');
443INSERT INTO Vybaveni_hrace VALUES('Logitech G633 Artemis Spectrum','sOAZ');
444INSERT INTO Vybaveni_hrace VALUES('Logitech G413 Carbon','sOAZ');
445INSERT INTO Vybaveni_hrace VALUES('Razer DeathAdder Elite','sOAZ');
446
447-- Hylissang vybaveni
448INSERT INTO Vybaveni_hrace VALUES('SteelSeries QcK + Limited Gaming Mouspad','Hylissang');
449INSERT INTO Vybaveni_hrace VALUES('HyperX Cloud Flight','Hylissang');
450INSERT INTO Vybaveni_hrace VALUES('Logitech G413 Carbon','Hylissang');
451INSERT INTO Vybaveni_hrace VALUES('Razer DeathAdder Elite','Hylissang');
452
453-- Rekkles vybaveni
454INSERT INTO Vybaveni_hrace VALUES('Razer Firefly Chroma','Rekkles');
455INSERT INTO Vybaveni_hrace VALUES('Logitech G933 Artemis Spectrum','Rekkles');
456INSERT INTO Vybaveni_hrace VALUES('Corsair Gaming K70 RGB Rapidfire Cherry MX Speed','Rekkles');
457INSERT INTO Vybaveni_hrace VALUES('Razer Naga Chroma','Rekkles');
458
459-- Caps vybaveni
460INSERT INTO Vybaveni_hrace VALUES('SteelSeries QcK + Limited Gaming Mouspad','Caps');
461INSERT INTO Vybaveni_hrace VALUES('Razer Electra V2','Caps');
462INSERT INTO Vybaveni_hrace VALUES('Logitech G413 Carbon','Caps');
463INSERT INTO Vybaveni_hrace VALUES('Razer Naga Chroma','Caps');
464
465-- Broxah vybaveni
466INSERT INTO Vybaveni_hrace VALUES('Razer Firefly Chroma','Broxah');
467INSERT INTO Vybaveni_hrace VALUES('HyperX Cloud Flight','Broxah');
468INSERT INTO Vybaveni_hrace VALUES('Corsair Gaming K95 RGB Platinum Cherry MX Speed','Broxah');
469INSERT INTO Vybaveni_hrace VALUES('Razer DeathAdder Elite','Broxah');
470
471 -- Bwipo vybaveni
472INSERT INTO Vybaveni_hrace VALUES('SteelSeries QcK + Limited Gaming Mouspad','Bwipo');
473INSERT INTO Vybaveni_hrace VALUES('Logitech G933 Artemis Spectrum','Bwipo');
474INSERT INTO Vybaveni_hrace VALUES('Corsair Gaming K70 RGB Rapidfire Cherry MX Speed','Bwipo');
475INSERT INTO Vybaveni_hrace VALUES('Logitech G403 Prodigy Gaming Mouse','Bwipo');
476
477
478INSERT INTO Vybaveni_hrace VALUES('Koss-porta-pro','Hrac_1');
479INSERT INTO Vybaveni_hrace VALUES('Bloody9','Hrac_1');
480INSERT INTO Vybaveni_hrace VALUES('Koss-porta-pro','Hrac_2');
481INSERT INTO Vybaveni_hrace VALUES('Bloody9','Hrac_2');
482INSERT INTO Vybaveni_hrace VALUES('Koss-porta-pro','Hrac_3');
483
484
485-- Testovaci hraci testovacich tymu
486INSERT INTO Hraci_klanu VALUES('Hrac_1','Pokusny klan 1');
487INSERT INTO Hraci_klanu VALUES('Hrac_2','Pokusny klan 1');
488INSERT INTO Hraci_klanu VALUES('Hrac_3','Pokusny klan 2');
489INSERT INTO Hraci_klanu VALUES('Hrac_4','Pokusny klan 2');
490
491-- Skutecni hraci skutecnych klanu
492-- NRG Esports
493INSERT INTO Hraci_klanu VALUES('iddqd','NRG Esports');
494INSERT INTO Hraci_klanu VALUES('babybay','NRG Esports');
495INSERT INTO Hraci_klanu VALUES('Danteh','NRG Esports');
496INSERT INTO Hraci_klanu VALUES('super','NRG Esports');
497INSERT INTO Hraci_klanu VALUES('nomy','NRG Esports');
498INSERT INTO Hraci_klanu VALUES('dhaK','NRG Esports');
499INSERT INTO Hraci_klanu VALUES('sleepy','NRG Esports');
500INSERT INTO Hraci_klanu VALUES('sinatraa','NRG Esports');
501INSERT INTO Hraci_klanu VALUES('Nevix','NRG Esports');
502INSERT INTO Hraci_klanu VALUES('Architect','NRG Esports');
503INSERT INTO Hraci_klanu VALUES('moth','NRG Esports');
504INSERT INTO Hraci_klanu VALUES('ChoiHyoBin','NRG Esports');
505INSERT INTO Hraci_klanu VALUES('Brettbox','NRG Esports');
506
507-- Fnatic
508INSERT INTO Hraci_klanu VALUES('sOAZ','Fnatic');
509INSERT INTO Hraci_klanu VALUES('Rekkles','Fnatic');
510INSERT INTO Hraci_klanu VALUES('Broxah','Fnatic');
511INSERT INTO Hraci_klanu VALUES('Bwipo','Fnatic');
512INSERT INTO Hraci_klanu VALUES('Caps','Fnatic');
513INSERT INTO Hraci_klanu VALUES('Hylissang','Fnatic');
514INSERT INTO Hraci_klanu VALUES('YoungBuck','Fnatic');
515INSERT INTO Hraci_klanu VALUES('Sam','Fnatic');
516
517-- Team Vitality
518INSERT INTO Hraci_klanu VALUES('Cabochard','Team Vitality');
519INSERT INTO Hraci_klanu VALUES('Gilius','Team Vitality');
520INSERT INTO Hraci_klanu VALUES('Jiizuke','Team Vitality');
521INSERT INTO Hraci_klanu VALUES('Minitroupax','Team Vitality');
522INSERT INTO Hraci_klanu VALUES('Jactroll','Team Vitality');
523INSERT INTO Hraci_klanu VALUES('Nji','Team Vitality');
524INSERT INTO Hraci_klanu VALUES('YamatoCannon','Team Vitality');
525INSERT INTO Hraci_klanu VALUES('Nico','Team Vitality');
526
527-- Team Envy
528INSERT INTO Hraci_klanu VALUES('Taimou','Team Envy');
529INSERT INTO Hraci_klanu VALUES('EFFECT','Team Envy');
530INSERT INTO Hraci_klanu VALUES('Seagull','Team Envy');
531INSERT INTO Hraci_klanu VALUES('Mickie','Team Envy');
532INSERT INTO Hraci_klanu VALUES('cocco','Team Envy');
533INSERT INTO Hraci_klanu VALUES('chipshajen','Team Envy');
534INSERT INTO Hraci_klanu VALUES('HarryHook','Team Envy');
535INSERT INTO Hraci_klanu VALUES('aKm','Team Envy');
536INSERT INTO Hraci_klanu VALUES('Rascal','Team Envy');
537INSERT INTO Hraci_klanu VALUES('uNKOE','Team Envy');
538INSERT INTO Hraci_klanu VALUES('TazMo','Team Envy');
539
540-- Testovaci hraci testovacich tymu
541INSERT INTO Hraci_tymu VALUES('Hrac_2','Pokusny tym 1');
542INSERT INTO Hraci_tymu VALUES('Hrac_1','Pokusny tym 1');
543INSERT INTO Hraci_tymu VALUES('Hrac_4','Pokusny tym 2');
544INSERT INTO Hraci_tymu VALUES('Hrac_3','Pokusny tym 2');
545
546-- Skutecni hraci skutecnych tymu
547-- San Francisco Shock
548INSERT INTO Hraci_tymu VALUES('iddqd','San Francisco Shock');
549INSERT INTO Hraci_tymu VALUES('babybay','San Francisco Shock');
550INSERT INTO Hraci_tymu VALUES('Danteh','San Francisco Shock');
551INSERT INTO Hraci_tymu VALUES('super','San Francisco Shock');
552INSERT INTO Hraci_tymu VALUES('nomy','San Francisco Shock');
553INSERT INTO Hraci_tymu VALUES('dhaK','San Francisco Shock');
554INSERT INTO Hraci_tymu VALUES('sleepy','San Francisco Shock');
555INSERT INTO Hraci_tymu VALUES('sinatraa','San Francisco Shock');
556INSERT INTO Hraci_tymu VALUES('Nevix','San Francisco Shock');
557INSERT INTO Hraci_tymu VALUES('Architect','San Francisco Shock');
558INSERT INTO Hraci_tymu VALUES('moth','San Francisco Shock');
559INSERT INTO Hraci_tymu VALUES('ChoiHyoBin','San Francisco Shock');
560
561 -- Dallas Fuel
562INSERT INTO Hraci_tymu VALUES('Taimou','Dallas Fuel');
563INSERT INTO Hraci_tymu VALUES('EFFECT','Dallas Fuel');
564INSERT INTO Hraci_tymu VALUES('Seagull','Dallas Fuel');
565INSERT INTO Hraci_tymu VALUES('Mickie','Dallas Fuel');
566INSERT INTO Hraci_tymu VALUES('cocco','Dallas Fuel');
567INSERT INTO Hraci_tymu VALUES('chipshajen','Dallas Fuel');
568INSERT INTO Hraci_tymu VALUES('HarryHook','Dallas Fuel');
569INSERT INTO Hraci_tymu VALUES('aKm','Dallas Fuel');
570INSERT INTO Hraci_tymu VALUES('Rascal','Dallas Fuel');
571INSERT INTO Hraci_tymu VALUES('uNKOE','Dallas Fuel');
572
573 -- Fnatic
574INSERT INTO Hraci_tymu VALUES('sOAZ','Fnatic');
575INSERT INTO Hraci_tymu VALUES('Rekkles','Fnatic');
576INSERT INTO Hraci_tymu VALUES('Broxah','Fnatic');
577INSERT INTO Hraci_tymu VALUES('Bwipo','Fnatic');
578INSERT INTO Hraci_tymu VALUES('Caps','Fnatic');
579INSERT INTO Hraci_tymu VALUES('Hylissang','Fnatic');
580
581-- Team Vitality
582INSERT INTO Hraci_tymu VALUES('Cabochard','Team Vitality');
583INSERT INTO Hraci_tymu VALUES('Gilius','Team Vitality');
584INSERT INTO Hraci_tymu VALUES('Jiizuke','Team Vitality');
585INSERT INTO Hraci_tymu VALUES('Minitroupax','Team Vitality');
586INSERT INTO Hraci_tymu VALUES('Jactroll','Team Vitality');
587INSERT INTO Hraci_tymu VALUES('Nji','Team Vitality');
588INSERT INTO Hraci_tymu VALUES('YamatoCannon','Team Vitality');
589
590
591-- Zamereni testovacich hracu
592INSERT INTO Zamereni_hracu VALUES('Hrac_1','Overwatch');
593INSERT INTO Zamereni_hracu VALUES('Hrac_2','Overwatch');
594INSERT INTO Zamereni_hracu VALUES('Hrac_3','Overwatch');
595INSERT INTO Zamereni_hracu VALUES('Hrac_4','Overwatch');
596
597-- Zamereni skutecnych hracu
598-- Vsechna zamereni neodpovidaji realite
599INSERT INTO Zamereni_hracu VALUES('iddqd','Overwatch');
600INSERT INTO Zamereni_hracu VALUES('babybay','Overwatch');
601INSERT INTO Zamereni_hracu VALUES('babybay','Counter-Strike: Global Offensive');
602INSERT INTO Zamereni_hracu VALUES('Danteh','Overwatch');
603INSERT INTO Zamereni_hracu VALUES('Danteh','Rocket League');
604INSERT INTO Zamereni_hracu VALUES('Danteh','Dota 2');
605INSERT INTO Zamereni_hracu VALUES('super','Overwatch');
606INSERT INTO Zamereni_hracu VALUES('nomy','Overwatch');
607INSERT INTO Zamereni_hracu VALUES('nomy','League of Legends');
608INSERT INTO Zamereni_hracu VALUES('dhaK','Overwatch');
609INSERT INTO Zamereni_hracu VALUES('dhaK','Rocket League');
610INSERT INTO Zamereni_hracu VALUES('sleepy','Overwatch');
611INSERT INTO Zamereni_hracu VALUES('sleepy','League of Legends');
612INSERT INTO Zamereni_hracu VALUES('sinatraa','Overwatch');
613INSERT INTO Zamereni_hracu VALUES('Nevix','Overwatch');
614INSERT INTO Zamereni_hracu VALUES('Nevix','Counter-Strike: Global Offensive');
615INSERT INTO Zamereni_hracu VALUES('Architect','Overwatch');
616INSERT INTO Zamereni_hracu VALUES('moth','Overwatch');
617INSERT INTO Zamereni_hracu VALUES('ChoiHyoBin','Overwatch');
618INSERT INTO Zamereni_hracu VALUES('Taimou','Overwatch');
619INSERT INTO Zamereni_hracu VALUES('Taimou','Counter-Strike: Global Offensive');
620INSERT INTO Zamereni_hracu VALUES('EFFECT','Overwatch');
621INSERT INTO Zamereni_hracu VALUES('Seagull','Overwatch');
622INSERT INTO Zamereni_hracu VALUES('Mickie','Overwatch');
623INSERT INTO Zamereni_hracu VALUES('cocco','Overwatch');
624INSERT INTO Zamereni_hracu VALUES('chipshajen','Overwatch');
625INSERT INTO Zamereni_hracu VALUES('chipshajen','Dota 2');
626INSERT INTO Zamereni_hracu VALUES('HarryHook','Overwatch');
627INSERT INTO Zamereni_hracu VALUES('HarryHook','Dota 2');
628INSERT INTO Zamereni_hracu VALUES('aKm','Overwatch');
629INSERT INTO Zamereni_hracu VALUES('Rascal','Overwatch');
630INSERT INTO Zamereni_hracu VALUES('HarryHook','League of Legends');
631INSERT INTO Zamereni_hracu VALUES('uNKOE','Overwatch');
632INSERT INTO Zamereni_hracu VALUES('sOAZ','League of Legends');
633INSERT INTO Zamereni_hracu VALUES('sOAZ','Counter-Strike: Global Offensive');
634INSERT INTO Zamereni_hracu VALUES('Rekkles','League of Legends');
635INSERT INTO Zamereni_hracu VALUES('Rekkles','Overwatch');
636INSERT INTO Zamereni_hracu VALUES('Broxah','League of Legends');
637INSERT INTO Zamereni_hracu VALUES('Broxah','Dota 2');
638INSERT INTO Zamereni_hracu VALUES('Bwipo','League of Legends');
639INSERT INTO Zamereni_hracu VALUES('Caps','League of Legends');
640INSERT INTO Zamereni_hracu VALUES('Hylissang','League of Legends');
641INSERT INTO Zamereni_hracu VALUES('Hylissang','Overwatch');
642INSERT INTO Zamereni_hracu VALUES('YoungBuck','League of Legends');
643INSERT INTO Zamereni_hracu VALUES('Cabochard','League of Legends');
644INSERT INTO Zamereni_hracu VALUES('Gilius','League of Legends');
645INSERT INTO Zamereni_hracu VALUES('Gilius','Dota 2');
646INSERT INTO Zamereni_hracu VALUES('Jiizuke','League of Legends');
647INSERT INTO Zamereni_hracu VALUES('Jiizuke','Overwatch');
648INSERT INTO Zamereni_hracu VALUES('Minitroupax','League of Legends');
649INSERT INTO Zamereni_hracu VALUES('Jactroll','League of Legends');
650INSERT INTO Zamereni_hracu VALUES('Jactroll','Dota 2');
651INSERT INTO Zamereni_hracu VALUES('Nji','League of Legends');
652INSERT INTO Zamereni_hracu VALUES('YamatoCannon','League of Legends');
653INSERT INTO Zamereni_hracu VALUES('YamatoCannon','Counter-Strike: Global Offensive');
654
655
656-- Zamereni tymu
657INSERT INTO Zamereni_tymu VALUES('Pokusny tym 1','Overwatch');
658INSERT INTO Zamereni_tymu VALUES('Pokusny tym 2','Overwatch');
659INSERT INTO Zamereni_tymu VALUES('Dallas Fuel','Overwatch');
660INSERT INTO Zamereni_tymu VALUES('San Francisco Shock','Overwatch');
661INSERT INTO Zamereni_tymu VALUES('Fnatic','League of Legends');
662INSERT INTO Zamereni_tymu VALUES('Team Vitality','League of Legends');
663
664
665-- Mody jednotlivych her
666INSERT INTO Mody_hry VALUES('Overwatch',1);
667INSERT INTO Mody_hry VALUES('Overwatch',2);
668INSERT INTO Mody_hry VALUES('Overwatch',3);
669INSERT INTO Mody_hry VALUES('Overwatch',4);
670INSERT INTO Mody_hry VALUES('Overwatch',5);
671INSERT INTO Mody_hry VALUES('League of Legends',6);
672INSERT INTO Mody_hry VALUES('League of Legends',7);
673INSERT INTO Mody_hry VALUES('League of Legends',8);
674
675
676-- Sponzori turnaju
677INSERT INTO Sponzori_turnaje VALUES('PC Summergames 2018','Psychiatricka lecebna Bohnice','hlavni',1);
678
679INSERT INTO Sponzori_turnaje VALUES('2018 EU LCS Spring Playoffs','MSI','hlavni',50000);
680INSERT INTO Sponzori_turnaje VALUES('2018 EU LCS Spring Playoffs','AMD','vedlejsi',20000);
681INSERT INTO Sponzori_turnaje VALUES('2018 EU LCS Spring Playoffs','BenQ','vedlejsi',20000);
682INSERT INTO Sponzori_turnaje VALUES('2018 EU LCS Spring Playoffs','Monster','vedlejsi',4400);
683
684INSERT INTO Sponzori_turnaje VALUES('2017 EU LCS Summer Playoffs','MSI','hlavni',50000);
685INSERT INTO Sponzori_turnaje VALUES('2017 EU LCS Summer Playoffs','DXseat','vedlejsi',20000);
686INSERT INTO Sponzori_turnaje VALUES('2017 EU LCS Summer Playoffs','Twitch','vedlejsi',20000);
687INSERT INTO Sponzori_turnaje VALUES('2017 EU LCS Summer Playoffs','Fnatic Gear','vedlejsi',4400);
688
689INSERT INTO Sponzori_turnaje VALUES('2017 League of Legends World Championship','Mercedez-Benz','hlavni',2000000);
690INSERT INTO Sponzori_turnaje VALUES('2017 League of Legends World Championship','Intel','hlavni',1000000);
691INSERT INTO Sponzori_turnaje VALUES('2017 League of Legends World Championship','T-Mobile','hlavni',1000000);
692INSERT INTO Sponzori_turnaje VALUES('2017 League of Legends World Championship','Logitech','vedlejsi',300000);
693INSERT INTO Sponzori_turnaje VALUES('2017 League of Legends World Championship','BenQ','vedlejsi',250000);
694INSERT INTO Sponzori_turnaje VALUES('2017 League of Legends World Championship','MSI','vedlejsi',120000);
695
696INSERT INTO Sponzori_turnaje VALUES('IEM Season X - World Championship','Intel','hlavni',50000);
697
698INSERT INTO Sponzori_turnaje VALUES('All-Star Paris 2014','BenQ','vedlejsi',10000);
699INSERT INTO Sponzori_turnaje VALUES('All-Star Paris 2014','MSI','vedlejsi',15000);
700INSERT INTO Sponzori_turnaje VALUES('All-Star Paris 2014','Monster','vedlejsi',5000);
701INSERT INTO Sponzori_turnaje VALUES('All-Star Paris 2014','Logitech','hlavni',20000);
702
703
704-- Zapasy
705INSERT INTO Zapas (datum, delkaH, delkaM, delkaS, nazev_hry, nazev_turnaje)
706VALUES(TO_DATE('2018-12-26','YYYY-MM-DD'),20,20,20,'Overwatch','PC Summergames 2018');
707INSERT INTO Zapas (datum, delkaH, delkaM, delkaS, nazev_hry, nazev_turnaje)
708VALUES(TO_DATE('2018-12-25','YYYY-MM-DD'),0,18,26,'Overwatch','PC Summergames 2018');
709INSERT INTO Zapas (datum, delkaH, delkaM, delkaS, nazev_hry, nazev_turnaje)
710VALUES(TO_DATE('2018-12-25','YYYY-MM-DD'),0,20,18,'Overwatch','PC Summergames 2018');
711INSERT INTO Zapas (datum, delkaH, delkaM, delkaS, nazev_hry, nazev_turnaje)
712VALUES(TO_DATE('2018-03-31','YYYY-MM-DD'),0,29,42,'League of Legends','2018 EU LCS Spring Playoffs');
713INSERT INTO Zapas (datum, delkaH, delkaM, delkaS, nazev_hry, nazev_turnaje)
714VALUES(TO_DATE('2018-03-31','YYYY-MM-DD'),0,33,11,'League of Legends','2018 EU LCS Spring Playoffs');
715INSERT INTO Zapas (datum, delkaH, delkaM, delkaS, nazev_hry, nazev_turnaje)
716VALUES(TO_DATE('2018-03-31','YYYY-MM-DD'),0,30,35,'League of Legends','2018 EU LCS Spring Playoffs');
717INSERT INTO Zapas (datum, delkaH, delkaM, delkaS, nazev_hry, nazev_turnaje)
718VALUES(TO_DATE('2018-03-31','YYYY-MM-DD'),0,25,41,'League of Legends','2018 EU LCS Spring Playoffs');
719
720
721-- Zapasy tymu
722INSERT INTO Zapasy_tymu (tymA, tymB, vyherce, id_zapasu)
723VALUES('Pokusny tym 1','Pokusny tym 2','Pokusny tym 2',1);
724INSERT INTO Zapasy_tymu (tymA, tymB, vyherce, id_zapasu)
725VALUES('Dallas Fuel','San Francisco Shock','San Francisco Shock',2);
726INSERT INTO Zapasy_tymu (tymA, tymB, vyherce, id_zapasu)
727VALUES('San Francisco Shock','Dallas Fuel','Dallas Fuel',3);
728INSERT INTO Zapasy_tymu (tymA, tymB, vyherce, id_zapasu)
729VALUES('Fnatic','Team Vitality','Fnatic',4);
730INSERT INTO Zapasy_tymu (tymA, tymB, vyherce, id_zapasu)
731VALUES('Fnatic','Team Vitality','Team Vitality',5);
732INSERT INTO Zapasy_tymu (tymA, tymB, vyherce, id_zapasu)
733VALUES('Fnatic','Team Vitality','Fnatic',6);
734INSERT INTO Zapasy_tymu (tymA, tymB, vyherce, id_zapasu)
735VALUES('Fnatic','Team Vitality','Fnatic',7);
736
737-- Zapasy hracu
738INSERT INTO Zapasy_hracu VALUES('Hrac_1',1);
739INSERT INTO Zapasy_hracu VALUES('Hrac_2',1);
740INSERT INTO Zapasy_hracu VALUES('Hrac_3',1);
741INSERT INTO Zapasy_hracu VALUES('Hrac_4',1);
742
743-- shock sestava
744INSERT INTO Zapasy_hracu VALUES('iddqd',2);
745INSERT INTO Zapasy_hracu VALUES('babybay',2);
746INSERT INTO Zapasy_hracu VALUES('Danteh',2);
747INSERT INTO Zapasy_hracu VALUES('dhaK',2);
748INSERT INTO Zapasy_hracu VALUES('sleepy',2);
749INSERT INTO Zapasy_hracu VALUES('sinatraa',2);
750INSERT INTO Zapasy_hracu VALUES('iddqd',3);
751INSERT INTO Zapasy_hracu VALUES('Nevix',3);
752INSERT INTO Zapasy_hracu VALUES('Danteh',3);
753INSERT INTO Zapasy_hracu VALUES('dhaK',3);
754INSERT INTO Zapasy_hracu VALUES('moth',3);
755INSERT INTO Zapasy_hracu VALUES('sinatraa',3);
756
757-- dallas sestava
758INSERT INTO Zapasy_hracu VALUES('Taimou',2);
759INSERT INTO Zapasy_hracu VALUES('Seagull',2);
760INSERT INTO Zapasy_hracu VALUES('chipshajen',2);
761INSERT INTO Zapasy_hracu VALUES('HarryHook',2);
762INSERT INTO Zapasy_hracu VALUES('Rascal',2);
763INSERT INTO Zapasy_hracu VALUES('aKm',2);
764INSERT INTO Zapasy_hracu VALUES('Taimou',3);
765INSERT INTO Zapasy_hracu VALUES('Seagull',3);
766INSERT INTO Zapasy_hracu VALUES('EFFECT',3);
767INSERT INTO Zapasy_hracu VALUES('HarryHook',3);
768INSERT INTO Zapasy_hracu VALUES('cocco',3);
769INSERT INTO Zapasy_hracu VALUES('Mickie',3);
770
771-- 4 zapasy Fnatic vs Team Vitality
772-- Fnatic
773INSERT INTO Zapasy_hracu VALUES('Bwipo',4);
774INSERT INTO Zapasy_hracu VALUES('Broxah',4);
775INSERT INTO Zapasy_hracu VALUES('Caps',4);
776INSERT INTO Zapasy_hracu VALUES('Rekkles',4);
777INSERT INTO Zapasy_hracu VALUES('Hylissang',4);
778INSERT INTO Zapasy_hracu VALUES('Bwipo',5);
779INSERT INTO Zapasy_hracu VALUES('Broxah',5);
780INSERT INTO Zapasy_hracu VALUES('Caps',5);
781INSERT INTO Zapasy_hracu VALUES('Rekkles',5);
782INSERT INTO Zapasy_hracu VALUES('Hylissang',5);
783INSERT INTO Zapasy_hracu VALUES('Bwipo',6);
784INSERT INTO Zapasy_hracu VALUES('Broxah',6);
785INSERT INTO Zapasy_hracu VALUES('Caps',6);
786INSERT INTO Zapasy_hracu VALUES('Rekkles',6);
787INSERT INTO Zapasy_hracu VALUES('Hylissang',6);
788INSERT INTO Zapasy_hracu VALUES('Bwipo',7);
789INSERT INTO Zapasy_hracu VALUES('Broxah',7);
790INSERT INTO Zapasy_hracu VALUES('Caps',7);
791INSERT INTO Zapasy_hracu VALUES('Rekkles',7);
792INSERT INTO Zapasy_hracu VALUES('Hylissang',7);
793
794-- Team Vitality
795INSERT INTO Zapasy_hracu VALUES('Cabochard',4);
796INSERT INTO Zapasy_hracu VALUES('Gilius',4);
797INSERT INTO Zapasy_hracu VALUES('Jiizuke',4);
798INSERT INTO Zapasy_hracu VALUES('Minitroupax',4);
799INSERT INTO Zapasy_hracu VALUES('Jactroll',4);
800INSERT INTO Zapasy_hracu VALUES('Cabochard',5);
801INSERT INTO Zapasy_hracu VALUES('Gilius',5);
802INSERT INTO Zapasy_hracu VALUES('Jiizuke',5);
803INSERT INTO Zapasy_hracu VALUES('Minitroupax',5);
804INSERT INTO Zapasy_hracu VALUES('Jactroll',5);
805INSERT INTO Zapasy_hracu VALUES('Cabochard',6);
806INSERT INTO Zapasy_hracu VALUES('Gilius',6);
807INSERT INTO Zapasy_hracu VALUES('Jiizuke',6);
808INSERT INTO Zapasy_hracu VALUES('Minitroupax',6);
809INSERT INTO Zapasy_hracu VALUES('Jactroll',6);
810INSERT INTO Zapasy_hracu VALUES('Cabochard',7);
811INSERT INTO Zapasy_hracu VALUES('Gilius',7);
812INSERT INTO Zapasy_hracu VALUES('Jiizuke',7);
813INSERT INTO Zapasy_hracu VALUES('Minitroupax',7);
814INSERT INTO Zapasy_hracu VALUES('Jactroll',7);
815
816
817
818INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
819VALUES(15,8,6,'Hrac_3','Hrac_1',1);
820INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
821VALUES(15,8,25,'Hrac_2','Hrac_4',1);
822INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
823VALUES(15,8,25,'Hrac_1',NULL,1);
824INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
825VALUES(0,2,8,'iddqd','Seagull',2);
826INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
827VALUES(0,2,8,'Taimou','babybay',2);
828INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
829VALUES(0,2,8,'sinatraa','Seagull',3);
830INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
831VALUES(0,2,8,'EFFECT','iddqd',3);
832
833-- Fnatic vs Team Vitality 1
834INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
835VALUES(0,3,30,'Jactroll','Rekkles',4);
836INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
837VALUES(0,3,32,'Hylissang','Gilius',4);
838INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
839VALUES(0,5,30,'Gilius','Caps',4);
840INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
841VALUES(0,9,30,'Jiizuke','Caps',4);
842INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
843VALUES(0,9,30,'Cabochard','Bwipo',4);
844INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
845VALUES(0,10,30,'Jiizuke','Caps',4);
846INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
847VALUES(0,12,30,'Minitroupax','Rekkles',4);
848INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
849VALUES(0,12,30,'Caps','Minitroupax',4);
850INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
851VALUES(0,13,30,'Bwipo','Gilius',4);
852INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
853VALUES(0,14,30,'Caps','Minitroupax',4);
854INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
855VALUES(0,18,30,'Minitroupax','Caps',4);
856INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
857VALUES(0,20,30,'Jactroll','Caps',4);
858INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
859VALUES(0,21,30,'Jiizuke','Broxah',4);
860INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
861VALUES(0,23,30,'Minitroupax','Rekkles',4);
862INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
863VALUES(0,23,30,'Jactroll','Rekkles',4);
864INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
865VALUES(0,24,30,'Jiizuke','Caps',4);
866INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
867VALUES(0,24,30,'Cabochard','Caps',4);
868INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
869VALUES(0,24,30,'Gilius','Bwipo',4);
870INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
871VALUES(0,29,30,'Cabochard','Broxah',4);
872INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
873VALUES(0,29,30,'Minitroupax','Bwipo',4);
874INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
875VALUES(0,29,30,'Gilius','Caps',4);
876INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
877VALUES(0,29,30,'Jiizuke','Bwipo',4);
878INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
879VALUES(0,29,30,'Caps','Minitroupax',4);
880
881-- Fnatic vs Team Vitality 2
882INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
883VALUES(0,5,30,'Bwipo','Gilius',5);
884INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
885VALUES(0,8,30,'Bwipo','Gilius',5);
886INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
887VALUES(0,11,30,'Jactroll','Rekkles',5);
888INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
889VALUES(0,5,30,'Bwipo','Gilius',5);
890INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
891VALUES(0,16,30,'Gilius','Broxah',5);
892INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
893VALUES(0,17,30,'Jiizuke','Broxah',5);
894INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
895VALUES(0,19,30,'Gilius','Caps',5);
896INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
897VALUES(0,20,30,'Hylissang','Cabochard',5);
898INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
899VALUES(0,22,30,'Caps','Jiizuke',5);
900INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
901VALUES(0,22,30,'Hylissang','Jiizuke',5);
902INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
903VALUES(0,22,30,'Broxah','Gilius',5);
904INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
905VALUES(0,23,30,'Cabochard','Rekkles',5);
906INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
907VALUES(0,23,30,'Bwipo','Minitroupax',5);
908INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
909VALUES(0,28,30,'Gilius','Broxah',5);
910INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
911VALUES(0,28,30,'Jactroll','Rekkles',5);
912INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
913VALUES(0,28,30,'Cabochard','Caps',5);
914INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
915VALUES(0,28,30,'Bwipo','Gilius',5);
916INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
917VALUES(0,28,30,'Hylissang','Minitroupax',5);
918INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
919VALUES(0,31,30,'Hylissang','Gilius',5);
920INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
921VALUES(0,31,30,'Bwipo','Minitroupax',5);
922INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
923VALUES(0,31,30,'Caps','Jiizuke',5);
924
925-- Fnatic vs Team Vitality 3
926INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
927VALUES(0,0,30,'Rekkles','Jactroll',6);
928INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
929VALUES(0,0,30,'Gilius','Hylissang',6);
930INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
931VALUES(0,2,30,'Bwipo','Gilius',6);
932INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
933VALUES(0,8,30,'Caps','Gilius',6);
934INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
935VALUES(0,9,30,'Cabochard','Bwipo',6);
936INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
937VALUES(0,9,30,'Broxah','Cabochard',6);
938INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
939VALUES(0,9,30,'Caps','Jiizuke',6);
940INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
941VALUES(0,12,30,'Jiizuke','Broxah',6);
942INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
943VALUES(0,12,30,'Gilius','Caps',6);
944INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
945VALUES(0,13,30,'Minitroupax','Rekkles',6);
946INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
947VALUES(0,13,30,'Bwipo','Cabochard',6);
948INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
949VALUES(0,16,30,'Cabochard','Caps',6);
950INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
951VALUES(0,16,30,'Jactroll','Caps',6);
952INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
953VALUES(0,16,30,'Jiizuke','Bwipo',6);
954INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
955VALUES(0,20,30,'Jactroll','Broxah',6);
956INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
957VALUES(0,20,30,'Cabochard','Rekkles',6);
958INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
959VALUES(0,20,30,'Gilius','Bwipo',6);
960INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
961VALUES(0,20,30,'Bwipo','Jiizuke',6);
962INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
963VALUES(0,22,30,'Jiizuke','Caps',6);
964INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
965VALUES(0,22,30,'Minitroupax','Caps',6);
966INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
967VALUES(0,22,30,'Gilius','Rekkles',6);
968INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
969VALUES(0,22,30,'Jactroll','Broxah',6);
970INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
971VALUES(0,28,30,'Minitroupax','Bwipo',6);
972INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
973VALUES(0,28,30,'Gilius','Bwipo',6);
974INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
975VALUES(0,28,30,'Jactroll','Rekkles',6);
976INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
977VALUES(0,29,30,'Cabochard','Caps',6);
978INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
979VALUES(0,29,30,'Jiizuke','Rekkles',6);
980INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
981VALUES(0,29,30,'Bwipo','Jiizuke',6);
982
983-- Fnatic vs Team Vitality 4
984INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
985VALUES(0,2,30,'Jiizuke','Caps',7);
986INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
987VALUES(0,7,30,'Jiizuke','Bwipo',7);
988INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
989VALUES(0,8,30,'Hylissang','Minitroupax',7);
990INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
991VALUES(0,11,30,'Jiizuke','Broxah',7);
992INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
993VALUES(0,13,30,'Hylissang','Gilius',7);
994INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
995VALUES(0,14,30,'Jiizuke','Broxah',7);
996INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
997VALUES(0,15,30,'Minitroupax','Caps',7);
998INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
999VALUES(0,15,30,'Gilius','Hylissang',7);
1000INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1001VALUES(0,18,30,'Hylissang','Cabochard',7);
1002INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1003VALUES(0,18,30,'Rekkles','Jiizuke',7);
1004INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1005VALUES(0,18,30,'Bwipo','Minitroupax',7);
1006INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1007VALUES(0,19,30,'Cabochard','Caps',7);
1008INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1009VALUES(0,22,30,'Bwipo','Cabochard',7);
1010INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1011VALUES(0,23,30,'Hylissang','Cabochard',7);
1012INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1013VALUES(0,23,30,'Jiizuke','Caps',7);
1014INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1015VALUES(0,23,30,'Minitroupax','Caps',7);
1016INSERT INTO Umrti (cas_v_zapase_H, cas_v_zapase_M, cas_v_zapase_S, zesnuly, vrah, id_zapasu)
1017VALUES(0,23,30,'Cabochard','Rekkles',7);
1018
1019
1020INSERT INTO Asistence VALUES(1,'Hrac_2','pridavani DMG');
1021INSERT INTO Asistence VALUES(3,'Hrac_3','DMG');
1022INSERT INTO Asistence VALUES(4,'chipshajen','DMG boost');
1023INSERT INTO Asistence VALUES(4,'Taimou','DMG');
1024INSERT INTO Asistence VALUES(6,'HarryHook','healing');
1025
1026
1027
1028
1029/*************************************************************************
1030 ------------------------- 3. CAST PROJEKTU -----------------------------
1031 --------------------------- SELECT DOTAZY ------------------------------
1032 *************************************************************************/
1033-- SQL skript obsahujÃcà dotazy SELECT musà obsahovat konkrétnì alespoò dva dotazy využÃvajÃcà spojenà dvou tabulek,
1034-- jeden využÃvajÃcà spojenà tøà tabulek, dva dotazy s klauzulà GROUP BY a agregaènà funkcÃ, jeden dotaz obsahujÃcÃ
1035-- predikát EXISTS a jeden dotaz s predikátem IN s vnoøeným selectem (nikoliv IN s množinou konstatnÃch dat).
1036-- U každého z dotazù musà být (v komentáøi SQL kódu) popsáno srozumitelnì, jaká data hledá daný dotaz
1037-- (jaká je jeho funkce v aplikaci).
1038
1039
1040/*
1041 Zadani: Dotaz s klauzuli GROUP BY a agregacni funkci COUNT
1042 Ucel:
1043 Ktery hrac umrel nejvickrat v ramci turnaje 2018 EU LCS Spring Playoffs v zapasech mezi tymy
1044 Fnatic a Team Vitality. Pokud ma vice hracu stejny maximalni pocet umrti, vyber vsechny
1045*/
1046SELECT
1047 u.zesnuly AS hrac,
1048 COUNT(u.zesnuly) AS pocet_umrti
1049FROM Zapas z
1050 JOIN Zapasy_tymu t ON z.id = t.id_zapasu
1051 JOIN Umrti u ON t.id_zapasu = u.id_zapasu
1052WHERE nazev_turnaje = '2018 EU LCS Spring Playoffs'
1053GROUP BY u.zesnuly
1054HAVING COUNT(u.zesnuly) >= ALL (
1055 SELECT
1056 COUNT(u.zesnuly) AS pocet_umrti
1057 FROM Zapas z
1058 JOIN Zapasy_tymu t ON z.id = t.id_zapasu
1059 JOIN Umrti u ON t.id_zapasu = u.id_zapasu
1060 WHERE nazev_turnaje = '2018 EU LCS Spring Playoffs'
1061 GROUP BY u.zesnuly
1062);
1063
1064
1065
1066/*
1067 Zadani: Dotaz s klauzuli GROUP BY a agregacnimi funkcemi COUNT a AVG
1068 Ucel:
1069 Prumerny pocet obeti jednotlivych hracu v ramci turnaje 2018 EU LCS Spring Playoffs
1070 ze 4 zapasu mezi tymy Fnatic a Team Vitality
1071*/
1072SELECT
1073 Zabijaci.vrah AS hrac,
1074 ROUND(AVG(Zabijaci.pocet_obeti), 4) AS prumerny_pocet_obeti
1075FROM (
1076 SELECT
1077 z_h.id_zapasu AS id_zapasu,
1078 z_h.prezdivka_hrace AS vrah,
1079 COUNT(u.zesnuly) AS pocet_obeti
1080 FROM Zapasy_hracu z_h
1081 JOIN Zapas z ON z_h.id_zapasu = z.id
1082 LEFT JOIN Umrti u ON z_h.id_zapasu = u.id_zapasu AND z_h.prezdivka_hrace = u.vrah
1083 WHERE z.nazev_turnaje = '2018 EU LCS Spring Playoffs'
1084 GROUP BY z_h.id_zapasu, z_h.prezdivka_hrace
1085) Zabijaci
1086GROUP BY Zabijaci.vrah;
1087
1088
1089
1090/*
1091 Zadani: Dotaz s predikatem EXISTS
1092 Ucel:
1093 Hraci, kteri se zameruji pouze na hru Overwatch a na zadnou jinou
1094*/
1095SELECT h.*
1096FROM Hrac h
1097 JOIN Zamereni_hracu z ON h.prezdivka = z.prezdivka_hrace
1098WHERE z.hra_zamereni = 'Overwatch' AND NOT EXISTS(
1099 SELECT *
1100 FROM Zamereni_hracu z
1101 WHERE h.prezdivka = z.prezdivka_hrace
1102 AND z.hra_zamereni <> 'Overwatch'
1103);
1104
1105
1106
1107/*
1108 Zadani: Dotaz s predikatem IN a poddotazem
1109 Ucel:
1110 Hraci z tymu ucastnicich se turnaje 2018 EU LCS Spring Playoffs, kteri nehrali
1111 v zadnem ze zapasu.
1112*/
1113SELECT *
1114FROM Hrac WHERE prezdivka IN (
1115 SELECT DISTINCT h_t.prezdivka_hrace
1116 FROM Zapas z
1117 JOIN Zapasy_tymu z_t ON z.id = z_t.id_zapasu
1118 JOIN Tym t ON z_t.tymA = t.nazev OR z_t.tymB = t.nazev
1119 JOIN Hraci_tymu h_t ON t.nazev = h_t.nazev_tymu
1120 WHERE z.nazev_turnaje = '2018 EU LCS Spring Playoffs'
1121)
1122AND prezdivka NOT IN (
1123 /* Poddotaz je takto slozity protoze chceme pouze hrace, kteri hrali v nejakem
1124 z techto zapasu a zaroven skutecne byli cleny danych tymu. Jinak receno
1125 chceme vyradit hrace, kteri hrali jako zaskok v nejakem ze zapasu, ale
1126 zaroven nebyli cleny techto tymu.
1127 */
1128 SELECT DISTINCT h_t.prezdivka_hrace
1129 FROM Zapas z
1130 JOIN Zapasy_tymu z_t ON z.id = z_t.id_zapasu
1131 JOIN Tym t ON z_t.tymA = t.nazev OR z_t.tymB = t.nazev
1132 JOIN Hraci_tymu h_t ON t.nazev = h_t.nazev_tymu
1133 WHERE z.nazev_turnaje = '2018 EU LCS Spring Playoffs' AND h_t.prezdivka_hrace IN (
1134 SELECT DISTINCT z_h.prezdivka_hrace
1135 FROM Zapas z
1136 JOIN Zapasy_hracu z_h ON z.id = z_h.id_zapasu
1137 WHERE z.nazev_turnaje = '2018 EU LCS Spring Playoffs'
1138 )
1139);
1140
1141/*
1142 Zadani: 2 dotazy s alespon dvemi tabulkami
1143 Ucel:
1144 1. Hraci pusobici v tymu Dallas Fuel
1145 2. Mody pouzivane v ligovych zapasech jednotlivych her
1146*/
1147SELECT H.prezdivka, H.jmeno, H.prostredni_jmeno, H.prijmeni, H.narodnost
1148FROM Hrac H, Hraci_tymu H_T
1149WHERE nazev_tymu = 'Dallas Fuel' AND H.prezdivka = H_T.prezdivka_hrace;
1150
1151SELECT H.nazev, H.vydavatel, H.zanr, H_M.nazev, H_M.typ, H_M.pocet_hracu_tymu
1152FROM Hra H, Mody_hry M_H, Herni_mod H_M
1153WHERE H.nazev = M_H.nazev_hry AND H_M.id = M_H.id_modu;
1154
1155
1156/*
1157 Zadani: Dotaz s alespon tremi tabulkami
1158 Ucel:
1159 Hraci hrajici hru Overwatch a tym, ve kterem pusobi
1160*/
1161SELECT H.prezdivka, H.jmeno, H.prostredni_jmeno, H.prijmeni, H_T.nazev_tymu
1162FROM Hrac H, Zamereni_hracu Z_H, Hraci_tymu H_T
1163WHERE H.prezdivka = Z_H.prezdivka_hrace AND H.prezdivka = H_T.prezdivka_hrace
1164 AND Z_H.hra_zamereni = 'Overwatch';
1165
1166
1167
1168/*
1169 Pomocna sekvence pro ucely generovani prezdivky hrace.
1170 */
1171CREATE SEQUENCE hrac_seq
1172 START WITH 1
1173 INCREMENT BY 1
1174 NOCACHE
1175 NOCYCLE;
1176
1177/*
1178 Trigger, ktery slouzi ke generickemu doplneni prezdivky hrace pokud nebyla zadana.
1179 Pro ucely tohoto zadani byla upravena tabulka hrace. Nyni se muze v poli prezdivka vyskytovat NULL hodnota
1180
1181 Pri kazdem nevyplneni hodnoty prezdivka se pricte sekvence hrac_seq a cislo se spoji s retezecem 'Generic_nick_'.
1182 */
1183CREATE OR REPLACE TRIGGER bi_hrac
1184 BEFORE INSERT ON Hrac
1185 FOR EACH ROW
1186DECLARE
1187 hrac_pocitadlo INTEGER;
1188BEGIN
1189 IF :NEW.prezdivka IS NULL THEN
1190 hrac_pocitadlo := hrac_seq.nextval;
1191 :NEW.prezdivka := CONCAT('Generic_nick_', hrac_pocitadlo);
1192 END IF;
1193end;
1194
1195/*
1196 Vytvoreni hrace, ktery nema zadanou prezdivku (je tedy vygenerovana).
1197 */
1198INSERT INTO Hrac(jmeno, prijmeni, narodnost)
1199 VALUES('Hrac', 'Hrac', 'unknown');
1200
1201/*
1202 Vypis zaznamu tohoto hrace a tedy demonstrace vygenerovane prezdivky.
1203 */
1204SELECT * FROM Hrac WHERE jmeno = 'Hrac';
1205
1206/*
1207 Trigger, ktery slouzi k moznosti zmenit nazev hry a propagaci teto hodnoty do tabulek s cizim klicem
1208 */
1209CREATE OR REPLACE TRIGGER bu_hra_nazev
1210 BEFORE UPDATE ON Hra
1211 FOR EACH ROW
1212BEGIN
1213 -- uprava v Hraci_tymu
1214 UPDATE Zapas
1215 SET nazev_hry = :NEW.nazev
1216 WHERE nazev_hry = :OLD.nazev;
1217 -- uprava v Hraci_klanu
1218 UPDATE Mody_hry
1219 SET nazev_hry = :NEW.nazev
1220 WHERE nazev_hry = :OLD.nazev;
1221
1222 UPDATE Zamereni_hracu
1223 SET hra_zamereni = :NEW.nazev
1224 WHERE hra_zamereni = :OLD.nazev;
1225
1226 UPDATE Zamereni_tymu
1227 SET hra_zamereni = :NEW.nazev
1228 WHERE hra_zamereni = :OLD.nazev;
1229end;
1230
1231/*
1232 Demonstrace toho, ze dotaz UPDATE je mozne provest a nezpusobi chybu integritnich omezeni
1233 */
1234UPDATE Hra
1235SET nazev = 'OVERWATCH'
1236WHERE nazev = 'Overwatch';
1237
1238/*
1239 Vypis hracu hrajici prejmenovanou hru
1240 */
1241SELECT H.prezdivka FROM Hrac H, Zamereni_hracu ZH
1242 WHERE ZH.prezdivka_hrace = H.prezdivka AND ZH.hra_zamereni = 'OVERWATCH';
1243
1244
1245/*
1246 Ulozena procedura, ktera vyjme vsechna umrti daneho zapasu a archivuje je do tabulky, ktera byla vytvorena pro tyto ucely (Archiv_umrti)
1247
1248 Vytvori se kurzor do tabulky Umrti a vybere vsechny radky.
1249 Informace o radku jsou po navazani kurzoru ulozeny v promenne umrti_id_radek.
1250 Hodnoty jsou nasledne vlozeny do tabulky archivu a po pruchodu vsemi radky se procedura ukonci.
1251 */
1252
1253CREATE OR REPLACE PROCEDURE archivace_umrti (id_zapasu_in IN Zapas.id%TYPE) AS
1254BEGIN
1255 DECLARE
1256 pocet_radku INTEGER;
1257
1258 CURSOR umrti_id_cursor IS
1259 SELECT *
1260 FROM Umrti
1261 WHERE id_zapasu = id_zapasu_in;
1262
1263 umrti_id_radek umrti_id_cursor%ROWTYPE;
1264 BEGIN
1265 SELECT COUNT(*) INTO pocet_radku FROM Umrti;
1266 OPEN umrti_id_cursor;
1267
1268 WHILE pocet_radku > 0
1269 LOOP
1270 FETCH umrti_id_cursor INTO umrti_id_radek;
1271 EXIT WHEN umrti_id_cursor%NOTFOUND; -- nepovedl se fetch
1272
1273 INSERT INTO Archiv_umrti
1274 VALUES(umrti_id_radek.id, umrti_id_radek.cas_v_zapase_H, umrti_id_radek.cas_v_zapase_M, umrti_id_radek.cas_v_zapase_S,umrti_id_radek.vrah,umrti_id_radek.zesnuly, umrti_id_radek.id_zapasu);
1275
1276 pocet_radku := pocet_radku - 1;
1277 END LOOP;
1278
1279 CLOSE umrti_id_cursor; -- neni treba ale je to dobry zvyk (cursor je definovany v declare bloku takze se uzavre sam)
1280 END;
1281END;
1282
1283
1284/*
1285 Procedura, ktera se vola pri mazani tymu.
1286
1287 Pri mazani tymu se projdou vsechny zapasy daneho tymu a ty se postupne odstranuji pricemz se vola zalohovani na umrti zapasu funkci 'archivace_umrti'.
1288 */
1289
1290CREATE OR REPLACE PROCEDURE smazani_tymu(nazev_tymu IN Tym.nazev%TYPE) AS
1291BEGIN
1292 DECLARE
1293 pocet_radku INTEGER;
1294
1295 CURSOR zapas_id_cursor IS
1296 SELECT id_zapasu
1297 FROM Zapasy_tymu
1298 WHERE tymA = nazev_tymu OR tymB = nazev_tymu;
1299
1300 zapas_id_radek zapas_id_cursor%ROWTYPE; -- neni potreba ROWTYPE protoze vybiram pouze jednu hodnotu ale je to dobry zvyk
1301 BEGIN
1302 SELECT COUNT(*) INTO pocet_radku FROM Zapasy_tymu
1303 WHERE tymA = nazev_tymu OR tymB = nazev_tymu;
1304
1305 IF pocet_radku = 0 THEN
1306 RAISE NO_DATA_FOUND;
1307 END IF;
1308
1309 OPEN zapas_id_cursor;
1310
1311 WHILE pocet_radku > 0
1312 LOOP
1313 FETCH zapas_id_cursor INTO zapas_id_radek;
1314 EXIT WHEN zapas_id_cursor%NOTFOUND;
1315
1316 archivace_umrti(zapas_id_radek.id_zapasu);
1317
1318 DELETE FROM Zapas
1319 WHERE id = zapas_id_radek.id_zapasu;
1320
1321 pocet_radku := pocet_radku - 1;
1322 END LOOP;
1323
1324 DELETE FROM Tym
1325 WHERE nazev = nazev_tymu;
1326
1327 CLOSE zapas_id_cursor;
1328 EXCEPTION
1329 WHEN NO_DATA_FOUND THEN
1330 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
1331 END;
1332END;
1333
1334/*
1335 Vypsani vsech zabiti, nebo pripadnych smrti, tymu Dallas Fuel.
1336 */
1337SELECT U.* FROM Umrti U, Zapasy_tymu ZT, Zapas Z, Tym T
1338 WHERE (ZT.tymB = T.nazev OR ZT.tymA = T.nazev) AND
1339 U.id_zapasu = Z.id AND ZT.id_zapasu = Z.id AND T.nazev = 'Dallas Fuel';
1340
1341/*
1342 Zavolani procedury, ktery smaze tym a behem mazani archivuje tabulku Umrti pro dany tym.
1343 */
1344CALL smazani_tymu('Dallas Fuel');
1345
1346/*
1347 Znovu stejny dotaz, ktery nyni nevypise nic.
1348 */
1349SELECT U.* FROM Umrti U, Zapasy_tymu ZT, Zapas Z, Tym T
1350 WHERE (ZT.tymB = T.nazev OR ZT.tymA = T.nazev) AND
1351 U.id_zapasu = Z.id AND ZT.id_zapasu = Z.id AND T.nazev = 'Dallas Fuel';
1352
1353/*
1354 Vypis archivovanych zaznamu z tabulky Umrti, ktere se vztahuji k tymu Dallas Fuel.
1355 */
1356SELECT * FROM Archiv_umrti;
1357
1358/*
1359 Dotaz, ktery vypise pocet hracu jednotlivych tymu a nazvy techto tymu
1360
1361 Tento dotaz bude slouzit jako testovaci pro indexaci
1362 */
1363EXPLAIN PLAN FOR
1364 SELECT Tym.nazev, COUNT(Hraci_tymu.prezdivka_hrace) AS pocet_hracu
1365 FROM Tym, Hraci_tymu
1366 WHERE Tym.nazev = Hraci_tymu.nazev_tymu
1367 GROUP BY Tym.nazev;
1368
1369SELECT *
1370 FROM TABLE(DBMS_XPLAN.DISPLAY);
1371
1372/*
1373 Vytvoreni indexu
1374 */
1375CREATE INDEX index_hraci_tymu ON Hraci_tymu(nazev_tymu);
1376
1377/*
1378 Vykon po indexovani
1379 */
1380
1381EXPLAIN PLAN FOR
1382 SELECT Tym.nazev, COUNT(Hraci_tymu.prezdivka_hrace) AS pocet_hracu
1383 FROM Tym, Hraci_tymu
1384 WHERE Tym.nazev = Hraci_tymu.nazev_tymu
1385 GROUP BY Tym.nazev;
1386
1387SELECT *
1388 FROM TABLE(DBMS_XPLAN.DISPLAY);
1389
1390/*
1391 Vytvoreni uzivatele a prirazeni prav.
1392
1393 Kolega tedy muze pouze pridavat zapasy a jednotlive udalosti spojene se zapasem.
1394
1395 TOHLE DELA DRUHY UZIVATEL XPAVEL34
1396 */
1397GRANT SELECT, INSERT, UPDATE, DELETE ON Zapas TO XNAVRA61;
1398GRANT SELECT, INSERT, UPDATE, DELETE ON Umrti TO XNAVRA61;
1399GRANT SELECT, INSERT, UPDATE, DELETE ON Asistence TO XNAVRA61;
1400GRANT SELECT, INSERT, UPDATE, DELETE ON Zapasy_tymu TO XNAVRA61;
1401GRANT SELECT, INSERT, UPDATE, DELETE ON Zapasy_hracu TO XNAVRA61;
1402
1403/*
1404 Nyni uz pokracuji ve sve casti a to je vytvoreni pohledu.
1405
1406 Vytvoreny je pouze jeden pohled i kdyz je pristupove pravo nastavene na vice tabulek
1407 */
1408CREATE MATERIALIZED VIEW pohled_zapasy
1409 BUILD IMMEDIATE
1410 REFRESH FORCE
1411 FOR UPDATE AS
1412SELECT * FROM XPAVEL34.ZAPAS;
1413
1414/*
1415 --------------------------------------------
1416 -- Nasleduji dotazy s vytvorenym pohledem --
1417 --------------------------------------------
1418
1419 Vlozeni do pohledu.
1420 */
1421INSERT INTO pohled_zapasy
1422VALUES(500, TO_DATE('2018-04-30','YYYY-MM-DD'),0,32,44,'League of Legends','2018 EU LCS Spring Playoffs');
1423
1424/*
1425 Vypsani noveho zaznamu.
1426 */
1427SELECT * FROM pohled_zapasy WHERE id = 500;
1428
1429/*
1430 Vypis prvnich 4 zaznamu.
1431 */
1432SELECT * FROM pohled_zapasy WHERE id < 5;
1433
1434/*
1435 Ostraneni prvniho zaznamu.
1436 */
1437DELETE FROM pohled_zapasy WHERE id = 1;
1438
1439/*
1440 Prazdny vypis (prvek byl odstranen).
1441 */
1442SELECT * FROM pohled_zapasy WHERE id = 1;
1443
1444/*
1445 Aktualizace delky ve 4. zapase (delka byla vypsana v jednom z predchozich dotazu).
1446 */
1447UPDATE pohled_zapasy
1448SET delkaH = 1
1449WHERE id = 4;
1450
1451/*
1452 Vypis nove delky
1453 */
1454SELECT * FROM pohled_zapasy WHERE id = 4;
1455
1456DROP MATERIALIZED VIEW pohled_zapasy;