ejercicio base de datos

create table FABRICANTE
(codigo number(20) not null primary key,
nombre varchar2(20) not null,
pais varchar2(20) not null,
fecha date not null);

create table VENDEDOR
(id_vendedor number(10) not null primary key,
rut_vendedor varchar2(20) not null,
drut_vendedor char(1) not null,
id_dueno number(10) not null,
nombre varchar2(20) not null,
apellido varchar2(20) not null,
telefono number(20) not null,
fecha_contrato date not null);

create table BOLETA
(nro_boleta number(10) not null primary key,
id_vendedor number(10) not null,
fecha_boleta date not null,
monto_boleta number(10),
CONSTRAINT FK_BOLETA_VENDEDOR FOREIGN KEY(id_vendedor) REFERENCES VENDEDOR(id_vendedor));

create table DETALLE_BOLETA
(id_detalle number(10) not null primary key,
nro_boleta number(10) not null,
cantidad number(10) not null,
valor_unitario number(15,5) not null,
valor_total number(15,5) not null,
CONSTRAINT FK_DETALLE_BOLETA_BOLETA FOREIGN KEY(nro_boleta) REFERENCES BOLETA(nro_boleta));

create table ESTADISTICAS
(id_estadisticas number(20) not null primary key,
nro_boleta number(10) not null,
numero_ventas number(10) not null,
monto_vendido number(10) not null,
CONSTRAINT FK_ESTADISTICAS_BOLETA FOREIGN KEY(nro_boleta) REFERENCES BOLETA(nro_boleta));

create table AUTOMOVIL
(codigo_automovil number(20) not null primary key,
modelo varchar2(15) not null,
combustible varchar2(15) not null,
valor number(20) not null,
asientos number(20) not null,
fecha_auto date not null,
codigo number(20),
CONSTRAINT FK_AUTOMOVIL_FABRICANTE FOREIGN KEY(codigo) REFERENCES FABRICANTE(codigo));

create table DUENO
(id_dueno number(10) not null primary key,
codigo_automovil number(10) not null,
nombre_dueno varchar2(50) not null,
edad number(10) not null,
telefono number(15) not null,
direccion varchar2(200) not null,
CONSTRAINT FK_DUENO_AUTOMOVIL FOREIGN KEY(codigo_automovil) REFERENCES AUTOMOVIL(codigo_automovil));



create table GARANTIA
(id_garantia number(20) not null primary key,
codigo_automovil number(10) not null,
monto_garantia number(10) not null,
tiempo_garantia number(10) not null,
CONSTRAINT FK_GARANTIA_AUTOMOVIL FOREIGN KEY(codigo_automovil) REFERENCES AUTOMOVIL(codigo_automovil));


create table REPUESTOS
(codigo_repuestos number(10) not null primary key,
nombre varchar2(40) not null,
valor number(10) not null,
descuento number(10) not null,
codigo_automovil number(20) not null,
CONSTRAINT FK_REPUESTOS_AUTOMOVIL FOREIGN KEY(codigo_automovil) REFERENCES AUTOMOVIL(codigo_automovil));


CREATE SEQUENCE SEQ_PRUEBA3
INCREMENT BY 10
START WITH 100;

INSERT INTO AUTOMOVIL(codigo_automovil)
VALUES (SEQ_PRUEBA3.NEXTVAL);

insert into FABRICANTE values(10001,'mercedez','alemania','05/05/11');
insert into FABRICANTE values(10002,'ford','EEUU','05/05/12');
insert into FABRICANTE values(10003,'ferrari','italia','05/05/13');

insert into AUTOMOVIL values(123,'AX123','gasolina98',25000000,5,'05/05/2011',10001);
insert into AUTOMOVIL values(124,'AX124','gasolina98',26000000,4,'05/05/2012',10002);
insert into AUTOMOVIL values(125,'AX125','gasolina98',27000000,5,'05/05/2013',10003);

insert into REPUESTOS values(20002,'tubo de escape',350000,5,123);
insert into REPUESTOS values(20003,'asiento',220000,7,124);
insert into REPUESTOS values(20004,'radio',150000,5,125);


insert into DUENO values(20002,123,'juanito',37,45664837,'av. etc1');
insert into DUENO values(20003,124,'pepito',38,45664838,'av. etc2');
insert into DUENO values(20004,125,'pedrito',39,45664839,'av. etc3');

insert into VENDEDOR values(51001,66666666,6,20002,'alex','perez',45654325,'05/04/08');
insert into VENDEDOR values(51002,77777777,7,20003,'alex','perez',45654326,'05/04/08');
insert into VENDEDOR values(51003,88888888,8,20004,'alex','perez',45654327,'05/04/08');

insert into BOLETA values(80000001,51001,'05/07/07',25000000);
insert into BOLETA values(80000002,51002,'05/07/08',26000000);
insert into BOLETA values(80000003,51003,'05/07/09',27000000);

insert into DETALLE_BOLETA values(123456,80000001,5,25000000,20250000);
insert into DETALLE_BOLETA values(123457,80000002,6,24000000,19440000);
insert into DETALLE_BOLETA values(123458,80000003,5,27000000,17820000);

insert into GARANTIA values (457001,123,120000,12);
insert into GARANTIA values (457002,124,100000,12);
insert into GARANTIA values (457003,125,110000,12);

insert into ESTADISTICAS values (280101,80000001,3,57510000);
insert into ESTADISTICAS values (280102,80000002,3,57510000);
insert into ESTADISTICAS values (280103,80000003,3,57510000);

SELECT  rut_vendedor||'-'||drut_vendedor ||' '|| nombre ||' '|| apellido||' '|| fecha_contrato
FROM VENDEDOR;


UPDATE BOLETA
SET   salary = salary *1.25
WHERE monto_boleta > 150000;