Saltar al contenido

Taller SQL: Consultas SQL en Oracle Database 19C usando Oracle Live SQL

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.

Plataforma de entrenamiento Oracle Live SQL

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.

Esquema HR – Oracle

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«.

Esquema base de datos HR

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«.

Sentencia para devolver versión de Oracle Database

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

Otras publicaciones

Publicado enBase de DatosHerramientas