· 9 years ago · Nov 08, 2016, 12:00 PM
1/*1.U bazi autoradionica: Napisati proceduru koja će preko parametra primiti oznaku radionice.
2Procedura mora ispisati ime i prezime te iznos plaće za onog radnika(radnike) koji radi u
3zadanoj radionici i ima najveću plaću. Iznos plaće potrebno je raÄunati kao umnožak
4vrijednosti atributa koefPlace i iznosOsnovice.
5*/
6
7
8DELIMITER //
9CREATE PROCEDURE procedura1(IN oznaka VARCHAR(55))
10BEGIN
11
12DECLARE placa DOUBLE;
13
14SELECT MAX(KoefPlaca* IznosOsnovice) INTO placa FROM radnik
15JOIN odjel ON radnik.sifOdjel=odjel.sifOdjel
16JOIN kvar ON odjel.sifOdjel=kvar.sifOdjel
17JOIN rezervacija ON kvar.sifKvar=rezervacija.sifKvar
18JOIN radionica ON rezervacija.oznRadionica=radionica.oznRadionica
19WHERE radionica.oznRadionica= oznaka;
20
21
22SELECT radnik.imeRadnik, radnik.prezimeRadnik, MAX(KoefPlaca*IznosOsnovice)
23
24FROM radnik JOIN odjel ON radnik.sifOdjel=odjel.sifOdjel
25JOIN kvar ON odjel.sifOdjel=kvar.sifOdjel
26JOIN rezervacija ON kvar.sifKvar=rezervacija.sifKvar
27JOIN radionica ON rezervacija.oznRadionica=radionica.oznRadionica
28WHERE radionica.oznRadionica= oznaka
29AND KoefPlaca*IznosOsnovice = placa;
30
31END //
32DELIMITER ;
33
34CALL procedura1("R7");
35
36/*2.U bazi studenti: Napisati proceduru "dohvatiJMBAG" koja će za ulazne parametre imati:
37- ime studenta - VARCHAR(50)
38- prezime studenta - VARCHAR(50)
39Procedura ispisuje ime, prezime, te jmbag pronađenih studenata.
40*/
41
42
43
44DROP PROCEDURE IF EXISTS dohvatiJMBAG;
45DELIMITER //
46CREATE PROCEDURE dohvatiJMBAG(IN imest VARCHAR(50), IN prezimest VARCHAR(50))
47BEGIN
48 SELECT ime, prezime, jmbag FROM studenti
49 WHERE ime = imest
50 AND prezime = prezimest;
51END //
52DELIMITER ;
53
54/* 3. U bazi autoradionica: Napisati proceduru koja preko iste varijable prima podatak o radniku
55(sifRadnik), te vraća ukupan broj naloga na kojima je zadani radnik radio. */
56
57
58DROP PROCEDURE IF EXISTS broj;
59DELIMITER //
60CREATE PROCEDURE brojnaloga(INOUT sifra INT)
61BEGIN
62
63SELECT COUNT(nalog.sifRadnik) INTO sifra FROM nalog
64WHERE nalog.sifRadnik=sifra;
65
66END //
67DELIMITER ;
68
69SET @k = 122;
70
71CALL brojnaloga(@k);
72
73SELECT @k;
74
75
76/* 4. U bazi studenti: Napisati proceduru "izbrojiNastavnikeNaSmjeru" koja će za ulazno/izlazni
77parametar imati ID Smjera. U isti parametar potrebno je vratiti broj nastavnika, dok je u
78samoj proceduri potrebno ispisati sve nastavnike tog smjera.*/
79
80
81
82DROP PROCEDURE IF EXISTS broj;
83DELIMITER //
84CREATE PROCEDURE izbrojiNastavnikeNaSmjeru(INOUT idsmjera INT)
85BEGIN
86
87SELECT nastavnici.* FROM nastavnici
88JOIN izvrsitelji ON nastavnici.jmbg=izvrsitelji.jmbgNastavnik
89JOIN kolegiji ON izvrsitelji.idKolegij=kolegiji.id
90JOIN smjerovi ON kolegiji.idSmjer=smjerovi.id
91
92WHERE smjerovi.id=idsmjera;
93
94
95SELECT COUNT(nastavnici.jmbg) INTO idsmjera FROM nastavnici
96JOIN izvrsitelji ON nastavnici.jmbg=izvrsitelji.jmbgNastavnik
97JOIN kolegiji ON izvrsitelji.idKolegij=kolegiji.id
98JOIN smjerovi ON kolegiji.idSmjer=smjerovi.id
99
100WHERE smjerovi.id=idsmjera;
101
102
103END //
104DELIMITER ;
105
106SET @k = 1;
107
108CALL izbrojiNastavnikeNaSmjeru(@k);
109
110SELECT @k;
111
112
113/* 5. U bazi autoradionica: Napravite funkciju „brojVozilaRegUMjestu“ koja će za
114ulazni parametar primati oznaku mjesta, a vraćat će broj vozila koje su klijenti registrirali u
115tom mjestu. Koristeći izrađenu funkciju, ispisati broj vozila u svim mjestima gdje je
116registrirano barem jedno vozilo. Sortirati silazno po broju vozila, te ispis ograniÄiti na prvih 10
117zapisa. */
118
119
120
121
122
123DROP FUNCTION IF EXISTS brojVozilaRegUMjestu ;
124DELIMITER //
125CREATE FUNCTION brojVozilaRegUMjestu(oznaka VARCHAR(55)) RETURNS INT
126
127DETERMINISTIC
128
129BEGIN
130
131DECLARE vrati INT;
132
133SELECT COUNT(klijent.pbrReg) INTO vrati
134
135FROM klijent
136
137WHERE klijent.pbrMjesto=oznaka;
138
139RETURN vrati;
140
141END //
142DELIMITER ;
143
144SELECT mjesto.nazivMjesto, COUNT(klijent.pbrReg) AS "Broj registriranih auti" FROM klijent JOIN mjesto
145ON klijent.pbrReg = mjesto.pbrMjesto
146WHERE brojVozilaRegUMjestu(mjesto.pbrMjesto) IS NOT NULL
147GROUP BY nazivMjesto
148ORDER BY COUNT(klijent.pbrReg) DESC
149LIMIT 10;
150
151
152/* 6. U bazi studenti: Tablici kolegiji dodati novi atribut odlicnihStudenata odgovarajućeg tipa
153podatka. Napisati proceduru koja će zadanom kolegiju u dotiÄni atribut upisati koliko ukupno
154studenata ima ocjenu odliÄan iz tog kolegija. Potrebno je prebrojati samo one studente koji
155su se na studij upisali u posljednje 3 godine (koristiti funkciju za dohvat trenutnog datuma i
156vremena s poslužitelja).
157*/
158
159
160ALTER TABLE kolegiji ADD odlicnihStudenata INT;
161
162DROP PROCEDURE IF EXISTS dodajOdlicne;
163DELIMITER //
164CREATE PROCEDURE dodajOdlicne(IN imeKol INT)
165 BEGIN
166 DECLARE broj INT;
167 SELECT COUNT(*) INTO broj FROM kolegiji
168 JOIN ocjene ON kolegiji.id = ocjene.idKolegij
169 JOIN studenti ON ocjene.jmbagStudent = studenti.jmbag
170 WHERE kolegiji.id = imeKol
171 AND ocjene.ocjena = 5
172 AND YEAR(datumUpisa) >= YEAR(CURDATE())-10;
173
174 UPDATE kolegiji
175 SET odlicnihStudenata = broj
176 WHERE kolegiji.id = imeKol;
177 END //
178DELIMITER ;
179
180CALL dodajOdlicne(45);
181
182
183/* 7. U bazi studenti: Napisati funkciju „prosjekOcjenaPoUstanoviISmjeru“ koja za ulazne
184parametre ima naziv ustanove i naziv smjera. Funkcija mora vratiti prosjek svih ocjena na
185određenom smjeru i ustanovi */
186
187
188DROP FUNCTION IF EXISTS prosjekOcjenaPoUstanoviIsmjeru ;
189DELIMITER //
190CREATE FUNCTION prosjekOcjenaPoUstanoviIsmjeru(nazivustanove VARCHAR(55), nazivsmjera VARCHAR(55)) RETURNS DOUBLE
191
192DETERMINISTIC
193
194BEGIN
195
196DECLARE prosjek DOUBLE;
197
198SELECT AVG(ocjene.ocjena) INTO prosjek FROM ocjene
199JOIN kolegiji ON ocjene.idKolegij=kolegiji.id
200JOIN smjerovi ON kolegiji.idSmjer=smjerovi.id
201JOIN ustanove ON smjerovi.oibUstanova=ustanove.oib
202
203WHERE smjerovi.naziv=nazivsmjera AND ustanove.naziv=nazivustanove;
204
205RETURN prosjek;
206
207END //
208DELIMITER ;
209
210
211
212SELECT prosjekOcjenaPoUstanoviIsmjeru("TehniÄko veleuÄiliÅ¡te u Zagrebu", "smjer informatika")
213
214
215/* 8. U bazi autoradionica: Napisati funkciju koja će svim radnicima iz zadanog odjela povećati
216koeficijent plaće za 0,5, a svim ostalima smanjiti za isti koeficijent. Funkcija vraća vrijednost 1. */
217
218
219
220DROP FUNCTION IF EXISTS radionicaplaca ;
221DELIMITER //
222CREATE FUNCTION radionicaplaca(idodjel INT) RETURNS INT
223
224DETERMINISTIC
225
226BEGIN
227
228UPDATE radnik
229SET radnik.KoefPlaca=KoefPlaca + 0.5
230WHERE radnik.sifOdjel =idodjel;
231
232UPDATE radnik
233SET radnik.KoefPlaca=KoefPlaca - 0.5
234WHERE radnik.sifOdjel != idodjel;
235
236
237RETURN 1;
238END//
239DELIMITER ;
240
241SELECT radionicaplaca(27);
242
243
244/*------------------------------------------*/
245
246DROP FUNCTION IF EXISTS fooRad;
247DELIMITER //
248CREATE FUNCTION fooRad(sifraOdjel INT)
249RETURNS INT
250DETERMINISTIC
251BEGIN
252 UPDATE radnik
253 SET KoefPlaca = KoefPlaca + 0.5
254 WHERE sifOdjel = sifraOdjel;
255 UPDATE radnik
256 SET KoefPlaca = KoefPlaca - 0.5
257 WHERE sifOdjel != sifraOdjel;
258 RETURN 1;
259END; //
260DELIMITER ;
261
262SELECT fooRad(27);
263
264
265
266/* 9. U bazi studenti: Napisati funkciju koja će za zadani smjer vratiti podatak o broju koliko
267ukupno studenata studira na zadanom smjeru, a poštanski brojevi prebivanja i stanovanja su
268im razliÄiti. */
269
270
271
272
273DROP FUNCTION IF EXISTS studosi;
274DELIMITER //
275CREATE FUNCTION studosi(sifrasmjer INT)
276RETURNS INT
277DETERMINISTIC
278BEGIN
279
280DECLARE broj INT;
281
282SELECT COUNT(studenti.jmbag) INTO broj FROM studenti
283WHERE studenti.idSmjer = sifrasmjer
284AND postBrStanovanja != postBrPrebivanje;
285
286
287 RETURN broj;
288END; //
289DELIMITER ;
290
291
292SELECT studosi(1);
293
294
295
296/* 10. U bazi autoradionica: Napisati funkciju koja će preko parametra primiti podatak o klijentu
297(sifKlijent). Funkcija mora za zadanog klijenta vratiti podatak o radionici (OznRadionica) u
298kojoj se radi na nalogu vezanom uz zadanog klijenta. Ako je za klijenta evidentirano više
299naloga, tada je potrebno vratiti radionicu posljednje zaprimljenog naloga. */
300
301
302DROP FUNCTION IF EXISTS klijentic;
303DELIMITER //
304CREATE FUNCTION klijentic(sifraklijent INT)
305RETURNS VARCHAR(55)
306DETERMINISTIC
307BEGIN
308
309DECLARE vrati VARCHAR(55);
310
311SELECT oznRadionica INTO vrati FROM radionica
312NATURAL JOIN rezervacija
313NATURAL JOIN kvar
314NATURAL JOIN nalog
315NATURAL JOIN klijent
316
317WHERE klijent.sifKlijent=sifraklijent
318ORDER BY datPrimitkaNalog DESC
319LIMIT 0,1;
320
321RETURN vrati;
322END //
323DELIMITER ;
324
325
326SELECT klijentic(1139);
327
328
329/*11. U bazi studenti: Potrebno je napisati proceduru koja će za zadani fakultet vratiti broj
330profesora i asistenata (dvije vrijednosti) na tom fakultetu. Zanemariti podatak ako je ista
331osoba i profesor i asistent, te obratiti pažnju što ako jedan profesor/asistent radi na više
332kolegija*/
333
334
335
336DROP PROCEDURE IF EXISTS faksolito;
337DELIMITER //
338CREATE PROCEDURE faksolito(IN idfaksolito VARCHAR(50), OUT brojprofa INT, OUT brojstudosa INT)
339 BEGIN
340
341 SELECT DISTINCT COUNT(izvrsitelji.jmbgNastavnik) INTO brojprofa FROM izvrsitelji
342 JOIN kolegiji ON izvrsitelji.idKolegij=kolegiji.id
343 JOIN smjerovi ON kolegiji.idSmjer=smjerovi.id
344 JOIN ustanove ON smjerovi.oibUstanova=ustanove.oib
345
346 WHERE idUlogaIzvrsitelja="1"
347 AND ustanove.oib=idfaksolito;
348
349
350 SELECT DISTINCT COUNT(izvrsitelji.jmbgNastavnik) INTO brojstudosa FROM izvrsitelji
351 JOIN kolegiji ON izvrsitelji.idKolegij=kolegiji.id
352 JOIN smjerovi ON kolegiji.idSmjer=smjerovi.id
353 JOIN ustanove ON smjerovi.oibUstanova=ustanove.oib
354
355 WHERE idUlogaIzvrsitelja="2"
356 AND ustanove.oib=idfaksolito;
357
358 END //
359DELIMITER ;
360
361
362
363
364CALL faksolito("08814003451", @n, @m);
365
366SELECT @n, @m;
367
368
369/* 12. U bazi autoradionica: Napisat funkciju koja vraća datum sa najviše zaprimljenih naloga.
370Ako ih je više vratiti najnoviji datum.
371*/
372
373DROP FUNCTION IF EXISTS datumic;
374DELIMITER //
375CREATE FUNCTION datumic()
376RETURNS DATE
377DETERMINISTIC
378BEGIN
379 DECLARE datum DATE;
380 SELECT datPrimitkaNalog INTO datum FROM nalog
381 GROUP BY nalog.datPrimitkaNalog
382 HAVING COUNT(nalog.sifKvar)
383 ORDER BY COUNT(nalog.datPrimitkaNalog) DESC, datPrimitkaNalog DESC
384 LIMIT 0,1;
385 RETURN datum;
386END; //
387DELIMITER ;
388
389
390SELECT datumic();
391
392
393
394/* 13. U bazi studenti: Napisati proceduru koja će za zadanu ustanovu, zadanu prosjeÄnu ocjenu i
395zadani broj studenata, ispisati naziv ustanove, naziv smjera, ime, prezime i jmbag svih
396studenata koji imaju prosjeÄnu ocjenu veću od zadane. Potrebno je ispisati samo N ‘najboljih’
397studenata. */
398
399DROP PROCEDURE IF EXISTS ludilo;
400DELIMITER //
401CREATE PROCEDURE ludilo(IN idfaksolito VARCHAR(50), IN ocjenice INT, IN studentici INT)
402
403 BEGIN
404
405 SELECT ustanove.naziv, smjerovi.naziv, studenti.ime, studenti.prezime, studenti.jmbag
406 FROM ustanove
407 JOIN smjerovi ON ustanove.oib=smjerovi.oibUstanova
408 JOIN studenti ON smjerovi.id=studenti.idSmjer
409 JOIN ocjene ON studenti.jmbag=ocjene.jmbagStudent
410 WHERE ustanove.oib=idfaksolito
411 GROUP BY ustanove.naziv, smjerovi.naziv, studenti.ime, studenti.prezime, studenti.jmbag, ustanove.oib
412
413 HAVING AVG(ocjene.ocjena) > ocjenice
414
415 LIMIT 0, studentici;
416
417 END //
418DELIMITER ;
419
420
421
422CALL ludilo("08814003451", 2.0, 20);
423
424
425
426
427
428
429
430
431
432
433
434/* broj mjesta u zupaniji */
435
436DROP PROCEDURE IF EXISTS brojmjesta;
437DELIMITER //
438CREATE PROCEDURE brojmjesta(IN zupanijaime VARCHAR(50), OUT brojmjesta INT)
439BEGIN
440
441DECLARE pomnaziv VARCHAR(50);
442DECLARE pombroj INT;
443
444SELECT zupanija.nazivZupanija, COUNT(mjesto.pbrMjesto) INTO pomnaziv, brojmjesta
445FROM zupanija
446JOIN mjesto ON zupanija.sifZupanija=mjesto.sifZupanija
447WHERE zupanija.nazivZupanija=zupanijaime;
448
449
450END //
451DELIMITER ;
452
453CALL brojmjesta("ZagrebaÄka", @i);
454
455SELECT @i;
456
457/* davorov */
458
459
460DROP PROCEDURE IF EXISTS brojMjesta;
461DELIMITER //
462CREATE PROCEDURE brojMjesta(IN zupanija VARCHAR(50))
463BEGIN
464 SELECT COUNT(pbrMjesto) FROM mjesto
465 JOIN zupanija ON mjesto.sifZupanija = zupanija.sifZupanija
466 WHERE zupanija.nazivZupanija = zupanija;
467END; //
468DELIMITER ;
469
470
471
472
473
474
475/*(otprilike) Napisati funkciju koja za zadanu godinu na kvarovima koji su potrosili najvise sati rada
476 povecava radnike za 1. */
477
478DROP FUNCTION IF EXISTS fooSati;
479DELIMITER //
480CREATE FUNCTION fooSati(godina INT)
481RETURNS INT
482DETERMINISTIC
483BEGIN
484 DECLARE pom INT;
485 DECLARE pomsifra INT;
486 DECLARE pomnaziv VARCHAR(50);
487
488 SELECT kvar.sifKvar,kvar.nazivKvar , SUM(satiKvar) INTO pomsifra,pomnaziv,pom FROM kvar
489 JOIN nalog ON nalog.sifKvar=kvar.sifKvar
490 WHERE YEAR(nalog.datPrimitkaNalog)=godina
491
492 GROUP BY kvar.sifKvar,kvar.nazivKvar
493 ORDER BY SUM(satiKvar) DESC
494 LIMIT 0,1;
495
496 UPDATE kvar JOIN nalog ON nalog.sifKvar=kvar.sifKvar
497 SET kvar.brojRadnika=kvar.brojRadnika+1
498 WHERE YEAR(nalog.datPrimitkaNalog)=godina AND kvar.sifKvar=pomsifra;
499
500 RETURN 1;
501END; //
502DELIMITER ;
503
504
505SELECT * FROM nalog
506JOIN kvar ON nalog.sifKvar= kvar.sifKvar
507WHERE YEAR(nalog.datPrimitkaNalog) = 2004;
508
509SELECT fooSati(2004);
510SELECT * FROM nalog
511JOIN kvar ON nalog.sifKvar= kvar.sifKvar
512WHERE YEAR(nalog.datPrimitkaNalog) = 2004;
513
514
515/* zamjeni 2 radnika njihove sifre i napisati s kim je zamijenjeno */
516
517DROP FUNCTION IF EXISTS fooZamjena;
518DELIMITER //
519CREATE FUNCTION fooZamjena(sif1 INT, sif2 INT)
520RETURNS VARCHAR(100)
521DETERMINISTIC
522BEGIN
523 DECLARE prvi VARCHAR(10);
524 DECLARE drugi VARCHAR(20);
525 DECLARE poruka VARCHAR(100);
526
527
528 UPDATE radnik
529 SET sifRadnik = 999
530 WHERE sifRadnik = sif2;
531
532 UPDATE radnik
533 SET sifRadnik = sif2
534 WHERE sifRadnik = sif1;
535
536 UPDATE radnik
537 SET sifRadnik = sif1
538 WHERE sifRadnik = 999;
539
540 SELECT imeRadnik INTO prvi FROM radnik
541 WHERE sifRadnik = sif2;
542 SELECT imeRadnik INTO drugi FROM radnik
543 WHERE sifRadnik = sif1;
544
545 SET poruka = CONCAT(prvi, " je zamijenjen s ", drugi);
546 RETURN poruka;
547END; //
548DELIMITER ;
549
550
551
552
553
554
555
556
557
558/* DROP FUNCTION IF EXISTS fooZamjena;
559DELIMITER //
560CREATE FUNCTION fooZamjena(sif1 INT, sif2 INT)
561RETURNS VARCHAR(100)
562DETERMINISTIC
563BEGIN
564 DECLARE prvi VARCHAR(10);
565 DECLARE drugi VARCHAR(20);
566 DECLARE poruka VARCHAR(100);
567 UPDATE radnik
568 SET sifRadnik = 999
569 WHERE sifRadnik = sif2;
570 UPDATE radnik
571 SET sifRadnik = sif2
572 WHERE sifRadnik = sif1;
573 UPDATE radnik
574 SET sifRadnik = sif1
575 WHERE sifRadnik = 999;
576 SELECT imeRadnik INTO prvi FROM radnik
577 WHERE sifRadnik = sif2;
578 SELECT imeRadnik INTO drugi FROM radnik
579 WHERE sifRadnik = sif1;
580 SET poruka = CONCAT(prvi, " je zamijenjen s ", drugi);
581 RETURN poruka;
582END; //
583DELIMITER ; */