CREATE DATABASE VENTAS
GO
USE VENTAS
GO
CREATE TABLE DISTRITOS(
CODDIST CHAR(4) PRIMARY KEY,
NOMDIST VARCHAR(40) NOT NULL)
GO
INSERT INTO DISTRITOS VALUES ('D001','LOS OLIVOS')
INSERT INTO DISTRITOS VALUES ('D002','ATE')
INSERT INTO DISTRITOS VALUES ('D003','BREÑA')
INSERT INTO DISTRITOS VALUES ('D004','COMAS')
INSERT INTO DISTRITOS VALUES ('D005','CHORRILLOS')
GO
SELECT *FROM DISTRITOS
GO
CREATE TABLE CARGOS(
CODCAR CHAR(4) PRIMARY KEY,
NOMCAR VARCHAR(40) NOT NULL)
GO
INSERT INTO CARGOS VALUES ('C001','ANALISTA')
INSERT INTO CARGOS VALUES ('C002','PROGRAMADOR')
INSERT INTO CARGOS VALUES ('C003','VENDEDOR')
INSERT INTO CARGOS VALUES ('C004','JEFE DE VENTAS')
INSERT INTO CARGOS VALUES ('C005','SECRETARIA')
GO
SELECT * FROM CARGOS
GO
CREATE TABLE MARCAS(
CODMAR CHAR(4) PRIMARY KEY,
NOMMAR VARCHAR(40) NOT NULL)
GO
INSERT INTO MARCAS VALUES ('M001','HP')
INSERT INTO MARCAS VALUES ('M002','IBM')
INSERT INTO MARCAS VALUES ('M003','SAMSUMG')
INSERT INTO MARCAS VALUES ('M004','APPLE')
INSERT INTO MARCAS VALUES ('M005','TOSHIBA')
GO
SELECT * FROM MARCAS
GO
CREATE TABLE FORMAS_PAGO(
CODFORM CHAR(4) PRIMARY KEY,
NOMFORM VARCHAR(40) NOT NULL)
GO
INSERT INTO FORMAS_PAGO VALUES ('F001','EFECTIVO')
INSERT INTO FORMAS_PAGO VALUES ('F002','TARJETA DE CREDITO')
INSERT INTO FORMAS_PAGO VALUES ('F003','TARJETA DE DEBITO')
GO
SELECT * FROM FORMAS_PAGO
GO
CREATE TABLE TIPO_COMPROBANTE(
CODTIPO CHAR(4) PRIMARY KEY,
NOMTIPO VARCHAR(40) NOT NULL)
GO
INSERT INTO TIPO_COMPROBANTE VALUES ('T001','FACTURA')
INSERT INTO TIPO_COMPROBANTE VALUES ('T002','BOLETA DE PAGO')
INSERT INTO TIPO_COMPROBANTE VALUES ('T003','TICKET')
INSERT INTO TIPO_COMPROBANTE VALUES ('T004','VOUCHER')
GO
SELECT * FROM TIPO_COMPROBANTE
GO
CREATE TABLE PERSONAL (
CODPER CHAR(4) PRIMARY KEY,
NOMPER VARCHAR(40) NOT NULL,
DNI CHAR(8) NOT NULL,
SEXO CHAR(1) NOT NULL,
ECIVIL CHAR(1) NOT NULL,
FNAC DATETIME NOT NULL,
DIREC VARCHAR(40) NOT NULL,
CODDIST CHAR(4) FOREIGN KEY REFERENCES DISTRITOS (CODDIST),
CODCAR CHAR(4) FOREIGN KEY REFERENCES CARGOS (CODCAR),
SUELDO DECIMAL(7,2) NOT NULL,
TELEF VARCHAR(9) NOT NULL,
EMAIL VARCHAR(40) NOT NULL)
GO
INSERT INTO PERSONAL VALUES ('CP01','JAVIER GUTIERREZ','03296565','M','S','05/05/1991','AV
MORALES DUAREZ 1542','D001','C001',1500,'958647523','javicho@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP02','PERCY ALMESTAR','03396565','M','C','02/02/1985','AV
AREQUIPA 4562 ','D002','C002',1300,'958647023','persy@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP03','CESAR OCEDA','03496565','M','S','06/07/1990','AV JAVIER
PRADO 1211','D003','C003',1300,'958617523','cesar@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP04','CARLA HUAMAN','03056565','F','S','10/11/1989','AV
PETIT THOUARS 1952','D004','C004',1000,'958647723','carlos@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP05','SAMUEL
VERDE','03696565','M','C','04/12/1987','ARENALES
546','D005','C005',800,'958647529','samuel@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP06','JAVIER HERRERA','03796565','M','S','03/06/1988','AV
ARICA 654','D003','C002',1000,'958647523','javi@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP07','MANUEL ANAYA','03096965','M','S','02/05/1992','AV
NACIONES 458','D002','C003',900,'958647524','manuel@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP08','RICHARD TATAJE','03076565','M','S','01/11/1983','AV
ARGENTINA 1456','D004','C001',1630,'958687523','tataje@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP09','LUISA SIFUENTES ORE','03086565','F','S','08/12/1986','AV
COLONIAL 361','D001','C004',950,'958649523','luis@hotmail.com')
INSERT INTO PERSONAL VALUES ('CP10','CARLOS GALLEGOS','09096565','M','C','09/11/1982','JR
JORGE CHAVEZ 245','D005','C005',1490,'968647523','gallegos@hotmail.com')
GO
SELECT * FROM PERSONAL
GO
CREATE TABLE CLIENTES (
CODCLI CHAR(4) PRIMARY KEY,
NOMCLI VARCHAR(40) NOT NULL,
DNI CHAR(8) NOT NULL,
RUC CHAR(11) NOT NULL,
SEXO CHAR(1) NOT NULL,
FNAC DATETIME NOT NULL,
DIREC VARCHAR(40) NOT NULL,
CODDIST CHAR(4) FOREIGN KEY REFERENCES DISTRITOS (CODDIST),
TELEF VARCHAR(9) NOT NULL,
EMAIL VARCHAR(40))
GO
INSERT INTO CLIENTES VALUES ('CL01','MARCOS
VALLE','03025210','03025210541','M','02/03/1990','AV PERÚ N°
3426','D001','965863236','marcos@hotmail.com')
INSERT INTO CLIENTES VALUES ('CL02','ALEJANDRO
VALDERRAMA','02025310','02025310784','M','12/04/1983','CALLE SANTA SUSANA
N°185','D002','988661237','alejandro@yahoo.com')
INSERT INTO CLIENTES VALUES ('CL03','CARLOS
FARFAN','03023010','03023010215','M','24/02/1980','AV. ANGAMOS N°
6548','D003','964563236','carlos@gmail.com')
INSERT INTO CLIENTES VALUES ('CL04','RAFAEL
PIEDRA','03095210','03095210684','M','17/09/1985','AV. LA MARINA
N°1453','D004','958761246','rafael@hotmail.com')
INSERT INTO CLIENTES VALUES ('CL05','RODOLFO
CORDERO','03921215','03921215341','M','07/04/1989','AV. JAVIER PRADO
N°2451','D005','986223869','rodolfo@hotmail.com')
INSERT INTO CLIENTES VALUES ('CL06','ALFREDO
HUAYTA','03025240','03025240548','M','28/07/1988','AV. CENTRAL
N°1542','D003','976546132','alfredo@gmail.com')
INSERT INTO CLIENTES VALUES ('CL07','OSWALDO
GUTIERREZ','03025430','03025430217','M','13/03/1987','AV. SANTA ANITA
N°1452','D001','963474536','oswaldo@hotmail.com')
INSERT INTO CLIENTES VALUES ('CL08','JAVIER
LLACCUA','03005210','03005210693','M','22/11/1986','AV. 13 DE
ENERO','D002','948668396','javier@yahoo.com')
INSERT INTO CLIENTES VALUES ('CL09','SANDRA
CALDERON','03525210','03525210280','F','12/05/1984','AV VILLA MARIA
N°245','D005','961310326','sandra@hotmail.com')
INSERT INTO CLIENTES VALUES ('CL10','JORGE APAZA
TORRES','03025216','03025216227','M','02/03/1983','AV ANTONIO DE SUCRE
N°1452','D004','984562836','jorge@hotmail.com')
GO
SELECT * FROM CLIENTES
GO
CREATE TABLE PROVEEDORES (
CODPROV CHAR(4) PRIMARY KEY,
NOMPROV VARCHAR(40) NOT NULL,
RUC CHAR(11) NOT NULL,
DIREC VARCHAR(40) NOT NULL,
CODDIST CHAR(4) FOREIGN KEY REFERENCES DISTRITOS (CODDIST),
TELEF VARCHAR(9) NOT NULL,
EMAIL VARCHAR(40) NOT NULL,
WEB VARCHAR(40) NOT NULL)
GO
INSERT INTO PROVEEDORES VALUES ('PV01','INFOTOUCH','01231659871','JR BELLO
HORIZONTE','D001','987654231','infotouch@hotmail.com','www.infot.com.pe')
INSERT INTO PROVEEDORES VALUES ('PV02','ALEX FERGUSO','03234653587','CALLE SEÑOR DE LOS
MILAGROS 123','D002','987604231','alex@hotmail.com','www.alex.com.pe')
INSERT INTO PROVEEDORES VALUES ('PV03','JOSE SOTO','04234659971','AV CUBA
4578','D003','987659231','soto@hotmail.com','www.soto.com.pe')
INSERT INTO PROVEEDORES VALUES ('PV04','LUIS ENCISO','05234659171','AV BENAVIDES
987','D004','987654931','enciso@hotmail.com','www.enciso.com.pe')
INSERT INTO PROVEEDORES VALUES ('PV05','BAR PRODUCTS','02234658371','AV JAVIER PRADO
ESTE N°1587','D005','985634981','barproducts@hotmail.com','www.barproducts.com.pe')
GO
SELECT *FROM PROVEEDORES
GO
CREATE TABLE PRODUCTOS(
CODPROD CHAR(4) PRIMARY KEY,
NOMPROD VARCHAR(40) NOT NULL,
CODMAR CHAR(4) FOREIGN KEY REFERENCES MARCAS (CODMAR),
PRECIO DECIMAL(7,2) NOT NULL,
STOCK SMALLINT NOT NULL,
ESTADO CHAR(1) NOT NULL)
GO
INSERT INTO PRODUCTOS VALUES ('PR01','MONITOR LCD','M001',500,30,'A')
INSERT INTO PRODUCTOS VALUES ('PR02','TECLADO','M002',150,50,'I')
INSERT INTO PRODUCTOS VALUES ('PR03','EQUIPO DE SONIDO','M003',600,30,'A')
INSERT INTO PRODUCTOS VALUES ('PR04','PARLANTES','M004',200,20,'I')
INSERT INTO PRODUCTOS VALUES ('PR05','AUDIFONOS','M005',120,40,'A')
INSERT INTO PRODUCTOS VALUES ('PR06','MONITOR LED','M005',700,30,'A')
INSERT INTO PRODUCTOS VALUES ('PR07','IMPRESORA','M003',400,20,'I')
INSERT INTO PRODUCTOS VALUES ('PR08','MICRO SD','M001',40,50,'A')
INSERT INTO PRODUCTOS VALUES ('PR09','USB','M002',20,50,'A')
INSERT INTO PRODUCTOS VALUES ('PR10','CPU','M004',1000,25,'A')
GO
SELECT * FROM PRODUCTOS
GO
CREATE TABLE ABASTECIMIENTO(
CODABAS CHAR(4) PRIMARY KEY,
CODPROV CHAR(4) FOREIGN KEY REFERENCES PROVEEDORES (CODPROV),
CODPROD CHAR(4) FOREIGN KEY REFERENCES PRODUCTOS (CODPROD))
GO
INSERT INTO ABASTECIMIENTO VALUES ('A001','PV01','PR01')
INSERT INTO ABASTECIMIENTO VALUES ('A002','PV02','PR02')
INSERT INTO ABASTECIMIENTO VALUES ('A003','PV03','PR03')
INSERT INTO ABASTECIMIENTO VALUES ('A004','PV04','PR04')
INSERT INTO ABASTECIMIENTO VALUES ('A005','PV05','PR05')
GO
SELECT * FROM ABASTECIMIENTO
GO
CREATE TABLE COMPROBANTE_CABECERA(
CODCOMP VARCHAR(10) PRIMARY KEY,
CODCLI CHAR(4) FOREIGN KEY REFERENCES CLIENTES (CODCLI),
CODPER CHAR(4) FOREIGN KEY REFERENCES PERSONAL (CODPER),
CODTIPO CHAR(4) FOREIGN KEY REFERENCES TIPO_COMPROBANTE (CODTIPO),
CODFORM CHAR(4) FOREIGN KEY REFERENCES FORMAS_PAGO (CODFORM),
FECHACOMP DATETIME NOT NULL,
ESTADO CHAR(1) NOT NULL)
GO
INSERT INTO COMPROBANTE_CABECERA VALUES ('CC01','CL01','CP01','T001','F001','12/10/11','A')
INSERT INTO COMPROBANTE_CABECERA VALUES ('CC02','CL02','CP02','T002','F003','13/08/12','A')
INSERT INTO COMPROBANTE_CABECERA VALUES ('CC03','CL03','CP03','T001','F002','08/05/09','I')
INSERT INTO COMPROBANTE_CABECERA VALUES ('CC04','CL04','CP04','T002','F001','02/11/07','I')
INSERT INTO COMPROBANTE_CABECERA VALUES ('CC05','CL05','CP05','T002','F002','16/06/10','A')
GO
SELECT * FROM COMPROBANTE_CABECERA
GO
CREATE TABLE COMPROBANTE_DETALLE(
CODCOMP VARCHAR(10) FOREIGN KEY REFERENCES COMPROBANTE_CABECERA (CODCOMP),
CODPROD CHAR(4) FOREIGN KEY REFERENCES PRODUCTOS (CODPROD),
CANTIDAD SMALLINT NOT NULL,
PRECIO DECIMAL(7,2) NOT NULL,
ESTADO CHAR(1) NOT NULL)
GO
INSERT INTO COMPROBANTE_DETALLE VALUES ('CC01','PR01',4,500,'A')
INSERT INTO COMPROBANTE_DETALLE VALUES ('CC02','PR02',2,150,'A')
INSERT INTO COMPROBANTE_DETALLE VALUES ('CC03','PR03',2,600,'I')
INSERT INTO COMPROBANTE_DETALLE VALUES ('CC04','PR04',4,20,'I')
INSERT INTO COMPROBANTE_DETALLE VALUES ('CC05','PR05',4,40,'A')
GO
SELECT * FROM COMPROBANTE_DETALLE
GO
