Bases de Datos
Grado en Ingeniería Informática
Tema 4 - Structured Query Language - Ejercicios Parte 4-1. Ej1) ¿La siguiente sentencia SELECT se ejecuta correctamente? SELECT last_name, job_id, salary AS Sal FROM employees; Ej2) ¿La siguiente sentencia SELECT se ejecuta correctamente? SELECT * FROM job_grades; Ej3) En la siguiente sentencia hay cuatro errores de codificación. ¿Puede identificarlos? SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; Se le ha contratado como programador de SQL para Acme Corporation. Su primera tarea es crear algunos informes según los datos de las tablas de recursos humanos. Ej4) Su primera tarea es determinar la estructura de la tabla DEPARTMENTS y su contenido. Ej5) Determine la estructura de la tabla EMPLOYEES . El departamento de recursos humanos desea que una consulta muestre el apellido, ID de cargo, fecha de contratación e ID de empleado de cada empleado, con el ID de empleado en primer lugar. Propo rcione un alias STARTDATE para la columna HIRE_DATE . Ej6) El departamento de recursos humanos desea una consulta para mostrar todos los ID de cargo únicos de la tabla EMPLOYEES. Ej7) El departamento de recursos humanos desea cabeceras de columna más descriptivas para su informe sobre empleados. Asigne a las cabeceras cab eceras de columna de la sentencia Ej5 los nombres Emp#, Employee, Job y Hire Date, respectivamente. Ej8) El departamento de recursos humanos ha solicitado un informe de todos los empleados y sus ID de cargo. Muestre el apellido concatenado con el ID de cargo (separado por una coma y un espacio) y asigne a la columna el nombre Employee and Title. Ej9) Para familiarizarse con los datos de la tabla EMPLOYEES, cree una consulta para mostrar todos los datos de dicha tabla. Separe cada salida de la columna por una coma. Asigne a la columna el título THE_OUTPUT .
Tema 4 - Ejercici Ejercicios os
Página 1
Bases de Datos
Grado en Ingeniería Informática
Parte 4-2. El departamento de recursos humanos sigue necesitando su ayuda para crear algunas consultas. Ej1) Debido a problemas presupuestarios, el departamento de recursos humanos necesita un informe que muestre el apellido y el salario de los empleados que ganan más de 12.000 dólares. Ej2) Cree un informe que muestre el apellido y el número de departamento para el número de empleado 176. Ej3) El departamento de recursos humanos necesita encontrar los empleados con salarios tanto altos como bajos. Modifique la sentencia Ej1 para mostrar el apellido y el salario de cualquier empleado cuyo salario no esté entre 5.000 y 12.000 dólares. Ej4) Cree un informe para mostrar el apellido, ID de cargo y fecha de contratación de los empleados cuyos apellidos sean Matos y Taylor. Ordene la consulta en orden ascendente por fecha de contratación. Ej5) Muestre el apellido y el ID de departamento de todos los empleados de los departamentos 20 o 50 en orden alfabético ascendente por nombre. Ej6) Modifique la sentencia Ej3 para mostrar el apellido y el salario de los empleados que ganan entre 5.000 y 12.000 dólares y están en el departamento 20 o 50. Etiquete las columnas Employee y Monthly Salary, respectivamente. Ej7) El departamento de recursos humanos necesita un informe que muestre el apellido y la fecha de contratación de todos los empleados contratados durante el año 1994. Ej8) Cree un informe para mostrar el apellido y el puesto de todos los empleados que no tienen un supervisor. Ej9) Cree un informe para mostrar el apellido, salario y comisión de todos los empleados que perciben comisiones. Ordene los datos en orden descendente de salario y comisiones. Utilice la posición numérica de la columna en la cláusula ORDER BY. Ej10) Muestre todos los apellidos de los empleados cuya tercera letra sea una "a". Ej11) Muestre los apellidos de los empleados que tengan una "a" y una "e" en su apellido. Ej12) Muestre el apellido, cargo y salario de todos los empleados que sean vendedores (SA_REP) u oficinistas en el departamento de stock (ST_CLERK) y cuyos salarios no sean iguales que 2.500, 3.500 ó 7.000 dólares. Ej13) Modifique la sentencia Ej6 para mostrar el apellido, salario y comisión de todos los empleados cuya comisión sea del 20%.
Tema 4 - Ejercicios
Página 2
Bases de Datos
Grado en Ingeniería Informática
Parte 4-3. Ej1) Escriba una consulta para mostrar la fecha del sistema. Etiquete la columna como Date. Ej2) El departamento de recursos humanos necesita un informe que muestre el número de empleado, apellido, salario y salario aumentado en un 15,5% (expresado como número entero) para cada empleado. Etiquete la columna como New Salary. Ej3) Modifique la consulta Ej2 para agregar una columna que reste el salario antiguo del nuevo. Etiquete la columna como Increase. Ej4) Escriba una consulta que muestre el apellido (con la primera letra en mayúsculas y el resto en minúsculas) y la longitud del apellido de todos los empleados cuyos nombres empiecen por las letras "J", "A" o "M". Proporcione a cada columna una etiqueta adecuada. Ordene los resultados por el apellido de los empleados. Ej5) El departamento de recursos humanos desea buscar la duración del contrato de cada empleado. Para cada empleado, muestre el apellido y calcule el número de meses entre el día de hoy y la fecha de contratación del empleado. Etiquete la columna como MONTHS_WORKED . Ordene los resultados por el número de meses durante los que ha trabajado. Redondee el número de meses hasta el número entero más cercano. Ej6) Cree una consulta para mostrar el apellido y el salario de todos los empleados. Formatee el salario para que tenga 15 caracteres de longitud y tenga un relleno hacia la izquierda con el símbolo $. Etiquete la columna como SALARY. Ej7) Cree una consulta que muestre los primeros ocho caracteres de los apellidos de los empleados y que indique las cantidades de sus salarios con asteriscos. Cada asterisco significa mil dólares. Ordene los datos en orden descendente de salarios. Etiquete la columna como EMPLOYEES_AND_THEIR_SALARIES . Ej8) Cree una consulta para mostrar el apellido y el número de semanas durante las que han trabajado todos los empleados del departamento 90. Etiquete la columna de número de semanas como TENURE. Trunque el valor del número de semana en 0 decimales. Muestre los registros en orden descendente de antigüedad del empleado.
Tema 4 - Ejercicios
Página 3
Bases de Datos
Grado en Ingeniería Informática
Parte 4-4. Ej1) Cree un informe que produzca los siguientes resultados para cada empleado:
earns monthly but wants <3 veces el salario.>. Etiquete la columna como Dream Salaries. Ej2) Muestre el apellido, fecha de contratación y fecha de revisión de salario de cada empleado, que es el primer lunes después de seis meses de contrato. Etiquete la columna como REVIEW. Formatee las fechas para que aparezcan en un formato similar a “Lunes treinta y uno de julio de 2000”. Ej3) Muestre el apellido, fecha de contratación y día de la semana en el que empezó a trabajar el empleado. Etiquete la columna como DAY. Ordene los resultados por el día de la semana, empezando por el lunes. Ej4) Cree una consulta que muestre los apellidos y comisiones de los empleados. Si un empleado no obtiene ninguna comisión, indique "No Commission". Etiquete la columna como COMM. Ej5) Con la función DECODE, escriba una consulta que muestre el grado de todos los empleados según el valor de la columna JOB_ID, utilizando los siguientes datos: Cargo Grado AD_PRES ST_MAN IT_PROG SA_REP ST_CLERK Otro
A B C D E 0
6) Vuelva a escribir la sentencia Ej5 utilizando la sintaxis CASE.
Tema 4 - Ejercicios
Página 4
Bases de Datos
Grado en Ingeniería Informática
Parte 4-5. Ej1) ¿Las funciones de grupo funcionan en varias filas para producir un resultado por grupo? Ej2) ¿Las funciones de grupo incluyen valores nulos en los cálculos? Ej3) ¿La cláusula WHERE restringe las filas antes de incluirlas en un cálculo de grupo? El departamento de recursos humanos necesita los siguientes informes: Ej4) Encontrar el valor más alto, el valor más bajo, la suma y la media del salario de todos los empleados. Etiquete las columnas como Maximum, Minimum, Sum y Average, respectivamente. Redondee los resultados al número entero más cercano. Ej5) Modifique la consulta Ej4 para mostrar el valor mínimo, el valor máximo, la suma y la media del salario de cada tipo de cargo. Ej6) Escriba una consulta para mostrar el número de personas con el mismo cargo. Ej7) Determine el número de gestores sin enumerarlos en una lista. Etiquete la columna Number of Managers. Para ello, utilice la columna MANAGER_ID. Ej8) Busque la diferencia entre los salarios más altos y más bajos. Etiquete la columna como DIFFERENCE. Ej9) Cree un informe para mostrar el número de gestor y el salario del empleado con menor sueldo de ese gestor. Excluya a cualquier trabajador del que desconozca su gestor. Excluya cualquier grupo en el que el salario mínimo sea 6.000 dólares o menos. Ordene la salida en orden descendente de salarios. Ej10) Cree una consulta para mostrar el número total de empleados y, de ese total, el número de empleados contratados en 1995, 1996, 1997 y 1998. Cree las cabeceras de columna adecuadas. Ej11) Cree una consulta de matriz para mostrar el cargo, el salario de ese cargo según el número de departamento y el salario total del cargo para los departamentos 20, 50, 80 y 90, proporcionando a cada columna una cabecera adecuada.
Tema 4 - Ejercicios
Página 5
Bases de Datos
Grado en Ingeniería Informática
Parte 4-6. Ej1) Escriba una consulta para que el departamento de recursos humanos genere las direcciones de todos los departamentos. Utilice las tablas LOCATIONS y COUNTRIES. Muestre el ID de ubicación, dirección, ciudad, estado o provincia y país en la salida. Utilice NATURAL JOIN para producir los resultados. Ej2) El departamento de recursos humanos necesita un informe sólo de los empleados con los departamentos correspondientes. Escriba una consulta para mostrar el apellido, número y nombre de departamento de estos empleados. Ej3) El departamento de recursos humanos necesita un informe de todos los empleados de Toronto. Muestre el apellido, cargo, número y nombre de departamento de todos los empleados que trabajan en Toronto. Ej4) Cree un informe para mostrar el apellido y número de empleado junto con el apellido y número de gestor del empleado. Etiquete las columnas como Employee, Emp#, Manager y Mgr#, respectivamente. Ej5) Modifique Ej4 para mostrar todos los empleados, incluido King, que no tienen gestor. Ordene los resultados por número de empleado. Ej6) Cree un informe del departamento de recursos humanos que muestre los apellidos y números de empleado y todos los empleados que trabajen en el mismo departamento como un empleado determinado. Proporcione a cada columna una etiqueta adecuada. Ej7) El departamento de recursos humanos desea determinar los nombres de todos los empleados contratados después de Davies. Cree una consulta para mostrar el nombre y la fecha de contratación de cualquier empleado contratado después del empleado Davies. Ej8) El departamento de recursos humanos necesita buscar el nombre y la fecha de contratación de todos los empleados contratados antes que sus gestores, junto con el nombre y fecha de contratación del gestor.
Tema 4 - Ejercicios
Página 6
Bases de Datos
Grado en Ingeniería Informática
Parte 4-6bis. Resuelva los ejercicios utilizando la sintaxis de unión en Oracle . Ej1) Escriba una consulta para que el departamento de recursos humanos genere las direcciones de todos los departamentos. Utilice las tablas LOCATIONS y COUNTRIES. Muestre el ID de ubicación, dirección, ciudad, estado o provincia y país en la salida. Ej2) El departamento de recursos humanos necesita un informe de todos los empleados. Escriba una consulta para mostrar el apellido, número y nombre de departamento de todos los empleados. Ej3) El departamento de recursos humanos necesita un informe de todos los empleados de Toronto. Muestre el apellido, cargo, número y nombre de departamento de todos los empleados que trabajan en Toronto. Ej4) Cree un informe para mostrar el apellido y número de empleado junto con el apellid o y número de gestor de sus gestores. Etiquete las columnas como Employee, Emp#, Manager y Mgr#, respectivamente. Ej5) Modifique Ej4 para mostrar todos los empleados, incluido King, que no tienen gestor. Ordene los resultados por número de empleado. Ej6) Cree un informe del departamento de recursos humanos que muestre los apellidos y números de empleado y todos los empleados que trabajen en el mismo departamento como un empleado determinado. Proporcione a cada columna una etiqueta adecuada. Ej7) El departamento de recursos humanos desea determinar los nombres de todos los empleados contratados después de Davies. Cree una consulta para mostrar el nombre y la fecha de contratación de cualquier empleado contratado después del empleado Davies. Ej8) El departamento de recursos humanos necesita buscar el nombre y la fecha de contratación de todos los empleados contratados antes que sus gestores, junto con el nombre y fecha de contratación del gestor.
Tema 4 - Ejercicios
Página 7
Bases de Datos
Grado en Ingeniería Informática
Parte 4-7. Ej1) Cree un informe que muestre el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio. Ordene los resultados en orden ascendente de salario. Ej2) Escriba una consulta que muestre el número de empleado y el apellido de todos los empleados que trabajan en un departamento con un empleado cuyo apellido contiene una "u". Ej3) El departamento de recursos humanos necesita un informe que muestre el apellido, número de departamento e ID de cargo de todos los empleados cuyo ID de ubicación de departamento sea 1700. Ej5) Cree un informe para el departamento de recursos humanos que muestre el número de departamento, apellido e ID de cargo de cada empleado del departamento Executive. Ej6) Cree un informe que muestre una lista de todos los empleados cuyo salario sea mayor que el salario de los empleados del departamento 60. Ej7) Modifique la consulta Ej2 para mostrar el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio y que trabajan en un departamento con cualquier empleado cuyo apellido contiene una "u".
Tema 4 - Ejercicios
Página 8
Bases de Datos
Grado en Ingeniería Informática
Parte 4-8. Ej1) El departamento de recursos humanos necesita una lista de ID de departamento que no contienen el ID de cargo ST_CLERK . Utilice los operadores de definición para crear este informe. Ej2) El departamento de recursos humanos necesita una lista de países que no tienen ningún departamento. Muestre el ID de país y el nombre de los países. Utilice los operadores de definición para crear este informe. Ej3) Cree una lista de cargos para los departamentos 10, 50 y 20, en ese orden. Muestre los ID de cargo y departamento mediante los operadores de definición. Ej4) Cree un informe que muestre los ID de empleado y de cargo de los empleados que actualmente tienen un puesto que es el mismo que tenían cuando fueron contratados (es decir, han cambiado de cargo pero ahora han vuelto a su puesto original). Ej5) El departamento de recursos humanos necesita un informe con las siguientes especificaciones: ID de departamento y apellido de todos los empleados de la tabla EMPLOYEES, independientemente de si pertenecen o no a un departamento. El nombre e ID de departamento de todos los departamentos de la tabla DEPARTMENTS , independientemente de si hay o no empleados trabajando en dichos departamentos. Para ello, escriba una consulta compuesta.
Tema 4 - Ejercicios
Página 9
Bases de Datos
Grado en Ingeniería Informática
Parte 4-9. Para realizar estos ejercicios necesitará utilizar la tabla MY_EMPLOYEE. Ejecute el siguiente código para hacerlo: CREATE TABLE my_employee ( id NUMBER(4), last_name VARCHAR2(25), first_name VARCHAR2(25), userid VARCHAR2(8), salary NUMBER(8,2), PRIMARY KEY(id) ); Ej1) Describa la estructura de la tabla MY_EMPLOYEE para identificar los nombres de las columnas. Ej2) Cree una sentencia INSERT para agregar la primera fila de datos a la tabla MY_EMPLOYEE a partir de los siguientes datos de ejemplo. No muestre la lista de columnas en la cláusula INSERT. No introduzca aún todas las filas.
Ej3) Rellene la tabla MY_EMPLOYEE con la segunda fila de datos de ejemplo de la lista anterior. En esta ocasión, muestre explícitamente la lista de columnas en la cláusula INSERT. Ej4) Escriba una sentencia INSERT en un archivo de script reutilizable para cargar las filas restantes en la tabla MY_EMPLOYEE. El script se debe solicitar para todas las columnas (ID, LAST_NAME , FIRST_NAME, USERID y SALARY). Ej5) Cambie el apellido del empleado 3 a Drexler. Ej6) Cambie el salario a 1.000 dólares para todos los empleados con un salario inferior a 900. Ej7) Suprima Betty Dancs de la tabla MY_EMPLOYEE.
Tema 4 - Ejercicios
Página 10
Bases de Datos
Grado en Ingeniería Informática
Ej8) Modifique el script Ej4 de forma que USERID se genere automáticamente concatenando la primera letra del primer nombre y de los primeros siete caracteres del apellido. El USERID generado debe estar en minúscula. Por lo tanto, no se debe solicitar el script para el USERID.
Tema 4 - Ejercicios
Página 11
Bases de Datos
Grado en Ingeniería Informática
Parte 4-10. Ej1) Cree la tabla DEPT según el siguiente gráfico de instancias de tabla.
Ej2) Rellene la tabla DEPT con datos de la tabla DEPARTMENTS . Incluya sólo las columnas que necesite. Ej3) Cree la tabla EMP según el siguiente gráfico de instancias de tabla.
Ej4) Cree la tabla EMPLOYEES2 según la estructura de la tabla EMPLOYEES. Incluya sólo las columnas EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY y DEPARTMENT_ID. Asigne a las columnas de la tabla los nombres ID, FIRST_NAME , LAST_NAME , SALARY y DEPT_ID, respectivamente. Ej5) Borre la tabla EMPLOYEES2 .
Tema 4 - Ejercicios
Página 12
Bases de Datos
Grado en Ingeniería Informática
Parte 4-11. Ej1) El personal del departamento de recursos humanos desea ocultar algunos de los datos de la tabla EMPLOYEES. Cree una vista denominada EMPLOYEES_VU basada en los números y los apellidos de los empleados y en los números de departamento de la tabla EMPLOYEES. La cabecera del nombre de empleado debe ser EMPLOYEE. Ej2) Con la vista EMPLOYEES_VU , escriba una consulta para el departamento de recursos humanos para visualizar todos los nombres de empleados y números de departamento. Ej3) El departamento 50 necesita acceso a los datos de los empleados. Cree una vista con el nombre DEPT50 que contenga los números y apellidos de los empleados y los números de departamento de todos los empleados del departamento 50. Se le ha pedido que etiquete las columnas de la vista como EMPNO, EMPLOYEE y DEPTNO. Por motivos de seguridad, no permita la reasignación de un empleado a otro departamento a través de la vista. Ej4) Cree un sinónimo para la tabla EMPLOYEES. Llámelo EMP.
Tema 4 - Ejercicios
Página 13
