· 10 years ago · Sep 27, 2016, 05:40 AM
1--1
2select k.Ime, k.Prezime, d.Naziv from Kupac as k inner join Grad as g
3on g.IDGrad = k.GradID inner join Drzava as d on d.IDDrzava = g.DrzavaID
4where (k.Telefon like '% %' or k.Telefon like '%-%') and d.Naziv = 'Hrvatska'
5
6--2
7select p.Naziv, sum(s.Kolicina) as kolicina from Proizvod as p inner join Stavka as s
8on p.IDProizvod = s.ProizvodID inner join Racun as r
9on r.IDRacun = s.RacunID inner join Kupac as k on k.IDKupac = r.KupacID
10inner join Grad as g on g.IDGrad = k.GradID inner join Drzava as d
11on d.IDDrzava = g.DrzavaID where d.Naziv='Bosna i Hercegovina'
12group by p.Naziv order by kolicina asc
13
14--3
15insert into Drzava(Naziv) values('Canada')
16insert into Grad(Naziv, DrzavaID) values('Montreal', 4)
17insert into Kupac(Ime, Prezime, GradID) values('Leonardo', 'Cohen', 14)
18
19--4
20
21create table NajprodavanijiProizvodi(
22IDProizvod int not null,
23Naziv nvarchar(50) not null,
24ProdanihKomada int not null
25)
26
27select top 10 p.Naziv, sum(s.Kolicina) as kolicina from Proizvod as p inner join Stavka as s
28on p.IDProizvod = s.ProizvodID
29group by p.Naziv order by kolicina desc
30
31insert into NajprodavanijiProizvodi(IDProizvod, Naziv, ProdanihKomada)
32select top 10 1, p.Naziv, sum(s.Kolicina) as kolicina from Proizvod as p inner join Stavka as s
33on p.IDProizvod = s.ProizvodID
34group by p.Naziv order by kolicina desc
35
36--5
37create table dostavljac(
38IDdostavljac int not null primary key,
39Ime nvarchar(50) not null,
40prezime nvarchar(50) not null,
41oib int not null,
42unique(oib)
43)
44
45create table pizza(
46IDpizza int not null primary key,
47naziv nvarchar(50) not null,
48cijena int not null check(cijena >20 and cijena <40)
49)
50
51create table dostava(
52IDdostava int not null primary key,
53datumdostave date not null,
54dostavljacID int not null foreign key references dostavljac (IDdostavljac),
55pizzaID int not null foreign key references pizza (IDpizza)
56)
57
58--6
59create view mjeseci
60as
61select top 99.99 percent MONTH(r.DatumIzdavanja) as mjeseci, count(*) as broj from Racun as r
62group by month(r.DatumIzdavanja)
63order by mjeseci asc
64go
65
66select * from mjeseci
67drop view mjeseci
68
69--7
70
71create view imeiprezime
72as
73select k.Ime, k.Prezime
74from Kupac as k where (select count(*) from Racun as r where r.KupacID = k.IDKupac)> 3
75go
76
77select * from imeiprezime
78
79alter view imeiprezime
80as
81select k.Ime, k.Prezime,(select count(*) from Racun as r where r.KupacID = k.IDKupac)as broj
82from Kupac as k where (select count(*) from Racun as r where r.KupacID = k.IDKupac)> 3
83go
84
85select * from imeiprezime
86
87drop view imeiprezime
88
89--8
90create view proizvodview
91as
92select p.Naziv from Proizvod as p
93where p.Boja is not null and p.CijenaBezPDV >= 1000
94with check option
95go
96
97select * from proizvodview
98
99drop view proizvodview
100
101--9
102create view kartice
103as
104select cast(kk.IstekGodina as varchar(5))+'-'+ CAST(kk.IstekMjesec as varchar(5)) as godina_i_mjesec, COUNT(*) as broj_kartica
105from KreditnaKartica as kk where kk.Tip='Visa'
106group by kk.IstekGodina, kk.IstekMjesec
107go
108
109--provjera
110select * from KreditnaKartica as kk where kk.Tip ='Visa' and kk.IstekGodina=2006
111and kk.IstekMjesec=5
112
113alter view kartice
114with schemabinding
115as
116select top 2 CAST(kk.IstekMjesec as varchar(5)) as mjeseci, COUNT(*) as broj_kartica
117from dbo.KreditnaKartica as kk
118group by kk.IstekMjesec
119order by broj_kartica desc
120go
121
122select * from kartice
123
124drop view kartice
125
126
127--10
128dbcc traceon(3604)
129dbcc ind('AdventureWorksOBP', 'Racun', -1)
130
131--a
132--Podaci su smjesteni na 200 stranica, a ineksi su smjesteni na 1 stranicu
133
134dbcc page('AdventureWorksOBP', 1, 490, 3) with tableresults
135--b
136--Vrijednost stupca IDRacun za 1 je 43659, a za zadnji 43816 na prvoj stranici
137
138dbcc page('AdventureWorksOBP', 1, 493, 3) with tableresults
139--c
140--Nalazi ste na stranici 493
141
142--d
143insert into Racun(DatumIzdavanja, BrojRacuna, KupacID)
144values('20151115', 'SO75123', 4189)
145--dobio je ID 75124
146dbcc ind('AdventureWorksOBP', 'Racun', -1)
147dbcc page('AdventureWorksOBP', 1, 1032, 3) with tableresults
148--Nalazi se na stranici 1032
149
150delete from Racun where IDRacun = 75124
151
152--11
153SET STATISTICS IO ON
154
155select IDKupac from Kupac where Prezime = 'Adams' and GradID = 9
156
157create nonclustered index kup on Kupac(Prezime)
158include(GradID)
159
160drop index kup on kupac
161
162--12
163
164select IDKupac, Ime, Prezime from Kupac where Prezime like 'A%'
165and GradID = 9
166
167create nonclustered index kup2 on Kupac(Prezime)
168include(Ime, GradID)
169
170drop index kup2 on Kupac
171
172
173--4 ishod
174--1
175
176create proc zad1
177@naziv nvarchar(50),
178@iddrz int,
179@kljuc int output
180as
181declare @nazivdr nvarchar(50)
182declare @drid int
183select @nazivdr = Naziv, @drid = DrzavaID from grad
184
185if @nazivdr = @naziv and @iddrz = @drid
186 begin
187 set @kljuc = -1
188 print 'Grad je vec kreiran'
189 end
190else
191 begin
192 insert into Grad(Naziv, DrzavaID) values(@naziv, @iddrz)
193 set @kljuc = SCOPE_IDENTITY()
194 end
195go
196
197declare @ispis nvarchar(50)
198exec zad1 'Tokyo', 3, @ispis output
199print @ispis
200go
201
202drop proc zad1
203
204--provjera
205select * from Grad
206delete from Grad where IDGrad = 15
207
208
209--2
210
211create function BrojRacunaKomercijalista(
212@IDkom int
213)
214returns int
215as
216begin
217
218declare @brojac int
219
220select @brojac = count(*) from Komercijalist as k inner join Racun as r
221on k.IDKomercijalist = r.KomercijalistID
222where r.KomercijalistID = @IDkom
223
224return @brojac
225end
226go
227
228select k.Ime, k.Prezime, k.StalniZaposlenik, dbo.BrojRacunaKomercijalista(k.IDKomercijalist)
229as [Broj Racuna] from Komercijalist as k
230
231
232--3
233create proc crud
234 @ime nvarchar(50),
235 @prezime nvarchar(50),
236 @stalnozaposlen bit,
237 @status char(1),
238 @ime2 nvarchar(50),
239 @prezime2 nvarchar(50)
240 as
241 if @status ='i'
242 insert into Komercijalist(Ime, Prezime, StalniZaposlenik)
243 values(@ime, @prezime, @stalnozaposlen)
244else if @status = 'u'
245 update Komercijalist set Ime = @ime, Prezime = @prezime,
246 StalniZaposlenik = @stalnozaposlen where IDKomercijalist = dbo.cruder(@ime2, @prezime2)
247else if @status = 's'
248 select * from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
249else if @status = 'd'
250 delete from Komercijalist where IDKomercijalist = dbo.cruder(@ime, @prezime)
251else
252 print 'Krivo unseno slovo'
253go
254
255
256create function cruder(
257 @ime nvarchar(50),
258 @prezime nvarchar(50)
259)
260returns int
261as
262begin
263
264declare @vracaj int
265select @vracaj = IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime
266
267return @vracaj
268end
269go
270
271execute crud 'tester', 'testeric', 1, 'u', 'tester', 'testeric'
272
273select * from Komercijalist
274
275-- Zadatak 1 (Ishod 5)
276
277CREATE TABLE Zapisnik(
278 IDZapisnik INT PRIMARY KEY IDENTITY,
279 Sadrzaj nvarchar(MAX)
280 )
281
282GO
283
284CREATE TRIGGER okidacDrzava ON Drzava AFTER INSERT,UPDATE,DELETE
285AS
286
287DECLARE @uneseno INT
288DECLARE @obrisano INT
289
290SELECT @uneseno=IDDrzava FROM inserted
291SELECT @obrisano=IDDrzava FROM deleted
292
293IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
294BEGIN
295 INSERT INTO Zapisnik VALUES ('U tablicu država unesen je redak s ID-em ' + CAST(@uneseno AS nvarchar(50)))
296END
297IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
298BEGIN
299 INSERT INTO Zapisnik VALUES ('Iz tablice država obrisan je redak s ID-em ' + CAST(@obrisano AS nvarchar(50)))
300END
301IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
302BEGIN
303 INSERT INTO Zapisnik VALUES ('U tablici država promijenjen je redak s ID-em ' + CAST(@obrisano AS nvarchar(50)))
304END
305
306GO
307
308INSERT INTO Drzava VALUES ('Sokovia')
309UPDATE Drzava SET Naziv='Moldova' WHERE IDDrzava=SCOPE_IDENTITY()
310DELETE FROM Drzava WHERE IDDrzava=SCOPE_IDENTITY()
311
312SELECT * FROM Zapisnik
313
314DROP TRIGGER okidacDrzava
315
316-- Zadatak 2 (Ishod 5)
317
318GO
319
320CREATE TRIGGER okidacKreditna ON KreditnaKartica FOR UPDATE
321AS
322DECLARE @Brojac INT
323DECLARE @StaraGodina INT
324DECLARE @NovaGodina INT
325DECLARE @StariMjesec INT
326DECLARE @NoviMjesec INT
327DECLARE @BrojKartice INT
328
329SELECT @Brojac=COUNT(*) FROM inserted
330
331IF @Brojac=1
332 BEGIN
333 SELECT @NovaGodina=IstekGodina FROM inserted
334 SELECT @NoviMjesec=IstekMjesec FROM inserted
335 SELECT @StaraGodina=IstekGodina FROM deleted
336 SELECT @StariMjesec=IstekMjesec FROM deleted
337 SELECT @BrojKartice=Broj FROM inserted
338 INSERT INTO Zapisnik VALUES ('Promijenjeni podaci kartice broj ' + CAST(@BrojKartice AS nvarchar(50)) + ' staro trajanje je ' + CAST (@StaraGodina AS nvarchar(50)) + '-' + CAST(@StariMjesec AS nvarchar(50)) + ', novo trajanje je ' + CAST(@NovaGodina AS nvarchar(50)) + '-' + CAST(@NoviMjesec AS nvarchar(50)))
339 END
340ELSE BEGIN
341 INSERT INTO Zapisnik VALUES ('Promijenjeno više kartica')
342END
343GO
344
345UPDATE KreditnaKartica
346SET tip='Visa', Broj='321321321' ,IstekMjesec=11, IstekGodina=2020
347WHERE IDKreditnaKartica=1
348
349SELECT*
350FROM Zapisnik
351
352DROP TRIGGER okidacKreditna
353
354-- Zadatak 3 (Ishod 6)
355
356GO
357
358CREATE PROC Zadatak3(
359 @inputXML XML
360 )
361AS
362DECLARE @DrzavaID INT
363IF EXISTS (SELECT * FROM Drzava WHERE Naziv IN (SELECT Tablica.Stupac.VALUE('@Naziv','nvarchar(50)') FROM @inputXML.nodes('/Drzava') AS Tablica(Stupac)))
364BEGIN
365 SELECT @DrzavaID=IDDrzava FROM Drzava WHERE Naziv IN (SELECT Tablica.Stupac.VALUE('@Naziv','nvarchar(50)') FROM @inputXML.nodes('/Drzava') AS Tablica(Stupac))
366 INSERT INTO Grad
367 SELECT Tablica.Stupac.VALUE('.','nvarchar(50)') AS Naziv,
368 @DrzavaID AS DrzavaID
369 FROM @inputXML.nodes('/Drzava/Grad') AS Tablica(Stupac)
370END
371ELSE BEGIN
372 INSERT INTO Drzava
373 SELECT Tablica.Stupac.VALUE('@Naziv','nvarchar(50)')
374 FROM @inputXML.nodes('/Drzava') AS Tablica(Stupac)
375 SET @DrzavaID=SCOPE_IDENTITY()
376 INSERT INTO Grad
377 SELECT Tablica.Stupac.VALUE('.','nvarchar(50)') AS Naziv,
378 @DrzavaID AS DrzavaID
379 FROM @inputXML.nodes('/Drzava/Grad') AS Tablica(Stupac)
380END
381EXEC Zadatak3 '<Drzava Naziv="Hrvatska14515">
382<Grad>Bjelovar</Grad>
383<Grad>Vinkovci</Grad>
384<Grad>Koprivnica</Grad>
385</Drzava>'
386
387-- Zadatak 4 (Ishod 6)
388
389GO
390
391CREATE TYPE Optimus AS TABLE(
392Naziv nvarchar(50),
393BrojProizvoda nvarchar(50),
394Boja nvarchar(50),
395MinimalnaKolicinaNaSkladistu INT,
396CijenaBezPDV money,
397PotkategorijaID INT)
398
399GO
400
401CREATE PROC Zadatak04(
402 @inputTab Optimus READONLY
403 )
404AS
405INSERT INTO Proizvod
406SELECT * FROM @inputTab
407
408GO
409
410DECLARE @inputTab Optimus
411INSERT INTO @inputTab VALUES ('Test','15616565','plava',15,500,1)
412INSERT INTO @inputTab VALUES ('Testic2','14187948','crvena',47,458,1)
413EXEC Zadatak04 @inputTab
414
415-- Zadatak 5 (Ishod 7)
416
417GO
418CREATE PROC Zadatak05(
419 @IDKreditnaKartica INT)
420AS
421DELETE FROM Stavka WHERE RacunID IN (SELECT IDRacun FROM Racun WHERE KreditnaKarticaID IN (SELECT IDKreditnaKartica FROM KreditnaKartica WHERE IDKreditnaKartica=@IDKreditnaKartica))
422DELETE FROM Racun WHERE KreditnaKarticaID IN (SELECT IDKreditnaKartica FROM KreditnaKartica WHERE IDKreditnaKartica=@IDKreditnaKartica)
423DELETE FROM KreditnaKartica WHERE IDKreditnaKartica=@IDKreditnaKartica
424GO
425
426BEGIN TRY
427 BEGIN TRAN
428 EXEC Zadatak05 5
429 EXEC Zadatak05 72
430 EXEC Zadatak05 1
431 COMMIT TRAN
432 PRINT 'Brisanje uspješno!'
433END TRY
434BEGIN CATCH
435 ROLLBACK TRAN
436 PRINT 'Desila se greška: ' + ERROR_MESSAGE()
437END CATCH
438GO
439
440
441-- Zadatak 6 (Ishod 7)
442
443-- Skripta 1
444
445--1
446SET TRANSACTION ISOLATION LEVEL READ COMMITTED
447BEGIN TRAN
448
449--3
450UPDATE Drzava
451SET Naziv='Hrvatistan'
452WHERE IDDrzava=1
453
454--5
455ROLLBACK TRAN
456SELECT * FROM Drzava WHERE IDDrzava=1
457
458-- Skripta 2
459
460--2
461SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
462BEGIN TRAN
463
464--4
465SELECT * FROM Drzava WHERE IDDrzava=1 --Prljavo Äitanje
466
467--6
468ROLLBACK TRAN
469
470
471--ISHOD 7 nesto
472
473--1
474--Uspjeh
475create proc transakcija
476@IDKomercijalist int
477as
478begin try
479 begin tran
480 delete from Stavka where RacunID in(select IDRacun from Racun where KomercijalistID = @IDKomercijalist)
481 delete from Racun where KomercijalistID = @IDKomercijalist
482 delete from Komercijalist where IDKomercijalist = @IDKomercijalist
483 commit
484 print 'Uspjesno je obrisan komercijalist, stavke, racun'
485end try
486begin catch
487 if @@trancount > 1
488 ROLLBACK
489 print 'Komercijalist, stavke, racun nisu obrisani'
490end catch
491
492go
493
494--1
495--Greska
496
497create proc transakcijaGreska
498@IDKomercijalist int
499as
500begin try
501 begin tran
502 delete from Komercijalist where IDKomercijalist = @IDKomercijalist
503 delete from Racun where KomercijalistID = @IDKomercijalist
504 delete from Stavka where RacunID in(select IDRacun from Racun where KomercijalistID = @IDKomercijalist)
505 commit
506 print 'Uspjesno je obrisan komercijalist, stavke, racun'
507end try
508begin catch
509 if @@trancount > 1
510 ROLLBACK
511 print 'Komercijalist, stavke, racun nisu obrisani'
512end catch
513
514go
515
516
517
518
519
520exec transakcija 296
521
522exec transakcijaGreska 296
523
524select * from Komercijalist
525insert into Komercijalist(Ime, Prezime, StalniZaposlenik)values('Tester', 'Testeric', 1)
526
527select * from Racun
528insert into Racun(DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID)
529values('20150514', 'SO1234', 432, 296,16281)
530
531
532--2
533--Prljavo Äitanje
534--Problem nastaje tako sto smo ukljucili Read Uncommited
535--i dok smo tipa u transakciji nesto updatali i jos nismo
536--commitali ili rollback mozemo vidit koji je trenutni zapis
537--u tom redku... ovoga se mozemo rijesit tako da ukljucimo
538--Read commited i dok transakcija nije gotova mi sa selektom
539--nemoze pristupit tom redku u bazi nego trebamo cekat
540--da zavrsi transakcija i onda procitamo sta je u tom redku
541--Rjesit ovu stvar mozemo Read commited
542
543--Neponovljivo Äitanje
544--Tipa imamo ukljucen read commited i sada dok smo u transakciji
545-- mozemo mjenjat jednu vrijednost u drugu tipa imamo polje di je 50
546--pa izmjenimo ga u 60 i vise ovaj 50 necemo vidit kada se
547--transakcija zatvori sa COMMIT ili ROLLBACK ostaje samo zadnja vrijednost
548--one vrijednosti prije nisu vise moguce za dohvatit i to je
549--neponovljivo citanje... Rjesit ovu stvar mozemo repeatable read
550
551--Fantom
552-- Tipa imamo transakciju i dohvatimo sve drzave koje pocinju sa slovo H
553-- i sada umetnemo u transakciju neku drzavu sa slovom H
554-- i sada kada zelimo dohvatiti sve drzave sa slovom H necemo uspit jer
555--prva transkacija moze dohvatit samo stvari koje su bile umetnute prije
556--i sada kada ide pokusat dohvatit opet sve drzave sa H javit ce problem
557--da je blokirano jer u drugoj transakciji nije commitano ili rollback
558--i taj jedan red ostane ko fantom poveca seleket za 1 vise i nemozemo
559--vise dohvatiti niti staru drzavu sa H jer ova ostane u drugoj transakciji
560--visit... Rjesit ovu stvar mozemo ukljicivanje Serializable
561
562-- Zadatak 7 (Ishod 8)
563
564EXEC sp_addumpdevice
565 @devtype='Disk',
566 @logicalname='IspitnaLokacija2',
567 @physicalname='D:\Proba.bak'
568
569ALTER DATABASE AdventureWorksOBP
570SET RECOVERY FULL
571
572BACKUP DATABASE AdventureWorksOBP TO IspitnaLokacija2
573WITH
574 name='AWBP1',
575 description='Prvi backup - puni.',
576 STATS
577
578INSERT INTO KreditnaKartica VALUES ('Visa',758963145872,12,2018)
579
580BACKUP DATABASE AdventureWorksOBP TO IspitnaLokacija2
581WITH
582 DIFFERENTIAL,
583 name='AWBP1',
584 description='Prvi backup - puni.'
585
586RESTORE HEADERONLY FROM IspitnaLokacija2
587RESTORE FILELISTONLY FROM IspitnaLokacija2
588
589RESTORE DATABASE AWOBP_Vraceno FROM IspitnaLokacija2
590WITH
591 file=1,
592 MOVE 'AdventureWorksOBP' TO 'D:\SQL\AWOBP.mdf',
593 MOVE 'AdventureWorksOBP_log' TO 'D:\SQL\AWOBPlog.ldf',
594 norecovery,
595 stats
596
597RESTORE DATABASE AWOBP_Vraceno FROM IspitnaLokacija2
598WITH
599 file=2,
600 MOVE 'AdventureWorksOBP' TO 'D:\SQL\AWOBP.mdf',
601 MOVE 'AdventureWorksOBP_log' TO 'D:\SQL\AWOBPlog.ldf',
602 recovery,
603 stats
604
605SELECT * FROM sys.backup_devices
606
607GO
608EXEC sp_dropdevice
609 @logicalname='IspitnaLokacija2'
610GO
611
612-- Zadatak 8 (Ishod 8)
613
614CREATE LOGIN [Mirko Mirkić]
615WITH PASSWORD = 'centos'
616GO
617
618CREATE USER Mirko FROM LOGIN [Mirko Mirkić]
619GO
620
621CREATE ROLE Selektori
622GO
623
624GRANT SELECT ON Proizvod TO Selektori
625GO
626
627EXEC sp_addrolemember
628 @rolename='Selektori',
629 @membername='Mirko'