triggers

create trigger insertar_persona2 AFTER INSERT ON persona
FOR EACH ROW
INSERT INTO persona2 (nombre_nuevo, apellido_nuevo, telefono_nuevo, direccion_nuevo, email_nuevo, celular_nuevo, usuario. modificado, proceso, idpersona);


create trigger modifica_persona2 before update on persona
FOR EACH ROW
INSERT INTO persona2 (nombre_anterior, apellido_anterior, telefono_anterior, direccion_anterior, email_anterior, celular_anterior,nombre_nuevo, apellido_nuevo, telefono_nuevo, direccion_nuevo, email_nuevo, celular_nuevo, usuario, modificado,  idpersona)
values (old.nombre, old.apellido,old.telefono,old.direccion, old.email, old.celular, new.nombre, new.apellido,new.telefono, new.direccion, new.email, new.celular, current_user(), now(), new.idpersona);

Procedimiento Abogados

delimiter //
CREATE PROCEDURE audiencia (in idpersona VARCHAR(10))
block1: BEGIN
declare  ultfech date;
declare  cont int (20) ;
declare  idcaso1 varchar (20) ;
declare cursor1 for select audiencia.fecha from audiencia;persona;caso;tipocaso where audiencia.idcaso=caso.idcaso
and persona.idpersona=audiencia.idacusado
and caso.idtipocaso=tipocaso.idtipocaso
and audiencia.fecha>=( DATE_ADD( CURDATE() ,INTERVAL-12 MONTH))
cont=0;
OPEN cursor1;
cursor1: LOOP
FETCH cursor1 Into  ultfech
if ( ultfech >=( DATE_ADD( CURDATE() ,INTERVAL-12 MONTH))) then
cont=cont+1;
END IF
END LOOP cursor1;
if (cont=0)then
UPDATE caso SET idestadocaso="1" WHERE  idcaso=idcaso;
END IF
close  audiencia;

//

Base de Datos Abogados

DROP DATABASE IF EXISTS acta;

CREATE DATABASE caso;

USE  caso;

DROP TABLE IF EXISTS persona;
CREATE TABLE persona(idpersona varchar(10) not null,
nombres varchar(100) not null, apellidos varchar(100) not null,
telefono varchar(15) not null, direccion varchar(100) not null,
email varchar(100), celular varchar(20),
PRIMARY KEY(idpersona));

DROP TABLE IF EXISTS rol;
CREATE TABLE rol (idrol varchar(2) not null,
descripcion varchar(100) not null,
PRIMARY KEY(idrol));

DROP TABLE IF EXISTS tipocaso;
CREATE TABLE tipocaso (idtipocaso varchar(2) not null,
descripcion varchar(100) not null,
PRIMARY KEY(idtipocaso));

DROP TABLE IF EXISTS fallo;
CREATE TABLE fallo (idfallo varchar(2) not null,
descripcion varchar(100) not null,
PRIMARY KEY(idfallo));

DROP TABLE IF EXISTS caso;
CREATE TABLE caso (idcaso varchar(5) not null,
descripcion varchar(200) not null,
idtipocaso varchar(2) not null,
idfallo varchar(2) not null,
PRIMARY KEY(idcaso),
FOREIGN KEY(idtipocaso) REFERENCES tipocaso(idtipocaso),
FOREIGN KEY(idfallo) REFERENCES fallo(idfallo));

DROP TABLE IF EXISTS sede;
CREATE TABLE sede (idsede varchar(2) not null,
nombre varchar(100) not null,
direccion varchar(100) not null,
PRIMARY KEY(idsede));

DROP TABLE IF EXISTS personarolcaso;
CREATE TABLE personarolcaso (idpersona varchar(10) not null,
idrol varchar(2) not null,
idcaso varchar(5) not null,
FOREIGN KEY(idpersona) REFERENCES persona(idpersona),
FOREIGN KEY(idrol) REFERENCES rol(idrol),
FOREIGN KEY(idcaso) REFERENCES caso(idcaso));

DROP TABLE IF EXISTS audiencia;
CREATE TABLE audiencia (idaudiencia varchar(2) not null,
fecha date not null,
idcaso varchar(5) not null,
idsede varchar(2) not null,
idjuez varchar(10) not null,
idabogadodefensor varchar(10) not null,
idabogadoacusador varchar(10) not null,
idfiscal varchar(10) not null,
idacusado varchar(10) not null,
PRIMARY KEY(idaudiencia),
FOREIGN KEY(idjuez) REFERENCES persona(idpersona),
FOREIGN KEY(idabogadodefensor) REFERENCES persona(idpersona),
FOREIGN KEY(idfiscal) REFERENCES persona(idpersona),
FOREIGN KEY(idacusado) REFERENCES persona(idpersona),
FOREIGN KEY(idsede) REFERENCES sede(idsede));

DROP TABLE IF EXISTS testigoaudiencia;
CREATE TABLE testigoaudiencia (idaudiencia varchar(2) not null,
idpersona varchar(10) not null,
FOREIGN KEY(idaudiencia) REFERENCES audiencia(idaudiencia),
FOREIGN KEY(idpersona) REFERENCES persona(idpersona));

INSERT INTO persona

VALUES ('71000000','SARA','GARCIA','1111111','CARRERA 1 CALLE 100','sara@garcia.com','1111111111'),

('72000000','LAURA','AGUDELO','2222222','CARRERA 2 CALLE 200','laura@agudelo.com','2222222222'),

('73000000','ANA','MONTOYA','3333333','CARRERA 3 CALLE 300','ana@montoya.com','3333333333'),

('74000000','HAROL','VALDES','4444444','CARRERA 4 CALLE 400','harol@valdes.com','4444444444'),

('75000000','SAMUEL','GARCIA','5555555','CARRERA 5 CALLE 500','samuel@garcia.com','5555555555'),

('76000000','EDWIN','ZAPATA','6666666','DESCONOCIDO','','6666666666'),

('77000000','DARIO','VALENCIA','7777777','DESCONOCIDO','','7777777777'),

('78000000','PEDRO','IDARRAGA','8888888','DESCONOCIDO','','8888888888'),

('79000000','ANIBAL','MAYA','9999999','DESCONOCIDO','','9999999999'),

('80000000','VANESA','MONSALVE','8000000','CARRERA 30 EDIFICIO 50','vanesa@medellin.co','8000000000'),

('81000000','MABEL','COLORADO','8111111','CARRERA 35 EDIFICIO 55','mabel@medellin.co','8111111111'),

('82000000','HARRY','MONSALVE','8222222','CARRERA 45 EDIFICIO 65','harry@medellin.co','8222222222'),

('83000000','TOMY','MONSALVE','8333333','CARRERA 35 EDIFICIO 55','tomy@medellin.co','8333333333'),

('84000000','LEONEL','LOAIZA','8444444','BELLAVISTA AV. 56','','8444444444');

INSERT INTO rol

VALUES ('01','JUEZ'),('02','FISCAL'),('03','ABOGADO DEFENSOR'),('04','ABOGADO ACUSADOR'),

('05','ACUSADO'),('06','TESTIGO'),('07','JURADO'),('08','SECRETARIO');

INSERT INTO tipocaso

VALUES ('01','HOMICIDIO'),('02','HURTO CALIFICADO'),('03','LESIONES PERSONALES'),

('04','VIOLACION'),('05','PREVARICATO'),('06','FALSEDAD EN DOCUMENTO PUBLICO'),

('07','EVASION DE IMPUESTOS'),('08','DEMANDA POR ALIMENTOS');

INSERT INTO fallo

VALUES ('01','EN PROCESO'),('02','FALLADO'),('03','SUSPENDIDO'),('04','EN RECESO'),

('05','CANCELADO');

INSERT INTO caso

VALUES ('C0001','ASESINATO PERSONA MAYOR EN LA CRA 50 CALLE 50 EN MEDELLIN','01','01'),

('C0002','EVASION DE IMPUESTOS POR PARTE DEL CIUDADANO REY','07','01'),

('C0003','VIOLACION DEL MENOR DE EDAD XXX POR PARTE DEL CIUDADANO X','04','02'),

('C0004','FALTA A LAS OBLIGACIONES PARENTALES POR PARTE DEL SEÑOR ABC RESPECTO DE SU HIJO','08','04'),

('C0005','ROBO A MANO ARMADA DEL VEHICULO XYZ POR PARTE DEL MENOR DE EDAD AAA','02','05'),

('C0006','LESIONES PERSONALES CON ARMA BLANCA','03','01');

INSERT INTO sede

VALUES ('S1','EDIFICIO DEL CAFE','CARRERA 46 CALLE 49'),('S2','EDFICIO FURATENA','CALLE 50 CARRERA 47'),

('S3','EDIFICIO COLTEJER','CARRERA 46 CALLE 52'),('S4','OFICINA REGIONAL MEDELLIN','CARRERA 25 CALLE 80'),

('S5','OFICINA POBLADO','CALLE 10 CARRERA 39');

INSERT INTO personarolcaso

VALUES ('71000000','01','C0002'),('72000000','02','C0002'),('71000000','03','C0001'),

('73000000','04','C0005'),('72000000','04','C0005'),('75000000','01','C0003'),

('75000000','01','C0004'),('75000000','03','C0002'),('74000000','04','C0001'),

('74000000','02','C0004'),('74000000','04','C0002'),('76000000','05','C0002'),

('71000000','03','C0003'),('72000000','04','C0003'),('73000000','02','C0003'),

('77000000','05','C0003'),('71000000','03','C0004'),('73000000','04','C0004'),

('78000000','05','C0004'),('72000000','01','C0001'),('73000000','02','C0001'),

('79000000','05','C0001'),('80000000','02','C0006'),('81000000','03','C0006'),

('82000000','06','C0006'),('83000000','01','C0006'),('84000000','05','C0006'),

('79000000','04','C0006');

INSERT INTO audiencia

VALUES ('01','2012-07-4','C0002','S5','71000000','75000000','74000000','72000000','76000000'),

('02','2012-06-15','C0003','S2','75000000','71000000','72000000','73000000','77000000'),

('03','2012-05-19','C0004','S3','75000000','71000000','73000000','74000000','78000000'),

('04','2012-04-25','C0001','S1','72000000','71000000','74000000','73000000','79000000'),

('05','2012-03-06','C0006','S4','83000000','81000000','79000000','80000000','84000000');

INSERT INTO testigoaudiencia 

VALUES ('01','71000000'),('01','75000000'),('01','74000000'),('01','72000000'),('01','76000000'),

('02','75000000'),('02','71000000'),('02','72000000'),('02','73000000'),('02','77000000'),

('03','75000000'),('03','71000000'),('03','73000000'),('03','74000000'),('03','78000000'),

('04','72000000'),('04','71000000'),('04','74000000'),('04','73000000'),('04','79000000'),

('05','83000000'),('05','81000000'),('05','79000000'),('04','80000000'),('04','84000000');

Cursor

delimiter //

create procedure clientedelmes()

begin

declare Cid_cliente varchar (20);

declare Cmes date;

declare Cnombreclient varchar (40);

declare Cvalor int (40);

declare cursorclientemes cursor for Select cliente.id_cliente, cliente.nombre_cliente, transacciones.valortransaccion,  transacciones.fechatransaccion

from cliente,transacciones 

where transacciones.id_cliente = cliente.id_cliente 

and month (fechatransaccion)= MONTH(DATE_ADD(CURDATE(),INTERVAL - 1 MONTH))

and valortransaccion >= (50000)

group by id_cliente;

open cursorclientemes;

cursorclientemes_loop: LOOP

fetch cursorclientemes into Cid_cliente, Cnombreclient, Cvalor, Cmes;

insert into clientemes values (Cid_cliente, Cnombreclient, Cvalor, Cmes);

END LOOP cursorclientemes_loop; 

close cursorclientemes;

END;

//

call clientedelmes//

Procedimiento

CREATE PROCEDURE realizarcompra (in pid_articulo varchar (20), pid_transaccion varchar (20), pid_cliente varchar (20), pfechatransaccion date, pvalor_transaccion int (40))

begin

insert into transacciones (id_cliente,id_articulo,id_transaccion,fechatransaccion,valor_transaccion) values ("3", "4", "1", "2010-10-10", "600000");

insert into transaccionxarticulo (id_articulo, id_transaccion) values ("401", "03");

end

Vistas

☻Vista1: Muestra el tipo y precio de cada articulo

create view vista1 as

select articulo.id_articulo,articulo.nombre,articulo.id_tipo,articulo.valor,tipo.descripcion from articulo,tipo

where articulo.id_tipo = tipo.id_tipo;

select * from vista1 ;

 ☻Vista2: Muestra las ventas que realiza un empleado en 3 meses

create view vista2 as

select empleado.id_empleado,empleado.nombre_emp

from empleado,transacciones

where empleado.id_empleado=transacciones.id_empleado

and fechatransaccion > (date_add(curdate(),interval -  3 month )) ;

select * from vista2 ;

☻ Consulta que utiliza las dos vistas (vista1 y vista2) y muestre que artículos ha vendido cada empleado

select vista1.id_articulo , vista1.nombre, vista2. nombre_emp, fechatransaccion  from artxtrans, transacciones, vista1, vista2

where vista1.id_articulo=artxtrans.id_articulo

and vista2.id_empleado=transacciones.id_empleado

and transacciones.id_transaccion=artxtrans.id_transaccion;

Consultas por descripción

select descripcion,count(*) from tipo

join articulo

on tipo.id_tipo = articulo.id_tipo

join artxtrans

on artxtrans.id_articulo=articulo.id_articulo

join transacciones

on transacciones.id_transaccion = artxtrans.id_transaccion

group by (descripcion);