· 7 years ago · Dec 18, 2018, 03:42 PM
1create database Radnici
2go
3use Radnici
4
5
6-- 2.
7
8-- Kreiraj tabelu Radnici
9create table Radnici
10(
11 id int not null, --kolona ne moze imati NULL vrednosti
12 ime nvarchar(20) not null,
13 prezime nvarchar(20) not null,
14 jmbg char(13) null -- kolona moze imati NULL vrednosti
15 --,add constraint pk_radnici primary key (id)- moze i ovde da se metne primarni kljuc
16)
17
18-- 3.
19
20-- Upisivanje podataka u tabelu
21insert into Radnici (id, ime, prezime, jmbg)
22values (1, 'Radisa', 'Radnik', '0101999720000')
23
24select * from Radnici
25
26
27-- 4.
28
29-- Kolone koje mogu imati null vrednosti se mogu izostaviti u insert komandi
30insert into Radnici (id, ime, prezime) values (1, 'Radisa', 'Radnik')
31
32select * from Radnici
33
34
35-- 5.
36
37
38-- Brisanje kolone id
39alter table Radnici drop column id
40
41select * from Radnici
42
43-- Dodavanje kolone u vec postojecu tabelu sa auto incrementom
44
45/*
46 IDENTITY [ (seed , increment) ]
47
48 Argumenti
49
50 seed
51 Is the value that is used for the very first row loaded into the table.
52
53 increment
54
55 Is the incremental value that is added to the identity value of the previous row that was loaded.
56 You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
57
58*/
59
60alter table Radnici add id int identity(10,2)
61
62
63-- Kolone imaju osobinu identity se ne mogu insertovati kada je fleg IDENTITY_INSERT setovan na OFF sto je njegova default vrednost
64insert into Radnici (ime, prezime) values ('Radisa', 'Radnik'),('i1','p1') -- ovaj upit ce baciti gresku
65select * from Radnici
66
67insert into Radnici (ime, prezime) values ('Radisa', 'Radnik') -- ispravan upit
68insert into Radnici (ime, prezime) values ('Mika', 'Radnik1')
69insert into Radnici (ime, prezime, jmbg) values ('Zika', 'Radnik2', '0000000123456')
70
71select * from Radnici -- svi dodati redovi za id imaju automatski dodeljenu vrednost
72
73
74-- 6.
75
76
77-- Dodavanje primarnog kljuca na kolonu id tabele Radnici
78alter table Radnici add constraint pk_radnici primary key (id)
79
80
81
82-- 7.
83
84create table Angazovanja
85(
86 id int not null,
87 id_radnika int not null,
88 id_sektora int not null
89)
90insert into Angazovanja values (1000,1000,1000)
91select * from Angazovanja
92
93 alter table Angazovanja add constraint pk_angazovanja primary key (id)
94
95insert into Angazovanja(id, id_radnika, id_sektora) values (11, 10, 2)
96insert into Angazovanja(id, id_radnika, id_sektora) values (11, 10, 2) -- greska jer id vrednost 11 vec postoji
97
98select * from Angazovanja
99
100
101-- 8.
102
103
104-- Sa trenutnim ogranicenjima mozemo insert-ovati proizvoljan id radnika, a to je lose jer bi trebalo da
105-- mozemo da insertujemo samo one id-eve koji postoje u tabeli radnici
106
107select * from Angazovanja
108select * from Radnici
109
110-- Strani kljuc
111alter table Angazovanja add constraint fk_id_radnika
112 foreign key (id_radnika) references Radnici(id)
113
114delete from Angazovanja where id = 11
115
116alter table Angazovanja add constraint fk_id_radnika
117 foreign key (id_radnika) references Radnici(id)
118
119insert into Angazovanja(id, id_radnika, id_sektora) values (11, 10, 2) -- radnik sa id-em 10 ne postoji
120
121--ispravno
122select * from Radnici
123insert into Angazovanja values (1, 4, 4) -- Angazovan je radnik Zika, koji postoji u tabeli Radnici
124
125
126select * from Radnici
127select * from Angazovanja
128
129
130--9.
131
132
133delete from Angazovanja where 1 = 1 -- izbrisi sve
134
135
136-- 10.
137
138insert into Radnici(id, ime, prezime, jmbg) values (50, 'Jovan', 'Jovanovic', null) -- greska, id ima osobinu identity a fleg IDENTITY_INSERT je setovan na OFF
139
140SET IDENTITY_INSERT Radnici on -- Omoguci eksplicitno upisivanje podataka i za id
141
142insert into Radnici(id, ime, prezime, jmbg) values (50, 'Jovan', 'Jovanovic', null)
143
144insert into Radnici(id, ime, prezime, jmbg) values (50, 'Jovan', 'Jovanovic', null) --Sada moramo da postojuemo jedinstvenost kolone id, tj ne mozemo da upisemo vrednost koja vec postoji
145
146select * from Radnici
147
148-- 11.
149
150SET IDENTITY_INSERT Radnici off -- ONEMOGUCI eksplicitno upisivanje podataka za id
151
152insert into Radnici
153SELECT Imen, Imen, convert(varchar(2),Snast) + '12345678901'
154FROM studije.dbo.Nastavnici
155
156select * from Radnici
157
158-- 12.
159
160--update
161update radnici
162set prezime = 'Nepoznato'
163where id > 30
164
165select * from Radnici
166
167delete from Radnici where prezime != 'Nepoznato'
168
169select * from Radnici
170
171
172-- 13.
173
174insert into Angazovanja values (22,50, 1)
175
176select * from Radnici
177select * from Angazovanja
178
179
180delete from Radnici where id = 10-- Ne moze se obrisati jer se id 50 pojavljuje u tabali Angazovanja
181
182
183-- 14. Kaskadno brisanje (ako iz tabele Radnici izbrisemo radnijka sa id-em 50, izbrisace se
184-- i redovi u svim tabelama sa kojima je on povezan preko svog id-a)
185
186select * from Angazovanja
187delete from Angazovanja where id_radnika=10
188
189alter table Angazovanja drop constraint fk_id_radnika -- Izbrisi staro ogranicenje stranog kljuca
190
191alter table Angazovanja add constraint fk_id_radnika -- dodaj novo ogranicenje stranog kljuca
192 foreign key (id_radnika) references Radnici(id) ON delete cascade
193
194 insert into Angazovanja values (10,12,1)
195
196delete from Radnici where id = 12
197
198select * from Radnici
199select * from Angazovanja
200
201
202-- 15.
203
204-- pocetak while naredbe
205--mora se sve selektuje
206
207declare @i int -- deklaracija promenljive tipa int
208set @i = 1 -- setuj njenu pocetnu vrednost
209
210select @i
211
212while @i < 10000 -- sve dok je vrednost promenljive i manja od 10000
213begin
214 insert into Radnici values (CONCAT(@i, 'mile'), 'neko', null) -- upisi podatke u tabelu Radnici
215 set @i = @i + 1 -- Povecaj vrednost promenljive za 1
216end
217select * from Radnici
218
219-- kraj while naredbe
220
221select count(*) from radnici
222
223select * from Radnici where ime = '55mile'
224
225-- 16
226
227 /*
228
229 FUNKCIJE
230
231 */
232
233use STUDIJE
234
235
236
237
238
239-- Kreiranje funkcije
240create function prosek(@brojInd int, @godUpisan int)
241returns decimal(6,2)
242as
243begin
244 declare @p decimal(6,2)
245
246 select @p = avg(Ocena * 1.0)
247 from Prijave
248 where Indeks = @brojInd and Upisan = @godUpisan and Ocena > 5
249
250 return @p
251end
252
253
254select dbo.prosek(1,2000)
255
256-- Izvrsavanje funkcije
257
258--16.1.
259select dbo.prosek(2, 2001)
260
261--16.2
262declare @prosek_studenta decimal(6,2)
263set @prosek_studenta = dbo.prosek(2,2001)
264
265select @prosek_studenta
266
267--16.3
268select Indeks, Upisan,( select avg(Ocena * 1.0)
269 from Prijave
270 where Indeks =s.Indeks and Upisan = s.Upisan and Ocena > 5
271 ) -- dbo.prosek(Indeks, Upisan) as 'Prosecna ocena' moze umesto ovog ugnjezdenog
272from Studenti s
273
274--16.4
275select indeks, upisan, imes
276from Studenti
277where dbo.prosek(indeks, upisan) > 9.0
278
279-- 17. Funkcija koja vraca tabelu predmeta na smeru
280
281create function predmeti_na_smeru(@smer int)
282returns table
283as
284 return (select Spred, Semestar
285 from Planst
286 where Ssmer = @smer)
287
288go
289
290select *
291from predmeti_na_smeru(1)
292
293
294/*
295
296 PROCEDURE
297
298*/
299
300-- 18 Kreirati proceduru za unos nove prijave
301
302create procedure unos_nove_prijave(@brInd int, @godUpisa int, @spred int, @snast int)
303as
304begin
305 insert into Prijave(Indeks, Upisan, Spred, Snast, Datump)
306 values(@brInd, @godUpisa, @spred, @snast, GETDATE())
307end
308
309-- * Ne mogu se koristiti u select, having i where delovima
310-- * Pomocu njih mozemo da radimo insert, update i delete nad podacima
311
312-- nacin pozivanja
313
314exec unos_nove_prijave 2, 2002, 1, 2
315
316
317select * from Prijave where Indeks = 2 and Upisan = 2002 and Spred=1 and Snast=2--PROVERA
318
319
320
321-- 19 - Procedure sa vise povratnih tipova
322
323create procedure vise_povratnih(@prva int output, @druga int output, @treca int)
324as
325begin
326 set @prva = @treca - 2
327 set @druga = @treca - 1
328end
329
330
331-- I poziv bez 'output' - vrednosti @izlaz_prva, @izlaz_druga ce biti null
332
333declare @izlaz_prva int
334declare @izlaz_druga int
335
336exec vise_povratnih @prva=@izlaz_prva , @druga = @izlaz_druga , @treca = 5
337select @izlaz_prva, @izlaz_druga
338
339
340-- II poziv - sa 'output' - vrednosti @izlaz_prva, @izlaz_druga ce biti 3 i 4
341
342declare @izlaz_prva int
343declare @izlaz_druga int
344
345exec vise_povratnih @prva=@izlaz_prva output, @druga = @izlaz_druga output, @treca = 5
346select @izlaz_prva, @izlaz_druga
347
348
349create procedure Proc1 (@brispita int output,@prosek float output,@ind int,@upisan int)
350as
351begin
352
353select @prosek=avg(ocena*1.0),@brispita=count(*)
354from Prijave
355where Indeks=@ind and Upisan=@upisan and ocena>5
356
357end
358
359
360exec Proc1
361
362
363CREATE PROCEDURE naziv
364 -- Add the parameters for the stored procedure here
365 @naziv int
366AS
367BEGIN
368
369END
370GO
371
372CREATE FUNCTION fun
373(
374 @godUpisan int
375)
376RETURNS TABLE
377AS
378RETURN
379(
380
381 SELECT *
382 from Prijave
383 where Upisan = @godUpisan
384)
385GO
386
387declare @i int
388set @i=5
389select @i
390
391select *
392from dbo.fun(2000)
393
394--1. Kreirati proceduru koja vraca prosek i broj nepolozenih ispita za odredjenog studenta
395
396create procedure prosek_nepolozeno(@brInd int, @godUpisa int, @prosek decimal(6,2) output,
397 @nepolozeno int output)
398as
399begin
400 select @prosek = avg(ocena * 1.0)
401 from Prijave
402 where Indeks = @brInd and Upisan = @godUpisa
403 and ocena > 5
404
405 select @nepolozeno = count(ps.Spred)
406 from Studenti s join Planst ps on s.Ssmer = ps.Ssmer
407 left join Prijave pr on pr.Indeks = s.Indeks and
408 pr.Upisan = s.Upisan and ps.Spred = pr.Spred and Ocena > 5
409 where pr.Spred is null and s.Indeks = @brInd and s.Upisan = @godUpisa
410end
411
412
413-- PRIMER POZIVA
414declare @prosek_izlaz decimal(6,2)
415declare @koliko_jos int
416exec prosek_nepolozeno @brInd = 2, @godUpisa = 2002, @prosek = @prosek_izlaz output, @nepolozeno = @koliko_jos output
417select 2, 2002, @prosek_izlaz, @koliko_jos
418
419--2. Napisati proceduru za upis ocene
420
421create procedure polozen_ispit(@brInd int, @godUpisa int, @spred int, @snast int,
422 @ocena int)
423as
424begin
425 declare @postoji int
426 set @postoji = 0
427 select @postoji = count(*)
428 from prijave
429 where Indeks = @brInd and Upisan = @godUpisa and Spred = @spred
430 and Snast = @snast and ocena is null
431
432 if (@postoji > 0)
433 begin
434 update prijave set Ocena = @ocena, Datump = GETDATE()
435 where Indeks = @brInd and Upisan = @godUpisa and Spred = @spred
436 and Snast = @snast and ocena is null
437 end
438end
439
440--PRIMER POZIVA
441exec polozen_ispit 5, 2002, 1, 1, 7
442
443
444-----------------------------------------------------------------------------------------
445
446/* TRIGERI - SINTAKSA
447
448 CREATE TRIGGER [ schema_name.]trigger_name
449 ON { table|view }
450 { FOR | AFTER | INSTEAD OF }
451 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
452 AS { sql_statement [ ; ] [ ... ] }
453
454*/
455
456
457-- 3.
458alter trigger provera_godine_upisa on studenti
459instead of insert
460as
461begin
462 if exists (select Upisan from inserted where Upisan > DATEPART(yyyy, GETDATE()) )
463 begin
464 raiserror('Ta skolska godina jos nije pocela', -1, -1)
465 end
466end
467
468delete from Studenti where upisan>2014
469
470select *
471from studenti
472where upisan>2014
473
474insert into Studenti values
475 (1, 2015, 'Nenad', 'Beograd', GETDATE(), 1),(1, 2020, 'Nenad', 'Beograd', GETDATE(), 1),(1, 2022, 'Nenad', 'Beograd', GETDATE(), 1)
476
477insert into Studenti values
478 (5, 2012, 'Nenad', 'Beograd', GETDATE(), 1),
479 (5, 2013, 'Nenad', 'Beograd', GETDATE(), 1),
480 (5, 2021, 'Nenad', 'Beograd', GETDATE(), 1); -- Dobija se obavestenje za jedan insert kod koga je godina upisa 2021 "Ta skolska godina jos nije pocela"
481
482select * from Studenti
483where Imes like 'Nenad' -- selekcijom podataka vidimo da je i red za koji smo dobili obavestenje upisan u tabelu
484
485insert into Studenti values
486 (10, 2010, 'Nenad', 'Beograd', GETDATE(), 1)
487
488insert into Studenti values
489 (30, 2024, 'Nenad', 'Beograd', GETDATE(), 1)
490
491select * from Studenti
492where Imes like 'Nenad'
493
494-- 4.
495alter trigger provera_godine_upisa on studenti
496instead of insert
497as
498
499--if exists (select Upisan from inserted where Upisan > DATEPART(yyyy, getdate()))
500--begin
501-- raiserror('Ta skolska godina jos nije pocela', -1, -1);
502--end
503--else
504--begin
505
506 insert into Studenti select * from inserted where upisan<=DATEPART(year,getdate())
507end
508
509insert into Studenti values
510 (4, 2013, 'Marko Markovic', 'Beograd', GETDATE(), 1),
511 (7, 2014, 'Marko Markovic', 'Beograd', GETDATE(), 1),
512 (12, 2026, 'Marko Markovic', 'Beograd', GETDATE(), 1); -- Nece biti ubacen nijedan red, iako dva studenta zadovoljavaju uslov za upis
513
514
515select * from Studenti
516where Imes like 'Marko Markovic'
517
518
519insert into Studenti values
520 (5, 2006, 'Milan11', 'Beograd', GETDATE(), 1)
521
522insert into Studenti values
523 (25, 2022, 'Milan222', 'Beograd', GETDATE(), 1)
524
525select * from Studenti
526where Imes like 'Marko Markovic'
527
528-- 5. Automatsko cuvanje izbrisanih ocena
529
530create table ponistene_ocene
531(
532 Spred smallint null,
533 Indeks smallint null,
534 Upisan smallint null,
535 Snast smallint null,
536 Datump datetime not null,
537 Ocena smallint null
538)
539
540
541create trigger arhiva_ponistenih on Prijave
542after delete
543as
544begin
545
546 insert into ponistene_ocene select * from deleted
547end
548
549
550delete from Prijave where Indeks = 1 and Upisan = 2000
551
552select * from ponistene_ocene
553
554
555-- 6. Automatsko cuvanje izbrisanih ocena
556
557create table izmenjene_ocene
558(
559 Spred smallint null,
560 Indeks smallint null,
561 Upisan smallint null,
562 Snast smallint null,
563 Datump datetime not null,
564 Ocena smallint null
565)
566
567create trigger arhiva_izmenjenih on Prijave
568after update
569as
570begin
571
572 insert into izmenjene_ocene select * from deleted
573end
574
575update Prijave set ocena = 10 where Indeks = 1 and Upisan = 2002
576
577select * from izmenjene_ocene
578
579-- 7. Kursori
580
581
582
583 declare @indeks int
584 declare @upisan int
585 declare @spred int
586 declare @ocena int
587
588 -- Deklarisi kursor za rezultat zeljenog upita
589 declare kursorKrozPrijave cursor for
590 select indeks, upisan, spred, ocena from prijave
591
592 open kursorKrozPrijave -- Otovri kursor za citanje
593
594 FETCH NEXT FROM kursorKrozPrijave INTO @indeks, @upisan, @spred, @ocena -- Ucitaj prvi red rezultata u promenljive
595
596 WHILE @@FETCH_STATUS = 0
597 BEGIN
598
599 --select @indeks, @upisan, @spred, @ocena
600 print concat(@indeks, ' ', @upisan, ' ', @spred, ' ', @ocena)
601
602 FETCH NEXT FROM kursorKrozPrijave INTO @indeks, @upisan, @spred, @ocena
603 END
604
605 CLOSE kursorKrozPrijave
606 DEALLOCATE kursorKrozPrijave