CBY2001_EP_13

Vicerrectoría Académica Dirección de Servicios Académicos Subdirección de Servicios a Escuelas

ACTIVIDAD DE APRENDIZAJE PRESENCIAL

Sigla Curso

CBY2001

Nombre Curso

Créditos 8

Hrs. Semestrales Totales

90

CONSULTAS SQL

Requisitos MBY1001

Fecha Actualización

Octubre 2016

Escuela o Programa Transversal

ESCUELA DE INFORMÁTICA Y TELECOMUNICACIONES

EA

13

Carrera/s

INGENIERÍA EN INFORMÁTICA ANALISTA PROGRAMADOR COMPUTACIONAL INGENIERÍA EN GESTIÓN DE TECNOLOGÍAS DE LA INFORMACIÓN

N° Unidad

3

APRENDIZAJE(S) ESPERADO(S)

Construir sentencias complejas de recuperación y manipulación de datos para solucionar los requerimientos de información planteados. Administrar usuarios, usuarios, privilegios de los usuarios y objetos en los esquemas esquemas de la base de datos para garantizar la seguridad y mejorar el acceso de los datos para solucionar los requerimientos planteados. Crea vistas, sinónimos, índices y secuencias en los esquemas de la base de datos para recuperar y manipular en forma eficiente los datos de acuerdo con los requerimientos planteados. NOMBRE DE LA ACTIVIDAD: Optimizando la gestión de control de ventas de Oracle

INSTRUCCIONES Desbloquee el usuario HR y ejecute el script Complementa_HR_4.sql que se proporciona en la web. Mantenga la cuenta abierta por si desea comprobar alguna de las actividades. DESCRIPCIÓN DE LA ACTIVIDAD:

Debido al surgimiento de nuevas necesidades en la empresa y gracias a su buen desempeño le han vuelto a contratar para que, junto a su personal, realice estas actividades. En concreto, se requieren una serie de informes nuevos para los ejecutivos encargados de pagar las remuneraciones. Si bien el control de las ventas no tiene que ver directamente con estos empleados, si requieren datos relativos a ventas que son relevantes a la hora de efectuar los pagos. Después de analizar el problema Ud. ha concluido que la solución apropiada es crear estos informes usando la utilidad de vistas que provee la base de datos, labor a la cual debe asignar a un programador de su staff. También se requiere incorporar a la base de datos a una serie de practicantes que se han superado la fase


de requisitos y entrenamiento y que deben ser contratados. 1.

En primer lugar, para dar inicio a la serie de actividades por realizar, se requiere que Ud. incorpore al programador que efectuará parte de las mismas. Dado que es posible que luego se contraten más profesionales para desarrollar labores similares, se acordó con la gerencia crear un rol que agrupe los privilegios de objeto necesarios. De esta forma, se debe implementar lo que se detalla:

2.

Un rol con las características siguientes: ROL DEVELOPER_ROLE

-

3.

Crear el usuario DEVELOPER_1 con las características siguientes: USUARIO DEVELOPER_1

4.

PRIVILEGIOS DE OBJETO Privilegios de consulta y modificación (se incluye inserción y eliminación) sobre la tabla EMPLOYEES del esquema HR. Privilegios de consulta sobre las tablas agregadas al esquema HR (REP_VENTAS, VENTAS, PRACTICANTES)

Características Roles: CONNECT, RESOURCE, DEVELOPER_ROLE Privilegios de objeto: Sólo los otorgados a través del rol DEVELOPER_ROLE Privilegios de Sistema: Creación de vistas y de sinónimos en cualquier esquema.

Oracle ha iniciado una fase de renovación de personal; poco a poco, los nuevos trabajadores irán sustituyendo a los antiguos. Por lo pronto, se requiere incorporar a la tabla EMPLOYEES a todos los practicantes que superaron las pruebas iniciales y la fase de entrenamiento, los que se identifican con status 1 en la tabla de practicantes. La gerencia había encomendado esta labor a otro programador con anterioridad pero no se siguieron estrictamente sus indicaciones. Para esta nuevo proceso, la Gerencia le ha solicitado asignar números internos para el campo employee_id a contar del número 10001, con el fin de identificar claramente a esta nueva generación de empleados. Para satisfacer estas necesidades usted encarga al programador developer_1 la realización de las siguientes tareas. a. Crear una secuencia llamada SQ_IDEMP para asignar los nuevos números a los practicantes que se incorporan a la empresa. La secuencia debe iniciar en 10001. El incremento será el que está seteado por omisión. b. Visualizar mediante una consulta los nombre de los practicantes que superaron las pruebas y que aún no están registrados en la tabla EMPLOYEES.

c. Mediante una inserción masiva traspasar a todos los practicantes seleccionados que no hayan sido ya ingresados a la tabla EMPLOYEES. Utilizar la secuencia creada para asignar el número del


empleado. Al finalizar el proceso los datos en la tabla EMPLOYEES deben ser los siguientes:

5.

Todos los meses uno de los ejecutivos encargados de remuneraciones debe emitir un informe con las ventas de los representantes correspondientes al mes anterior, dado que los sueldos se pagan agregando, al salario de los empleados, el monto que perciben por concepto de comisiones, las que deben calcularse sobre las ventas del mes inmediatamente anterior. Una vez que este informe ha sido emitido, se procede a ejecutar el proceso de cálculo de las remuneraciones. Dado que ellos no son especialistas en SQL, siempre tienen dificultades para generar el informe. Los ejecutivos interesados le han manifestado que el informe debe contener el número, fecha y monto de la venta, la id y nombre del representante de ventas y el monto de la comisión que se debe pagar, la que corresponde a un 2% de la venta por el porcentaje de comisión que la empresa ha otorgado al vendedor. Dadas las dificultades observadas, Ud. le solicita al programador developer _1 que construya una vista llamada v_comisiones que para mayor seguridad debe ser de solo lectura. La vista debe quedar almacenada en el esquema HR y mostrar los datos ordenados por fecha y nombre completo del vendedor y las cifras en formato monetario. La vista se construyó el 05/11/2017.

6.

Con el fin de incentivar a los vendedores de la compañía y particularmente a aquellos que no presentan ventas en el mes anterior al actual, la compañía ha decidido otorgar un incentivo monetario. Dado que no desea desvincular a los vendedores que no presenten ventas, la Gerencia ha decidido enviarlos a un curso de capacitación. Para aquellos que si presenten ventas se otorgará un bono por cada venta realizada, de acuerdo con las siguientes indicaciones: si el número de ventas oscila entre 1 y 4 el bono será equivalente a $2 00 (dólares) por venta, para los vendedores que tengan entre 5 y 8 ventas el bono será de $250 por venta y de $300 si el número de ventas es igual o mayor a 9. La vista debe mostrar la id del vendedor, su nombre completo, la cantidad de ventas en el mes de proceso y el monto del bono en formato monetario. La vista fue creada por el usuario developer_1 el 05/11/2017 por lo que debe devolver las ventas del mes de octubre y debe quedar almacenada en el esquema del usuario HR.

7.

La empresa desea estimular a los representantes de venta que superaron el promedio del total de ventas


mensuales durante el mes anterior al actual. Aún no está definido cuál será el estímulo, pero se requiere que los ejecutivos de remuneraciones cuenten con este dato para el siguiente proceso de pagos. Por este motivo, como usuario developer_1 debe crear una vista llamada v_ventas_totales que muestre id del empleado, nombre del vendedor y el total de sus ventas, para todos los empleados cuyas ventas totales superaron la venta total promedio en el mismo período. La vista, tal como en los casos anteriores, se creó el 05/11/2017 por lo que debe considerar las ventas del mes de octubre y quedar almacenada dentro del esquema HR.

8.

Dado que los supervisores de venta representan una parte crucial en el éxito del equipo de ventas, la Gerencia también ha considerado darles un incentivo, motivo por el cual se requiere que elabore una vista llamada v_ejecutivosupervisor que muestre el nombre del ejecutivo, el total de las ventas del mes de proceso, el monto de la comisión que se paga al ejecutivo, el que equivale a un 2% del total de sus ventas, el nombre del supervisor a cargo, el porcentaje de comisión que la compañía ha establecido para el supervisor y el monto a pagar por el concepto de comisión del ejecutivo, el que se calcula sobre un 5% del monto vendido por cada representante que tenga a cargo. La vista se creó el 05/11/2017 por lo que debe procesar las ventas del mes de octubre y quedar almacenada en el esquema HR.

9.

A contar de ahora, y por cada mes, la Gerencia ha tomado la decisión de premiar al supervisor cuyos dirigidos acumulen el mayor monto de ventas. El premio aún no lo dirime el directorio de la empresa, pero en los pasillos se especula que se trata de un viaje de fin de semana (4 días, 3 noches) a alguna playa del caribe, con todos los gastos pagados. Por este motivo, para que esta información esté disponible para el directorio a través de los encargados de remuneraciones, le ha solicitado al programador developer _1 que elabore una vista que muestre la id, el nombre completo y el monto total de las ventas de sus dirigidos en el mes de proceso. La consulta fue elaborada por el programador el 05/11/2017 por lo que debe procesar las ventas del mes de octubre y quedar almacenada en el esquema HR.

10. Con el fin de facilitar la creación de los informes necesarios para el proceso de pago de las remuneraciones y también con el fin de facilitar el acceso a ciertos objetos y de incrementar la seguridad, de forma que sólo ciertos usuarios puedan accederlos, usted ha estimado necesario realizar las tareas siguientes, las que debe efectuar como usuario administrador (SYSTEM o SYS): a. Crear un sinónimo para que cualquier usuario que posea privilegios sobre la vista v_comisiones pueda accederla sin anteponer el nombre del esquema. b. Crear sinónimo para que cualquier usuario que posea privilegios sobre la vista v_incentivos pueda accederla sin anteponer el nombre del esquema.


c. Crear sinónimos para que cualquier usuario con privilegios sobre las tablas EMPLOYEES, REP_VENTAS y VENTAS del esquema HR pueda accederlas sin anteponer el nombre del esquema. d. Crear un rol con las características siguientes: ROL REMUN_ROLE

-

PRIVILEGIOS DE OBJETO Privilegio de consulta sobre la tabla EMPLOYEES del esquema HR. Privilegio de consulta sobre todas las tablas agregadas al esquema HR (REP_VENTAS, VENTAS, CARGAS_FAMILIARES, ANNOS_TRABAJADOS, TRAMO_PAGO_CARGAS, RANGO_AUMENTO)

e. Crear el usuario REMUN_1 con las características siguientes: USUARIO REMUN_1

Características Roles: CONNECT y REMUN_ROLE Privilegios de objeto: Consulta sobre las vistas v_comisiones y v_total_ventas del esquema HR. Privilegios de Sistema: No posee

11. Conecte como usuario REMUN_1 y pruebe si puede consultar las tablas y vistas para las cuales se le concedieron privilegios. 12. La gerencia desea tener a la brevedad el pre-informe de las remuneraciones que corresponde pagar en el mes actual, razón por la cual, dado que el ambiente de trabajo de los ejecutivos de remuneraciones ya ha sido diseñado, le han pedido que, como usuario REMUN_1 desarrolle una consulta que muestre por el momento los haberes de los representantes de venta en el mes de proceso. La consulta debe mostrar la id del vendedor, su nombre completo, el salario, el monto de la comisión que debe obtener de la vista v_comisiones, el monto por concepto de cargas que corresponde a $50 (dólares por carga) y el monto del bono por haber superado el promedio de ventas en el mes anterior, que corresponderá a un 1% del total de sus ventas, dato que debe obtenerse de la vista v_total_ventas. En la consulta debe usar los sinónimos a los que tiene acceso.


Duración de la actividad (horas): 3 horas

Recursos de información:

Forma de trabajo:

□ Impreso

X Individual □ Grupal Infraestructura (lugar):

□ Informático Material de apoyo (insumos y equipamiento) para la actividad:

□ Sala de clases X Laboratorio □ Sala de equipos

1. Cápsulas de video 2. Material complementario de la experiencia

□ Otros (especifique)_____________

Modalidad X Presencial □ No Presencial

CBY2001_EP_13

Recommend Documents