create database panaderia;
use panaderia;
create table entrada
(
loging varchar(10) not null ,
password varchar(10) not null
);
insert into entrada values('carlos','castillo');
create table entrada2
(
loging varchar(10) not null ,
password varchar(10) not null
);
insert into entrada2 values('juraly','villalva');
create table vendedores
(
codigo int null primary key,
nombres varchar(50) not null,
apellidos varchar(50) not null,
cedula varchar(40) not null
);
create table clientesdeudores
(
codigo int not null primary key,
nombres varchar(30) not null,
apellidos varchar (20) not null,
direccion varchar(30) not null,
telefono varchar(20)
);
create table proveedoresempresas
(
codigo int not null primary key,
nombre varchar(30) not null,
direccion varchar(30) not null,
telefono varchar (30) not null,
nit varchar(30) not null
);
create table proveedoresparticulares
(
codigo int not null primary key,
nombres varchar(30) not null,
apellidos varchar (30) not null,
direccion varchar(50) not null,
telefono varchar (30) not null
);
create table productosmatprimas
(
nombre varchar (30) not null,
codigo varchar(20) not null primary key,
medida varchar (15) not null
);
create table gastosmateriasprimas
(
codigodegasto int(10) not null primary key,
fecha date not null,
cantidad varchar(20) not null,
codigo varchar (10) not null,
foreign key(codigo) references productosmatprimas( codigo )on delete cascade on update cascade
);
create table comprasdemateriasprimas
(
codigodecompra int not null primary key,
fecha date not null,
codigoproveedor varchar(10) not null
);
create table detallesdecompras
(
codigodecompra int not null,
codigodeproducto varchar (8)not null,
cantidad varchar(20) not null,
valorunitario varchar(10) not null,
valortotal varchar(10) not null,
foreign key (codigodecompra) references comprasdemateriasprimas (codigodecompra) on delete cascade on update cascade
);
create table productoselaborados
(
nombre varchar(10) not null,
codigo varchar (19)not null primary key,
valor int (20) not null
);
create table cantidaddeproductofabricado
(
codigo int not null primary key,
codigodeproducto varchar(10) not null,
fecha date not null,
cantidad int (5) not null,
foreign key (codigodeproducto) references productoselaborados (codigo) on delete cascade on update cascade
);
create table perdidas
(
codigodeperdida int not null primary key,
codigoproducto varchar(10) not null,
cantidad varchar(10) not null,
fecha date not null
);
create table ventasinmediatas
(
codigodeventa int not null primary key,
clasedepago varchar (15) not null,
fecha date not null,
hora varchar(10) not null
);
create table detallesventainmediatas
(
codigodeventa int not null primary key,
codigoproducto varchar(10) not null,
cantidad int(5) not null,
valorunitario varchar(10) not null,
valortotal varchar(20) not null,
foreign key (codigoproducto) references productoselaborados(codigo) on delete cascade on update cascade,
foreign key (codigodeventa) references ventasinmediatas(codigodeventa) on delete cascade on update cascade
);
create table ventasporpedidos
(
codigodeventa int not null primary key,
clasedepago varchar (15) not null,
fecha date not null,
descuento int(10) not null,
codigodeclientedeudor int(11) not null,
foreign key(codigodeclientedeudor) references clientesdeudores (codigo) on delete cascade on update cascade
);
create table detallesventaporpedidos
(
codigodeventa int not null primary key,
codigoproducto varchar(5) not null,
cantidad int(5) not null,
valorunitario varchar(10) not null,
valortotal varchar(10) not null,
foreign key (codigoproducto) references productoselaborados(codigo) on delete cascade on update cascade
);
create table vendedoresambulantes
(
codigo int not null primary key,
nombres varchar(30) not null,
apellidos varchar(30) not null,
cedula varchar (30) not null,
telefono varchar (30)not null
);
create table ventasambulantes
(
codigodeventa int not null primary key,
clasedepago varchar (15) not null,
fecha date not null,
valordepagoavendedor int(15) not null,
codigovendedor int (11) not null,
codigoclientedeudor int (11) not null,
foreign key(codigovendedor) references vendedoresambulantes(codigo) on delete cascade on update cascade,
foreign key(codigoclientedeudor) references clientesdeudores(codigo) on delete cascade on update cascade
);
create table detalleventaambulantes
(
codigoventa int not null,
codigoprod varchar(19) not null,
cantidad varchar (10) not null,
valorunitario varchar (10) not null,
valortotal varchar(10) not null,
foreign key(codigoventa) references ventasambulantes(codigodeventa) on delete cascade on update cascade,
foreign key(codigoprod) references productoselaborados(codigo) on delete cascade on update cascade
);
create table ingresos
(
codigo int not null primary key,
totalventas int(20) not null,
fecha date not null
);
create table utilidades
(
CODIGO int not null primary key,
valor int (20) not null,
fecha date not null
);
create table representaciones
(
codigo int not null primary key,
nombre varchar (20 ) not null,
valor int(15) not null
);
create table egresos
(
codigo int not null primary key,
valortotal int(20) not null,
fecha date not null
);
create table devoluciones
(
codigo int (11) not null primary key,
fecha date not null,
cantidad int (5) not null,
codigoprod varchar(5) not null,
codigovendedor INT (11) not null,
foreign key (codigoprod) references productoselaborados(codigo) on delete cascade on update cascade,
foreign key (codigovendedor) references vendedoresambulantes(codigo) on delete cascade on update cascade
);
create table pagos
(
codigo int not null primary key,
fecha date not null,
valorpago int(15) not null,
saldo int(15) not null,
codigodeclientedeudor INT (11) not null,
foreign key (codigodeclientedeudor) references clientesdeudores(codigo) on delete cascade on update cascade
);
create table administradores
(
nombres varchar (30) not null,
apellidos varchar(30) not null,
cedula varchar (30) not null primary key,
profesion varchar(30) not null
);
insert into clientesdeudores values('001','antonio andres','perez barboza','barrio ginea','2895123');
insert into clientesdeudores values('002','juan antonio','lascajo ruiz','barrio el bosque','2895121');
insert into clientesdeudores values('003','carcos julio','peralta barboza','barrio el trebol','2895123');
insert into clientesdeudores values('004','juana antonia','mercado mendoza','barrio la esmeralda','2897123');
insert into clientesdeudores values('005','edilma rafaela','guzman ruiz','la avenida','2893623');
insert into clientesdeudores values('006','pedro julio','acosta ulloa','barrio san pedro','2898773');
insert into clientesdeudores values('007','josefina del carmen','palencia atencia','barrio chupundun','2895100');
insert into clientesdeudores values('008','antonela alsix','ortegon de la ossa','barrio las malvinas','2895128');
insert into clientesdeudores values('009','luisa antonia','piñerez arrieta','barrio san juan','2995123');
insert into clientesdeudores values('010','alfonso abel','avila ortega','barrio la esmeralda','2898285');
insert into clientesdeudores values('011','luisa margarita','pineda arrieta','barrio villa coral','2195123');
insert into clientesdeudores values('012','hugo andres','aguas barreto','barrio la transformacion','2235123');
insert into clientesdeudores values('013','jose oscar','perez barsena','barrio ginea','2895987');
insert into clientesdeudores values('014','antonela ilse','iriarte barboza','barrio las margaritas','2895765');
insert into clientesdeudores values('015','andres alfonso','ruiz avila','barrio las brisas','3395123');
insert into clientesdeudores values('017','afrodicio manuel','mercado castro','la avenida','2835123');
insert into proveedoresempresas values('001','el costello','cra 21 N0 123','2895123','2222');
insert into proveedoresempresas values('002','harina toll','cra 32 N0 12-23','1234123','3333');
insert into proveedoresempresas values('003','la bodegita','cra 27 N0 11-43','2895145','4444');
insert into proveedoresempresas values('004','los paisas','cra 24 N0 13-5','2895657','5555');
insert into proveedoresempresas values('005','el bodegon','cra 29 N0 223','2800123','6666');
insert into proveedoresempresas values('006','harinas colombia','clle 21 N0 43-56','7766123','7777');
insert into proveedoresempresas values('007','rinacolom','cra 311 N0 320','6765123','8888');
insert into proveedoresempresas values('008','azucarar rio','cra 87 N0 153','6766123','9999');
insert into proveedoresempresas values('010','margarinas since','cra 6 N0 67-10','2895993','1010');
insert into proveedoresempresas values('011','esencias la hunica','cra 31 N0 156','9895453','1011');
insert into proveedoresempresas values('012','azucarhimg','cra 76 N0 129','2890023','1012');
insert into proveedoresempresas values('013','margarynas21','cra 37 N0 76-7','2645123','1013');
insert into proveedoresempresas values('014','abastosharinal','cra 76 N0 433','2675123','1014');
insert into proveedoresempresas values('015','harinas la economa','cra 291 N0 13','7667123','1015');
insert into proveedoresempresas values('016','plasticoscolm','cra 71 N0 16-31','8895123','1016');
insert into proveedoresempresas values('017','harinitas la sonrisa','cra 43 N0 13-45','7895123','1017');
insert into proveedoresempresas values('018','azucar text','cra 41 N0 13-65','6555123','1018');
insert into proveedoresempresas values('019','el haritoccidente','cra 31 N0 19-65','2899123','1019');
insert into proveedoresempresas values('020','la poisan','cra 55 N0 112','2895009','1020');
insert into proveedoresparticulares values('001','nora andrea','luna primera','cra 22 N0 12-234','2345674');
insert into proveedoresparticulares values('003','mary luz','avila ortega','cra 32 N0 22-44','2332123');
insert into proveedoresparticulares values('002',' luz mila',' ulloa palencia','cra 32 N0 22-44','9932123');
insert into proveedoresparticulares values('004','margarita lia','avila ulloa','cra 33 N0 23-44','2332123');
insert into proveedoresparticulares values('005','maria eugenia','avila arrieta','cra 31 N0 21-44','7862123');
insert into proveedoresparticulares values('006','carmen sofia','dominguez arrieta','cra 44 N0 99-44','8832123');
insert into proveedoresparticulares values('007','danessa lia','avila pineda','cra 65 N0 99-44','7652123');
insert into proveedoresparticulares values('008','norma estela','alvares prieto','cra 44 N0 82-41','6632123');
insert into proveedoresparticulares values('009','juana antonia','arrieta lara','cra 65 N0 82-04','7632123');
insert into proveedoresparticulares values('010','hugo jose','atencia perez','cra 23 N0 62-94','6673123');
insert into proveedoresparticulares values('011','marcos jose','acosta acosta','cra 71 N0 87-01','7762123');
insert into proveedoresparticulares values('012','oscar daniel','pineda arrieta','cra 32 N0 55-44','8832123');
insert into proveedoresparticulares values('013','juan migel','lara zola','cra 39 N0 22-44','2992123');
insert into productosmatprimas values('harina','m001','bultos');
insert into productosmatprimas values('azucar','m002','bultos');
insert into productosmatprimas values('margarina','m003','cajas');
insert into productosmatprimas values ('levadura','m004','kg');
insert into productosmatprimas values('polvo de hornear','m005','kg');
insert into productosmatprimas values('esencias','m006','lts');
insert into productosmatprimas values('sal','m007','kg');
insert into productosmatprimas values('bocadillo','m008','kg');
insert into productosmatprimas values('arequipe','m009','kg');
insert into productosmatprimas values('queso','m010','libras');
insert into productosmatprimas values('huevos','m011','unidad');
insert into productosmatprimas values('frutas artificiales','m012','kg');
insert into gastosmateriasprimas values('0001','07-02-15','80kilos','m001');
insert into gastosmateriasprimas values('0002','07-02-15','40kilos','m002');
insert into gastosmateriasprimas values('0003','07-02-15','50gk','m003');
insert into gastosmateriasprimas values('0004','07-02-15','40kg','m004');
insert into gastosmateriasprimas values('0005','08-03-21','80kilos','m001');
insert into gastosmateriasprimas values('0006','08-03-21','40kilos','m002');
insert into gastosmateriasprimas values('0007','08-03-21','30kg','m003');
insert into gastosmateriasprimas values('0008','09-04-11','160kilos','m001');
insert into gastosmateriasprimas values('0009','09-04-11','80kilos','m002');
insert into gastosmateriasprimas values('0010','09-04-11','70kg','m003');
insert into gastosmateriasprimas values('0011','09-04-11','86kg','m004');
insert into gastosmateriasprimas values('0012','09-04-11','80kg','m005');
insert into gastosmateriasprimas values('0013','09-04-11','30lts','m006');
insert into comprasdemateriasprimas values ('0001','09-05-17','001');
insert into comprasdemateriasprimas values ('0002','09-05-17','002');
insert into comprasdemateriasprimas values ('0003','09-05-17','003');
insert into comprasdemateriasprimas values ('0004','09-05-17','004');
insert into comprasdemateriasprimas values ('0005','09-05-17','005');
insert into comprasdemateriasprimas values ('0006','09-05-17','006');
insert into comprasdemateriasprimas values ('0007','09-05-17','007');
insert into comprasdemateriasprimas values ('0008','09-05-17','008');
insert into comprasdemateriasprimas values ('0009','09-05-17','009');
insert into detallesdecompras values('0001','m001','10','1000','10000');
insert into detallesdecompras values('0001','m002','8','1000','8000');
insert into detallesdecompras values('0001','m00'3,'9','9000','9000');
insert into detallesdecompras values('0001','m004','10','1000','10000');
insert into detallesdecompras values('0001','m005','10','1000','10000');
insert into productoselaborados values('croazan','001-x',500);
insert into productoselaborados values('croazan','001-y',1000);
insert into productoselaborados values('panochita','002-x',500);
insert into productoselaborados values('panochita','002-y',1000);
insert into productoselaborados values('pan de queso','003-x',500);
insert into productoselaborados values('pan de queso','003-y',1000);
insert into productoselaborados values('rosquita','004-x',500);
insert into productoselaborados values('rosquita','004-y',1000);
insert into productoselaborados values('lengua','005-x',500);
insert into productoselaborados values('lengua','005-y',1000);
insert into productoselaborados values('pan de 500','w',500);
insert into productoselaborados values('pan de sal','007-y',1000);
insert into productoselaborados values('galleta','008-y',1000);
insert into productoselaborados values('galleta','008-z',2000);
insert into cantidaddeproductofabricado values('002','001-x','09-08-08','20');
insert into cantidaddeproductofabricado values('003','001-y','09-08-01','80');
insert into cantidaddeproductofabricado values('004','004-x','09-08-02','400');
insert into cantidaddeproductofabricado values('005','008-y','09-08-03','200');
insert into cantidaddeproductofabricado values('006','005-x','09-08-04','100');
insert into cantidaddeproductofabricado values('008','w','09-08-06','60');
insert into ventasinmediatas values('01','acredito','09-03-03','4:00');
insert into ventasinmediatas values('02','contado','08-08-08','2:00');
insert into ventasinmediatas values('03','contado','08-02-07','6:00');
insert into ventasinmediatas values('04','contado','07-02-07','4:00');
insert into ventasinmediatas values('05','contado','06-02-07','3:00');
insert into ventasinmediatas values('06','contado','05-9-02-07','5:00');
insert into ventasinmediatas values('07','contado','03-02-07','3:00');
insert into ventasinmediatas values('08','contado','02-02-07','2:00');
insert into ventasinmediatas values('09','contado','05-02-07','1:00');
insert into ventasinmediatas values('010','contado','04-02-07','1:00');
insert into detallesventainmediatas values ('01','001-X','45','1000','45000');
insert into detallesventainmediatas values ('02','001-X','34','1000','34000');
insert into detallesventainmediatas values ('03','001-X','40','1000','40000');
insert into detallesventainmediatas values ('04','001-X','60','1000','60000');
insert into detallesventainmediatas values ('05','001-X','70','1000','70000');
insert into detallesventainmediatas values ('06','001-X','40','1000','40000');
insert into detallesventainmediatas values ('07','001-X','30','1000','30000');
insert into detallesventainmediatas values ('08','001-X','60','1000','60000');
insert into ventasporpedidos values('1','Efectivo','09-08-04','30','01');
insert into ventasporpedidos values('2','Efectivo','09-08-04','40','02');
insert into ventasporpedidos values('3','Efectivo','09-08-04','50','03');
insert into ventasporpedidos values('4','Efectivo','09-08-04','70','04');
insert into ventasporpedidos values('5','Efectivo','09-08-04','10','05');
insert into ventasporpedidos values('6','Efectivo','09-08-04','30','06');
insert into detallesventaporpedidos values( '11','001-x','8','1000','8000');
insert into detallesventaporpedidos values( '12','001-x','56','1000','56000');
insert into detallesventaporpedidos values( '13','001-x','56','1000','56000');
insert into detallesventaporpedidos values( '14','001-x','6','1000','6000');
insert into detallesventaporpedidos values( '14','001-x','45','1000','45000');
insert into detallesventaporpedidos values( '16','001-x','4','1000','4000');
insert into detallesventaporpedidos values( '17','001-x','89','1000','89000');
insert into detallesventaporpedidos values( '18','001-x','80','1000','80000');
insert into vendedoresambulantes values ('01','claudia marcela','castillo de la ossa','98000','324566');
insert into vendedoresambulantes values ('02','maria eugenia','avila palencia','9801','325566');
insert into vendedoresambulantes values ('03','jorge arturo','iriarte tovar','98002','326566');
insert into vendedoresambulantes values ('04','javier manuel','castillo toloza','98003','327L566');
insert into vendedoresambulantes values ('05','patricia isabel','pineda pineda','98004','328566');
insert into vendedoresambulantes values ('06','lina maria','alviz salgado','98005','329566');
insert into vendedoresambulantes values ('07','jhon jairo','cataño martinez','98006','321566');
insert into vendedoresambulantes values ('08','luis miguel','de la ossa garrido','98007','323566');
insert into vendedoresambulantes values ('09','paulino jose','arrieta perez','98008','322566');
insert into vendedoresambulantes values ('010','hector manuel','meza ucros','98009','311566');
insert into ventasambulantes values('21','acredito','09-05-08',10000, '08','001');
insert into ventasambulantes values('22','acredito','09-05-08',10000, '02','002');
insert into ventasambulantes values('23','acredito','09-05-08',10000, '01','002');
insert into ventasambulantes values('24','acredito','09-05-08',10000, '04','003');
insert into ventasambulantes values('25','acredito','09-05-08',10000, '02','002');
insert into ventasambulantes values('26','acredito','09-05-08',10000, '02','004');
insert into ventasambulantes values('27','acredito','09-05-08',10000, '06','004');
insert into detalleventaambulantes values('21','001-x',3,'1000','3000');
insert into detalleventaambulantes values('22','001-x','56','1000','56000');
insert into detalleventaambulantes values('23','001-x','70','1000','70000');
insert into detalleventaambulantes values('24','001-x','30','1000','30000');
insert into detalleventaambulantes values('25','001-x','39','1000','39000');
insert into detalleventaambulantes values('26','001-x','3','1000','3000');
insert into detalleventaambulantes values('27','001-x','31','1000','31000');
insert into ingresos values('55',30000,'09-01-02');
insert into ingresos values('56',56000,'09-01-02');
insert into ingresos values('57',57000,'09-02-03');
insert into ingresos values('58',90000,'09-03-04');
insert into ingresos values('59',130000,'09-04-05');
insert into ingresos values('60',125000,'09-05-06');
insert into ingresos values('61',135000,'09-06-07');
insert into utilidades values( '001',50000,'09-02-02');
insert into utilidades values('002',10000,'09-03-03');
insert into utilidades values('003',10000,'09-04-04');
insert into utilidades values('004',100000,'09-05-05');
insert into utilidades values('005',95000,'09-06-06');
insert into utilidades values('006',95000,'09-07-07');
insert into devoluciones values('01','09-01-01',8,'001-x','01');
insert into devoluciones values('02','09-01-01',8,'001-y','02');
insert into devoluciones values('03','09-01-02',7,'002-x','03');
insert into devoluciones values('04','09-01-03',6,'001-x','04');
insert into devoluciones values('05','09-01-04',4,'001-x','05');
insert into representaciones values('01','luz',30000);
insert into representaciones values('02','gaz',40000);
insert into representaciones values('03','agua',30000);
insert into representaciones values('04','pago aemple',20000);
insert into egresos values('001',30000,'09-01-02');
insert into egresos values('002',40000,'09-02-03');
insert into egresos values('003',30000,'09-03-04');
insert into egresos values('004',20000,'09-03-05');
insert into egresos values('005',30000,'09-04-06');
insert into egresos values('006',30000,'09-05-06');
insert into egresos values('007',40000,'09-06-06');
insert into administradores values ('juan manuel','sampayo pineda','99999','administrador');
insert into administradores values ('antonio eduardo','pineda pineda','99998','medico');
insert into administradores values ('ana maria','rosales tirado','99996','enfermero');
insert into administradores values ('hector jose','meza osorio','99995','profesor');
insert into administradores values ('antonio eduardo','figueroa ruis','99994','ingeniero');
insert into administradores values ('maria eugenia','meza meza','99993','ingeniero');
insert into administradores values ('juan jose','castiilo ruis','99992','bilologo');
insert into administradores values ('edinson manuel','gamboa tullo','99991','contador');
insert into administradores values ('lius manuel','samper pizano','99990','contador');
insert into administradores values ('gerson fabio','uribe velez','99997','administrador');
insert into vendedores values ('001','hugo manuel','figueroa pineda','23555');
insert into vendedores values ('003','luis jose','osorio romero','23557');
insert into vendedores values ('004''anderson jose','lastre ruis','23558');
insert into vendedores values ('005','ana maria','eraso ruis','23559');
insert into vendedores values ('006','erika maria','lopez meza','23551');
insert into vendedores values ('008','karla maria','gonzales pacheco','23553');
insert into vendedores values ('009','raul jose','meza ucros','23554');
insert into vendedores values ('007','elkin panocha','ulloa meza','23560');
insert into perdidas values('001','001-x','34','09-05-16');
insert into perdidas values('002','001-x','34','09-05-16');
insert into perdidas values('003','m001','34','09-05-16');
insert into perdidas values('004','m001','34','09-05-16');
insert into perdidas values('005','m001','34','09-05-16');
insert into perdidas values('007','m001','34','09-05-16');
insert into perdidas values('008','m001','34','09-05-16');
insert into perdidas values('009','m001','34','09-05-16');
insert into pagos values('001','09-05-16',40000,10000,'001');
insert into pagos values('002','09-05-17',50000,30000,'002');
insert into pagos values('003','09-05-18',40000,20000,'003');
insert into pagos values('004','09-05-19',80000,40000,'004');
insert into pagos values('005','09-05-15',40000,10000,'005');
insert into pagos values('006','09-05-12',70000,30000,'006');
insert into pagos values('007','09-05-13',80000,50000,'007');
insert into pagos values('008','09-05-15',40000,20000,'008');
insert into pagos values('009','09-05-15',90000,40000,'009');
No hay comentarios:
Publicar un comentario