Ventascib

if db_id('VENTASCIB') is not null

begin

use master

drop database VENTASCIB

end

go

CREATE DATABASE VENTASCIB

GO

USE VENTASCIB

go

SET DATEFORMAT YMD

CREATE TABLE TB_DISTRITO(

COD_DIS CHAR(5)NOT NULL PRIMARY KEY,

NOM_DIS VARCHAR(50)

)

CREATE TABLE TB_VENDEDOR(

COD_VEN CHAR(3)NOT NULL PRIMARY KEY ,

NOM_VEN VARCHAR(20)NOT NULL,

APE_VEN VARCHAR(20)NOT NULL,

SUELDO_VEN MONEY NOT NULL,

FEC_ING DATE NOT NULL,

TIP_VEN VARCHAR(10)NOT NULL,

COD_DIS CHAR(5)NOT NULL REFERENCES TB_DISTRITO

)

CREATE TABLE TB_CLIENTE(

COD_CLI CHAR (5)NOT NULL PRIMARY KEY,

RAZ_SOC_CLI CHAR(30)NOT NULL,

DIR_CLI VARCHAR(100)NOT NULL,

TLF_CLI CHAR(9)NOT NULL,

RUC_CLI CHAR(11) NULL,

COD_DIS CHAR(5)NOT NULL REFERENCES TB_DISTRITO,

FEC_REG DATE NOT NULL,

TIP_CLI VARCHAR(10) NOT NULL,

CONTACTO VARCHAR(30) NOT NULL

)

CREATE TABLE TB_PROVEEDOR(

COD_PRV CHAR(5)NOT NULL PRIMARY KEY,

RAZ_SOC_PRV VARCHAR(80)NOT NULL,

DIR_PRV VARCHAR(100)NOT NULL,

TEL_PRV CHAR(15) NULL,

COD_DIS CHAR(5)NOT NULL REFERENCES TB_DISTRITO,

REP_VEN VARCHAR(80) NOT NULL

)

CREATE TABLE TB_FACTURA(

NUM_FAC VARCHAR(12)NOT NULL PRIMARY KEY,

FEC_FAC DATE NOT NULL,

COD_CLI CHAR(5)NOT NULL REFERENCES TB_CLIENTE,

FEC_CAN DATE NOT NULL,

EST_FAC VARCHAR(10)NOT NULL,

COD_VEN CHAR(3)NOT NULL REFERENCES TB_VENDEDOR,

PORC_IGV DECIMAL NOT NULL

)

CREATE TABLE TB_ORDEN_COMPRA(

NUM_OCO CHAR(5)NOT NULL PRIMARY KEY,

FEC_OCO DATE NOT NULL,

COD_PRV CHAR(5)NOT NULL REFERENCES TB_PROVEEDOR,

FEC_ATE DATE NOT NULL,

EST_OCO CHAR(1)NOT NULL

)

CREATE TABLE TB_PRODUCTO(

COD_PRO CHAR(5)NOT NULL PRIMARY KEY,

DES_PRO VARCHAR(50)NOT NULL,

PRE_PRO MONEY NOT NULL,

STK_ACT INT NOT NULL,

STK_MIN INT NOT NULL,

UNI_MED VARCHAR(30)NOT NULL,

LIN_PRO VARCHAR(30)NOT NULL,

IMPORTADO VARCHAR(10)NOT NULL

)

CREATE TABLE TB_DETALLE_FACTURA(

NUM_FAC VARCHAR(12)NOT NULL REFERENCES TB_FACTURA,

COD_PRO CHAR(5)NOT NULL REFERENCES TB_PRODUCTO,

CAN_VEN INT NOT NULL,

PRE_VEN MONEY NOT NULL

PRIMARY KEY (NUM_FAC,COD_PRO)

)

CREATE TABLE TB_DETALLE_COMPRA(

NUM_OCO CHAR(5)NOT NULL REFERENCES TB_ORDEN_COMPRA,

COD_PRO CHAR(5)NOT NULL REFERENCES TB_PRODUCTO,

CAN_PED INT NOT NULL

PRIMARY KEY (NUM_OCO,COD_PRO)

)

CREATE TABLE TB_ABASTECIMIENTO(

COD_PRV CHAR(5)NOT NULL REFERENCES TB_PROVEEDOR,

COD_PRO CHAR(5)NOT NULL REFERENCES TB_PRODUCTO,

PRE_ABA MONEY NOT NULL

PRIMARY KEY (COD_PRV,COD_PRO)

)

GO

INSERT INTO TB_DISTRITO VALUES('D01','Surco')

INSERT INTO TB_DISTRITO VALUES('D02','Jesús María')

INSERT INTO TB_DISTRITO VALUES('D03','San Isidro')

INSERT INTO TB_DISTRITO VALUES('D04','La Molina')

INSERT INTO TB_DISTRITO VALUES('D05','San Miguel')

INSERT INTO TB_DISTRITO VALUES('D06','Miraflores')

INSERT INTO TB_DISTRITO VALUES('D07','Barranco')

INSERT INTO TB_DISTRITO VALUES('D08','Chorrillos')

INSERT INTO TB_DISTRITO VALUES('D09','San Borja')

INSERT INTO TB_DISTRITO VALUES('D10','Lince')

INSERT INTO TB_DISTRITO VALUES('D11','Breña')

INSERT INTO TB_DISTRITO VALUES('D12','Magdalena')

INSERT INTO TB_DISTRITO VALUES('D13','Rimac')

INSERT INTO TB_DISTRITO VALUES('D14','Surquillo')

INSERT INTO TB_DISTRITO VALUES('D15','Pueblo Libre')

INSERT INTO TB_DISTRITO VALUES('D16','Bellavista')

INSERT INTO TB_DISTRITO VALUES('D17','Callao')

INSERT INTO TB_DISTRITO VALUES('D18','San Martin de Porres')

INSERT INTO TB_DISTRITO VALUES('D19','Santa Anita')

INSERT INTO TB_DISTRITO VALUES('D20','Los Olivos')

INSERT INTO TB_DISTRITO VALUES('D21','Independencia')

INSERT INTO TB_DISTRITO VALUES('D22','Lima - Cercado')

INSERT INTO TB_DISTRITO VALUES('D24','San Luis')

INSERT INTO TB_DISTRITO VALUES('D25','El Agustino')

INSERT INTO TB_DISTRITO VALUES('D26','San Juan de Lurigancho')

INSERT INTO TB_DISTRITO VALUES('D27','Ate - Vitarte')

INSERT INTO TB_DISTRITO VALUES('D28','San Juan de Miraflores')

INSERT INTO TB_DISTRITO VALUES('D29','Carmen de la Legua')

INSERT INTO TB_DISTRITO VALUES('D30','Comas')

INSERT INTO TB_DISTRITO VALUES('D31','Villa María del Triunfo')

INSERT INTO TB_DISTRITO VALUES('D32','Villa el Salvador')

INSERT INTO TB_DISTRITO VALUES('D33','La Perla')

INSERT INTO TB_DISTRITO VALUES('D34','Ventanilla')

INSERT INTO TB_DISTRITO VALUES('D35','Puente Piedra')

INSERT INTO TB_DISTRITO VALUES('D36','Carabayllo')

INSERT INTO TB_DISTRITO VALUES('D37','Santa Maria')

INSERT INTO TB_DISTRITO VALUES('D38','San Guchito')

INSERT INTO TB_DISTRITO VALUES('D45','La Punta')

SELECT* FROM TB_DISTRITO

go

INSERT INTO TB_VENDEDOR VALUES('V01','JUANA','ALVA',1000,'07/12/2003','1','D08')

INSERT INTO TB_VENDEDOR VALUES('V02','JUAN','SOTO',1200,'10/03/2002','2','D03')

INSERT INTO TB_VENDEDOR VALUES('V03','CARLOS','AREVALO',1500,'02/10/1992','2','D09')

INSERT INTO TB_VENDEDOR VALUES('V04','CESAR','OJEDA',850,'08/11/1999','1','D01')

INSERT INTO TB_VENDEDOR VALUES('V05','JULIO','VEGA',1500,'11/05/1997','1','D01')

INSERT INTO TB_VENDEDOR VALUES('V06','ANA','ORTEGA',1200,'11/05/1994','1','D05')

INSERT INTO TB_VENDEDOR VALUES('V07','JOSE','PALACIOS',2500,'12/02/1997','1','D02')

INSERT INTO TB_VENDEDOR VALUES('V08','RUBEN','GOMEZ',1450,'12/08/1996','2','D04')

INSERT INTO TB_VENDEDOR VALUES('V09','PATRICIA','ARCE',1800,'12/05/1997','2','D04')

INSERT INTO TB_VENDEDOR VALUES('V10','RENATO','PEREZ',1550,'11/08/1998','2','D01')

SELECT * FROM TB_VENDEDOR

GO

INSERT INTO TB_CLIENTE VALUES('C001','Finseth','Av. Los Viñedos 150','4342318','48632081','D05','12/10/2008','1','Alicia Barreto')

INSERT INTO TB_CLIENTE VALUES('C002','Orbi','Av. Emilio Cavenecia 225','4406335','57031642','D04','02/01/2013','2','Alfonso Beltran')

INSERT INTO TB_CLIENTE VALUES('C003','Serviemsa','Jr. Collagate 522','75012403',NULL,'D05','06/03/2011','2','Christian Laguna')

INSERT INTO TB_CLIENTE VALUES('C004','Issa','Calle Los Aviadores 263','3725910','46720159','D01','9/12/2012','1','Luis Apumayta')

INSERT INTO TB_CLIENTE VALUES('C005','Mass','Av. Tomas Marsano 880','4446177','83175942','D14','10/01/2013','1','Katia Armejo')

INSERT INTO TB_CLIENTE VALUES('C006','Berker','Av. Los Proceres 521','3810322','54890124','D05','7/05/2013','1','Judith Aste')

INSERT INTO TB_CLIENTE VALUES('C007','Fidenza','Jr. El Niquel 282','5289034','16204790','D20','10/02/2012','2','Hector Vivanco')

INSERT INTO TB_CLIENTE VALUES('C008','Intech','Av. San Luis 2619 5to P','2249493','34021824','D09','01/07/2011','2','Carlos Villanueva')

INSERT INTO TB_CLIENTE VALUES('C009','Prominent','Jr. Iquique 132','43233519',NULL,'D11','06/11/2010','1','Jorge Valdivia')

INSERT INTO TB_CLIENTE VALUES('C010','Landu','Av.Nicolas

...