-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPoblamiento BANCOS.sql
330 lines (265 loc) · 15.2 KB
/
Poblamiento BANCOS.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
Use ProyectoServiu;
go
insert into dbo.Banco values (1,'Banco Estado');
insert into dbo.Banco values (2,'Banco Chile');
insert into dbo.Banco values (3,'Banco BCI');
insert into dbo.Banco values (4,'Banco CorpBanca');
insert into dbo.Banco values (5,'Banco Santander');
insert into dbo.Banco values (6,'Banco Falabella');
insert into dbo.Banco values (7,'Banco Itaú');
insert into dbo.Banco values (8,'Banco Security');
insert into dbo.Banco values (9,'Banco Bice');
insert into dbo.Banco values (10,'Banco Penta');
go
insert into dbo.Nacionalidad values (1,'Chileno');
insert into dbo.Nacionalidad values (2,'Extranjero');
insert into dbo.Nacionalidad values (3,'NN');
go
insert into dbo.Region values (1,'Tarapaca');
insert into dbo.Region values (2,'Antofagasta');
insert into dbo.Region values (3,'Atacama');
insert into dbo.Region values (4,'Coquimbo');
insert into dbo.Region values (5,'Valparaiso');
insert into dbo.Region values (6,'Libertador General Bernardo O’Higgins');
insert into dbo.Region values (7,'Maule');
insert into dbo.Region values (8,'Concepción');
insert into dbo.Region values (9,'Araucania');
insert into dbo.Region values (10,'Los Lagos');
insert into dbo.Region values (11,'Aysén del General Carlos Ibañez del Campo');
insert into dbo.Region values (12,'Magallanes y de la Antártica Chilena')
insert into dbo.Region values (13,'Metropolitana de Santiago')
insert into dbo.Region values (14,'De los Rios')
insert into dbo.Region values (15,'Arica y Parinacota')
insert into dbo.Region values (16,'Ñuble')
go
insert into dbo.Comuna values (1,'Alto Hospicio',1);
insert into dbo.Comuna values (2,'Iquique',1);
insert into dbo.Comuna values (3,'Colchane',1);
insert into dbo.Comuna values (4,'TalTal',2);
insert into dbo.Comuna values (5,'Calama',2);
insert into dbo.Comuna values (6,'Ollague',2);
insert into dbo.Comuna values (7,'San Pedro de atacama',2);
insert into dbo.Comuna values (8,'Diego de almagro',3);
insert into dbo.Comuna values (9,'Caldera',3);
insert into dbo.Comuna values (10,'Tierra Amarilla',3);
insert into dbo.Comuna values (11,'Andacollo',4);
insert into dbo.Comuna values (12,'Ovalle',4);
insert into dbo.Comuna values (13,'MontePatria',4);
insert into dbo.Comuna values (14,'Quintero',5);
insert into dbo.Comuna values (15,'Puchuncavi',5);
insert into dbo.Comuna values (16,'Juan Fernandez',5);
insert into dbo.Comuna values (17,'Colchagua',6);
insert into dbo.Comuna values (18,'Cachapoal',6);
insert into dbo.Comuna values (19,'Cardenal Caro',6);
insert into dbo.Comuna values (20,'Curepto',7);
insert into dbo.Comuna values (21,'Pelarco',7);
insert into dbo.Comuna values (22,'San Clemente',7);
insert into dbo.Comuna values (23,'Coronel',8);
insert into dbo.Comuna values (23,'Tome',8);
insert into dbo.Comuna values (25,'Talcahuano',8);
insert into dbo.Comuna values (26,'Angol',9);
insert into dbo.Comuna values (27,'Collipulli',9);
insert into dbo.Comuna values (28,'Curacautin',9);
insert into dbo.Comuna values (29,'Puerto Varas',10);
insert into dbo.Comuna values (30,'Osorno',10);
insert into dbo.Comuna values (31,'Purranque',10);
insert into dbo.Comuna values (32,'Chile Chico',11);
insert into dbo.Comuna values (33,'Lago Verde',11);
insert into dbo.Comuna values (34,'Tortel',11);
insert into dbo.Comuna values (35,'Laguna Blanca',12);
insert into dbo.Comuna values (36,'Punta Arenas',12);
insert into dbo.Comuna values (37,'Rio Verde',12);
insert into dbo.Comuna values (38,'Santiago',13);
insert into dbo.Comuna values (39,'San Bernardo',13);
insert into dbo.Comuna values (40,'Puente Alto',13);
insert into dbo.Comuna values (41,'Nuñoa',13);
insert into dbo.Comuna values (42,'Santiago',13);
insert into dbo.Comuna values (43,'Providencia',13);
insert into dbo.Comuna values (44,'Maipu',13);
insert into dbo.Comuna values (45,'Macul',13);
insert into dbo.Comuna values (46,'Las Condes',13);
insert into dbo.Comuna values (47,'Vitacura',13);
insert into dbo.Comuna values (48,'La Pintana',13);
insert into dbo.Comuna values (49,'La Florida',13);
insert into dbo.Comuna values (50,'Renca',13);
insert into dbo.Comuna values (51,'Lo Prado',13);
insert into dbo.Comuna values (52,'Conchali',13);
insert into dbo.Comuna values (53,'Quilicura',13);
insert into dbo.Comuna values (54,'Quinta Normal',13);
insert into dbo.Comuna values (55,'Lo Espejo',13);
insert into dbo.Comuna values (56,'Estacion Central',13);
insert into dbo.Comuna values (57,'El Bosque',13);
insert into dbo.Comuna values (58,'Valdivia',14);
insert into dbo.Comuna values (59,'La union',14);
insert into dbo.Comuna values (60,'Lago Ranco',14);
insert into dbo.Comuna values (61,'Arica',15);
insert into dbo.Comuna values (62,'Putre',15);
insert into dbo.Comuna values (63,'Camarones',15);
go
insert into dbo.Propietario values (169199957,'Carlos','Ortiz','Mardones',958586705,'cortizmardones@gmail.com');
insert into dbo.Propietario values (184997142,'Ricardo','Soto','Gomez',995875633,'gomez.soto.ricardo@gmail.com');
insert into dbo.Propietario values (163002957,'Gloria','Mora','Crema',992243213,'crema.mora.gloria@gmail.com');
insert into dbo.Propietario values (219684886,'Javier','Santaolalla','Marin',925817396,'javier_santamarin@gmail.com');
insert into dbo.Propietario values (178569465,'Mercedes','Sosa','Romero',982574891,'mer_romero_sosa@gmail.com');
go
insert into dbo.Tramo_vivienda values (1,'1800');
insert into dbo.Tramo_vivienda values (2,'2400');
insert into dbo.Tramo_vivienda values (3,'2800');
go
insert into dbo.Tipo_Cuenta values (1,'Cuenta Corriente');
insert into dbo.Tipo_Cuenta values (2,'Cuenta Vista');
insert into dbo.Tipo_Cuenta values (3,'Cuenta Ahorro');
go
insert into dbo.Pueblo_Originario values ('No pertenece',0,0);
insert into dbo.Pueblo_Originario values ('Aymara',1,500);
insert into dbo.Pueblo_Originario values ('Quechua',1,500);
insert into dbo.Pueblo_Originario values ('Atacameño',1,500);
insert into dbo.Pueblo_Originario values ('kolla',1,500);
insert into dbo.Pueblo_Originario values ('Diaguita',1,500);
insert into dbo.Pueblo_Originario values ('Rapanui',1,500);
insert into dbo.Pueblo_Originario values ('Mapuche',1,500);
insert into dbo.Pueblo_Originario values ('Yagán',1,500);
insert into dbo.Pueblo_Originario values ('Chango',1,500);
insert into dbo.Pueblo_Originario values ('Picunche',1,500);
insert into dbo.Pueblo_Originario values ('Cunco',1,500);
insert into dbo.Pueblo_Originario values ('Chono',1,500);
go --select * from Certificado_permanencia; --Cree la tabla con identy
insert into Certificado_permanencia values ('Sin certificado',0);
insert into Certificado_permanencia values ('Con certificado',1);
go
insert into relacion_carga values (1,'Conyuge');
insert into relacion_carga values (2,'Hijo - Hija');
insert into relacion_carga values (3,'Nieto - Nieta');
insert into relacion_carga values (4,'Otro');
go
insert into dbo.Conyuge values (171487965,'Camila','Sanchez','Somoza','19880322',1);
insert into dbo.Conyuge values (139943317,'Claudio','Angulo','Briones','19600718',1);
insert into dbo.Conyuge values (117236125,'German','Torres','Carrasco','19850410',2);
insert into dbo.Conyuge values (105432881,'Carla','Mardones','Quilodran','19550508',2);
insert into dbo.Conyuge values (208334721,'Venancio','Castro','Soto','20001001',1);
go
insert into Carga values (240224932,'Esteban','Paredes','Gonzales','20190322','2019-07-23','2019-07-23');
insert into Carga values (265803175,'Ricardo','Milos','Ugarte','20190512','2019-07-23','2019-07-23');
insert into Carga values (206683183,'Maria','Estanford','Maluenda','20150712','2019-07-23','2019-07-23');
insert into Carga values (208956723,'Isabel','Mardones','Picaporte','20150902','2019-07-23','2019-07-23');
insert into Carga values (192239378,'Diego','Llanquin','Astroza','20131101','2019-07-23','2019-07-23');
go
insert into dbo.UF values (1,27592,'11-05-2019');
insert into dbo.UF values (2,27600,'12-05-2019');
insert into dbo.UF values (3,27603,'13-05-2019');
insert into dbo.UF values (4,27604,'14-05-2019');
insert into dbo.UF values (5,27660,'15-05-2019');
insert into dbo.UF values (6,27670,'16-05-2019');
insert into dbo.UF values (7,27675,'17-05-2019');
insert into dbo.UF values (8,27678,'18-05-2019');
insert into dbo.UF values (9,27700,'19-05-2019');
insert into dbo.UF values (10,27705,'20-05-2019');
go
insert into dbo.Consultor values (105395949,'Emilio','Sanhueza','esanhueza@minvu.cl',958586705);
insert into dbo.Consultor values (206595302,'Estefanie','Milan','emilan@minvu.cl',93578143);
insert into dbo.Consultor values (148956723,'Nevenka','Malloa','nmalloa@minvu.cl',972148931);
insert into dbo.Consultor values (210276580,'Raul','Pardo','rpardo@minvu.cl',930246705);
insert into dbo.Consultor values (55461066,'Jose','Collio','jcollio@minvu.cl',915667188);
go
insert into cuenta values (667788,3,1,8500000,'2019-07-23','2019-07-23');
insert into cuenta values (327195,3,10,9275833,'2019-07-23','2019-07-23');
insert into cuenta values (167284,3,6,17500423,'2019-07-23','2019-07-23');
insert into cuenta values (102871,3,8,12799000,'2019-07-23','2019-07-23');
insert into cuenta values (897726,3,4,233000,'2019-07-23','2019-07-23');
go
insert into dbo.Deposito values (1,8500000);
insert into dbo.Deposito values (2,9275833);
insert into dbo.Deposito values (3,17500423);
insert into dbo.Deposito values (4,12799000);
insert into dbo.Deposito values (5,233000);
go
insert into dbo.Deposito_Cuenta values (1,667788,1,'2019-07-23');
insert into dbo.Deposito_Cuenta values (2,327195,2,'2019-07-23');
insert into dbo.Deposito_Cuenta values (3,167284,3,'2019-07-23');
insert into dbo.Deposito_Cuenta values (4,102871,4,'2019-07-23');
insert into dbo.Deposito_Cuenta values (5,897726,5,'2019-07-23');
go
insert into dbo.Domicilio values ('El concretero','01798','','','','',1,'2019-07-23',1,'San Jose de la Construcción','Cordillera','2019-07-23','2019-07-23');
insert into dbo.Domicilio values ('Avenida Ejercito Libertador','215','C','33','','',1,'2019-07-23',1,'Los Alerces','Santiago Centro','2019-07-23','2019-07-23');
insert into dbo.Domicilio values ('Bron Sur','2520','','77B','','',1,'2019-07-23',51,'Santa Julia','Frente al consultorio','2019-07-23','2019-07-23');
insert into dbo.Domicilio values ('Callejon Capturi','77193','','','','',1,'2019-07-23',9,'Peninsula de fuego','A un costado del estadio municipal','2019-07-23','2019-07-23');
insert into dbo.Domicilio values ('Travesia Baratem','01644','','','','',1,'2019-07-23',40,'Catemito Norte','Cerca del terminal rodoviario','2019-07-23','2019-07-23');
go
insert into Tipo_vivienda values (1,'Casa');
insert into Tipo_vivienda values (2,'Departamento');
go
insert into Vivienda values (1,'Casa Viena',2600,1, 3,163002957,1);
insert into Vivienda values (2,'Casa Londres',2000,1,2,169199957,2);
insert into Vivienda values (3,'Casa Niza',1450,1,1,178569465,3);
insert into Vivienda values (4,'Departamento Sevilla',2799,2,3,184997142,4);
insert into Vivienda values (5,'Casa Milán',2100,1,2,219684886,5);
go
--TABLA PARAMETRICA
insert into Estado_Civil values (1,'Soltero',60);
insert into Estado_Civil values (2,'Casado',100);
insert into Estado_Civil values (3,'Viudo',20);
insert into Estado_Civil values (4,'Conviviente Civil',80);
insert into Estado_Civil values (5,'Divorciado',40);
go
--TABLA PARAMETRICA
insert into tipo_titulo values (1,'Título Profesional',200);
insert into tipo_titulo values (2,'Título Técnico',100);
insert into tipo_titulo values (3,'Sin titulo',0);
go
insert into Titulo values (1,'Sin Titulo',0,3);
insert into Titulo values (2,'Ingeniero en Informatica',1,1);
insert into Titulo values (3,'Analista Programador Computacional',1,2);
insert into Titulo values (4,'Arquitecto de Software',1,1);
insert into Titulo values (5,'Asistente de soporte',1,2);
insert into Titulo values (6,'Ingeniero en redes y telecomunicaciones',1,1);
go
insert into Postulante values (169199957,'Carlos','Alberto','Ortiz','Mardones',2557036,91234567,91234567,0140000,'cortizmardones@gmail.com','1988-03-22',32,1,2,102871,1,1350000,8,2,1,'2019-07-23','2019-07-23');
insert into Postulante values (155106514,'Romina','Alejandra','Jara','Romero',2557127,92569871,920897451,8320000,'romina.jara@gmail.com','1988-03-22',40,2,5,167284,2,1750000,2,5,2,'2019-07-23','2019-07-23');
insert into Postulante values (171302838,'Francisco','Orlando','Bernales','Ortiz',26567762,982634572,983266942,8320000,'francisco.bernales@gmail.com','1988-03-22',55,1,4,327195,3,2100000,2,1,1,'2019-07-23','2019-07-23');
insert into Postulante values (94737419,'Nicole','Andrea','Mardones','Quilodran',26567763,982547612,921456172,2340000,'nicole.mardones@gmail.com','1988-03-22',29,2,3,667788,4,1999000,2,4,1,'2019-07-23','2019-07-23');
insert into Postulante values (222872707,'Claudio','Andres','Sandoval','Matus',26567766,963415782,993628876,4030000,'claudio.sandoval@gmail.com','1988-03-22',19,1,1,897726,5,1192540,4,6,1,'2019-07-23','2019-07-23');
go
insert into Postulante_Conyuge values (169199957,105432881,'20190401',1,1);
insert into Postulante_Conyuge values (155106514,117236125,'20190415',1,2);
insert into Postulante_Conyuge values (171302838,139943317,'20190522',1,3);
insert into Postulante_Conyuge values (94737419,171487965,'20190413',1,4);
insert into Postulante_Conyuge values (222872707,208334721,'20190501',1,5);
go
insert into Postulante_Carga values (169199957,192239378,1,1,'2019-07-23','2019-07-23');
insert into Postulante_Carga values (155106514,206683183,1,2,'2019-07-23','2019-07-23');
insert into Postulante_Carga values (171302838,208956723,1,3,'2019-07-23','2019-07-23');
insert into Postulante_Carga values (94737419,240224932,1,4,'2019-07-23','2019-07-23');
insert into Postulante_Carga values (222872707,265803175,1,1,'2019-07-23','2019-07-23');
go
insert into Formulario_A values (8081,'2019-07-23','Ernesto Perez',169199957,1);
insert into Formulario_A values (8082,'2019-07-23','Ernesto Perez',155106514,2);
insert into Formulario_A values (8083,'2019-07-23','Fabiola Tapia',171302838,3);
insert into Formulario_A values (8084,'2019-07-23','Ernesto Perez',94737419,4);
insert into Formulario_A values (8085,'2019-07-23','Ernesto Perez',222872707,5);
go
insert into Informe_Tecnico values (8081,'2019-07-23','2019-07-23',8081,1,55461066);
insert into Informe_Tecnico values (8082,'2019-07-23','2019-07-23',8082,2,105395949);
insert into Informe_Tecnico values (8083,'2019-07-23','2019-07-23',8083,3,148956723);
insert into Informe_Tecnico values (8084,'2019-07-23','2019-07-23',8084,4,206595302);
insert into Informe_Tecnico values (8085,'2019-07-23','2019-07-23',8085,5,210276580);
go
--TABLA PARAMETRICA
insert into Tramo_Edad values (1,18,29,200);
insert into Tramo_Edad values (2,30,39,150);
insert into Tramo_Edad values (3,40,50,100);
go
--TABLA PARAMETRICA
insert into Tramo_Carga_Familiar values (1,5,11,200);
insert into Tramo_Carga_Familiar values (2,2,4,100);
insert into Tramo_Carga_Familiar values (3,0,1,50);
go
--TABLA PARAMETRICA
insert into Tramo_Ahorro values(1,10000000,99000000,500);
insert into Tramo_Ahorro values(2,9000000,9999999,400);
insert into Tramo_Ahorro values(3,8000000,8999999,200);
go
insert into error values(1,'El Rut ingresado ya existe en la BDD');
insert into error values(2,'La cuenta bancaria ya existe en la BDD');
insert into error values(3,'El rut consultado no se encuentra en la BDD');
insert into error values(4,'El rut de la carga familiar ya ha sido ingresado');
--SCRIPT BIEN HECHO , RESPETANDO INTEGRIDAD REFERENCIAL A LA HORA DE SER CARGADO (32 TABLAS HASTA EL MOMENTO)