• Author / Uploaded
• Jorge Espejo

Citation preview

PRUEBA PARCIAL N°1 PBD3301 FORMA A NOMBRE: SECCIÓN:

FECHA:

UNIDAD DE APRENDIZAJE: Construyendo Consultas SQL. UNIDAD DE COMPETENCIA: Opera sobre una base de datos utilizando lenguaje SQL

permitiendo realizar operaciones con los datos existentes en ella. DETALLE EVALUACIÓN APRENDIZAJES EVALUADOS:  Construir sentencias de recuperación y manipulación, de una base de datos

relacional, según sintaxis, restricciones del lenguaje, requisitos de la lógica negocios, requisitos de información y sistema de gestión de base de datos. Puntaje Total:

84 puntos

Nota: 7.0

Puntaje:

50.4 puntos

Nota: 4.0

Puntaje obtenido:

NOTA:

INSTRUCCIONES GENERALES:  Desarrolle los casos planteados usando la herramienta Oracle SQLDeveloper.  Puede hacer usos de las presentaciones de la asignatura y/o apuntes personales como material de consulta durante el desarrollo de la prueba.  Los casos están planteados sobre el Modelo que se adjunta como Anexo “A”. Por esta razón, para construir las soluciones de los requerimientos de información planteados en cada caso, deberá ejecutar el scripts scripts_crea_tablas_bd_propiedades.sql. (entregado por el docente) que creará y poblará las tablas del Modelo entregado.  Todos los resultados deben ser redondeados a valores enteros.  Al finalizar la prueba envíe los scripts construidos al profesor mediante un mensaje interno de BlackBoard. NOTA: LOS RESULTADO QUE SE MUESTRAN EN CADA CASO SON REFERENCIALES PARA QUE PUEDA VER EL FORMATO EN EL CUÁL SE REQUIERE LA INFORMACIÓN.

RENT A HOUSE es una empresa líder en la región Metropolitana dedicada al arriendo de propiedades cuyo éxito se debe en gran medida a la forma innovadora en que manejan los arriendos de las propiedades a través de los diferentes Ejecutivos de Arriendos que son parte de la empresa. Debido al incremento del negocio, la Gerencia ha decido crear un departamento de Informática. Después de haber efectuado todas las etapas del proceso de selección, Ud. ha sido contratado para ser parte de este nuevo departamento y será el encargado de la primera etapa del proyecto del Sistema de Control de Arriendos RENT A HOUSE que consiste en automatizar los requerimientos de información para la gestión del CORREDORA DE PROPIEDADES negocio que se plantean en cada caso. 1.- Algunos ejecutivos de arriendos de la empresa fueron sorprendidos modificando los datos que tienen relación a las propiedades que la Corredora tiene a su cargo sin que se les haya autorizado a hacerlo. Por este motivo, la Gerencia desea restringir de alguna manera el acceso a los datos de las tablas. Para ello Ud. ha propuesto que esta información sea presentada a los usuarios a través de una vista que permita sólo efectuar consultas de datos a través de ella. La vista V_PROPIEDADES permitirá visualizar el rut del propietario, número de la propiedad, el tipo de propiedad, cantidad de dormitorios, cantidad de baños, valor de su arriendo y valor del gasto común (si no se cobra gasto común por la propiedad se debe mostrar en valor CERO). Si la propiedad no posee dormitorios y/o baños se debe visualizar el mensaje NO POSEE. La información en la Vista se debe obtener en el formato que se muestra en el ejemplo y ordenada en forma ascendente por número de dormitorios y valor del arriendo.

CREATE OR REPLACE VIEW V_PROPIEDADES_RO AS SELECT prop.numrut_prop || '-' || prop.dvrut_prop RUT_PROPIETARIO, Pro.Nro_Propiedad Nro_Propiedad, tp.desc_tipo_propiedad TIPO_PROPIEDAD, pro.direccion_propiedad direccion, NVL(TO_CHAR(pro.nro_dormitorios),'NO POSEE') DORMITORIOS, NVL(TO_CHAR(pro.nro_banos),'NO POSEE') BAÑOS, TO_CHAR(pro.valor_arriendo,'$999,999,999') "VALOR ARRIENDO", TO_CHAR(NVL(pro.valor_gasto_comun,0),'$999,999,999') "VALOR GASTO COMUN" FROM propietario prop JOIN propiedad pro ON(prop.numrut_prop = pro.Numrut_Prop) JOIN tipo_Propiedad tp ON(Pro.id_tipo_propiedad = tp.id_tipo_propiedad) ORDER BY dormitorios, valor_arriendo WITH READ ONLY;

2.- La Gerencia desea mejorar la gestión de los Ejecutivos de Arriendo de propiedades y poder situar a la Corredora de Propiedades entre las mejores del país. Por esta razón, de acuerdo a las propiedades que estos empleados tienen a su cargo se les apoyará e incentivará para poder cumplir con las metas establecidas por la Gerencia. La tabla que se muestra a continuación explica la estrategia que la Gerencia desea implementar: PROPIEDADES A SU CARGO

Aún no tiene propiedades a su cargo Entre 1 y 2 propiedades a su cargo Entre 3 y 4 propiedades a su cargo El resto

PROPUESTA

Se enviará a curso de técnicas de captación de clientes Se le incentivará con bono de $40.000 para aumentar arriendos Se le incentivará con bono de $70.000 para aumentar arriendos Se le asignará un bono el próximo mes de $150.000

La información que se requiere es rut del empleado, nombre completo del empleado, propiedades que tiene a su cargo y la propuesta de incentivo que le corresponde. El informe se debe mostrar en el formato del ejemplo y ordenado en forma descendente por el total de propiedades que el ejecutivo de arriendo posee y en forma ascendente por su nombre:

SELECT TO_CHAR(e.numrut_emp,'09G999G999') || '-' || e.dvrut_emp "RUT EMPLEADO", INITCAP(e.appaterno_emp) || ' ' || SUBSTR(e.apmaterno_emp,1,1) || '. ' || INITCAP(e.nombre_emp) "NOMBRE EMPLEADO", COUNT(p.nro_propiedad) "PROPIEDADES A SU CARGO", (CASE WHEN COUNT(p.nro_propiedad) < 1 THEN 'Se enviará a curso de Técnicas de Captación de Clientes' WHEN COUNT(p.nro_propiedad) BETWEEN 1 AND 2 THEN 'Se le incentivará con bono de $40.000 para aumentar arriendos' WHEN COUNT(p.nro_propiedad) BETWEEN 3 AND 4 THEN 'Se le incentivará con bono de $70.000 para aumentar arriendos' ELSE 'Se le asignará un bono el próximo mes de $150.000' END) "PROPUESTA DE INCENTIVO" FROM empleado e LEFT OUTER JOIN propiedad p ON(e.numrut_emp=p.numrut_emp) WHERE e.id_categoria_emp=3 GROUP BY e.numrut_emp, e.dvrut_emp, e.appaterno_emp,e.apmaterno_emp,e.nombre_emp ORDER BY "PROPIEDADES A SU CARGO" DESC, e.nombre_emp ASC;

3.- En la actualidad no se sabe las comunas en las cuales la Corredora de Propiedades posee más propiedades para arrendar. Para la nueva Gerencia esta información es de importancia para su estrategia de captación de posibles nuevos clientes que deseen arrendar. Por eso, a partir la información que se genere se tomarán decisiones de cómo orientar las estrategias del negocio. Para solucionar este requerimiento, el usuario requiere saber por cada comuna el total de propiedades y su tipo de aquellas comunas que poseen una cantidad de propiedades en arriendo igual o mayor que el total de propiedades promedio por comuna. El informe debe visualizar la información en el formato del ejemplo y ordenada por comuna y total de propiedades:

SELECT c.nombre_comuna comuna, tp.desc_tipo_propiedad "TIPO DE PROPIEDAD", COUNT(p.nro_propiedad) "TOTAL DE PROPIEDADES" FROM comuna c JOIN propiedad p ON(c.id_comuna = p.id_comuna) JOIN tipo_propiedad tp ON(p.id_tipo_propiedad = tp.id_tipo_propiedad) HAVING COUNT(p.nro_propiedad) >= (SELECT ROUND(AVG(COUNT(nro_propiedad))) FROM propiedad GROUP BY id_comuna) GROUP BY c.nombre_comuna, tp.desc_tipo_propiedad ORDER BY comuna, "TOTAL DE PROPIEDADES";

4.- A contar del año 2018 se modificará la forma en que se pagarán las comisiones de los Ejecutivos de Arriendos ya que la forma actual en que se efectúa el cálculo de las comisiones afecta a muchos empleados. En una primera propuesta, el valor de la comisión va a depender de la cantidad de propiedades y el valor de arriendo promedio de las propiedades que tiene a su cargo. Por ejemplo, si el empleado posee 5 propiedades a su cargo la comisión que le corresponde es el 5% del valor de arriendo promedio de esas propiedades, si el empleado posee 2 propiedad a su cargo el valor de su comisión será el 2% del valor de arriendo promedio de las 2 propiedades etc. Por esta razón, se desea efectuar una primera prueba considerando sólo a los Ejecutivos de Arriendos que poseen propiedades a su cargo. La información que requiere la Gerencia es el nombre completo del ejecutivo, su salario actual, total de propiedades que tiene a su cargo, valor total de los arriendos de las propiedades que tiene a cargo, valor promedio de arriendo de las propiedades a su cargo, valor de la comisión que le corresponde y el valor del sueldo total (que corresponde a la suma de su salario actual y el valor de la comisión). La información se requiere en el formato del ejemplo y ordenada en forma alfabética por apellido paterno.

SELECT e.numrut_emp || '-' || e.dvrut_emp "RUT EMPLEADO", e.nombre_emp || ' ' || e.appaterno_emp || ' ' || e.apmaterno_emp "NOMBRE EMPLEADO", TO_CHAR(e.sueldo_emp,'999G999G999') Sueldo, COUNT(p.nro_propiedad) "PROPIEDADES A SU CARGO", TO_CHAR(NVL(SUM(p.valor_arriendo),0),'$999G999G999') "VALOR TOTAL DE ARRIENDO", TO_CHAR(NVL(AVG(p.valor_arriendo),0),'$999G999G999') "VALOR ARRIENDO PROMEDIO", TO_CHAR(NVL(AVG(p.valor_arriendo)* COUNT(p.nro_propiedad)/100,0),'$999G999G999') "VALOR COMISIÓN", TO_CHAR(e.sueldo_emp + NVL(AVG(p.valor_arriendo)* COUNT(p.nro_propiedad)/100,0),'$999G999G999') "SUELDO TOTAL" FROM empleado e JOIN propiedad p ON (e.numrut_emp = p.numrut_emp) WHERE id_categoria_emp=3 GROUP BY e.numrut_emp, e.dvrut_emp, e.nombre_emp, e.appaterno_emp, e.apmaterno_emp, e.sueldo_emp ORDER BY e.appaterno_emp;

5.- La Gerencia de la Corredora de Propiedades preocupada por la gestión de sus empleados, desea tener una reunión con los Ejecutivos de Arriendos que a la fechan tienen menos de 3 propiedades en arriendo a su cargo. La idea es poder definir con ellos una estrategia que permita mejorar su desempeño enviándolos a una capacitación. Una vez establecidas las necesidades de estos empleados, se les enviará a sus correos la información de la fecha y lugar en donde tendrá que asistir a la capacitación. Para esto, se requiere contar con un informe con los siguientes requerimientos:  Por cada ejecutivo de arriendo saber su nombre completo, dirección y comuna en la que vive, fecha de nacimiento, años que lleva trabajando en la empresa, correo del empleado y propiedades que tiene a su cargo.  De acuerdo a lo indicado por el usuario, el correo del empleado corresponde a: los años que lleva trabajando en la empresa + la penúltima y antepenúltima letra del apellido paterno + el año de nacimiento + '@empleadorh.cl'. Al emitir el informe, la información se debería visualizar en el formato del ejemplo y ordenada alfabéticamente por apellido paterno del empleado en forma descendente:

SELECT e.nombre_emp || ' ' || e.appaterno_emp || ' ' || e.apmaterno_emp empleado, e.direccion_emp || ' ' || UPPER(c.nombre_comuna) "DIRECCION EMPLEADO", e.fecnac_emp "FECHA NACIMIENTO", EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fecing_emp) "AÑOS TRABAJADOS", (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fecing_emp)) ||LOWER(SUBSTR(e.appaterno_emp,-3,2) || SUBSTR(e.fecnac_emp,7) || '@empleadorh.cl') "CORREO EMPLEADO", COUNT(p.nro_propiedad) "PROPIEDADES A SU CARGO" FROM comuna c JOIN empleado e ON(c.id_comuna = e.id_comuna) LEFT OUTER JOIN propiedad p ON(e.numrut_emp = p.numrut_emp) WHERE e.id_categoria_emp=3 GROUP BY e.nombre_emp, e.appaterno_emp,e.apmaterno_emp,e.fecnac_emp,e.direccion_emp,c.nombre_comuna, SYSDATE, fecing_emp HAVING COUNT(p.numrut_emp) < 3 ORDER BY e.appaterno_emp DESC;