Universidad Privada Antenor Orrego Facultad de Ingeniería Ingeniería de Computación y Sistemas
Tiempo: 90 minutos Aulas: G501
EXAMEN LABORATORIO DE BASE DE DATOS 2014-10 Estudiante: JORGE ANGELO MELGAR GOZZER ============================================================================================== 1. Dado el siguiente DER, escribir las sentencias SQL para obtener los siguientes r esultados: - Utilizar el tipo de join apropiado y subconsultas subconsultas según sea conveniente a.
Crear la tabla JOBS sabiendo que JOB_ID es PK (2 ptos.)
CREATE TABLE JOBS ( JOB_ID VARCHAR2(10) NOT NULL PRIMARY KEY, JOB_TITLE VARCHAR 2(10) NOT NULL, MIN_SALARY NUMBER(6,0) MIN_SALARY NUMBER(6,0) ); b. Todas las regiones cuyo nombre inicien con “SU” y región_id este comprendido comprendido entre 1 y 100 100 (1 ptos.)
SELECT * FROM REGIONS WHERE REGION_NAME LIKE ‘SU%’ AND REGION _ID BETWEEN 1 AND 100 ; c.
Listar id de empleados y su nombre completo, de aquellos que han variado su job, presentarlo en orden desendente por el numero de variaciones. (2 ptos.)
SELECT E.EMPLOYEE_ID, LAST_NAME LA ST_NAME ll ‘’ ll FIRST_NAME AS NOMBRES , COUNT (*) FROM EMPLOYEES.E JOIN JOB_HISTORY.JH ON E.EMPLOYEE_ID = JH, EMPLOYEE_ID EMPLOYEE_ID GROUP BY E.EMPLOYEE_ID, LAST_NAME ll’’llFIRST_NAME ORDER BY 3 DESC d. Listar jobs que no tuvieron variación de asignación entre 01/01/2009 y 31/12/2012 y q su salario minimo sea mayor a 5000 (3ptos.) SELECT JOB_ID, JOB_TITTLE FROM JOBS MINUS SELECT J.JOB_ID, JOB_TITTLE FROM JOBS.J JOIN JOB_HISTORY.JH JOB_HISTORY.JH ON J.JOB=ID=JH.JOB_ID WHERE MIN_SALARY > 5000 AND ( START_DATE >= > = TO_DATE(‘31/12/2012’,’DD/MM/YYYY’) AND END_DATE <= <= TO_DATE(‘01/01/2009’’DD/M TO_DATE(‘01/01/2009’’DD/MM/YYYY’) M/YYYY’)
e.
Todos los departamentos con su dirección y el administrador: department_id, department_name, nombre completo del administrador, dirección dirección completa. (3 ptos.)
SELECT department_id, department_name, first_name ll''ll last_name AS NOMBRES, stree_adreessll''llcityll''ll state_province AS DIRECCION FROM DEPARTMENTS.D JOIN EMPLOYEES.E EMPL OYEES.E ON.D.MANAGER_ID= E.EMPLOYEE_ID JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
Ing. Jorge Piminchumo Flores
29-04-2014 1
Universidad Privada Antenor Orrego Facultad de Ingeniería Ingeniería de Computación y Sistemas
Tiempo: 90 minutos Aulas: G501
EXAMEN LABORATORIO DE BASE DE DATOS 2014-10 f.
Liste los Jobs y su nombre con el salario mas alto, promedio, total de salario y cuantos empleados existen por cada Jobs, solo mostrar a aquellos Jobs con salario mas alto mayor a 100,000. Redondear el máximo y minimo y truncar el promedio (3 ptos.)
SELECT job_id, job_tittle,ROUND( MAX(salary),2) ,TRUNCATE( AVG (salary),2) , SUM ( salary) , COUNT (*) FROM employees e NATURAL JOIN jobs j GROUP BY job_id, job_titlle HAVING ROUND(MAX(salary)) > 100000
g.
Mostrar empleados, su nombre, sus job, nombre de Jobs, salario y el máximo salario del Jobs de aquellos empleados que su salario es mayor o igual al salario máximo del Jobs. (3 ptos.)
SELECT employee_id, first_name ll’’ll last_name AS NOMBRES, job_id, job_tittle, MAX(salary) FROM Employees e NATURAL JOIN Jobs J WHERE salary >= MAX(SALARY) h.
Listar id empleado y nomnbre completo de empleados que trabajaron a la vez en el departamento ‘CONT’, ‘GERE’ y ‘VENT’ en el 2002,2005, 2008 y 2010; y su fecha de contrato este entre 25/04/2000 y 15/12/2010. (3 ptos.)
SELECT e.employee_id, first_name ll’’ll last_name AS NOMBRES FROM employees e JOIN departments ON ( e.department_id = d.department_id) JOIN job_history j ON (e.employee_id=j.employee_id) WHERE department_name = ‘CONT’ AND TOCHAR(start_date’yyyy’) IN (‘2002’,’2005’,’2008’,’2010’)AND Hire_date BETWEEN TO_DATE(‘25/04/2000’,’dd,mm,yyyy’) AND TO_DATE(‘15/12/2010’,’dd/mm/yyyy’) INTERSECT SELECT e.employee_id, first_name ll’’ll last_name AS NOMBRES FROM employees e JOIN departments ON ( e.department_id = d.department_id) JOIN job_history j ON (e.employee_id=j.employee_id) WHERE department_name = ‘GERE’ AND TOCHAR(start_date’yyyy’) IN (‘2002’,’2005’,’2008’,’2010’)AND Hire_date BETWEEN TO_DATE(‘25/04/2000’,’dd,mm,yyyy’) AND TO_DATE(‘15/12/2010’,’dd/mm/yyyy’) INTERSECT SELECT e.employee_id, first_name ll’’ll last_name AS NOMBRES FROM employees e JOIN departments ON ( e.department_id = d.department_id) JOIN job_history j ON (e.employee_id=j.employee_id) WHERE department_name = ‘VENT’ AND TOCHAR(start_date’yyyy’) IN (‘2002’,’2005’,’2008’,’2010’)AND Hire_date BETWEEN TO_DATE(‘25/04/2000’,’dd,mm,yyyy’) AND TO_DATE(‘15/12/2010’,’dd/mm/yyyy’)
Ing. Jorge Piminchumo Flores
29-04-2014 2
Universidad Privada Antenor Orrego Facultad de Ingeniería Ingeniería de Computación y Sistemas
Tiempo: 90 minutos Aulas: G501
EXAMEN LABORATORIO DE BASE DE DATOS 2014-10
Ing. Jorge Piminchumo Flores
29-04-2014 3
Universidad Privada Antenor Orrego Facultad de Ingeniería Ingeniería de Computación y Sistemas
Tiempo: 90 minutos Aulas: G501
EXAMEN LABORATORIO DE BASE DE DATOS 2014-10
PREGUNTAS PLANTEADAS:
Listar id , nombres completos separando el apellido y nombre por el signo + y su job de cada empleado(nombre)que tenga una comisión mayor a 1000 y su fecha de contratación sea mayor a 15/10/2002 y contar y ordernar job_id de forma descendente SELECT employee_id,first_name ll’+’ll last_name AS NombreCompleto, job_id, job_tittle, COUNT(*) FROM employees e NATURAL JOIN jobs j WHERE commission_pct > 1000 AND hire_date > TO_DATE (‘15/10/2002’,’dd/mm/yyy’) GROUP BY job_id, job_tittle ORDER BY job_id desc;
Mostrar los Jobs(id y nombre) donde el numero de empleados sea mayor a 20 SELECT job_id, job_tittle, COUNT(e.employee_id) FROM jobs j NATURAL JOIN employees e WHERE COUNT (e.employee_id) >20 GROUP BY job_id, job_tittle;
Listar región_id y su country cuyo nombre empiece con P y a su vez contar cuantos son SELECT región_id, country_name , count(*) FROM regions r NATURAL JOIN countries c WHERE country_name LIKE ‘P%’ GROUP BY region_id, country_name;
Listar los empleados , su nombre completo,sus comisiones y su nombre de job cuyo salario esté entre 2000 y 5000 , su fecha de contrato sea mayor al 10/10/2000 SELECT employee_id, first_name ll’ ’ll last_name , commission_pct, job_tittle FROM employees e JOIN jobs j ON ( e.job_id = j.job_id) WHERE salary BETWEEN ‘2000’ AND ‘5000’ AND hire_date > TO_DATE(‘10/10/2000’,dd/mm/yyyy’)
Listar el nombre completo de los trabajadores cuyo salario sea mayor a 1000 y menor a 5000 , sacar el promedio de salario y trabajen en el departamento VENT,
SELECT first_name ll’’ll last_name AS NOMBRES, AVG(salary) FROM employees e NATURAL JOIN department d WHERE salary > 1000 AND salary < 5000 AND Department_name = ‘VENT’ GROUP BY NOMBRES;
Ing. Jorge Piminchumo Flores
29-04-2014 4
Universidad Privada Antenor Orrego Facultad de Ingeniería Ingeniería de Computación y Sistemas
Tiempo: 90 minutos Aulas: G501
EXAMEN LABORATORIO DE BASE DE DATOS 2014-10
Mostrar los departamentos (id y nombre )+ el nombre completo de su administrador ,cuyo salario sea 15000 y su numero de teléfono comience con 989
SELECT department_id, department_name , manager_id , first_name ll’ ’ll last _name AS ADMINISTRADOR FROM department d NATURAL JOIN employees e WHERE salary > 15000 AND phone_number LIKE ‘989%’;
Crear la table employees , sabiendo que employee_id es clave primaria
CREATE TABLE EMPLOYEES( Employee_id NUMBER(6,0) NOT NULL PRIMARY KEY, Manager_id NUMBER(6,0), Department_id NUMBER(4,0), First_name VARCHAR2(20), Last_name VARCHAR2(25), Email VARCHAR2(25), Phone_number VARCHAR2(20), Hire_date DATE, Job_id VARCHAR2(10), Salary number(8,2), Commission_pct NUMBER(2,2) );
Ing. Jorge Piminchumo Flores
29-04-2014 5
