· 7 years ago · Dec 25, 2018, 03:20 PM
1--1. Kreirati proceduru koja vraca prosek i broj nepolozenih ispita za odredjenog studenta
2
3create procedure prosek_nepolozeno(@brInd int, @godUpisa int, @prosek decimal(6,2) output,
4 @nepolozeno int output)
5as
6begin
7 select @prosek = avg(ocena * 1.0)
8 from Prijave
9 where Indeks = @brInd and Upisan = @godUpisa
10 and ocena > 5
11
12 select @nepolozeno = count(ps.Spred)
13 from Studenti s join Planst ps on s.Ssmer = ps.Ssmer
14 left join Prijave pr on pr.Indeks = s.Indeks and
15 pr.Upisan = s.Upisan and ps.Spred = pr.Spred and Ocena > 5
16 where pr.Spred is null and s.Indeks = @brInd and s.Upisan = @godUpisa
17end
18
19
20-- PRIMER POZIVA
21declare @prosek_izlaz decimal(6,2)
22declare @koliko_jos int
23exec prosek_nepolozeno @brInd = 2, @godUpisa = 2002, @prosek = @prosek_izlaz output, @nepolozeno = @koliko_jos output
24select 2, 2002, @prosek_izlaz, @koliko_jos
25
26--2. Napisati proceduru za upis ocene
27
28create procedure polozen_ispit(@brInd int, @godUpisa int, @spred int, @snast int,
29 @ocena int)
30as
31begin
32 declare @postoji int
33 set @postoji = 0
34 select @postoji = count(*)
35 from prijave
36 where Indeks = @brInd and Upisan = @godUpisa and Spred = @spred
37 and Snast = @snast and ocena is null
38
39 if (@postoji > 0)
40 begin
41 update prijave set Ocena = @ocena, Datump = GETDATE()
42 where Indeks = @brInd and Upisan = @godUpisa and Spred = @spred
43 and Snast = @snast and ocena is null
44 end
45end
46
47--PRIMER POZIVA
48exec polozen_ispit 5, 2002, 1, 1, 7
49
50
51-----------------------------------------------------------------------------------------
52
53/* TRIGERI - SINTAKSA
54
55 CREATE TRIGGER [ schema_name.]trigger_name
56 ON { table|view }
57 { FOR | AFTER | INSTEAD OF }
58 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
59 AS { sql_statement [ ; ] [ ... ] }
60
61*/
62
63
64-- 3.
65
66alter trigger provera_godine_upisa on studenti
67instead of insert
68as
69begin
70 if exists (select Upisan from inserted where Upisan > DATEPART(yyyy, GETDATE()) )
71 begin
72 raiserror('Ta skolska godina jos nije pocela', -1, -1)
73 end
74 else
75 begin
76 insert into studenti
77 select * from inserted
78 end
79end
80
81--Ubacuje ispravne iz inserted u tabelu studenti
82alter trigger provera_godine_upisa on studenti
83instead of insert
84as
85begin
86
87 insert into studenti
88 select * from inserted
89 where Upisan <=datepart(yyyy,GETDATE())
90
91end
92
93
94--1 nacin
95insert into Studenti values
96 (3, 2012, 'Pera', 'Beograd', GETDATE(), 1),
97 (3, 2013, 'Pera', 'Beograd', GETDATE(), 1),
98 (3, 2085, 'Pera', 'Beograd', GETDATE(), 1); -- Dobija se obavestenje za jedan insert kod koga je godina upisa 2021 "Ta skolska godina jos nije pocela"
99
100--2 nacin
101 insert into Studenti values (12, 2012, 'Pera', 'Beograd', GETDATE(), 1)
102 insert into Studenti values (12, 2013, 'Pera', 'Beograd', GETDATE(), 1)
103 insert into Studenti values (12, 20100, 'Pera', 'Beograd', GETDATE(), 1)
104
105delete from studenti where imes='Pera'
106
107select * from Studenti
108where Imes like 'Pera' -- selekcijom podataka vidimo da je i red za koji smo dobili obavestenje upisan u tabelu
109
110insert into Studenti values
111 (10, 2010, 'Nenad', 'Beograd', GETDATE(), 1)
112
113insert into Studenti values
114 (30, 2024, 'Nenad', 'Beograd', GETDATE(), 1)
115
116select * from Studenti
117where Imes like 'Nenad'
118
119-- 4.
120alter trigger provera_godine_upisa on studenti
121instead of insert
122as
123
124if exists (select Upisan from inserted where Upisan > DATEPART(yyyy, getdate()))
125begin
126 raiserror('Ta skolska godina jos nije pocela', -1, -1);
127end
128else
129begin
130
131 insert into Studenti select * from inserted
132end
133
134insert into Studenti values
135 (4, 2013, 'Marko Markovic', 'Beograd', GETDATE(), 1),
136 (7, 2014, 'Marko Markovic', 'Beograd', GETDATE(), 1),
137 (12, 2026, 'Marko Markovic', 'Beograd', GETDATE(), 1); -- Nece biti ubacen nijedan red, iako dva studenta zadovoljavaju uslov za upis
138
139
140select * from Studenti
141where Imes like 'Marko Markovic'
142
143
144insert into Studenti values
145 (5, 2006, 'Milan11', 'Beograd', GETDATE(), 1)
146
147insert into Studenti values
148 (25, 2022, 'Milan222', 'Beograd', GETDATE(), 1)
149
150select * from Studenti
151where Imes like 'Marko Markovic'
152
153-- 5. Automatsko cuvanje izbrisanih ocena
154
155create table ponistene_ocene
156(
157 Spred smallint null,
158 Indeks smallint null,
159 Upisan smallint null,
160 Snast smallint null,
161 Datump datetime not null,
162 Ocena smallint null
163)
164delete from prijave where ocena=6
165select *
166from ponistene_ocene
167
168create trigger arhiva_ponistenih on Prijave
169after delete
170as
171begin
172
173 insert into ponistene_ocene select * from deleted
174end
175
176
177delete from Prijave where Indeks = 1 and Upisan = 2000
178
179select * from ponistene_ocene
180
181
182-- 6. Automatsko cuvanje izbrisanih ocena
183
184create table izmenjene_ocene
185(
186 Spred smallint null,
187 Indeks smallint null,
188 Upisan smallint null,
189 Snast smallint null,
190 Datump datetime not null,
191 Ocena smallint null
192)
193
194create trigger arhiva_izmenjenih on Prijave
195after update
196as
197begin
198
199 insert into izmenjene_ocene select * from deleted
200end
201
202update Prijave set ocena = 10 where Indeks = 1 and Upisan = 2002
203
204select * from izmenjene_ocene
205
206-- 7. Kursori
207
208
209
210 declare @indeks int
211 declare @upisan int
212 declare @spred int
213 declare @ocena int
214
215 -- Deklarisi kursor za rezultat zeljenog upita
216 declare kursorKrozPrijavea cursor for
217 select indeks, upisan, spred, ocena
218 from prijave
219 where ocena>7
220
221 open kursorKrozPrijavea -- Otovri kursor za citanje
222
223 FETCH NEXT FROM kursorKrozPrijavea INTO @indeks, @upisan, @spred, @ocena -- Ucitaj prvi red rezultata u promenljive
224
225 WHILE @@FETCH_STATUS = 0
226 BEGIN
227
228 --select @indeks, @upisan, @spred, @ocena
229 print concat(@indeks, ' ', @upisan, ' ', @spred, ' ', @ocena)
230
231 FETCH NEXT FROM kursorKrozPrijavea INTO @indeks, @upisan, @spred, @ocena
232 END
233
234 CLOSE kursorKrozPrijavea
235 DEALLOCATE kursorKrozPrijavea
236
237
238-- =============================================
239CREATE FUNCTION dajSve
240(
241 -- Add the parameters for the function here
242 @indeks int,
243 @upisan int
244)
245RETURNS
246@ocene TABLE
247(
248 -- Add the column definitions for the TABLE variable here
249 o int,
250 s int
251)
252AS
253BEGIN
254 insert into @ocene
255 select ocena,spred
256 from prijave
257 where indeks=@indeks and Upisan=@upisan
258
259 RETURN
260END
261GO
262
263select * from dbo.dajSve (1,2000)
264
265
266CREATE FUNCTION dajocene
267(
268 -- Add the parameters for the function here
269 <@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
270 <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
271)
272RETURNS TABLE
273AS
274RETURN
275(
276 -- Add the SELECT statement with parameter references here
277 SELECT spred,ocena from
278)
279GO