El alcance de este taller será elaborar las sentencias SQL que respondan a las consultas de información (ejercicios propuestos) sobre el esquema de base de datos HR (Human Resources) en un servidor Oracle Database 19C.
Utilizaremos la plataforma cloud de Oracle Live SQL, por lo tanto no será necesario que instale el software de administración Oracle Database 19C.
Nota 1: Si no tienes una cuenta de usuario en esta plataforma de entrenamiento de Oracle, puedas crearte una cuenta aquí.
Nota 2: Se requiere conocimiento básico sobre diseño de base de datos y SQL para poner en práctica los ejercicios de consulta SQL sobre Oracle Database 19C.
Modelo Lógico de una Base de Datos
Conocer el modelo lógico de una base de datos le ayudará a comprender como se almacena la información. El siguiente gráfico muestra las entidades y relaciones de la base de datos Human Resources (HR) utilizada por Oracle en sus textos de entrenamiento.
También puedes consultar información del modelo lógico del esquema HR en la plataforma Oracle Live, ingresando a la opción «Schema«, y seleccionando el esquema «Human Resources«.
Cómo ejecutar sentencias SQL en Oracle Live
Para escribir y ejecutar sentencias SQL o PL/SQL en Oracle Live, debe ingresar a la opción «SQL Worksheet» del menú de opciones que aparece en la parte izquierda de la aplicación, luego ingresar la sentencia SQL en el área de trabajo (fondo blanco) y finalmente para ejecutar la sentencia SQL deberá hacer clic en el botón «Run«.
El resultado de la ejecución lo podrá visualizar en la parte inferior de la pantalla. En la imagen se muestra la sentencia para devolver la versión del Oracle Database.
SELECT * FROM v$version;
-- RESULTADO: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
EJERCICIOS SQL
Ejercicio 01
Muestre el salario más alto, más bajo, salario total de la planilla y salario promedio de la tabla empleados. Etiquete las columnas como «Salario Máximo», «Salario Mínimo», «Salario Planilla» y «Salario Promedio» respectivamente. Redondee los resultados al valor entero más cercano, con un formato numérico para la moneda de dólares.
SELECT
TO_CHAR(ROUND(MAX(salary)),'$99,999.00') AS "Salario Máximo",
TO_CHAR(ROUND(MIN(salary)),'$99,999.00') AS "Salario Mínimo",
TO_CHAR(ROUND(SUM(salary)),'$999,999.00') AS "Salario Planilla",
TO_CHAR(ROUND(AVG(salary)),'$99,999.00') AS "Salario Promedio"
FROM hr.employees;
Ejercicio 02
Muestre el salario más alto, más bajo, salario total y salario promedio por cada tipo de puesto que se tiene en la organización. Etiquete las columnas como «Puesto», «Salario Máximo», «Salario Mínimo», «Salario Total» y «Salario Promedio» respectivamente. Redondee los resultados con dos cifras decimales, con un formato numérico para la moneda de dólares.
SELECT
j.job_title AS "Puesto",
TO_CHAR(ROUND(MAX(e.salary),2),'$99,999.00') AS "Salario Máximo",
TO_CHAR(ROUND(MIN(e.salary),2),'$99,999.00') AS "Salario Mínimo",
TO_CHAR(ROUND(SUM(e.salary),2),'$999,999.00') AS "Salario Total",
TO_CHAR(ROUND(AVG(e.salary),2),'$99,999.00') AS "Salario Promedio"
FROM hr.employees e INNER JOIN hr.jobs j ON e.job_id=j.job_id
GROUP BY j.job_title;
Ejercicio 03
Escriba una consulta que muestre la cantidad de personas que tienen el mismo puesto y a cuanto asciende la suma total de sus salarios. El resultado debe mostrarse en orden descendente por el puesto que tiene la mayor cantidad de empleados.
SELECT
j.job_title AS "Puesto",
COUNT(e.job_id) AS "Total Empleados",
TO_CHAR(ROUND(SUM(e.salary),2),'$999,999.00') AS "Salario Total"
FROM hr.employees e INNER JOIN hr.jobs j ON e.job_id=j.job_id
GROUP BY j.job_title
ORDER BY 2 DESC
Ejercicio 04
Escriba una consulta para mostrar la diferencia entre el salario más alto y el salario más bajo. Etiquete la columna como «Diferencia Salarial».
SELECT
TO_CHAR(ROUND(MAX(salary),2),'$99,999.00') AS "Salario Máximo",
TO_CHAR(ROUND(MIN(salary),2),'$99,999.00') AS "Salario Mínimo",
TO_CHAR(ROUND(MAX(salary) - MIN(salary),2),'$99,999.00') AS "Diferencia Salarial"
FROM hr.employees
Ejercicio 05
Muestre el ID del Manager y el salario del empleado con menos paga para ese manager. Excluya a cualquier empleado cuyo manager sea desconocido. Excluya cualquier puesto donde el salario mínimo sea menor que $6,000. Ordene la salida en orden descendente por salario.
SELECT
DISTINCT(m.manager_id) "Manager",
(
-- INICIO SUBQUERY
SELECT TO_CHAR(ROUND(e.salary,2),'$99,999.00')
FROM hr.employees e
WHERE e.manager_id=m.manager_id
ORDER BY e.salary ASC
FETCH NEXT 1 ROWS ONLY
-- FIN SUBQUERY
) AS "Salario Mínimo de Empleado"
FROM hr.employees m
INNER JOIN hr.jobs j ON m.job_id=j.job_id
WHERE m.manager_id>0 AND j.min_salary>=6000
ORDER BY 2 DESC
Ejercicio 06
Escriba una consulta para mostrar el número de empleado y apellidos de todos los empleados que ganan por encima del salario promedio. Ordene los resultados en orden descendente de salario.
SELECT
employee_id "Número Empleado",
last_name "Apellidos Empleado",
TO_CHAR(ROUND(salary,2),'$99,999.00') "Salario Empleado"
FROM hr.employees
WHERE salary>(SELECT AVG(salary) from hr.employees)
Ejercicio 07
Muestre los nombres y apellidos (es una sola columna), nombre del departamento y el nombre del puesto de todos los empleados cuyo código de ubicación de departamento (LOCATION_ID) es 1700. El resultado del reporte debe mostrarse en orden ascendente por el apellido del empleado.
SELECT
(first_name || ' ' || last_name) "Nombres Empleado",
d.department_name "Departamento",
j.job_title "Puesto"
FROM hr.employees e
INNER JOIN hr.departments d ON e.department_id=d.department_id
INNER JOIN hr.jobs j ON e.job_id=j.job_id
WHERE d.location_id=1700
ORDER BY e.last_name ASC
Ejercicio 08
Elabore un reporte que contenga los nombres, apellidos, puesto, salario y fecha de ingreso de los empleados que le reportan a King, ordenados por su fecha de ingreso a la empresa de manera descendente.
SELECT
(e.first_name || ' ' || e.last_name) "Empleado",
j.job_title "Puesto",
TO_CHAR(ROUND(e.salary,2),'$99,999.00') "Salario",
e.hire_date "Fecha Ingreso"
FROM hr.employees e
INNER JOIN hr.jobs j ON e.job_id=j.job_id
WHERE e.manager_id=100
ORDER BY e.hire_date DESC
Ejercicio 09
Generar un reporte que muestre la cantidad de empleados y el salario total de cada departamento de la empresa. Ordenar la información por el departamento que tiene más empleados a menos.
SELECT
d.department_name "Departamento",
COUNT(e.employee_id) "Total Empleados",
TO_CHAR(ROUND(SUM(e.salary),2),'$999,999.00') "Salario Total",
TO_CHAR(ROUND(AVG(e.salary),2),'$999,999.00') "Salario Promedio"
FROM hr.employees e
INNER JOIN hr.departments d ON e.department_id=d.department_id
GROUP BY d.department_name
ORDER BY 2 DESC
Ejercicio 10
Muestre el nombre del Manager, nombre del puesto, nombre de departamento, salario y cantidad de trabajadores que tiene a su cargo. Ordenar según la cantidad de trabajadores en orden descendente.
SELECT
(m.first_name || ' ' || m.last_name) "Manager",
j.job_title "Puesto",
d.department_name "Departamento",
TO_CHAR(ROUND(m.salary,2),'$999,999.00') "Salario",
(SELECT COUNT(employee_id) FROM hr.employees WHERE manager_id = m.employee_id) "Total Empleados"
FROM hr.employees m
INNER JOIN hr.jobs j ON m.job_id=j.job_id
INNER JOIN hr.departments d ON m.department_id=d.department_id
WHERE m.employee_id IN (SELECT DISTINCT(manager_id) FROM hr.employees)
ORDER BY 5 DESC
Ejercicio 11
Encuentre a los empleados que tienen más de una rotación laboral.
SELECT (e.first_name || ' ' || e.last_name) "Empleado",
COUNT(jh.employee_id) "Rotaciones"
FROM hr.job_history jh
INNER JOIN hr.employees e ON jh.employee_id=e.employee_id
GROUP BY (e.first_name || ' ' || e.last_name)
HAVING COUNT(jh.employee_id)>1
Ejercicio 12
¿Cuál fue el año donde ingresaron más trabajadores?
SELECT
TO_CHAR(hire_date, 'YYYY') "Año Contratación",
COUNT(employee_id) "Empleados"
FROM hr.employees
GROUP BY TO_CHAR(hire_date, 'YYYY')
ORDER BY 2 DESC
Ejercicio 13
Mostrar los nombres, apellidos, salario actual y el nuevo salario equivalente al 8% adicional de su salario actual de los empleados cuyo salario actual sea igual, menor o mayor hasta por 5% del salario mínimo del puesto. Ordenar resultado por el sueldo actual más alto.
SELECT
(e.first_name || ' ' || e.last_name) "Empleado",
TO_CHAR(ROUND(e.salary, 2),'$99,999.00') "Salario Actual",
TO_CHAR(ROUND(j.min_salary,2),'$99,999.00') "Salario Mínimo Puesto",
TO_CHAR(ROUND(j.max_salary,2), '$99,999.00') "Salario Máximo Puesto",
TO_CHAR(ROUND((e.salary + e.salary*0.08),2),'$99,999.00') "Nuevo Salario"
FROM hr.employees e
INNER JOIN hr.jobs j on e.job_id=j.job_id
WHERE e.salary<=j.min_salary OR
e.salary BETWEEN j.min_salary AND (j.min_salary+j.min_salary*0.05)
Ejercicio 14
Generar un reporte incremente en un 20% el salario de los empleados que ingresaron a la empresa entre el año 2003 a 2005.
SELECT
(first_name || ' ' || last_name) "Empleado",
hire_date "Fecha Ingreso",
TO_CHAR(ROUND(salary, 2),'$99,999.00') "Salario Actual",
TO_CHAR(ROUND((salary + salary*0.2),2),'$99,999.00') "Nuevo Salario"
FROM hr.employees
WHERE extract(year from hire_date) BETWEEN 2003 AND 2004
ORDER BY 3 DESC
Ejercicio 15
Obtener el salario promedio de los empleados que tengan el cargo de «Stock Manager».
SELECT TO_CHAR(ROUND(AVG(e.salary),2),'$99,999.00') "Salario Promedio Stock Manager"
FROM hr.employees e
INNER JOIN hr.jobs j ON e.job_id=j.job_id
WHERE UPPER(j.job_title)='STOCK MANAGER'
Ejercicio 16
Generar un reporte que muestre la cantidad de empleados que ingresaron en cada año, el costo del salario mensual, anual y el salario promedio por cada año.
SELECT
EXTRACT(YEAR FROM hire_date) "Año",
COUNT(employee_id) "Empleados que ingresaron",
TO_CHAR(ROUND(SUM(salary),2),'$999,999.00') "Planilla Mensual",
TO_CHAR(ROUND(SUM(salary)*12,2),'$9,999,999.00') "Planilla Anual",
TO_CHAR(ROUND(AVG(salary),2),'$99,999.00') "Salario Promedio Mensual"
FROM hr.employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY 1 ASC
Ejercicio 17
Generar un reporte que muestre los nombres y apellidos, salario del empleado, el salario mínimo según el cargo del empleado, de aquellos cuya diferencia del salario y el salario mínimo sea menor a S/. 500.00 y además que indique si merece un aumento sólo si la diferencia entre hoy y la fecha de contratación es mayor a 5,000 días. El mismo reporte debe mostrar el nuevo salario que corresponde a un aumento del 15% .
SELECT
e.first_name "Nombres",
e.last_name "Apellidos",
e.hire_date "Fecha Ingreso",
TO_CHAR(ROUND(e.salary,2),'$99,999.00') "Salario",
TO_CHAR(ROUND(c.min_salary,2),'$99,999.00') "Salario Mínimo",
TO_CHAR(ROUND((e.salary*1.15),2),'$99,999.00') "Nuevo Salario"
FROM hr.employees e
INNER JOIN hr.jobs c ON e.job_id=c.job_id
WHERE (CURRENT_DATE - e.hire_date)>5000 AND
e.salary-c.min_salary<500
Publicaciones que pueden interesarte
- Taller PL/SQL: Bloques Anónimos, Procedimientos Almacenados, Funciones y Triggers sobre una base de datos Oracle 19C.
- Buenas prácticas para administrar la base de datos de producción
Completa el siguiente formulario para recibir noticias y publicaciones a tu correo electrónico.