BASE DE DATOS-FACTURA VENTA

Ver el tema anterior Ver el tema siguiente Ir abajo

BASE DE DATOS-FACTURA VENTA

Mensaje  cristhian navia el Miér Mar 16, 2011 1:58 am

Create Database DBCFactura;
use DBCFactura;

Create table TClientes
(CodCli int not null primary key,
NomCli varchar(max),ApellCli varchar(max),DirCli varchar(max),
TelCli int,CiuCli varchar(max));

Create table TArticulo
(CodArt int not null Primary key,
NomArt varchar(max),StockArt int,
PrecArt int);


Create table TVendedor
(CodVen int not null primary key,
NomVen varchar(max),ApellVen varchar(max),DirVen varchar(max),
TelVen int,CiuVen varchar(max));

Create table Categoria
(CodArt int not null primary key,
NomArt varchar(max),MarArt varchar(max));

Create table Fact_Cabecera
(CodFact int not null primary key,
Fecha date,Total int,
CodCli int,Constraint FK_CodCli Foreign Key (CodCli) references TClientes(CodCli),
CodVen int,Constraint FK_CodVen Foreign Key (CodVen) references TVendedor(CodVen));

Create table Fact_Detalle
(CodFact int not null primary key,
Cantidad int,PrecArt int,
CodArt int,
Constraint FK_CodArt Foreign Key (CodArt) references TArticulo(CodArt));

Alter Table TClientes add FecNacCli date;

alter table TClientes drop FecNacCli;

Insert into TClientes (CodCli,NomCli,ApellCli,DirCli,TelCli,CiuCli)values
(10001,'carlos','perez','cll 10 #14-45',5536589,'cali'),
(10002,'marcela','gallego','cll 45 #78-45',6632589,'barranquilla'),
(10003,'luis','bermudes','crr 7 #71-10',4458596,'armenia'),
(10004,'pedro','carabali','cll 70 #21-78',3324578,'pereira'),
(10005,'carolina','riascos','crr 40 #85-10',7784136,'buga'),
(10006,'islena','romero','cll 56 #13-48',9965425,'tulua'),
(10007,'emilio','castaño','crr 80 #32-88',7741596,'bogota'),
(10008,'nubia','ortega','crr 39 #2-38',5532489,'cauca'),
(10009,'diana','gil','cll 40 #90-46',9952374,'cali'),
(10010,'fernando','lopez','crr 32 #45-82',6658747,'guacari');

insert into TVendedor (CodVen,NomVen,ApellVen,DirVen,TelVen,CiuVen)values
(201,'jesus','diaz','cll 1 #39-45',6651212,'cali'),
(202,'kevin','caicedo','crr 80 #77-99',5558899,'pereira'),
(203,'lilian','ortiz','cll 40 #55-66',4412265,'manizales'),
(204,'nadia','navia','crr 32 #66-55',2259988,'huila'),
(205,'james','cuatin','cll 45 #87-54',8887445,'tulua'),
(206,'libardo','ortega','cll 100 #11-56',9993256,'armenia'),
(207,'luisa','revelo','crr 59 #15-55',3332221,'cali'),
(208,'sebastian','velasco','cll 20 #77-47',2215877,'buga'),
(209,'yuly','florez','cll 2 #50-65',8854215,'manizales'),
(210,'lorena','rendon','crr 45 #99-88',4485596,'bogota');

insert into TArticulo (CodArt,NomArt,PrecArt,StockArt)values
(30001,'condon today',2500,15),
(30002,'cerveza',1500,120),
(30003,'tenis all star',120000,50),
(30004,'foami pliego',2000,45),
(30005,'cuaderno 5 materias',15000,27),
(30006,'de todito',2500,75),
(30007,'camara digital',350000,21),
(30008,'teclado',21000,16),
(30009,'morral',110000,12),
(30010,'usb 4gb',25000,32);

insert into Categoria (CodArt,NomArt,MarArt)values
(30001,'condon','today'),
(30002,'cerveza','poker'),
(30003,'tenis','all star'),
(30004,'foami pliego','norma'),
(30005,'cuaderno 5 materias','norma'),
(30006,'de todito','fritoley'),
(30007,'camara digital','sony'),
(30008,'teclado','hp'),
(30009,'morral','totto'),
(30010,'usb 4gb','kingston');

insert into Fact_Cabecera (CodFact,CodCli,CodVen,Fecha,Total)values
(4001,10001,201,'03-jun-01',302000),
(4002,10002,202,'15-may-98',15000),
(4003,10003,203,'07-ago-05',21000),
(4004,10004,204,'25-mar-86',125000),
(4005,10005,205,'27-feb-09',45000),
(4006,10006,206,'19-nov-10',15000),
(4007,10007,207,'26-jul-08',2500),
(4008,10008,208,'14-abr-06',410000),
(4009,10009,209,'20-dic-89',67000),
(4010,10010,210,'31-oct-03',89000);

insert into Fact_Detalle (CodFact,CodArt,PrecArt,Cantidad)values
(4001,30001,2500,10),
(4002,30002,1500,30),
(4003,30003,120000,2),
(4004,30004,2000,7),
(4005,30005,15000,1),
(4006,30006,2500,3),
(4007,30007,350000,1),
(4008,30008,21000,2),
(4009,30009,110000,1),
(4010,30010,25000,3);

SELECT *
FROM TArticulo
ORDER BY NomArt,PrecArt;

DELETE FROM TClientes
WHERE TelCli = 7784136;

UPDATE TVendedor
SET NomVen = 'lorena'
WHERE NomVen = 'dorency';


avatar
cristhian navia

Mensajes : 4
Fecha de inscripción : 11/02/2011

Ver perfil de usuario

Volver arriba Ir abajo

Ver el tema anterior Ver el tema siguiente Volver arriba

- Temas similares

 
Permisos de este foro:
No puedes responder a temas en este foro.