· 6 years ago · May 20, 2019, 06:16 PM
1------------------------------------------------UPITI ZA DROPOVANE SHEMA, TABELA, SEQUENCI------------------------------------------------
2
3IF EXISTS (SELECT schema_id FROM sys.schemas WHERE [name] = 'HR_Sluzba')
4BEGIN
5 IF OBJECT_ID('[HR_Sluzba].[Parametar_pripada]', 'U') IS NOT NULL
6 BEGIN
7 IF OBJECT_ID ('HR_Sluzba.FK_PARAMETAR_PRIPADA_PRIPADA', 'F') IS NOT NULL
8 BEGIN
9 ALTER TABLE [HR_Sluzba].[Parametar_pripada] DROP CONSTRAINT FK_PARAMETAR_PRIPADA_PRIPADA;
10 END
11 IF OBJECT_ID ('HR_Sluzba.FK_PARAMETAR_PRIPADA_IMA_PARAMETAR', 'F') IS NOT NULL
12 BEGIN
13 ALTER TABLE [HR_Sluzba].[Parametar_pripada] DROP CONSTRAINT FK_PARAMETAR_PRIPADA_IMA_PARAMETAR;
14 END
15
16 DROP TABLE [HR_Sluzba].[Parametar_pripada];
17 END
18
19 IF OBJECT_ID('[HR_Sluzba].[Pripada]', 'U') IS NOT NULL
20 BEGIN
21 IF OBJECT_ID ('HR_Sluzba.FK_PRIPADA_EVALUACIONI_TEST', 'F') IS NOT NULL
22 BEGIN
23 ALTER TABLE [HR_Sluzba].[Pripada] DROP CONSTRAINT FK_PRIPADA_EVALUACIONI_TEST;
24 END
25 IF OBJECT_ID ('HR_Sluzba.FK_PRIPADA_TIP_TESTA', 'F') IS NOT NULL
26 BEGIN
27 ALTER TABLE [HR_Sluzba].[Pripada] DROP CONSTRAINT FK_PRIPADA_TIP_TESTA;
28 END
29
30 DROP TABLE [HR_Sluzba].[Pripada];
31 END
32
33 IF OBJECT_ID('[HR_Sluzba].[Ima_Parametar]', 'U') IS NOT NULL
34 BEGIN
35 IF OBJECT_ID('HR_Sluzba.FK_IMA_PARAMETAR_PARAMETAR','F') IS NOT NULL
36 BEGIN
37 ALTER TABLE [HR_Sluzba].[Ima_Parametar] DROP CONSTRAINT FK_IMA_PARAMETAR_PARAMETAR;
38 END
39 IF OBJECT_ID ('HR_Sluzba.FK_IMA_PARAMETAR_TIP_TESTA', 'F') IS NOT NULL
40 BEGIN
41 ALTER TABLE [HR_Sluzba].[Ima_Parametar] DROP CONSTRAINT FK_IMA_PARAMETAR_TIP_TESTA;
42 END
43
44 DROP TABLE [HR_Sluzba].[Ima_Parametar];
45 END
46
47
48 IF OBJECT_ID('[HR_Sluzba].[Tip_evaluacionog_testa]', 'U') IS NOT NULL
49 BEGIN
50 DROP TABLE [HR_Sluzba].[Tip_evaluacionog_testa];
51
52 IF OBJECT_ID ('HR_Sluzba.Tip_Evaluacionog_testa_Sequence', 'SO') IS NOT NULL
53 BEGIN
54 DROP SEQUENCE HR_Sluzba.Tip_Evaluacionog_testa_Sequence;
55 END
56 END
57
58 IF OBJECT_ID('[HR_Sluzba].[Evaluacioni_Test]', 'U') IS NOT NULL
59 BEGIN
60 IF OBJECT_ID ('HR_Sluzba.FK_EVALUACIONI_TEST_ZAPOSLENI', 'F') IS NOT NULL
61 BEGIN
62 ALTER TABLE [HR_Sluzba].[Evaluacioni_Test] DROP CONSTRAINT FK_EVALUACIONI_TEST_ZAPOSLENI;
63 END
64
65 DROP TABLE [HR_Sluzba].[Evaluacioni_Test];
66
67 IF OBJECT_ID ('HR_Sluzba.Evaluacioni_test_Sequence', 'SO') IS NOT NULL
68 BEGIN
69 DROP SEQUENCE HR_Sluzba.Evaluacioni_test_Sequence;
70 END
71 END
72
73 IF OBJECT_ID('[HR_Sluzba].[Zaposlen]', 'U') IS NOT NULL
74 BEGIN
75 IF OBJECT_ID('HR_Sluzba.FK_Zaposlen_Kandidat', 'F') IS NOT NULL
76 BEGIN
77 ALTER TABLE [HR_Sluzba].[Zaposlen] DROP CONSTRAINT FK_Zaposlen_Kandidat;
78 END
79 DROP TABLE [HR_Sluzba].[Zaposlen];
80 END
81
82
83 IF OBJECT_ID('[HR_Sluzba].[Parametar]', 'U') IS NOT NULL
84 BEGIN
85 DROP TABLE [HR_Sluzba].[Parametar];
86
87 IF OBJECT_ID ('HR_Sluzba.Parametar_Sequence', 'SO') IS NOT NULL
88 BEGIN
89 DROP SEQUENCE HR_Sluzba.Parametar_Sequence;
90 END
91 END
92
93
94 IF OBJECT_ID('[HR_Sluzba].[Kandidat]', 'U') IS NOT NULL
95 BEGIN
96 DROP TABLE [HR_Sluzba].[Kandidat];
97
98 IF OBJECT_ID ('HR_Sluzba.Kandidat_Sequence', 'SO') IS NOT NULL
99 BEGIN
100 DROP SEQUENCE HR_Sluzba.Kandidat_Sequence;
101 END
102 END
103
104
105 DROP SCHEMA [HR_Sluzba];
106END;
107GO
108CREATE SCHEMA [HR_Sluzba];
109GO
110
111---------------------------------------------------UPITI ZA KREIRANJE SEQUENCI---------------------------------------------------
112
113CREATE SEQUENCE [HR_Sluzba].[Parametar_Sequence]
114 AS int
115 start with 0
116 increment by 1
117 cycle;
118
119
120CREATE SEQUENCE [HR_Sluzba].[Tip_Evaluacionog_testa_Sequence]
121 AS int
122 start with 0
123 increment by 1
124 cycle;
125
126CREATE SEQUENCE [HR_Sluzba].[Evaluacioni_test_Sequence]
127 AS int
128 start with 0
129 increment by 1
130 cycle;
131
132CREATE SEQUENCE [HR_Sluzba].[Kandidat_Sequence]
133 AS int
134 start with 1
135 increment by 1
136 minvalue 1
137 maxvalue 10000
138 cycle;
139
140---------------------------------------------------UPITI ZA KREIRANJE TABELA---------------------------------------------------
141
142CREATE TABLE [HR_Sluzba].[Parametar]
143(
144 [id_parametra] NUMERIC(5),
145 [kriterijum_evaluacije] NVARCHAR(75) NOT NULL,
146 CONSTRAINT PK_Parametar_id_parametra PRIMARY KEY (id_parametra)
147);
148GO
149
150ALTER TABLE [HR_Sluzba].[Parametar]
151 ADD CONSTRAINT DFT_ID_PARAMETAR DEFAULT(next value for [HR_Sluzba].[Parametar_Sequence]) for id_parametra;
152
153CREATE TABLE [HR_Sluzba].[Tip_evaluacionog_testa]
154(
155 [id_tipa_evaluacionog_testa] NUMERIC(5),
156 [vrsta_testa] NVARCHAR(50) NOT NULL,
157 CONSTRAINT PK_TIP_EVALUACIONOG_TESTA_id_tipa_evaluacionog_testa PRIMARY KEY(id_tipa_evaluacionog_testa)
158);
159GO
160
161
162ALTER TABLE [HR_Sluzba].[Tip_evaluacionog_testa]
163 ADD CONSTRAINT DFT_ID_TIP_TESTA DEFAULT(next value for [HR_Sluzba].[Tip_Evaluacionog_testa_Sequence]) for id_tipa_evaluacionog_testa;
164
165
166
167CREATE TABLE [HR_Sluzba].[Ima_Parametar]
168(
169 [id_parametra] NUMERIC(5),
170 [id_tipa_evaluacionog_testa] NUMERIC(5),
171 CONSTRAINT FK_IMA_PARAMETAR_PARAMETAR FOREIGN KEY (id_parametra)
172 REFERENCES [HR_Sluzba].[Parametar](id_parametra),
173 CONSTRAINT FK_IMA_PARAMETAR_TIP_TESTA FOREIGN KEY (id_tipa_evaluacionog_testa)
174 REFERENCES [HR_Sluzba].[Tip_Evaluacionog_testa](id_tipa_evaluacionog_testa),
175 CONSTRAINT PK_IMA_PARAMETAR PRIMARY KEY (id_parametra,id_tipa_evaluacionog_testa)
176);
177GO
178
179
180CREATE TABLE [HR_Sluzba].[Kandidat] (
181 k_id NUMERIC(5) default (next value for HR_Sluzba.Kandidat_sequence),
182 k_jmbg NUMERIC(13) not null,
183 k_ime VARCHAR(15) not null,
184 k_prezime CHAR(15) not null,
185 k_datrodj DATE not null,
186 k_pol VARCHAR(1) not null,
187 k_email CHAR(50) not null,
188 k_broj CHAR(15) not null,
189 k_drzv_stal CHAR(15) not null,
190 k_grad_stal CHAR(15) not null,
191 k_adres_stal CHAR(50) not null,
192 k_drzv_preb CHAR(15),
193 k_grad_preb CHAR(15),
194 k_adres_preb CHAR(50),
195 CONSTRAINT PK_Kandidat_k_id PRIMARY KEY (k_id),
196 CONSTRAINT UQ_Kandidat_k_jmbg unique (k_jmbg),
197 CONSTRAINT UQ_Kandidat_k_email unique (k_email),
198 CONSTRAINT UQ_Kandidat_k_broj unique (k_broj),
199 CONSTRAINT CH_Kandidat_k_jmbg CHECK (len(k_jmbg) = 13),
200 CONSTRAINT CH_Kandidat_k_datrodj CHECK (DATEDIFF(day, k_datrodj, CAST(current_timestamp as DATE)) > 0),
201 CONSTRAINT CK_Kandidat_k_pol CHECK (k_pol in ('m', 'M', 'z', 'Z'))
202);
203
204CREATE TABLE [HR_Sluzba].[Zaposlen] (
205 k_id NUMERIC(5),
206 mbr NUMERIC(13),
207 z_jmbg NUMERIC(13) not null,
208 z_ime VARCHAR(15) not null,
209 z_prezime CHAR(15) not null,
210 z_datrodj DATE not null,
211 z_pol VARCHAR(1) not null,
212 z_email CHAR(50) not null,
213 z_broj CHAR(15) not null,
214 z_drzv_stal CHAR(15) not null,
215 z_grad_stal CHAR(15) not null,
216 z_adres_stal CHAR(50) not null,
217 z_drzv_preb CHAR(15),
218 z_grad_preb CHAR(15),
219 z_adres_preb CHAR(50),
220 z_datum_zap DATE not null,
221 CONSTRAINT UQ_Zaposlen_z_jmbg unique (z_jmbg),
222 CONSTRAINT UQ_Zaposlen_z_email unique (z_email),
223 CONSTRAINT UQ_Zaposlen_z_broj unique (z_broj),
224 CONSTRAINT CH_Zaposlen_z_jmbg CHECK (len(z_jmbg) = 13),
225 CONSTRAINT CH_Zaposlen_z_datrodj CHECK (DATEDIFF(day, z_datrodj, CAST(current_timestamp as DATE)) > 0),
226 CONSTRAINT CH_Zaposlen_z_datum_zap_datrodj CHECK (DATEDIFF(day, z_datrodj, z_datum_zap) > 0),
227 CONSTRAINT CH_Zaposlen_z_datum_zap CHECK (DATEDIFF(day, z_datum_zap, CAST(current_timestamp as DATE)) > 0),
228 CONSTRAINT CK_Zaposlen_z_pol CHECK (z_pol in ('m', 'M', 'z', 'Z')),
229 CONSTRAINT FK_Zaposlen_Kandidat FOREIGN KEY (k_id)
230 references HR_Sluzba.Kandidat (k_id),
231 CONSTRAINT PK_Zaposlen_mbr_k_id PRIMARY KEY (mbr, k_id)
232);
233
234
235 CREATE TABLE [HR_Sluzba].[Evaluacioni_Test]
236 (
237 [id_evaluacionog_testa] NUMERIC(5),
238 [mbr] NUMERIC(13),
239 [k_id] NUMERIC(5),
240 [datum_testiranja] DATE NOT NULL,
241 CONSTRAINT FK_EVALUACIONI_TEST_ZAPOSLENI FOREIGN KEY(mbr,k_id)
242 REFERENCES [HR_Sluzba].[Zaposlen](mbr,k_id),
243 CONSTRAINT PK_EvALUACIONI_TEST PRIMARY KEY (id_evaluacionog_testa,mbr,k_id)
244 );
245 GO
246
247 ALTER TABLE [HR_Sluzba].[Evaluacioni_Test]
248 ADD CONSTRAINT DFT_ID_EVL_TEST DEFAULT(next value for [HR_Sluzba].[Evaluacioni_test_Sequence]) for id_evaluacionog_testa;
249
250
251 CREATE TABLE [HR_Sluzba].[Pripada]
252 (
253 [id_evaluacionog_testa] NUMERIC(5),
254 [mbr] NUMERIC(13),
255 [k_id] NUMERIC(5),
256 [id_tipa_evaluacionog_testa] NUMERIC(5),
257 CONSTRAINT FK_PRIPADA_EVALUACIONI_TEST FOREIGN KEY(id_evaluacionog_testa,mbr,k_id)
258 REFERENCES [HR_Sluzba].[Evaluacioni_Test](id_evaluacionog_testa,mbr,k_id),
259 CONSTRAINT FK_PRIPADA_TIP_TESTA FOREIGN KEY(id_tipa_evaluacionog_testa)
260 REFERENCES [HR_Sluzba].[Tip_evaluacionog_testa](id_tipa_evaluacionog_testa),
261 CONSTRAINT PK_PRIPADA PRIMARY KEY (id_evaluacionog_testa,mbr,k_id,id_tipa_evaluacionog_testa)
262 );
263 GO
264
265CREATE TABLE [HR_Sluzba].[Parametar_pripada]
266(
267 [rezultat_testa] NUMERIC(5) NOT NULL,
268 [id_parametra] NUMERIC(5),
269 [id_tipa_evaluacionog_testa] NUMERIC(5),
270 [id_evaluacionog_testa] NUMERIC(5),
271 [mbr] NUMERIC(13),
272 [k_id] NUMERIC(5),
273 [id_evaluacionog_tipa] NUMERIC(5),
274 CONSTRAINT FK_PARAMETAR_PRIPADA_PRIPADA FOREIGN KEY(id_evaluacionog_testa,mbr,k_id,id_tipa_evaluacionog_testa)
275 REFERENCES [HR_Sluzba].[Pripada](id_evaluacionog_testa,mbr,k_id,id_tipa_evaluacionog_testa),
276 CONSTRAINT FK_PARAMETAR_PRIPADA_IMA_PARAMETAR FOREIGN KEY(id_parametra,id_evaluacionog_tipa)
277 REFERENCES [HR_Sluzba].[Ima_Parametar](id_parametra,id_tipa_evaluacionog_testa),
278 CONSTRAINT PK_PARAMETAR_PRIPADA PRIMARY KEY(id_parametra,id_tipa_evaluacionog_testa,id_evaluacionog_testa,mbr,k_id,id_evaluacionog_tipa)
279);
280GO
281
282------------------------------------------------UPITI ZA UNOSENJE VREDNOSTI U TABELE------------------------------------------------
283
284/*DODAVANJE VREDNOSTI U TABELU KANDIDAT*/
285INSERT INTO HR_Sluzba.Kandidat (k_id,k_jmbg,k_ime,k_prezime,k_datrodj,k_pol,k_email,k_broj,k_drzv_stal,k_grad_stal,k_adres_stal,k_drzv_preb,k_grad_preb,k_adres_preb) VALUES
286 (next value for HR_Sluzba.Kandidat_Sequence,'5435444443326','Nikola','Nikolic','1997-01-30','M','nikolankolic1997@gmail.com','5354354543','Srbija','Pozarevac','B Stefana 8','Srbija','Novi Sad','B Slobode bb'),
287 (next value for HR_Sluzba.Kandidat_Sequence,'5435433333326','Marko','Markovic','1999-08-26','M','markomarkovi@hotmail.com','645454546','Srbija','Loznica','L Stevica 6','Srbija','Futog','NS put'),
288 (next value for HR_Sluzba.Kandidat_Sequence,'5435412345678','Milica','Milic','1987-04-20','Z','milicamilic@yahoo.com','132121321','Srbija','Loznica','Buliver Dositeja 1','Srbija','Beograd','Kneza Milosa 6'),
289 (next value for HR_Sluzba.Kandidat_Sequence,'8630660952838','Miloje','Antić','1952-02-02','M','milojeantic52@gmail.com','524702967','Srbija','Aranđelovac','Palih boraca 6','Srbija','Novi Sad','Moravska 11'),
290 (next value for HR_Sluzba.Kandidat_Sequence,'7415093640474','Irena','Ateljevic','1955-01-01','Z','irenaateljevic55@hotmail.com','072919278','BiH','Tuzla','Ustanicka 8','Srbija','Novi Sad','Negotinska 45'),
291 (next value for HR_Sluzba.Kandidat_Sequence,'4691326178273','Marina','Bogdanović','1959-08-20','Z','marinabogdanovic59@gmail.com','309190145','BiH','Zenica','Sarajevski put bb','Srbija','Beograd','Leposave Mihajlović 37'),
292 (next value for HR_Sluzba.Kandidat_Sequence,'5806924322452','Mihajlo','Stojanović','1960-05-18','M','mihajlostojanovic60@gmail.com','476755438','Srbija','Sombor','Svetog Save 26','Srbija','Beograd','Kirila Savića 82'),
293 (next value for HR_Sluzba.Kandidat_Sequence,'7487272113170','Mila','Kovac','1960-08-15','Z','milakovac1960@hotmail.com','780477190','Srbija','Ljubovija','Nemanjina 12','Srbija','Novi Sad','Olge Petrov 11'),
294 (next value for HR_Sluzba.Kandidat_Sequence,'8536316245751','Slavomir','Bogdanović','1961-02-06','M','slavomirbogdanovic61@gmail.com','203083681','BiH','Mostar','Lazinska 20','Srbija','Beograd','Junska bb'),
295 (next value for HR_Sluzba.Kandidat_Sequence,'8401148212423','Teodora','CrnÄević','1961-12-16','Z','teodoracrncevic61@hotmail.com','055556196','Hrvatska','Rijeka','Abramovićeva 14','Srbija','Beograd','Zorina 20'),
296 (next value for HR_Sluzba.Kandidat_Sequence,'6413779161048','Ivanka','Mihailović','1968-04-30','Z','ivankamihailovic68@hotmail.com','127888043','Hrvatska','Osjek','Alage 1','Srbija','Beograd','Emila Zole 26'),
297 (next value for HR_Sluzba.Kandidat_Sequence,'1567019236793','Momir','Petrović','1973-05-30','M','momirpetrovic73@yahoo.com','959544682','Srbija','Petrovac','Brace Jašić 28','Srbija','Novi Sad','Pionriska 1'),
298 (next value for HR_Sluzba.Kandidat_Sequence,'1363767701761','Jevrem','Janković','1973-09-23','M','jevremjankovic73@hotmail.com','586670382','Srbija','Niš','Bulevar Republike 4','Srbija','Beograd','Delijska 11'),
299 (next value for HR_Sluzba.Kandidat_Sequence,'4208537362437','Neno','Pavlović','1974-01-13','M','nenopavlovic74@hotmail.com','280870560','Hrvatska','Zadar','Alkarski put bb','Srbija','Beograd','Grahovska 6'),
300 (next value for HR_Sluzba.Kandidat_Sequence,'3188566586003','Mihajlo','Kovac','1975-04-28','M','mihajlokovac75@gmail.com','264990634','Srbija','Å abac','Proletejeva 63','Srbija','Novi Sad','Rataska bb'),
301 (next value for HR_Sluzba.Kandidat_Sequence,'4575495415321','Stanislava','Filipović','1977-12-19','Z','stanislavafilipovic77@hotmail.com','345194050','Hrvatska','Karlovac','Anićeva bb','Srbija','Novi Sad','Simonidina 32'),
302 (next value for HR_Sluzba.Kandidat_Sequence,'8056184826809','Dejana','Pavlović','1979-02-18','Z','dejanapavlovic79@hotmail.com','694410839','Hrvatska','Pula','Antoljaki 45','Srbija','Beograd','Viline Vode 4'),
303 (next value for HR_Sluzba.Kandidat_Sequence,'7500821294016','Dejana','Branković','1984-06-28','Z','dejanabrankovic84@gmail.com','893001021','Srbija','Kraljevo','Trg Ferdiha 23','Srbija','Novi Sad','Mornarska');
304
305/*DODAVANJE VREDNOSTI U TABELU ZAPOSLEN*/
306INSERT INTO HR_Sluzba.Zaposlen (k_id,mbr,z_jmbg,z_ime,z_prezime,z_datrodj,z_pol,z_email,z_broj,z_drzv_stal,z_grad_stal,z_adres_stal,z_drzv_preb,z_grad_preb,z_adres_preb,z_datum_zap) VALUES
307 (2,'6756045108644','5435433333326','Marko','Markovic','1999-08-26','M','markomarkovi@hotmail.com','645454546','Srbija','Loznica','L Stevica 6','Srbija','Futog','NS put','2004-10-27'),
308 (3,'6912051554350','5435412345678','Milica','Milic','1987-04-20','Z','milicamilic@yahoo.com','132121321','Srbija','Loznica','Buliver Dositeja 1','Srbija','Beograd','Kneza Milosa 6','2001-06-12'),
309 (4,'9801762982136','8630660952838','Miloje','Antić','1952-02-02','M','milojeantic52@gmail.com','524702967','Srbija','Aranđelovac','Palih boraca 6','Srbija','Novi Sad','Moravska 11','2004-11-11'),
310 (5,'3444534677142','7415093640474','Irena','Ateljevic','1955-01-01','Z','irenaateljevic55@hotmail.com','072919278','BiH','Tuzla','Ustanicka 8','Srbija','Novi Sad','Negotinska 45','2005-01-26'),
311 (6,'9768267793041','4691326178273','Marina','Bogdanović','1959-08-20','Z','marinabogdanovic59@gmail.com','309190145','BiH','Zenica','Sarajevski put bb','Srbija','Beograd','Leposave Mihajlović 37','2005-02-09'),
312 (8,'0582958504587','7487272113170','Mila','Kovac','1960-08-15','Z','milakovac1960@hotmail.com','780477190','Srbija','Ljubovija','Nemanjina 12','Srbija','Novi Sad','Olge Petrov 11','2005-07-04'),
313 (9,'7467674761963','8536316245751','Slavomir','Bogdanović','1961-02-06','M','slavomirbogdanovic61@gmail.com','203083681','BiH','Mostar','Lazinska 20','Srbija','Beograd','Junska bb','2007-06-04'),
314 (10,'1420399638411','8401148212423','Teodora','CrnÄević','1961-12-16','Z','teodoracrncevic61@hotmail.com','055556196','Hrvatska','Rijeka','Abramovićeva 14','Srbija','Beograd','Zorina 20','2008-07-16'),
315 (11,'2167270938438','6413779161048','Ivanka','Mihailović','1968-04-30','Z','ivankamihailovic68@hotmail.com','127888043','Hrvatska','Osjek','Alage 1','Srbija','Beograd','Emila Zole 26','2009-06-08'),
316 (12,'1392667994118','1567019236793','Momir','Petrović','1973-05-30','M','momirpetrovic73@yahoo.com','959544682','Srbija','Petrovac','Brace Jašić 28','Srbija','Novi Sad','Pionriska 1','2010-01-28'),
317 (13,'4709965354125','1363767701761','Jevrem','Janković','1973-09-23','M','jevremjankovic73@hotmail.com','586670382','Srbija','Niš','Bulevar Republike 4','Srbija','Beograd','Delijska 11','2010-04-08'),
318 (15,'6649441392640','3188566586003','Mihajlo','Kovac','1975-04-28','M','mihajlokovac75@gmail.com','264990634','Srbija','Å abac','Proletejeva 63','Srbija','Novi Sad','Rataska bb','2010-12-20'),
319 (16,'7092097107260','4575495415321','Stanislava','Filipović','1977-12-19','Z','stanislavafilipovic77@hotmail.com','345194050','Hrvatska','Karlovac','Anićeva bb','Srbija','Novi Sad','Simonidina 32','2015-09-01'),
320 (18,'8761567302511','7500821294016','Dejana','Branković','1984-06-28','Z','dejanabrankovic84@gmail.com','893001021','Srbija','Kraljevo','Trg Ferdiha 23','Srbija','Novi Sad','Mornarska','2018-11-21');
321
322
323/*DODAVANE VREDNOSTI U TABELU EVALUACIONI TEST*/
324INSERT INTO HR_Sluzba.Evaluacioni_Test (id_evaluacionog_testa,mbr,k_id,datum_testiranja) VALUES
325 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'6756045108644',2,'2015-06-26'),
326 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'6756045108644',2,'2015-10-19'),
327 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'6756045108644',2,'2015-12-31'),
328 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'6912051554350',3,'2016-02-08'),
329 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'6912051554350',3,'2016-03-22'),
330 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'9801762982136',4,'2016-04-07'),
331 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'3444534677142',5,'2016-05-05'),
332 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'3444534677142',5,'2016-11-16'),
333 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'3444534677142',5,'2016-12-01'),
334 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'3444534677142',5,'2017-01-18'),
335 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'9768267793041',6,'2017-01-26'),
336 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'0582958504587',8,'2017-05-23'),
337 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'7467674761963',9,'2017-09-26'),
338 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'1420399638411',10,'2017-10-03'),
339 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'2167270938438',11,'2017-10-04'),
340 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'2167270938438',11,'2017-12-21'),
341 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'2167270938438',11,'2018-03-02'),
342 (next value for HR_Sluzba.Evaluacioni_test_Sequence,'1392667994118',12,'2018-03-30');
343
344/*DODAVANJE VREDNOSTI U TABELU TIP EVALUACIONOG TESTA*/
345INSERT INTO HR_Sluzba.Tip_evaluacionog_testa (id_tipa_evaluacionog_testa,vrsta_testa) VALUES
346 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Test opste kulture'),
347 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Usmeno ispitivanje'),
348 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Prakticni test'),
349 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Psiholosko testiranje'),
350 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Zdrastveni test'),
351 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Strucni test - usmena forma'),
352 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Strucni test - pismena forma'),
353 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Test resavanja zadataka'),
354 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Rad u stresnim situacijima'),
355 (next value for HR_Sluzba.Tip_Evaluacionog_testa_Sequence,'Dopunsko testiranje');
356
357/*DODAVANE VREDNOSTI U TABELU PRIPADA*/
358
359INSERT INTO HR_Sluzba.Pripada (id_evaluacionog_testa,mbr,k_id,id_tipa_evaluacionog_testa) VALUES
360 ('0','6756045108644',2,'2'),
361 ('1','6756045108644',2,'3'),
362 ('2','6756045108644',2,'0'),
363 ('3','6912051554350',3,'8'),
364 ('4','6912051554350',3,'1'),
365 ('5','9801762982136',4,'2'),
366 ('6','3444534677142',5,'4'),
367 ('7','3444534677142',5,'9'),
368 ('8','3444534677142',5,'0'),
369 ('9','3444534677142',5,'2'),
370 ('10','9768267793041',6,'6'),
371 ('11','0582958504587',8,'7'),
372 ('12','7467674761963',9,'0'),
373 ('13','1420399638411',10,'9'),
374 ('14','2167270938438',11,'1'),
375 ('15','2167270938438',11,'2'),
376 ('16','2167270938438',11,'8'),
377 ('17','1392667994118',12,'4');
378
379/*DODAVANJE VREDNOSTI U TABELU PARAMETAR*/
380
381INSERT INTO HR_Sluzba.Parametar (id_parametra,kriterijum_evaluacije) VALUES
382 (next value for HR_Sluzba.Parametar_Sequence,'Opsta pismenost'),--0
383 (next value for HR_Sluzba.Parametar_Sequence,'Pozavanje politike'),--0
384 (next value for HR_Sluzba.Parametar_Sequence,'Poznavanje sporta'),--0
385 (next value for HR_Sluzba.Parametar_Sequence,'Poznavanje NUnita'),--1
386 (next value for HR_Sluzba.Parametar_Sequence,'Poznavanje Agilne metodologije'),--1
387 (next value for HR_Sluzba.Parametar_Sequence,'Poznavanje javascripta'),--2
388 (next value for HR_Sluzba.Parametar_Sequence,'Rad s REST tehnologijama'),--2
389 (next value for HR_Sluzba.Parametar_Sequence,'Procena vida'),--4
390 (next value for HR_Sluzba.Parametar_Sequence,'Procena sluha'),--4
391 (next value for HR_Sluzba.Parametar_Sequence,'Komunkacija'),--3
392 (next value for HR_Sluzba.Parametar_Sequence,'Rad u grupama'),--3
393 (next value for HR_Sluzba.Parametar_Sequence,'Rad s ogranicenim vremenom'),--8
394 (next value for HR_Sluzba.Parametar_Sequence,'Rad u zahtevnim situacijama');--8
395
396/*DODAVANJE VREDNOSTI U TABELU IMA PARAMETAR*/
397
398INSERT INTO HR_Sluzba.Ima_Parametar (id_parametra,id_tipa_evaluacionog_testa) VALUES
399 (0,0),--0
400 (1,0),--0
401 (2,0),--0
402 (3,1),--1
403 (4,1),--1
404 (5,2),--2
405 (6,2),--2
406 (7,4),--4
407 (8,4),--4
408 (9,3),--3
409 (10,3),--3
410 (11,8),--8
411 (12,8);--8
412
413/*DODAVANJE VREDNOSTI U TABELU PARAMETAR PRIPADA*/
414INSERT INTO HR_Sluzba.Parametar_pripada (rezultat_testa,id_parametra,id_tipa_evaluacionog_testa,id_evaluacionog_testa,mbr,k_id,id_evaluacionog_tipa) VALUES
415 (8,0,0,2,'6756045108644',2,0),
416 (7,1,0,8,'3444534677142',5,0),
417 (8,2,0,12,'7467674761963',9,0),
418 (9,3,1,4,'6912051554350',3,1),
419 (8,4,1,14,'2167270938438',11,1),
420 (10,5,2,5,'9801762982136',4,2),
421 (9,6,2,15,'2167270938438',11,2),
422 (8,7,4,6,'3444534677142',5,4),
423 (10,8,4,17,'1392667994118',12,4),
424 (9,9,3,1,'6756045108644',2,3),
425 (7,10,3,1,'6756045108644',2,3),
426 (9,11,8,3,'6912051554350',3,8),
427 (8,12,8,16,'2167270938438',11,8);
428
429
430--/////////////////////////////////////////////////////////PET SQL UPITA///////////////////////////////////////////////////////--
431
432SELECT z_ime+' '+z_prezime as 'Ime i Prezime',AVG(rezultat_testa) FROM HR_Sluzba.Parametar_pripada
433inner join HR_Sluzba.Zaposlen on(HR_Sluzba.Parametar_pripada.mbr=HR_Sluzba.Zaposlen.mbr)
434group by z_ime,z_prezime;
435
436
437SELECT rezultat_testa,AVG(DATEDIFF(year, z_datum_zap, datum_testiranja)) as 'Broj godina radnog odnosa' from HR_Sluzba.Zaposlen
438inner JOIN HR_Sluzba.Evaluacioni_Test on (HR_Sluzba.Zaposlen.mbr=HR_Sluzba.Evaluacioni_Test.mbr)
439inner join HR_Sluzba.Parametar_pripada on(HR_Sluzba.Parametar_pripada.mbr=HR_Sluzba.Evaluacioni_Test.mbr)
440group by rezultat_testa
441order by rezultat_testa desc;