La tarea consiste en realizar consultas en SQL sobre el Sistema de Información descrito a continuación:

• Una empresa está organizada en Departamentos.
• Cada departamento se ubica físicamente en un Centro de trabajo, pudiendo contener un Centro más de un Departamento.
• Cada departamento tiene un director que es un empleado de la empresa y puede desempeñar el cargo en Propiedad o en Funciones.
• Cada departamento depende de otro departamento, excepto el de más alto nivel, que no depende de ninguno.
• Cada departamento tiene a su cargo a varios departamentos, excepto los departamentos de más bajo nivel, de los cuales no depende ninguno.
• Cada empleado tiene una categoría que lleva asociada un complemento de Julio y otro de Navidad, y pertenece a un departamento.
• De los Centros interesa almacenar el código, que es único para cada Centro, Direccion y localidad.
• De los Departamentos interesa almacenar el código, que es único para cada dpto, y la siguiente información: Denominacion, Centro en el que está ubicado, departamento del que depende, en el caso de dependa e alugno, Empleado jefe, Tipo de empleado jefe (si es en propiedad o en funciones, y presupuesto asignado.
• De los Empleados interesa almacenar el código, que es único para cada empleado, y la siguiente información nombre, ap1, ape2, direccion, localidad, telef, Departamento en el que trabaja, categoría que tiene, comision (solo la tendrán algunos empleados, otros tendrán nulos) , y salario
• De las Categorías interesa almacenar el código, que es único para cada Categoría, la denominación de categoría y el importe de paga extra de julio y el importe de septiembre.

En el script BD04_Tarea_creadptoyCarga.sql, contenido en el PDF del enlace, tienes las sentencias para crear el usuario c##dpto con clave dpto y las tablas y añadir datos para probar las sentencias SQL que se piden en la tarea.

PDF con sentencias script creadptoyCarga.sql

Crea un  script con las sentencias del pdf y ejecútalo. El script conecta como administrador con “sys as sysdba”, crea el usuario c##dpto,  las tablas e inserta contenido en ellas.

Obtén el diagrama del modelo relacional siguiendo los pasos del enlace

Obtención del modelo entidad relación a partir de las tablas con SQLDeveloper

para entender bien el esquema, sus tablas y la relación entre ellas.

Como tarea realiza los siguientes requirimientos en SQL

1. Obtener los nombres y salarios de los empleados con más de 1000 euros de salario por orden alfabético.
2. Obtener el nombre de los empleados cuya comisión es superior al 20% de su salario.
3. Obtener el código de empleado, código de departamento, nombre y sueldo total en pesetas de aquellos empleados cuyo sueldo total (salario más comisión) supera los 1800 euros. Presentarlos ordenados por código de departamento y dentro de éstos por orden alfabético.
4. Obtener por orden alfabético los nombres de empleados cuyo salario igualen o superen en más de un 5% al salario de la empleada ‘MARIA JAZMIN’.
5. Obtener una listado ordenado por años en la empresa con los nombres, y apellidos de los empleados y los años de antigüedad en la empresa
6. Obtener el nombre de los empleados que trabajan en un departamento con presupuesto superior a 50.000 euros. Hay que usar predicado cuantificado
7. Obtener los nombres y apellidos de empleados que más cobran en la empresa.Considerar el salario más la comisión,
8. Obtener en orden alfabético los nombres de empleado cuyo salario es inferior al mínimo de los empleados del departamento 1.
9. Obtener los nombre de empleados que trabajan en el departamento del cuál es jefe el empleado con código 1.
10. Obtener los nombres de los empleados cuyo primer apellido empiece por las letras p, q, r, s.
11. Obtener los empleados cuyo nombre de pila contenga el nombre JUAN.
12. Obtener los nombres de los empleados que viven en ciudades en las que hay algún centro de trabajo
13. Obtener el nombre del jefe de departamento que tiene mayor salario de entre los jefes de departamento.
14. Obtener en orden alfabético los salarios y nombres de los empleados cuyo salario sea superior al 60% del máximo salario de la empresa.
15. Obtener en cuántas ciudades distintas viven los empleados
16. El nombre y apellidos del empleado que más salario cobra
17. Obtener las localidades y número de empleados de aquellas en las que viven más de 3 empleados
18. Obtener para cada departamento cuántos empleados trabajan, la suma de sus salarios y la suma de sus comisiones para aquellos dep artamen to en los que hay algún empleado cuyo salario es superior a 1700 euros.
19. Obtener el departamento que más empleados tiene
20. Obtener los nombres de todos los centros y los departamentos que se ubican en cada uno,así como aquellos centros que no tienen departamentos.
21. Obtener el nombre del departamento de más alto nivel, es decir, aquel que no depende de ningún otro.
22. Obtener todos los departamentos existentes en la empresa y los empleados (si los tiene) que pertenecen a él.
23. Obtener un listado en el que aparezcan todos los departamentos existentes y el departamento del cual depende,si depende de alguno.
24. Obtener un listado ordenado alfabéticamente donde aparezcan los nombres de los empleados y a continuación el literal "tiene comisión" si la tiene,y "no tiene comisión" si no la tiene.
25.  Obtener un listado de las localidades en las que hay centros y no vive ningún empleado ordenado alfabéticamente.
26. Obtener un listado de las localidades en las que hay centros y además vive al menos un empleado ordenado alfabéticamente.
27. Esta cuestión puntúa por 2. Se desea dar una gratificación por navidades en función de la antigüedad en la empresa siguiendo estas pautas:
    • Si lleva entre 1 y 5 años, se le dará 100 euros
    • Si lleva entre 6 y 10 años, se le dará 50 euros por año
    • Si lleva entre 11 y 20 años, se le dará 70 euros por año
    • Si lleva más de 21 años, se le dará 100 euros por año
28. Obtener un listado de los empleados,ordenado alfabéticamente,indicando cuánto le corresponde de gratificación.
29. Obtener a los nombres, apellidos de los empleados que no son jefes de departamento.

El script del enunciado para crear el esquema y probar las sentencias

Oracle xe 18c

SQLDeveloper opcional

Es aconsejable que pruebes todos los ejercicios. Puedes escribirlos en la hoja de trabajo de SQLDeveloper o directamente en SQLPlus. Comprueba que los resultados son los correctos en función de los datos.

Una vez realizada la tarea elaborarás un único documento donde figuren las respuestas correspondientes. El envío se realizará a través de la plataforma de la forma establecida para ello, y el archivo se nombrará siguiendo las siguientes pautas:

apellido1_apellido2_nombre_SIGxx_Tarea

Asegúrate que el nombre no contenga la letra ñ, tildes ni caracteres especiales extraños. Así por ejemplo la alumna Begoña Sánchez Mañas para la cuarta unidad del MP de BD, debería nombrar esta tarea como...

sanchez_manas_begona_BD04_Tarea