Ejercicios base de datos

README.md

Estimados, aca compartamos los resultados y ejercicios de base de datos

Ejercicios listados:

• Ejercicio Nº1
  • Version Olaf
• Ejercicio Nº2
  • Version Olaf
  • Version Sole(100% funcional)
• Ejercicio Nº3
  • Version Edutardo
• Ejercicio Nº4
  • Version Sole
• Ejercicio Nº5
• Ejercicio Nº6

Ejercicio Nº1

Características:

• Joins (LEFT)
• Cálculos aritméticos
• Subconsultas
• Tratamiento de fechas(EXTRACT)
• Crea Vista con permisos de solo lectura

CREATE VIEW V_CLIENTES AS 
(
    SELECT
    C.IDCLIENTE AS "código cliente",
    C.EMPRESA AS "empresa",
    C.NOMBRE_CONTACTO AS "representante",
    C.FONO AS "numero de contacto",
    NVL(NULLIF(TO_CHAR((
        SELECT
            SUM((DP.PRECIOUNIDAD - (DP.PRECIOUNIDAD * DP.DESCUENTO)) * DP.CANTIDAD)
        FROM PEDIDO P
        LEFT JOIN DETALLE_PEDIDO DP ON DP.IDPEDIDO = P.IDPEDIDO
        WHERE P.IDCLIENTE = C.IDCLIENTE
        AND EXTRACT(YEAR FROM P.FECHAPEDIDO) = (EXTRACT(YEAR FROM SYSDATE)-1)
    ), '$999G999G999'), '0'), 'No realizó compras el año anterior')
    AS "total compra año anterior"
    FROM CLIENTE C
    -- El siguiente segmento de código es innecesario, pero podria ser util.
    -- group by C.IDCLIENTE, C.EMPRESA, C.NOMBRE_CONTACTO, C.FONO
)
WITH READ ONLY;

Ejercicio Nº2

Caracteristicas:

• Concatenación de caracteres
• Formateo de texto(to_char)
• Subconsulta
• Uso de joins(left join)
• Tratamiento de fecha(extract)
• Cálculos aritméticos
• Crea vista

SELECT
    E.RUT,
    (E.NOMBRE || E.APELLIDO) AS "EMPLEADO",
    E.CARGO,
    E.FECHA_CONTRATACION AS "CONTRATACION",
    TO_CHAR(E.SUELDO_BASE, '$999G999G999') AS "SUELDO BASE",
    TO_CHAR((
        SELECT
            SUM((DP.PRECIOUNIDAD - (DP.PRECIOUNIDAD * DP.DESCUENTO)) * DP.CANTIDAD)
        FROM PEDIDO P
        LEFT JOIN DETALLE_PEDIDO DP ON DP.IDPEDIDO = P.IDPEDIDO
    ), '$999G999G999') AS "TOTAL VENTA",
    TO_CHAR((
        CASE
        WHEN (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM E.FECHA_CONTRATACION)) >= 3 THEN E.SUELDO_BASE * 0.15
        ELSE E.SUELDO_BASE * 0.08
        END
    ), '$999G999G999') AS "REAJUSTE",
    TO_CHAR( E.SUELDO_BASE + (
        CASE
        WHEN (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM E.FECHA_CONTRATACION)) >= 3 THEN E.SUELDO_BASE * 0.15
        ELSE E.SUELDO_BASE * 0.08
        END
    ), '$999G999G999') AS "SUELDO REAJUSTADO",
    TO_CHAR((
        (E.SUELDO_BASE * 0.07) * (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM E.FECHA_CONTRATACION))
    ), '$999G999G999')  AS BONIFICACION
FROM EMPLEADO E;

EJERCICIO Nº2

By Sole

IMPORTANTE:

• Añadir sentencia para crear Vista(encapsular)

SELECT emp.rut, 
emp.nombre ||' ' || emp.apellido "EMPLEADO",
emp.cargo,
emp.fecha_contratacion "CONTRATACION",
TO_CHAR(emp.sueldo_base, '$999G999G999') "SUELDO",
sum(dped.preciounidad * dped.cantidad) "TOTAL VENTA",
CASE
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM emp.fecha_contratacion) >3 THEN to_char((emp.sueldo_base*0.15),'$999g999g999')
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM emp.fecha_contratacion) <3 THEN to_char((emp.sueldo_base*0.08),'$999g999g999')
ELSE to_char((emp.sueldo_base*0.07),'$999g999g999') END "REAJUSTE",
CASE
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM emp.fecha_contratacion) >3 THEN to_char(emp.sueldo_base+(emp.sueldo_base*0.15),'$999g999g999')
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM emp.fecha_contratacion) <3 THEN to_char(emp.sueldo_base+(emp.sueldo_base*0.08),'$999g999g999')
ELSE to_char(emp.sueldo_base+(emp.sueldo_base*0.07),'$999g999g999') END "SUELDO REAJUSTADO",
TO_CHAR(emp.sueldo_base*0.07, '$999G999G999') "BONIFICACION"
FROM empleado emp
LEFT JOIN pedido ped ON emp.IDEMPLEADO = ped.IDEMPLEADO
LEFT JOIN detalle_pedido dped ON ped.idpedido = dped.IDPEDIDO
WHERE EXTRACT(YEAR FROM ped.FECHAPEDIDO) = 2017
group by emp.rut, 
emp.nombre ||' ' || emp.apellido,
emp.cargo,
emp.fecha_contratacion,
emp.sueldo_base,
emp.fecha_contratacion
ORDER BY 2;

EJERCICIO Nº3

By Eduardo

SELECT  E.RUT,
        INITCAP(E.NOMBRE|| ' ' || E.APELLIDO) AS "EMPLEADO",
        TO_CHAR(E.SUELDO_BASE,'$999g999g999') AS "SUELDO",
        count(P.IDEMPLEADO) AS "VENTAS_REALIZADAS",
        CASE
            WHEN COUNT(P.IDEMPLEADO) < 50 THEN 'Se enviará a curso de capacitación en técnicas de venta'
            WHEN COUNT(P.IDEMPLEADO) BETWEEN 60 AND 80 THEN 'Tendrá un incentivo de $250.000.'
            WHEN COUNT(P.IDEMPLEADO)>80 THEN 'Tendrá un incentivo de $250.000 más 3 días libres' END "PROPUESTA INCENTIVO"
FROM EMPLEADO E
    LEFT JOIN PEDIDO P ON E.IDEMPLEADO=P.IDEMPLEADO
    WHERE EXTRACT(YEAR FROM P.FECHAPEDIDO)=EXTRACT(YEAR FROM SYSDATE)-1
    GROUP BY E.RUT, INITCAP(E.NOMBRE|| ' ' || E.APELLIDO),TO_CHAR(E.SUELDO_BASE,'$999g999g999')
ORDER BY 2;

EJERCICIO Nº4

By Sole, Editado por Tomas

CREATE SEQUENCE SEQ_EMPLEADO
INCREMENT BY 1
START WITH 12
MAXVALUE 9999
NOCACHE
NOCYCLE;

INSERT INTO EMPLEADO VALUES (SEQ_EMPLEADO.NEXTVAL, 'Rodriguez', 'Ignacio', 'Representante de ventas', 'Sr.', '08/05/1981', sysdate,
'Avenida Portales 453', null, null, null, null, null, 700000, null, '15554231-7', null); 
INSERT INTO EMPLEADO VALUES (SEQ_EMPLEADO.NEXTVAL, 'Mardones', 'Alejandro', 'Supervisor de ventas', 'Sr.', '15/06/1979', sysdate,
'pasaje lazarillo 123', null, null, null, null, null, 500000, null, '13324154-8', null); 

SELECT * FROM EMPLEADO;

EJERCICIO 6.a

DROP INDEX PRUEBA.IDX_DETALLE_PEDIDO;

CREATE INDEX PRUEBA.IDX_DETALLE_PEDIDO ON PEDIDO(IDCOMPANIA);

SELECT
    P.IDPEDIDO AS "NRO VENTA",
    P.FECHAPEDIDO AS "FECHA VENTA",
    UPPER(C.EMPRESA) AS "CLIENTE",
    UPPER(C.NOMBRE_CONTACTO) AS "REPRESENTANTE",
    NVL(C.FONO, 'SIN TELÉFONO DE CONTACTO') AS "NRO CONTACTO", 
    NVL(TO_CHAR(SUM(DP.CANTIDAD), '999'), 'SIN DETALLE') AS "CANTIDAD PRODUCTOS"
FROM PEDIDO P
JOIN CLIENTE C
    ON C.IDCLIENTE=P.IDCLIENTE
    AND P.IDCOMPANIA=3
LEFT OUTER JOIN DETALLE_PEDIDO DP
    ON DP.IDPEDIDO=P.IDPEDIDO 
GROUP BY
    P.IDPEDIDO,
    P.FECHAPEDIDO,
    UPPER(C.EMPRESA),
    C.EMPRESA,
    UPPER(C.NOMBRE_CONTACTO),
    NVL(C.FONO, 'SIN TELÉFONO DE CONTACTO') ;

EJERCICIO 6.b

DROP INDEX PRUEBA.IDX_PEDIDO;
CREATE INDEX PRUEBA.IDX_PEDIDO ON PEDIDO (LOWER(SUBSTR(IDCLIENTE, 1, 1)));

SELECT
    P.IDPEDIDO,
    P.FECHAPEDIDO,
    P.IDCLIENTE,
    P.NOMBRE_DESTINATARIO,
    DP.CANTIDAD 
FROM PEDIDO P
JOIN DETALLE_PEDIDO DP
ON P.IDPEDIDO=DP.IDPEDIDO
AND LOWER(SUBSTR(IDCLIENTE,1,1)) = 'S';

EJERCICIO 5.a

CREATE ROLE ROL_CONSULTA;
GRANT select ON PRUEBA.CLIENTE TO ROL_CONSULTA;
GRANT select ON PRUEBA.PEDIDO TO ROL_CONSULTA;
GRANT select ON PRUEBA.DETALLE_PEDIDO TO ROL_CONSULTA;
GRANT select ON PRUEBA.v_clientes TO ROL_CONSULTA;

CREATE ROLE ROL_CARDTIME;
GRANT select, update ON PRUEBA.CLIENTE TO ROL_CARDTIME;
GRANT select, update ON PRUEBA.PEDIDO TO ROL_CARDTIME;
GRANT select, update ON PRUEBA.DETALLE_PEDIDO TO ROL_CARDTIME;
GRANT select ON PRUEBA.V_EMPLEADOS_ICENTIVOSTO ROL_CARDTIME;

EJERCICIO 5.b

CREATE USER CONSULTA_USR IDENTIFIED BY "1234";
GRANT CONNECT, ROL_CONSULTA TO CONSULTA_USR ;
GRANT SELECT ON PRUEBA.EMPLEADO TO CONSULTA_USR;

CREATE USER CARDTIME_USR IDENTIFIED BY "1234";
GRANT CONNECT, RESOURCE, ROL_CARDTIME TO CARDTIME_USR ;
GRANT SELECT,UPDATE ON PRUEBA.EMPLEADO TO CARDTIME_USR;
GRANT SELECT ON PRUEBA.V_EMPLEADOS_INCENTIVOS TO CARDTIME_USR;
GRANT CREATE VIEW TO CARDTIME_USR;

EJERCICIO 5.c

CREATE PUBLIC SYNONYM EMPLEADO FOR PRUEBA.EMPLEADO;
CREATE PUBLIC SYNONYM CLIENTE FOR PRUEBA.CLIENTE;
CREATE PUBLIC SYNONYM PEDIDO FOR PRUEBA.PEDIDO;
CREATE PUBLIC SYNONYM DETALLE_PEDIDO FOR PRUEBA.DETALLE_PEDIDO;

CREATE SYNONYM CONSULTA_USR.V_CLIENTE FOR PRUEBA.V_CLIENTE;
CREATE SYNONYM CARDTIME_USR.V_CLIENTE FOR PRUEBA.V_CLIENTE;

EJERCICIO 5.d

CREATE SYNONYM CARDTIME_USR.V_EMPLEADOS_SUELDO FOR PRUEBA.V_EMPLEADOS_SUELDO;