· 9 years ago · Nov 27, 2016, 12:26 AM
1
2
3/*1. Napisati transakciju koja će kvaru s nazivom kvara Uštimavanje pokretnog krova promijeniti
4vrijednost satiKvara na 1. Potrebno je opozvati promjene koje je transakcija uÄinila.*/
5
6SET autocommit=0;
7
8BEGIN;
9
10UPDATE kvar
11SET satiKvar=1
12WHERE kvar.nazivKvar= "UÅ¡timavanje pokretnog krova";
13
14ROLLBACK;
15SET autocommit=1;
16
17END;
18
19
20
21/* 2. Napisati transakciju koja će kvaru s nazivom kvara 'Uštimavanje pokretnog krova'
22 promijeniti vrijednost satiKvara na 3, te nakon toga obrisati sve kvarove Äija je
23vrijednost atributa satiKvar veća od 3. Potrebno je opozvati naredbu brisanja
24
25*/
26
27set autocommit=0;
28
29begin;
30
31update kvar
32set kvar.satiKvar=3
33where kvar.nazivKvar="UÅ¡timavanje pokretnog krova";
34
35savepoint tockica;
36
37delete from kvar
38where kvar.satiKvar > 3;
39
40rollback to savepoint tockica;
41
42set autocommit=1;
43
44
45/* 3. Napisati transakciju koja će unijeti novi zapis u relaciju i promijeniti podatke:
46u tablicu nalog unijeti podatke 1152, 31, 334, 2011-11-1, 2, 7 te tome nalogu naknadno
47 promijeniti prioritetNalog na 1. Potrebno je potvrditi sve promjene.*/
48
49
50
51 set autocommit=1;
52 begin;
53
54 insert into nalog values(1152,31,334,"2011-11-01",2,7);
55
56 update nalog
57 set nalog.prioritetNalog=1
58 where nalog.sifKvar="31" and nalog.sifKlijent="1152" and nalog.datPrimitkaNalog="2011-11-1";
59
60 rollback;
61 set autocommit=1;
62
63/*4. Napisati transakciju koja će radniku sa šifrom 277 smanjiti koeficijent plaće za 0.5,
64a radniku sa šifrom 313 povećati za isti iznos. Potvrditi da se promjena stvarno nije dogodila.*/
65
66
67
68set autocommit=0;
69begin;
70
71update radnik
72set KoefPlaca=KoefPlaca-0.5
73where sifRadnik=277;
74
75UPDATE radnik
76SET KoefPlaca=KoefPlaca+0.5
77WHERE sifRadnik=313;
78
79rollback;
80set autocommit=1;
81
82
83
84
85/*5. Napisati proceduru koja će za kvar sa zadanom šifrom, preko parametara vratiti naziv kvara.
86Potrebno je napisati primjer poziva procedure te ispisa rezultata*/
87
88
89
90drop procedure if exists procedura1;
91
92DELIMITER //
93create procedure procedura1(in sifrica int, out nazivic varchar(50))
94begin
95
96select nazivKvar into nazivic
97from kvar
98where kvar.sifKvar=sifrica;
99
100end; //
101delimiter ;
102
103
104
105/* 6. Napisati proceduru koja za zadani naziv županije, preko parametra vraća iznos najmanje i
106najveće plaće radnika koji živi u toj županiji. Potrebno je napisati primjer poziva procedure te
107ispisa rezultata. */
108
109DROP PROCEDURE IF EXISTS procedura2;
110DELIMITER //
111CREATE PROCEDURE procedura2(IN nazivzup varchar(50), OUT minica int, out maxica int)
112begin
113
114select max(KoefPlaca*IznosOsnovice) into maxica
115from radnik
116natural join mjesto
117natural join zupanija
118where zupanija.nazivZupanija = nazivzup;
119
120SELECT Min(KoefPlaca*IznosOsnovice) into minica
121FROM radnik
122NATURAL JOIN mjesto
123NATURAL JOIN zupanija
124WHERE zupanija.nazivZupanija = nazivzup;
125
126end;//
127delimiter ;
128
129call procedura2("ZagrebaÄka",@k,@j);
130
131select @k, @j;
132
133
134/*7. Napisati proceduru koja zadanom kvaru povećava vrijednost atributa satiKvar za zadanu
135vrijednost. Procedura prima dva ulazna parametra: naziv kvara i broj sati (za koji će se uvećati
136vrijednost atributa satiKvar). Potrebno je napisati primjer poziva procedure.*/
137
138
139drop procedure if exists procedura3;
140delimiter //
141
142create procedure procedura3(in nazivk varchar(50), in brojs int)
143
144begin
145
146update kvar
147set satiKvar=satiKvar + brojs
148where nazivKvar=nazivk;
149end;//
150call procedura3("Zamjena prednjeg fara", 5);
151
152/*8. Napisati proceduru koja će primiti šifru kvara te preko iste varijable za zadani kvar vratiti
153vrijednost atributa satiKvar. Potrebno je napisati primjer poziva procedure te ispisa rezultata*/
154
155drop procedure if exists procedura4;
156delimiter //
157create procedure procedura4(inout varijablica int)
158begin
159
160
161select satiKvar into varijablica
162from kvar
163where kvar.sifKvar=varijablica;
164
165
166end; //
167delimiter ;
168set@k=36;
169call procedura4(@k);
170
171select @k;
172
173
174/*9. Napisati proceduru koja će ispisati sve kvarove Äija je vrijednost atributa satiKvar veća od
175prosjeÄne vrijednosti satServisa iz tablice rezervacija na zadani dan u tjednu. Procedura preko
176parametra prima oznaku dana u tjednu. Potrebno je napisati primjer poziva procedure*/
177
178drop procedure if exists deveti;
179
180delimiter//
181
182create procedure deveti(in oznaka varchar(50) )
183begin
184
185select kvar.*
186from kvar
187natural join rezervacija
188
189where rezervacija.datVrstaDan=oznaka
190and satiKvar > (select avg(satServis) from rezervacija);
191end; //
192
193call deveti("PO");
194
195/*10. Napisati proceduru koja će obrisati sve radnike u zadanom odjelu. Odjel je potrebno zadati
196preko njegovog imena (odjel.nazivOdjel). Potrebno je napisati primjer poziva procedure te
197ispisa rezultata.*/
198
199drop procedure if exists deseti;
200
201delimiter //
202
203create procedure deseti(in odjelime varchar(50))
204begin
205
206delete from radnik
207
208where sifOdjel in( select sifOdjel from odjel where odjel.nazivOdjel=odjelime);
209
210end;//
211delimiter ;
212
213call deseti("Limarija");
214
215
216
217/* 11. Modificirajte tablicu zupanija tako da dodate novi atribut brojMjesta tipa INT. Napišite
218proceduru koja prima šifru županije te popunjava atribut brojMjesta podatkom koliko se
219mjesta nalazi u toj županiji. Potrebno je napisati primjer poziva procedure.
220*/
221
222
223alter table zupanija
224add column brojMjesta int;
225
226drop procedure if exists eleven;
227
228delimiter //
229
230
231create procedure eleven(in sifrica int)
232
233begin
234
235declare brojcek int default 0;
236
237select count(mjesto.pbrMjesto) into brojcek
238from mjesto natural join zupanija
239where sifZupanija=sifrica;
240
241update zupanija
242set brojMjesta=brojcek
243where sifZupanija=sifrica;
244
245end; //
246delimiter ;
247call eleven(1);
248
249/*12. Modificirajte tablicu nalog tako da dodate novi atribut razlikaNalog tipa INT. Napisati
250proceduru koja prima Å¡ifru klijenta, Å¡ifru kvara i datum primitka naloga te u atribut
251razlikaNalog upisuje kolika je razlika između predviđenog trajanja za popravak kvara
252(satiKvar iz tablice kvar) i ostvarenih sati rada za popravak kvara po tom nalogu
253(ostvareniSatiRada). Potrebno je napisati primjer poziva procedure te ispisa rezultata*/
254
255
256alter table nalog
257add column razlikaNalog int;
258
259drop if exists dvanaesti:
260
261delimiter //
262create procedure dvanaesti(in sifrica int, int sifricakvara int, in datumic date)
263begin
264
265declare razlika double;
266
267select satiKvar-OstvareniSatiiRada into razlika from kvar
268natural join nalog
269where sifKvar=sifricakvara and sifKlijent=sifrica and datPrimitkaNalog=datumic;
270
271update nalog
272set razlikaNalog=razlika
273WHERE sifKvar=sifricakvara AND sifKlijent=sifrica AND datPrimitkaNalog=datumic;
274
275end;
276
277
278/*13. Napisati funkciju koja vraća broj klijenata na Äijim je nalozima radio radnik (Å¡ifra radnika je
279ulazni parametar). Potrebno je napisati primjer poziva funkcije*/
280
281drop function if exists funkcica;
282DELIMITER //
283CREATE FUNCTION funkcica(najboljasifraikada INT) RETURNS INT
284DETERMINISTIC
285begin
286
287declare brojcek int;
288
289select count(sifKlijent) into brojcek from nalog
290where sifRadnik=najboljasifraikada;
291
292return brojcek;
293
294end;//
295delimiter ;
296
297select funkcica(122);
298
299
300/* 14. Napisati funkciju koja za zadanog radnika vraća koliko je klijenata registriralo
301 vozilo u županiji u kojoj radi zadani radnik. Šifru radnika funkcija mora primiti preko
302globalne varijable.
303Potrebno je napisati primjer poziva funkcije.*/
304
305DROP FUNCTION IF EXISTS funkcica14;
306DELIMITER //
307CREATE FUNCTION funkcica14(sifrasifrica INT) RETURNS INT
308DETERMINISTIC
309BEGIN
310declare klijentici int;
311
312select count(klijent.sifKlijent) into klijentici
313from klijent
314join mjesto on klijent.pbrReg=mjesto.pbrMjesto
315join radnik on mjesto.pbrMjesto=radnik.pbrStan
316where radnik.sifRadnik=sifrasifrica;
317
318return klijentici;
319
320end; //
321delimiter ;
322
323set @u=126;
324select funkcica14(@u);
325
326
327
328/*15. Napisati funkciju koja generira jedinstveni identifikator klijenta.
329 Funkcija prima Å¡ifru klijenta i
330vraća identifikator u obliku 'iprezime123' pri Äemu je:
331i – prvo slovo imena
332prezime – prezime klijenta
333123 – posljednje tri brojke iz godine rođenja klijenta*/
334
335
336drop function if exists petnaesti;
337delimiter //
338create function petnaesti(sifraklijentica int) returns varchar(50)
339deterministic
340begin
341
342declare godina varchar(50);
343declare prvoslovo varchar(5);
344declare prezime varchar(50);
345
346select left(imeKlijent,1) into prvoslovo
347from klijent
348where sifKlijent=sifraklijentica;
349
350select prezimeKlijent into prezime
351from klijent
352WHERE sifKlijent=sifraklijentica;
353
354select substring(jmbgKlijent from 5 for 3) into godina from klijent
355where sifKlijent=sifraklijentica;
356
357return concat(prvoslovo,prezime,godina);
358end;//
359delimiter ;
360
361select petnaesti(1137);
362
363
364/*16. Napisati funkciju koja broji koliko je naloga zaprimljeno u posljednjih mjesec dana. Ako nije
365zaprimljen ni jedan nalog, funkcija mora vratiti -1 (inaÄe vraća broj naloga). */
366
367
368DROP FUNCTION IF EXISTS sesnaesti;
369DELIMITER //
370CREATE FUNCTION sesnaesti(sifraklijentica INT) RETURNS VARCHAR(50)
371DETERMINISTIC
372BEGIN
373declare brojcek int default 0;
374select count(*) into brojcek from nalog
375where datPrimitkaNalog > adddate(curdate(), interval -1 month);
376
377
378if brojcek=0
379then
380 set brojcek= -1;
381end if;
382
383RETURN brojcek;
384
385end; //
386delimiter ;
387
388select sesnaesti(1137);
389
390
391
392/*17. Napisati funkciju za unos novoga kvara u tablicu kvar. Ako se unosi kvar sa postojećom
393šifrom, potrebno je novom kvaru pridijeliti novu šifru i ispisati odgovarajuću poruku: Unijeli
394ste podatke o novom kvaru. Automatski mu je pridijeljena nova Å¡ifra novasifra. Ako se unosi
395kvar s korektnom Å¡ifrom, potrebno je vratiti poruku: Unijeli ste podatke o kvaru i dodijelili mu
396Å¡ifru novasifra. Potrebno je napisati primjer poziva funkcije i ispisa rezultata*/
397
398
399
400DROP FUNCTION IF EXISTS sedamnaesti;
401DELIMITER //
402CREATE FUNCTION sedamnaesti(sifrakvar INT, nazivkvara varchar(50), sifraodjela int, brojcekradnika int,
403saticikvara int) RETURNS VARCHAR(150)
404
405DETERMINISTIC
406BEGIN
407DECLARE ifsifra INT DEFAULT 0;
408
409if sifrakvar not in (select sifKvar from kvar)
410then
411insert into kvar values(sifrakvar, nazivkvara, sifraodjela, brojcekradnika, saticikvara);
412return concat("Unijeli ste podatke o kvaru i dodijelili mu
413Å¡ifru", sifrakvar);
414else
415set ifsifra = (select max(sifKvar)+1 from kvar);
416INSERT INTO kvar VALUES(ifsifra, nazivkvara, sifraodjela, brojcekradnika, saticikvara);
417RETURN CONCAT("Unijeli ste podatke o kvaru i dodijelili mu
418Å¡ifru", ifsifra);
419end if;
420end; //
421delimiter ;
422
423
424
425/* 18. Napisati funkciju koja će kapitalizirati prvo slovo svake rijeÄi u danom tekstu. Pretpostavite da
426rijeÄ poÄinje nakon nekog od sljedećih znakova: ' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.'
427Potrebno je napisati primjer poziva funkcije i ispisa rezultata.
428(vidi http://dev.mysql.com/doc/refman/5.0/en/string-functions.html)
429*/
430
431DROP FUNCTION IF EXISTS sedamnaesti;
432DELIMITER //
433CREATE FUNCTION osamnaesti(tekst varchar(150))
434DETERMINISTIC
435BEGIN
436
437declare duzina int;
438
439
440
441/*19. Napisati funkciju koja za ulazni parametar prima ulaznu varijablu pbr tipa integer. Funkcija
442nad tablicom radnik umanjuje vrijednost atributa KoefPlaca za 1 za sve zapise kod kojih je
443pbrStan jednak ulaznoj varijabli (pbr). Funkcija vraća broj 1. Zadatak je potrebno riješiti
444pomoću kursora.*/
445
446
447DROP FUNCTION IF EXISTS devetnaesti;
448DELIMITER //
449CREATE FUNCTION devetnaesti(pbr int) returns int
450DETERMINISTIC
451BEGIN
452declare nesto double;
453declare nesto2 int;
454declare flag bool;
455declare c cursor for
456select KoefPlaca,sifRadnik from radnik
457where pbrStan=pbr;
458
459 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
460 SET flag = FALSE;
461 open c;
462
463petlja:loop
464
465fetch c into nesto, nesto2;
466
467 IF flag=TRUE THEN
468 LEAVE petlja;
469 END IF;
470
471update radnik
472set KoefPlaca=KoefPlaca+1
473where sifRadnik=nesto2;
474
475end loop;
476close c;
477return 1;
478end; //
479delimiter ;
480
481select devetnaesti(49000);
482
483/* 20. Napisati proceduru koja će sve radnike promaknuti u viši odjel (odjel veći za 1 od trenutnog
484odjela). Procedura vraća broj promaknutih radnika. Obavezno je koristiti kursore.
485*/
486
487DROP procedure IF EXISTS dvadeseti;
488DELIMITER //
489CREATE procedure dvadeseti(out brojcek int)
490
491BEGIN
492declare flag bool;
493declare sifrica int;
494
495DECLARE c CURSOR FOR
496SELECT sifRadnik FROM radnik;
497
498 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
499 SET flag = FALSE;
500 OPEN c;
501set brojcek=found_rows();
502petlja:LOOP
503
504fetch c into sifrica;
505
506 IF flag=TRUE THEN
507 LEAVE petlja;
508 END IF;
509
510UPDATE radnik
511SET sifOdjel=sifOdjel+1
512where sifRadnik=sifrica;
513
514end loop;
515close c;
516end; //
517delimiter ;
518
519call dvadeseti(@kur);
520
521select @kur;
522
523
524/* 21. Modificirati proceduru iz prethodnog zadatka na naÄin da promakne samo one radnike koji
525su iz zadanog mjesta (procedura prima naziv mjesta). */
526
527DROP PROCEDURE IF EXISTS dvadesetprvi;
528DELIMITER //
529CREATE PROCEDURE dvadesetprvi(in mjesto2 varchar(55), OUT brojcek INT)
530
531BEGIN
532DECLARE flag BOOL;
533DECLARE sifrica INT;
534
535DECLARE c CURSOR FOR
536SELECT sifRadnik FROM radnik
537join mjesto on radnik.pbrStan=mjesto.pbrMjesto
538where mjesto.nazivMjesto=mjesto2;
539
540 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
541 SET flag = FALSE;
542 OPEN c;
543SET brojcek=FOUND_ROWS();
544
545petlja:LOOP
546
547FETCH c INTO sifrica;
548
549 IF flag=TRUE THEN
550 LEAVE petlja;
551 END IF;
552
553UPDATE radnik
554SET sifOdjel=sifOdjel+1
555WHERE sifRadnik=sifrica;
556
557END LOOP;
558CLOSE c;
559END; //
560DELIMITER ;
561
562
563CALL dvadesetprvi("Zagreb", @kurso);
564
565SELECT @kurso;
566
567
568
569/*22. Modificirati proceduru iz prethodnog zadatka na naÄin da ako se radnik promiÄe u
570nepostojeći odjel (šifra odjela veća od maksimalne moguće), onda je potrebno opozvati
571promaknuće (rollback) a inaÄe potvrditi promaknuće(commit).*/
572
573DROP PROCEDURE IF EXISTS dvadesetdrugi;
574DELIMITER //
575CREATE PROCEDURE dvadesetdrugi(IN mjesto2 VARCHAR(55), OUT brojcek INT)
576
577BEGIN
578DECLARE flag BOOL;
579DECLARE sifrica INT;
580declare sifo int;
581declare maxica int default 0;
582
583DECLARE c CURSOR FOR
584SELECT sifRadnik, sifOdjel FROM radnik
585JOIN mjesto ON radnik.pbrStan=mjesto.pbrMjesto
586WHERE mjesto.nazivMjesto=mjesto2;
587
588 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
589 SET flag = FALSE;
590 OPEN c;
591SET brojcek=FOUND_ROWS();
592select max(sifOdjel) into maxica from odjel;
593petlja:LOOP
594
595FETCH c INTO sifrica,sifo;
596
597 IF flag=TRUE THEN
598 LEAVE petlja;
599 END IF;
600
601if sifo >= maxica then rollback;
602else
603UPDATE radnik
604SET sifOdjel=sifo+1
605WHERE sifRadnik=sifrica;
606commit;
607end if;
608
609END LOOP;
610CLOSE c;
611END; //
612DELIMITER ;
613
614CALL dvadesetdrugi("Zagreb", @kurso);
615
616SELECT @kurso;
617SELECT sifRadnik, sifOdjel FROM radnik
618JOIN mjesto ON radnik.pbrStan=mjesto.pbrMjesto
619WHERE mjesto.nazivMjesto='Zagreb';
620
621
622
623/*23. Nadograditi proceduru iz prethodnog zadatka tako da vraća podatke o promaknutim
624radnicima (šifru radnika) te odjel kojem radnik nakon promaknuća pripada.*/
625
626DROP PROCEDURE IF EXISTS dvadesettreci;
627DELIMITER //
628CREATE PROCEDURE dvadesettreci(IN mjesto2 VARCHAR(55), OUT brojcek INT, out sifrasifrica int, out odjelodjelic int )
629
630BEGIN
631DECLARE flag BOOL;
632DECLARE sifrica INT;
633DECLARE sifo INT;
634DECLARE maxica INT DEFAULT 0;
635
636DECLARE c CURSOR FOR
637SELECT sifRadnik, sifOdjel FROM radnik
638JOIN mjesto ON radnik.pbrStan=mjesto.pbrMjesto
639WHERE mjesto.nazivMjesto=mjesto2;
640
641 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
642 SET flag = FALSE;
643 OPEN c;
644SET brojcek=FOUND_ROWS();
645SELECT MAX(sifOdjel) INTO maxica FROM odjel;
646petlja:LOOP
647
648FETCH c INTO sifrica,sifo;
649
650 IF flag=TRUE THEN
651 LEAVE petlja;
652 END IF;
653
654IF sifo >= maxica THEN ROLLBACK;
655ELSE
656UPDATE radnik
657SET sifOdjel=sifo+1
658WHERE sifRadnik=sifrica;
659
660set sifrasifrica=sifrica;
661set odjelodjelic=sifo;
662
663COMMIT;
664END IF;
665
666END LOOP;
667CLOSE c;
668END; //
669DELIMITER ;
670
671CALL dvadesettreci("Zagreb", @kurso, @qw, @we);
672SELECT @kurso, @qw, @we;