· 10 years ago · Sep 09, 2016, 11:12 PM
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-- Zadatak 7 (Ishod 8)
472
473EXEC sp_addumpdevice
474 @devtype='Disk',
475 @logicalname='IspitnaLokacija2',
476 @physicalname='D:\Proba.bak'
477
478ALTER DATABASE AdventureWorksOBP
479SET RECOVERY FULL
480
481BACKUP DATABASE AdventureWorksOBP TO IspitnaLokacija2
482WITH
483 name='AWBP1',
484 description='Prvi backup - puni.',
485 STATS
486
487INSERT INTO KreditnaKartica VALUES ('Visa',758963145872,12,2018)
488
489BACKUP DATABASE AdventureWorksOBP TO IspitnaLokacija2
490WITH
491 DIFFERENTIAL,
492 name='AWBP1',
493 description='Prvi backup - puni.'
494
495RESTORE HEADERONLY FROM IspitnaLokacija2
496RESTORE FILELISTONLY FROM IspitnaLokacija2
497
498RESTORE DATABASE AWOBP_Vraceno FROM IspitnaLokacija2
499WITH
500 file=1,
501 MOVE 'AdventureWorksOBP' TO 'D:\SQL\AWOBP.mdf',
502 MOVE 'AdventureWorksOBP_log' TO 'D:\SQL\AWOBPlog.ldf',
503 norecovery,
504 stats
505
506RESTORE DATABASE AWOBP_Vraceno FROM IspitnaLokacija2
507WITH
508 file=2,
509 MOVE 'AdventureWorksOBP' TO 'D:\SQL\AWOBP.mdf',
510 MOVE 'AdventureWorksOBP_log' TO 'D:\SQL\AWOBPlog.ldf',
511 recovery,
512 stats
513
514SELECT * FROM sys.backup_devices
515
516GO
517EXEC sp_dropdevice
518 @logicalname='IspitnaLokacija2'
519GO
520
521-- Zadatak 8 (Ishod 8)
522
523CREATE LOGIN [Mirko Mirkić]
524WITH PASSWORD = 'centos'
525GO
526
527CREATE USER Mirko FROM LOGIN [Mirko Mirkić]
528GO
529
530CREATE ROLE Selektori
531GO
532
533GRANT SELECT ON Proizvod TO Selektori
534GO
535
536EXEC sp_addrolemember
537 @rolename='Selektori',
538 @membername='Mirko'