Laboratorio

CREATE TABLE clientes(

cod_cliCHAR( 3 ) NOT NULL PRIMARY KEY ,

ape_cli VARCHAR( 20 ) ,

nom_cli VARCHAR( 20 ) ,

direc_cli VARCHAR( 30 ) ,

distri_cli VARCHAR( 20 ) ,

ruc_cli INT( 12 ) ,

telef_cli INT( 9 ) ,

fn_cli DATE,

sexoCHAR( 1 )

);

CREATE TABLE CATEGORIA(

COD_CAT CHAR( 3 ) NOT NULL PRIMARY KEY ,

NOMB_CAT VARCHAR(20)

);

CREATE TABLE PRODUCTOS(

COD_PRO CHAR( 3 ) NOT NULL PRIMARY KEY ,

NOM_PRO VARCHAR( 20 ) ,

DES_PRO VARCHAR( 30 ) ,

MAR_PRO VARCHAR( 20 ) ,

PRE_PRO DOUBLE,

STOCK NUMERIC,

COD_CAT CHAR(3)

);

CREATE TABLE  VENDEDORES(

COD_VEN CHAR( 3 ) NOT NULL PRIMARY KEY ,

APE_VEN VARCHAR(20),

NOM_VEN VARCHAR(20)

);

CREATE TABLE FACTURAS(

NRO_FAC CHAR(6) NOT NULL PRIMARY KEY ,

COD_CLI CHAR(3),

FEC_FAC DATE,

STFAC DOUBLE,

IGV_FAC DOUBLE,

MPFAC DOUBLE,

COD_VEN CHAR(3)

);

CREATE TABLE DET_FACT(

NRO_FAC CHAR(6),

COD_PRO CHAR(3),

CANT INT(3),

TOTAL DOUBLE

);

*******************************************

            INSERCION DE DATOS

*******************************************

INSERT INTO clientes values

('001','CARRANZA RUIZ','RAFAEL','AV VENEZUELA 345','BREÑA','10141512123','4252623','19781012','M'),

('002','OLIVA MENDOZA','NANCY','JR JUNIN 345','PUEBLO LIBRE','10146782120','4645253','19730514','F'),

('003','LAURA BORJA','FRANK','AV TRAPICHE S/N','CARABAYLLO','10146780000','5515978','19911011','M');

INSERT INTO productos values

('001','ACEITE','BOTELLA DE 1 LITRO','PRIMOR',4.99,100,'001'),

('002','JABON','TOCADOR','PALMOLIVE',2.50,50,'005'),

('003','CHAMPU','BOTELLA','PANTENE',25,50,'006');

INSERT INTO FACTURAS values

('000001','002','20070914',10.5,1.99,12.49,'01'),

('000002','001','20071015',12.58,2.39,14.97,'02'),

('000003','003','20071121',15,2,30,'03');

INSERT INTO DET_FACT values

('000001','002',3,7.5),

('000001','001',1,4.99),

('000002','001',2,9.98);

INSERT INTO VENDEDORES values

('01','GARCIA','KAREN'),

('02','CACERES','CARLOS'),

('03','BETY','BORJA');

INSERT INTO CATEGORIA values

('001','ABARROTES'),

('002','PANADERIA'),

('003','LACTEOS'),

('00','LIMPIEZA');

*********************************************

            CONSULTAS REALIZADAS

*********************************************

a:

select *

from clientes

where substr(direc_cli,1,2)='AV';

...