UfEaTG4S

1RBP PRIPREMA ZA ODBRANU VEŽBI MAJ 2016
21. Kreirati bazu Imenik sa tabelama:
3OSOBA (#idosobe,ime,prezime)
4TELEFON ($idosobe,brtelefona)
5• Postaviti da je prezime obavezno polje
6• Povezati tabele
7• Popuniti tabele sa po dva zapisa
8• Kreirati pogled view_OT koji prikazuje imena osoba i njihove brojeve telefona.
9BAZA PREDUZECE
102. Kreirati funkciju fun_Najplaceniji koja vraća sve podatke o radniku sa najvećim primanjima u
11preduzeću.
12create function fun_Najplaceniji()
13returns table
14return
15(
16select *
17from RADNIK
18where plata+isnull(premija,0) in (select max(plata+isnull(premija,0))
19from RADNIK)
20)
212.1. Kreirati upit koji vraca sve podatke o najplacenijem radniku koristeći funkciju
22fun_Najplaceniji.
23select * from fun_Najplaceniji()
243. Kreirati funkciju fun_BrojRadnika koja za zadatu kvalifikaciju vraća koliko radnika u
25preduzeću ima zadatu kvalifikaciju . U slučaju da se zada nepostojeća kvalifikacija funkcija
26treba da vrati broj 200.
27create function fun_BrojRadnika(@kvalif nchar(4))
28returns int
29as
30begin
31declare @BrRadnika int
32if NOT EXISTS (select kvalif from radnik where kvalif=@kvalif)
33begin
34set @BrRadnika=200
35end
36else
37begin
38select @BrRadnika=count(Id_radnika)
39from RADNIK
40where kvalif=@kvalif
41end
42return @BrRadnika
43end
443.1.Proveriti za kvalifikaciju KSS i KV
45print dbo. fun_BrojRadnika('KSS')
46print dbo. fun_BrojRadnika('KV')
474. Kreirati pogled view_ORP koji prikazuje imena odeljenja, imena radnika koji rade u tim
48odeljenjima i imena projekata na kojima rade samo za radnike koji imaju više od 20 godina
49staža.
50create view view_ORP
51as
52SELECT dbo.ODELJENJE.Ime_od, dbo.RADNIK.Ime, dbo.PROJEKAT.Ime_proj
53FROM dbo.ODELJENJE INNER JOIN dbo.RADNIK
54ON dbo.ODELJENJE.Id_odeljenja = dbo.RADNIK.Id_odeljenja
55INNER JOIN dbo.UCESCE
56ON dbo.RADNIK.Id_radnika = dbo.UCESCE.Id_radnika
57INNER JOIN dbo.PROJEKAT
58ON dbo.UCESCE.Id_projekta = dbo.PROJEKAT.Id_projekta
59where year(SYSDATETIME())-year(dat_zap)>20
60Poziv pogleda:
61select * from view_ORP
625. Kreirati uskladištenu proceduru sp_Premesti koja radnika čije se ime i prezime zadaje
63prebacuje u odeljenje čije se ime zadaje. U slucaju da radnik vec radi u trazenom odeljenju
64vratiti poruku RADNIK JE VEC U TRAZENOM ODELJENJU, u protivno vratiti poruku RADNIK
65PREMESTEN.
66create procedure sp_Premesti
67@ime nchar(30),
68@prezime nchar(30),
69@imeod nchar(30)
70as
71begin
72if not exists (select id_radnika from radnik
73 where ime=@ime and prezime=@prezime and
74 id_odeljenja=(select Id_odeljenja from ODELJENJE where Ime_od=@imeod))
75begin
76update radnik
77set Id_odeljenja=(select Id_odeljenja from ODELJENJE where
78Ime_od=@imeod)
79where ime=@ime and prezime=@prezime
80select 'RADNIK PREMESTEN'
81end
82 else
83begin
84select 'RADNIK JE VEC U TRAZENOM ODELJENJU'
85end
86end
87Poziv procedure:
88exec sp_Premesti 'Petar', 'Vasić', 'Plan'
896. Kreirati uskladištenu proceduru sp_PovecajPlatu koja u sistemskoj tabeli ##tempRadnik
90pamti stanje tabele Radnik a potom povećava plate radncima sa zadatom kvalifikacijom za
91zadati iznos. Procedura vraca ime,prezime i platu radnika samo za radnike kojima je plata
92promenjena.
93create procedure sp_PovecajPlatu
94@kvalif nchar(5),
95@procenat real
96as
97begin
98if exists (select * from tempdb.INFORMATION_SCHEMA.TABLES where
99tempdb.INFORMATION_SCHEMA.TABLES.TABLE_NAME='##tempRadnik')
100begin
101truncate table ##tempRadnik
102insert into ##tempRadnik
103select *
104from RADNIK
105end
106else
107begin
108select * into ##tempRadnik
109from RADNIK
110end
111update radnik
112set plata=plata+plata*@procenat/100
113where kvalif=@kvalif
114select ime,prezime,plata
115from ##tempRadnik
116except
117select ime,prezime,plata
118from radnik
119end
1207. Kreirati triger trig_RadnikPremija koji samo za promenu vrednosti premije u tabeli radnik
121vraca poruku PREMIJA PROMENJENA.
122create trigger trig_RadnikPremija
123on radnik
124for update
125as
126begin
127if update(premija)
128begin
129select 'PREMIJA PROMENJENA'
130end
131end
1328. Kreirati proceduru sp_Odeljenje sa primenom transakcije. Procedura dodaje novo odeljenje
133cije se ime i mesto zadaju. Ukoliko je ime odeljenja krace od 5 karaktera obustaviti
134transakciju I vratiti poruku TRANSAKCIJA OBUSTAVLJENA u protivnom potvrditi transakciju I
135vratiti poruku TRANSAKCIJA OBAVLJENA.
136create procedure sp_Odeljenje
137@imeod nchar(30),
138@mesto nchar(30)
139as
140begin
141declare @idodeljenja int
142 select @idodeljenja=max(id_odeljenja)
143 from ODELJENJE
144 set @idodeljenja=@idodeljenja+10
145begin tran --Pokretanje Transakcije
146insert into ODELJENJE(id_odeljenja,ime_od,mesto)
147values(@idodeljenja,@imeod,@mesto)
148if len(@imeod)<5
149begin
150rollback --Odustajanje od Transakcije
151select 'TRANSAKCIJA OBUSTAVLJENA'
152end
153else
154begin
155commit --Izvrsi Transakciju
156select 'TRANSAKCIJA OBAVLJENA'
157end
158end