agpDvzNA

1/*
2=========================================================
3==========================BLIC===========================
4=========================================================
5*/
6
7/* Zadatak - ishod 4
8Napravite proceduru koja ce kroz ulazne parametre primati podatke
9o nekom kupcu: ime, prezime, email, telefon, te naziv grada i drzave kupca.
10
11Procedura provjerava postoji li taj kupac, njegov grad i njegova drzava u bazi.
12Postojanje kupca provjeriti pomocu imena, prezimena i emaila.
13Ukoliko neki od podataka nedostaje procedura ga treba upisati.
14Podatke koji vec postoje ne treba ponovo upisivati.
15
16Neovisno o tome jesu li podatci postojali ili ih je procedura upisala,
17kroz izlazni parametar treba vratiti jednu od poruka, ovisno o tome
18sto je procedura napravila;
19
20"novi kupac (IDKupac), novi grad(IDGrad), nova drzava (IDDrzava)"
21"novi kupac (IDKupac), novi grad(IDGrad), postojeca drzava (IDDrzava)"
22"novi kupac (IDKupac), postojeci grad(IDGrad), postojeca drzava (IDDrzava)"
23"postojeci kupac (IDKupac), postojeci grad(IDGrad), postojeca drzava (IDDrzava)"
24Pritom su IDKupac, IDGrad i IDDrzava u gornjim porukama stvarni podatci iz baze.
25
26Napraviti jedan primjer poziva procedure koji ce ispisati vrijednost 
27izlaznog parametra procedure. */
28use AdventureWorksOBP
29go
30
31create proc p1
32    @ime nvarchar(50), @prezime nvarchar(50), @email nvarchar(50),
33    @telefon nvarchar(25), @grad nvarchar(50), @drzava nvarchar(50)
34as
35    declare @idkupac int, @idgrad int, @iddrzava int
36
37    select @idkupac = k.IDKupac
38    from Kupac as k
39    where k.Ime = @ime and k.Prezime = @prezime and k.Email = @email
40
41    select @idgrad = g.IDGrad
42    from Grad as g
43    where g.Naziv = @grad
44
45    select @iddrzava = d.IDDrzava
46    from Drzava as d
47    where d.Naziv = @drzava
48
49    IF @idkupac is null and @idgrad is null and @iddrzava is null
50    BEGIN
51        insert into Drzava(Naziv)
52        values (@drzava)
53        set @iddrzava = SCOPE_IDENTITY()
54        
55        insert into Grad(Naziv, DrzavaID)
56        values (@grad, @iddrzava)
57        set @idgrad = SCOPE_IDENTITY()
58        
59        insert into Kupac(Ime, Prezime, Email, Telefon, GradID)
60        values (@ime, @prezime, @email, @telefon, @idgrad)
61        set @idkupac = SCOPE_IDENTITY()
62        
63        return 'Novi Kupac(' + cast(@idkupac as nvarchar(50)) + '), novi grad(' + cast(@idgrad as nvarchar(50)) + '), nova drzava(' + cast(@iddrzava as nvarchar(50)) + ').'
64    END
65    ELSE IF @idkupac is null and @idgrad is null and @iddrzava is not null
66    BEGIN
67        insert into Grad(Naziv, DrzavaID)
68        values (@grad, @iddrzava)
69        set @idgrad = SCOPE_IDENTITY()
70        
71        insert into Kupac(Ime, Prezime, Email, Telefon, GradID)
72        values (@ime, @prezime, @email, @telefon, @idgrad)
73        set @idkupac = SCOPE_IDENTITY()
74        
75        return 'Novi Kupac(' + cast(@idkupac as nvarchar(50)) + '), novi grad(' + cast(@idgrad as nvarchar(50)) + '), postojeca drzava(' + cast(@iddrzava as nvarchar(50)) + ').'
76    END
77    ELSE IF @idkupac is null and @idgrad is not null and @iddrzava is not null
78    BEGIN
79        insert into Kupac(Ime, Prezime, Email, Telefon, GradID)
80        values (@ime, @prezime, @email, @telefon, @idgrad)
81        set @idkupac = SCOPE_IDENTITY()
82        
83        return 'Novi Kupac(' + cast(@idkupac as nvarchar(50)) + '), postojeci grad(' + cast(@idgrad as nvarchar(50)) + '), postojeca drzava(' + cast(@iddrzava as nvarchar(50)) + ').'
84    END
85    ELSE
86        BEGIN
87        return 'Postojeci Kupac(' + cast(@idkupac as nvarchar(50)) + '), postojeci grad(' + cast(@idgrad as nvarchar(50)) + '), postojeca drzava(' + cast(@iddrzava as nvarchar(50)) + ').'
88    END
89go
90-- Declare a variable to return the results of the function. 
91DECLARE @ret nvarchar(50);   
92
93-- Execute the function while passing a value to the @status parameter
94EXEC @ret = dbo.p1 "Ivan", "Ivanovic", "ivan@example.com", "056067253", "zagreb", "hrvatska"
95
96-- View the returned value.  The Execute and Select statements must be executed at the same time.  
97SELECT N'Function returned: ' + @ret; 
98
99
100
101/*
102=========================================================
103=======================Predavanja========================
104=========================================================
105*/
106-- -- -- -- -- -- -- -- PREDAVANJE 1
107-- 1. Dohvatiti sve kupce iz tablice Kupac koji se zovu Ana ili Tamara i iz Osijeka su.
108SELECT * FROM Kupac WHERE Ime IN ('Ana', 'Tamara') AND GradID = 2
109-- ili 
110SELECT * FROM Kupac WHERE (Ime = 'Ana' OR Ime = 'Tamara') AND GradID = 2
111
112-- 2. Umetnite proizvod "Sony Player" cijene 985,50 kuna. 
113-- Potkategorija je "Playeri", kategorija "Razno". Podatke koji nisu zadani izmislite.
114INSERT INTO Kategorija (Naziv) VALUES ('Razno') 
115
116-- Ume�e kategoriju s ID-em 9 (kod vas mo�e biti druk�ije).
117INSERT INTO Potkategorija (KategorijaID, Naziv) VALUES (9, 'Playeri') 
118
119-- Ume�e potkategoriju s ID-em 38 (kod vas mo�e biti druk�ije).
120INSERT INTO Proizvod (Naziv, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV, PotkategorijaID) 
121VALUES ('Sony Player', 'XX-0001', 'Trula vi�nja', 20, 985.50, 38)
122
123-- 3. Napravite tablicu KupacVIP sa stupcima ime i prezime. Umetnite u nju sve kupce koji se zove Karen, Mary ili Jimmy.
124CREATE TABLE KupacVIP
125(
126	IDKupacVIP int CONSTRAINT PK_KupacVIP PRIMARY KEY IDENTITY,
127	Ime nvarchar(50) NOT NULL,
128	Prezime nvarchar(50) NOT NULL
129)
130
131INSERT INTO KupacVIP (Ime, Prezime)
132SELECT Ime, Prezime FROM Kupac WHERE Ime IN ('Karen', 'Mary', 'Jimmy')
133
134SELECT * FROM KupacVIP
135
136DROP TABLE KupacVIP
137
138-- 4. Kupcima s ID-evima 40, 41 i 42 promijenite e-mail u nepoznato@nepoznato.com
139UPDATE Kupac
140SET Email = 'nepoznato@nepoznato.com'
141WHERE IDKupac IN (40, 41, 42)
142
143SELECT * FROM Kupac WHERE IDKupac IN (40, 41, 42)
144
145-- 5. Obri�ite sve kupce koji se prezivaju Trtimirovi�. Je li se dogodila pogre�ka? Koliko ih je obrisano?
146DELETE FROM Kupac WHERE Prezime = 'Trtimirovi�'
147
148-- 6. Dohvatiti imena i prezimena svih kupaca i uz svakog ispisati naziv grada i dr�ave.
149SELECT 
150	k.Ime,
151	k.Prezime,
152	g.Naziv AS NazivGrada,
153	d.Naziv AS NazivDrzave
154FROM Kupac AS k
155INNER JOIN Grad AS g ON k.GradID = g.IDGrad
156INNER JOIN Drzava AS d ON g.DrzavaID = d.IDDrzava
157
158-- 7. Ispisati nazive proizvoda koji su na nekoj stavci ra�una prodani u vi�e od 35 komada. Svaki proizvod navesti samo jednom.
159SELECT DISTINCT
160	p.Naziv
161FROM Stavka AS s
162INNER JOIN Proizvod AS p ON s.ProizvodID = p.IDProizvod
163WHERE s.Kolicina > 35
164
165-- 8. Koriste�i lijevo vanjsko spajanje dohvatiti sve proizvode koji nisu nikad prodani.
166SELECT *
167FROM Proizvod AS p
168LEFT OUTER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
169WHERE s.ProizvodID IS NULL
170
171-- 9. Koriste�i puno vanjsko spajanje ispisati nazive dr�ava i nazive gradova. 
172-- Ispisati samo one gradove koji nemaju definiranu dr�avu i one dr�ave koji 
173-- nemaju upisanih gradova.
174SELECT 
175	g.Naziv AS NazivGrada,
176	d.Naziv AS NazivDrzave
177FROM Grad AS g
178FULL OUTER JOIN Drzava AS d ON g.DrzavaID = d.IDDrzava
179WHERE g.IDGrad IS NULL OR d.IDDrzava IS NULL
180
181-- 10. Vratite nazive svih proizvoda i uz svaki ispi�ite boju ako je definirana, 
182-- odnosno "NIJE DEFINIRANA" ako nije.
183SELECT 
184	Naziv,
185	ISNULL(Boja, 'NIJE DEFINIRANA') AS Boja
186FROM Proizvod
187
188-- 11. Vratite prosje�nu cijenu proizvoda iz potkategorije 16.
189SELECT AVG(CijenaBezPdv)
190FROM Proizvod
191WHERE PotkategorijaID = 16
192
193-- 12. Vratite datume najstarijeg i najnovijeg ra�una izdanog kupcu 131.
194SELECT 
195	MIN(DatumIzdavanja) AS NajstarijiRacun,
196	MAX(DatumIzdavanja) AS NajnovijiRacun
197FROM Racun
198WHERE KupacID = 131
199
200-- 13. Grupiranjem ispi�ite sve boje proizvoda i pokraj svake napi�ite koliko proizvoda ima tu boju.
201SELECT 
202	Boja,
203	COUNT(*) AS BrojPoizvodaKojiImaTuBoju
204FROM Proizvod
205GROUP BY Boja
206
207-- 14. Grupiranjem ispi�ite koliko je ra�una izdano koje godine.
208SELECT 
209	YEAR(DatumIzdavanja) AS GodinaIzdavanja,
210	COUNT(*) AS IzdanoTeGodine
211FROM Racun
212GROUP BY YEAR(DatumIzdavanja)
213
214-- 15. Grupiranjem ispi�ite ukupno zara�ene iznose za svaki od proizvoda koji je prodan u vi�e od 2000 primjeraka.
215SELECT 
216	p.Naziv,
217	SUM(s.UkupnaCijena) AS UkupnoZaradjeno
218FROM Stavka AS s
219INNER JOIN Proizvod AS p ON s.ProizvodID = p.IDProizvod
220GROUP BY p.Naziv
221HAVING SUM(s.Kolicina) > 2000
222
223-- 16. Koriste�i podupite, dohvatite imena i prezimena 5 komercijalista koji su izdali najvi�e ra�una.
224SELECT TOP 5
225    k.Ime, 
226    k.Prezime, 
227    (SELECT COUNT(r.IDRacun) FROM Racun r WHERE k.IDKomercijalist = r.KomercijalistID) AS UkupnoRacuna 
228FROM Komercijalist k
229ORDER BY UkupnoRacuna DESC
230
231-- 17. Dohvatite sve boje proizvoda. Uz svaku boju pomo�u podupita dohvatite broj proizvoda u toj boji.
232SELECT DISTINCT
233	p.Boja,
234	(SELECT COUNT(*)
235	FROM Proizvod AS p1
236	WHERE ISNULL(p1.Boja, 'NEMA') = ISNULL(p.Boja, 'NEMA')) AS BrojProizvodaTeBoje
237FROM Proizvod AS p
238
239-- 18. Vratite sve proizvode koji nikad nisu prodani:
240-- a)Pomo�u IN ili NOT IN:
241SELECT *
242FROM Proizvod AS p
243WHERE 
244	p.IDProizvod NOT IN (SELECT s.ProizvodID FROM Stavka AS s)
245
246-- b) Pomo�u EXISTS ili NOT EXISTS:
247SELECT *
248FROM Proizvod AS p
249WHERE 
250	NOT EXISTS(SELECT * FROM Stavka AS s WHERE s.ProizvodID = p.IDProizvod)
251	
252
253-- -- -- -- -- -- -- -- PREDAVANJE 2, 3
254/* Kod za DEMO (slide 5) */
255-- 1. Kreiranje baze.
256CREATE DATABASE Predavanje02Demo
257GO
258
259USE Predavanje02Demo
260GO
261
262-- 2. Kreiranje osnovne tablice i punjenje podacima.
263CREATE TABLE Klijent
264(
265	IDKlijent int CONSTRAINT PK_Klijent PRIMARY KEY IDENTITY, 
266	Ime nvarchar(50) NOT NULL, 
267	Prezime nvarchar(50) NOT NULL, 
268	Tel1 nvarchar(50) NULL, 
269	Tel2 nvarchar(50) NULL, 
270	Tel3 nvarchar(50) NULL
271)
272
273INSERT INTO Klijent (Ime, Prezime, Tel1, Tel2, Tel3) VALUES ('Miro', 'Miri�', '095/111-222', null, null)
274INSERT INTO Klijent (Ime, Prezime, Tel1, Tel2, Tel3) VALUES ('Ana', 'Ani�', '091/222-333', '098/999-555', null)
275INSERT INTO Klijent (Ime, Prezime, Tel1, Tel2, Tel3) VALUES ('Juro', 'Juri�', '099/999-222', null, null)
276GO
277
278-- 3. Normalizacija. Prvo kreiramo dvije nove tablice.
279CREATE TABLE KlijentOsoba
280(
281	IDKlijentOsoba int CONSTRAINT PK_KlijentOsoba PRIMARY KEY IDENTITY, 
282	Ime nvarchar(50) NOT NULL, 
283	Prezime nvarchar(50) NOT NULL
284)
285
286CREATE TABLE KlijentTelefon
287(
288	IDKlijentTelefon int CONSTRAINT PK_KlijentTelefon PRIMARY KEY IDENTITY, 
289	KlijentOsobaID int CONSTRAINT FK_KlijentTelefon_KlijentOsoba FOREIGN KEY REFERENCES KlijentOsoba(IDKlijentOsoba) NOT NULL,
290	BrojTelefona nvarchar(50) NOT NULL, 
291	Rbr int NOT NULL
292)
293GO
294
295-- 4. Migriramo podatke (ignoriraju�i potrebnu eksplicitnog umetanja vrijednosti primarnog klju�a).
296INSERT INTO KlijentOsoba (Ime, Prezime)
297SELECT Ime, Prezime
298FROM Klijent
299
300INSERT INTO KlijentTelefon (KlijentOsobaID, BrojTelefona, Rbr)
301SELECT IDKlijent, Tel1, 1 AS Rbr FROM Klijent WHERE Tel1 IS NOT NULL
302
303INSERT INTO KlijentTelefon (KlijentOsobaID, BrojTelefona, Rbr)
304SELECT IDKlijent, Tel2, 2 AS Rbr FROM Klijent WHERE Tel2 IS NOT NULL
305
306INSERT INTO KlijentTelefon (KlijentOsobaID, BrojTelefona, Rbr)
307SELECT IDKlijent, Tel3, 3 AS Rbr FROM Klijent WHERE Tel3 IS NOT NULL
308GO
309
310-- 5. Uklanjamo staru tablicu (i time "skr�imo" sve njene korisnike)
311DROP TABLE Klijent
312GO
313
314-- 6. Izra�ujemo pogled istog naziva.
315CREATE VIEW Klijent
316AS
317SELECT 
318	ko.IDKlijentOsoba AS IDKlijent,
319	ko.Ime,
320	ko.Prezime,
321	(SELECT kt.BrojTelefona FROM KlijentTelefon AS kt WHERE kt.KlijentOsobaID = ko.IDKlijentOsoba AND kt.Rbr = 1) AS Tel1,
322	(SELECT kt.BrojTelefona FROM KlijentTelefon AS kt WHERE kt.KlijentOsobaID = ko.IDKlijentOsoba AND kt.Rbr = 2) AS Tel2,
323	(SELECT kt.BrojTelefona FROM KlijentTelefon AS kt WHERE kt.KlijentOsobaID = ko.IDKlijentOsoba AND kt.Rbr = 3) AS Tel3
324FROM KlijentOsoba AS ko
325GO
326
327-- Aplikacija sad mo�e i dalje koristiti objekt naziva Klijent (ali samo za dohva�anje podataka)
328SELECT * FROM Klijent
329GO
330
331USE master
332DROP DATABASE Predavanje02Demo
333GO
334
335/* Primjeri 1, 2, 3, 4.
3361. Situacija: neki korisnik nema pravo koristiti tablicu
337   Proizvod, a treba određene podatke. Pomognite mu tako
338   što ćete napraviti pogled koji će dohvaćati sve proizvode.
3392. Iskoristite pogled za dohvaćanje svih zapisa
340a. Iskoristite pogled za dohvaćanje zapisa u potkategoriji 13
341b. Iskoristite pogled za ispis boja proizvoda i broja proizvoda u
342   svakoj boji, padajuće prema broju proizvoda
343c. Iskoristite pogled i pokraj naziva proizvoda ispišite i naziv
344   potkategorije
3453. Promijenite pogled tako da preimenujete stupac Naziv u
346   NazivProizvoda. Dohvatite podatke kroz pogled.
3474. Uklonite pogled
348*/
349CREATE VIEW p1 
350AS
351SELECT * FROM Proizvod
352GO
353
354SELECT * FROM p1 
355SELECT * FROM p1 WHERE PotkategorijaID = 13
356SELECT Boja, COUNT(*) AS BrojProizvoda FROM p1 GROUP BY Boja ORDER BY BrojProizvoda DESC
357
358SELECT p1.Naziv, pk.Naziv AS PotkategorijaNaziv
359FROM p1
360INNER JOIN Potkategorija AS pk ON p1.PotkategorijaID = pk.IDPotkategorija
361GO
362
363ALTER VIEW p1 
364AS
365SELECT IDProizvod, Naziv AS NazivProizvoda, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV, PotkategorijaID FROM Proizvod
366GO
367
368SELECT * FROM p1 
369
370DROP VIEW p1
371GO
372
373/* Primjeri 5, 6.
3745. Vašeg šefa (koji voli Management Studio) zanimaju
375   podaci: ime i prezime komercijalista i koliko je ukupno
376   prodao proizvoda. Rezultate želi sortirane opadajuće
377   prema broju prodanih proizvoda.
378   a. Objasnite šefu kako da napiše upit
379   b. Napravite pogled i objasnite Å¡efu kako da ga koristi
3806. Obrišite pogled.
381*/
382CREATE VIEW p2
383AS
384SELECT
385	k.Ime,
386	k.Prezime,
387	SUM(s.Kolicina) AS KolicinaProizvoda
388FROM Komercijalist AS k
389INNER JOIN Racun AS r on k.IDKomercijalist = r.KomercijalistID
390INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
391GROUP BY k.Ime, k.Prezime
392GO
393
394SELECT * FROM p2 ORDER BY KolicinaProizvoda DESC
395GO
396
397DROP VIEW p2
398GO
399
400/* Primjeri 7, 8.
4017. Gospođa iz prodaje treba pristup podacima o kreditnim
402   karticama, ali samo za kartice tipa Diners. Napravite
403   pogled. Pomoću pogleda dohvatite sve podatke o
404   karticama tipa Diners, a zatim o karticama tipa Visa.
4058. Obrišite pogled.
406*/
407CREATE VIEW p3
408AS
409SELECT *
410FROM KreditnaKartica AS kk
411WHERE kk.Tip = 'Diners'
412GO
413
414SELECT * FROM p3
415SELECT * FROM p3 WHERE Tip = 'Diners'
416SELECT * FROM p3 WHERE Tip = 'Visa'
417GO
418
419DROP VIEW p3
420GO
421
422/* Primjeri 9, 10, 11.
4239. Marketing treba podatke o svim proizvodima koji su
424   prodani u više od 2000 primjeraka. Napravite pogled koji
425   će to omogućiti.
42610. Dodajte u pogled informaciju o broj prodanih proizvoda.
42711. Uklonite pogled.
428*/
429CREATE VIEW p4
430AS
431SELECT *
432FROM Proizvod AS p
433WHERE (SELECT SUM(Kolicina) FROM Stavka AS s WHERE s.ProizvodID = p.IDProizvod) > 2000
434GO
435
436SELECT * FROM p4
437GO
438
439ALTER VIEW p4
440AS
441SELECT 
442	*,
443	(SELECT SUM(Kolicina) FROM Stavka AS s WHERE s.ProizvodID = p.IDProizvod) AS Prodano
444FROM Proizvod AS p
445WHERE (SELECT SUM(Kolicina) FROM Stavka AS s WHERE s.ProizvodID = p.IDProizvod) > 2000
446GO
447
448SELECT * FROM p4
449GO
450
451DROP VIEW p4
452GO
453
454-- Primjeri 12. Napravite pogled koji će dohvaćati sve iz tablice Kupac.
455-- Možete li napraviti INSERT, UPDATE i DELETE nekog kupca?
456CREATE VIEW p5
457AS
458SELECT * FROM Kupac
459GO
460
461SELECT * FROM p5
462
463INSERT INTO p5 (Ime, Prezime, Email, Telefon, GradID) VALUES ('Miro', 'Miri�', NULL, '042/111-222', 9)
464
465UPDATE p5 SET Email = 'miro@miro.com' WHERE IDKupac = 19993
466
467DELETE FROM p5 WHERE IDKupac = 19993
468GO
469
470-- Primjeri 13. Promijenite prethodni pogled tako da dohvaća sve stupce osim Prezime. 
471-- Možete li napraviti INSERT, UPDATE i DELETE nekog kupca? Obrišite pogled.
472ALTER VIEW p5
473AS
474SELECT IDKupac, Ime, Email, Telefon, GradID FROM Kupac
475GO
476
477INSERT INTO p5 (Ime, Email, Telefon, GradID) VALUES ('Janko', NULL, '042/222-333', 8)
478
479UPDATE p5 SET Email = 'ana@ana.com' WHERE IDKupac = 19992
480
481DELETE FROM p5 WHERE IDKupac = 19992
482GO
483
484DROP VIEW p5
485GO
486
487/* 14. Napravite pogled koji će dohvaćati ime i prezime kupca te
488       sve podatke o gradu.
489   	a. Možete li napraviti INSERT grada kroz pogled. Vidi li se kroz pogled?
490   	b. Možete li napraviti UPDATE ili DELETE grada kroz pogled?
491   	c. Obrišite pogled.
492*/
493CREATE VIEW p6
494AS
495SELECT 
496	k.Ime,
497	k.Prezime,
498	g.*
499FROM Kupac AS k
500INNER JOIN Grad AS g ON k.GradID = g.IDGrad
501GO
502
503SELECT * FROM p6
504
505INSERT INTO p6 (Naziv, DrzavaID) VALUES ('Velika Gorica', 1)
506
507SELECT * FROM p6
508SELECT * FROM Grad
509
510UPDATE p6 SET Naziv = 'Velika Gorica PROMIJENJENO!' WHERE IDGrad = 69
511
512DELETE FROM p6 WHERE IDGrad = 69
513GO
514
515DROP VIEW p6
516GO
517
518/* Primjeri 15, 16, 17.
51915. Napravite pogled koji će dohvaćati sve kupce iz Sarajeva.
520	Pomoću tog pogleda umetnite kupca iz Zagreba. Vidi li se
521	kupac kroz pogled?
52216. Promijenite pogled tako da ne dopušta umetanje/izmjenu
523	redaka koji neće biti vidljivi kroz njega. Probajte umetnuti
524	novog kupca.
52517. Obrišite pogled.
526*/
527CREATE VIEW p7
528AS
529SELECT * FROM Kupac WHERE GradID = 9
530GO
531
532SELECT * FROM p7
533
534INSERT INTO p7 (Ime, Prezime, Email, Telefon, GradID) VALUES ('Lana', 'Lani�', NULL, NULL, 1)
535
536SELECT * FROM p7
537GO
538
539ALTER VIEW p7
540AS
541SELECT * FROM Kupac WHERE GradID = 9
542WITH CHECK OPTION
543GO
544
545INSERT INTO p7 (Ime, Prezime, Email, Telefon, GradID) VALUES ('Vana', 'Vani�', NULL, NULL, 1)
546GO
547
548DROP VIEW p7
549GO
550
551/* Primjeri 18. Napravite tablicu Osoba sa stupcima IDOsoba, Ime, 
552	Prezime, OdjelID i Placa i umetnite nekoliko redaka.
553	a. Napravite pogled koji dohvaća sve iz tablice
554	b. Iskoristite pogled za dohvaćanje podataka
555	c. Uklonite stupac OdjelID iz tablice
556	d. Možete li iskoristiti pogled za dohvaćanje podataka?
557	e. Promijenite definiciju pogleda tako da bude čvrsto vezan uz objekte koje koristi
558	f. Uklonite stupac Placa iz tablice
559	g. Uklonite pogled
560*/
561CREATE TABLE Osoba
562(
563	IDOsoba int CONSTRAINT PK_Osoba PRIMARY KEY IDENTITY,
564	Ime nvarchar(50),
565	Prezime nvarchar(50),
566	OdjelID int,
567	Placa money 
568)
569GO
570INSERT INTO Osoba (Ime, Prezime, OdjelID, Placa) VALUES ('Miro', 'Miri�', 1, 5000)
571INSERT INTO Osoba (Ime, Prezime, OdjelID, Placa) VALUES ('Ana', 'Ani�', 1, 8500)
572INSERT INTO Osoba (Ime, Prezime, OdjelID, Placa) VALUES ('Juro', 'Juri�', 2, 3850)
573GO
574
575CREATE VIEW p8
576AS
577SELECT IDOsoba, Ime, Prezime, OdjelID, Placa FROM Osoba
578GO
579
580SELECT * FROM p8
581GO
582
583ALTER TABLE Osoba DROP COLUMN OdjelID
584GO
585
586SELECT * FROM p8
587GO
588
589ALTER VIEW p8 WITH SCHEMABINDING
590AS
591SELECT IDOsoba, Ime, Prezime, Placa FROM dbo.Osoba
592GO
593
594ALTER TABLE Osoba DROP COLUMN Placa
595GO
596
597DROP VIEW p8
598GO
599
600/* Primjeri 19. Napravite pogled koji dohvaća ime i prezime kupca, te
601	naziv države.
602	1. Pogledajte SELECT upit pogleda kroz sučelje i pomoću sistemske procedure sp_helptext 
603	2. Zaštitite pogled
604	3. Pogledajte SELECT upit pogleda kroz sučelje i pomoću sistemske procedure sp_helptext
605	4. Promijenite pogled tako da bude zaštićen i čvrsto vezan uz tablice
606	5. Promijenite pogled tako da bude zaštićen, čvrsto vezan uz tablice i da ne dopušta izmjene koje neće biti vidljive kroz pogled
607	6. Uklonite pogled 
608*/
609CREATE VIEW p9
610AS
611SELECT
612	k.Ime,
613	k.Prezime,
614	d.Naziv AS DrzavaNaziv
615FROM Kupac AS k
616INNER JOIN Grad AS g ON k.GradID = g.IDGrad
617INNER JOIN Drzava AS d ON d.IDDrzava = g.DrzavaID
618GO
619
620SELECT * FROM p9
621GO
622
623EXECUTE sp_helptext p9
624GO
625
626ALTER VIEW p9 WITH ENCRYPTION
627AS
628SELECT
629	k.Ime,
630	k.Prezime,
631	d.Naziv AS DrzavaNaziv
632FROM Kupac AS k
633INNER JOIN Grad AS g ON k.GradID = g.IDGrad
634INNER JOIN Drzava AS d ON d.IDDrzava = g.DrzavaID
635GO
636
637SELECT * FROM p9
638GO
639
640EXECUTE sp_helptext p9
641GO
642
643ALTER VIEW p9 WITH ENCRYPTION, SCHEMABINDING
644AS
645SELECT
646	k.Ime,
647	k.Prezime,
648	d.Naziv AS DrzavaNaziv
649FROM dbo.Kupac AS k
650INNER JOIN dbo.Grad AS g ON k.GradID = g.IDGrad
651INNER JOIN dbo.Drzava AS d ON d.IDDrzava = g.DrzavaID
652GO
653
654ALTER VIEW p9 WITH ENCRYPTION, SCHEMABINDING
655AS
656SELECT
657	k.Ime,
658	k.Prezime,
659	d.Naziv AS DrzavaNaziv
660FROM dbo.Kupac AS k
661INNER JOIN dbo.Grad AS g ON k.GradID = g.IDGrad
662INNER JOIN dbo.Drzava AS d ON d.IDDrzava = g.DrzavaID
663WITH CHECK OPTION
664GO
665
666SELECT * FROM p9
667GO
668
669DROP VIEW p9
670GO
671
672
673-- 1. Kad je napravljena baza AdventureWorksOBP?
674select * from sys.databases
675where name='AdventureWorksOBP'
676-- 2018-09-14 09:27:07.627
677
678
679--2. Ispišite sve obavezne stupce koji pripadaju tablici Kupac.
680select * from sys.columns
681where object_id=789577851
682-- IDKupac, Ime, Prezime
683
684select * from sys.tables
685where name='kupac'
686-- object_id=789577851
687
688select * from sys.columns
689where object_id=
690(
691	select object_id from sys.tables where name='kupac'
692)
693-- IDKupac, Ime, Prezime
694
695
696--3. Ispišite naziv stupca koji se najčešće pojavljuje 
697-- u bazi AdventureWorksOBP te ispišite i koliko je to puta.
698select name, count(*)
699from sys.columns
700group by name
701order by 2 desc
702-- 'status'
703
704
705--4. Ispišite sve primarne i UNIQUE ključeve na tablici Proizvod.
706select * from sys.tables
707where name='proizvod'
708-- Proizvod object_id=757577737
709
710select *
711from sys.key_constraints
712where parent_object_id=757577737
713-- PK_Proizvod
714
715
716--5. Ispišite nazive svih stranih ključeva u tablici Racun te napišite tablice kamo vode.
717select *
718from sys.tables
719-- Racun object_id=821577965
720
721select *
722from sys.foreign_keys as fk
723where fk.parent_object_id=821577965
724-- FK_Racun_Komercijalist vodi na referenced_object_id=629577281
725-- FK_Racun_KreditnaKartica vodi na referenced_object_id=597577167
726
727select * from sys.tables where object_id=629577281
728-- Komercijalist
729
730select * from sys.tables where object_id=597577167
731-- KreditnaKartica
732
733
734-- -- -- -- -- -- -- -- PREDAVANJE 4
735/*
736a. Na koliko stranica su smješteni podaci iz tablice dbo.Proizvod?
737b. Koji su redni brojevi tih stranica?
738c. Po kojem stupcu su poredani zapisi na stranicama?
739d. Pronađite slot na kojem je redak za IDProizvod = 858 (Half-Finger Gloves, S). Koja je boja tog proizvoda?
740e. Koji je zadnji redak na prvoj stranici?
741f. Koji je prvi redak na drugoj stranici?
742g. Umetnite redak u tablicu. Na koju stranicu je dodan i zašto? Pronađite ga.
743*/
744DBCC TRACEON(3604)
745GO
746
747DBCC IND('AdventureWorksOBP', 'Proizvod', -1)
748-- a) Podaci iz tablice dbo.Proizvod se smje�teni na 6 stranica (PageType = 1)
749-- b) Njihovi redni brojevi su 1836, 1848, 1850, 1851, 1852 i 1853.
750
751DBCC PAGE('AdventureWorksOBP', 1, 1836, 3) WITH TABLERESULTS
752-- c) Zapisi na stranicama su poredani po IDProizvod.
753
754DBCC PAGE('AdventureWorksOBP', 1, 1851, 3) WITH TABLERESULTS
755-- d) Slot za redak za IDProizvod = 858 (Half-Finger Gloves, S) je 78, boja proizvoda je crna.
756
757DBCC PAGE('AdventureWorksOBP', 1, 1836, 3) WITH TABLERESULTS
758-- e) Zadnji redak na prvoj stranici je IDProizvod = 425.
759
760DBCC PAGE('AdventureWorksOBP', 1, 1848, 3) WITH TABLERESULTS
761-- f) Prvi redak na drugoj stranici je IDProizvod = 426.
762
763-- g)
764INSERT INTO dbo.Proizvod (Naziv, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV, PotkategorijaID)
765VALUES ('Testni proizvod 1', 'TP-001', 'ljubi�asta', 0, 5.55, NULL)
766
767DBCC PAGE('AdventureWorksOBP', 1, 1853, 3) WITH TABLERESULTS
768-- Dodan je na zadnju stranicu, a to je 1853.
769
770
771-- -- -- -- -- -- -- -- PREDAVANJE 5
772/* Optimiziranje upita
7731. Optimizirajte upit: SELECT DatumIzdavanja FROM Racun
774    WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
775    a. Koliko stranica je pregledao RDBMS?
776    b. Napravite indeks koji optimizira upit
777    c. Koliko sad stranica pregled RDBMS?
778    d. Uklonite indeks
779
7802. Optimizirajte upit: SELECT IDRacun, DatumIzdavanja
781    FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
782
7833. Optimizirajte upit: SELECT IDRacun, BrojRacuna,
784    DatumIzdavanja FROM Racun WHERE DatumIzdavanja
785    BETWEEN '20010702' AND '20010702 23:59:59'
786    a. Pomaže li nam postojeći indeks? Zašto? Kako ćemo riješiti problem?
787    
7884. Optimizirajte upit: SELECT r.IDRacun,
789    SUM(s.Kolicina) AS Kolicina,
790    SUM(s.UkupnaCijena) AS UkupnaCijena
791    FROM Racun AS r
792    INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
793    WHERE r.DatumIzdavanja BETWEEN '20040126' AND '20040126 23:59:59' AND BrojRacuna LIKE 'SO6275%'
794    GROUP BY r.IDRacun
795*/
796
797SET STATISTICS IO ON
798
799-- Primjer 1.
800SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
801-- logical reads 202
802
803CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja)
804
805SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
806-- logical reads 2
807
808DROP INDEX ix1 ON Racun
809GO
810
811-- Primjer 2.
812SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
813-- logical reads 202
814
815CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja)
816
817SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
818-- logical reads 2
819
820DROP INDEX ix1 ON Racun
821GO
822
823-- Primjer 3.
824SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
825-- logical reads 202
826
827CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja)
828
829SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
830-- logical reads 10 - mora iæi na klasterirani indeks (key lookup) jer mu nedostaje vrijednost za BrojRacuna.
831
832DROP INDEX ix1 ON Racun
833
834CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)
835
836SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
837-- logical reads 2 - sad ne mora iæi na klasterirani indeks.
838
839DROP INDEX ix1 ON Racun
840GO
841
842-- Primjer 4.
843SELECT r.IDRacun, SUM(s.Kolicina) AS Kolicina, SUM(s.UkupnaCijena) AS UkupnaCijena
844FROM Racun AS r
845INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
846WHERE 
847	r.DatumIzdavanja BETWEEN '20040126' AND '20040126 23:59:59' 
848	AND BrojRacuna LIKE 'SO6275%'
849GROUP BY r.IDRacun
850-- Stavka: logical reads 847
851-- Racun: logical reads 202
852
853CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)
854
855CREATE NONCLUSTERED INDEX ix1 ON Stavka(RacunID) INCLUDE (Kolicina, UkupnaCijena)
856
857SELECT r.IDRacun, SUM(s.Kolicina) AS Kolicina, SUM(s.UkupnaCijena) AS UkupnaCijena
858FROM Racun AS r
859INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
860WHERE 
861	r.DatumIzdavanja BETWEEN '20040126' AND '20040126 23:59:59' 
862	AND BrojRacuna LIKE 'SO6275%'
863GROUP BY r.IDRacun
864-- Stavka: logical reads 18
865-- Racun: logical reads 3
866
867DROP INDEX ix1 ON Racun
868DROP INDEX ix1 ON Stavka
869GO
870
871/* primjer korisnosti indeksa */
872-- Dropnuti sve neklasterirane indekse s tablice Racun.
873CREATE FUNCTION GetNajnoviji
874(
875	@IDKupac int
876)
877RETURNS datetime
878AS
879BEGIN
880	DECLARE @Datum datetime
881
882	SELECT TOP 1 @Datum = DatumIzdavanja FROM Racun
883	WHERE KupacID = @IDKupac
884	ORDER BY DatumIzdavanja DESC
885	RETURN @Datum
886END
887GO
888
889SELECT 
890	IDKupac,
891	Ime,
892	Prezime,
893	dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
894FROM Kupac
895GO
896
897CREATE NONCLUSTERED INDEX ix2 ON Racun(KupacID) INCLUDE (DatumIzdavanja)
898GO
899
900
901-- -- -- -- -- -- -- -- PREDAVANJE 6
902-- Primjer 1. Deklarirajte varijable @Ime i @Prezime i dodijelite im
903-- neke vrijednosti. Prikažite dodijeljene vrijednosti.
904DECLARE @Ime nvarchar(50)
905DECLARE @Prezime nvarchar(50)
906
907SET @Ime = 'Miro'
908SET @Prezime = 'Miri�'
909
910PRINT @Ime
911PRINT @Prezime
912PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
913
914SELECT @Ime
915SELECT @Prezime
916SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
917GO
918
919-- Primjer 2. Deklarirajte varijable @Ime i @Prezime i dodijelite im vrijednosti 
920-- iz tablice Kupac za IDKupac jednak 16. Prikažite dodijeljene vrijednosti.
921DECLARE @Ime nvarchar(50)
922DECLARE @Prezime nvarchar(50)
923
924SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac WHERE IDKupac = 16
925
926SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
927GO
928
929-- Primjer 3. Deklarirajte varijable @Ime i @Prezime i dodijelite im vrijednosti 
930-- iz tablice Kupac tako da odaberete sve retke iz tablice. Prikažite dodijeljene vrijednosti.
931DECLARE @Ime nvarchar(50)
932DECLARE @Prezime nvarchar(50)
933
934SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac
935
936SELECT 'Korisnik: ' + @Ime + ' ' + @Prezime
937GO
938
939-- Naredba IF - ELSE IF - ELSE.
940DECLARE @Broj float
941SET @Broj = RAND()
942
943IF @Broj >= 0 AND @Broj < 0.33 BEGIN
944	SELECT 'Prva tre�ina'
945END
946ELSE IF @Broj >= 0.33 AND @Broj < 0.67 BEGIN
947	SELECT 'Druga tre�ina'
948END
949ELSE BEGIN
950	SELECT 'Tre�a tre�ina'
951END
952GO
953
954-- Primjer 4. Napišite proceduru koja dohvaća sve retke iz tablice
955-- Kupac. Pozovite proceduru. Promijenite proceduru tako da vraća rezultate 
956-- poredane po imenu pa po prezimenu. Uklonite proceduru.
957CREATE PROC dbo.DohvatiSveKupce
958AS
959SELECT * FROM Kupac
960GO
961
962EXEC dbo.DohvatiSveKupce
963GO
964
965ALTER PROCEDURE dbo.DohvatiSveKupce
966AS
967SELECT * FROM Kupac ORDER BY Ime, Prezime
968GO
969
970EXEC dbo.DohvatiSveKupce
971GO
972
973DROP PROC dbo.DohvatiSveKupce
974GO
975
976-- Primjer 5.Napišite proceduru koja dohvaća prvih 10 redaka iz tablice Proizvod, 
977-- prvih 5 redaka iz tablice KreditnaKartica i zadnja 3 retka iz tablice Racun. 
978-- Pozovite proceduru. Uklonite proceduru.
979CREATE PROC p5
980AS
981SELECT TOP 10 * FROM Proizvod
982SELECT TOP 5 * FROM KreditnaKartica
983SELECT TOP 3 * FROM Racun ORDER BY IDRacun DESC
984GO
985
986EXEC p5
987GO
988
989DROP PROC p5
990GO
991
992-- Primjer 6. Napišite proceduru koja prima @ID proizvoda i i vraća samo taj proizvod 
993-- iz tablice Proizvod. Pozovite proceduru na oba načina. Uklonite proceduru.
994CREATE PROC p6
995	@AjDiProizvoda int
996AS
997SELECT * FROM Proizvod WHERE IDProizvod = @AjDiProizvoda
998GO
999
1000EXEC p6 1
1001EXEC p6 @AjDiProizvoda = 1
1002GO
1003
1004DROP PROC p6
1005GO
1006
1007-- Primjer 7. Napišite proceduru koja prima dvije cijene i vraća nazive i
1008-- cijene svih proizvoda čija cijena je u zadanom rasponu.
1009-- Pozovite proceduru na oba načina. Uklonite proceduru.
1010CREATE PROC p7
1011	@Cijena1 money,
1012	@Cijena2 money
1013AS
1014SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV BETWEEN @Cijena1 AND @Cijena2
1015GO
1016
1017EXEC p7 500, 1000
1018EXEC p7 @Cijena1 = 500, @Cijena2 = 1000
1019EXEC p7 @Cijena2 = 1000, @Cijena1 = 500 -- Parametri nisu po redu.
1020GO
1021
1022DROP PROC p7
1023GO
1024
1025-- Primjer 8. Napišite proceduru koja prima četiri parametra potrebna za
1026-- unos nove kreditne kartice. Neka procedura napravi novi zapis u KreditnaKartica. 
1027-- Neka procedura prije i nakon umetanja dohvati broj zapisa u tablici. Pozovite 
1028-- proceduru na oba načina. Uklonite proceduru.
1029CREATE PROC p8
1030	@Tip nvarchar(50),
1031	@Broj nvarchar(25),
1032	@IstekMjesec tinyint,
1033	@IstekGodina smallint
1034AS
1035-- Dohvacanje prije.
1036SELECT COUNT(*) AS BrojPrije FROM dbo.KreditnaKartica 
1037
1038-- Umetanje.
1039INSERT INTO dbo.KreditnaKartica (Tip, Broj, IstekMjesec, IstekGodina)
1040VALUES (@Tip, @Broj, @IstekMjesec, @IstekGodina)
1041
1042-- Dohvacanje nakon.
1043SELECT COUNT(*) AS BrojNakon FROM dbo.KreditnaKartica 
1044GO
1045
1046EXEC p8 'American Express', '12345678901234', 11, 2013
1047EXEC p8 @Tip = 'Visa', @Broj = '99999999990000', @IstekMjesec = 5, @IstekGodina = 2014
1048GO
1049
1050DROP PROC p8
1051GO
1052
1053-- Primjer 9. Napišite proceduru koja prima tri boje i za svaku boju vraća
1054-- proizvode u njoj. Pozovite proceduru i nakon toga je uklonite.
1055CREATE PROC p9
1056	@Boja1 nvarchar(15),
1057	@Boja2 nvarchar(15),
1058	@Boja3 nvarchar(15)
1059AS
1060SELECT * FROM Proizvod WHERE Boja = @Boja1
1061SELECT * FROM Proizvod WHERE Boja = @Boja2
1062SELECT * FROM Proizvod WHERE Boja = @Boja3
1063GO
1064
1065EXEC p9 'Red', '�uta', 'Black'
1066GO
1067
1068DROP PROC p9
1069GO
1070
1071-- Primjer 10. Napišite proceduru koja prima parametre @IDProizvod i @Boja. 
1072-- Parametar @Boja neka bude izlazni parametar. Neka procedura za zadani proizvod 
1073-- vrati njegovu boju pomoću izlaznog parametra. Pozovite proceduru na oba načina i
1074-- ispišite vraćenu vrijednost. Uklonite proceduru.
1075CREATE PROC p10
1076	@IDProizvod int,
1077	@Boja nvarchar(15) OUTPUT
1078AS
1079SELECT @Boja = Boja FROM Proizvod WHERE IDProizvod = @IDProizvod
1080GO
1081
1082DECLARE @Farba nvarchar(15)
1083EXEC p10 320, @Farba OUTPUT
1084PRINT @Farba
1085GO
1086
1087DECLARE @Farba nvarchar(15)
1088EXEC p10 @IDProizvod = 320, @Boja = @Farba OUTPUT
1089PRINT @Farba
1090GO
1091
1092DROP PROC p10
1093GO
1094
1095-- Primjer 11. Napišite proceduru koja prima kriterij po kojemu ćete filtrirati
1096-- prezimena iz tablice Kupac. Neka procedura pomoću izlaznog parametra vrati broj 
1097-- zapisa koji zadovoljavaju zadani kriterij. Neka procedura vrati i sve zapise koji 
1098-- zadovoljavaju kriterij. Pozovite proceduru i ispišite vraćenu vrijednost. Uklonite proceduru.
1099CREATE PROC p11
1100	@Filter nvarchar(50),
1101	@BrojZapisa int OUTPUT
1102AS
1103SELECT @BrojZapisa = COUNT(*) FROM Kupac WHERE Prezime LIKE @Filter
1104
1105SELECT * FROM Kupac WHERE Prezime LIKE @Filter
1106GO
1107
1108DECLARE @Cnt int
1109EXEC p11 '%abe%', @Cnt OUTPUT
1110PRINT @Cnt
1111GO
1112
1113DROP PROC p11
1114GO
1115
1116-- Primjer 12. Napišite proceduru koja za zadanog komercijalistu pomoću izlaznih 
1117-- parametara vraća njegovo ime i prezime te ukupnu količinu izdanih računa.
1118CREATE PROC p12
1119	@KomercijalistID int,
1120	@Ime nvarchar(50) OUTPUT,
1121	@Prezime nvarchar(50) OUTPUT,
1122	@IzdaoRacuna int OUTPUT
1123AS
1124SELECT 
1125	@Ime = k.Ime, 
1126	@Prezime = k.Prezime, 
1127	@IzdaoRacuna = COUNT(*)
1128FROM Komercijalist AS k
1129INNER JOIN Racun AS r ON k.IDKomercijalist = r.KomercijalistID
1130WHERE k.IDKomercijalist = @KomercijalistID
1131GROUP BY k.Ime, k.Prezime
1132GO
1133
1134DECLARE @Ime nvarchar(50), @Prezime nvarchar(50), @IzdaoRacuna int
1135EXEC p12 276, @Ime OUTPUT, @Prezime OUTPUT, @IzdaoRacuna OUTPUT
1136SELECT @Ime
1137SELECT @Prezime
1138SELECT @IzdaoRacuna
1139GO
1140
1141DROP PROC p12
1142GO
1143
1144-- Primjer 13. Napišite proceduru koja prima ime i prezime kupca i vraća 0
1145-- kao RETURN parametar ako kupac postoji u tablici, odnosno 200 ako kupac ne 
1146-- postoji. Pozovite proceduru i ispišite RETURN vrijednost.
1147CREATE PROC p13
1148	@Ime nvarchar(50),
1149	@Prezime nvarchar(50)
1150AS
1151IF EXISTS(SELECT * FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime)
1152	RETURN 0
1153ELSE
1154	RETURN 200
1155GO
1156
1157DECLARE @RetVal int
1158EXEC @RetVal = p13 'Amy', 'Alberts'
1159PRINT @RetVal
1160GO
1161
1162DECLARE @RetVal int
1163EXEC @RetVal = p13 'Pa�ko', 'Patak'
1164PRINT @RetVal
1165GO
1166
1167-- Primjer 14. Promijenite proceduru iz prethodnog zadatka tako da bude zaštićena.
1168ALTER PROC p13
1169	@Ime nvarchar(50),
1170	@Prezime nvarchar(50)
1171WITH ENCRYPTION
1172AS
1173IF EXISTS(SELECT * FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime)
1174	RETURN 0
1175ELSE
1176	RETURN 200
1177GO
1178
1179-- Primjer 15. Uklonite proceduru.
1180DROP PROC p13
1181GO
1182
1183-- Primjer 16. Napravite tablicu Student koja se sastoji od stupaca IDStudent, Ime, 
1184-- Prezime i JMBAG i umetnite neke podatke. Napišite proceduru koja vraća ime i prezime 
1185-- iz te tablice i pozovite je.
1186CREATE TABLE Student
1187(
1188	IDStudent int CONSTRAINT PK_Student PRIMARY KEY IDENTITY,
1189	Ime nvarchar(50),
1190	Prezime nvarchar(50),
1191	JMBAG char(11)
1192)
1193GO
1194
1195INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Miro', 'Miri�', '111222333')
1196INSERT INTO Student (Ime, Prezime, JMBAG) VALUES ('Ana', 'Ani�', '999888777')
1197GO
1198
1199CREATE PROC p16
1200AS
1201SELECT Ime, Prezime FROM Student
1202GO
1203
1204EXEC p16
1205GO
1206
1207-- Primjer 17. Promijenite proceduru tako da uz ime i prezime vraća i datum rođenja. Što se desilo?
1208ALTER PROC p16
1209AS
1210SELECT Ime, Prezime, DatumRodjenja FROM Student
1211GO
1212
1213-- Primjer 18. Promijenite proceduru tako da vraća sve zapise iz tablice
1214-- IzmisljenaTablica. Å to se desilo? Pokrenite proceduru. Å to se desilo?
1215ALTER PROC p16
1216AS
1217SELECT * FROM IzmisljenaTablica
1218GO
1219
1220EXEC p16
1221GO
1222
1223-- Primjer 19. Napravite tablicu IzmisljenaTablica i pokrenite proceduru. Å to se desilo?
1224CREATE TABLE IzmisljenaTablica
1225(
1226	Naziv nvarchar(50)
1227)
1228GO
1229
1230EXEC p16
1231GO
1232
1233
1234-- CRUD.
1235
1236-- Primjer 20. Svaka operacija posebno. Napravite procedure koje rade CRUD operacije 
1237-- na tablici Student tako da svakoj operaciji dodijelite posebnu proceduru. 
1238-- Iskoristite procedure za umetanje, izmjenu, dohvaćanje i brisanje zapisa.
1239CREATE PROC InsertStudent
1240	@IDStudent int OUTPUT, 
1241	@Ime nvarchar(50), 
1242	@Prezime nvarchar(50), 
1243	@JMBAG char(11)
1244AS
1245INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
1246SET @IDStudent = SCOPE_IDENTITY()
1247GO
1248
1249CREATE PROC UpdateStudent
1250	@IDStudent int, 
1251	@Ime nvarchar(50), 
1252	@Prezime nvarchar(50), 
1253	@JMBAG char(11)
1254AS
1255UPDATE Student 
1256SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
1257WHERE IDStudent = @IDStudent
1258GO
1259
1260CREATE PROC DeleteStudent
1261	@IDStudent int
1262AS
1263DELETE FROM Student WHERE IDStudent = @IDStudent
1264GO
1265
1266CREATE PROC GetStudent
1267	@IDStudent int
1268AS
1269SELECT * FROM Student WHERE IDStudent = @IDStudent
1270GO
1271
1272EXEC GetStudent 1
1273
1274DECLARE @NoviIDStudenta int
1275EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT, @Ime = 'Ana', @Prezime = 'Ani�', @JMBAG = '11224451253'
1276SELECT @NoviIDStudenta
1277
1278EXEC GetStudent 1
1279
1280EXEC UpdateStudent 1, 'Ana', 'Ani� Miri�', '11224451253'
1281
1282EXEC GetStudent 1
1283
1284EXEC DeleteStudent 1
1285
1286EXEC GetStudent 1
1287GO
1288
1289-- Primjer 21. INSERT/UPDATE zajedno, ostalo posebno. Napravite procedure koje rade 
1290-- CRUD operacije na tablici Student tako da operacije umetanja i izmjene obavite u
1291-- jednoj proceduri, a druge dvije operacije obavite u posebnim procedurama. Iskoristite 
1292-- procedure za umetanje, izmjenu, dohvaćanje i brisanje zapisa.
1293CREATE PROC MergeStudent
1294	@IDStudent int OUTPUT, 
1295	@Ime nvarchar(50), 
1296	@Prezime nvarchar(50), 
1297	@JMBAG char(11)
1298AS
1299IF EXISTS(SELECT * FROM Student WHERE IDStudent = @IDStudent)
1300	UPDATE Student 
1301	SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
1302	WHERE IDStudent = @IDStudent
1303ELSE
1304	INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
1305	SET @IDStudent = SCOPE_IDENTITY()
1306GO
1307
1308CREATE PROC DeleteStudent
1309	@IDStudent int
1310AS
1311DELETE FROM Student WHERE IDStudent = @IDStudent
1312GO
1313
1314CREATE PROC GetStudent
1315	@IDStudent int
1316AS
1317SELECT * FROM Student WHERE IDStudent = @IDStudent
1318GO
1319
1320EXEC GetStudent 1
1321
1322DECLARE @NoviIDStudenta int
1323EXEC MergeStudent @NoviIDStudenta OUTPUT, 'Ana', 'Ani�', '11224451253'
1324PRINT @NoviIDStudenta
1325
1326EXEC GetStudent 2
1327
1328EXEC MergeStudent 2, 'Ana', 'Ani� Miri�', '11224451253'
1329
1330EXEC GetStudent 2
1331
1332EXEC DeleteStudent 2
1333
1334EXEC GetStudent 2
1335GO
1336
1337-- Primjer 22. UPDATE/INSERT/DELETE zajedno. Napravite procedure koje rade CRUD 
1338-- operacije na tablici Student tako da operacije umetanja, izmjene i brisanja obavite 
1339-- u jednoj proceduri, a dohvaćanje u drugoj. Iskoristite procedure za umetanje, izmjenu, 
1340-- dohvaćanje i brisanje zapisa.
1341CREATE PROC ChangeStudent
1342	@Operacija char(1),
1343	@IDStudent int OUTPUT, 
1344	@Ime nvarchar(50), 
1345	@Prezime nvarchar(50), 
1346	@JMBAG char(11)
1347AS
1348IF @Operacija = 'U'
1349	UPDATE Student 
1350	SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
1351	WHERE IDStudent = @IDStudent
1352ELSE IF @Operacija = 'I' BEGIN
1353	INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
1354	SET @IDStudent = SCOPE_IDENTITY()
1355END
1356ELSE IF @Operacija = 'D'
1357	DELETE FROM Student WHERE IDStudent = @IDStudent
1358GO
1359
1360CREATE PROC GetStudent
1361	@IDStudent int
1362AS
1363SELECT * FROM Student WHERE IDStudent = @IDStudent
1364GO
1365
1366EXEC GetStudent 3
1367
1368DECLARE @NoviIDStudenta int
1369EXEC ChangeStudent 'I', @NoviIDStudenta OUTPUT, 'Ana', 'Ani�', '11224451253'
1370PRINT @NoviIDStudenta
1371
1372EXEC GetStudent 3
1373
1374EXEC ChangeStudent 'U', 3, 'Ana', 'Ani� Miri�', '11224451253'
1375
1376EXEC GetStudent 3
1377
1378EXEC ChangeStudent 'D', 3, null, null, null
1379
1380EXEC GetStudent 3
1381GO
1382
1383
1384
1385-- -- -- -- -- -- -- -- PREDAVANJE 7
1386-- 1. Dohvatite sve podatke za račune iz 2003, s tim da datum
1387-- izdavanja formatirate na hrvatski način.
1388SELECT	
1389	IDRacun, 
1390	CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja, 
1391	BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
1392FROM Racun AS r
1393WHERE YEAR(DatumIzdavanja) = 2003
1394
1395-- 2. Dohvatite nazive svih proizvoda i uz svaki naziv u zagradi ispišite i njegov ID.
1396SELECT Naziv + ' (' + CAST(IDProizvod AS nvarchar(20)) + ')' AS Naziv
1397FROM Proizvod
1398
1399-- ili
1400
1401SELECT Naziv + ' (' + CONVERT(nvarchar(50), IDProizvod) + ')' AS Naziv
1402FROM Proizvod
1403
1404/* 3. Dohvatite sve proizvode i pri tome dodajte još jedan
1405stupac u kojem ćete ispisati:
1406o "Jeftino" ako je cijena manja od 100
1407o "Srednje skupo" ako je cijena između 100 i 1000
1408o "Skupo" ako je cijena veća od 1000 */
1409SELECT *,
1410	CASE
1411		WHEN CijenaBezPDV < 100 THEN 'Jeftino'
1412		WHEN CijenaBezPDV BETWEEN 100 AND 1000 THEN 'Srednje skupo'
1413		ELSE 'Skupo'
1414	END AS CijenaKomentar
1415FROM Proizvod
1416
1417-- 4. Dohvatite naziv i boju svih proizvoda. Za boju koja je NULL ispisati "Nije definirana".
1418SELECT Naziv, 
1419	CASE
1420		WHEN Boja IS NULL THEN 'Nedefinirana'
1421		ELSE Boja
1422	END AS Boja
1423FROM Proizvod
1424
1425-- ili 
1426
1427SELECT Naziv, ISNULL(Boja, 'Nedefinirana') AS Boja
1428FROM Proizvod
1429
1430-- 5. Deklarirajte varijable @a i @b i dajte im vrijednosti 51 i 0. Unutar TRY bloka 
1431-- izračunajte @a / @b. Unutar CATCH bloka ispišite informacije o greški koja se desila.
1432DECLARE @a int = 51
1433DECLARE @b int = 0
1434
1435BEGIN TRY
1436	PRINT @a / @b
1437END TRY
1438BEGIN CATCH
1439	PRINT ERROR_MESSAGE()
1440	PRINT ERROR_NUMBER()
1441	PRINT ERROR_SEVERITY()
1442	PRINT ERROR_LINE()
1443	PRINT ERROR_PROCEDURE()
1444END CATCH
1445GO
1446
1447/* 6. Napravite tablicu Vrsta koja ima stupce IDVrsta (primarni ključ, ali nije IDENTITY) 
1448    i Naziv. Napravite proceduru koja prima IDVrsta i Naziv i umeće ih u tablicu. Pozovite
1449    proceduru dva puta s vrijednostima 1 i "Pingvin".
1450    o Implementirajte TRY/CATCH izvan procedure i pozovite je.
1451    o Implementirajte TRY/CATCH unutar procedure i pozovite je.
1452*/
1453CREATE TABLE Vrsta
1454(
1455	IDVrsta int CONSTRAINT PK_Vrsta PRIMARY KEY,
1456	Naziv nvarchar(50)
1457)
1458GO
1459
1460CREATE PROC InsertVrsta
1461	@IDVrsta int,
1462	@Naziv nvarchar(50)
1463AS
1464INSERT INTO Vrsta (IDVrsta, Naziv) VALUES (@IDVrsta, @Naziv)
1465GO
1466
1467EXEC InsertVrsta 1, 'Pingvin'
1468EXEC InsertVrsta 1, 'Pingvin'
1469GO
1470
1471BEGIN TRY
1472	EXEC InsertVrsta 2, 'Slon'
1473	EXEC InsertVrsta 2, 'Slon'
1474END TRY
1475BEGIN CATCH
1476	PRINT ERROR_MESSAGE()
1477	PRINT ERROR_NUMBER()
1478	PRINT ERROR_SEVERITY()
1479	PRINT ERROR_LINE()
1480	PRINT ERROR_PROCEDURE()
1481END CATCH
1482GO
1483
1484ALTER PROC InsertVrsta
1485	@IDVrsta int,
1486	@Naziv nvarchar(50)
1487AS
1488BEGIN TRY
1489	INSERT INTO Vrsta (IDVrsta, Naziv) VALUES (@IDVrsta, @Naziv)
1490END TRY
1491BEGIN CATCH
1492	PRINT 'Desila se gre�ka: ' + ERROR_MESSAGE()
1493	PRINT 'Vrsta nije upisana.'
1494END CATCH
1495GO
1496
1497EXEC InsertVrsta 3, 'Ovca'
1498EXEC InsertVrsta 3, 'Ovca'
1499GO
1500
1501-- 7. Napišite funkciju koja prima ID proizvoda i dohvaća broj prodanih primjeraka. 
1502-- Pozovite funkciju samostalno. Dohvatite sve nazive i boje i uz svaki proizvod ispišite
1503-- koliko primjeraka je prodano. Promijenite funkciju tako da vrati 0 za one proizvode 
1504-- koji nisu prodani niti u jednom primjerku. Ako treba, optimizirajte. Uklonite funkciju.
1505CREATE FUNCTION UkupnaKolicina
1506(
1507	@IDProizvod int
1508)
1509RETURNS int
1510AS
1511BEGIN
1512	DECLARE @Ukupno int
1513
1514	SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @IDProizvod
1515
1516	RETURN @Ukupno
1517END
1518GO
1519
1520DECLARE @Rez int
1521SET @Rez = dbo.UkupnaKolicina(776)
1522SELECT @Rez
1523
1524SELECT
1525	Naziv,
1526	Boja,
1527	dbo.UkupnaKolicina(IDProizvod) AS Prodano
1528FROM Proizvod
1529GO
1530
1531ALTER FUNCTION UkupnaKolicina
1532(
1533	@IDProizvod int
1534)
1535RETURNS int
1536AS
1537BEGIN
1538	DECLARE @Ukupno int
1539
1540	SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @IDProizvod
1541
1542	RETURN 
1543		CASE
1544			WHEN @Ukupno IS NOT NULL THEN @Ukupno
1545			ELSE 0
1546		END
1547END
1548GO
1549
1550SELECT
1551	Naziv,
1552	Boja,
1553	dbo.UkupnaKolicina(IDProizvod) AS Prodano
1554FROM Proizvod
1555GO
1556
1557CREATE INDEX ix1 ON Stavka (ProizvodID) INCLUDE (Kolicina)
1558
1559SELECT
1560	Naziv,
1561	Boja,
1562	dbo.UkupnaKolicina(IDProizvod) AS Prodano
1563FROM Proizvod
1564GO
1565
1566-- 8. Napišite funkciju koja prima string. Ako je broj znakova u stringu <= 10, 
1567-- neka funkcija vrati ulazni string. Ako ne, neka vrati prvih 7 znakova i iza toga 
1568-- tri točke. Ispišite nazive svih proizvoda koristeći napravljenu funkciju.
1569CREATE FUNCTION Skrati
1570(
1571	@s nvarchar(max)
1572)
1573RETURNS nvarchar(10)
1574AS
1575BEGIN
1576	RETURN CASE
1577		WHEN LEN(@s) <= 10 THEN @s
1578		ELSE SUBSTRING(@s, 1, 7) + '...'
1579	END
1580END
1581GO
1582
1583SELECT dbo.Skrati('Zvonko')
1584SELECT dbo.Skrati('Zvonko Telefonko')
1585
1586SELECT 
1587	Naziv,
1588	dbo.Skrati(Naziv) AS NazivSkraceni
1589FROM Proizvod
1590GO
1591
1592-- 9. Napišite funkciju koja za zadanog kupca vraća datum najnovije kupovine. 
1593-- Ispišite sve kupce i kraj svakog ispišite datum najnovije kupovine. Ako treba, optimizirajte.
1594CREATE FUNCTION GetNajnoviji
1595(
1596	@IDKupac int
1597)
1598RETURNS datetime
1599AS
1600BEGIN
1601	DECLARE @Datum datetime
1602	
1603	SELECT TOP 1 @Datum = DatumIzdavanja FROM Racun
1604	WHERE KupacID = @IDKupac
1605	ORDER BY DatumIzdavanja DESC
1606	
1607	RETURN @Datum
1608END
1609GO
1610
1611SELECT 
1612	IDKupac,
1613	dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
1614FROM Kupac
1615GO
1616
1617CREATE NONCLUSTERED INDEX ix2 ON Racun(KupacID) INCLUDE (DatumIzdavanja)
1618GO
1619
1620SELECT 
1621	IDKupac,
1622	dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
1623FROM Kupac
1624GO
1625
1626-- 10. Napišite jednostavnu tabličnu funkciju koja vraća IDKupac, ime i prezime 
1627-- svih osoba čije prezime započinje sa zadanim stringom. Iskoristite funkciju za 
1628-- dohvat svih osoba čije prezime započinje sa 'Ac'. Uz svaku osobu dohvatite i njegove narudžbe.
1629CREATE FUNCTION DohvatiOsobe
1630(
1631    @PrezimeLike nvarchar(50)
1632)
1633RETURNS TABLE
1634AS
1635RETURN
1636    SELECT IDKupac, Ime, Prezime
1637    FROM Kupac
1638    WHERE Prezime LIKE @PrezimeLike + '%'
1639GO
1640
1641SELECT * FROM DohvatiOsobe('Ac')
1642GO
1643
1644SELECT * 
1645FROM DohvatiOsobe('Ac') AS o
1646INNER JOIN Racun AS r ON o.IDKupac = r.KupacID
1647GO
1648
1649-- 11. Napišite jednostavnu tabličnu funkciju koja prima dva datuma. Neka funkcija 
1650-- vrati sve račune koji su izrađeni između tih datuma. Iskoristite funkciju za dohvat 
1651-- računa između 11.11.2002. i 15.03.2003. 
1652-- + Promijenite funkciju tako da datum izdavanja vrati u hrvatskom formatu.
1653CREATE FUNCTION DohvatiRacuneIzmedju
1654(
1655    @D1 datetime, 
1656    @D2 datetime
1657)
1658RETURNS TABLE
1659AS
1660RETURN
1661	SELECT *
1662	FROM Racun AS r
1663	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
1664GO
1665
1666SELECT * FROM DohvatiRacuneIzmedju('20021111', '20030315')
1667GO
1668
1669ALTER FUNCTION DohvatiRacuneIzmedju
1670(
1671    @D1 datetime, 
1672    @D2 datetime
1673)
1674RETURNS TABLE
1675AS
1676RETURN
1677	SELECT 
1678		IDRacun, 
1679		CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja, 
1680		BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
1681	FROM Racun AS r
1682	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
1683GO
1684
1685SELECT * FROM DohvatiRacuneIzmedju('20021111', '20030315')
1686GO
1687
1688-- 12. Napišite i iskoristitie funkciju jednaku onoj iz zadatka 11, ali neka ova bude složena. 
1689CREATE FUNCTION DohvatiRacuneIzmedjuSlozena
1690(
1691    @D1 datetime, 
1692    @D2 datetime
1693)
1694RETURNS @rez TABLE
1695(
1696	IDRacun int, 
1697	DatumIzdavanja datetime, 
1698	BrojRacuna nvarchar(25), 
1699	KupacID int, 
1700	KomercijalistID int, 
1701	KreditnaKarticaID int, 
1702	Komentar nvarchar(128)	
1703)
1704AS
1705BEGIN
1706	
1707	INSERT INTO @rez (IDRacun, DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar)
1708	SELECT IDRacun, DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar
1709	FROM Racun AS r
1710	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
1711	
1712	RETURN
1713END
1714GO
1715
1716SELECT * FROM DohvatiRacuneIzmedjuSlozena('20021111', '20030315')
1717GO
1718
1719-- 13. Napišite složenu tabličnu funkciju koja prima cijenu. Ako je cijena NULL, 
1720-- vratite nazive i cijene svih proizvoda iz tablice Proizvod. Ako nije, vratite 
1721-- nazive i cijene samo onih proizvoda čija cijena je veća od zadane cijene.
1722-- Iskoristite funkciju s NULL i s nekom cijenom.
1723CREATE FUNCTION Slozena13
1724(
1725	@Cijena money
1726)
1727RETURNS @rez TABLE ( Naziv nvarchar(50), Cijena money )
1728AS
1729BEGIN
1730	IF @Cijena IS NULL BEGIN
1731		INSERT INTO @rez (Naziv, Cijena)
1732		SELECT Naziv, CijenaBezPDV FROM Proizvod
1733	END
1734	ELSE BEGIN
1735		INSERT INTO @rez (Naziv, Cijena)
1736		SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV > @Cijena
1737	END
1738	
1739	RETURN
1740END
1741GO
1742
1743SELECT * FROM Slozena13(NULL)
1744SELECT * FROM Slozena13(2300)
1745GO
1746
1747-- 14. Napravite tablicu Student koja se sastoji od IDStudent, Ime, Prezime i JMBAG. 
1748-- Napravite procedure koje rade CRUD operacije INSERT, UPDATE i DELETE na tablici Student. 
1749-- Svakoj operaciji dodijelite posebnu proceduru. Operaciju SELECT implementirajte 
1750-- tabličnom funkcijom. Iskoristite procedure i funkcije za umetanje, izmjenu, 
1751-- dohvaćanje i brisanje zapisa.
1752CREATE TABLE Student
1753(
1754	IDStudent int CONSTRAINT PK_Student PRIMARY KEY IDENTITY,
1755	Ime nvarchar(50),
1756	Prezime nvarchar(50),
1757	JMBAG char(11)
1758)
1759GO
1760
1761CREATE PROC InsertStudent
1762	@IDStudent int OUTPUT, 
1763	@Ime nvarchar(50), 
1764	@Prezime nvarchar(50), 
1765	@JMBAG char(11)
1766AS
1767INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
1768SET @IDStudent = SCOPE_IDENTITY()
1769GO
1770
1771CREATE PROC UpdateStudent
1772	@IDStudent int, 
1773	@Ime nvarchar(50), 
1774	@Prezime nvarchar(50), 
1775	@JMBAG char(11)
1776AS
1777UPDATE Student 
1778SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
1779WHERE IDStudent = @IDStudent
1780GO
1781
1782CREATE PROC DeleteStudent
1783	@IDStudent int
1784AS
1785DELETE FROM Student WHERE IDStudent = @IDStudent
1786GO
1787
1788CREATE FUNCTION dbo.GetStudent
1789(
1790    @IDStudent int
1791)
1792RETURNS TABLE
1793AS
1794    RETURN SELECT * FROM Student WHERE IDStudent = @IDStudent
1795GO
1796
1797SELECT * FROM GetStudent(1)
1798
1799DECLARE @NoviIDStudenta int
1800EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT, @Ime = 'Ana', @Prezime = 'Ani�', @JMBAG = '11224451253'
1801PRINT @NoviIDStudenta
1802
1803SELECT * FROM GetStudent(1)
1804
1805EXEC UpdateStudent 1, 'Ana', 'Anic Miric', '11224451253'
1806
1807SELECT * FROM GetStudent(1)
1808
1809EXEC DeleteStudent 1
1810
1811SELECT * FROM GetStudent(1)
1812GO
1813
1814
1815
1816/*
1817=========================================================
1818=========================Vjezbe==========================
1819=========================================================
1820*/
1821
1822---------------- Vjezba 1 
1823-- 1a. Napisati skriptu koja �e implementirati sljede�i model (dan u vje�bi)
1824-- kreiranje i prebacivanje na bazu
1825CREATE DATABASE RentACar
1826GO
1827
1828USE RentACar
1829GO
1830
1831-- kreiranje tablica
1832-- prvo idu maticne talice (ne sadrze u sebi FK-eve)
1833
1834-- Proizvodac
1835CREATE TABLE Proizvodjac
1836(
1837	IDProizvodjac int CONSTRAINT PK_Proizvodjac PRIMARY KEY IDENTITY,
1838	Naziv nvarchar(50) NOT NULL
1839)
1840GO
1841
1842-- Kategorija
1843CREATE TABLE Kategorija
1844(
1845	IDKategorija int CONSTRAINT PK_Kategorija PRIMARY KEY IDENTITY,
1846	Naziv nvarchar(50) NOT NULL
1847)
1848GO
1849
1850-- Status vozila
1851CREATE TABLE StatusVozila
1852(
1853	IDStatusVozila int CONSTRAINT PK_StatusVozila PRIMARY KEY IDENTITY,
1854	Naziv nvarchar(50) NOT NULL
1855)
1856GO
1857
1858-- Klijent
1859CREATE TABLE Klijent
1860(
1861	IDKlijent int CONSTRAINT PK_Klijent PRIMARY KEY IDENTITY,
1862	Ime nvarchar(50) NOT NULL,
1863	Prezime nvarchar(50) NOT NULL,
1864	EMail nvarchar(50) NULL,
1865	Telefon nvarchar(50) NULL,
1866	Adresa nvarchar(50) NOT NULL,
1867	Grad nvarchar(50) NOT NULL,
1868	Drzava nvarchar(50) NOT NULL
1869)
1870GO
1871
1872--------------------------------
1873-- kreiranje tablica
1874-- kao drugu stvar kreiramo tablice koje se na njih vezu (sadrze strane kljuceve)
1875-- kreiranje ide slijedno - prvo vezne tablice koje se vezu na maticne, zatim tablice 
1876-- koje se vezu na prve vezne tablice itd.
1877
1878-- Model -> veze se na Proizvodaca
1879CREATE TABLE Model
1880(
1881	IDModel int CONSTRAINT PK_Model PRIMARY KEY IDENTITY,
1882	Naziv nvarchar(50) NOT NULL,
1883	IDProizvodjac int CONSTRAINT FK_Model_Proizvodjac 
1884		FOREIGN KEY REFERENCES Proizvodjac(IDProizvodjac) NULL,
1885)
1886GO
1887
1888-- slijedece vezne tablice - vezu se na maticne i na vezne
1889-- tablice napravljene u prethodnoj rundi
1890
1891-- Vozilo -> veze se na Kategorija (maticna tablica) i na Model
1892-- (vezna tablica 1. runde)
1893CREATE TABLE Vozilo
1894(
1895	IDVozilo int CONSTRAINT PK_Vozilo PRIMARY KEY IDENTITY,
1896	Registracija nvarchar(20) NOT NULL,
1897	IDModel int CONSTRAINT FK_Vozilo_Model 
1898		FOREIGN KEY REFERENCES Model(IDModel) NOT NULL,
1899	IDKategorija int CONSTRAINT FK_Vozilo_Kategorija 
1900		FOREIGN KEY REFERENCES Kategorija(IDKategorija) NOT NULL,
1901	Kilometraza int NOT NULL,
1902	CijenaPodDanu money NOT NULL
1903)
1904GO
1905
1906-- Najam -> Veze sa na Status vozila i na Klijent (maticne tablice)
1907-- te na Vozilo (vezna tablica 2. runde)
1908CREATE TABLE Najam
1909(
1910	IDNajam int CONSTRAINT PK_Najam PRIMARY KEY IDENTITY,
1911	IDStatusVozila int CONSTRAINT FK_Najam_StatusVozila
1912		FOREIGN KEY REFERENCES StatusVozila(IDStatusVozila) NOT NULL,
1913	IDKlijent int CONSTRAINT FK_Najam_Klijent
1914		FOREIGN KEY REFERENCES Klijent(IDKlijent) NOT NULL,
1915	IDVozilo int CONSTRAINT FK_Najam_Vozilo
1916		FOREIGN KEY REFERENCES Vozilo(IDVozilo) NOT NULL,
1917	DatumOd datetime NOT NULL,
1918	DatumDo datetime NULL
1919)
1920GO
1921
1922
1923/*
1924Bazu napuniti sljede�im podacima:
1925Proizvo�a�i: Opel, Honda, Volkswagen
1926Modeli: Astra, Vectra, Civic, Legend, Polo, Golf, Passat
1927Kategorije: �Benzinski motor�, �Dizelski motor�
1928Vozila: 
1929Po 1 vozilo svakog modela s benzinskim motorom
1930Svakog Opela dodati i s dizelskim motorom
1931Klijenti: Iva Ivi�, Maja Maji�, Miro Miri�
1932StatusVozila: �Pla�eno�, �Preuzeto�, �Vra�eno�
1933Najam: upi�ite da je svaki klijent iznajmio po 2 vozila, s podacima po �elji (jednog je vratio, jedan je preuzet)
1934*/
1935
1936-- Vje�ba 1b.
1937
1938-- Proizvo�a�i: Opel, Honda, Volkswagen
1939INSERT INTO Proizvodjac (Naziv) VALUES ('Opel'), ('Honda'), ('Volkswagen'), ('Fiat')
1940GO
1941
1942-- Modeli: Astra, Vectra, Civic, Legend, Polo, Golf, Passat
1943INSERT INTO Model(Naziv, IDProizvodjac) VALUES ('Astra', 1), ('Vectra', 1), 
1944('Civic', 2), ('Legend', 2), ('Polo', 3), ('Golf', 3), ('Passat', 3), ('C5', null)
1945GO
1946
1947-- Kategorije: �Benzinski motor�, �Dizelski motor�
1948INSERT INTO Kategorija (Naziv) VALUES ('Benzinski motor'), ('Dizelski motor')
1949GO
1950
1951-- Vozila: 
1952-- Po 1 vozilo svakog modela s benzinskim motorom
1953INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1954VALUES ('ZG111A', 1, 1, 25000, 550.00)
1955GO
1956
1957INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1958VALUES ('ZG222A', 2, 1, 35000, 530.00)
1959GO
1960
1961INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1962VALUES ('ZG333A', 3, 1, 15000, 650.00)
1963GO
1964
1965INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1966VALUES ('ZG444A', 4, 1, 25000, 950.00)
1967GO
1968
1969INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1970VALUES ('ZG555A', 5, 1, 125000, 250.00)
1971GO
1972
1973INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1974VALUES ('ZG666A', 6, 1, 75000, 450.00)
1975GO
1976
1977INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1978VALUES ('ZG777A', 7, 1, 5000, 800.00)
1979GO
1980
1981-- Svakog Opela dodati i s dizelskim motorom
1982INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1983VALUES ('ZG555B', 5, 2, 45000, 200.00)
1984GO
1985
1986INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1987VALUES ('ZG666B', 6, 2, 35000, 400.00)
1988GO
1989
1990INSERT INTO Vozilo (Registracija, IDModel, IDKategorija, Kilometraza, CijenaPodDanu)
1991VALUES ('ZG777B', 7, 2, 6000, 750.00)
1992GO
1993
1994-- Klijenti: Iva Ivi�, Maja Maji�, Miro Miri�
1995INSERT INTO Klijent (Ime, Prezime, EMail, Telefon, Adresa, Grad, Drzava)
1996VALUES ('Iva', 'Ivi�', 'iva.ivic@abc.com', null, 'Vrtna 17', 'Vara�din', 'HR')
1997GO
1998
1999INSERT INTO Klijent (Ime, Prezime, EMail, Telefon, Adresa, Grad, Drzava)
2000VALUES ('Maja', 'Maji�', 'maja.majic@abc.com', null, 'Sun�ana 17', 'Vara�din', 'HR')
2001GO
2002
2003INSERT INTO Klijent (Ime, Prezime, EMail, Telefon, Adresa, Grad, Drzava)
2004VALUES ('Miro', 'Miri�', null, '042/111-222', '�iroka 152', 'Vara�din', 'HR')
2005GO
2006
2007
2008-- StatusVozila: �Pla�eno�, �Preuzeto�, �Vra�eno�
2009INSERT INTO StatusVozila (Naziv) VALUES ('Pla�eno')
2010GO
2011
2012INSERT INTO StatusVozila (Naziv) VALUES ('Preuzeto')
2013GO
2014
2015INSERT INTO StatusVozila (Naziv) VALUES ('Vra�eno')
2016GO
2017
2018
2019-- Najam: upi�ite da je svaki klijent iznajmio po 2 vozila, s podacima po �elji (jednog je vratio, jedan je preuzet)
2020INSERT INTO Najam (IDStatusVozila, IDKlijent, IDVozilo, DatumOd, DatumDo)
2021VALUES (3, 1, 2, '20090901', '20090902')
2022GO
2023
2024INSERT INTO Najam (IDStatusVozila, IDKlijent, IDVozilo, DatumOd, DatumDo)
2025VALUES (2, 1, 4, '20090917', null)
2026GO
2027
2028INSERT INTO Najam (IDStatusVozila, IDKlijent, IDVozilo, DatumOd, DatumDo)
2029VALUES (3, 2, 3, '20090901', '20090902')
2030GO
2031
2032INSERT INTO Najam (IDStatusVozila, IDKlijent, IDVozilo, DatumOd, DatumDo)
2033VALUES (2, 2, 7, '20090924', null)
2034GO
2035
2036INSERT INTO Najam (IDStatusVozila, IDKlijent, IDVozilo, DatumOd, DatumDo)
2037VALUES (3, 3, 6, '20090901', '20090902')
2038GO
2039
2040INSERT INTO Najam (IDStatusVozila, IDKlijent, IDVozilo, DatumOd, DatumDo)
2041VALUES (2, 3, 6, '20090929', null)
2042GO
2043
2044--------------------------------------------------------------------------
2045
2046-- Vje�ba 1c.
2047/*
2048Ispi�ite ID proizvo�a�a i broj njegovih modela, opadaju�e prema broju modela
2049Ispi�ite naziv proizvo�a�a i broj njegovih modela, opadaju�e prema broju modela
2050Ispi�ite najjeftiniju i najskuplju cijenu iznajmljivanja vozila za 10 dana
2051Ispi�ite ukupnu kilometra�u koju su pre�la Opel vozila
2052Ispi�ite broj klijenata
2053Ispi�ite sve klijente iz Vara�dina koji nemaju definiran e-mail
2054Ispi�ite sve najmove kod kojih su vozila vra�ena u pro�lom tjednu
2055*/
2056
2057-- Ispi�ite ID proizvo�a�a i broj njegovih modela, opadaju�e prema broju modela
2058SELECT IDProizvodjac, Count(*) AS BrojModela FROM Model 
2059GROUP BY IDProizvodjac ORDER BY BrojModela DESC
2060
2061-- Ispi�ite naziv proizvo�a�a i broj njegovih modela, opadaju�e prema broju modela
2062SELECT p.Naziv, Count(*) AS BrojModela 
2063FROM Model AS m
2064INNER JOIN Proizvodjac AS p ON m.IDProizvodjac = p.IDProizvodjac
2065GROUP BY p.Naziv ORDER BY BrojModela DESC
2066
2067-- Ispi�ite najjeftiniju i najskuplju cijenu iznajmljivanja vozila za 10 dana
2068SELECT
2069	Min(CijenaPodDanu) * 10 AS NajmanjaCijena, 
2070	Max(CijenaPodDanu) * 10 AS NajvecaCijena 
2071FROM Vozilo
2072
2073-- Ispi�ite ukupnu kilometra�u koju su pre�la Opel vozila
2074SELECT Sum(Kilometraza) AS Km FROM Vozilo WHERE IDModel = 1
2075
2076-- Ispi�ite broj klijenata
2077SELECT Count(*) FROM Klijent
2078
2079-- Ispi�ite sve klijente iz Vara�dina koji nemaju definiran e-mail
2080SELECT * FROM Klijent WHERE Grad = 'Vara�din' AND EMail IS NULL
2081
2082
2083--------------------------------------------------------------------------
2084
2085-- Vje�ba 1d.
2086/*
2087Ispi�ite registraciju i naziv modela, rastu�e prema nazivu
2088Ispi�ite registraciju, naziv modela i naziv proizvo�a�a, rastu�e prema nazivu proizvo�a�a
2089Ispi�ite registraciju, naziv modela i naziv kategorije, rastu�e prema nazivu kategorije
2090Ispi�ite registraciju, naziv modela, naziv proizvo�a�a i naziv kategorije, rastu�e prema registraciji
2091Ispi�ite sve najmove. Za svaki najam ispi�ite: od kada, do kada, naziv statusa, naziv proizvo�a�a, naziv kategorije, ime i prezime klijenta
2092*/
2093
2094-- Ispi�ite registraciju i naziv modela, rastu�e prema nazivu
2095SELECT v.Registracija, m.Naziv FROM Vozilo AS v 
2096INNER JOIN Model AS m ON v.IDModel = m.IDModel
2097ORDER BY m.Naziv
2098
2099-- Ispi�ite registraciju, naziv modela i naziv proizvo�a�a, rastu�e prema nazivu proizvo�a�a
2100SELECT v.Registracija, m.Naziv, p.Naziv AS NazivProizvodjaca FROM Vozilo AS v 
2101INNER JOIN Model AS m ON v.IDModel = m.IDModel
2102INNER JOIN Proizvodjac AS p on m.IDProizvodjac = p.IDProizvodjac
2103ORDER BY p.Naziv
2104
2105-- Ispi�ite registraciju, naziv modela i naziv kategorije, rastu�e prema nazivu kategorije
2106SELECT v.Registracija, m.Naziv, k.Naziv AS Kategorija FROM Vozilo AS v 
2107INNER JOIN Model AS m ON v.IDModel = m.IDModel
2108INNER JOIN Kategorija AS k on v.IDKategorija = k.IDKategorija
2109ORDER BY k.Naziv
2110
2111-- Ispi�ite registraciju, naziv modela, naziv proizvo�a�a i naziv kategorije, rastu�e prema registraciji
2112SELECT v.Registracija, m.Naziv, p.Naziv AS NazivProizvodjaca, k.Naziv AS Kategorija FROM Vozilo AS v 
2113INNER JOIN Model AS m ON v.IDModel = m.IDModel
2114INNER JOIN Proizvodjac AS p on m.IDProizvodjac = p.IDProizvodjac
2115INNER JOIN Kategorija AS k on v.IDKategorija = k.IDKategorija
2116ORDER BY v.Registracija
2117
2118-- Ispi�ite sve najmove. Za svaki najam ispi�ite: od kada, do kada, naziv statusa, naziv proizvo�a�a, 
2119-- naziv kategorije, ime i prezime klijenta
2120SELECT n.DatumOd, n.DatumDo, sv.Naziv AS Stanje, p.Naziv AS Proizvodjac, k.Ime, k.Prezime FROM Najam AS n
2121INNER JOIN StatusVozila AS sv ON n.IDStatusVozila = sv.IDStatusVozila
2122INNER JOIN Vozilo AS v ON v.IDVozilo = n.IDVozilo
2123INNER JOIN Model AS m ON v.IDModel = m.IDModel
2124INNER JOIN Proizvodjac AS p ON m.IDProizvodjac = p.IDProizvodjac
2125INNER JOIN Klijent AS k ON n.IDKlijent = k.IDKlijent
2126
2127
2128--------------------------------------------------------------------------
2129
2130-- Vje�ba 1e.
2131/*
2132Ispi�ite sve proizvo�a�e koji nemaju niti jedan model (spajanjem i podupitom)
2133Ispi�ite nazive svih modela i nazive njihovih proizvo�a�a. Ako neki model nema proizvo�a�a, stavite vrijednost "nepoznato".
2134Ispi�ite nazive dvaju proizvo�a�a koji imaju najvi�e modela
2135*/
2136
2137-- Ispi�ite sve najmove kod kojih su vozila vra�ena u pro�lom tjednu
2138SELECT * FROM Najam WHERE IDStatusVozila = 3 AND DatumDo BETWEEN '20150921' AND '20150927 23:59:59'
2139
2140-- Ispi�ite sve proizvo�a�e koji nemaju niti jedan model (spajanjem i podupitom)
2141-- ispis spajanjem
2142SELECT p.*
2143FROM Proizvodjac AS p
2144LEFT JOIN Model AS m ON p.IDProizvodjac = m.IDProizvodjac
2145WHERE m.IDProizvodjac IS NULL
2146
2147-- ispis podupitom
2148SELECT *
2149FROM Proizvodjac AS p
2150WHERE NOT EXISTS(SELECT * FROM Model AS m WHERE p.IDProizvodjac = m.IDProizvodjac)
2151
2152-- Ispi�ite nazive svih modela i nazive njihovih proizvo�a�a. Ako neki model nema proizvo�a�a, stavite vrijednost "nepoznato".
2153SELECT 
2154	m.Naziv AS NazivModela,
2155	ISNULL(p.Naziv, 'nepoznato') AS NazivProizvodjaca
2156FROM Model AS m
2157LEFT JOIN Proizvodjac AS p ON m.IDProizvodjac = p.IDProizvodjac
2158
2159-- Ispi�ite nazive dvaju proizvo�a�a koji imaju najvi�e modela
2160SELECT TOP 2
2161	p.Naziv,
2162	COUNT(*) AS BrojVozila
2163FROM Proizvodjac AS p
2164LEFT JOIN model AS m ON m.IDProizvodjac = p.IDProizvodjac
2165GROUP BY p.Naziv
2166ORDER BY BrojVozila DESC 
2167--------------------------------------------------------------------------
2168
2169
2170
2171---------------- Vjezba 2
2172-----------------------------------------------------------------------------------
2173-- prebacivanje na bazu AdventureWorksOBP
2174USE AdventureWorksOBP
2175GO
2176
2177-----------------------------------------------------------------------------------
2178-- Vje�be 1.
2179
2180-- Vje�ba 1.1.
2181-- Napravite pogled koji �e dohva�ati sve iz tablice Kupac
2182CREATE VIEW p1 
2183AS
2184SELECT * FROM Kupac
2185GO
2186
2187-- Vje�ba 1.1.a)
2188-- Iskoristite pogled za dohva�anje svih zapisa
2189SELECT * FROM p1 
2190GO
2191
2192-- Vje�ba 1.1.b)
2193-- Iskoristite pogled za dohva�anje onih osoba �ije ime zapo�inje sa �L� i prezime zavr�ava na �a�
2194SELECT * FROM p1 
2195WHERE Ime LIKE 'L%' AND Prezime LIKE '%a'
2196GO
2197
2198-- Vje�ba 1.1.c)
2199-- Iskoristite pogled za ispis svih ID-eva gradova i broja osoba koje �ive u tom gradu, 
2200-- padaju�e prema broju osoba
2201-- (pomo�u grupiranja i pomo�u podupita)
2202-- pomo�u grupiranja
2203SELECT GradID, COUNT(*) AS BrojOsoba 
2204FROM p1 
2205GROUP BY GradID 
2206ORDER BY BrojOsoba DESC
2207GO
2208
2209-- pomo�u podupita
2210SELECT DISTINCT 
2211	GradID, 
2212	(SELECT COUNT(*) 
2213	 FROM p1 AS sq 
2214	 WHERE ISNULL(sq.GradID, '') = ISNULL(mq.GradID, '')) AS BrojOsoba
2215FROM p1 AS mq 
2216ORDER BY BrojOsoba DESC
2217GO
2218
2219-- Vje�ba 1.1.d)
2220-- Iskoristite pogled tako da ispi�ete ime i prezime te pokraj svakoga 
2221-- ispi�ite njegov naziv grada i naziv dr�ave
2222SELECT 
2223	p1.Ime, 
2224	p1.Prezime,
2225	g.Naziv as 'Grad',
2226	d.Naziv as 'Dr�ava'
2227FROM p1
2228LEFT JOIN Grad AS g ON p1.GradID = g.IDGrad
2229LEFT JOIN Drzava AS d ON g.DrzavaID= d.IDDrzava
2230GO
2231
2232-- Vje�ba 1.2)
2233-- Promijenite pogled tako da ne uklju�uje stupce Email, Telefon i GradID
2234ALTER VIEW p1 
2235AS
2236SELECT IDKupac, Ime, Prezime FROM Kupac
2237GO
2238-- provjera
2239SELECT * FROM p1 
2240GO
2241
2242-- Vje�ba 1.2)
2243-- Uklonite pogled
2244DROP VIEW p1
2245GO
2246
2247
2248-----------------------------------------------------------------------------------
2249-- Vje�be 2.
2250
2251-- Vje�ba 2.1)
2252-- Pripremite sljede�e izvje�taje u obliku pogleda:
2253
2254-- Vje�ba 2.1.a)
2255-- Ispi�ite nazive svih kupaca, te za svakoga ispi�ite email, grad i naziv dr�ave u kojoj je smje�ten
2256-- Tablice: Kupac, Grad, Drzava
2257
2258CREATE VIEW p2 AS
2259SELECT 
2260	k.Ime,
2261	k.Prezime,
2262	k.Email,
2263	g.Naziv AS Grad,
2264	d.Naziv AS Drzava
2265FROM Kupac AS k
2266LEFT JOIN Grad AS g ON k.GradID = g.IDGrad
2267LEFT JOIN Drzava AS d ON g.DrzavaID = d.IDDrzava
2268GO
2269-- provjera
2270SELECT * FROM p2
2271GO
2272
2273-- Vje�ba 2.1.b)
2274-- Ispi�ite sve dr�ave i za svaku od njih ispi�ite koliko kupaca iz nje postoji
2275-- Tablice: Kupac, Grad, Drzava
2276
2277CREATE VIEW p3 AS
2278SELECT 
2279	d.Naziv AS Drzava,
2280	COUNT(k.IDKupac) AS BrojKupaca
2281FROM Kupac AS k
2282LEFT JOIN Grad AS g ON k.GradID = g.IDGrad
2283LEFT JOIN Drzava AS d ON g.DrzavaID = d.IDDrzava
2284GROUP BY d.Naziv
2285GO
2286-- provjera
2287SELECT * FROM p3
2288GO
2289
2290-- Vje�ba 2.1.c)
2291-- Ispi�ite nazive svih proizvoda koje je kupilo >300 kupaca
2292-- Tablice: Kupac, Racun, Stavka i Proizvod
2293
2294CREATE VIEW p4 AS
2295SELECT 
2296	p.Naziv,
2297	COUNT(k.IDKupac) AS BrojKupaca
2298FROM Proizvod AS p
2299INNER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
2300INNER JOIN Racun AS r ON s.RacunID = r.IDRacun
2301INNER JOIN Kupac AS k ON r.KupacID = k.IDKupac
2302GROUP BY p.Naziv
2303HAVING COUNT(k.IDKupac) > 300
2304GO
2305-- provjera
2306SELECT * FROM p4
2307GO
2308
2309-- Vje�ba 2.1.d)
2310-- Ispi�ite nazive i zaradu 5 proizvoda koji se najbolje prodaju
2311-- Tablice: Stavka i Proizvod
2312
2313CREATE VIEW p5 AS
2314SELECT TOP 5
2315	p.Naziv,
2316	SUM(s.UkupnaCijena) AS Zarada
2317FROM Proizvod AS p
2318INNER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
2319GROUP BY p.Naziv
2320ORDER BY Zarada DESC
2321GO
2322-- provjera
2323SELECT * FROM p5
2324GO
2325
2326-- Vje�ba 2.1.e)
2327-- Uklonite sve poglede
2328DROP VIEW p2
2329DROP VIEW p3
2330DROP VIEW p4
2331DROP VIEW p5
2332GO
2333
2334
2335-----------------------------------------------------------------------------------
2336
2337-- Vje�be 3.
2338
2339-- Vje�ba 3.1)
2340-- Napravite pogled koji vra�a imena i prezimena te e-mailove svih kupaca iz Zagreba.
2341
2342CREATE VIEW p6 AS
2343SELECT 
2344	k.Ime,
2345	k.Prezime,
2346	k.Email,
2347	g.Naziv AS Grad
2348FROM Kupac AS k
2349INNER JOIN Grad AS g ON k.GradID = g.IDGrad
2350WHERE g.Naziv = 'Zagreb'
2351GO
2352-- provjera
2353SELECT * FROM p6
2354GO
2355
2356-- Vje�ba 3.2)
2357-- Promijenite pogled tako da dohva�a i sve kupce iz Splita.
2358
2359ALTER VIEW p6 AS
2360SELECT 
2361	k.Ime,
2362	k.Prezime,
2363	k.Email,
2364	g.Naziv AS Grad
2365FROM Kupac AS k
2366INNER JOIN Grad AS g ON k.GradID = g.IDGrad
2367WHERE g.Naziv = 'Zagreb' OR g.Naziv = 'Split'
2368GO
2369-- provjera
2370SELECT * FROM p6
2371GO
2372
2373-- Vje�ba 3.3)
2374-- Koriste�i pogled ispi�ite broj kupaca iz Zagreba i broj kupaca iz Splita.
2375
2376SELECT 
2377	Grad,
2378	COUNT(*) AS Broj
2379FROM p6
2380GROUP BY Grad
2381GO
2382
2383-- Vje�ba 3.4)
2384-- Uklonite pogled.
2385
2386DROP VIEW p6
2387GO
2388
2389
2390---------------- Vjezba 3
2391--------------------------------------------------------------------------
2392-- Vje�ba 1.
2393--------------------------------
2394-- Napravite pogled koji �e dohva�ati sve stupce i retke iz tablice Kategorija
2395CREATE VIEW v1 AS
2396SELECT * FROM Kategorija
2397GO
2398--------------------------------
2399
2400--------------------------------
2401-- Vje�ba 1a.
2402--------------------------------
2403-- Ispi�ite nazive kategorija, potkategorija i proizvoda (koristite kreirani pogled)
2404SELECT
2405	v1.Naziv as 'Kategorija',
2406	Potkategorija.Naziv as 'Potkategorija',
2407	Proizvod.Naziv as 'Proizvod'
2408FROM v1
2409RIGHT JOIN Potkategorija ON Potkategorija.KategorijaID = v1.IDKategorija
2410RIGHT JOIN Proizvod ON Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
2411GO
2412--------------------------------
2413
2414--------------------------------
2415-- Vje�ba 1b.
2416--------------------------------
2417-- Pomo�u pogleda umetnite kategoriju naziva �Alarmi�
2418INSERT INTO v1 (Naziv) VALUES ('Alarmi')
2419GO
2420--------------------------------
2421
2422--------------------------------
2423-- Vje�ba 1c.
2424--------------------------------
2425-- Pomo�u pogleda promijenite kategoriji �Alarmi� naziv u �Aktivna za�tita�
2426UPDATE v1 SET Naziv = 'Aktivna za�tita' WHERE Naziv = 'Alarmi'
2427GO
2428--------------------------------
2429
2430--------------------------------
2431-- Vje�ba 1d.
2432--------------------------------
2433-- Pomo�u pogleda obri�ite kategoriju �Aktivna za�tita�
2434DELETE FROM v1 WHERE Naziv = 'Aktivna za�tita'
2435GO
2436--------------------------------
2437
2438--------------------------------
2439-- Vje�ba 1e.
2440--------------------------------
2441-- Uklonite pogled
2442DROP VIEW v1
2443GO
2444--------------------------------
2445
2446--------------------------------------------------------------------------
2447-- Vje�ba 2.
2448--------------------------------
2449-- Napravite pogled koji �e dohva�ati naziv grada, naziv dr�ave u kojoj se nalazi te sve 
2450-- podatke o kupcima koji im pripadaju (tablice Grad, Drzava, Kupac).
2451CREATE VIEW v2 AS
2452SELECT
2453	Drzava.Naziv as 'Dr�ava',
2454	Grad.Naziv as 'Grad',
2455	Kupac.* 
2456FROM Kupac
2457LEFT JOIN Grad ON Kupac.GradID = Grad.IDGrad
2458LEFT JOIN Drzava ON Grad.DrzavaID = Drzava.IDDrzava
2459GO
2460-- provjera
2461SELECT * FROM v2
2462GO
2463--------------------------------
2464
2465--------------------------------
2466-- Vje�ba 2a.
2467--------------------------------
2468-- Probajte pomo�u pogleda umetnuti novi grad. �to se dogodilo?
2469INSERT INTO v2 (Grad) VALUES ('Bedekov�ina')
2470GO
2471SELECT * FROM Grad
2472GO
2473
2474--------------------------------
2475
2476--------------------------------
2477-- Vje�ba 2b.
2478--------------------------------
2479-- Probajte pomo�u pogleda umetnuti novu dr�avu. �to se dogodilo?
2480INSERT INTO v2 (Dr�ava) VALUES ('Uzbekistan')
2481GO
2482SELECT * FROM Drzava
2483GO
2484--------------------------------
2485
2486--------------------------------
2487-- Vje�ba 2c.
2488--------------------------------
2489-- Probajte pomo�u pogleda umetnuti novog kupca. �to se dogodilo? Mo�ete li vidjeti novododanog kupca kroz pogled? Postoji li on u tablici?
2490INSERT INTO v2 (Ime, Prezime) 
2491	VALUES ('Pero','Peri�')
2492GO
2493SELECT * FROM Kupac
2494GO
2495SELECT * FROM v2
2496GO
2497--------------------------------
2498
2499--------------------------------
2500-- Vje�ba 2d.
2501--------------------------------
2502-- Uklonite pogled
2503DROP VIEW v2
2504GO
2505--------------------------------
2506
2507--------------------------------------------------------------------------
2508-- Vje�ba 3.
2509--------------------------------
2510-- Napravite pogled koji �e dohva�ati sve kreditne kartice koje su tipa Visa ili MasterCard (tablica KreditnaKastica)
2511CREATE VIEW v3 AS
2512SELECT * FROM KreditnaKartica WHERE Tip IN ('Visa', 'MasterCard')
2513GO
2514--------------------------------
2515
2516--------------------------------
2517-- Vje�ba 3a i 3b.
2518--------------------------------
2519-- Umetnite zapis o kreditnoj kartici tipa American Express.
2520-- Dohvatiti umetnuti redak pomo�u pogleda. �to se dogodilo? Je li redak uspje�no umetnut u tablicu?
2521INSERT INTO v3 (Tip, Broj,IstekMjesec, IstekGodina) 
2522VALUES ('American Express', '111222333444', 12, 2012)
2523GO
2524SELECT * FROM v3 WHERE Tip = 'American Express'
2525GO
2526
2527--------------------------------
2528
2529--------------------------------
2530-- Vje�ba 3c.
2531--------------------------------
2532-- Promijenite pogled tako da ne dopu�ta umetanje/izmjenu redaka koji ne�e biti vidljivi kroz njega.
2533ALTER VIEW v3 AS
2534SELECT * FROM KreditnaKartica WHERE Tip IN ('Visa', 'MasterCard')
2535WITH CHECK OPTION
2536GO
2537INSERT INTO v3 (Tip, Broj,IstekMjesec, IstekGodina) 
2538VALUES ('American Express', '111222333444', 12, 2012)
2539GO
2540--------------------------------
2541
2542--------------------------------
2543-- Vje�ba 3d.
2544--------------------------------
2545-- Umetnite zapis o kreditnoj kartici tipa MasterCard. �to se dogodilo? Je li redak uspje�no umetnut u tablicu?
2546INSERT INTO v3 (Tip, Broj,IstekMjesec, IstekGodina) 
2547VALUES ('MasterCard', '111222333444', 12, 2012)
2548GO
2549--------------------------------
2550
2551--------------------------------
2552-- Vje�ba 3e.
2553--------------------------------
2554-- Promijenite pogled tako da dopu�ta umetanje/izmjenu redaka koji ne�e biti vidljivi kroz njega.
2555ALTER VIEW v3 AS
2556SELECT * FROM KreditnaKartica WHERE Tip IN ('Visa', 'MasterCard')
2557GO
2558--------------------------------
2559
2560--------------------------------
2561-- Vje�ba 3f.
2562--------------------------------
2563-- Uklonite pogled
2564DROP VIEW v3
2565GO
2566--------------------------------
2567
2568-- Vje�be 4.
2569/*
2570Napravite tablicu Film sa stupcima IDFilm, Naziv, GodinaProizvodnje, TrajanjeMinuta i KratkiOpis. Umetnite koji redak. 
2571Napravite pogled koji dohva�a sve iz tablice Film
2572Iskoristite pogled za dohva�anje podataka
2573Uklonite stupac TrajanjeMinuta iz tablice
2574Iskoristite pogled za dohva�anje podataka
2575Promijenite pogled tako da bude �vrsto vezan uz objekte koje koristi
2576Iskoristite pogled za dohva�anje podataka
2577Uklonite stupac GodinaProizvodnje iz tablice. Je li uklanjanje uspjelo? Za�to?
2578Uklonite pogled i tablicu
2579*/
2580CREATE TABLE Film
2581(
2582	IDFilm int CONSTRAINT PK_Film PRIMARY KEY IDENTITY,
2583	Naziv nvarchar(200) NOT NULL,
2584	GodinaProizvodnje int NOT NULL,
2585	TrajanjeMinuta int NOT NULL,
2586	KratkiOpis nvarchar(max) NOT NULL
2587)
2588GO
2589INSERT INTO Film (Naziv, GodinaProizvodnje, TrajanjeMinuta, KratkiOpis) 
2590	VALUES (N'O ma�kama i psima 2: Osveta Kitty Galore', 2010, 85, N'U vje�noj bitci ma�aka i pasa, jedna poludjela ma�ka odvest �e stvari jednu �apu predaleko. Kitty Galore, biv�a agentica �pijunske organizacije MIJAU, pobjegla je i skovala �avolji plan u kojem ne samo da �e poku�ati pripitomiti svoje vje�ne neprijatelje, ve� i svoje biv�e kolege ma�ke, ali i cjelokupno �ovje�anstvo. Suo�eni s ovom prijetnjom bez presedana, ma�ke i psi biti �e prisiljeni prvi put u povijesti udru�iti snage kako bi spasili sebe � ali i svoje vlasnike')
2591INSERT INTO Film (Naziv, GodinaProizvodnje, TrajanjeMinuta, KratkiOpis) 
2592	VALUES (N'Shrek uvijek i zauvijek', 2010, 95, N'Nakon �to se borio s opakim zmajem, spasio prekrasnu princezu i oslobodio �itavo kraljevstvo � kakav bi zadatak jo� mogao postojati u �ivotu slavnog Shreka? Odgovor je jasan: obitelj, odnosno svakodnevna bitka s malim nesta�nim Shreki�ima. Me�utim, sve �e to biti kratka vijeka, jer �e Shrek ubrzo upoznati lukavog Rumpelstiltskina i iznenada se na�i u potpuno drugoj dimenziji, alternativnoj budu�nosti u kojoj se on i Fiona nikad nisu susreli, u kojoj je ba� on ugro�ena vrsta, a Rumpelstiltskin kralj! Naravno, jedino je Shrek taj koji �e mo�i ispraviti novonastalu situaciju, spasiti prijatelje, obnoviti kraljevstvo i ponovno prona�i svoju pravu, jedinu ljubav.')
2593INSERT INTO Film (Naziv, GodinaProizvodnje, TrajanjeMinuta, KratkiOpis) 
2594	VALUES (N'Resident Evil: Drugi svijet', 2010, 90, N'Svijet je poharan virusnom infekcijom od koje oboljeli postaju zombiji. Alice (Milla Jovovich) pronalazi pre�ivjele i spa�ava ih. Njezina bitka s Umbrella Corporation dosti�e smrtonosne razine, no u pomo� joj priska�e stari prijatelj. U nadi da �e prona�i sigurno mjesto odlaze prema Los Angelesu. No, grad je preplavljen tisu�ama zombija i oni �e naletjeti u zamku')
2595GO
2596
2597CREATE VIEW v4 AS
2598SELECT * FROM Film
2599GO
2600
2601SELECT * FROM v4
2602GO
2603
2604ALTER TABLE Film DROP COLUMN TrajanjeMinuta
2605GO
2606
2607ALTER VIEW v4 
2608WITH SCHEMABINDING
2609AS
2610SELECT IDFilm, Naziv, GodinaProizvodnje, KratkiOpis FROM dbo.Film
2611GO
2612
2613SELECT * FROM v4
2614GO
2615
2616ALTER TABLE Film DROP COLUMN GodinaProizvodnje
2617GO
2618
2619DROP VIEW v4
2620DROP TABLE Film
2621GO
2622
2623-- Vje�be 5.
2624/*
2625Napravite pogled koji dohva�a 10 proizvoda koji su najvi�e prodavani. Stupci koje pogled vra�a neka budu ID i naziv te ukupna koli�ina prodanih proizvoda.
2626Pogledajte SELECT upit pogleda kroz su�elje i pomo�u sistemske procedure sp_helptext
2627Za�titite pogled
2628Pogledajte SELECT upit pogleda kroz su�elje i pomo�u sistemske procedure sp_helptext
2629Promijenite pogled tako da bude za�ti�en i �vrsto vezan uz tablice
2630Promijenite pogled tako da bude za�ti�en, �vrsto vezan uz tablice i da ne dopu�ta izmjene koje ne�e biti vidljive kroz pogled
2631Uklonite pogled
2632*/
2633CREATE VIEW v5 AS
2634SELECT TOP 10 
2635	Proizvod.IDProizvod AS 'ID',
2636	Proizvod.Naziv,
2637	SUM(Stavka.Kolicina) AS 'UkupnaKolicina'
2638FROM Proizvod
2639INNER JOIN Stavka ON Stavka.ProizvodID = Proizvod.IDProizvod
2640GROUP BY Proizvod.IDProizvod, Proizvod.Naziv
2641ORDER BY 3 DESC
2642GO
2643
2644SELECT * FROM v5
2645GO
2646
2647EXECUTE sp_helptext v5
2648GO
2649
2650ALTER VIEW v5 
2651WITH ENCRYPTION
2652AS
2653SELECT TOP 10 
2654	Proizvod.IDProizvod AS 'ID',
2655	Proizvod.Naziv,
2656	SUM(Stavka.Kolicina) AS 'UkupnaKolicina'
2657FROM Proizvod
2658INNER JOIN Stavka ON Stavka.ProizvodID = Proizvod.IDProizvod
2659GROUP BY Proizvod.IDProizvod, Proizvod.Naziv
2660ORDER BY 3 DESC
2661GO
2662
2663EXECUTE sp_helptext v5
2664GO
2665
2666ALTER VIEW v5 
2667WITH ENCRYPTION, SCHEMABINDING
2668AS
2669SELECT TOP 10 
2670	dbo.Proizvod.IDProizvod AS 'ID',
2671	dbo.Proizvod.Naziv,
2672	SUM(dbo.Stavka.Kolicina) AS 'UkupnaKolicina'
2673FROM dbo.Proizvod
2674INNER JOIN dbo.Stavka ON dbo.Stavka.ProizvodID = dbo.Proizvod.IDProizvod
2675GROUP BY dbo.Proizvod.IDProizvod, dbo.Proizvod.Naziv
2676ORDER BY 3 DESC
2677GO
2678
2679ALTER VIEW v5 
2680WITH ENCRYPTION, SCHEMABINDING
2681AS
2682SELECT TOP 10 
2683	dbo.Proizvod.IDProizvod AS 'ID',
2684	dbo.Proizvod.Naziv,
2685	SUM(dbo.Stavka.Kolicina) AS 'UkupnaKolicina'
2686FROM dbo.Proizvod
2687INNER JOIN dbo.Stavka ON dbo.Stavka.ProizvodID = dbo.Proizvod.IDProizvod
2688GROUP BY dbo.Proizvod.IDProizvod, dbo.Proizvod.Naziv
2689ORDER BY 3 DESC
2690WITH CHECK OPTION
2691GO
2692
2693DROP VIEW v5
2694GO
2695
2696
2697---------------- Vjezba 4
2698-- prebacivanje na bazu
2699USE AdventureWorksOBP
2700GO
2701
2702--------------------------------------------------------------------------
2703-- Vje�be 1.
2704DBCC TRACEON(3604)
2705DBCC IND('AdventureWorksOBP', 'Drzava', -1)
2706-- a. DBCC IND vra�a 2 retka, ali samo jedan od njih ima PageType jednak 1. 
2707-- Dakle, svi podaci iz tablice su smje�teni na jednu stranicu.
2708-- b. PageFID = 1, PagePID = 77
2709
2710DBCC PAGE('AdventureWorksOBP', 1, 77, 3) WITH TABLERESULTS
2711-- c. Za 1. redak imamo: "Slot 0 Offset 0x60 Length 31", dakle, njegova duljina je 31 bajta.
2712--    Za 2. redak imamo: "Slot 1 Offset 0x7f Length 31", dakle, njegova duljina je 31 bajta.
2713--    Za 3. redak imamo: "Slot 2 Offset 0x9e Length 53", dakle, njegova duljina je 53 bajta.
2714-- d. Reci su poslagani po stupcu IDDrzava, uzlazno od a prema z.
2715-- e. Redak ispred Njema�ka je Hrvatska, a iza je Bosna i Hercegovina.
2716
2717
2718-- Vje�be 2.
2719DBCC TRACEON(3604)
2720DBCC IND('AdventureWorksOBP', 'Proizvod', -1)
2721
2722DBCC PAGE('AdventureWorksOBP', 1, 79, 3) WITH TABLERESULTS
2723DBCC PAGE('AdventureWorksOBP', 1, ???, 3) WITH TABLERESULTS
2724
2725SELECT COUNT(*) FROM Proizvod
2726-- g. Ukupno stranica = ??
2727--    Ukupno redaka = 504
2728--    Prosje�no redaka po stranici = ??
2729
2730-- Vje�be 3.
2731DBCC TRACEON(3604)
2732DBCC IND('AdventureWorksOBP', 'Kupac', -1)
2733
2734DBCC PAGE('AdventureWorksOBP', 1, ???, 3) WITH TABLERESULTS
2735
2736
2737---------------- Vjezba 5
2738-----------------------------------------------------------------------------------
2739-----------------------------------------------------------------------------------
2740-- prebacivanje na bazu AdventureWorksOBP
2741USE AdventureWorksOBP
2742GO
2743
2744-----------------------------------------------------------------------------------
2745-- Optimizacija indeksa
2746-- Uklonite sve indekse osim klasteriranih s tablica Proizvod i Racun.
2747-- Postavite SET STATISTICS IO ON
2748
2749SET STATISTICS IO ON
2750
2751-- Zadatak 1.
2752-- Optimizirajte upit:
2753-- SELECT PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 
2754
2755-- a.) Koliko stranica pregled RDBMS?
2756SELECT PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 
2757-- (28 row(s) affected)
2758-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2759-- logical reads 8
2760
2761-- b.) Napravite indeks koji optimizira upit
2762CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID)
2763GO
2764
2765-- c.) Koliko sad stranica pregled RDBMS?
2766SELECT PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 
2767-- (28 row(s) affected)
2768-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2769-- logical reads 2
2770
2771-- d.) Uklonite indeks
2772DROP INDEX Proizvod.i1
2773GO
2774
2775-- Zadatak 2.
2776-- Optimizirajte upit: SELECT IDProizvod, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 
2777
2778-- a.) Koliko stranica pregled RDBMS?
2779SELECT IDProizvod, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
2780-- (28 row(s) affected)
2781-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2782-- logical reads 8
2783
2784-- b.) Napravite indeks koji optimizira upit
2785CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID)
2786GO
2787
2788-- c.) Koliko sad stranica pregled RDBMS?
2789SELECT IDProizvod, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
2790-- (28 row(s) affected)
2791-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2792-- logical reads 2
2793
2794-- d.) Uklonite indeks
2795DROP INDEX Proizvod.i1
2796GO
2797
2798-- Zadatak 3.
2799-- Optimizirajte upit:
2800-- SELECT ProductID, Name, ProductSubcategoryID FROM Production.Product WHERE ProductSubcategoryID = 12 
2801
2802SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
2803-- (28 row(s) affected)
2804-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2805-- logical reads 8
2806
2807CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID)
2808GO
2809
2810SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
2811-- (28 row(s) affected)
2812-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2813-- logical reads 8
2814
2815-- a) Poma�e li nam indeks iz prethodnog primjera? �to u�initi sa stupcem Naziv? 
2816-- logical reads 8 - mora i�i na klasterirani indeks jer mu nedostaje vrijednost za Naziv.
2817DROP INDEX Proizvod.i1
2818GO
2819
2820CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID) INCLUDE (naziv)
2821GO
2822
2823SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
2824-- (28 row(s) affected)
2825-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2826-- logical reads 2 - sad ne mora i�i na klasterirani indeks.
2827
2828DROP INDEX Proizvod.i1
2829GO
2830
2831-- Zadatak 4.
2832-- Optimizirajte upit:
2833-- SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 AND Naziv LIKE 'ML%'
2834
2835SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 AND Naziv LIKE 'ML%'
2836-- (8 row(s) affected)
2837-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2838-- logical reads 8 - mora i�i na klasterirani indeks jer mu nedostaje vrijednost za Naziv.
2839CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID) INCLUDE (naziv)
2840GO
2841
2842SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 AND Naziv LIKE 'ML%'
2843-- (8 row(s) affected)
2844-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2845-- logical reads 2 - sad ne mora i�i na klasterirani indeks.
2846
2847DROP INDEX Proizvod.i1
2848GO
2849
2850-- Zadatak 5.
2851-- Optimizirajte upit:
2852-- SELECT Boja, COUNT(*) AS Cnt FROM Proizvod WHERE PotkategorijaID = 12 GROUP BY Boja ORDER BY Cnt DESC
2853
2854SELECT Boja, COUNT(*) AS Cnt FROM Proizvod WHERE PotkategorijaID = 12 GROUP BY Boja ORDER BY Cnt DESC
2855-- (2 row(s) affected)
2856-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2857-- logical reads 8 - mora i�i na klasterirani indeks jer mu nedostaje vrijednost za Boja.
2858CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID) INCLUDE (Boja)
2859GO
2860
2861SELECT Boja, COUNT(*) AS Cnt FROM Proizvod WHERE PotkategorijaID = 12 GROUP BY Boja ORDER BY Cnt DESC
2862-- (2 row(s) affected)
2863-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2864-- logical reads 2 - sad ne mora i�i na klasterirani indeks.
2865
2866DROP INDEX Proizvod.i1
2867GO
2868
2869-- Zadatak 6.
2870-- Optimizirajte upit:
2871-- SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'  
2872
2873-- a.) Koliko stranica pregled RDBMS?
2874SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
2875-- (4 row(s) affected)
2876-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2877-- logical reads 202
2878
2879-- b.) Napravite indeks koji optimizira upit
2880CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja)
2881GO
2882
2883-- c.) Koliko sad stranica pregled RDBMS?
2884SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' 
2885-- (4 row(s) affected)
2886-- Table 'Racun'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2887-- logical reads 2
2888
2889-- d.) Uklonite indeks
2890DROP INDEX Racun.i1
2891GO
2892
2893-- Zadatak 7.
2894-- Optimizirajte upit:
2895-- SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'  
2896
2897-- a.) Koliko stranica pregled RDBMS?
2898SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
2899-- (4 row(s) affected)
2900-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2901-- logical reads 202
2902
2903-- b.) Napravite indeks koji optimizira upit
2904CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja)
2905GO
2906
2907-- c.) Koliko sad stranica pregled RDBMS?
2908SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' 
2909-- (4 row(s) affected)
2910-- Table 'Racun'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2911-- logical reads 2
2912
2913-- d.) Uklonite indeks
2914DROP INDEX Racun.i1
2915GO
2916
2917-- Zadatak 8.
2918-- Optimizirajte upit:
2919-- SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'  
2920
2921SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' 
2922-- (4 row(s) affected)
2923-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2924-- logical reads 202
2925
2926CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja)
2927GO
2928
2929SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' 
2930-- (4 row(s) affected)
2931-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2932-- logical reads 202
2933
2934-- a) Poma�e li nam indeks iz prethodnog primjera? �to u�initi sa stupcem BrojRacuna? 
2935-- logical reads 202 - mora i�i na klasterirani indeks jer mu nedostaje vrijednost za BrojRacuna.
2936DROP INDEX Racun.i1
2937GO
2938
2939CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)
2940GO
2941
2942SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' 
2943-- (4 row(s) affected)
2944-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2945-- logical reads 2 - sad ne mora i�i na klasterirani indeks.
2946
2947DROP INDEX Racun.i1
2948GO
2949
2950-- Zadatak 9.
2951-- Optimizirajte upit:
2952-- SELECT IDRacun FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' AND BrojRacuna LIKE 'S%'  
2953
2954SELECT IDRacun FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' AND BrojRacuna LIKE 'S%' 
2955-- (4 row(s) affected)
2956-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2957-- logical reads 202
2958
2959CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)
2960GO
2961
2962SELECT IDRacun FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' AND BrojRacuna LIKE 'S%' 
2963-- (4 row(s) affected)
2964-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2965-- logical reads 2 - sad ne mora i�i na klasterirani indeks.
2966
2967DROP INDEX Racun.i1
2968GO
2969
2970-- Zadatak 10.
2971-- Optimizirajte upit:
2972-- SELECT KupacID, COUNT(*) AS Cnt  FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' GROUP BY KupacID ORDER BY Cnt DESC
2973
2974SELECT KupacID, COUNT(*) AS Cnt  FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' GROUP BY KupacID ORDER BY Cnt DESC
2975-- (4 row(s) affected)
2976-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2977-- logical reads 202
2978
2979CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja) INCLUDE (KupacID)
2980GO
2981
2982SELECT KupacID, COUNT(*) AS Cnt  FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' GROUP BY KupacID ORDER BY Cnt DESC 
2983-- (4 row(s) affected)
2984-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2985-- logical reads 2 - sad ne mora i�i na klasterirani indeks.
2986
2987DROP INDEX Racun.i1
2988GO
2989
2990-----------------------------------------------------------------------------------
2991
2992
2993
2994---------------- Vjezba 6
2995-----------------------------------------------------------------------------------
2996/*
2997Vje�be se rade na bazi AdventureWorksOBP. 
2998*/
2999USE AdventureWorksOBP
3000GO
3001-----------------------------------------------------------------------------------
3002-- Zadatak 1.
3003/*
3004VARIJABLE
3005Deklarirajte varijable @Ime i @Prezime i dodijelite im neke vrijednosti. 
3006Ispi�ite dodijeljene vrijednosti.
3007*/
3008DECLARE @Ime nvarchar(50)
3009DECLARE @Prezime nvarchar(50)
3010
3011SET @Ime = 'Miro'
3012SET @Prezime = 'Miri�'
3013
3014PRINT @Ime
3015PRINT @Prezime
3016PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
3017GO
3018-----------------------------------------------------------------------------------
3019-- Zadatak 2.
3020/*
3021VARIJABLE
3022Deklarirajte varijable @Ime i @Prezime i dodijelite im vrijednosti iz tablice Kupac za IDKupac jednak 8812. 
3023Ispi�ite dodijeljene vrijednosti.
3024*/
3025DECLARE @Ime nvarchar(50)
3026DECLARE @Prezime nvarchar(50)
3027
3028SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac WHERE IDKupac = 8812
3029
3030PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
3031GO
3032-----------------------------------------------------------------------------------
3033-- Zadatak 3.
3034/*
3035VARIJABLE
3036Deklarirajte varijable @Ime i @Prezime i dodijelite im vrijednosti iz tablice Kupac tako da odaberete sve retke iz tablice. 
3037Ispi�ite dodijeljene vrijednosti.
3038*/
3039DECLARE @Ime nvarchar(50)
3040DECLARE @Prezime nvarchar(50)
3041
3042SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac
3043
3044PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
3045GO
3046-----------------------------------------------------------------------------------
3047SET NOCOUNT ON
3048-----------------------------------------------------------------------------------
3049-- Zadatak 4.
3050/*
3051IF-ELSE IF-ELSE i SCOPE_IDENTITY()
3052Provjerite broj zapisa u tablici Kupac. 
3053Ako ih ima vi�e ili jednako 20000, ispi�ite �Postoji vi�e od 20000 kupaca :)�. 
3054Ako ih ima manje, ispi�ite �Jo� nismo dostigli 20000 kupaca :(�
3055*/
3056DECLARE @Broj int
3057SELECT @Broj = COUNT(*) FROM dbo.Kupac
3058IF @Broj >= 20000
3059	PRINT 'Postoji vi�e od 20000 kupaca :)'
3060ELSE
3061	PRINT 'Jo� nismo dostigli 20000 kupaca :('
3062GO
3063-----------------------------------------------------------------------------------
3064-- Zadatak 5.
3065/*
3066IF-ELSE IF-ELSE i SCOPE_IDENTITY()
3067Umetnite zapis u tablicu Drzava, generiranu IDENTITY vrijednost dodijelite nekoj varijabli pa je ispi�ite.
3068*/
3069DECLARE @NoviID int
3070INSERT INTO Drzava VALUES ('Gruzija')
3071SET @NoviID = SCOPE_IDENTITY()
3072PRINT @NoviID
3073GO
3074-----------------------------------------------------------------------------------
3075-- Zadatak 6.
3076/*
3077IF-ELSE IF-ELSE i SCOPE_IDENTITY()
3078Umetnite zapis u tablicu Drzava i u varijablu spremite generiranu IDENTITY vrijednost. 
3079Iskoristite tu vrijednost da biste za tu dr�avu umetnuli dva grada.
3080*/
3081DECLARE @IDDrzavaNovi int
3082INSERT INTO Drzava VALUES ('Kina')
3083select SCOPE_IDENTITY()
3084SET @IDDrzavaNovi = SCOPE_IDENTITY()
3085
3086INSERT INTO Grad VALUES ('�angaj', @IDDrzavaNovi)
3087INSERT INTO Grad VALUES ('Peking', @IDDrzavaNovi)
3088GO
3089-----------------------------------------------------------------------------------
3090-- Zadatak 7.
3091/*
3092PROCEDURE BEZ PARAMETARA
3093Napi�ite proceduru koja dohva�a sve retke iz tablice Drzava. 
3094Pozovite proceduru. 
3095Promijenite proceduru tako da vra�a rezultate poredane padaju�e po nazivu dr�ave. 
3096Uklonite proceduru.
3097*/
3098CREATE PROC p7
3099AS
3100SELECT * FROM Drzava
3101GO
3102
3103EXEC p7
3104GO
3105
3106ALTER PROC p7
3107AS
3108SELECT * FROM Drzava ORDER BY Naziv DESC
3109GO
3110
3111EXEC p7
3112GO
3113
3114DROP PROC p7
3115GO
3116-----------------------------------------------------------------------------------
3117-- Zadatak 8.
3118/*
3119PROCEDURE BEZ PARAMETARA
3120Napi�ite proceduru koja dohva�a prvih 5 redaka iz tablice Racun, prvih 5 redaka iz tablice Stavka i prvih 5 redaka iz tablice Proizvod. 
3121Pozovite proceduru. 
3122Uklonite proceduru.
3123*/
3124CREATE PROC p8
3125AS
3126SELECT TOP 5 * FROM Racun
3127SELECT TOP 5 * FROM Stavka
3128SELECT TOP 5 * FROM Proizvod
3129GO
3130
3131EXEC p8
3132GO
3133
3134DROP PROC p8
3135GO
3136-----------------------------------------------------------------------------------
3137-- Zadatak 9.
3138/*
3139PROCEDURE S ULAZNIM PARAMETRIMA
3140Napi�ite proceduru koja prima @ID proizvoda i vra�a samo taj proizvod iz tablice Proizvod. 
3141Pozovite proceduru na oba na�ina. 
3142Uklonite proceduru.
3143*/
3144CREATE PROC p9
3145	@IDPr int
3146AS
3147SELECT * FROM Proizvod WHERE IDProizvod = @IDPr
3148GO
3149
3150EXEC p9 1
3151EXEC p9 @IDPr = 1
3152GO
3153
3154DROP PROC p9
3155GO
3156-----------------------------------------------------------------------------------
3157-- Zadatak 10.
3158/*
3159PROCEDURE S ULAZNIM PARAMETRIMA
3160Napi�ite proceduru koja prima dvije cijene i vra�a nazive i cijene svih proizvoda �ija je cijena u zadanom rasponu. 
3161Pozovite proceduru na oba na�ina. 
3162Uklonite proceduru.
3163*/
3164CREATE PROC p10
3165	@Cijena1 money,
3166	@Cijena2 money
3167AS
3168SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV BETWEEN @Cijena1 AND @Cijena2
3169GO
3170
3171EXEC p10 500, 1000
3172EXEC p10 @Cijena1 = 500, @Cijena2 = 1000
3173EXEC p10 @Cijena2 = 1000, @Cijena1 = 500
3174GO
3175
3176DROP PROC p10
3177GO
3178-----------------------------------------------------------------------------------
3179-- Zadatak 11.
3180/*
3181PROCEDURE S ULAZNIM PARAMETRIMA
3182Napi�ite proceduru koja prima naziv dr�ave i naziv grada. 
3183Neka procedura umetne grad koji pripada zadanoj dr�avi. 
3184Pazite na to postoji li ve� dr�ava upisana u tablicu Drzava ili ne postoji. 
3185Ako postoji, nemojte je umetati ponovno. 
3186Pozovite proceduru za dr�avu �Japan� i grad �Osaka�. 
3187Pozovite proceduru za dr�avu �Japan� i grad �Tokyo� i potvrdite da radi ispravno. 
3188Uklonite proceduru.
3189*/
3190CREATE PROC p11
3191	@Drzava nvarchar(50),
3192	@Grad nvarchar(50)
3193AS
3194DECLARE @IDDrzava int
3195SELECT @IDDrzava = IDDrzava FROM Drzava WHERE Naziv = @Drzava
3196
3197IF @IDDrzava IS NOT NULL BEGIN
3198	INSERT INTO Grad VALUES (@Grad, @IDDrzava)
3199END
3200ELSE BEGIN
3201	INSERT INTO Drzava VALUES (@Drzava)
3202	SET @IDDrzava = SCOPE_IDENTITY()
3203	INSERT INTO Grad VALUES (@Grad, @IDDrzava)
3204END
3205GO
3206
3207EXEC p11 'Japan', 'Osaka'
3208SELECT * FROM Grad
3209SELECT * FROM Drzava
3210
3211EXEC p11 'Japan', 'Tokyo'
3212SELECT * FROM Grad
3213SELECT * FROM Drzava
3214GO
3215
3216DROP PROC p11
3217GO
3218-----------------------------------------------------------------------------------
3219-- Zadatak 12.
3220/*
3221PROCEDURE S IZLAZNIM PARAMETRIMA
3222Napi�ite proceduru koja prima parametre @IDProizvod i @Boja. Parametar @Boja neka bude izlazni parametar. 
3223Neka procedura za zadani proizvod vrati njegovu boju pomo�u izlaznog parametra. 
3224Pozovite proceduru i ispi�ite vra�enu vrijednost. 
3225Uklonite proceduru.
3226*/
3227CREATE PROC p12
3228	@IDProizvod int,
3229	@Boja nvarchar(15) OUTPUT
3230AS
3231SELECT @Boja = Boja FROM Proizvod WHERE IDProizvod = @IDProizvod
3232GO
3233
3234DECLARE @Farba nvarchar(15)
3235EXEC p12 320, @Farba OUTPUT
3236PRINT @Farba
3237GO
3238
3239DROP PROC p12
3240GO
3241-----------------------------------------------------------------------------------
3242-- Zadatak 13.
3243/*
3244PROCEDURE S IZLAZNIM PARAMETRIMA
3245Napi�ite proceduru koja prima kriterij po kojemu �ete filtrirati prezimena iz tablice Kupac. 
3246Neka procedura pomo�u izlaznog parametra vrati broj zapisa koji zadovoljavaju zadani kriterij. 
3247Neka procedura vrati i sve zapise koji zadovoljavaju kriterij. 
3248Pozovite proceduru i ispi�ite vra�enu vrijednost. 
3249Uklonite proceduru.
3250*/
3251CREATE PROC p13
3252	@Filter nvarchar(50),
3253	@BrojZapisa int OUTPUT
3254AS
3255SELECT @BrojZapisa = COUNT(*) FROM Kupac WHERE Prezime LIKE @Filter
3256SELECT * FROM Kupac WHERE Prezime LIKE @Filter
3257GO
3258
3259DECLARE @Cnt int
3260EXEC p13 '%hu%', @Cnt OUTPUT
3261PRINT @Cnt
3262GO
3263
3264DROP PROC p13
3265GO
3266-----------------------------------------------------------------------------------
3267-- Zadatak 14.
3268/*
3269PROCEDURE S IZLAZNIM PARAMETRIMA
3270Napi�ite proceduru koja za zadanog komercijalistu pomo�u izlaznih parametara vra�a njegovo ime 
3271i prezime te ukupnu zara�enu koli�inu novaca.
3272*/
3273CREATE PROC p14
3274	@KomercijalistID int,
3275	@Ime nvarchar(50) OUTPUT,
3276	@Prezime nvarchar(50) OUTPUT,
3277	@Zaradio money OUTPUT
3278AS
3279SELECT @Ime = Ime, @Prezime = Prezime
3280FROM Komercijalist
3281WHERE IDKomercijalist = @KomercijalistID
3282
3283SELECT @Zaradio = SUM(s.UkupnaCijena) 
3284FROM Racun AS r
3285INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
3286WHERE r.KomercijalistID = @KomercijalistID
3287GO
3288
3289DECLARE @Ime nvarchar(50), @Prezime nvarchar(50), @Zaradio money
3290EXEC p14 276, @Ime OUTPUT, @Prezime OUTPUT, @Zaradio OUTPUT
3291PRINT @Ime
3292PRINT @Prezime
3293PRINT @Zaradio
3294GO
3295
3296DROP PROC p14
3297GO
3298-----------------------------------------------------------------------------------
3299-- Zadatak 15. jedno mogu�e rje�enje (bolje).
3300/*
3301DODATNI ZADACI
3302Napi�ite proceduru koja ume�e novu kategoriju i kroz izlazni parametar vra�a generiranu IDENTITY vrijednost. 
3303Ako ve� postoji kategorija zadanog imena, ne treba je upisivati ponovno i u tom slu�aju kroz izlazni parametar vratite vrijednost -1. 
3304Pozovite proceduru i ispi�ite vra�enu vrijednost. 
3305Uklonite proceduru.
3306*/
3307CREATE PROC p15
3308	@Kategorija nvarchar(50),
3309	@ID int OUTPUT
3310AS
3311IF NOT EXISTS(SELECT * FROM Kategorija WHERE Naziv = @Kategorija) 
3312	BEGIN
3313		INSERT INTO Kategorija VALUES (@Kategorija)
3314		SET @ID = SCOPE_IDENTITY()
3315	END
3316ELSE 
3317	BEGIN
3318		SET @ID = -1
3319	END
3320GO
3321-----------------------------------------------------------------------------------
3322-- Zadatak 15. drugo mogu�e rje�enje (lo�ije).
3323/*
3324DODATNI ZADACI
3325Napi�ite proceduru koja ume�e novu kategoriju i kroz izlazni parametar vra�a generiranu IDENTITY vrijednost. 
3326Ako ve� postoji kategorija zadanog imena, ne treba je upisivati ponovno i u tom slu�aju kroz izlazni parametar vratite vrijednost -1. 
3327Pozovite proceduru i ispi�ite vra�enu vrijednost. 
3328Uklonite proceduru.
3329*/
3330CREATE PROC p15
3331	@Kategorija nvarchar(50),
3332	@ID int OUTPUT
3333AS
3334DECLARE @Broj int
3335SELECT @Broj = COUNT(*) FROM Kategorija WHERE Naziv = @Kategorija
3336
3337IF @Broj = 0 BEGIN
3338	INSERT INTO Kategorija VALUES (@Kategorija)
3339	SET @ID = SCOPE_IDENTITY()
3340END
3341ELSE BEGIN
3342	SET @ID = -1
3343END
3344GO
3345
3346DECLARE @ID int
3347EXEC p15 'Gla�ala', @ID OUTPUT
3348PRINT @ID
3349GO
3350
3351DECLARE @ID int
3352EXEC p15 'Gla�ala', @ID OUTPUT
3353PRINT @ID
3354GO
3355
3356DROP PROC p15
3357GO
3358-----------------------------------------------------------------------------------
3359-- Zadatak 16.
3360/*
3361DODATNI ZADACI
3362Napi�ite proceduru koja kroz tri izlazna parametra vra�a najmanju, najve�u i prosje�nu cijenu proizvoda iz tablice Proizvod. 
3363Neka procedura vrati i sve proizvode koji imaju cijenu ve�u od 0 i manju od prosje�ne. 
3364Pozovite proceduru, ispi�ite vra�ene vrijednosti i uklonite proceduru.
3365*/
3366CREATE PROC p16
3367	@Najmanja money OUTPUT,
3368	@Najveca money OUTPUT,
3369	@Prosjecna money OUTPUT
3370AS
3371SELECT 
3372	@Najmanja = MIN(CijenaBezPDV),
3373	@Najveca = MAX(CijenaBezPDV),
3374	@Prosjecna = AVG(CijenaBezPDV)
3375FROM Proizvod
3376
3377SELECT * FROM Proizvod WHERE CijenaBezPDV > 0 AND CijenaBezPDV < @Prosjecna
3378GO
3379
3380DECLARE @Najmanja money, @Najveca money, @Prosjecna money
3381EXEC p16 @Najmanja OUTPUT, @Najveca OUTPUT, @Prosjecna OUTPUT
3382PRINT @Najmanja
3383PRINT @Najveca
3384PRINT @Prosjecna
3385GO
3386
3387DROP PROC p16
3388GO
3389-----------------------------------------------------------------------------------
3390-- Zadatak 17.
3391/*
3392PROCEDURE S RETURN PARAMETRIMA
3393Napi�ite proceduru koja prima ime i prezime osobe 
3394i vra�a 0 kao RETURN parametar ako osoba postoji u tablici, 
3395odnosno 200 ako osoba ne postoji. 
3396Pozovite proceduru i ispi�ite RETURN vrijednost. 
3397*/
3398CREATE PROC p15
3399	@Ime nvarchar(50),
3400	@Prezime nvarchar(50)
3401AS
3402DECLARE @BrojOsoba int
3403SELECT @BrojOsoba = Count(*) FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime
3404
3405IF @BrojOsoba = 0
3406	RETURN 200
3407ELSE
3408	RETURN 0
3409GO
3410
3411DECLARE @RetVal int
3412EXEC @RetVal = p15 'Amy', 'Albertss'
3413PRINT @RetVal
3414GO
3415-----------------------------------------------------------------------------------
3416-- Zadatak 18.
3417/*
3418PROCEDURE S RETURN PARAMETRIMA
3419Promijenite proceduru iz prethodnog zadatka tako da bude za�ti�ena.
3420*/
3421ALTER PROC p15
3422	@Ime nvarchar(50),
3423	@Prezime nvarchar(50)
3424WITH ENCRYPTION
3425AS
3426DECLARE @BrojOsoba int
3427SELECT @BrojOsoba = Count(*) FROM Kupac WHERE Ime = @Ime AND Prezime = @Prezime
3428
3429IF @BrojOsoba = 0
3430	RETURN 200
3431ELSE
3432	RETURN 0
3433GO
3434-----------------------------------------------------------------------------------
3435-- Zadatak 19.
3436/*
3437PROCEDURE S RETURN PARAMETRIMA
3438Uklonite proceduru.
3439*/
3440DROP PROC p15
3441GO
3442-----------------------------------------------------------------------------------
3443
3444
3445---------------- Vjezba 7-8
3446-----------------------------------------------------------------------------------
3447-- Zadatak 1.
3448/*
3449ODGO�ENA PROVJERA REFERENCI
3450Napravite tablicu Student koja se sastoji od stupaca 
3451IDStudent, Ime, Prezime i JMBAG. 
3452Napi�ite proceduru koja vra�a ime i prezime 
3453iz te tablice i pozovite je.
3454*/
3455CREATE TABLE Student
3456(
3457	IDStudent int PRIMARY KEY IDENTITY,
3458	Ime nvarchar(50),
3459	Prezime nvarchar(50),
3460	JMBAG char(11)
3461)
3462GO
3463
3464CREATE PROC p18
3465AS
3466SELECT Ime, Prezime FROM Student
3467GO
3468
3469EXEC p18
3470GO
3471-----------------------------------------------------------------------------------
3472-- Zadatak 2.
3473/*
3474ODGO�ENA PROVJERA REFERENCI
3475Promijenite proceduru tako da uz ime i prezime 
3476vra�a i datum ro�enja (nepostoje�i stupac!). 
3477�to se desilo?
3478*/
3479ALTER PROC p18
3480AS
3481SELECT Ime, Prezime, DatumRodjenja FROM Student
3482GO
3483-----------------------------------------------------------------------------------
3484-- Zadatak 3.
3485/*
3486ODGO�ENA PROVJERA REFERENCI
3487Promijenite proceduru tako da vra�a sve zapise 
3488iz tablice IzmisljenaTablica. 
3489�to se desilo? 
3490Pokrenite proceduru. 
3491�to se desilo?
3492*/
3493ALTER PROC p18
3494AS
3495SELECT * FROM IzmisljenaTablica
3496GO
3497
3498EXEC p18
3499GO
3500-----------------------------------------------------------------------------------
3501-- Zadatak 4.
3502/*
3503ODGO�ENA PROVJERA REFERENCI
3504Napravite tablicu IzmisljenaTablica 
3505i pokrenite proceduru. 
3506�to se desilo?
3507*/
3508CREATE TABLE IzmisljenaTablica
3509(
3510	IDIzmisljenaTablica int PRIMARY KEY IDENTITY,
3511	IzmisljeniStupac nvarchar(50)
3512)
3513GO
3514
3515EXEC p18
3516GO
3517-----------------------------------------------------------------------------------
3518-- Zadatak 5.
3519/*
3520CRUD OPERACIJE
3521Svaka operacija posebno.
3522Napravite procedure koje rade CRUD operacije 
3523na tablici Student tako da svakoj operaciji 
3524dodijelite posebnu proceduru. 
3525Iskoristite procedure za umetanje, izmjenu, 
3526dohva�anje i brisanje zapisa.
3527*/
3528
3529CREATE PROC InsertStudent
3530	@IDStudent int OUTPUT, 
3531	@Ime nvarchar(50), 
3532	@Prezime nvarchar(50), 
3533	@JMBAG char(11)
3534AS
3535INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
3536SET @IDStudent = SCOPE_IDENTITY()
3537GO
3538
3539CREATE PROC UpdateStudent
3540	@IDStudent int, 
3541	@Ime nvarchar(50), 
3542	@Prezime nvarchar(50), 
3543	@JMBAG char(11)
3544AS
3545UPDATE Student 
3546SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
3547WHERE IDStudent = @IDStudent
3548GO
3549
3550CREATE PROC DeleteStudent
3551	@IDStudent int
3552AS
3553DELETE FROM Student WHERE IDStudent = @IDStudent
3554GO
3555
3556CREATE PROC GetStudent
3557	@IDStudent int
3558AS
3559SELECT * FROM Student WHERE IDStudent = @IDStudent
3560GO
3561
3562EXEC GetStudent 1
3563
3564DECLARE @NoviIDStudenta int
3565EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT, 
3566	@Ime = 'Ana', @Prezime = 'Ani�', @JMBAG = '11224451253'
3567PRINT @NoviIDStudenta
3568
3569EXEC GetStudent 1
3570
3571EXEC UpdateStudent 1, 'Ana', 'Ani� Miri�', '11224451253'
3572
3573EXEC GetStudent 1
3574
3575EXEC DeleteStudent 1
3576
3577EXEC GetStudent 1
3578GO
3579-----------------------------------------------------------------------------------
3580-- Zadatak 6.
3581/*
3582CRUD OPERACIJE
3583INSERT/UPDATE zajedno, ostalo posebno.
3584Napravite procedure koje rade CRUD operacije na tablici Student 
3585tako da operacije umetanja i izmjene obavite u jednoj proceduri, 
3586a druge dvije operacije obavite u posebnim procedurama. 
3587Iskoristite procedure za umetanje, izmjenu, dohva�anje i brisanje zapisa.
3588*/
3589CREATE PROC MergeStudent
3590	@IDStudent int OUTPUT, 
3591	@Ime nvarchar(50), 
3592	@Prezime nvarchar(50), 
3593	@JMBAG char(11)
3594AS
3595IF Exists(SELECT * FROM Student WHERE IDStudent = @IDStudent)
3596	UPDATE Student 
3597	SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
3598	WHERE IDStudent = @IDStudent
3599ELSE
3600	INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
3601	SET @IDStudent = SCOPE_IDENTITY()
3602GO
3603
3604CREATE PROC DeleteStudent
3605	@IDStudent int
3606AS
3607DELETE FROM Student WHERE IDStudent = @IDStudent
3608GO
3609
3610CREATE PROC GetStudent
3611	@IDStudent int
3612AS
3613SELECT * FROM Student WHERE IDStudent = @IDStudent
3614GO
3615
3616EXEC GetStudent 1
3617
3618DECLARE @NoviIDStudenta int
3619EXEC MergeStudent @NoviIDStudenta OUTPUT, 'Ana', 'Ani�', '11224451253'
3620PRINT @NoviIDStudenta
3621
3622EXEC GetStudent 2
3623
3624EXEC MergeStudent 2, 'Ana', 'Ani� Miri�', '11224451253'
3625
3626EXEC GetStudent 2
3627
3628EXEC DeleteStudent 2
3629
3630EXEC GetStudent 2
3631GO
3632-----------------------------------------------------------------------------------
3633-- Zadatak 7.
3634/*
3635CRUD OPERACIJE
3636UPDATE/INSERT/DELETE zajedno.
3637Napravite procedure koje rade CRUD operacije na tablici Student 
3638tako da operacije umetanja, izmjene i brisanja obavite u jednoj proceduri, 
3639a dohva�anje u drugoj. 
3640Iskoristite procedure za umetanje, izmjenu, dohva�anje i brisanje zapisa.
3641*/
3642CREATE PROC ChangeStudent
3643	@Operacija char(1),
3644	@IDStudent int OUTPUT, 
3645	@Ime nvarchar(50), 
3646	@Prezime nvarchar(50), 
3647	@JMBAG char(11)
3648AS
3649IF @Operacija = 'U'
3650	UPDATE Student 
3651	SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
3652	WHERE IDStudent = @IDStudent
3653ELSE IF @Operacija = 'I' BEGIN
3654	INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
3655	SET @IDStudent = SCOPE_IDENTITY()
3656END
3657ELSE IF @Operacija = 'D'
3658	DELETE FROM Student WHERE IDStudent = @IDStudent
3659GO
3660
3661CREATE PROC GetStudent
3662	@IDStudent int
3663AS
3664SELECT * FROM Student WHERE IDStudent = @IDStudent
3665GO
3666
3667EXEC GetStudent 3
3668
3669DECLARE @NoviIDStudenta int
3670EXEC ChangeStudent 'I', @NoviIDStudenta OUTPUT, 'Ana', 'Ani�', '11224451253'
3671PRINT @NoviIDStudenta
3672
3673EXEC GetStudent 3
3674
3675EXEC ChangeStudent 'U', 3, 'Ana', 'Ani� Miri�', '11224451253'
3676
3677EXEC GetStudent 3
3678
3679EXEC ChangeStudent 'D', 3, null, null, null
3680
3681EXEC GetStudent 3
3682GO
3683-----------------------------------------------------------------------------------
3684-- Zadatak 8.
3685/*
3686PRETVARANJE TIPOVA PODATAKA
3687Dohvatite brojeve ra�una i datume izdavanja za kupca s ID-em 378.
3688Datume izdavanja formatirajte na hrvatski na�in.
3689*/
3690SELECT	
3691	BrojRacuna,
3692	CONVERT(char(10), DatumIzdavanja, 104) AS DatumIzdavanja
3693FROM Racun WHERE KupacID = 378
3694GO
3695-----------------------------------------------------------------------------------
3696-- Zadatak 9.
3697/*
3698PRETVARANJE TIPOVA PODATAKA
3699Napi�ite proceduru za umetanje zapisa u tablicu Drzava. 
3700Neka procedura kroz izlazni parametar vrati vrijednost primarnog klju�a novog zapisa. 
3701Pozovite proceduru i ispi�ite vra�enu vrijednost u formatu: 
3702�Umetnuta je dr�ava s ID-em n�, gdje je n vrijednost primarnog klju�a.
3703*/
3704CREATE PROC p1
3705	@Naziv nvarchar(50),
3706	@ID int OUTPUT
3707AS
3708INSERT INTO Drzava (Naziv) VALUES (@Naziv)
3709SET @ID = SCOPE_IDENTITY()
3710GO
3711
3712DECLARE @n int
3713EXEC p1 'Gruzija', @n OUTPUT
3714PRINT 'Umetnuta je dr�ava s ID-em ' + CAST(@n AS nvarchar(10))
3715GO
3716-----------------------------------------------------------------------------------
3717-- Zadatak 10.
3718/*
3719PRETVARANJE TIPOVA PODATAKA
3720Dohvatite nazive svih proizvoda i uz svaki naziv u zagradi 
3721ispi�ite i njegov ID te cijenu, 
3722npr. �HL Road Rear Wheel (ID = 828, Cijena = 357.06)�
3723*/
3724SELECT 
3725	Naziv + ' (ID = ' + CAST(IDProizvod AS nvarchar(50)) + ', Cijena = ' + CAST(CijenaBezPDV AS nvarchar(50)) + ')' AS Naziv
3726FROM dbo.Proizvod
3727-----------------------------------------------------------------------------------
3728-- Zadatak 11.
3729/*
3730NAREDBA CASE I WHILE
3731Dohvatite nazive svih proizvoda i uz svaki naziv ispi�ite i naziv potkategorije. 
3732Ako neke potkategorije nema, napi�ite �Nije definirana�.
3733*/
3734SELECT 
3735	p.Naziv,
3736	CASE
3737		WHEN pk.Naziv IS NULL THEN 'Nije definirana'
3738		ELSE pk.Naziv
3739	END AS NazivPotkategorije
3740FROM Proizvod AS p
3741LEFT JOIN Potkategorija AS pk ON p.PotkategorijaID = pk.IDPotkategorija
3742-----------------------------------------------------------------------------------
3743-- Zadatak 12.
3744/*
3745NAREDBA CASE I WHILE
3746Dohvatite naziv i cijene svih proizvoda. 
3747Za cijene koje su ispod 1000, napi�ite �Jeftino�, izme�u 1000 i 2000 
3748napi�ite �Prihvatljivo�, za sve ostale napi�ite �Skupo�.
3749*/
3750SELECT 
3751	Naziv,
3752	CASE
3753		WHEN CijenaBezPDV < 1000 THEN 'Jeftino'
3754		WHEN CijenaBezPDV BETWEEN 1000 AND 2000 THEN 'Prihvatljivo'
3755		ELSE 'Skupo'
3756	END AS Procjena
3757FROM Proizvod
3758GO
3759-----------------------------------------------------------------------------------
3760-- Zadatak 13.
3761/*
3762NAREDBA CASE I WHILE
3763Napravite tablicu Proba sa stupcima IDProba (primarni klju� i IDENTITY) i Vrijednost (int). 
3764U stupac Vrijednost unesite vrijednosti izme�u 10.000.000 i 10.015.000.
3765*/
3766CREATE TABLE Proba ( IDProba int PRIMARY KEY IDENTITY, Vrijednost int )
3767GO
3768
3769DECLARE @i int = 10000000
3770WHILE @i <= 10015000 BEGIN
3771	INSERT INTO Proba VALUES (@i)
3772	SET @i += 1
3773END
3774
3775SELECT * FROM Proba
3776GO
3777-----------------------------------------------------------------------------------
3778/*
3779Svaka gre�ka u SQL Serveru ima svoju razinu ozbiljnosti
3780(engl. severity level):
3781	o Razine 1 do 10 predstavljaju informacije
3782	o Razine 11 do 16 predstavljaju korisni�ke gre�ke
3783		� 11 � tra�eni objekt ne postoji (npr. DROP TABLE NemaMe)
3784		� 15 � upit nema ispravnu sintaksu (npr. SELECT * FRMO Student)
3785		� 16 � sve ostale korisni�ke gre�ke (npr. PRINT 17/0)
3786	o Razine 17 do 19 predstavljaju softverske gre�ke koje zahtijevaju
3787		intervenciju administratora
3788		� 17 � nema prostora na disku za tra�enu operaciju
3789	o Razine 20 do 25 predstavljaju fatalne gre�ke i mogu rezultirati
3790		zatvaranjem konekcije
3791T-SQL nudi mogu�nost strukturiranog hvatanja gre�aka
3792	o Hvataju se gre�ke razine 11 do 19 (1-10 i 20-25 se ne hvataju)
3793*/
3794-----------------------------------------------------------------------------------
3795-- Zadatak 14.
3796/*
3797STRUKTURIRANO HVATANJE POGRE�AKA
3798Napravite proceduru koja prima dva broja i kroz izlazni parametar 
3799vra�a prvi broj podijeljen drugim. 
3800Ako se desi gre�ka, neka procedura u izlazni parametar upi�e 0 
3801i neka ispi�e tekst gre�ke. 
3802Pozovite proceduru i ispi�ite rezultat dijeljenja.
3803*/
3804create PROC pp2
3805	@a int,
3806	@b int,
3807	@c int OUTPUT
3808AS
3809BEGIN TRY
3810	SET @c = @a / @b
3811END TRY
3812BEGIN CATCH
3813	SET @c = 0
3814	PRINT ERROR_MESSAGE()
3815END CATCH
3816GO
3817
3818DECLARE @Rez int
3819EXEC pp2 32, 4, @Rez OUTPUT
3820PRINT @Rez
3821
3822EXEC pp2 32, 0, @Rez OUTPUT
3823PRINT @Rez
3824GO
3825-----------------------------------------------------------------------------------
3826-- Zadatak 15.
3827/*
3828STRUKTURIRANO HVATANJE POGRE�AKA
3829Napravite tablicu Zivotinja koja ima stupce IDZivotinja 
3830(primarni klju�, ali nije IDENTITY) i Naziv. 
3831Napravite proceduru koja prima IDZivotinja i Naziv i ume�e ih u tablicu. 
3832Pozovite proceduru dva puta s vrijednostima 20 i "�aplja".
3833Implementirajte TRY/CATCH izvan procedure i pozovite je.
3834Implementirajte TRY/CATCH unutar procedure i pozovite je.
3835*/
3836CREATE TABLE Zivotinja 
3837(
3838	IDZivotinja int PRIMARY KEY,
3839	Naziv nvarchar(50)
3840)
3841GO
3842
3843CREATE PROC InsertZivotinja 
3844	@IDZivotinja int,
3845	@Naziv nvarchar(50)
3846AS
3847INSERT INTO Zivotinja (IDZivotinja, Naziv) VALUES (@IDZivotinja, @Naziv)
3848GO
3849
3850EXEC InsertZivotinja 20, '�aplja'
3851EXEC InsertZivotinja 20, '�aplja'
3852GO
3853
3854BEGIN TRY
3855	EXEC InsertZivotinja 20, '�aplja'
3856	EXEC InsertZivotinja 20, '�aplja'
3857END TRY
3858BEGIN CATCH
3859	PRINT 'Error message: ' + cast(ERROR_MESSAGE() as nvarchar(100))
3860	PRINT 'Error number: ' + cast(ERROR_NUMBER() as nvarchar(100))
3861	PRINT 'Error severity: ' + cast(ERROR_SEVERITY() as nvarchar(100))
3862	PRINT 'Error line ' + cast(ERROR_LINE() as nvarchar(100))
3863	PRINT 'Error procedure: ' + cast(ERROR_PROCEDURE() as nvarchar(100))
3864END CATCH
3865GO
3866
3867ALTER PROC InsertZivotinja 
3868	@IDZivotinja int,
3869	@Naziv nvarchar(50)
3870AS
3871BEGIN TRY
3872	INSERT INTO Zivotinja (IDZivotinja, Naziv) VALUES (@IDZivotinja, @Naziv)
3873END TRY
3874BEGIN CATCH
3875	PRINT 'Desila se gre�ka: ' + ERROR_MESSAGE()
3876	PRINT '�ivotinja nije upisana.'
3877END CATCH
3878GO
3879
3880EXEC InsertZivotinja 20, '�aplja'
3881GO
3882-----------------------------------------------------------------------------------
3883-- Zadatak 16.
3884/*
3885SKALARNE FUNKCIJE
3886Napi�ite funkciju koja prima ID proizvoda i dohva�a broj prodanih primjeraka. 
3887Pozovite funkciju samostalno. 
3888Dohvatite nazive i boje svih proizvoda i uz svaki proizvoda 
3889ispi�ite koliko primjeraka je prodano. 
3890Promijenite funkciju tako da vrati 0 za one proizvode koji nisu prodani 
3891niti u jednom primjerku. 
3892Uklonite funkciju.
3893*/
3894create FUNCTION UkupnaKolicina
3895(
3896	@ID int
3897)
3898RETURNS int
3899AS
3900BEGIN
3901	DECLARE @Ukupno int
3902	SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID
3903	RETURN @Ukupno
3904END
3905GO
3906
3907DECLARE @Rez int
3908SET @Rez = dbo.UkupnaKolicina(776)
3909PRINT @Rez
3910
3911SELECT
3912	Naziv,
3913	Boja,
3914	dbo.UkupnaKolicina(IDProizvod) AS Prodano
3915FROM Proizvod
3916GO
3917
3918ALTER FUNCTION UkupnaKolicina
3919(
3920	@ID int
3921)
3922RETURNS int
3923AS
3924BEGIN
3925	DECLARE @Ukupno int
3926
3927	SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID
3928
3929	RETURN CASE
3930				WHEN @Ukupno IS NOT NULL THEN @Ukupno
3931				ELSE 0
3932			END
3933END
3934GO
3935
3936SELECT
3937	Naziv,
3938	Boja,
3939	dbo.UkupnaKolicina(IDProizvod) AS Prodano
3940FROM Proizvod
3941GO
3942-----------------------------------------------------------------------------------
3943-- Zadatak 17.
3944/*
3945SKALARNE FUNKCIJE
3946Napi�ite funkciju koja prima string. 
3947Ako je broj znakova u stringu manji od 10, neka funkcija vrati ulazni string. 
3948Ako ne, neka vrati prvih 7 znakova i iza toga tri to�ke. 
3949Ispi�ite nazive svih proizvoda koriste�i napravljenu funkciju.
3950*/
3951CREATE FUNCTION Skrati
3952(
3953	@s nvarchar(max)
3954)
3955RETURNS nvarchar(10)
3956AS
3957BEGIN
3958	RETURN CASE
3959		WHEN LEN(@s) <= 10 THEN @s
3960		ELSE SUBSTRING(@s, 1, 7) + '...'
3961	END
3962END
3963GO
3964
3965PRINT dbo.Skrati('Zvonko')
3966PRINT dbo.Skrati('Zvonko Telefonko')
3967
3968SELECT 
3969	Naziv,
3970	dbo.Skrati(Naziv) AS NazivSkraceni
3971FROM Proizvod
3972GO
3973-----------------------------------------------------------------------------------
3974-- Zadatak 18.
3975/*
3976SKALARNE FUNKCIJE
3977Napi�ite funkciju koja za zadanog kupca vra�a datum najnovije kupovine. 
3978Ispi�ite sve kupce i kraj svakog ispi�ite datum najnovije kupovine. 
3979Ako treba, optimizirajte!
3980*/
3981CREATE FUNCTION GetNajnoviji
3982(
3983	@IDKupac int
3984)
3985RETURNS datetime
3986AS
3987BEGIN
3988	DECLARE @Datum datetime
3989	
3990	SELECT TOP 1 @Datum = DatumIzdavanja FROM dbo.Racun
3991	WHERE KupacID = @IDKupac
3992	ORDER BY DatumIzdavanja DESC
3993	
3994	RETURN @Datum
3995END
3996GO
3997
3998SELECT 
3999	*,
4000	dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
4001FROM Kupac
4002
4003CREATE NONCLUSTERED INDEX i1 ON dbo.Racun(KupacID) INCLUDE (DatumIzdavanja)
4004GO
4005-----------------------------------------------------------------------------------
4006-- Zadatak 19.
4007/*
4008JEDNOSTAVNE TABLI�NE FUNKCIJE
4009Napi�ite jednostavnu tabli�nu funkciju koja vra�a IDKupac, ime i prezime svih osoba �ije prezime zapo�inje sa zadanim stringom. 
4010Iskoristite funkciju za dohvat svih osoba �ije prezime zapo�inje sa 'Zhu'. 
4011Uz svaku osobu dohvatite i njegove ra�une.
4012*/
4013CREATE FUNCTION DohvatiOsobe
4014(
4015    @PrezimeLike nvarchar(50)
4016)
4017RETURNS TABLE
4018AS
4019RETURN
4020    SELECT IDKupac, Ime, Prezime
4021    FROM Kupac
4022    WHERE Prezime LIKE @PrezimeLike + '%'
4023GO
4024
4025SELECT * FROM DohvatiOsobe('Zhu')
4026
4027SELECT * 
4028FROM DohvatiOsobe('Zhu') AS os
4029INNER JOIN Racun AS r ON os.IDKupac = r.KupacID
4030GO
4031-----------------------------------------------------------------------------------
4032-- Zadatak 20.
4033/*
4034JEDNOSTAVNE TABLI�NE FUNKCIJE
4035Napi�ite jednostavnu tabli�nu funkciju koja prima dva datuma. 
4036Neka funkcija vrati broj ra�una, datum izdavanja i ime i prezime kupca za sve ra�une izdane izme�u zadanih datuma. 
4037Iskoristite funkciju za dohvat ra�una izme�u 01.06.2004. i 03.06.2004.
4038Promijenite funkciju da datum vrati u hrvatskom formatu.
4039*/
4040CREATE FUNCTION DohvatiRacune
4041(
4042    @D1 datetime, 
4043    @D2 datetime
4044)
4045RETURNS TABLE
4046AS
4047RETURN
4048	SELECT r.BrojRacuna, r.DatumIzdavanja, k.Ime, k.Prezime
4049	FROM Racun AS r
4050	LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
4051	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
4052GO
4053
4054SELECT * FROM DohvatiRacune('20040601', '20040603')
4055GO
4056
4057ALTER FUNCTION DohvatiRacune
4058(
4059    @D1 datetime, 
4060    @D2 datetime
4061)
4062RETURNS TABLE
4063AS
4064RETURN
4065	SELECT r.BrojRacuna, CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja, k.Ime, k.Prezime
4066	FROM Racun AS r
4067	LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
4068	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
4069GO
4070
4071SELECT * FROM DohvatiRacune('20040601', '20040603')
4072GO
4073-----------------------------------------------------------------------------------
4074-- Zadatak 21.
4075/*
4076SLO�ENE TABLI�NE FUNKCIJE
4077Napi�ite slo�enu tabli�nu funkciju koja se pona�a kao funkcija iz zadatka 2.
4078PODSJETNIK na zadatak 2:
4079Napi�ite slo�enu  tabli�nu funkciju koja prima dva datuma. 
4080Neka funkcija vrati broj ra�una, datum izdavanja i ime i prezime kupca za sve ra�une izdane izme�u zadanih datuma. 
4081Iskoristite funkciju za dohvat ra�una izme�u 01.06.2004. i 03.06.2004.
4082*/
4083CREATE FUNCTION DohvatiRacuneSloz
4084(
4085    @D1 datetime, 
4086    @D2 datetime
4087)
4088RETURNS @RetVal TABLE ( BrojRacuna nvarchar(25), DatumIzdavanja datetime, Ime nvarchar(50), Prezime nvarchar(50) )
4089AS
4090BEGIN
4091	INSERT INTO @RetVal (BrojRacuna, DatumIzdavanja, Ime, Prezime)
4092	SELECT r.BrojRacuna, r.DatumIzdavanja, k.Ime, k.Prezime
4093	FROM Racun AS r
4094	LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
4095	WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
4096	
4097	RETURN
4098END
4099GO
4100
4101SELECT * FROM DohvatiRacuneSloz('20040601', '20040603')
4102GO
4103-----------------------------------------------------------------------------------
4104-- Zadatak 22.
4105/*
4106SLO�ENE TABLI�NE FUNKCIJE
4107Napi�ite slo�enu tabli�nu funkciju koja prima cijenu. 
4108Ako je cijena NULL, vratite nazive i cijene svih proizvoda iz tablice Proizvod. 
4109Ako nije, vratite nazive i cijene samo onih proizvoda �ija cijena je ve�a od zadane cijene. 
4110Iskoristite funkciju s NULL i s cijenom od 3000.
4111*/
4112CREATE FUNCTION F4
4113(
4114	@Cijena money
4115)
4116RETURNS @rez TABLE ( Naziv nvarchar(50), Cijena money )
4117AS
4118BEGIN
4119	IF @Cijena IS NULL BEGIN
4120		INSERT INTO @rez (Naziv, Cijena)
4121		SELECT Naziv, CijenaBezPDV FROM Proizvod
4122	END
4123	ELSE BEGIN
4124		INSERT INTO @rez (Naziv, Cijena)
4125		SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV > @Cijena
4126	END
4127	
4128	RETURN
4129END
4130GO
4131
4132SELECT * FROM F4(NULL)
4133SELECT * FROM F4(3000)
4134GO
4135
4136
4137/* Zadatak 23. SLO�ENE TABLI�NE FUNKCIJE
4138Napi�ite slo�enu tabli�nu funkciju koja prima jedan datum i koja vra�a tablicu 
4139koja se sastoji od jednog stupca i koja sadr�ava sljede�ih 5 datuma. 
4140Primjerice, ako je zadan 03.12.2011, funkcija treba vratiti 04.12., 05.12, 06.12, 07.12, 08.12.
4141*/
4142CREATE FUNCTION GetDatume
4143(
4144	@Datum datetime
4145)
4146RETURNS @RetVal TABLE ( Datum datetime )
4147AS
4148BEGIN
4149	DECLARE @i int = 1
4150	WHILE @i <= 5 BEGIN
4151		INSERT INTO @RetVal (Datum)
4152		VALUES (Dateadd(day, @i, @Datum))
4153		
4154		SET @i += 1
4155	END
4156	
4157	RETURN
4158END
4159GO
4160
4161SELECT * FROM GetDatume(GETDATE())
4162SELECT * FROM GetDatume('20111229')
4163GO