- PEA2

Base de datos de ventas

Para los siguientes ejercicios utilizaremos una propia base de datos de ventas, debido a que no
contamos con los permisos de alterar la información de los esquemas de base de datos que
nos proporciona Oracle Live SQL.
1. Copia las siguientes sentencias SQL y pégalo en la ventana de trabajo de la herramienta
para finalmente ejecutar las sentencias.
-- Ventas Database SQL
1
-- author: Definir su datos
2 -- Servidor: Oracle Database 19c Enterprise Edition Release
3 -- Versión del servidor: 19.4.0.0.0
4
5 -- CREANDO TABLA CLIENTES

6
CREATE TABLE clientes (
7
cod_cliente CHAR(5) NOT NULL PRIMARY KEY,
8
nombres VARCHAR2(30) NOT NULL,
9 distrito VARCHAR2(30) NULL,
10 telefono VARCHAR2(10) NULL

11 );

12
-- CREANDO TABLA PRODUCTOS
13
14
CREATE TABLE productos (
15 cod_producto CHAR(5) NOT NULL PRIMARY KEY,
16 descripcion VARCHAR2(30) NULL,
17 precio_unitario NUMBER(9,2) NULL
);
18
19
-- CREANDO TABLA FACTURAS
20
21
CREATE TABLE facturas (
22 cod_factura CHAR(5) NOT NULL PRIMARY KEY,
23 cod_cliente CHAR(5) NOT NULL,

24 fecha_emision DATE NULL,

importe_total DECIMAL(9,2) NULL
25
);
26
27
-- CREANDO TABLA DETALLE_FACTURAS

IT América L. Sabalú Santillan 1

Instructora - Docentes
MOS & MTA Microsoft - CISCO
asabalu@isise.edu.pe
993.921.087
 - PEA2
28
29 CREATE TABLE detalle_facturas (
cod_factura CHAR(5) NOT NULL,
30
cod_producto CHAR(5) NOT NULL,
31
cantidad SMALLINT NOT NULL,
32 subtotal DECIMAL(9,2) NOT NULL
33 );

34
35 -- AGREGANDO RELACIONES Y CLAVES PRIMARIAS

36
ALTER TABLE facturas
37
ADD FOREIGN KEY (cod_cliente)
38 REFERENCES clientes(cod_cliente);
39
40 ALTER TABLE detalle_facturas
41 ADD PRIMARY KEY (cod_factura,cod_producto);

42
ALTER TABLE detalle_facturas
43
ADD FOREIGN KEY (cod_factura)
44
REFERENCES facturas(cod_factura);
45
46 ALTER TABLE detalle_facturas
47 ADD FOREIGN KEY (cod_producto)

48 REFERENCES productos(cod_producto);

49
-- INSERTANDO REGISTROS A LA TABLA CLIENTES
50
INSERT INTO clientes VALUES('C0001','Julián Pérez','Lince','3214568');
51
INSERT INTO clientes VALUES('C0002','Maria Chavez','Jesus Maria','4215678');
52
INSERT INTO clientes VALUES('C0003','Juan Chacaltana','Comas','5416321');
53 INSERT INTO clientes VALUES('C0004','Guadalupe Fernández','Cercado','5216364');
54 INSERT INTO clientes VALUES('C0005','José Gómez','Lince','3458974');

55 INSERT INTO clientes VALUES('C0006','Carlos Segura','Rimac','4562312');

INSERT INTO clientes VALUES('C0007','Javier Buleje','Lince','3218974');
56
INSERT INTO clientes VALUES('C0008','Mary Huaman','Cercado','5631278');
57 INSERT INTO clientes VALUES('C0009','Cristian Sánchez','Cercado','5410591');
58 INSERT INTO clientes VALUES('C0010','Flor Paz','Lince','3564165');
59
60 -- INSERTANDO REGISTROS A LA TABLA PRODUCTOS

61 INSERT INTO productos VALUES('P0001','Memoria DDR 256 Mb',120.00);

IT América L. Sabalú Santillan 2

Instructora - Docentes
MOS & MTA Microsoft - CISCO
asabalu@isise.edu.pe
993.921.087
 - PEA2
62 INSERT INTO productos VALUES('P0002','Memoria DDR 500 Mb',190.00);
INSERT INTO productos VALUES('P0003','Mainboard PcChip v25',280.00);
63
INSERT INTO productos VALUES('P0004','Mainboard Intel V101',320.00);
64
INSERT INTO productos VALUES('P0005','Procesador Intel 3.6',390.00);
65 INSERT INTO productos VALUES('P0006','Procesador Intel 2.6',340.00);
66 INSERT INTO productos VALUES('P0007','Procesador Celeron 3.2',320.00);

67 INSERT INTO productos VALUES('P0008','Disco Duro SATA 80 GB',180.00);

INSERT INTO productos VALUES('P0009','Disco Duro SATA 120 GB',220.00);
68
INSERT INTO productos VALUES('P0010','Case Prezcom 500 Wats',120.00);
69
70 -- INSERTANDO REGISTROS A TABLA FACTURAS
71 INSERT INTO facturas VALUES('F0001','C0004',SYSDATE,0);
72 INSERT INTO facturas VALUES('F0002','C0006',SYSDATE,0);
INSERT INTO facturas VALUES('F0003','C0007',SYSDATE,0);
73
INSERT INTO facturas VALUES('F0004','C0004',SYSDATE,0);
74
INSERT INTO facturas VALUES('F0005','C0008',SYSDATE,0);
75 INSERT INTO facturas VALUES('F0006','C0002',SYSDATE,0);
76 INSERT INTO facturas VALUES('F0007','C0006',SYSDATE,0);

77 INSERT INTO facturas VALUES('F0008','C0009',SYSDATE,0);

INSERT INTO facturas VALUES('F0009','C0010',SYSDATE,0);
78
INSERT INTO facturas VALUES('F0010','C0004',SYSDATE,0);
79
80
-- INSERTANDO REGISTROS A LA TABLA DETALLE FACTURAS
81 INSERT INTO detalle_facturas VALUES('F0001','P0003',3,840.00);
82 INSERT INTO detalle_facturas VALUES('F0001','P0005',5,1950.00);

83 INSERT INTO detalle_facturas VALUES('F0001','P0006',2,680.00);

INSERT INTO detalle_facturas VALUES('F0001','P0009',1,220.00);
84
INSERT INTO detalle_facturas VALUES('F0002','P0009',3,660.00);
85 INSERT INTO detalle_facturas VALUES('F0002','P0005',8,3120.00);
86 INSERT INTO detalle_facturas VALUES('F0002','P0002',2,380.00);
87 INSERT INTO detalle_facturas VALUES('F0002','P0007',3,960.00);
INSERT INTO detalle_facturas VALUES('F0002','P0010',1,120.00);
88
INSERT INTO detalle_facturas VALUES('F0003','P0010',4,480.00);
89
INSERT INTO detalle_facturas VALUES('F0003','P0003',7,1960.00);
90 INSERT INTO detalle_facturas VALUES('F0003','P0006',2,680.00);
91 INSERT INTO detalle_facturas VALUES('F0003','P0009',1,220.00);

92 INSERT INTO detalle_facturas VALUES('F0004','P0005',3,1170.00);

INSERT INTO detalle_facturas VALUES('F0004','P0006',6,2040.00);
93
INSERT INTO detalle_facturas VALUES('F0004','P0007',8,2560.00);
94 INSERT INTO detalle_facturas VALUES('F0005','P0003',3,840.00);
95 INSERT INTO detalle_facturas VALUES('F0005','P0005',5,1950.00);

IT América L. Sabalú Santillan 3

Instructora - Docentes
MOS & MTA Microsoft - CISCO
asabalu@isise.edu.pe
993.921.087
 - PEA2
96 INSERT INTO detalle_facturas VALUES('F0005','P0002',1,190.00);
INSERT INTO detalle_facturas VALUES('F0006','P0007',3,960.00);
97
INSERT INTO detalle_facturas VALUES('F0006','P0002',2,380.00);
98
INSERT INTO detalle_facturas VALUES('F0007','P0003',3,840.00);
99 INSERT INTO detalle_facturas VALUES('F0007','P0008',4,720.00);
100 INSERT INTO detalle_facturas VALUES('F0008','P0003',3,840.00);

101 INSERT INTO detalle_facturas VALUES('F0008','P0006',1,340.00);

INSERT INTO detalle_facturas VALUES('F0009','P0003',3,840.00);
102
INSERT INTO detalle_facturas VALUES('F0010','P0009',5,1100.00);
103
104
105
106
2. Generar los siguientes objetos:
• DICCIONARIO DE DATOS
• EL MODELADO DE DATOS
• EL DIAGRAMA DE ENTIDAD RELACION
3. Procedimiento Almacenado
Crear un procedimiento almacenado para actualizar el precio de un producto que se envíe como parámetro de entrada.
4. Iterar un cursor
Crear un procedimiento almacenado que devuelva la relación de clientes que no tienen una factura emitida a su nombre.
5. Funciones
• Crear una función que devuelva el nombre del producto más vendido.
• Crear una función que nos devuelva la venta máxima.
• Crear una función que nos devuelva el promedio de las venta máxima.
6. Función de agrupación SUM
Escribir un procedimiento almacenado que permita actualizar el importe total de una factura, según el detalle que tenga
asignado. El código de factura debe enviarse como parámetro de entrada.
7. Reporte de ventas
Desarrollar un procedimiento almacenado que devuelva un reporte con la lista y sumatoria total de todas facturas agrupadas
por cliente.
8. Consultas
• Muestre el salario más alto, más bajo, salario total de la planilla y salario promedio de la tabla empleados. Etiquete las
columnas como «Salario Máximo», «Salario Mínimo», «Salario Planilla» y «Salario Promedio» respectivamente.
Redondee los resultados al valor entero más cercano, con un formato numérico para la moneda de dólares.
• Muestre el salario más alto, más bajo, salario total y salario promedio por cada tipo de puesto que se tiene en la
organización. Etiquete las columnas como «Puesto», «Salario Máximo», «Salario Mínimo», «Salario Total» y «Salario
Promedio» respectivamente. Redondee los resultados con dos cifras decimales, con un formato numérico para la
moneda de dólares.

IT América L. Sabalú Santillan 4

Instructora - Docentes
MOS & MTA Microsoft - CISCO
asabalu@isise.edu.pe
993.921.087
 - PEA2
• Escriba una consulta que muestre la cantidad de personas que tienen el mismo puesto y a cuanto asciende la suma total
de sus salarios. El resultado debe mostrarse en orden descendente por el puesto que tiene la mayor cantidad de
empleados.
• Escriba una consulta para mostrar la diferencia entre el salario más alto y el salario más bajo. Etiquete la columna como
«Diferencia Salarial.
• Muestre el ID del Manager y el salario del empleado con menos paga para ese manager. Excluya a cualquier empleado
cuyo manager sea desconocido. Excluya cualquier puesto donde el salario mínimo sea menor que $6,000. Ordene la
salida en orden descendente por salario.
• Muestre los nombres y apellidos (es una sola columna), nombre del departamento y el nombre del puesto de todos los
empleados cuyo código de ubicación de departamento (LOCATION_ID) es 1700. El resultado del reporte debe mostrarse
en orden ascendente por el apellido del empleado.
• Escriba una consulta para mostrar el número de empleado y apellidos de todos los empleados que ganan por encima
del salario promedio. Ordene los resultados en orden descendente de salario
• Elabore un reporte que contenga los nombres, apellidos, puesto, salario y fecha de ingreso de los empleados que le
reportan a King, ordenados por su fecha de ingreso a la empresa de manera descendente.
• Generar un reporte que muestre la cantidad de empleados y el salario total de cada departamento de la empresa.
Ordenar la información por el departamento que tiene más empleados a menos.
• Muestre el nombre del Manager, nombre del puesto, nombre de departamento, salario y cantidad de trabajadores que
tiene a su cargo. Ordenar según la cantidad de trabajadores en orden descendente.
• Encuentre a los empleados que tienen más de una rotación laboral.
• ¿Cuál fue el año donde ingresaron más trabajadores?
• Mostrar los nombres, apellidos, salario actual y el nuevo salario equivalente al 8% adicional de su salario actual de los
empleados cuyo salario actual sea igual, menor o mayor hasta por 5% del salario mínimo del puesto. Ordenar resultado
por el sueldo actual más alto.
• Generar un reporte incremente en un 20% el salario de los empleados que ingresaron a la empresa entre el año 2003 a
2005.
• Generar un reporte que muestre los nombres y apellidos, salario del empleado, el salario mínimo según el cargo del
empleado, de aquellos cuya diferencia del salario y el salario mínimo sea menor a S/. 500.00 y además que indique si
merece un aumento sólo si la diferencia entre hoy y la fecha de contratación es mayor a 5,000 días. El mismo reporte
debe mostrar el nuevo salario que corresponde a un aumento del 15% .

IT América L. Sabalú Santillan 5

Instructora - Docentes
MOS & MTA Microsoft - CISCO
asabalu@isise.edu.pe
993.921.087