· 9 years ago · Nov 14, 2016, 09:32 AM
1/*U bazi autoradionica: Modificirati tablicu odjel tako da dodate novi atribut najmanjaPlaca
2tipa DOUBLE.
3Napisati proceduru koja će:
4• primiti šifru odjela
5• za zadani odjel izraÄunati plaću radnika koji radi u zadanom odjelu a ima
6najmanju plaću u tom odjelu
7• u novostvoreni atribut najmanjaPlaca unijeti prethodno izraÄunat iznos
8• plaću je potrebno izraÄunati kao umnožak iznosa osnovice i koeficijenta plaće
9Napisati primjer poziva procedure.*/
10
11ALTER TABLE odjel
12ADD COLUMN najmanjaPlaca DOUBLE;
13
14DROP PROCEDURE IF EXISTS fooPlaca;
15DELIMITER //
16CREATE PROCEDURE fooPlaca(IN sifra INT)
17BEGIN
18 DECLARE najmanja DOUBLE;
19 SELECT MIN(radnik.koefPlaca * radnik.IznosOsnovice) INTO najmanja FROM radnik
20 JOIN odjel ON radnik.sifOdjel = odjel.sifOdjel
21 WHERE odjel.sifOdjel = sifra;
22 UPDATE odjel
23 SET najmanjaPlaca = najmanja
24 WHERE sifOdjel = sifra;
25END; //
26DELIMITER ;
27
28CALL fooPlaca(4);
29
30/* U bazi studenti: Napisati funkciju koja će unijeti novog studenta te provjeriti postoji li već
31student sa istim jmbag-om, da li su poštanski brojevi valjani, da datum upisa nije danas ili u
32budućnosti, te da li smjer postoji. U sluÄaju pogreÅ¡ke, vratiti poruku o tome Å¡to se dogodilo.*/
33
34DROP FUNCTION IF EXISTS fooStudent;
35DELIMITER //
36CREATE FUNCTION fooStudent(jmbagS INT, imeS VARCHAR(50), prezimeS VARCHAR(50), datumS DATE, prebS INT, stanS INT, smjerS INT)
37RETURNS VARCHAR(100)
38DETERMINISTIC
39BEGIN
40 DECLARE broj INT;
41 DECLARE pbr1 INT;
42 DECLARE pbr2 INT;
43 DECLARE ids INT;
44
45 SELECT COUNT(*) INTO ids FROM smjerovi
46 WHERE id = smjerS;
47 SELECT COUNT(*) INTO pbr1 FROM mjesta
48 WHERE postbr = prebS;
49 SELECT COUNT(*) INTO pbr2 FROM mjesta
50 WHERE postbr = stanS;
51 SELECT COUNT(*) INTO broj FROM studenti
52 WHERE jmbag = jmbagS;
53
54 IF datumS >= CURDATE() THEN
55 RETURN "Neispravan datum!";
56 END IF;
57 IF broj != 0 THEN
58 RETURN "Student s istim JMBAG-om vec postoji!";
59 END IF;
60 IF pbr1 = 0 || pbr2 = 0 THEN
61 RETURN "Neispravan postanski broj stanovanja/prebivanja!";
62 END IF;
63 IF ids = 0 THEN
64 RETURN "Neispravan ID smjera!";
65 END IF;
66
67 INSERT INTO studenti (jmbag, ime, prezime, datumUpisa, postBrPrebivanje, postBrStanovanja, idSmjer) VALUES (jmbagS, imeS, prezimeS, datumS, prebS, stanS, smjerS);
68 RETURN "Student upisan!";
69END; //
70DELIMITER ;
71
72
73SELECT fooStudent(4564451, "Marko", "Markovic", "2010-09-28", 10050, 10000, 3);
74
75/*U bazi studenti: Napisati funkciju koja prima naziv smjera. Funkcija mora za sve kolegije sa
76zadanog smjera u atribut opis upisati tekst:
77a. „Lagani kolegij“ – ako je prosjek ocjena na tom kolegiju veći od 3.5
78b. „Težak kolegij“ – ako je prosjek ocjena na tom kolegiju manji ili jednak 3.5
79c. Funkcija vraća broj kolegija kojima je upisala u opis „Težak kolegij“.
80Zadatak je obavezno riješiti koristeći kursore*/
81
82
83DROP FUNCTION IF EXISTS fooKolegij;
84DELIMITER //
85CREATE FUNCTION fooKolegij(nazivS VARCHAR(50))
86RETURNS INT
87DETERMINISTIC
88BEGIN
89 DECLARE prosjek DOUBLE;
90 DECLARE trenutni_id, dohvaceno INT;
91 DECLARE i, broj INT DEFAULT 0;
92 DECLARE kur CURSOR FOR SELECT kolegiji.id, AVG(ocjene.ocjena) FROM kolegiji
93 JOIN smjerovi ON kolegiji.idSmjer = smjerovi.id
94 JOIN ocjene ON kolegiji.id = ocjene.idKolegij
95 WHERE smjerovi.naziv = nazivS
96 GROUP BY kolegiji.id;
97 OPEN kur;
98 SELECT FOUND_ROWS() INTO dohvaceno;
99 WHILE i<dohvaceno DO
100 FETCH kur INTO trenutni_id, prosjek;
101 IF prosjek <= 3.5 THEN
102 UPDATE kolegiji
103 SET opis = "Tezak kolegij"
104 WHERE id = trenutni_id;
105 SET broj = broj + 1;
106 ELSE
107 UPDATE kolegiji
108 SET opis = "Lagan kolegij"
109 WHERE id = trenutni_id;
110 END IF;
111 SET i = i + 1;
112 END WHILE;
113 CLOSE kur;
114 RETURN broj;
115END; //
116DELIMITER ;
117
118SELECT fooKolegij("smjer informatika");
119
120/*U bazi autoradionica: Napisati proceduru koja će koristeći proceduru iz prvog zadatka
121popuniti u tablici odjel atribut najmanjaPlaca. Procedura prima podatak o županiji te
122popunjava atribut najmanjaPlaca iskljuÄivo za odjele koji se odnose na radnika iz zadane
123županije
124Ako za određeni odjel procedura ne uspije pronaći zapis (pogreška NOT FOUND), potrebno je
125problem riješiti koristeći odgovarajući handler. Procedura vraća broj dohvaćenih i broj
126obrađenih zapisa*/
127
128ALTER TABLE odjel
129ADD COLUMN najmanjaPlaca DOUBLE;
130
131DROP PROCEDURE IF EXISTS fooPlaca1;
132DELIMITER //
133CREATE PROCEDURE fooPlaca1(IN zupa INT, OUT obr INT, OUT doh INT)
134BEGIN
135 DECLARE najmanja DOUBLE;
136 DECLARE sifO INT;
137 DECLARE flag BOOL;
138 DECLARE i INT DEFAULT 0;
139 DECLARE kur CURSOR FOR SELECT odjel.sifOdjel, radnik.KoefPlaca*radnik.IznosOsnovice FROM odjel
140 JOIN radnik ON odjel.sifOdjel = radnik.sifOdjel
141 JOIN mjesto ON radnik.pbrStan = mjesto.pbrMjesto
142 JOIN zupanija ON mjesto.sifZupanija = zupanija.sifZupanija
143 WHERE zupanija.sifZupanija = zupa;
144 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
145 SET obr = 0;
146 SET doh = 0;
147 OPEN kur;
148 SELECT FOUND_ROWS() INTO doh;
149 petlja: LOOP
150 FETCH kur INTO sifO, najmanja;
151 IF flag=TRUE THEN
152 LEAVE petlja;
153 END IF;
154 CALL fooPlaca(sifO);
155 SET obr = obr + 1;
156 END LOOP;
157 CLOSE kur;
158END; //
159DELIMITER ;
160
161CALL fooPlaca1(21, @a, @b);
162SELECT @a AS obradeno, @b AS dohvaceni;
163
164/*U bazi studenti: Napisati funkciju koja će primiti naziv smjera. Funkcija mora svim studentima
165sa zadanog smjera postaviti datum upisa na 1.9.2016. Funkcija vraća broj obrađenih zapisa..
166Zadatak je potrebno riješiti koristeći kursore i odgovarajuće handlere.
167Napisati primjer poziva funkcije*/
168
169DROP FUNCTION IF EXISTS fooSmjerovi;
170DELIMITER //
171CREATE FUNCTION fooSmjerovi(nazivS VARCHAR(50))
172RETURNS INT
173DETERMINISTIC
174BEGIN
175 DECLARE trenutni_id INT;
176 DECLARE broj INT DEFAULT 0;
177 DECLARE flag BOOL;
178 DECLARE kur CURSOR FOR SELECT studenti.jmbag FROM studenti
179 JOIN smjerovi ON studenti.idSmjer = smjerovi.id
180 WHERE smjerovi.naziv = nazivS;
181 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
182 OPEN kur;
183 petlja:LOOP
184 FETCH kur INTO trenutni_id;
185 IF flag = TRUE THEN
186 LEAVE petlja;
187 END IF;
188 UPDATE studenti
189 SET datumUpisa = "2016-09-01"
190 WHERE jmbag = trenutni_id;
191 SET broj = broj + 1;
192 END LOOP;
193 CLOSE kur;
194 RETURN broj;
195END; //
196DELIMITER ;
197
198SELECT fooSmjerovi("smjer informatika");
199
200/*U bazi studenti: Napisati proceduru koja će primiti naziv županije i broj N. Procedura mora za
201sva mjesta u zadanoj županiji ispisati:
202a. Naziv mjesta
203b. „Velik broj nastavnika“ – ako u tom mjestu stanuje više od N nastavnika
204c. „Mali broj nastavnika“ – ako u tom mjestu stanuje N ili manje nastavnika*/
205
206DROP PROCEDURE IF EXISTS fooNastavnici;
207DELIMITER //
208CREATE PROCEDURE fooNastavnici(nazivZ VARCHAR(50), N INT)
209BEGIN
210 DECLARE broj INT DEFAULT 0;
211 DECLARE flag BOOL;
212 DECLARE nazivM VARCHAR(50);
213 DECLARE kur CURSOR FOR SELECT COUNT(zupanije.id), mjesta.nazivMjesto FROM nastavnici
214 JOIN mjesta ON nastavnici.postBr = mjesta.postbr
215 JOIN zupanije ON mjesta.idZupanija = zupanije.id
216 WHERE zupanije.nazivZupanija = nazivZ
217 GROUP BY mjesta.nazivMjesto;
218 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
219 DROP TEMPORARY TABLE IF EXISTS tmp;
220 CREATE TEMPORARY TABLE
221 tmp(naziv VARCHAR(50),
222 broj_nastavnika VARCHAR(50));
223 OPEN kur;
224 petlja:LOOP
225 FETCH kur INTO broj, nazivM;
226 IF flag = TRUE THEN
227 LEAVE petlja;
228 END IF;
229 IF broj > N THEN
230 INSERT INTO tmp(naziv, broj_nastavnika) VALUES (nazivM, "Velik broj nastavnika");
231 ELSE
232 INSERT INTO tmp(naziv, broj_nastavnika) VALUES (nazivM, "Mali broj nastavnika");
233 END IF;
234 END LOOP;
235 CLOSE kur;
236 SELECT * FROM tmp;
237END; //
238DELIMITER ;
239
240CALL fooNastavnici("Grad Zagreb", 17);
241
242
243
244 /*U bazi studenti: Napisati proceduru koja će studenta sa ocjenom 1 iz nekog kolegija
245kopirati u privremenu tablicu s atributima (opisUsmenog, ime, prezime, jmbag).
246U atribut opisUsmenog napisati "Usmeni iz: X, Y, Z..." XYZ su kolegiji iz kojih student ima
247barem jednu jedinicu.*/
248
249DROP PROCEDURE IF EXISTS fooUsmeni;
250DELIMITER //
251CREATE PROCEDURE fooUsmeni()
252BEGIN
253 DECLARE trenutni_jmbag INT;
254 DECLARE trenutni_kolegij, imS, prezS VARCHAR(100);
255 DECLARE flag BOOL;
256 DECLARE broj INT DEFAULT 0;
257 DECLARE kur CURSOR FOR SELECT studenti.jmbag, kolegiji.naziv, studenti.ime, studenti.prezime FROM studenti
258 JOIN ocjene ON studenti.jmbag = ocjene.jmbagStudent
259 JOIN kolegiji ON ocjene.idKolegij = kolegiji.id
260 WHERE ocjene.ocjena = 1
261 GROUP BY studenti.jmbag;
262 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
263 SET flag = FALSE;
264 DROP TABLE IF EXISTS tmp;
265 CREATE TEMPORARY TABLE tmp(opisUsmenog VARCHAR(300), ime VARCHAR(50), prezime VARCHAR(50), jmbag INT);
266 OPEN kur;
267 petlja:LOOP
268 FETCH kur INTO trenutni_jmbag, trenutni_kolegij, imS, prezS;
269
270 IF flag = TRUE THEN
271 LEAVE petlja;
272 END IF;
273
274 SELECT COUNT(jmbag) INTO broj FROM tmp
275 WHERE jmbag = trenutni_jmbag;
276
277 IF broj = 0 THEN
278 INSERT INTO tmp(opisUsmenog, ime, prezime, jmbag) VALUES (CONCAT("Usmeni iz: ", trenutni_kolegij), imS, prezS, trenutni_jmbag);
279 ELSE
280 UPDATE tmp
281 SET opisUsmenog = CONCAT(opisUsmenog, ", ", trenutni_kolegij)
282 WHERE jmbag = trenutni_jmbag;
283 END IF;
284 END LOOP;
285 CLOSE kur;
286 SELECT * FROM tmp;
287END; //
288DELIMITER ;
289
290CALL fooUsmeni();
291
292/* U bazi autoradionica: Napisati proceduru koja će ispisati podatke klijenta (ime, prezime i
293Å¡ifru), te uz njega ispisati iznos popusta koji je taj klijent ostvario. Popust se dodjeljuje po
294slijedećem principu:
295- 5% popusta za do 10 sati kvara,
296- 10% popusta za 11 do 20 sati kvara,
297- 15% popusta za 21 do 30 sati kvara,
298- 20% popusta za 31 do 40 sati kvara.
299Potrebno je koristiti kursor, handler i privremenu tablicu. Ispisati samo one klijente koji imaju
300pravo na popust. Osigurat da po završetku procedure privremena tablica više nije dostupna.*/
301
302DROP PROCEDURE IF EXISTS fooKlijent;
303DELIMITER //
304CREATE PROCEDURE fooKlijent()
305BEGIN
306 DECLARE trenutna_sifra INT;
307 DECLARE i, p VARCHAR(50);
308 DECLARE flag BOOL;
309 DECLARE broj INT DEFAULT 0;
310 DECLARE kur CURSOR FOR SELECT klijent.imeKlijent, klijent.prezimeKlijent, klijent.sifKlijent, SUM(satiKvar) FROM klijent
311 JOIN nalog ON klijent.sifKlijent = nalog.sifKlijent
312 JOIN kvar ON nalog.sifKvar = kvar.sifKvar
313 GROUP BY klijent.imeKlijent, klijent.prezimeKlijent, klijent.sifKlijent;
314 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
315
316 DROP TEMPORARY TABLE IF EXISTS tmp;
317 CREATE TEMPORARY TABLE tmp(ime VARCHAR(50), prezime VARCHAR(50), sifra INT, iznosPopusta VARCHAR(50));
318
319 OPEN kur;
320 petlja:LOOP
321 FETCH kur INTO i, p, trenutna_sifra, broj;
322 IF flag = TRUE THEN
323 LEAVE petlja;
324 END IF;
325 IF broj <= 10 THEN
326 INSERT INTO tmp(ime, prezime, sifra, iznosPopusta) VALUES (i, p, trenutna_sifra, "5%");
327 END IF;
328 IF broj >= 11 AND broj <= 20 THEN
329 INSERT INTO tmp(ime, prezime, sifra, iznosPopusta) VALUES (i, p, trenutna_sifra, "10%");
330 END IF;
331 IF broj >= 21 AND broj <= 30 THEN
332 INSERT INTO tmp(ime, prezime, sifra, iznosPopusta) VALUES (i, p, trenutna_sifra, "15%");
333 END IF;
334 IF broj >= 31 AND broj <= 40 THEN
335 INSERT INTO tmp(ime, prezime, sifra, iznosPopusta) VALUES (i, p, trenutna_sifra, "20%");
336 END IF;
337 END LOOP;
338 CLOSE kur;
339 SELECT * FROM tmp;
340 DROP TEMPORARY TABLE IF EXISTS tmp;
341END; //
342DELIMITER ;
343
344
345CALL fooKlijent();
346
347/*U bazi studenti: Potrebno je tablici studenti dodati novi atribut prosjecnaOcjena.
348Napisati proceduru koja će pomoću kursora i handlera svim studentima izraÄunati
349njihovu prosjeÄnu ocjenu te ju upisat u novokreirani atribut. Ukoliko student
350nema nijednu ocjenu potrebno je upisati vrijednost 'NEOCJENJEN' (obratiti pažnju na
351tip podatka atributa prosjecnaOcjena). Procedura treba ispisat broj dohvaćenih
352podataka, te broj neocijenjenih studenata*/
353
354ALTER TABLE studenti
355ADD COLUMN prosjecnaOcjena VARCHAR(50);
356
357DROP PROCEDURE IF EXISTS fooOcjene;
358DELIMITER //
359CREATE PROCEDURE fooOcjene()
360BEGIN
361 DECLARE trenutni_id INT;
362 DECLARE prosjek DOUBLE;
363 DECLARE flag BOOL;
364 DECLARE doh, neoc INT DEFAULT 0;
365 DECLARE kur CURSOR FOR SELECT studenti.jmbag, AVG(ocjene.ocjena) FROM studenti
366 LEFT JOIN ocjene ON studenti.jmbag = ocjene.jmbagStudent
367 GROUP BY studenti.jmbag;
368 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
369 OPEN kur;
370 SELECT FOUND_ROWS() INTO doh;
371 petlja:LOOP
372 FETCH kur INTO trenutni_id, prosjek;
373 IF flag = TRUE THEN
374 LEAVE petlja;
375 END IF;
376
377 IF prosjek IS NOT NULL THEN
378 UPDATE studenti
379 SET prosjecnaOcjena = prosjek
380 WHERE jmbag= trenutni_id;
381 ELSE
382 UPDATE studenti
383 SET prosjecnaOcjena = "NEOCIJENJEN"
384 WHERE jmbag = trenutni_id;
385 SET neoc = neoc + 1;
386 END IF;
387 END LOOP;
388 CLOSE kur;
389 SELECT doh AS Dohvaceni, neoc AS Neocijenjeni;
390END; //
391DELIMITER ;
392
393CALL fooOcjene();