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