· 6 years ago · Jul 05, 2019, 09:20 AM
1--I4
2--zadatak 6
3create proc zad6
4 @Id int,
5 @Name nvarchar(40),
6 @Surname nvarchar(40),
7 @JMBAG char(11)
8as
9if exists (Select * from Student where IDStudent = @Id)
10 update Student set Ime = @Name, Prezime = @Surname, JMBAG = @JMBAG
11 where IDStudent = @Id
12else
13 insert into Student (Ime, Prezime, JMBAG) values (@Name,@Surname,@JMBAG)
14 set @Id = SCOPE_IDENTITY()
15go
16
17create proc zad6del
18 @Id int
19as
20delete from Student where IDStudent = @Id
21go
22
23create proc zad6select
24 @Id int
25as
26select * from Student where IDStudent = @Id
27go
28
29
30exec zad6select 1
31go
32
33exec zad6 2,'test2', 'test2', '22222222222'
34go
35
36exec zad6select 2
37go
38
39exec zad6del 2
40go
41
42go
43
44--PRETVARANJE TIPOVA PODATAKA
45--Napišite proceduru za umetanje zapisa u tablicu Drzava.
46--Neka procedura kroz izlazni parametar vrati vrijednost primarnog kljuèa novog zapisa.
47--Pozovite proceduru i ispišite vraæenu vrijednost u formatu:
48--“Umetnuta je država s ID-em n”, gdje je n vrijednost primarnog kljuèa.
49--*/
50CREATE PROC p1
51 @Naziv nvarchar(50),
52 @ID int OUTPUT
53AS
54INSERT INTO Drzava (Naziv) VALUES (@Naziv)
55SET @ID = SCOPE_IDENTITY()
56GO
57
58DECLARE @n int
59EXEC p1 'Gruzija', @n OUTPUT
60PRINT 'Umetnuta je država s ID-em ' + CAST(@n AS nvarchar(10))
61GO
62-----------------------------------------------------------------------------------
63-- Zadatak 7.
64/*
65CRUD OPERACIJE
66UPDATE/INSERT/DELETE zajedno.
67Napravite procedure koje rade CRUD operacije na tablici Student
68tako da operacije umetanja, izmjene i brisanja obavite u jednoj proceduri,
69a dohvaæanje u drugoj.
70Iskoristite procedure za umetanje, izmjenu, dohvaæanje i brisanje zapisa.
71*/
72CREATE PROC ChangeStudent
73 @Operacija char(1),
74 @IDStudent int OUTPUT,
75 @Ime nvarchar(50),
76 @Prezime nvarchar(50),
77 @JMBAG char(11)
78AS
79IF @Operacija = 'U'
80 UPDATE Student
81 SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
82 WHERE IDStudent = @IDStudent
83ELSE IF @Operacija = 'I' BEGIN
84 INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
85 SET @IDStudent = SCOPE_IDENTITY()
86END
87ELSE IF @Operacija = 'D'
88 DELETE FROM Student WHERE IDStudent = @IDStudent
89GO
90
91CREATE PROC GetStudent
92 @IDStudent int
93AS
94SELECT * FROM Student WHERE IDStudent = @IDStudent
95GO
96
97EXEC GetStudent 3
98
99DECLARE @NoviIDStudenta int
100EXEC ChangeStudent 'I', @NoviIDStudenta OUTPUT, 'Ana', 'Aniæ', '11224451253'
101PRINT @NoviIDStudenta
102
103EXEC GetStudent 3
104
105EXEC ChangeStudent 'U', 3, 'Ana', 'Aniæ Miriæ', '11224451253'
106
107EXEC GetStudent 3
108
109EXEC ChangeStudent 'D', 3, null, null, null
110
111EXEC GetStudent 3
112GO
113
114
115
116--SLOŽENE TABLIÈNE FUNKCIJE
117--Napišite složenu tabliènu funkciju koja prima cijenu.
118--Ako je cijena NULL, vratite nazive i cijene svih proizvoda iz tablice Proizvod.
119--Ako nije, vratite nazive i cijene samo onih proizvoda èija cijena je veæa od zadane cijene.
120--Iskoristite funkciju s NULL i s cijenom od 3000.
121--*/
122CREATE FUNCTION F4
123(
124 @Cijena money
125)
126RETURNS @rez TABLE ( Naziv nvarchar(50), Cijena money )
127AS
128BEGIN
129 IF @Cijena IS NULL BEGIN
130 INSERT INTO @rez (Naziv, Cijena)
131 SELECT Naziv, CijenaBezPDV FROM Proizvod
132 END
133 ELSE BEGIN
134 INSERT INTO @rez (Naziv, Cijena)
135 SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV > @Cijena
136 END
137
138 RETURN
139END
140GO
141
142SELECT * FROM F4(NULL)
143SELECT * FROM F4(3000)
144GO
145-----------------------------------------------------------------------------------
146-- Zadatak 23.
147/*
148SLOŽENE TABLIÈNE FUNKCIJE
149Napišite složenu tabliènu funkciju koja prima jedan datum i koja vraæa tablicu koja se sastoji od jednog stupca i koja sadržava sljedeæih 5 datuma.
150Primjerice, ako je zadan 03.12.2011, funkcija treba vratiti 04.12., 05.12, 06.12, 07.12, 08.12.
151*/
152CREATE FUNCTION GetDatume
153(
154 @Datum datetime
155)
156RETURNS @RetVal TABLE ( Datum datetime )
157AS
158BEGIN
159 DECLARE @i int = 1
160 WHILE @i <= 5 BEGIN
161 INSERT INTO @RetVal (Datum)
162 VALUES (Dateadd(day, @i, @Datum))
163
164 SET @i += 1
165 END
166
167 RETURN
168END
169GO
170
171SELECT * FROM GetDatume(GETDATE())
172SELECT * FROM GetDatume('20111229')
173GO
174
175
176--SLOŽENE TABLIÈNE FUNKCIJE
177--Napišite složenu tabliènu funkciju koja se ponaša kao funkcija iz zadatka 2.
178--PODSJETNIK na zadatak 2:
179--Napišite složenu tabliènu funkciju koja prima dva datuma.
180--Neka funkcija vrati broj raèuna, datum izdavanja i ime i prezime kupca za sve raèune izdane izmeðu zadanih datuma.
181--Iskoristite funkciju za dohvat raèuna izmeðu 01.06.2004. i 03.06.2004.
182--*/
183CREATE FUNCTION DohvatiRacuneSloz
184(
185 @D1 datetime,
186 @D2 datetime
187)
188RETURNS @RetVal TABLE ( BrojRacuna nvarchar(25), DatumIzdavanja datetime, Ime nvarchar(50), Prezime nvarchar(50) )
189AS
190BEGIN
191 INSERT INTO @RetVal (BrojRacuna, DatumIzdavanja, Ime, Prezime)
192 SELECT r.BrojRacuna, r.DatumIzdavanja, k.Ime, k.Prezime
193 FROM Racun AS r
194 LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
195 WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
196
197 RETURN
198END
199GO
200
201SELECT * FROM DohvatiRacuneSloz('20040601', '20040603')
202GO
203/*
204SKALARNE FUNKCIJE
205Napišite funkciju koja prima ID proizvoda i dohvaæa broj prodanih primjeraka.
206Pozovite funkciju samostalno.
207Dohvatite nazive i boje svih proizvoda i uz svaki proizvoda
208ispišite koliko primjeraka je prodano.
209Promijenite funkciju tako da vrati 0 za one proizvode koji nisu prodani
210niti u jednom primjerku.
211Uklonite funkciju.
212*/
213create FUNCTION UkupnaKolicina
214(
215 @ID int
216)
217RETURNS int
218AS
219BEGIN
220 DECLARE @Ukupno int
221 SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID
222 RETURN @Ukupno
223END
224GO
225
226DECLARE @Rez int
227SET @Rez = dbo.UkupnaKolicina(776)
228PRINT @Rez
229
230SELECT
231 Naziv,
232 Boja,
233 dbo.UkupnaKolicina(IDProizvod) AS Prodano
234FROM Proizvod
235GO
236
237--Ishod ucenja 4
238
239/* Zadatak 1
240
241 Napisite proceduru koja prima ime i prezime komercijaliste, umece novog komercijalistu u tablicu komercijalist
242
243 i kroz izlazni parametar vraca vrijednost primarnog kljuca umetnutog retka. Ako vec postojao komercijalist zadanog
244
245 imena i prezimena, ne treba ga upisivati ponovo i u tom slucaju kroz izlazni parametar vratite vrijednost -1.
246
247 pozovite proceduru dvaput s istim imenom i prezimenom te ispisite vracenu vrijednost [5] */
248
249use AdventureWorksOBP
250go
251create proc i401
252 @ime nvarchar(20),
253 @prezime nvarchar(20),
254 @id int output
255as
256begin
257 if exists (select * from Komercijalist where Ime = @ime and Prezime = @prezime)
258 begin
259 return -1
260 end
261 else begin
262 insert into Komercijalist (Ime, Prezime) values (@ime, @prezime)
263 set @id = SCOPE_IDENTITY()
264 end
265end
266go
267
268declare @id int
269exec i401 'testesttest', 'testesttest', @id output
270print cast (@id as nvarchar(10))
271go
272
273/* Zadatak 2
274
275 Napisite proceduru koja prima ID proiyvoda i provjerava postoji li takav proizvod i jeli ikad prodan. ako proizvod
276
277 koji je poslan proceduri ne postoji procedura treba pomocu return parametra vratiti -1. ako proizvod postoji
278
279 ali nije prodan ni u jednom primjerku treba vratiti putem return parametra 0. ako proizvod postoji i prodavan je,
280
281 procedura treba putem return parametra vratiti 1. pozovite proceduru tri puta kako bi prikazali navedene slucajeve. [5]*/
282use AdventureWorksOBP
283go
284alter proc i4zad02
285 @id int
286as
287begin
288 if not exists(select * from Proizvod where IDProizvod = @id)
289 begin
290 print -1
291 end
292 else if exists(select * from Proizvod where IDProizvod = @id) and exists(select * from Stavka where ProizvodID = @id)
293 begin
294 print 1
295 end
296 else if exists(select * from Proizvod where IDProizvod = @id) and not exists(select * from Stavka where ProizvodID = @id)
297 begin
298 print 0
299 end
300end
301go
302
303select * from Proizvod
304
305select ProizvodID from Stavka
306
307exec i4zad02 776
308/* Zadatak 3
309
310 Implementirajte CRUD operacije na tablici potkategorija pomocu jedne procedure. napravite umetanje, iymjenu, dohvacanje
311
312 i brisanje jedne potkategorije [5] */
313
314alter proc i04zad3
315 @Naziv nvarchar(30),
316 @kategorijaid int,
317 @id int,
318 @action nvarchar(30)
319as
320begin
321 if (@action = 'insert')
322 begin insert into Potkategorija (Naziv,KategorijaID) values (@Naziv, @kategorijaid)
323 set @id = SCOPE_IDENTITY()
324 end
325 else if(@action = 'update')
326 begin
327 update Potkategorija set Naziv = @Naziv, KategorijaID = @kategorijaid where IDPotkategorija = @id
328 end
329 else if(@action = 'delete')
330 begin
331 delete from Potkategorija where IDPotkategorija = @id
332 end
333end
334go
335
336exec i04zad3 'testtest', 1 , 1,'insert'
337go
338
339-- I6
340--zad 1
341
342alter proc zad01
343 @drzava xml
344as
345select distinct
346 Tablica.Stupac.value('@Naziv', 'nvarchar(10)') as Drzava
347from @drzava.nodes('/Drzava') as Tablica(Stupac)
348
349select
350 Tablica.Stupac.value('.','nvarchar(50)') as Grad
351from @drzava.nodes('/Drzava/Grad') as Tablica(Stupac)
352go
353
354exec zad01 '<Drzava Naziv="Hrvatska"><Grad>Bjelovar</Grad><Grad>Vinkovci</Grad><Grad>Koprivnica</Grad></Drzava>'
355go
356
357use AdventureWorksOBP
358go
359-- zad 2
360
361select * from Proizvod
362go
363create type MyProizvod4 as table (IDProizvod int, Naziv nvarchar(20), BrojProizvoda nvarchar(20), Boja nvarchar(20), MinimalnaKolicinaNaSkladistu smallint, CijenaBezPDV money)
364go
365
366alter proc zad022
367 @proizvod MyProizvod4 readonly
368as
369insert into Proizvod
370select * from @proizvod
371go
372
373declare @t MyProizvod4
374insert into @t values (1,'test1','test1',1,1,10)
375insert into @t values (1,'test2','test2',1,2,10)
376exec zad022 @t
377go
378
379
380select * from Proizvod where Naziv = 'test1' or Naziv = 'test2'
381
382
383use AdventureWorksOBP
384go
385--Napisite proceduru koja u jednom pozivu omogucava unos vise gradova u tablicu Grad, koristite tablicne varijable
386create type MyGrad3 as Table (Naziv nvarchar(50), DrzavaID int)
387go
388
389create proc testzad3
390 @gradovi MyGrad3 readonly
391as
392insert into Grad select * from @gradovi
393go
394
395
396declare @g MyGrad3
397insert into @g values ('test',1)
398insert into @g values ('test2',2)
399exec testzad3 @g
400go
401
402
403select * from Grad where Naziv like 'test%'
404
405
406--Napisati proceduru koja prima XML dokument oblika:
407--<Osobe>
408-- <Osoba ID="1">Ana Aniæ</Osoba>
409-- <Osoba ID="2">Mira Miriæ</Osoba>
410-- …
411--</Osobe>
412--Neka procedura vrati imena i prezimena te ID-eve svih osoba.
413--Svaki ID treba nadopuniti s nulama s lijeve strane na duljinu od 5 znakova
414--(Primjerice, za ID = 1 treba ispisati “00001”, za ID = “176” treba ispisati “00176”)
415--*/
416CREATE PROC Zad7
417 @dok xml
418AS
419SELECT
420 Tablica.Stupac.value('.', 'nvarchar(50)') AS ImePrezime,
421 CASE
422 WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 1 THEN '0000' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
423 WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 2 THEN '000' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
424 WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 3 THEN '00' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
425 WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 4 THEN '0' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
426 WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 5 THEN Tablica.Stupac.value('@ID', 'nvarchar(50)')
427 END AS ID
428FROM @dok.nodes('/Osobe/Osoba') AS Tablica(Stupac)
429GO
430-- Pozvati proceduru s nekim parametrom.
431
432/*
433KORISNIÈKI DEFINIRANI TABLIÈNI TIPOVI
434Napišite proceduru koja umeæe države proslijeðene
435pomoæu korisnièki definiranog tabliènog tipa.
436Pozovite proceduru.
437*/
438-- Kreiranje korisnièki definiranog tipa
439CREATE TYPE MyDrzava AS TABLE ( Naziv nvarchar(50) )
440GO
441-- Stvaranje procedure
442CREATE PROC zad9
443 @Drzave MyDrzava READONLY
444AS
445INSERT INTO Drzava
446SELECT * FROM @Drzave
447GO
448-- Pozivanje procedure.
449DECLARE @t MyDrzava
450INSERT INTO @t VALUES ('Indija')
451INSERT INTO @t VALUES ('Pakistan')
452INSERT INTO @t VALUES ('Bangladeš')
453EXEC zad9 @t
454GO
455select * from drzava
456
457--KORISNIÈKI DEFINIRANI TABLIÈNI TIPOVI
458--Napišite proceduru koja umeæe potkategorije proslijeðene
459--pomoæu korisnièki definiranog tabliènog tipa.
460--Umetnuti samo onu potkategoriju koja ne postoji u tablici
461--(ako postoji nemojte umetati ponovno).
462--Pozovite proceduru.
463--*/
464use AdventureWorksOBP
465go
466-- Kreiranje korisnièki definiranog tipa
467CREATE TYPE MyPotkategorija AS TABLE ( KategorijaID int, Naziv nvarchar(50) )
468GO
469-- Stvaranje procedure
470CREATE PROC zad10
471 @Potk MyPotkategorija READONLY
472AS
473INSERT INTO Potkategorija
474SELECT KategorijaID, Naziv FROM @Potk WHERE Naziv NOT IN (SELECT Naziv FROM Potkategorija)
475GO
476-- Pozivanje procedure.
477DECLARE @t MyPotkategorija
478INSERT INTO @t VALUES (1, 'Mountain Bikes')
479INSERT INTO @t VALUES (1, 'Ninja Bikes')
480EXEC zad10 @t
481GO
482
483select * from Potkategorija
484
485--Ishod 8
486--1
487use AdventureWorksOBP
488go
489
490select Proizvod.Naziv as Proizvod,Potkategorija.Naziv as Potkategorija, Sum(Stavka.UkupnaCijena) as Grandtotal
491from Stavka
492inner join Proizvod on Stavka.ProizvodID = Proizvod.IDProizvod
493inner join Potkategorija on Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
494group by cube (Potkategorija.Naziv, Proizvod.Naziv)
495
496
497--2
498select count(Kupac.IDKupac) as BrojKupaca, Grad.Naziv as Grad, Drzava.Naziv as Drzava
499from Kupac
500inner join Grad on Kupac.GradID = Grad.IDGrad
501inner join Drzava on Grad.DrzavaID = Drzava.IDDrzava
502group by cube (Grad.Naziv,Drzava.Naziv)
503order by BrojKupaca desc
504
505
506--6. Jednim upitom dohvatite sljedeće podatke: zaradu po državi, po gradu u državi te grand total.
507select Sum(Stavka.UkupnaCijena) as GrandTotal, Drzava.Naziv as Drzava, Grad.Naziv as Grad
508from Stavka
509inner join Racun on Stavka.RacunID = Racun.IDRacun
510inner join Kupac on Racun.KupacID = Kupac.IDKupac
511inner join Grad on Kupac.GradID = Grad.IDGrad
512inner join Drzava on Grad.DrzavaID = Drzava.IDDrzava
513group by rollup (Drzava.Naziv,Grad.Naziv)
514
515
516--3
517select p.Naziv, (p.CijenaBezPDV / AVG(p.CijenaBezPDV) OVER( ) * 100) as [Postotni udjel]
518from Proizvod as p
519where p.CijenaBezPDV > 0
520order by [Postotni udjel]
521
522select avg(p.CijenaBezPDV) from Proizvod as p
523
524--4
525select
526 Proizvod.Naziv,
527 Proizvod.CijenaBezPDV,
528 CijenaBezPDV - LAG(CijenaBezPDV, 1) over (order by CijenaBezPDV) as Delta
529from Proizvod
530order by CijenaBezPDV desc
531
532
533select k.Prezime, k.Ime, ROW_NUMBER() over (order by k.Prezime, k.Ime) as BrKupac
534from Kupac as k
535order by k.Prezime,k.Ime
536
537
538
539select k.*, ROW_NUMBER() over (order by k.GradID,k.Ime,k.Prezime) as Br
540from Kupac as k
541inner join Grad as g on k.GradID = g.IDGrad
542order by g.IDGrad,k.Prezime,k.Ime
543
544
545select s.*,RANK() over (order by s.Kolicina desc) as Ran
546from Stavka as s
547order by s.Kolicina desc
548
549
550select r.*, NTILE(10) over (order by r.DatumIzdavanja, r.IDRacun) as Br
551from Racun as r
552where r.KomercijalistID = 284
553order by DatumIzdavanja
554
555
556select p.Naziv,p.CijenabezPDV, lead(p.CijenaBezPDV, 1) over (order by p.CijenaBezPDV desc) as Delta
557from Proizvod as p
558where p.CijenaBezPDV > 0
559order by p.CijenaBezPDV desc