2mu9nEbb

1--I6 ZD3
2GO
3
4CREATE PROC UmetniKreditnu(@xml XML)
5AS
6
7DECLARE @tip NVARCHAR(50)
8DECLARE @broj NVARCHAR(25)
9DECLARE @istekMjesec TINYINT
10DECLARE @istekGodina SMALLINT
11
12SELECT
13@tip = stupac.value('@Tip', 'NVARCHAR(50)'),
14@istekMjesec = stupac.value('@IstekMjesec', 'TINYINT'),
15@istekGodina = stupac.value('@IstekGodina', 'SMALLINT'),
16@broj = stupac.value('.', 'NVARCHAR(25)')
17FROM @xml.nodes('/KreditnaKartica/KarticaBroj') AS a(stupac)
18
19IF EXISTS(SELECT * FROM KreditnaKartica WHERE Broj = @broj AND Tip = @tip)
20	UPDATE KreditnaKartica SET 
21		IstekMjesec = @istekMjesec,
22		IstekGodina = @istekGodina	
23	WHERE Broj = @broj AND Tip = @tip
24ELSE
25	INSERT INTO KreditnaKartica(Broj, Tip, IstekMjesec, IstekGodina)
26	VALUES (@broj, @tip, @istekMjesec, @istekGodina)
27
28GO
29
30DECLARE @procXML XML = '<KreditnaKartica>
31<KarticaBroj Tip="Visa" IstekMjesec="5" IstekGodina="2014">77776411189527</KarticaBroj>
32</KreditnaKartica>'
33
34EXEC UmetniKreditnu @procXML
35
36--I6 zd4
37CREATE TYPE Gradovi AS TABLE
38(
39	Naziv NVARCHAR(50),
40	DrzavaID INT
41)
42
43GO
44
45CREATE PROC UnesiGradove (@gradovi Gradovi READONLY)
46AS
47INSERT INTO Grad
48SELECT * FROM @gradovi
49
50GO
51
52DECLARE @gradovi Gradovi
53
54INSERT INTO @gradovi
55VALUES ('G1', 1), ('G2', 2), ('G3', 3)
56
57EXEC UnesiGradove @gradovi
58
59--I7 ZD5
60CREATE PROC UkloniKupca (@IDKupac INT)
61AS
62DELETE FROM Stavka WHERE RacunID IN (SELECT r.IDRacun FROM Racun AS r WHERE r.KupacID = @IDKupac)
63DELETE FROM Racun WHERE KupacID = @IDKupac
64DELETE FROM Kupac WHERE IDKupac = @IDKupac
65
66GO
67
68BEGIN TRAN
69
70BEGIN TRY
71
72EXEC UkloniKupca 30
73EXEC UkloniKupca 31
74EXEC UkloniKupca 32
75
76PRINT 'Uspjeh'
77COMMIT
78
79END TRY
80BEGIN CATCH
81
82PRINT 'Neuspjeh'
83ROLLBACK
84
85END CATCH
86
87--I7 ZD6
88
89--1.
90SET TRANSACTION ISOLATION LEVEL READ COMMITTED
91
92--3.
93BEGIN TRAN
94
95SELECT 
96*
97FROM KreditnaKartica AS k
98WHERE k.Broj LIKE '%929'
99
100WAITFOR DELAY '00:00:10'
101
102SELECT 
103*
104FROM KreditnaKartica AS k
105WHERE k.Broj LIKE '%929'
106
107--5.
108COMMIT
109
110-------------------------DRUGI QUERY-----------------------------------
111
112--2.
113SET TRANSACTION ISOLATION LEVEL READ COMMITTED
114
115--4.
116BEGIN TRAN
117
118INSERT INTO KreditnaKartica(Tip, Broj, IstekGodina, IstekMjesec)
119VALUES('tip', '12929', 2014, 12)
120COMMIT
121
122------------------------------------------------------------------------
123
124--problem možemo riješiti stavljanjem transaction isolation levela na Serializable
125
126
127
128
129
130
131-- 9 -- maknite sve nk indekse sa dbo.Proizvod, pa pitanja
132-- P: na koliko stranica su smješteni podaci iz tablice Proizvod?
133-- O: na 6 podatkovnih stranica (i ima jedna indeks stranica)
134dbcc traceon(3604)
135dbcc ind([AdventureWorksOBP], [Proizvod], -1)
136-- P: na prvoj podatkovnoj stranici, koji je IDProizvod za 1 i zadnji proizvod na 1. stranici?
137-- O: 1. je 1, zadnji je 425
138dbcc page(AdventureWorksOBP, 1, 178, 3) with tableresults
139-- P: na kojoj stranici se nalazi proizvod s ID-jem 777?
140-- O: nalazi se na stranici 183 (slot 70)
141dbcc page(AdventureWorksOBP, 1, 183, 3) with tableresults
142-- P: umetnite novi proizvod, na koju je stranicu dodan?
143-- O: budući da se sve sprema prema primarnom ključu koji se sam generira inkrementom, proizvod
144-- je dodan na zadnju stranicu (189) u slot 63
145insert into Proizvod (Naziv, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV)
146values ('Mačje Oko', 'PR0907','Roza', 2, 12)
147dbcc page(AdventureWorksOBP, 1, 189, 3) with tableresults
148
149-- 10 -- Optimizirati upit
150set statistics io on
151
152select
153	IDKreditnaKartica,
154	Broj,
155	Tip
156from KreditnaKartica
157where Tip like 'Master%'
158	and IstekGodina = 2007
159-- Scan count 1, logical reads 164
160
161--create nonclustered index index1 on KreditnaKartica(Tip)
162--include(Broj, IstekGodina)
163---- Scan count 1, logical reads 43
164
165--drop index index1 on KreditnaKartica
166
167--create nonclustered index index2 on KreditnaKartica(Broj)
168--include(Tip, IstekGodina)
169---- Scan count 1, logical reads 155
170
171--drop index index2 on KreditnaKartica
172
173create nonclustered index index3 on KreditnaKartica(IstekGodina)
174include(Tip, Broj)
175-- Scan count 1, logical reads 42
176
177-- INDEX 3 JE NAJJAÄŒI
178
179------------------------------------------ ISHOD 4 ------------------------------------------
180go
181-- 11 -- procedura koja prima ime i prezime komercijalista i ubacuje novoga, koroz izlazni parametar
182-- vraća novonastali id, odnosno -1 ukoliko isti već postoji (pa ga se ni ne mora ubacivat)
183
184create proc UbaciKomarcijalista
185	@ime nvarchar(50),
186	@prezime nvarchar(50),
187	@idKomercijalista int output
188as
189if exists (select IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime) begin
190	set @idKomercijalista = -1
191end
192else begin
193	insert into Komercijalist (Ime, Prezime)
194	values (@ime, @prezime)
195	set @idKomercijalista = SCOPE_IDENTITY()
196end
197go
198
199declare @idKomercijalista int
200execute UbaciKomarcijalista 'Isus', 'Krist', @idKomercijalista output
201print @idKomercijalista
202execute UbaciKomarcijalista 'Isus', 'Krist', @idKomercijalista output
203print @idKomercijalista
204go
205
206-- 12 -- procedura prima ID proizvoda, provjerava postoji li i je li kad prodan, preko return mora vratit
207-- sljedeće vrijednosti -1 (ne postoji), 0 (postoji, nikad prodan) 1 (postoji i prodavan n puta)
208
209create proc ProvjeriProizvod
210	@idProizvod int
211as
212if not exists (select Naziv from Proizvod where IDProizvod = @idProizvod) begin
213	return -1
214end
215if @idProizvod IN (select
216						p.IDProizvod
217					from Proizvod as p
218					where (select sum(Kolicina) from Stavka where ProizvodID = p.IDProizvod) IS NULL)
219begin
220	return 0
221end
222	return 1
223go
224
225
226declare @ajdi int = 1001
227declare @rezultat int
228-- postojeći, nikad prodani proizvod
229exec @rezultat = ProvjeriProizvod @ajdi
230print @rezultat
231-- postojeći prodavani proizvod
232set @ajdi = 707
233exec @rezultat = ProvjeriProizvod @ajdi
234print @rezultat
235-- nepostojeći proizvod
236set @ajdi = 20000
237exec @rezultat = ProvjeriProizvod @ajdi
238print @rezultat
239go
240
241select * from proizvod
242where (select sum(Kolicina) from Stavka where ProizvodID = IDProizvod) IS NOT NULL
243
244-- 13 -- implementirajte CRUD na Potkategorija s 2 procedure (C,U) i (R,D)
245-- napravite umetanje, izmjenu, dohvaćanje i brisanje jedne potkategorije
246
247go
248-- output varijabla je opcionalno i input varijabla, ak postoji promijeni, ak ne postoji inserta i u output da taj novi id
249create proc CU_Potkategorija
250	@idPotkategorije int output,
251	@naziv nvarchar(50),
252	@idKategorija int
253as
254if exists (select * from Potkategorija where IDPotkategorija = @idPotkategorije) begin
255	update Potkategorija
256	set Naziv = @naziv, KategorijaID = @idKategorija
257	where IDPotkategorija = @idPotkategorije
258end
259else begin
260	insert into Potkategorija (Naziv, KategorijaID)
261	values (@naziv, @idKategorija)
262	set @idPotkategorije = SCOPE_IDENTITY()
263end
264go
265
266-- demonstracija, prvo napravi pa updatea
267declare @noviAjdi int
268exec CU_Potkategorija @noviAjdi output, 'ZaPuknutLanac',4
269print @noviAjdi
270select * from Potkategorija where IDPotkategorija = @noviAjdi
271
272exec CU_Potkategorija @noviAjdi, 'KljucZaLanac', 4
273select * from Potkategorija where IDPotkategorija = @noviAjdi
274
275-- i još jedna procedura, ja sam sad tu stavila da je defaultno operacija R (valjda kao read oliti select ili get), ak se napise R ili nis, bu bilo R,
276-- za delete se bas mora navest D
277go
278create proc RD_Potkategorija
279	@ajdiPostojeci int,
280	@operacija char = 'R'
281as
282if @operacija = 'R' begin
283	select
284		*
285	from Potkategorija
286	where IDPotkategorija = @ajdiPostojeci
287end
288else if @operacija = 'D' begin
289	print 'Brise se potkategorija: ' + (select Naziv from Potkategorija where IDPotkategorija = @ajdiPostojeci)
290	delete from Potkategorija
291	where IDPotkategorija = @ajdiPostojeci
292end
293go
294
295declare @ajdiPostojeci int
296set @ajdiPostojeci = (select IDPotkategorija from Potkategorija where Naziv = 'KljucZaLanac')
297select * from Potkategorija where IDPotkategorija = @ajdiPostojeci
298exec RD_Potkategorija @ajdiPostojeci
299exec RD_Potkategorija @ajdiPostojeci, 'R'
300exec RD_Potkategorija @ajdiPostojeci, 'D'
301select * from Potkategorija where IDPotkategorija = @ajdiPostojeci