Software">
Parcial SQL
Parcial SQL
Parcial SQL
PROYECTO
Profesor
Diego Alexander Moreno Rojas
Bogotá
12/05/2023
Después de su quinto años en operación la empresa quiere conmemorar a sus
clientes más fieles, y a los empleados que han venido apoyando todo el proceso,
para ello necesitan dos sabanas de datos que cumpla las siguientes reglas:
SABANA DE DATOS 1:
DECLARE
v_id_cliente cabecera_ordenes.id_cliente%TYPE;
v_count NUMBER;
// se inicia un For para iterar en la consulta seguido de un loop validando con un if si la cantidad de
compras esta en el rango establecido de la categoría, se actualiza la tabla clientes y se agrega el id
de la categoría respectiva
BEGIN
FOR i IN (SELECT id_cliente, COUNT(*) AS count FROM cabecera_ordenes GROUP BY
id_cliente ORDER BY id_cliente ASC)
LOOP
v_id_cliente := i.id_cliente;
v_count := i.count;
IF v_count BETWEEN 100 AND 150 THEN
UPDATE clientes SET id_categoria = '1' WHERE id_cliente = v_id_cliente;
ELSIF v_count BETWEEN 151 AND 200 THEN
UPDATE clientes SET id_categoria = '2' WHERE id_cliente = v_id_cliente;
ELSIF v_count BETWEEN 201 AND 250 THEN
UPDATE clientes SET id_categoria = '3' WHERE id_cliente = v_id_cliente;
ELSIF v_count BETWEEN 251 AND 300 THEN
UPDATE clientes SET id_categoria = '4' WHERE id_cliente = v_id_cliente;
ELSIF v_count > 300 THEN
UPDATE clientes SET id_categoria = '5' WHERE id_cliente = v_id_cliente;
ELSE
UPDATE clientes SET id_categoria = '0' WHERE id_cliente = v_id_cliente;
END IF;
END LOOP;
END;
2. Los clientes que realizaron al menos una compra por año desde el primer año
de apertura pasan a la base final.
// Se inicia un For para iterar en la consulta se tiene en cuenta que para extraer el año
se utiliza la función extract en la columna fecha_orden y se agrupa id_cliente y se
omiten los registros duplicados con la función distinct y se muestra el id del cliente
que tenga 6 datos repetidos,
se utiliza un Loop para agregar los registros en la tabla clientes_alto_valor los datos
solicitados cuando la variable iterada coincide con el id_cliente
DECLARE
v_id_cliente CABECERA_ORDENES.ID_CLIENTE%TYPE;
BEGIN
FOR c IN (SELECT DISTINCT ID_CLIENTE
FROM CABECERA_ORDENES
WHERE EXTRACT(YEAR FROM TO_DATE(FECHA_ORDEN, 'MM/DD/YYYY')) BETWEEN 2018
AND 2023
GROUP BY ID_CLIENTE
HAVING COUNT(DISTINCT EXTRACT(YEAR FROM TO_DATE(FECHA_ORDEN,
'MM/DD/YYYY'))) = 6
ORDER BY ID_CLIENTE ASC) LOOP
v_id_cliente := c.ID_CLIENTE;
-- insertar en la tabla CLIENTES_ALTO_VALOR
INSERT INTO CLIENTES_ALTO_VALOR (ID_CLIENTE, NOMBRE_CLIENTE, CIUDAD, PAÍS,
NOMBRE_DIVISIÓN, CATEGORÍA_CLIENTE, TELÉFONO, INDICATIVO)
SELECT c.ID_CLIENTE, cl.NOMBRE_CLIENTE, cl.CIUDAD, cl.PAIS, dv.NOMBRE_DIVISION,
cat.CATEGORIA, cl.TELEFONO, cl.ID_CATEGORIA
FROM CLIENTES cl
INNER JOIN DIVISIONES dv ON cl.ID_DIVISION = dv.ID_DIVISION
INNER JOIN CATEGORIA_CLIENTES cat ON cl.ID_CATEGORIA = cat.ID_CATEGORIA
WHERE cl.ID_CLIENTE = v_id_cliente;
END LOOP;
END;
3. Los clientes que nos visitan desde otras ciudades se reconocen por que en su
número de teléfono utilizan el indicativo entre parénesis, si estos clientes
están en la categoría diamond pasan a la base final.
// se selecciona de la tabla cliente todos los registros cuyo teléfono inicie con
paréntesis y en el id de su categoría sea igual a 5
select * from clientes
where telefono like '(%' and id_categoria = '5';
1. Los 15 empleados con mayor margen de ganancia mensual por año, teniendo
en cuenta las demás reglas.
// Se selecciona los diferentes datos solicitados para poder iterar y así poder
hacer el respectivo calculo
DECLARE
CURSOR c_proveedores IS
SELECT id_proveedor, nit, proveedor, telefono, contacto_, correo_
FROM proveedores
WHERE REGEXP_LIKE(correo_,
'^[^@]+@(?!gmail\.com|hotmail\.com|outlook\.com)\w+\.\w+$')
AND id_proveedor = v_id_p AND nit = v_nit
AND proveedor = v_proveedor AND telefono = v_telefono
AND contacto_ = v_contacto AND correo_ = v_correo;
BEGIN
FOR r_proveedor IN c_proveedores LOOP
INSERT INTO proveedores_correo (id_proveedor, nit, proveedor, telefono, contacto_,
correo_)
VALUES (r_proveedor.id_proveedor, r_proveedor.nit, r_proveedor.proveedor,
r_proveedor.telefono, r_proveedor.contacto_, r_proveedor.correo_);
DBMS_OUTPUT.PUT_LINE('registros insertados ' || SQL%ROWCOUNT);
END LOOP;
COMMIT;
END;
((VENTA_PRODUCTO-COSTO_PRODUCTO)/VENTA_PRODUCTO)*100 AS MARGEN
FROM DETALLE_ORDENES
SELECT
d.nro_factura,
d.id_producto,
(SELECT p.id_proveedor FROM productos p WHERE p.id_producto = d.id_producto) AS
id_proveedor,
d.margen
FROM Detalle_Ordenes d
WHERE d.margen > 15;
6. Dejar el registro con mayor margen sin importar le año en el que fue generado.
// el código ejecuta una consulta en la tabla DETALLE_ORDENES y devuelve todas las filas
donde el valor de la columna MARGEN es igual al valor máximo de MARGEN en toda la
tabla DETALLE_ORDENES.
MARGEN.
SELECT *
FROM DETALLE_ORDENES
SELECT *
FROM PROVEEDORES