· 10 years ago · Sep 23, 2016, 09:28 PM
1ISHOD 4
2
3NapiÅ¡ite proceduru koja prima ime i prezime komercijalist, umeće novog komercijalistu u tablicu Komercijalist i kroz izlazni parametar vraća vrijednost primarnog kljuÄa umetnutog retka. Ako već postoji komercijalist zadanog imena i prezimena, ne treba ga upisivati ponovo i u tom sluÄaju kroz izlazni parametar vratite vrijednost -1. Pozovite proceduru dva puta s istim imenom i prezimenom te ispiÅ¡ite vraćenu vrijednost.
4
5create proc pr1
6@IDRedak int OUTPUT,
7@Ime nvarchar(50),
8@Prezime nvarchar(50)
9as
10if not exists (select * from Komercijalist where Ime=@Ime and Prezime=@Prezime)
11 begin
12 insert into Komercijalist (Ime,Prezime) values (@Ime,@Prezime)
13 set @IDRedak=SCOPE_IDENTITY()
14 end
15else
16 begin
17 set @IDRedak=-1
18 end
19go
20
21declare @t int, @Ime nvarchar(50),@Prezime nvarchar(50)
22exec pr1 @t output,'Luka','Sepat'
23print @t
24go
25
26declare @t int, @Ime nvarchar(50),@Prezime nvarchar(50)
27exec pr1 @t output, 'Luka','Sepat'
28print @t
29go
30
31select * from Komercijalist
32drop proc pr1
33go
34_________________________________________________________________________________________
35Napišite proceduru koja prima tri parametra: naziv države i dva naziva grada iz te države. Ako država sa zadanim nazivom postoji, umetnite gradove i vežite ih na tu državu.
36Ako država sa zadanim nazivom ne postoji, prvo umetnite državu, a zatim na nju vežite gradove.
37
38create proc pr2
39@Drzava nvarchar(50),
40@Grad1 nvarchar(50),
41@Grad2 nvarchar(50)
42as
43declare @IDDrzava int
44
45if exists(select * from Drzava where Naziv=@Drzava)
46 begin
47 select IDDrzava=@IDDrzava from Drzava
48 where Naziv=@Drzava
49 end
50else
51 begin
52 insert into Drzava values (@Drzava)
53 set @IDDrzava=SCOPE_IDENTITY()
54 end
55 insert into Grad (Naziv,DrzavaID)
56 values (@Grad1,@IDDrzava), (@Grad2,@IDDrzava)
57go
58
59exec pr2 'Kanada','Toronto','Vancouver'
60go
61
62select * from Grad
63select * from Drzava
64go
65_________________________________________________________________________________________
66Implementirajte CRUD operacije na tablici Potkategorija pomoću dvije procedure.
67Jedna neka obavlja operacije C i U, druga R i D.
68Napravite umetanje, izmjenu, dohvaćanje i brisanje jedne potkategorije pomoću napravljenih objekata.
69
70CREATE PROC prCreateOrUpdate
71 @IDPotkategorije INT, /* NULL - CREATE, OSTALO - UPDATE */
72 @NazivPotkategorije nvarchar(50),
73 @IDKategorije INT
74AS
75 IF @IDPotkategorije IS NULL BEGIN /* CREATE */
76 INSERT INTO Potkategorija (KategorijaID, Naziv)
77 VALUES
78 (@IDKategorije, @NazivPotkategorije)
79 END
80 ELSE BEGIN /* UPDATE */
81 UPDATE Potkategorija
82 SET
83 KategorijaID = @IDKategorije,
84 Naziv = @NazivPotkategorije
85 WHERE IDPotkategorija = @IDPotkategorije
86 END
87GO
88
89CREATE PROC prReadOrDelete
90 @IDPotkategorije INT,
91 @DELETE bit /* 0 - READ, 1 - DELETE */
92AS
93 IF @DELETE = 0 BEGIN /* READ */
94 SELECT *
95 FROM Potkategorija
96 WHERE IDPotkategorija = @IDPotkategorije
97 END
98 ELSE IF @DELETE = 1 BEGIN /* DELETE */
99 DELETE
100 FROM Potkategorija
101 WHERE IDPotkategorija = @IDPotkategorije
102 END
103GO
104_________________________________________________________________________________________
105
106--1 Napišite proceduru koja prima naziv potkategorije i ID kategorije, umeće novu potkategoriju za tu kategoriju u tablicu Potkategorija
107--i kroz izlazni parametar vraća vrijednost primarnog kljuÄa umetnutog retka. Ako već postoji potkategorija zadanog imena za tu kategoriju,
108--ne treba je upisivati ponovno i u tom sluÄaju kroz izlazni parametar vratite vrijednost -1. Pozovite proceduru dva puta s
109--istim nazivom potkategorije i ID-om kategorije te ispišite vraćenu vrijednost.
110
111--1
112create proc I4zad1
113@naziv nvarchar(50),
114@IDKat int,
115@PRkljuc int output
116as
117declare @katID int
118declare @nazivP nvarchar(50)
119select @katID=KategorijaID, @nazivP=naziv from Potkategorija
120if @katID=@IDKat and @nazivP=@naziv
121begin
122set @PRkljuc = -1
123end
124else
125begin insert into Potkategorija(naziv,KategorijaID)values( @naziv,@IDKat)
126set @PRkljuc = SCOPE_IDENTITY()
127end
128go
129declare @ispis int
130exec I4zad1 'NoviBicikli',1, @ispis output
131print @ispis
132exec I4zad1 'NoviBicikli',1, @ispis output
133print @ispis
134go
135
136drop proc I4zad1
137select*
138from Potkategorija
139go
140--1.1
141create proc I4zad1_1
142@naziv nvarchar(50),
143@iddrz int,
144@kljuc int output
145as
146declare @nazivdr nvarchar(50)
147declare @drid int
148select @nazivdr = Naziv, @drid = DrzavaID from grad
149
150if @nazivdr = @naziv and @iddrz = @drid
151 begin
152 set @kljuc = -1
153 print 'Grad je vec kreiran'
154 end
155else
156 begin
157 insert into Grad(Naziv, DrzavaID) values(@naziv, @iddrz)
158 set @kljuc = SCOPE_IDENTITY()
159 end
160go
161
162declare @ispis nvarchar(50)
163exec I4zad1_1 'Kyoto', 3, @ispis output
164print @ispis
165go
166
167drop proc zad1
168
169--provjera
170select * from Grad
171delete from Grad where IDGrad = 15
172go
173--1.2 Napišite proceduru koja prima naziv ime i prezime komercijaliste, umeće novog komercijalistu u tablicu Komercijalist
174--i kroz izlazni parametar vraća vrijednost primarnog kljuÄa umetnutog retka. Ako već postoji Komercijalist zadanog imena i prezimena
175--za tog komercijalista,
176--ne treba je upisivati ponovno i u tom sluÄaju kroz izlazni parametar vratite vrijednost -1. Pozovite proceduru dva puta s
177--istim imenom i prezimenom te ispišite vraćenu vrijednost.
178
179--1.2
180create proc I4zad1_2
181@Ime nvarchar(50),
182@Prezime nvarchar(50),
183@PRkljuc int output
184as
185declare @ImeK nvarchar(50)
186declare @PrezimeK nvarchar(50)
187select @ImeK=ime, @PrezimeK=Prezime from komercijalist
188if @ImeK=@Ime and @PrezimeK=@Prezime
189begin
190set @PRkljuc = -1
191end
192else
193begin insert into Komercijalist(Ime,Prezime)values( @Ime,@Prezime)
194set @PRkljuc = SCOPE_IDENTITY()
195end
196go
197declare @ispis int
198exec I4zad1_2 'Mirko','Mirkic', @ispis output
199print @ispis
200exec I4zad1_2 'Mirko','Mirkic', @ispis output
201print @ispis
202go
203
204drop proc I4zad1_2
205select*
206from komercijalist
207go
208--2 Napišite proceduru koja prima tri parametra: naziv drzave i dva naziva grada iz te drzave.
209-- Ako drzava sa zadanim imenom postoji umetnite gradove i vežite ih na tu državu.
210--Ako drzava sa zadanim nazivom ne postoji,umetnite prvo drzavu, a zatim na nju vezite gradove.
211
212--2
213CREATE PROC I4zad2
214 @NazivDrzave nvarchar(50),
215 @NazivGrada1 nvarchar(50),
216 @NazivGrada2 nvarchar(50)
217AS
218 DECLARE @IDDrzave INT
219
220 IF EXISTS(SELECT * FROM Drzava WHERE Naziv = @NazivDrzave) BEGIN
221 SELECT @IDDrzave = IDDrzava
222 FROM Drzava
223 WHERE Naziv = @NazivDrzave
224 END
225 ELSE BEGIN
226 INSERT INTO Drzava (Naziv)
227 VALUES
228 (@NazivDrzave)
229
230 SET @IDDrzave = SCOPE_IDENTITY()
231 END
232
233 INSERT INTO Grad (Naziv, DrzavaID)
234 VALUES
235 (@NazivGrada1, @IDDrzave),
236 (@NazivGrada2, @IDDrzave)
237GO
238EXEC I4zad2 'Kina','Guangzohu','Hangzohu'
239GO
240
241select*from Drzava
242select*from Grad
243go
244--2.1. Funkcija
245create function BrojRacunaKomercijalista(
246@IDkom int
247)
248returns int
249as
250begin
251
252declare @brojac int
253
254select @brojac = count(*) from Komercijalist as k inner join Racun as r
255on k.IDKomercijalist = r.KomercijalistID
256where r.KomercijalistID = @IDkom
257
258return @brojac
259end
260go
261
262select k.Ime, k.Prezime, k.StalniZaposlenik, dbo.BrojRacunaKomercijalista(k.IDKomercijalist)
263as [Broj Racuna] from Komercijalist as k
264go
265
266--3.1. Implementirajte CRUD operacije na tablici Potkategorija pomoću dvije procedure.Jedna neka obavalja
267-- operacije C i U, a druga R i D.Napravite umetanje,izmjenu,dohvaćanje i brisanje jedne potkategorije
268-- pomoću napravljenih objekata
269
270CREATE PROC Insert_Update
271 @IDPotkategorije INT, /* NULL - CREATE, OSTALO - UPDATE */
272 @NazivPotkategorije nvarchar(50),
273 @IDKategorije INT
274AS
275 IF @IDPotkategorije IS NULL BEGIN /* CREATE */
276 INSERT INTO Potkategorija (KategorijaID, Naziv)
277 VALUES
278 (@IDKategorije, @NazivPotkategorije)
279 SET @IDPotkategorije = SCOPE_IDENTITY()
280 END
281 ELSE BEGIN /* UPDATE */
282 UPDATE Potkategorija
283 SET
284 KategorijaID = @IDKategorije,
285 Naziv = @NazivPotkategorije
286 WHERE IDPotkategorija = @IDPotkategorije
287 END
288GO
289exec Insert_Update null ,'Bic',2
290
291select*from Potkategorija
292go
293
294CREATE PROC Read_Delete
295 @IDPotkategorije INT,
296 @DELETE bit /* 0 - READ, 1 - DELETE */
297AS
298 IF @DELETE = 0 BEGIN /* READ */
299 SELECT *
300 FROM Potkategorija
301 WHERE IDPotkategorija = @IDPotkategorije
302 END
303 ELSE IF @DELETE = 1 BEGIN /* DELETE */
304 DELETE
305 FROM Potkategorija
306 WHERE IDPotkategorija = @IDPotkategorije
307 END
308GO
309
310exec Read_Delete 52,1
311
312select*from Potkategorija
313
314go
315
316--3.2
317create proc crud
318 @ime nvarchar(50),
319 @prezime nvarchar(50),
320 @stalnozaposlen bit,
321 @status char(1),
322 @ime2 nvarchar(50),
323 @prezime2 nvarchar(50)
324 as
325 if @status ='i'
326 insert into Komercijalist(Ime, Prezime, StalniZaposlenik)
327 values(@ime, @prezime, @stalnozaposlen)
328else if @status = 'u'
329 update Komercijalist set Ime = @ime, Prezime = @prezime,
330 StalniZaposlenik = @stalnozaposlen where IDKomercijalist = dbo.cruder(@ime2, @prezime2)
331else if @status = 's'
332 select * from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
333else if @status = 'd'
334 delete from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
335else
336 print 'Krivo unseno slovo'
337go
338
339
340create function cruder(
341 @ime nvarchar(50),
342 @prezime nvarchar(50)
343)
344returns int
345as
346begin
347
348declare @vracaj int
349select @vracaj = IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime
350
351return @vracaj
352end
353go
354
355execute crud 'tester', 'testeric', 1, 'i', 'tester', 'testeric'
356
357select * from Komercijalist
358
359drop proc crud
360drop function cruder
361
362--CRUD OPERACIJE
363--INSERT/UPDATE zajedno, ostalo posebno.
364--Napravite procedure koje rade CRUD operacije na tablici Student tako da operacije umetanja i izmjene obavite u jednoj proceduri, a druge dvije operacije obavite u posebnim procedurama.
365--Iskoristite procedure za umetanje, izmjenu, dohvaæanje i brisanje zapisa.
366go
367CREATE PROC MergeStudent
368 @IDStudent int OUTPUT,
369 @Ime nvarchar(50),
370 @Prezime nvarchar(50),
371 @JMBAG char(11)
372AS
373IF Exists(SELECT * FROM Student WHERE IDStudent = @IDStudent)
374 UPDATE Student
375 SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
376 WHERE IDStudent = @IDStudent
377ELSE
378 INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
379 SET @IDStudent = SCOPE_IDENTITY()
380GO
381
382CREATE PROC DeleteStudent
383 @IDStudent int
384AS
385DELETE FROM Student WHERE IDStudent = @IDStudent
386GO
387
388CREATE PROC GetStudent
389 @IDStudent int
390AS
391SELECT * FROM Student WHERE IDStudent = @IDStudent
392GO
393
394EXEC GetStudent 18
395
396DECLARE @NoviIDStudenta int
397EXEC MergeStudent @NoviIDStudenta OUTPUT, 'Ana', 'Aniæ', '11224451253'
398PRINT @NoviIDStudenta
399
400EXEC GetStudent 19
401
402EXEC MergeStudent 2, 'Ana', 'Aniæ Miriæ', '11224451253'
403
404EXEC GetStudent 19
405
406EXEC DeleteStudent 19
407
408EXEC GetStudent 19
409GO
410
411select*from Student
412
413-- Zadatak 10.
414/*
415CRUD OPERACIJE
416UPDATE/INSERT/DELETE zajedno.
417Napravite procedure koje rade CRUD operacije na tablici Student tako da operacije umetanja, izmjene i brisanja obavite u jednoj proceduri, a dohvaæanje u drugoj.
418Iskoristite procedure za umetanje, izmjenu, dohvaæanje i brisanje zapisa.
419*/
420
421CREATE PROC ChangeStudent
422 @Operacija char(1),
423 @IDStudent int OUTPUT,
424 @Ime nvarchar(50),
425 @Prezime nvarchar(50),
426 @JMBAG char(11)
427AS
428IF @Operacija = 'U'
429 UPDATE Student
430 SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
431 WHERE IDStudent = @IDStudent
432ELSE IF @Operacija = 'I' BEGIN
433 INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
434 SET @IDStudent = SCOPE_IDENTITY()
435END
436ELSE IF @Operacija = 'D'
437 DELETE FROM Student WHERE IDStudent = @IDStudent
438GO
439
440CREATE PROC GetStudent
441 @IDStudent int
442AS
443SELECT * FROM Student WHERE IDStudent = @IDStudent
444GO
445
446EXEC GetStudent 21
447
448DECLARE @NoviIDStudenta int
449EXEC ChangeStudent 'I', @NoviIDStudenta OUTPUT, 'Ana', 'Aniæ', '11224451253'
450PRINT @NoviIDStudenta
451
452EXEC GetStudent 21
453
454EXEC ChangeStudent 'U', 21, 'Ana', 'Aniæ Miriæ', '11224451253'
455
456EXEC GetStudent 21
457
458EXEC ChangeStudent 'D', 21, null, null, null
459
460EXEC GetStudent 3
461GO
462
463--3.3 CRUD sa funkcijom
464CREATE PROC InsertStudent
465 @IDStudent int OUTPUT,
466 @Ime nvarchar(50),
467 @Prezime nvarchar(50),
468 @JMBAG char(11)
469AS
470INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
471SET @IDStudent = SCOPE_IDENTITY()
472GO
473
474CREATE PROC UpdateStudent
475 @IDStudent int,
476 @Ime nvarchar(50),
477 @Prezime nvarchar(50),
478 @JMBAG char(11)
479AS
480UPDATE Student
481SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
482WHERE IDStudent = @IDStudent
483GO
484
485CREATE PROC DeleteStudent
486 @IDStudent int
487AS
488DELETE FROM Student WHERE IDStudent = @IDStudent
489GO
490
491CREATE FUNCTION dbo.GetStudent
492(
493 @IDStudent int
494)
495RETURNS TABLE
496AS
497 RETURN SELECT * FROM Student WHERE IDStudent = @IDStudent
498GO
499
500SELECT * FROM GetStudent(1)
501
502DECLARE @NoviIDStudenta int
503EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT, @Ime = 'Ana', @Prezime = 'Aniæ', @JMBAG = '11224451253'
504PRINT @NoviIDStudenta
505
506SELECT * FROM GetStudent(1)
507
508EXEC UpdateStudent 1, 'Ana', 'Aniæ Miriæ', '11224451253'
509
510SELECT * FROM GetStudent(1)
511
512EXEC DeleteStudent 1
513
514SELECT * FROM GetStudent(1)
515GO
516
517
518--PRIMJERI ZA PROCEDURE
519-- Zadatak 10.
520/*
521PROCEDURE S ULAZNIM PARAMETRIMA
522Napišite proceduru koja prima dvije cijene i vraæa nazive i cijene svih proizvoda èija je cijena u zadanom rasponu.
523Pozovite proceduru na oba naèina.
524Uklonite proceduru.
525*/
526CREATE PROC p10
527 @Cijena1 money,
528 @Cijena2 money
529AS
530SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV BETWEEN @Cijena1 AND @Cijena2
531GO
532
533EXEC p10 500, 1000
534EXEC p10 @Cijena1 = 500, @Cijena2 = 1000
535EXEC p10 @Cijena2 = 1000, @Cijena1 = 500
536GO
537
538DROP PROC p10
539GO
540-----------------------------------------------------------------------------------
541
542-----------------------------------------------------------------------------------
543-- Zadatak 11.
544/*
545PROCEDURE S ULAZNIM PARAMETRIMA
546Napišite proceduru koja prima naziv države i naziv grada.
547Neka procedura umetne grad koji pripada zadanoj državi.
548Pazite na to postoji li veæ država upisana u tablicu Drzava ili ne postoji.
549Ako postoji, nemojte je umetati ponovno.
550Pozovite proceduru za državu “Japan†i grad “Osakaâ€.
551Pozovite proceduru za državu “Japan†i grad “Tokyo†i potvrdite da radi ispravno.
552Uklonite proceduru.
553*/
554CREATE PROC p11
555 @Drzava nvarchar(50),
556 @Grad nvarchar(50)
557AS
558DECLARE @IDDrzava int
559SELECT @IDDrzava = IDDrzava FROM Drzava WHERE Naziv = @Drzava
560
561IF @IDDrzava IS NOT NULL BEGIN
562 INSERT INTO Grad VALUES (@Grad, @IDDrzava)
563END
564ELSE BEGIN
565 INSERT INTO Drzava VALUES (@Drzava)
566 SET @IDDrzava = SCOPE_IDENTITY()
567 INSERT INTO Grad VALUES (@Grad, @IDDrzava)
568END
569GO
570
571EXEC p11 'Japan', 'Osaka'
572SELECT * FROM Grad
573SELECT * FROM Drzava
574
575EXEC p11 'Japan', 'Tokyo'
576SELECT * FROM Grad
577SELECT * FROM Drzava
578GO
579
580DROP PROC p11
581GO
582-----------------------------------------------------------------------------------
583
584-----------------------------------------------------------------------------------
585-- Zadatak 12.
586/*
587PROCEDURE S IZLAZNIM PARAMETRIMA
588Napišite proceduru koja prima parametre @IDProizvod i @Boja. Parametar @Boja neka bude izlazni parametar.
589Neka procedura za zadani proizvod vrati njegovu boju pomoæu izlaznog parametra.
590Pozovite proceduru i ispišite vraæenu vrijednost.
591Uklonite proceduru.
592*/
593CREATE PROC p12
594 @IDProizvod int,
595 @Boja nvarchar(15) OUTPUT
596AS
597SELECT @Boja = Boja FROM Proizvod WHERE IDProizvod = @IDProizvod
598GO
599
600DECLARE @Farba nvarchar(15)
601EXEC p12 320, @Farba OUTPUT
602PRINT @Farba
603GO
604
605DROP PROC p12
606GO
607-----------------------------------------------------------------------------------
608
609-----------------------------------------------------------------------------------
610-- Zadatak 13.
611/*
612PROCEDURE S IZLAZNIM PARAMETRIMA
613Napišite proceduru koja prima kriterij po kojemu æete filtrirati prezimena iz tablice Kupac.
614Neka procedura pomoæu izlaznog parametra vrati broj zapisa koji zadovoljavaju zadani kriterij.
615Neka procedura vrati i sve zapise koji zadovoljavaju kriterij.
616Pozovite proceduru i ispišite vraæenu vrijednost.
617Uklonite proceduru.
618*/
619CREATE PROC p13
620 @Filter nvarchar(50),
621 @BrojZapisa int OUTPUT
622AS
623SELECT @BrojZapisa = COUNT(*) FROM Kupac WHERE Prezime LIKE @Filter
624SELECT * FROM Kupac WHERE Prezime LIKE @Filter
625GO
626
627DECLARE @Cnt int
628EXEC p13 '%hu%', @Cnt OUTPUT
629PRINT @Cnt
630GO
631
632DROP PROC p13
633GO
634-----------------------------------------------------------------------------------
635
636-----------------------------------------------------------------------------------
637-- Zadatak 14.
638/*
639PROCEDURE S IZLAZNIM PARAMETRIMA
640Napišite proceduru koja za zadanog komercijalistu pomoæu izlaznih parametara vraæa njegovo ime
641i prezime te ukupnu zaraðenu kolièinu novaca.
642*/
643CREATE PROC p14
644 @KomercijalistID int,
645 @Ime nvarchar(50) OUTPUT,
646 @Prezime nvarchar(50) OUTPUT,
647 @Zaradio money OUTPUT
648AS
649SELECT @Ime = Ime, @Prezime = Prezime
650FROM Komercijalist
651WHERE IDKomercijalist = @KomercijalistID
652
653SELECT @Zaradio = SUM(s.UkupnaCijena)
654FROM Racun AS r
655INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
656WHERE r.KomercijalistID = @KomercijalistID
657GO
658
659DECLARE @Ime nvarchar(50), @Prezime nvarchar(50), @Zaradio money
660EXEC p14 276, @Ime OUTPUT, @Prezime OUTPUT, @Zaradio OUTPUT
661PRINT @Ime
662PRINT @Prezime
663PRINT @Zaradio
664GO
665
666DROP PROC p14
667GO
668-----------------------------------------------------------------------------------
669
670-----------------------------------------------------------------------------------
671-- Zadatak 15. jedno moguæe rješenje (bolje).
672/*
673DODATNI ZADACI
674Napišite proceduru koja umeæe novu kategoriju i kroz izlazni parametar vraæa generiranu IDENTITY vrijednost.
675Ako veæ postoji kategorija zadanog imena, ne treba je upisivati ponovno i u tom sluèaju kroz izlazni parametar vratite vrijednost -1.
676Pozovite proceduru i ispišite vraæenu vrijednost.
677Uklonite proceduru.
678*/
679CREATE PROC p15
680 @Kategorija nvarchar(50),
681 @ID int OUTPUT
682AS
683IF NOT EXISTS(SELECT * FROM Kategorija WHERE Naziv = @Kategorija)
684 BEGIN
685 INSERT INTO Kategorija VALUES (@Kategorija)
686 SET @ID = SCOPE_IDENTITY()
687 END
688ELSE
689 BEGIN
690 SET @ID = -1
691 END
692GO
693-----------------------------------------------------------------------------------
694
695-----------------------------------------------------------------------------------
696-- Zadatak 15. drugo moguæe rješenje (lošije).
697/*
698DODATNI ZADACI
699Napišite proceduru koja umeæe novu kategoriju i kroz izlazni parametar vraæa generiranu IDENTITY vrijednost.
700Ako veæ postoji kategorija zadanog imena, ne treba je upisivati ponovno i u tom sluèaju kroz izlazni parametar vratite vrijednost -1.
701Pozovite proceduru i ispišite vraæenu vrijednost.
702Uklonite proceduru.
703*/
704CREATE PROC p15
705 @Kategorija nvarchar(50),
706 @ID int OUTPUT
707AS
708DECLARE @Broj int
709SELECT @Broj = COUNT(*) FROM Kategorija WHERE Naziv = @Kategorija
710
711IF @Broj = 0 BEGIN
712 INSERT INTO Kategorija VALUES (@Kategorija)
713 SET @ID = SCOPE_IDENTITY()
714END
715ELSE BEGIN
716 SET @ID = -1
717END
718GO
719
720DECLARE @ID int
721EXEC p15 'Glaèala', @ID OUTPUT
722PRINT @ID
723GO
724
725DECLARE @ID int
726EXEC p15 'Glaèala', @ID OUTPUT
727PRINT @ID
728GO
729
730DROP PROC p15
731GO
732-----------------------------------------------------------------------------------
733
734-----------------------------------------------------------------------------------
735-- Zadatak 16.
736/*
737DODATNI ZADACI
738Napišite proceduru koja kroz tri izlazna parametra vraæa najmanju, najveæu i prosjeènu cijenu proizvoda iz tablice Proizvod.
739Neka procedura vrati i sve proizvode koji imaju cijenu veæu od 0 i manju od prosjeène.
740Pozovite proceduru, ispišite vraæene vrijednosti i uklonite proceduru.
741*/
742CREATE PROC p16
743 @Najmanja money OUTPUT,
744 @Najveca money OUTPUT,
745 @Prosjecna money OUTPUT
746AS
747SELECT
748 @Najmanja = MIN(CijenaBezPDV),
749 @Najveca = MAX(CijenaBezPDV),
750 @Prosjecna = AVG(CijenaBezPDV)
751FROM Proizvod
752
753SELECT * FROM Proizvod WHERE CijenaBezPDV > 0 AND CijenaBezPDV < @Prosjecna
754GO
755
756DECLARE @Najmanja money, @Najveca money, @Prosjecna money
757EXEC p16 @Najmanja OUTPUT, @Najveca OUTPUT, @Prosjecna OUTPUT
758PRINT @Najmanja
759PRINT @Najveca
760PRINT @Prosjecna
761GO
762
763DROP PROC p16
764GO
765
766-- Primjer 1.
767DECLARE @Ime nvarchar(50)
768DECLARE @Prezime nvarchar(50)
769
770SET @Ime = 'Miro'
771SET @Prezime = 'Miriæ'
772
773PRINT @Ime
774PRINT @Prezime
775PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
776
777SELECT @Ime
778SELECT @Prezime
779SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
780GO
781
782-- Primjer 2.
783DECLARE @Ime nvarchar(50)
784DECLARE @Prezime nvarchar(50)
785
786SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac WHERE IDKupac = 16
787
788SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
789GO
790
791-- Primjer 3.
792DECLARE @Ime nvarchar(50)
793DECLARE @Prezime nvarchar(50)
794
795SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac
796
797SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
798GO
799
800-- Naredba IF - ELSE IF - ELSE.
801DECLARE @Broj float
802SET @Broj = RAND()
803
804IF @Broj >= 0 AND @Broj < 0.33 BEGIN
805 SELECT 'Prva treæina'
806END
807ELSE IF @Broj >= 0.33 AND @Broj < 0.67 BEGIN
808 SELECT 'Druga treæina'
809END
810ELSE BEGIN
811 SELECT 'Treæa treæina'
812END
813GO
814
815-- Primjer 4.
816CREATE PROC dbo.DohvatiSveKupce
817AS
818SELECT * FROM Kupac
819GO
820
821EXEC dbo.DohvatiSveKupce
822GO
823
824ALTER PROCEDURE dbo.DohvatiSveKupce
825AS
826SELECT * FROM Kupac ORDER BY Ime, Prezime
827GO
828
829EXEC dbo.DohvatiSveKupce
830GO
831
832DROP PROC dbo.DohvatiSveKupce
833GO
834
835-- Primjer 5.
836CREATE PROC p5
837AS
838SELECT TOP 10 * FROM Proizvod
839SELECT TOP 5 * FROM KreditnaKartica
840SELECT TOP 3 * FROM Racun ORDER BY IDRacun DESC
841GO
842
843EXEC p5
844GO
845
846DROP PROC p5
847GO
848
849-- Primjer 6.
850CREATE PROC p6
851 @AjDiProizvoda int
852AS
853SELECT * FROM Proizvod WHERE IDProizvod = @AjDiProizvoda
854GO
855
856EXEC p6 1
857EXEC p6 @AjDiProizvoda = 1
858GO
859
860DROP PROC p6
861GO
862
863-- Primjer 7.
864CREATE PROC p7
865 @Cijena1 money,
866 @Cijena2 money
867AS
868SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV BETWEEN @Cijena1 AND @Cijena2
869GO
870
871EXEC p7 500, 1000
872EXEC p7 @Cijena1 = 500, @Cijena2 = 1000
873EXEC p7 @Cijena2 = 1000, @Cijena1 = 500 -- Parametri nisu po redu.
874GO
875
876DROP PROC p7
877GO
878
879-- Primjer 8.
880CREATE PROC p8
881 @Tip nvarchar(50),
882 @Broj nvarchar(25),
883 @IstekMjesec tinyint,
884 @IstekGodina smallint
885AS
886-- Dohvaæanje prije.
887SELECT COUNT(*) AS BrojPrije FROM dbo.KreditnaKartica
888
889-- Umetanje.
890INSERT INTO dbo.KreditnaKartica (Tip, Broj, IstekMjesec, IstekGodina)
891VALUES (@Tip, @Broj, @IstekMjesec, @IstekGodina)
892
893-- Dohvaæanje nakon.
894SELECT COUNT(*) AS BrojNakon FROM dbo.KreditnaKartica
895GO
896
897EXEC p8 'American Express', '12345678901234', 11, 2013
898EXEC p8 @Tip = 'Visa', @Broj = '99999999990000', @IstekMjesec = 5, @IstekGodina = 2014
899GO
900
901DROP PROC p8
902GO
903
904-- Primjer 9.
905CREATE PROC p9
906 @Boja1 nvarchar(15),
907 @Boja2 nvarchar(15),
908 @Boja3 nvarchar(15)
909AS
910SELECT * FROM Proizvod WHERE Boja = @Boja1
911SELECT * FROM Proizvod WHERE Boja = @Boja2
912SELECT * FROM Proizvod WHERE Boja = @Boja3
913GO
914
915EXEC p9 'Red', 'Žuta', 'Black'
916GO
917
918DROP PROC p9
919GO
920
921-- Primjer 10.
922CREATE PROC p10
923 @IDProizvod int,
924 @Boja nvarchar(15) OUTPUT
925AS
926SELECT @Boja = Boja FROM Proizvod WHERE IDProizvod = @IDProizvod
927GO
928
929DECLARE @Farba nvarchar(15)
930EXEC p10 320, @Farba OUTPUT
931PRINT @Farba
932GO
933
934DECLARE @Farba nvarchar(15)
935EXEC p10 @IDProizvod = 320, @Boja = @Farba OUTPUT
936PRINT @Farba
937GO
938
939DROP PROC p10
940GO
941
942-- Primjer 11.
943CREATE PROC p11
944 @Filter nvarchar(50),
945 @BrojZapisa int OUTPUT
946AS
947SELECT @BrojZapisa = COUNT(*) FROM Kupac WHERE Prezime LIKE @Filter
948
949SELECT * FROM Kupac WHERE Prezime LIKE @Filter
950GO
951
952DECLARE @Cnt int
953EXEC p11 '%abe%', @Cnt OUTPUT
954PRINT @Cnt
955GO
956
957DROP PROC p11
958GO
959
960-- Primjer 12.
961CREATE PROC p12
962 @KomercijalistID int,
963 @Ime nvarchar(50) OUTPUT,
964 @Prezime nvarchar(50) OUTPUT,
965 @IzdaoRacuna int OUTPUT
966AS
967SELECT
968 @Ime = k.Ime,
969 @Prezime = k.Prezime,
970 @IzdaoRacuna = COUNT(*)
971FROM Komercijalist AS k
972INNER JOIN Racun AS r ON k.IDKomercijalist = r.KomercijalistID
973WHERE k.IDKomercijalist = @KomercijalistID
974GROUP BY k.Ime, k.Prezime
975GO
976
977DECLARE @Ime nvarchar(50), @Prezime nvarchar(50), @IzdaoRacuna int
978EXEC p12 276, @Ime OUTPUT, @Prezime OUTPUT, @IzdaoRacuna OUTPUT
979SELECT @Ime
980SELECT @Prezime
981SELECT @IzdaoRacuna
982GO
983
984DROP PROC p12
985GO
986
987-- Primjer 13.
988CREATE PROC p13
989 @Ime nvarchar(50),
990 @Prezime nvarchar(50)
991AS
992IF EXISTS(SELECT * FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime)
993 RETURN 0
994ELSE
995 RETURN 200
996GO
997
998DECLARE @RetVal int
999EXEC @RetVal = p13 'Amy', 'Alberts'
1000PRINT @RetVal
1001GO
1002
1003DECLARE @RetVal int
1004EXEC @RetVal = p13 'Paško', 'Patak'
1005PRINT @RetVal
1006GO
1007
1008-- Primjer 14.
1009ALTER PROC p13
1010 @Ime nvarchar(50),
1011 @Prezime nvarchar(50)
1012WITH ENCRYPTION
1013AS
1014IF EXISTS(SELECT * FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime)
1015 RETURN 0
1016ELSE
1017 RETURN 200
1018GO
1019
1020--PRIMJERI FUNKCIJA
1021-- 1.
1022SELECT
1023 IDRacun,
1024 CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja,
1025 BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
1026FROM Racun AS r
1027WHERE YEAR(DatumIzdavanja) = 2003
1028
1029-- 2.
1030SELECT
1031 Naziv + ' (' + CAST(IDProizvod AS nvarchar(20)) + ')' AS Naziv
1032FROM Proizvod
1033
1034-- ili
1035
1036SELECT
1037 Naziv + ' (' + CONVERT(nvarchar(50), IDProizvod) + ')' AS Naziv
1038FROM Proizvod
1039
1040-- 3.
1041SELECT
1042 *,
1043 CASE
1044 WHEN CijenaBezPDV < 100 THEN 'Jeftino'
1045 WHEN CijenaBezPDV BETWEEN 100 AND 1000 THEN 'Srednje skupo'
1046 ELSE 'Skupo'
1047 END AS CijenaKomentar
1048FROM Proizvod
1049
1050-- 4.
1051SELECT
1052 Naziv,
1053 CASE
1054 WHEN Boja IS NULL THEN 'Nedefinirana'
1055 ELSE Boja
1056 END AS Boja
1057FROM Proizvod
1058
1059-- ili
1060
1061SELECT
1062 Naziv,
1063 ISNULL(Boja, 'Nedefinirana') AS Boja
1064FROM Proizvod
1065
1066-- 5.
1067DECLARE @a int = 51
1068DECLARE @b int = 0
1069
1070BEGIN TRY
1071 PRINT @a / @b
1072END TRY
1073BEGIN CATCH
1074 PRINT ERROR_MESSAGE()
1075 PRINT ERROR_NUMBER()
1076 PRINT ERROR_SEVERITY()
1077 PRINT ERROR_LINE()
1078 PRINT ERROR_PROCEDURE()
1079END CATCH
1080GO
1081
1082-- 6.
1083CREATE TABLE Vrsta
1084(
1085 IDVrsta int CONSTRAINT PK_Vrsta PRIMARY KEY,
1086 Naziv nvarchar(50)
1087)
1088GO
1089
1090CREATE PROC InsertVrsta
1091 @IDVrsta int,
1092 @Naziv nvarchar(50)
1093AS
1094INSERT INTO Vrsta (IDVrsta, Naziv) VALUES (@IDVrsta, @Naziv)
1095GO
1096
1097EXEC InsertVrsta 1, 'Pingvin'
1098EXEC InsertVrsta 1, 'Pingvin'
1099GO
1100
1101BEGIN TRY
1102 EXEC InsertVrsta 2, 'Slon'
1103 EXEC InsertVrsta 2, 'Slon'
1104END TRY
1105BEGIN CATCH
1106 PRINT ERROR_MESSAGE()
1107 PRINT ERROR_NUMBER()
1108 PRINT ERROR_SEVERITY()
1109 PRINT ERROR_LINE()
1110 PRINT ERROR_PROCEDURE()
1111END CATCH
1112GO
1113
1114ALTER PROC InsertVrsta
1115 @IDVrsta int,
1116 @Naziv nvarchar(50)
1117AS
1118BEGIN TRY
1119 INSERT INTO Vrsta (IDVrsta, Naziv) VALUES (@IDVrsta, @Naziv)
1120END TRY
1121BEGIN CATCH
1122 PRINT 'Desila se greška: ' + ERROR_MESSAGE()
1123 PRINT 'Vrsta nije upisana.'
1124END CATCH
1125GO
1126
1127EXEC InsertVrsta 3, 'Ovca'
1128EXEC InsertVrsta 3, 'Ovca'
1129GO
1130
1131-- 7.
1132CREATE FUNCTION UkupnaKolicina
1133(
1134 @IDProizvod int
1135)
1136RETURNS int
1137AS
1138BEGIN
1139 DECLARE @Ukupno int
1140
1141 SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @IDProizvod
1142
1143 RETURN @Ukupno
1144END
1145GO
1146
1147DECLARE @Rez int
1148SET @Rez = dbo.UkupnaKolicina(776)
1149SELECT @Rez
1150
1151SELECT
1152 Naziv,
1153 Boja,
1154 dbo.UkupnaKolicina(IDProizvod) AS Prodano
1155FROM Proizvod
1156GO
1157
1158ALTER FUNCTION UkupnaKolicina
1159(
1160 @IDProizvod int
1161)
1162RETURNS int
1163AS
1164BEGIN
1165 DECLARE @Ukupno int
1166
1167 SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @IDProizvod
1168
1169 RETURN
1170 CASE
1171 WHEN @Ukupno IS NOT NULL THEN @Ukupno
1172 ELSE 0
1173 END
1174END
1175GO
1176
1177SELECT
1178 Naziv,
1179 Boja,
1180 dbo.UkupnaKolicina(IDProizvod) AS Prodano
1181FROM Proizvod
1182GO
1183
1184CREATE INDEX ix1 ON Stavka (ProizvodID) INCLUDE (Kolicina)
1185
1186SELECT
1187 Naziv,
1188 Boja,
1189 dbo.UkupnaKolicina(IDProizvod) AS Prodano
1190FROM Proizvod
1191GO
1192
1193-- 8.
1194CREATE FUNCTION Skrati
1195(
1196 @s nvarchar(max)
1197)
1198RETURNS nvarchar(10)
1199AS
1200BEGIN
1201 RETURN CASE
1202 WHEN LEN(@s) <= 10 THEN @s
1203 ELSE SUBSTRING(@s, 1, 7) + '...'
1204 END
1205END
1206GO
1207
1208SELECT dbo.Skrati('Zvonko')
1209SELECT dbo.Skrati('Zvonko Telefonko')
1210
1211SELECT
1212 Naziv,
1213 dbo.Skrati(Naziv) AS NazivSkraceni
1214FROM Proizvod
1215GO
1216
1217-- 9.
1218CREATE FUNCTION GetNajnoviji
1219(
1220 @IDKupac int
1221)
1222RETURNS datetime
1223AS
1224BEGIN
1225 DECLARE @Datum datetime
1226
1227 SELECT TOP 1 @Datum = DatumIzdavanja FROM Racun
1228 WHERE KupacID = @IDKupac
1229 ORDER BY DatumIzdavanja DESC
1230
1231 RETURN @Datum
1232END
1233GO
1234
1235SELECT
1236 IDKupac,
1237 dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
1238FROM Kupac
1239GO
1240
1241CREATE NONCLUSTERED INDEX ix2 ON Racun(KupacID) INCLUDE (DatumIzdavanja)
1242GO
1243
1244SELECT
1245 IDKupac,
1246 dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
1247FROM Kupac
1248GO
1249
1250-- 10.
1251CREATE FUNCTION DohvatiOsobe
1252(
1253 @PrezimeLike nvarchar(50)
1254)
1255RETURNS TABLE
1256AS
1257RETURN
1258 SELECT IDKupac, Ime, Prezime
1259 FROM Kupac
1260 WHERE Prezime LIKE @PrezimeLike + '%'
1261GO
1262
1263SELECT * FROM DohvatiOsobe('Ac')
1264GO
1265
1266SELECT *
1267FROM DohvatiOsobe('Ac') AS o
1268INNER JOIN Racun AS r ON o.IDKupac = r.KupacID
1269GO
1270
1271-- 11.
1272CREATE FUNCTION DohvatiRacuneIzmedju
1273(
1274 @D1 datetime,
1275 @D2 datetime
1276)
1277RETURNS TABLE
1278AS
1279RETURN
1280 SELECT *
1281 FROM Racun AS r
1282 WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
1283GO
1284
1285SELECT * FROM DohvatiRacuneIzmedju('20021111', '20030315')
1286GO
1287
1288ALTER FUNCTION DohvatiRacuneIzmedju
1289(
1290 @D1 datetime,
1291 @D2 datetime
1292)
1293RETURNS TABLE
1294AS
1295RETURN
1296 SELECT
1297 IDRacun,
1298 CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja,
1299 BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
1300 FROM Racun AS r
1301 WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
1302GO
1303
1304SELECT * FROM DohvatiRacuneIzmedju('20021111', '20030315')
1305GO
1306
1307-- 12.
1308CREATE FUNCTION DohvatiRacuneIzmedjuSlozena
1309(
1310 @D1 datetime,
1311 @D2 datetime
1312)
1313RETURNS @rez TABLE
1314(
1315 IDRacun int,
1316 DatumIzdavanja datetime,
1317 BrojRacuna nvarchar(25),
1318 KupacID int,
1319 KomercijalistID int,
1320 KreditnaKarticaID int,
1321 Komentar nvarchar(128)
1322)
1323AS
1324BEGIN
1325
1326 INSERT INTO @rez (IDRacun, DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar)
1327 SELECT IDRacun, DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
1328 FROM Racun AS r
1329 WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
1330
1331 RETURN
1332END
1333GO
1334
1335SELECT * FROM DohvatiRacuneIzmedjuSlozena('20021111', '20030315')
1336GO
1337
1338-- 13.
1339CREATE FUNCTION Slozena13
1340(
1341 @Cijena money
1342)
1343RETURNS @rez TABLE ( Naziv nvarchar(50), Cijena money )
1344AS
1345BEGIN
1346 IF @Cijena IS NULL BEGIN
1347 INSERT INTO @rez (Naziv, Cijena)
1348 SELECT Naziv, CijenaBezPDV FROM Proizvod
1349 END
1350 ELSE BEGIN
1351 INSERT INTO @rez (Naziv, Cijena)
1352 SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV > @Cijena
1353 END
1354
1355 RETURN
1356END
1357GO
1358
1359SELECT * FROM Slozena13(NULL)
1360SELECT * FROM Slozena13(2300)
1361GO
1362
1363-- 14.
1364CREATE TABLE Student
1365(
1366 IDStudent int CONSTRAINT PK_Student PRIMARY KEY IDENTITY,
1367 Ime nvarchar(50),
1368 Prezime nvarchar(50),
1369 JMBAG char(11)
1370)
1371GO
1372
1373CREATE PROC InsertStudent
1374 @IDStudent int OUTPUT,
1375 @Ime nvarchar(50),
1376 @Prezime nvarchar(50),
1377 @JMBAG char(11)
1378AS
1379INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
1380SET @IDStudent = SCOPE_IDENTITY()
1381GO
1382
1383CREATE PROC UpdateStudent
1384 @IDStudent int,
1385 @Ime nvarchar(50),
1386 @Prezime nvarchar(50),
1387 @JMBAG char(11)
1388AS
1389UPDATE Student
1390SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
1391WHERE IDStudent = @IDStudent
1392GO
1393
1394CREATE PROC DeleteStudent
1395 @IDStudent int
1396AS
1397DELETE FROM Student WHERE IDStudent = @IDStudent
1398GO
1399
1400CREATE FUNCTION dbo.GetStudent
1401(
1402 @IDStudent int
1403)
1404RETURNS TABLE
1405AS
1406 RETURN SELECT * FROM Student WHERE IDStudent = @IDStudent
1407GO
1408
1409SELECT * FROM GetStudent(1)
1410
1411DECLARE @NoviIDStudenta int
1412EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT, @Ime = 'Ana', @Prezime = 'Aniæ', @JMBAG = '11224451253'
1413PRINT @NoviIDStudenta
1414
1415SELECT * FROM GetStudent(1)
1416
1417EXEC UpdateStudent 1, 'Ana', 'Aniæ Miriæ', '11224451253'
1418
1419SELECT * FROM GetStudent(1)
1420
1421EXEC DeleteStudent 1
1422
1423SELECT * FROM GetStudent(1)
1424GO
1425
1426-----------------------------------------------------------------------------------
1427/*
1428Vježbe se rade na bazi AdventureWorksOBP. Ako je nemate na raèunalu, attachirajte je (nalazi se na InfoEduci).
1429*/
1430USE AdventureWorksOBP
1431GO
1432-----------------------------------------------------------------------------------
1433
1434-----------------------------------------------------------------------------------
1435-- Zadatak 1.
1436/*
1437PRETVARANJE TIPOVA PODATAKA
1438Dohvatite brojeve raèuna i datume izdavanja za kupca s ID-em 378.
1439Datume izdavanja formatirajte na hrvatski naèin.
1440*/
1441SELECT
1442 BrojRacuna,
1443 CONVERT(char(10), DatumIzdavanja, 104) AS DatumIzdavanja
1444FROM Racun WHERE KupacID = 378
1445GO
1446-----------------------------------------------------------------------------------
1447
1448-----------------------------------------------------------------------------------
1449-- Zadatak 2.
1450/*
1451PRETVARANJE TIPOVA PODATAKA
1452Napišite proceduru za umetanje zapisa u tablicu Drzava.
1453Neka procedura kroz izlazni parametar vrati vrijednost primarnog kljuèa novog zapisa.
1454Pozovite proceduru i ispiÅ¡ite vraæenu vrijednost u formatu: “Umetnuta je država s ID-em nâ€, gdje je n vrijednost primarnog kljuèa.
1455*/
1456CREATE PROC p1
1457 @Naziv nvarchar(50),
1458 @ID int OUTPUT
1459AS
1460INSERT INTO Drzava (Naziv) VALUES (@Naziv)
1461SET @ID = SCOPE_IDENTITY()
1462GO
1463
1464DECLARE @n int
1465EXEC p1 'Gruzija', @n OUTPUT
1466PRINT 'Umetnuta je država s ID-em ' + CAST(@n AS nvarchar(10))
1467GO
1468-----------------------------------------------------------------------------------
1469
1470-----------------------------------------------------------------------------------
1471-- Zadatak 3.
1472/*
1473PRETVARANJE TIPOVA PODATAKA
1474Dohvatite nazive svih proizvoda i uz svaki naziv u zagradi ispiÅ¡ite i njegov ID te cijenu, npr. “HL Road Rear Wheel (ID = 828, Cijena = 357.06)â€
1475*/
1476SELECT
1477 Naziv + ' (ID = ' + CAST(IDProizvod AS nvarchar(50)) + ', Cijena = ' + CAST(CijenaBezPDV AS nvarchar(50)) + ')' AS Naziv
1478FROM dbo.Proizvod
1479-----------------------------------------------------------------------------------
1480
1481-----------------------------------------------------------------------------------
1482-- Zadatak 4.
1483/*
1484NAREDBA CASE I WHILE
1485Dohvatite nazive svih proizvoda i uz svaki naziv ispišite i naziv potkategorije.
1486Ako neke potkategorije nema, napiÅ¡ite “Nije definiranaâ€.
1487*/
1488SELECT
1489 p.Naziv,
1490 CASE
1491 WHEN pk.Naziv IS NULL THEN 'Nije definirana'
1492 ELSE pk.Naziv
1493 END AS NazivPotkategorije
1494FROM Proizvod AS p
1495LEFT JOIN Potkategorija AS pk ON p.PotkategorijaID = pk.IDPotkategorija
1496-----------------------------------------------------------------------------------
1497
1498-----------------------------------------------------------------------------------
1499-- Zadatak 5.
1500/*
1501NAREDBA CASE I WHILE
1502Dohvatite naziv i cijene svih proizvoda.
1503Za cijene koje su ispod 1000, napiÅ¡ite “Jeftinoâ€, izmeðu 1000 i 2000 napiÅ¡ite “Prihvatljivoâ€, za sve ostale napiÅ¡ite “Skupoâ€.
1504*/
1505SELECT
1506 Naziv,
1507 CASE
1508 WHEN CijenaBezPDV < 1000 THEN 'Jeftino'
1509 WHEN CijenaBezPDV BETWEEN 1000 AND 2000 THEN 'Prihvatljivo'
1510 ELSE 'Skupo'
1511 END AS Procjena
1512FROM Proizvod
1513GO
1514-----------------------------------------------------------------------------------
1515
1516-----------------------------------------------------------------------------------
1517-- Zadatak 6.
1518/*
1519NAREDBA CASE I WHILE
1520Napravite tablicu Proba sa stupcima IDProba (primarni kljuè i IDENTITY) i Vrijednost (int).
1521U stupac Vrijednost unesite vrijednosti izmeðu 10.000.000 i 10.015.000.
1522*/
1523CREATE TABLE Proba ( IDProba int PRIMARY KEY IDENTITY, Vrijednost int )
1524GO
1525
1526DECLARE @i int = 10000000
1527WHILE @i <= 10015000 BEGIN
1528 INSERT INTO Proba VALUES (@i)
1529 SET @i += 1
1530END
1531
1532SELECT * FROM Proba
1533GO
1534-----------------------------------------------------------------------------------
1535
1536-----------------------------------------------------------------------------------
1537-- Zadatak 7.
1538/*
1539STRUKTURIRANO HVATANJE POGREÅ AKA
1540Napravite proceduru koja prima dva broja i kroz izlazni parametar vraæa prvi broj podijeljen drugim.
1541Ako se desi greška, neka procedura u izlazni parametar upiše 0 i neka ispiše tekst greške.
1542Pozovite proceduru i ispišite rezultat dijeljenja.
1543*/
1544CREATE PROC p2
1545 @a int,
1546 @b int,
1547 @c int OUTPUT
1548AS
1549BEGIN TRY
1550 SET @c = @a / @b
1551END TRY
1552BEGIN CATCH
1553 SET @c = 0
1554 PRINT ERROR_MESSAGE()
1555END CATCH
1556GO
1557
1558DECLARE @Rez int
1559EXEC p2 32, 4, @Rez OUTPUT
1560PRINT @Rez
1561
1562EXEC p2 32, 0, @Rez OUTPUT
1563PRINT @Rez
1564GO
1565-----------------------------------------------------------------------------------
1566
1567-----------------------------------------------------------------------------------
1568-- Zadatak 8.
1569/*
1570STRUKTURIRANO HVATANJE POGREÅ AKA
1571Napravite tablicu Zivotinja koja ima stupce IDZivotinja (primarni kljuè, ali nije IDENTITY) i Naziv.
1572Napravite proceduru koja prima IDZivotinja i Naziv i umeæe ih u tablicu.
1573Pozovite proceduru dva puta s vrijednostima 20 i "Èaplja".
1574Implementirajte TRY/CATCH izvan procedure i pozovite je.
1575Implementirajte TRY/CATCH unutar procedure i pozovite je.
1576*/
1577CREATE TABLE Zivotinja
1578(
1579 IDZivotinja int PRIMARY KEY,
1580 Naziv nvarchar(50)
1581)
1582GO
1583
1584CREATE PROC InsertZivotinja
1585 @IDZivotinja int,
1586 @Naziv nvarchar(50)
1587AS
1588INSERT INTO Zivotinja (IDZivotinja, Naziv) VALUES (@IDZivotinja, @Naziv)
1589GO
1590
1591EXEC InsertZivotinja 20, 'Èaplja'
1592EXEC InsertZivotinja 20, 'Èaplja'
1593GO
1594
1595BEGIN TRY
1596 EXEC InsertZivotinja 20, 'Èaplja'
1597 EXEC InsertZivotinja 20, 'Èaplja'
1598END TRY
1599BEGIN CATCH
1600 PRINT ERROR_MESSAGE()
1601 PRINT ERROR_NUMBER()
1602 PRINT ERROR_SEVERITY()
1603 PRINT ERROR_LINE()
1604 PRINT ERROR_PROCEDURE()
1605END CATCH
1606GO
1607
1608ALTER PROC InsertZivotinja
1609 @IDZivotinja int,
1610 @Naziv nvarchar(50)
1611AS
1612BEGIN TRY
1613 INSERT INTO Zivotinja (IDZivotinja, Naziv) VALUES (@IDZivotinja, @Naziv)
1614END TRY
1615BEGIN CATCH
1616 PRINT 'Desila se greška: ' + ERROR_MESSAGE()
1617 PRINT 'Životinja nije upisana.'
1618END CATCH
1619GO
1620
1621EXEC InsertZivotinja 20, 'Èaplja'
1622GO
1623-----------------------------------------------------------------------------------
1624
1625-----------------------------------------------------------------------------------
1626-- Zadatak 9.
1627/*
1628SKALARNE FUNKCIJE
1629Napišite funkciju koja prima ID proizvoda i dohvaæa broj prodanih primjeraka.
1630Pozovite funkciju samostalno.
1631Dohvatite nazive i boje svih proizvoda i uz svaki proizvoda ispišite koliko primjeraka je prodano.
1632Promijenite funkciju tako da vrati 0 za one proizvode koji nisu prodani niti u jednom primjerku.
1633Uklonite funkciju.
1634*/
1635CREATE FUNCTION UkupnaKolicina
1636(
1637 @ID int
1638)
1639RETURNS int
1640AS
1641BEGIN
1642 DECLARE @Ukupno int
1643
1644 SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID
1645
1646 RETURN @Ukupno
1647END
1648GO
1649
1650DECLARE @Rez int
1651SET @Rez = dbo.UkupnaKolicina(776)
1652PRINT @Rez
1653
1654SELECT
1655 Naziv,
1656 Boja,
1657 dbo.UkupnaKolicina(IDProizvod) AS Prodano
1658FROM Proizvod
1659GO
1660
1661ALTER FUNCTION UkupnaKolicina
1662(
1663 @ID int
1664)
1665RETURNS int
1666AS
1667BEGIN
1668 DECLARE @Ukupno int
1669
1670 SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID
1671
1672 RETURN CASE
1673 WHEN @Ukupno IS NOT NULL THEN @Ukupno
1674 ELSE 0
1675 END
1676END
1677GO
1678
1679SELECT
1680 Naziv,
1681 Boja,
1682 dbo.UkupnaKolicina(IDProizvod) AS Prodano
1683FROM Proizvod
1684GO
1685-----------------------------------------------------------------------------------
1686
1687-----------------------------------------------------------------------------------
1688-- Zadatak 10.
1689/*
1690SKALARNE FUNKCIJE
1691Napišite funkciju koja prima string.
1692Ako je broj znakova u stringu manji od 10, neka funkcija vrati ulazni string.
1693Ako ne, neka vrati prvih 7 znakova i iza toga tri toèke.
1694Ispišite nazive svih proizvoda koristeæi napravljenu funkciju.
1695*/
1696CREATE FUNCTION Skrati
1697(
1698 @s nvarchar(max)
1699)
1700RETURNS nvarchar(10)
1701AS
1702BEGIN
1703 RETURN CASE
1704 WHEN LEN(@s) <= 10 THEN @s
1705 ELSE SUBSTRING(@s, 1, 7) + '...'
1706 END
1707END
1708GO
1709
1710PRINT dbo.Skrati('Zvonko')
1711PRINT dbo.Skrati('Zvonko Telefonko')
1712
1713SELECT
1714 Naziv,
1715 dbo.Skrati(Naziv) AS NazivSkraceni
1716FROM Proizvod
1717GO
1718-----------------------------------------------------------------------------------
1719
1720-----------------------------------------------------------------------------------
1721-- Zadatak 11.
1722/*
1723SKALARNE FUNKCIJE
1724Napišite funkciju koja za zadanog kupca vraæa datum najnovije kupovine.
1725Ispišite sve kupce i kraj svakog ispišite datum najnovije kupovine.
1726Ako treba, optimizirajte!
1727*/
1728CREATE FUNCTION GetNajnoviji
1729(
1730 @IDKupac int
1731)
1732RETURNS datetime
1733AS
1734BEGIN
1735 DECLARE @Datum datetime
1736
1737 SELECT TOP 1 @Datum = DatumIzdavanja FROM dbo.Racun
1738 WHERE KupacID = @IDKupac
1739 ORDER BY DatumIzdavanja DESC
1740
1741 RETURN @Datum
1742END
1743GO
1744
1745SELECT
1746 *,
1747 dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
1748FROM Kupac
1749
1750CREATE NONCLUSTERED INDEX i1 ON dbo.Racun(KupacID) INCLUDE (DatumIzdavanja)
1751GO
1752-------------------------
1753Ono sto je meni bilo na testu
1754Create function dbo.BrojKupaca(
1755@IDdr int
1756)
1757returns int
1758as
1759begin
1760
1761declare @brojac int
1762
1763select @brojac = count(*) from kupac as k inner join grad as g
1764on g.IDGrad = k.GradID
1765inner join Drzava as d
1766on d.IDDrzava=g.DrzavaID
1767where d.IDDrzava = @IDdr
1768return @brojac
1769end
1770go
1771
1772select d.Naziv,dbo.BrojKupaca(d.IDDrzava)
1773as [Broj Kupaca] from Drzava as d
1774go
1775
1776--Zadatak3.
1777
1778Create proc InsertKredintaKartica
1779 @IDkk int output,
1780 @tip nvarchar(50),
1781 @broj nvarchar(25),
1782 @IstekMjesec tinyint,
1783 @IstekGodina smallint
1784as
1785insert into KreditnaKartica(tip, broj, IstekMjesec,IstekGodina) values (@tip, @broj, @IstekMjesec,@IstekGodina)
1786set @IDkk = SCOPE_IDENTITY()
1787go
1788
1789Create proc UpdateKreditnaKartica
1790@IDkk int output,
1791 @tip nvarchar(50),
1792 @broj nvarchar(25),
1793 @IstekMjesec tinyint,
1794 @IstekGodina smallint
1795as
1796update KreditnaKartica
1797set Tip = @tip, Broj = @broj, IstekMjesec=@IstekMjesec , IstekGodina=@IstekGodina
1798where IDKreditnaKartica = @IDkk
1799go
1800
1801Create proc DeleteKreditnaKartica
1802 @IDkk int
1803as
1804delete from KreditnaKartica where IDKreditnaKartica = @IDkk
1805go
1806
1807Create function dbo.GetKreditnaKartica
1808(
1809 @IDkk int
1810)
1811returns table
1812as
1813 return select * from KreditnaKartica where IDKreditnaKartica = @IDkk
1814go
1815
1816select * from GetKreditnaKartica(19238)
1817
1818declare @NoviIDkk int
1819exec InsertKredintaKartica @IDkk = @NoviIDkk Output, @Tip = 'MasterCard', @Broj = '12332664695310', @IstekMjesec=11, @IstekGodina=2007
1820
1821select * from GetKreditnaKartica(19238)
1822
1823exec UpdateKreditnaKartica 19238, 'Visa', '12332664695310', 12, 2008
1824
1825select * from GetKreditnaKartica(19238)
1826
1827exec DeleteKreditnaKartica 19238
1828
1829select * from GetKreditnaKartica (19238)
1830go
1831
1832select*
1833from KreditnaKartica
1834
1835---------
1836
1837
1838
1839--ishod 4
1840--grad
1841create proc zad1
1842@naziv nvarchar(50),
1843@iddrz int,
1844@kljuc int output
1845as
1846declare @nazivdr nvarchar(50)
1847declare @drid int
1848select @nazivdr = Naziv, @drid = DrzavaID from grad
1849
1850if @nazivdr = @naziv and @iddrz = @drid
1851 begin
1852 set @kljuc = -1
1853 print 'Grad je vec kreiran'
1854 end
1855else
1856 begin
1857 insert into Grad(Naziv, DrzavaID) values(@naziv, @iddrz)
1858 set @kljuc = SCOPE_IDENTITY()
1859 end
1860go
1861
1862declare @ispis nvarchar(50)
1863exec zad1 'Tokyo', 3, @ispis output
1864print @ispis
1865go
1866
1867drop proc zad1
1868
1869--provjera
1870select * from Grad
1871delete from Grad where IDGrad = 15
1872
1873
1874
1875
1876--komercijalist
1877 create proc komercijalistpr
1878@ime nvarchar(50),
1879@prezime nvarchar(50),
1880@kljuc int output
1881as
1882declare @imekr nvarchar(50)
1883declare @prezimekr nvarchar(50)
1884select @imekr = Ime, @prezimekr = Prezime from Komercijalist
1885
1886if @imekr = @ime and @prezime = @prezimekr
1887 begin
1888 set @kljuc = -1
1889 print 'Postoji već osoba sa tim imenom i prezimenom'
1890 end
1891else
1892 begin
1893 insert into Komercijalist(Ime, Prezime) values(@ime, @prezime)
1894 set @kljuc = SCOPE_IDENTITY()
1895 end
1896go
1897
1898declare @ispis nvarchar(50)
1899exec komercijalistpr 'Denis', 'Horvat', @ispis output
1900print @ispis
1901go
1902
1903drop proc komercijalistpr
1904
1905--provjera
1906select * from Komercijalist
1907delete from komercijalist where ime='denis' and Prezime='Horvat'
1908
1909
1910
1911--crud
1912 create proc crud1
1913 @ime nvarchar(50),
1914 @prezime nvarchar(50),
1915 @stalnozaposlen bit,
1916 @status char(1),
1917 @ime2 nvarchar(50),
1918 @prezime2 nvarchar(50)
1919 as
1920 if @status ='i'
1921 insert into Komercijalist(Ime, Prezime, StalniZaposlenik)
1922 values(@ime, @prezime, @staln
1923else if @status = 'u'
1924 update Komercijalist set Ime = @ime, Prezime = @prezime,
1925 StalniZaposlenik = @stalnozaposlen where IDKomercijalist = dbo.cruder1(@ime2, @prezime2)
1926else if @status = 's'
1927 select * from Komercijalist where IDKomercijalist = dbo.cruder1(@ime, @prezime)
1928else if @status = 'd'
1929 delete from Komercijalist where IDKomercijalist = dbo.cruder1(@ime, @prezime)
1930else
1931 print 'Krivo unseno slovo'
1932go
1933
1934
1935create function cruder1(
1936 @ime nvarchar(50),
1937 @prezime nvarchar(50)
1938)
1939returns int
1940as
1941begin
1942
1943declare @vracaj int
1944select @vracaj = IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime
1945
1946return @vracaj
1947end
1948go
1949
1950execute crud1 'tester', 'testeric', 1, 'u', 'tester', 'testeric'
1951
1952select * from Komercijalist
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962--4 ishod
1963--1
1964
1965create proc zad1
1966@naziv nvarchar(50),
1967@iddrz int,
1968@kljuc int output
1969as
1970declare @nazivdr nvarchar(50)
1971declare @drid int
1972select @nazivdr = Naziv, @drid = DrzavaID from grad
1973
1974if @nazivdr = @naziv and @iddrz = @drid
1975 begin
1976 set @kljuc = -1
1977 print 'Grad je vec kreiran'
1978 end
1979else
1980 begin
1981 insert into Grad(Naziv, DrzavaID) values(@naziv, @iddrz)
1982 set @kljuc = SCOPE_IDENTITY()
1983 end
1984go
1985
1986declare @ispis nvarchar(50)
1987exec zad1 'Tokyo', 3, @ispis output
1988print @ispis
1989go
1990
1991drop proc zad1
1992
1993--provjera
1994select * from Grad
1995delete from Grad where IDGrad = 15
1996
1997
1998--2
1999
2000create function BrojRacunaKomercijalista(
2001@IDkom int
2002)
2003returns int
2004as
2005begin
2006
2007declare @brojac int
2008
2009select @brojac = count(*) from Komercijalist as k inner join Racun as r
2010on k.IDKomercijalist = r.KomercijalistID
2011where r.KomercijalistID = @IDkom
2012
2013return @brojac
2014end
2015go
2016
2017select k.Ime, k.Prezime, k.StalniZaposlenik, dbo.BrojRacunaKomercijalista(k.IDKomercijalist)
2018as [Broj Racuna] from Komercijalist as k
2019
2020
2021--3
2022create proc crud
2023 @ime nvarchar(50),
2024 @prezime nvarchar(50),
2025 @stalnozaposlen bit,
2026 @status char(1),
2027 @ime2 nvarchar(50),
2028 @prezime2 nvarchar(50)
2029 as
2030 if @status ='i'
2031 insert into Komercijalist(Ime, Prezime, StalniZaposlenik)
2032 values(@ime, @prezime, @stalnozaposlen)
2033else if @status = 'u'
2034 update Komercijalist set Ime = @ime, Prezime = @prezime,
2035 StalniZaposlenik = @stalnozaposlen where IDKomercijalist = dbo.cruder(@ime2, @prezime2)
2036else if @status = 's'
2037 select * from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2038else if @status = 'd'
2039 delete from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2040else
2041 print 'Krivo unseno slovo'
2042go
2043
2044
2045create function cruder(
2046 @ime nvarchar(50),
2047 @prezime nvarchar(50)
2048)
2049returns int
2050as
2051begin
2052
2053declare @vracaj int
2054select @vracaj = IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime
2055
2056return @vracaj
2057end
2058go
2059
2060execute crud 'tester', 'testeric', 1, 'u', 'tester', 'testeric'
2061
2062select * from Komercijalist
2063
2064
2065
2066
2067
2068
2069
2070
2071--4 ishod
2072--1
2073
2074create proc zad1
2075@naziv nvarchar(50),
2076@iddrz int,
2077@kljuc int output
2078as
2079declare @nazivdr nvarchar(50)
2080declare @drid int
2081select @nazivdr = Naziv, @drid = DrzavaID from grad
2082
2083if @nazivdr = @naziv and @iddrz = @drid
2084 begin
2085 set @kljuc = -1
2086 print 'Grad je vec kreiran'
2087 end
2088else
2089 begin
2090 insert into Grad(Naziv, DrzavaID) values(@naziv, @iddrz)
2091 set @kljuc = SCOPE_IDENTITY()
2092 end
2093go
2094
2095declare @ispis nvarchar(50)
2096exec zad1 'Tokyo', 3, @ispis output
2097print @ispis
2098go
2099
2100drop proc zad1
2101
2102--provjera
2103select * from Grad
2104delete from Grad where IDGrad = 15
2105
2106
2107--2
2108
2109create function BrojRacunaKomercijalista(
2110@IDkom int
2111)
2112returns int
2113as
2114begin
2115
2116declare @brojac int
2117
2118select @brojac = count(*) from Komercijalist as k inner join Racun as r
2119on k.IDKomercijalist = r.KomercijalistID
2120where r.KomercijalistID = @IDkom
2121
2122return @brojac
2123end
2124go
2125
2126select k.Ime, k.Prezime, k.StalniZaposlenik, dbo.BrojRacunaKomercijalista(k.IDKomercijalist)
2127as [Broj Racuna] from Komercijalist as k
2128
2129
2130--3
2131create proc crud
2132 @ime nvarchar(50),
2133 @prezime nvarchar(50),
2134 @stalnozaposlen bit,
2135 @status char(1),
2136 @ime2 nvarchar(50),
2137 @prezime2 nvarchar(50)
2138 as
2139 if @status ='i'
2140 insert into Komercijalist(Ime, Prezime, StalniZaposlenik)
2141 values(@ime, @prezime, @stalnozaposlen)
2142else if @status = 'u'
2143 update Komercijalist set Ime = @ime, Prezime = @prezime,
2144 StalniZaposlenik = @stalnozaposlen where IDKomercijalist = dbo.cruder(@ime2, @prezime2)
2145else if @status = 's'
2146 select * from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2147else if @status = 'd'
2148 delete from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2149else
2150 print 'Krivo unseno slovo'
2151go
2152
2153
2154create function cruder(
2155 @ime nvarchar(50),
2156 @prezime nvarchar(50)
2157)
2158returns int
2159as
2160begin
2161
2162declare @vracaj int
2163select @vracaj = IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime
2164
2165return @vracaj
2166end
2167go
2168
2169execute crud 'tester', 'testeric', 1, 'u', 'tester', 'testeric'
2170
2171select * from Komercijalist
2172
2173
2174
2175
2176
2177
2178--4 ishod
2179--1
2180
2181create proc zad1
2182@naziv nvarchar(50),
2183@iddrz int,
2184@kljuc int output
2185as
2186declare @nazivdr nvarchar(50)
2187declare @drid int
2188select @nazivdr = Naziv, @drid = DrzavaID from grad
2189
2190if @nazivdr = @naziv and @iddrz = @drid
2191 begin
2192 set @kljuc = -1
2193 print 'Grad je vec kreiran'
2194 end
2195else
2196 begin
2197 insert into Grad(Naziv, DrzavaID) values(@naziv, @iddrz)
2198 set @kljuc = SCOPE_IDENTITY()
2199 end
2200go
2201
2202declare @ispis nvarchar(50)
2203exec zad1 'Tokyo', 3, @ispis output
2204print @ispis
2205go
2206
2207drop proc zad1
2208
2209--provjera
2210select * from Grad
2211delete from Grad where IDGrad = 15
2212
2213
2214--2
2215
2216create function BrojRacunaKomercijalista(
2217@IDkom int
2218)
2219returns int
2220as
2221begin
2222
2223declare @brojac int
2224
2225select @brojac = count(*) from Komercijalist as k inner join Racun as r
2226on k.IDKomercijalist = r.KomercijalistID
2227where r.KomercijalistID = @IDkom
2228
2229return @brojac
2230end
2231go
2232
2233select k.Ime, k.Prezime, k.StalniZaposlenik, dbo.BrojRacunaKomercijalista(k.IDKomercijalist)
2234as [Broj Racuna] from Komercijalist as k
2235
2236
2237--3
2238create proc crud
2239 @ime nvarchar(50),
2240 @prezime nvarchar(50),
2241 @stalnozaposlen bit,
2242 @status char(1),
2243 @ime2 nvarchar(50),
2244 @prezime2 nvarchar(50)
2245 as
2246 if @status ='i'
2247 insert into Komercijalist(Ime, Prezime, StalniZaposlenik)
2248 values(@ime, @prezime, @stalnozaposlen)
2249else if @status = 'u'
2250 update Komercijalist set Ime = @ime, Prezime = @prezime,
2251 StalniZaposlenik = @stalnozaposlen where IDKomercijalist = dbo.cruder(@ime2, @prezime2)
2252else if @status = 's'
2253 select * from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2254else if @status = 'd'
2255 delete from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2256else
2257 print 'Krivo unseno slovo'
2258go
2259
2260
2261create function cruder(
2262 @ime nvarchar(50),
2263 @prezime nvarchar(50)
2264)
2265returns int
2266as
2267begin
2268
2269declare @vracaj int
2270select @vracaj = IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime
2271
2272return @vracaj
2273end
2274go
2275
2276execute crud 'tester', 'testeric', 1, 'u', 'tester', 'testeric'
2277
2278select * from Komercijalist
2279
2280---------------------
2281
2282--1
2283select k.Ime, k.Prezime, d.Naziv from Kupac as k inner join Grad as g
2284on g.IDGrad = k.GradID inner join Drzava as d on d.IDDrzava = g.DrzavaID
2285where (k.Telefon like '% %' or k.Telefon like '%-%') and d.Naziv = 'Hrvatska'
2286
2287--2
2288select p.Naziv, sum(s.Kolicina) as kolicina from Proizvod as p inner join Stavka as s
2289on p.IDProizvod = s.ProizvodID inner join Racun as r
2290on r.IDRacun = s.RacunID inner join Kupac as k on k.IDKupac = r.KupacID
2291inner join Grad as g on g.IDGrad = k.GradID inner join Drzava as d
2292on d.IDDrzava = g.DrzavaID where d.Naziv='Bosna i Hercegovina'
2293group by p.Naziv order by kolicina asc
2294
2295--3
2296insert into Drzava(Naziv) values('Canada')
2297insert into Grad(Naziv, DrzavaID) values('Montreal', 4)
2298insert into Kupac(Ime, Prezime, GradID) values('Leonardo', 'Cohen', 14)
2299
2300--4
2301
2302create table NajprodavanijiProizvodi(
2303IDProizvod int not null,
2304Naziv nvarchar(50) not null,
2305ProdanihKomada int not null
2306)
2307
2308select top 10 p.Naziv, sum(s.Kolicina) as kolicina from Proizvod as p inner join Stavka as s
2309on p.IDProizvod = s.ProizvodID
2310group by p.Naziv order by kolicina desc
2311
2312insert into NajprodavanijiProizvodi(IDProizvod, Naziv, ProdanihKomada)
2313select top 10 1, p.Naziv, sum(s.Kolicina) as kolicina from Proizvod as p inner join Stavka as s
2314on p.IDProizvod = s.ProizvodID
2315group by p.Naziv order by kolicina desc
2316
2317--5
2318create table dostavljac(
2319IDdostavljac int not null primary key,
2320Ime nvarchar(50) not null,
2321prezime nvarchar(50) not null,
2322oib int not null,
2323unique(oib)
2324)
2325
2326create table pizza(
2327IDpizza int not null primary key,
2328naziv nvarchar(50) not null,
2329cijena int not null check(cijena >20 and cijena <40)
2330)
2331
2332create table dostava(
2333IDdostava int not null primary key,
2334datumdostave date not null,
2335dostavljacID int not null foreign key references dostavljac (IDdostavljac),
2336pizzaID int not null foreign key references pizza (IDpizza)
2337)
2338
2339--6
2340create view mjeseci
2341as
2342select top 99.99 percent MONTH(r.DatumIzdavanja) as mjeseci, count(*) as broj from Racun as r
2343group by month(r.DatumIzdavanja)
2344order by mjeseci asc
2345go
2346
2347select * from mjeseci
2348drop view mjeseci
2349
2350--7
2351
2352create view imeiprezime
2353as
2354select k.Ime, k.Prezime
2355from Kupac as k where (select count(*) from Racun as r where r.KupacID = k.IDKupac)> 3
2356go
2357
2358select * from imeiprezime
2359
2360alter view imeiprezime
2361as
2362select k.Ime, k.Prezime,(select count(*) from Racun as r where r.KupacID = k.IDKupac)as broj
2363from Kupac as k where (select count(*) from Racun as r where r.KupacID = k.IDKupac)> 3
2364go
2365
2366select * from imeiprezime
2367
2368drop view imeiprezime
2369
2370--8
2371create view proizvodview
2372as
2373select p.Naziv from Proizvod as p
2374where p.Boja is not null and p.CijenaBezPDV >= 1000
2375with check option
2376go
2377
2378select * from proizvodview
2379
2380drop view proizvodview
2381
2382--9
2383create view kartice
2384as
2385select cast(kk.IstekGodina as varchar(5))+'-'+ CAST(kk.IstekMjesec as varchar(5)) as godina_i_mjesec, COUNT(*) as broj_kartica
2386from KreditnaKartica as kk where kk.Tip='Visa'
2387group by kk.IstekGodina, kk.IstekMjesec
2388go
2389
2390--provjera
2391select * from KreditnaKartica as kk where kk.Tip ='Visa' and kk.IstekGodina=2006
2392and kk.IstekMjesec=5
2393
2394alter view kartice
2395with schemabinding
2396as
2397select top 2 CAST(kk.IstekMjesec as varchar(5)) as mjeseci, COUNT(*) as broj_kartica
2398from dbo.KreditnaKartica as kk
2399group by kk.IstekMjesec
2400order by broj_kartica desc
2401go
2402
2403select * from kartice
2404
2405drop view kartice
2406
2407
2408--10
2409dbcc traceon(3604)
2410dbcc ind('AdventureWorksOBP', 'Racun', -1)
2411
2412--a
2413--Podaci su smjesteni na 200 stranica, a ineksi su smjesteni na 1 stranicu
2414
2415dbcc page('AdventureWorksOBP', 1, 490, 3) with tableresults
2416--b
2417--Vrijednost stupca IDRacun za 1 je 43659, a za zadnji 43816 na prvoj stranici
2418
2419dbcc page('AdventureWorksOBP', 1, 493, 3) with tableresults
2420--c
2421--Nalazi ste na stranici 493
2422
2423--d
2424insert into Racun(DatumIzdavanja, BrojRacuna, KupacID)
2425values('20151115', 'SO75123', 4189)
2426--dobio je ID 75124
2427dbcc ind('AdventureWorksOBP', 'Racun', -1)
2428dbcc page('AdventureWorksOBP', 1, 1032, 3) with tableresults
2429--Nalazi se na stranici 1032
2430
2431delete from Racun where IDRacun = 75124
2432
2433--11
2434SET STATISTICS IO ON
2435
2436select IDKupac from Kupac where Prezime = 'Adams' and GradID = 9
2437
2438create nonclustered index kup on Kupac(Prezime)
2439include(GradID)
2440
2441drop index kup on kupac
2442
2443--12
2444
2445select IDKupac, Ime, Prezime from Kupac where Prezime like 'A%'
2446and GradID = 9
2447
2448create nonclustered index kup2 on Kupac(Prezime)
2449include(Ime, GradID)
2450
2451drop index kup2 on Kupac
2452
2453
2454--4 ishod
2455--1
2456
2457create proc zad1
2458@naziv nvarchar(50),
2459@iddrz int,
2460@kljuc int output
2461as
2462declare @nazivdr nvarchar(50)
2463declare @drid int
2464select @nazivdr = Naziv, @drid = DrzavaID from grad
2465
2466if @nazivdr = @naziv and @iddrz = @drid
2467 begin
2468 set @kljuc = -1
2469 print 'Grad je vec kreiran'
2470 end
2471else
2472 begin
2473 insert into Grad(Naziv, DrzavaID) values(@naziv, @iddrz)
2474 set @kljuc = SCOPE_IDENTITY()
2475 end
2476go
2477
2478declare @ispis nvarchar(50)
2479exec zad1 'Tokyo', 3, @ispis output
2480print @ispis
2481go
2482
2483drop proc zad1
2484
2485--provjera
2486select * from Grad
2487delete from Grad where IDGrad = 15
2488
2489
2490--2
2491
2492create function BrojRacunaKomercijalista(
2493@IDkom int
2494)
2495returns int
2496as
2497begin
2498
2499declare @brojac int
2500
2501select @brojac = count(*) from Komercijalist as k inner join Racun as r
2502on k.IDKomercijalist = r.KomercijalistID
2503where r.KomercijalistID = @IDkom
2504
2505return @brojac
2506end
2507go
2508
2509select k.Ime, k.Prezime, k.StalniZaposlenik, dbo.BrojRacunaKomercijalista(k.IDKomercijalist)
2510as [Broj Racuna] from Komercijalist as k
2511
2512
2513--3
2514create proc crud
2515 @ime nvarchar(50),
2516 @prezime nvarchar(50),
2517 @stalnozaposlen bit,
2518 @status char(1),
2519 @ime2 nvarchar(50),
2520 @prezime2 nvarchar(50)
2521 as
2522 if @status ='i'
2523 insert into Komercijalist(Ime, Prezime, StalniZaposlenik)
2524 values(@ime, @prezime, @stalnozaposlen)
2525else if @status = 'u'
2526 update Komercijalist set Ime = @ime, Prezime = @prezime,
2527 StalniZaposlenik = @stalnozaposlen where IDKomercijalist = dbo.cruder(@ime2, @prezime2)
2528else if @status = 's'
2529 select * from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2530else if @status = 'd'
2531 delete from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
2532else
2533 print 'Krivo unseno slovo'
2534go
2535
2536
2537create function cruder(
2538 @ime nvarchar(50),
2539 @prezime nvarchar(50)
2540)
2541returns int
2542as
2543begin
2544
2545declare @vracaj int
2546select @vracaj = IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime
2547
2548return @vracaj
2549end
2550go
2551
2552execute crud 'tester', 'testeric', 1, 'u', 'tester', 'testeric'
2553
2554select * from Komercijalist
2555
2556-- Zadatak 1 (Ishod 5)
2557
2558CREATE TABLE Zapisnik(
2559 IDZapisnik INT PRIMARY KEY IDENTITY,
2560 Sadrzaj nvarchar(MAX)
2561 )
2562
2563GO
2564
2565CREATE TRIGGER okidacDrzava ON Drzava AFTER INSERT,UPDATE,DELETE
2566AS
2567
2568DECLARE @uneseno INT
2569DECLARE @obrisano INT
2570
2571SELECT @uneseno=IDDrzava FROM inserted
2572SELECT @obrisano=IDDrzava FROM deleted
2573
2574IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
2575BEGIN
2576 INSERT INTO Zapisnik VALUES ('U tablicu država unesen je redak s ID-em ' + CAST(@uneseno AS nvarchar(50)))
2577END
2578IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
2579BEGIN
2580 INSERT INTO Zapisnik VALUES ('Iz tablice država obrisan je redak s ID-em ' + CAST(@obrisano AS nvarchar(50)))
2581END
2582IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
2583BEGIN
2584 INSERT INTO Zapisnik VALUES ('U tablici država promijenjen je redak s ID-em ' + CAST(@obrisano AS nvarchar(50)))
2585END
2586
2587GO
2588
2589INSERT INTO Drzava VALUES ('Sokovia')
2590UPDATE Drzava SET Naziv='Moldova' WHERE IDDrzava=SCOPE_IDENTITY()
2591DELETE FROM Drzava WHERE IDDrzava=SCOPE_IDENTITY()
2592
2593SELECT * FROM Zapisnik
2594
2595DROP TRIGGER okidacDrzava
2596
2597-- Zadatak 2 (Ishod 5)
2598
2599GO
2600
2601CREATE TRIGGER okidacKreditna ON KreditnaKartica FOR UPDATE
2602AS
2603DECLARE @Brojac INT
2604DECLARE @StaraGodina INT
2605DECLARE @NovaGodina INT
2606DECLARE @StariMjesec INT
2607DECLARE @NoviMjesec INT
2608DECLARE @BrojKartice INT
2609
2610SELECT @Brojac=COUNT(*) FROM inserted
2611
2612IF @Brojac=1
2613 BEGIN
2614 SELECT @NovaGodina=IstekGodina FROM inserted
2615 SELECT @NoviMjesec=IstekMjesec FROM inserted
2616 SELECT @StaraGodina=IstekGodina FROM deleted
2617 SELECT @StariMjesec=IstekMjesec FROM deleted
2618 SELECT @BrojKartice=Broj FROM inserted
2619 INSERT INTO Zapisnik VALUES ('Promijenjeni podaci kartice broj ' + CAST(@BrojKartice AS nvarchar(50)) + ' staro trajanje je ' + CAST (@StaraGodina AS nvarchar(50)) + '-' + CAST(@StariMjesec AS nvarchar(50)) + ', novo trajanje je ' + CAST(@NovaGodina AS nvarchar(50)) + '-' + CAST(@NoviMjesec AS nvarchar(50)))
2620 END
2621ELSE BEGIN
2622 INSERT INTO Zapisnik VALUES ('Promijenjeno više kartica')
2623END
2624GO
2625
2626UPDATE KreditnaKartica
2627SET tip='Visa', Broj='321321321' ,IstekMjesec=11, IstekGodina=2020
2628WHERE IDKreditnaKartica=1
2629
2630SELECT*
2631FROM Zapisnik
2632
2633DROP TRIGGER okidacKreditna
2634
2635-- Zadatak 3 (Ishod 6)
2636
2637GO
2638
2639CREATE PROC Zadatak3(
2640 @inputXML XML
2641 )
2642AS
2643DECLARE @DrzavaID INT
2644IF EXISTS (SELECT * FROM Drzava WHERE Naziv IN (SELECT Tablica.Stupac.VALUE('@Naziv','nvarchar(50)') FROM @inputXML.nodes('/Drzava') AS Tablica(Stupac)))
2645BEGIN
2646 SELECT @DrzavaID=IDDrzava FROM Drzava WHERE Naziv IN (SELECT Tablica.Stupac.VALUE('@Naziv','nvarchar(50)') FROM @inputXML.nodes('/Drzava') AS Tablica(Stupac))
2647 INSERT INTO Grad
2648 SELECT Tablica.Stupac.VALUE('.','nvarchar(50)') AS Naziv,
2649 @DrzavaID AS DrzavaID
2650 FROM @inputXML.nodes('/Drzava/Grad') AS Tablica(Stupac)
2651END
2652ELSE BEGIN
2653 INSERT INTO Drzava
2654 SELECT Tablica.Stupac.VALUE('@Naziv','nvarchar(50)')
2655 FROM @inputXML.nodes('/Drzava') AS Tablica(Stupac)
2656 SET @DrzavaID=SCOPE_IDENTITY()
2657 INSERT INTO Grad
2658 SELECT Tablica.Stupac.VALUE('.','nvarchar(50)') AS Naziv,
2659 @DrzavaID AS DrzavaID
2660 FROM @inputXML.nodes('/Drzava/Grad') AS Tablica(Stupac)
2661END
2662EXEC Zadatak3 '<Drzava Naziv="Hrvatska14515">
2663<Grad>Bjelovar</Grad>
2664<Grad>Vinkovci</Grad>
2665<Grad>Koprivnica</Grad>
2666</Drzava>'
2667
2668-- Zadatak 4 (Ishod 6)
2669
2670GO
2671
2672CREATE TYPE Optimus AS TABLE(
2673Naziv nvarchar(50),
2674BrojProizvoda nvarchar(50),
2675Boja nvarchar(50),
2676MinimalnaKolicinaNaSkladistu INT,
2677CijenaBezPDV money,
2678PotkategorijaID INT)
2679
2680GO
2681
2682CREATE PROC Zadatak04(
2683 @inputTab Optimus READONLY
2684 )
2685AS
2686INSERT INTO Proizvod
2687SELECT * FROM @inputTab
2688
2689GO
2690
2691DECLARE @inputTab Optimus
2692INSERT INTO @inputTab VALUES ('Test','15616565','plava',15,500,1)
2693INSERT INTO @inputTab VALUES ('Testic2','14187948','crvena',47,458,1)
2694EXEC Zadatak04 @inputTab
2695
2696-- Zadatak 5 (Ishod 7)
2697
2698GO
2699CREATE PROC Zadatak05(
2700 @IDKreditnaKartica INT)
2701AS
2702DELETE FROM Stavka WHERE RacunID IN (SELECT IDRacun FROM Racun WHERE KreditnaKarticaID IN (SELECT IDKreditnaKartica FROM KreditnaKartica WHERE IDKreditnaKartica=@IDKreditnaKartica))
2703DELETE FROM Racun WHERE KreditnaKarticaID IN (SELECT IDKreditnaKartica FROM KreditnaKartica WHERE IDKreditnaKartica=@IDKreditnaKartica)
2704DELETE FROM KreditnaKartica WHERE IDKreditnaKartica=@IDKreditnaKartica
2705GO
2706
2707BEGIN TRY
2708 BEGIN TRAN
2709 EXEC Zadatak05 5
2710 EXEC Zadatak05 72
2711 EXEC Zadatak05 1
2712 COMMIT TRAN
2713 PRINT 'Brisanje uspješno!'
2714END TRY
2715BEGIN CATCH
2716 ROLLBACK TRAN
2717 PRINT 'Desila se greška: ' + ERROR_MESSAGE()
2718END CATCH
2719GO
2720
2721
2722-- Zadatak 6 (Ishod 7)
2723
2724-- Skripta 1
2725
2726--1
2727SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2728BEGIN TRAN
2729
2730--3
2731UPDATE Drzava
2732SET Naziv='Hrvatistan'
2733WHERE IDDrzava=1
2734
2735--5
2736ROLLBACK TRAN
2737SELECT * FROM Drzava WHERE IDDrzava=1
2738
2739-- Skripta 2
2740
2741--2
2742SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2743BEGIN TRAN
2744
2745--4
2746SELECT * FROM Drzava WHERE IDDrzava=1 --Prljavo Äitanje
2747
2748--6
2749ROLLBACK TRAN
2750
2751
2752-- Zadatak 7 (Ishod 8)
2753
2754EXEC sp_addumpdevice
2755 @devtype='Disk',
2756 @logicalname='IspitnaLokacija2',
2757 @physicalname='D:\Proba.bak'
2758
2759ALTER DATABASE AdventureWorksOBP
2760SET RECOVERY FULL
2761
2762BACKUP DATABASE AdventureWorksOBP TO IspitnaLokacija2
2763WITH
2764 name='AWBP1',
2765 description='Prvi backup - puni.',
2766 STATS
2767
2768INSERT INTO KreditnaKartica VALUES ('Visa',758963145872,12,2018)
2769
2770BACKUP DATABASE AdventureWorksOBP TO IspitnaLokacija2
2771WITH
2772 DIFFERENTIAL,
2773 name='AWBP1',
2774 description='Prvi backup - puni.'
2775
2776RESTORE HEADERONLY FROM IspitnaLokacija2
2777RESTORE FILELISTONLY FROM IspitnaLokacija2
2778
2779RESTORE DATABASE AWOBP_Vraceno FROM IspitnaLokacija2
2780WITH
2781 file=1,
2782 MOVE 'AdventureWorksOBP' TO 'D:\SQL\AWOBP.mdf',
2783 MOVE 'AdventureWorksOBP_log' TO 'D:\SQL\AWOBPlog.ldf',
2784 norecovery,
2785 stats
2786
2787RESTORE DATABASE AWOBP_Vraceno FROM IspitnaLokacija2
2788WITH
2789 file=2,
2790 MOVE 'AdventureWorksOBP' TO 'D:\SQL\AWOBP.mdf',
2791 MOVE 'AdventureWorksOBP_log' TO 'D:\SQL\AWOBPlog.ldf',
2792 recovery,
2793 stats
2794
2795SELECT * FROM sys.backup_devices
2796
2797GO
2798EXEC sp_dropdevice
2799 @logicalname='IspitnaLokacija2'
2800GO
2801
2802-- Zadatak 8 (Ishod 8)
2803
2804CREATE LOGIN [Mirko Mirkić]
2805WITH PASSWORD = 'centos'
2806GO
2807
2808CREATE USER Mirko FROM LOGIN [Mirko Mirkić]
2809GO
2810
2811CREATE ROLE Selektori
2812GO
2813
2814GRANT SELECT ON Proizvod TO Selektori
2815GO
2816
2817EXEC sp_addrolemember
2818 @rolename='Selektori',
2819 @membername='Mirko'