Caso Práctico Resuelto Ing. José Vicente Ch. J.
BASE DE DATOS DE SUPER AUTOS JACK CREATE TABLE motor ( id_motor NUMBER(5) PRIMARY KEY, motor VARCHAR(15) ); INSERT INTO motor VALUES (1,'HEMI'); INSERT INTO motor VALUES (2,'v10'); INSERT INTO motor VALUES (3,'V8'); INSERT INTO motor VALUES (4,'vOLKSWAGEN');
CREATE TABLE marca ( id_marca NUMBER(5) PRIMARY KEY, marca VARCHAR(15) ); INSERT INTO marca VALUES (1,'Dodge'); INSERT INTO marca VALUES (2,'volkswagen'); INSERT INTO marca VALUES (3,'Audi'); INSERT INTO marca VALUES (4,'Ford');
CREATE TABLE color ( id_color NUMBER(5) PRIMARY KEY, color VARCHAR(15) );
Caso Práctico Resuelto Ing. José Vicente Ch. J.
INSERT INTO color VALUES (1,'ROJO'); INSERT INTO color VALUES (2,'NEGRO'); INSERT INTO color VALUES (3,'AZUL'); INSERT INTO color VALUES (4,'GRIS');
CREATE TABLE tipo ( id_tipo NUMBER(5) PRIMARY KEY, tipo VARCHAR(15) ); INSERT INTO tipo VALUES (1,'Autobus'); INSERT INTO tipo VALUES (2,'Deportivo'); INSERT INTO tipo VALUES (3,'Sedan'); INSERT INTO tipo VALUES (4,'Pick-up');
CREATE TABLE modelo ( id_modelo NUMBER(5) PRIMARY KEY, modelo VARCHAR(15) ); INSERT INTO modelo VALUES (1,'2010'); INSERT INTO modelo VALUES (2,'2011'); INSERT INTO modelo VALUES (3,'2012'); INSERT INTO modelo VALUES (4,'2013');
Caso Práctico Resuelto Ing. José Vicente Ch. J.
CREATE TABLE vehiculo ( id_vehiculo NUMBER(6) PRIMARY KEY, vehiculo VARCHAR(12), id_motor NUMBER(3), id_marca NUMBER(3), id_color NUMBER(3), id_tipo NUMBER(3), id_modelo NUMBER(3), CONSTRAINT FK01 FOREIGN KEY (id_motor) REFERENCES motor (id_motor), CONSTRAINT FK02 FOREIGN KEY (id_marca) REFERENCES marca (id_marca), CONSTRAINT FK03 FOREIGN KEY (id_color) REFERENCES color (id_color), CONSTRAINT FK04 FOREIGN KEY (id_tipo) REFERENCES tipo (id_tipo), CONSTRAINT FK05 FOREIGN KEY (id_modelo) REFERENCES modelo (id_modelo) ); INSERT INTO vehiculo VALUES (111,'RAM',1,1,1,4,1); INSERT INTO vehiculo VALUES (112,'RAM',1,1,2,4,1); INSERT INTO vehiculo VALUES (113,'Bus',4,2,4,1,3); INSERT INTO vehiculo VALUES (114,'Focus',3,4,2,3,2); INSERT INTO vehiculo VALUES (115,'Focus',3,4,4,3,2); INSERT INTO vehiculo VALUES (116,'A3',1,3,3,3,2); INSERT INTO vehiculo VALUES (117,'A3',1,3,1,3,2); INSERT INTO vehiculo VALUES (118,'R8',2,3,4,2,4); INSERT INTO vehiculo VALUES (119,'Charger',1,1,3,2,3); INSERT INTO vehiculo VALUES (120,'Charger',1,1,1,2,3); INSERT INTO vehiculo VALUES (121,'Charger',1,1,2,2,3); INSERT INTO vehiculo VALUES (122,'Charger',1,1,4,2,3);
Caso Práctico Resuelto Ing. José Vicente Ch. J.
CREATE TABLE vendedor ( id_vendedor NUMBER(6) PRIMARY KEY, vendedor VARCHAR(18) ); INSERT INTO vendedor VALUES (1,'Daniel Rivera'); INSERT INTO vendedor VALUES (2,'Edgar Echeverria'); INSERT INTO vendedor VALUES (3,'Gustavo López'); INSERT INTO vendedor VALUES (4,'Rodrigo García');
CREATE TABLE venta ( id_venta NUMBER(6) PRIMARY KEY, id_vehiculo NUMBER(6), n_chasis NUMBER(10), id_marca NUMBER(3), id_color NUMBER(3),
Caso Práctico Resuelto Ing. José Vicente Ch. J.
precio FLOAT(9), fecha DATE, id_vendedor NUMBER(6), CONSTRAINT FK06 FOREIGN KEY (id_vehiculo) REFERENCES vehiculo (id_vehiculo), CONSTRAINT FK07 FOREIGN KEY (id_vendedor) REFERENCES vendedor (id_vendedor), CONSTRAINT FK08 FOREIGN KEY (id_marca) REFERENCES marca (id_marca), CONSTRAINT FK09 FOREIGN KEY (id_color) REFERENCES color (id_color) ); INSERT INTO venta (501,121,2023898458,1,1,320000.50,TO_DATE('09/01/2012','dd/mm/yyyy'),1); INSERT INTO venta (502,119,4140229008,1,3,325000.50,TO_DATE('20/01/2012','dd/mm/yyyy'),1); INSERT INTO venta (503,111,1234567890,1,1,220000.55,TO_DATE('30/01/2012','dd/mm/yyyy'),3); INSERT INTO venta (504,114,9876543210,4,2,113000.75,TO_DATE('02/02/2012','dd/mm/yyyy'),2); INSERT INTO venta (505,116,4190189565,3,3,258000.25,TO_DATE('13/02/2012','dd/mm/yyyy'),2); INSERT INTO venta (506,117,4622995000,3,1,250000.25,TO_DATE('23/02/2012','dd/mm/yyyy'),4); INSERT INTO venta (507,112,2382242565,1,2,220000.75,TO_DATE('23/02/2012','dd/mm/yyyy'),1); INSERT INTO venta (508,122,5789730110,1,4,320000.50,TO_DATE('02/03/2012','dd/mm/yyyy'),4); INSERT INTO venta (509,120,1012627700,1,1,320000.50,TO_DATE('12/03/2012','dd/mm/yyyy'),3); INSERT INTO venta (510,118,4693094978,1,4,850000.99,TO_DATE('28/03/2012','dd/mm/yyyy'),3);
CREATE TABLE inventario (
VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES
Caso Práctico Resuelto Ing. José Vicente Ch. J.
id_inventario NUMBER(6) PRIMARY KEY, id_vehiculo NUMBER(6), id_tipo NUMBER(5), existencias NUMBER(5), precio_unitario FLOAT(10), Total FLOAT(12), CONSTRAINT FK10 FOREIGN KEY (id_vehiculo) REFERENCES vehiculo (id_vehiculo), CONSTRAINT FK11 FOREIGN KEY (id_tipo) REFERENCES tipo (id_tipo) ); INSERT INTO inventario VALUES (1000,111,4,6,220000.55,1320003.30); INSERT INTO inventario VALUES (1001,112,4,5,220000.55,2200005.50); INSERT INTO inventario VALUES (1002,113,1,3,485000.00,1455000.00); INSERT INTO inventario VALUES (1003,114,3,8,113000.75,904006.00); INSERT INTO inventario VALUES (1004,115,3,5,113000.75,565003.75); INSERT INTO inventario VALUES (1005,116,3,2,258000.25,516000.50); INSERT INTO inventario VALUES (1006,117,3,3,258000.25,774000.75); INSERT INTO inventario VALUES (1007,118,2,1,850000.99,850000.99); INSERT INTO inventario VALUES (1008,119,2,5,325000.50,1625002.50); INSERT INTO inventario VALUES (1009,120,2,3,320000.50,960001.50); INSERT INTO inventario VALUES (1010,121,2,4,320000.50,1280002.00); INSERT INTO inventario VALUES (1011,122,2,2,320000.50,640001.00);
INFORME DE VENTAS POR COLOR SELECT color.id_color, color, id_venta FROM color.id_color=venta.id_color AND color.color='ROJO';
venta,
color
WHERE
Caso Práctico Resuelto Ing. José Vicente Ch. J.
SELECT color.id_color, color, id_venta FROM color.id_color=venta.id_color AND color.color='NEGRO';
venta,
color
WHERE
SELECT color.id_color, color, id_venta FROM color.id_color=venta.id_color AND color.color='AZUL';
venta,
color
WHERE
SELECT color.id_color, color, id_venta FROM color.id_color=venta.id_color AND color.color='GRIS';
venta,
color
WHERE
VOLUMEN DE VENTAS LA MARCA MÁS VENDIDA SELECT marca, COUNT(marca) Volumen_de_ventas FROM marca, vehiculo, venta WHERE vehiculo.id_vehiculo=venta.id_vehiculo AND venta.id_marca=marca.id_marca group by marca order by count(marca) desc;
Caso Práctico Resuelto Ing. José Vicente Ch. J.
LA MARCA MENOS VENDIDA SELECT marca, COUNT(marca) Volumen_de_ventas FROM marca, vehiculo, venta WHERE vehiculo.id_vehiculo=venta.id_vehiculo AND venta.id_marca=marca.id_marca group by marca order by count(marca) asc;
INFORME MENSUAL SELECT avg(sum(precio)) FROM vehiculo, venta WHERE (Fecha BETWEEN '01/01/2012' AND '31/01/2012') AND venta.id_vehiculo=vehiculo.id_vehiculo group by id_venta;
SELECT avg(sum(precio)) FROM vehiculo, venta WHERE (Fecha BETWEEN '01/02/2012' AND '29/02/2012') AND venta.id_vehiculo=vehiculo.id_vehiculo group by id_venta;
SELECT avg(sum(precio)) FROM vehiculo, venta WHERE (Fecha BETWEEN '01/03/2012' AND '31/03/2012') AND venta.id_vehiculo=vehiculo.id_vehiculo group by id_venta;
Caso Práctico Resuelto Ing. José Vicente Ch. J.
VOLUMEN DE VENTAS POR VENDEDOR DESCENDENTE SELECT vendedor, COUNT(vendedor) MAXIMO FROM venta, vehiculo, vendedor WHERE vehiculo.id_vehiculo=venta.id_vehiculo AND vendedor.id_vendedor=venta.id_vendedor group by vendedor order by count(vendedor) desc;
ASCENDENTE SELECT vendedor, COUNT(vendedor) MAXIMO FROM venta, vehiculo, vendedor WHERE vehiculo.id_vehiculo=venta.id_vehiculo AND vendedor.id_vendedor=venta.id_vendedor group by vendedor order by count(vendedor) asc;
INFORME DE MARCA Y COLOR MENOS VENDIDOS SELECT color, marca, COUNT(marca) MAXIMO FROM venta, vehiculo, marca, color WHERE vehiculo.id_vehiculo=venta.id_vehiculo AND marca.id_marca=vehiculo.id_marca AND color.id_color=vehiculo.id_color group by marca, color order by count(marca) asc;
Caso Práctico Resuelto Ing. José Vicente Ch. J.
ALTERAR LA TABLA VEHICULOS (AGREGAR CILINDRAJE) alter table vehiculo add cilindraje NUMBER(2);
ACTUALIZAR LA COLUMNA CILINDRAJE
UPDATE vehiculo SET id_motor=1, cilindraje=4 WHERE vehiculo='RAM'; UPDATE vehiculo SET id_motor=4, cilindraje=8 WHERE vehiculo='Bus'; UPDATE vehiculo SET id_motor=3, cilindraje=4 WHERE vehiculo='Focus'; UPDATE vehiculo SET id_motor=1, cilindraje=6 WHERE vehiculo='A3'; UPDATE vehiculo SET id_motor=2, cilindraje=8 WHERE vehiculo='R8';
Caso Práctico Resuelto Ing. José Vicente Ch. J.
UPDATE vehiculo SET id_motor=1, cilindraje=6 WHERE vehiculo='Charger';