-
Notifications
You must be signed in to change notification settings - Fork 1
/
Data.sql
333 lines (273 loc) · 12 KB
/
Data.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
331
332
333
/*CREACION DE LA BASE DE DATOS */
CREATE DATABASE PROYECTO;
/*INGRESAMOS A LA BASE DE DATOS PARA AGREGAR
TABLAS Y DATOS A LAS MISMAS*/
USE PROYECTO;
/*LA TABLA CLIENTE CONTENDRA DATOS DEL CLIENTE
Y SU CEDULA(DNI) VA A SER SU CLAVE PRIMARIA
#('cedula','Nombre','Apellido',FECHA_ASOCIADO)*/
CREATE TABLE CLIENTE(
DNI VARCHAR(11) NOT NULL PRIMARY KEY,
NOMBRE VARCHAR(20) NOT NULL,
APELLIDO VARCHAR(20) NOT NULL,
FECHA_ASOCIADO DATE NOT NULL
);
/*CREAMOS LA TABLA CONTACTO LA CUAL VA A CONTENER
LOS DATOS DE CONTACTO DE LOS CLIENTES
COMO PRIMARY KEY y FOREIGN KEY SU DNI
('cedula','telefono','celular','correo')*/
CREATE TABLE CONTACTO(
DNI VARCHAR(11) NOT NULL PRIMARY KEY,
TELEFONO VARCHAR(13),
CELULAR VARCHAR(11),
CORREO VARCHAR(25),
FOREIGN KEY (DNI)
REFERENCES CLIENTE(DNI),
CHECK( TELEFONO !=NULL OR CELULAR!= NULL)
);
/*CREACION DE LA TABLA DIRECCION
LA CUAL VA A CONTENER LA DIRECCION DEL CLIENTE
COMO PRIMARY KEY y FOREIGN KEY SU DNI
('cedula',''cidudad','calle1','calle2','Descripcion')*/
CREATE TABLE DIRECCION(
DNI VARCHAR(11) NOT NULL PRIMARY KEY,
CIUDAD VARCHAR(20) NOT NULL,
MANZANA VARCHAR(10) NOT NULL,
VILLA VARCHAR(10),
SECTOR VARCHAR(25),
FOREIGN KEY (DNI)
REFERENCES CLIENTE(DNI)
);
/*CREACION DE LA TABLA COMPRA
LA CUAL VA A TENER DATOS DE LA COMPRA
QUE EL CLIENTE REALISE
Y COMO PRIMARY KEY SU ID EL CUAL VA A SER AUTO_INCREMET
('CELUDA','FECHA_EMISION',NUM_TARGETAS,PRECIO_TARGETA,'ESTADO')*/
CREATE TABLE COMPRA(
ID INT AUTO_INCREMENT PRIMARY KEY,
DNI_CLIENTE VARCHAR(11) NOT NULL,
FECHA_EMISION DATE NOT NULL,
NUM_TARGETAS INT NOT NULL,
PRECIO_TARGETA FLOAT NOT NULL,
ESTADO VARCHAR(7) DEFAULT 'Debe',
CHECK (ESTADO='Pagado' OR ESTADO='Debe'),
FOREIGN KEY (DNI_CLIENTE) REFERENCES CLIENTE(DNI)
);
/* CREACION DE LA TABLA CHOFER DEL BUS
LA CUAL TENDRA SU INFORMACION
('DNI',NOMBRE','APELLIDO','TELEFONO','CELULAR')*/
CREATE TABLE CHOFER(
DNI VARCHAR(11) NOT NULL PRIMARY KEY,
NOMBRE VARCHAR(20) NOT NULL,
APELLIDO VARCHAR(20) NOT NULL,
TELEFONO VARCHAR(13),
CELULAR VARCHAR(12),
CHECK( TELEFONO !=NULL OR CELULAR!= NULL)
);
/*CREACION DE LA TABLA BUS LA CUAL TENDRA SU INFORMACION
CON LA PLACA COMO PRIMARY KEY
Y DNI_CLIENTE y DNI_CHOFER COMO FOREIGN KEY
('PLACA','DNI_CLIENTE','DNI_CHOFER','COPERATIVA')*/
CREATE TABLE BUS(
PLACA VARCHAR(9) PRIMARY KEY,
DNI_CLIENTE VARCHAR(11) NOT NULL,
DNI_CHOFER VARCHAR(11),
COOPERATIVA VARCHAR(30) NOT NULL,
FOREIGN KEY (DNI_CLIENTE) REFERENCES CLIENTE(DNI),
FOREIGN KEY (DNI_CHOFER) REFERENCES CHOFER(DNI)
);
/*SE INSERTAN LOS DATOS EN LA BASE*/
-- DNI_CLIENTE,NOMBRE,APELLIDO,FECHA_ASOCIADO
INSERT INTO CLIENTE
VALUES
('182756438','Luis', 'Aguirre','2000-09-30'),
('092374856','Celso','Aguirre','2001-02-23'),
('063453218','Enrique' ,'Aponte','2000-04-24'),
('086534213','Shirley', 'Buenanio','2001-07-12'),
('185478671','Javier', 'Campos','2000-03-22'),
('067889234','Cecilia',' Espinoza','2001-05-23'),
('098231456','Elena', 'Espinoza','2002-04-12'),
('067392940','Genni' ,'Espinoza','2001-03-14'),
('181244758','Robinson',' Espinoza','2000-04-22'),
('094214567','Socrates', 'Espinoza', '2001-06-23'),
('083324567','Angel','Espinoza','2000-02-11'),
('092112344','Marina','Freire','2002-08-14' ),
('086512358','Alberto',' Garcia','2002-07-12'),
('076579986','Luis','Garzon','2000-02-25'),
('064321345','Marianita','Garzon','2000-03-12'),
('182456789','Juan', 'Guevara','2002-06-29'),
('098764321','Luis',' Magallanes','2000-05-23'),
('131837495','Daice', 'Martinez','2003-01-11'),
('028394950','Maria', 'Martinez','2000-03-26'),
('180228475','Nelson',' Paredes','2001-06-15');
-- DNI_CLIENTE,telefono,celular,CORREO
INSERT INTO CONTACTO
VALUES
('182756438','213642','091628399','[email protected]'),
('092374856','278495','096273741','[email protected]'),
('063453218','273948','097387389','[email protected]'),
('086534213','276451','093748484','[email protected]'),
('185478671','266348','092727378','[email protected]' ),
('067889234','267394','092738485','[email protected]'),
('098231456','262738','092727172','[email protected]'),
('067392940','276378','093873728','[email protected]'),
('181244758','277849','092827162','[email protected]'),
('094214567','276384','092736613','[email protected]'),
('083324567','219203','092826254','[email protected]'),
('092112344','208594','092377378','[email protected]' ),
('086512358','287727','098162634','[email protected]'),
('076579986','278199','098482745','[email protected]'),
('064321345','299102','093826634','[email protected]'),
('182456789','289919','091615523','[email protected]'),
('098764321','288283','092716178','[email protected]'),
('131837495','287384','091773734','[email protected]'),
('028394950','289299','091727734','[email protected]'),
('180228475','299494','091717273','[email protected]');
-- DNI_CLIENTE,CIUDAD,MANZANA,VILLA,SECTOR
INSERT INTO DIRECCION
VALUES
('182756438','Guayaquil','MZ8','V3','geranios'),
('092374856','Guayaquil','MZ5','V2','orquideas'),
('063453218','Guayaquil','MZ7','V8','mucholote'),
('086534213','Guayaquil','MZ2','V9','colinasdelmaestro'),
('185478671','Guayaquil','MZ8','V2','sauces'),
('067889234','Guayaquil','MZ3','V2','rosales'),
('098231456','Guayaquil','MZ4','V5','orquideas'),
('067392940','Guayaquil','MZ02','V3','prosperina'),
('181244758','Guayaquil','MZ1','V2','bastionpopular'),
('094214567','Guayaquil','MZ1','V02','alborada'),
('083324567','Guayaquil','MZ9','V16','garzota'),
('092112344','Guayaquil','MZ5','V3','geranios'),
('086512358','Guayaquil','MZ16','V2','mucholote'),
('076579986','Guayaquil','MZ3','V1','huancavilca'),
('064321345','Guayaquil','MZ2','V9','samanes'),
('182456789','Guayaquil','MZ1','V6','geranios'),
('098764321','Guayaquil','MZ3','V9','orquideas'),
('131837495','Guayaquil','MZ5','V29','rosales'),
('028394950','Guayaquil','MZ6','V8','orquideas'),
('180228475','Guayaquil','MZ9','V2','prosperina');
-- DNI_CLIENTE,FECHA_EMISION,NUM_TARGETAS,PRECIO_TARGETA,ESTADO
INSERT INTO COMPRA (DNI_CLIENTE,FECHA_EMISION,NUM_TARGETAS,PRECIO_TARGETA,ESTADO)
VALUES
('182756438','2018-10-25',7,110,'Deuda'),
('092374856','2018-09-11',6,90,'Deuda'),
('063453218','2018-11-09',5,130,'Deuda'),
('086534213','2018-11-21',10,160,'Deuda'),
('185478671','2018-10-02',12,96,'Deuda'),
('067889234','2018-10-30',7,210,'Deuda'),
('098231456','2018-11-11',7,250,'Deuda'),
('067392940','2018-09-27',9,155,'Deuda'),
('181244758','2018-10-02',11,290,'Deuda'),
('094214567','2018-10-28',14,320,'Deuda'),
('083324567','2018-11-12',11,300,'Deuda'),
('092112344','2018-11-18',5,55,'Deuda'),
('086512358','2018-11-13',9,110,'Deuda'),
('076579986','2018-09-08',10,270,'Deuda'),
('064321345','2018-09-11',10,135,'Deuda'),
('182456789','2018-09-22',12,110,'Deuda'),
('098764321','2018-09-15',14,230,'Deuda'),
('131837495','2018-10-09',12,350,'Deuda'),
('028394950','2018-10-30',7,110,'Deuda'),
('180228475','2018-11-11',5,85,'Deuda');
-- DNI_CHOFER,NOMBRE,APELLIDO,telefono,celular
INSERT INTO CHOFER
VALUES
('137683904','Fabian','Sanchez','289138','097645217'),
('095362718','Paul','Garzon','261685','097515364'),
('126485934','Fabricio','Paredes','274683','092364758'),
('128374859','Danilo','Urrutia','273553','097542173'),
('126474859','Erik','Morales','274890','095623728'),
('093756838','Gonzalo','Aponte','271689','095371846'),
('068364829','Galo','Gavilanez','215368','096546272'),
('126476327','Yumber','Maticuerema','274892','091637483'),
('086372748','Pedro','Suarez','285903','095627183'),
('134847200','Jose','Arzube','285903','094262731'),
('078462749','Raul','Zambrano','217348','092153478'),
('063826384','Diego','Guevara','274802','091637483'),
('137472846','Alejandro','Gonzales','271664','093271839'),
('093827464','Humberto','Pazminio','284792','093253648'),
('126374849','Rodrigo','Martinez','281795','091264748'),
('092736462','Nelson','Paredes','285981','093271738'),
('117384729','Daniel','Morales','271654','096748382'),
('093746274','Paul','Villacis','285984','092352637'),
('092837182','Santiago','Alcivar','274891','092153748'),
('092737482','Xavier','Peralta','274892','093276441');
-- Placa,CLIENTE,CHOFER,COOPERATIVA
INSERT INTO BUS
VALUES
('GBG-536','182756438','137683904','10 de Agosto'),
('GBG-113','092374856','095362718','10 de Agosto'),
('GBG-244','063453218','126485934','10 de Agosto'),
('GBG-356','086534213','128374859','10 de Agosto'),
('GBG-739','185478671','126474859','10 de Agosto'),
('GBG-046','067889234','093756838','10 de Agosto'),
('GBG-859','098231456','068364829','10 de Agosto'),
('GBG-261','067392940','126476327','10 de Agosto'),
('GBG-078','181244758','086372748','10 de Agosto'),
('GBG-288','094214567','134847200','10 de Agosto'),
('GBG-996','083324567','078462749','10 de Agosto'),
('GBG-764','092112344','063826384','10 de Agosto'),
('GBG-254','086512358','137472846','10 de Agosto'),
('GBG-141','076579986','093827464','10 de Agosto'),
('GBG-237','064321345','126374849','10 de Agosto'),
('GBG-857','182456789','092736462','10 de Agosto'),
('GBG-998','098764321','117384729','10 de Agosto'),
('GBG-702','131837495','093746274','10 de Agosto'),
('GBG-805','028394950','092837182','10 de Agosto'),
('GBG-908','180228475','092737482','10 de Agosto');
SELECT * FROM CLIENTE;
SELECT * FROM CONTACTO;
SELECT * FROM DIRECCION;
SELECT * FROM COMPRA;
SELECT * FROM CHOFER;
SELECT * FROM BUS;
-- Creacion de Vistas Para los Reportes
-- Este Reporte muestra el total de compras por cliente
CREATE VIEW TOTAL_CLIENTE AS SELECT C.DNI,C.NOMBRE,C.APELLIDO,SUM(CO.NUM_TARGETAS) AS TOTAL_TARGETAS,SUM(CO.PRECIO_TARGETA) AS TOTAL_PRECIO FROM CLIENTE AS C INNER JOIN COMPRA AS CO ON C.DNI=CO.DNI_CLIENTE
GROUP BY C.DNI;
-- Este Reporte muestra el total de compras por cliente en el mes de Septiembre del 2018
CREATE VIEW TOTAL_CLIENTE1 AS SELECT C.DNI,C.NOMBRE,C.APELLIDO,SUM(CO.NUM_TARGETAS) AS TOTAL_TARGETAS,SUM(CO.PRECIO_TARGETA) AS TOTAL_PRECIO FROM CLIENTE AS C INNER JOIN COMPRA AS CO ON C.DNI=CO.DNI_CLIENTE
where FECHA_EMISION>='2018-09-01' and FECHA_EMISION<'2018-10-01'
GROUP BY C.DNI;
-- Este Reporte muestra el total de compras por cliente en el mes Octubre del 2018
CREATE VIEW TOTAL_CLIENTE2 AS SELECT C.DNI,C.NOMBRE,C.APELLIDO,SUM(CO.NUM_TARGETAS) AS TOTAL_TARGETAS,SUM(CO.PRECIO_TARGETA) AS TOTAL_PRECIO FROM CLIENTE AS C INNER JOIN COMPRA AS CO ON C.DNI=CO.DNI_CLIENTE
where FECHA_EMISION>='2018-10-01' and FECHA_EMISION<'2018-11-01'
GROUP BY C.DNI;
-- Este Reporte muestra el total de compras por cliente en el mes Novienbre del 2018
CREATE VIEW TOTAL_CLIENTE3 AS SELECT C.DNI,C.NOMBRE,C.APELLIDO,SUM(CO.NUM_TARGETAS) AS TOTAL_TARGETAS,SUM(CO.PRECIO_TARGETA) AS TOTAL_PRECIO FROM CLIENTE AS C INNER JOIN COMPRA AS CO ON C.DNI=CO.DNI_CLIENTE
where FECHA_EMISION>='2018-11-01' and FECHA_EMISION<'2018-12-01'
GROUP BY C.DNI;
-- Este Reporte muestra el promedio de compras por cliente
CREATE VIEW PROMEDIO_CLIENTE AS SELECT C.DNI,C.NOMBRE,C.APELLIDO,AVG(CO.NUM_TARGETAS) AS PROMEDIO_TARGETAS,AVG(CO.PRECIO_TARGETA) AS PROMEDIO_PRECIO FROM CLIENTE AS C INNER JOIN COMPRA AS CO ON C.DNI=CO.DNI_CLIENTE
GROUP BY C.DNI;
-- Este reporte muestra el numero de unidades que tiene cada cliente
CREATE VIEW UNIDADES_CLIENTE AS SELECT C.DNI,C.NOMBRE,C.APELLIDO,COUNT(B.PLACA) AS Total_Unidades FROM CLIENTE AS C INNER JOIN BUS AS B ON C.DNI=B.DNI_CLIENTE
GROUP BY C.DNI;
-- crear un procedimiento para saber el monto de dinero que el cliente esta debiendo
delimiter $$
create procedure Deuda(in id varchar(11),out valor float)
begin
SET valor=(SELECT SUM(CO.PRECIO_TARGETA) AS TOTAL_PRECIO FROM CLIENTE AS C INNER JOIN COMPRA AS CO ON C.DNI=CO.DNI_CLIENTE
where DNI=id and ESTADO='Deuda'
GROUP BY C.DNI);
end;
$$ delimiter ;
-- crear un disparado para que le sume un 5% a la venta si el cliente debe mas de 150 dolares
delimiter $$
create trigger Sum_Deuda BEFORE INSERT ON COMPRA
FOR EACH ROW
BEGIN
declare v float;
call Deuda(now.DNI_CLIENTE,@val);
SET v=(SELECT @val);
if v>150 then
set new.PRECIO_TARGETA=new.PRECIO_TARGETA + new.PRECIO_TARGETA*0.05;
else
set new.PRECIO_TARGETA=new.PRECIO_TARGETA;
end if;
END;
$$ DELIMITER ;
describe COMPRA;
-- CREACION DE INDICE: SE TOMA EN CUENTA QUE SE FILTRA MAS POR CLIENTE, POR LO QUE
-- SE OPTO POR CREAR UN INDICE EN LA TABLA CLIENTE EN LA COLUMNA DNI
CREATE INDEX IND_DNI ON CLIENTE(DNI);