El alcance de este post 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.
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.
|
1
2
|
SELECT* FROMv$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.
SELECTTO_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"FROMhr.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.
|
1
2
3
4
5
6
7
8
|
SELECTj.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"FROMhr.employees e INNERJOINhr.jobs j ONe.job_id=j.job_idGROUPBYj.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.
|
1
2
3
4
5
6
7
|
SELECTj.job_title AS"Puesto",COUNT(e.job_id) AS"Total Empleados", TO_CHAR(ROUND(SUM(e.salary),2),'$999,999.00') AS"Salario Total"FROMhr.employees e INNERJOINhr.jobs j ONe.job_id=j.job_idGROUPBYj.job_titleORDERBY2 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».
|
1
2
3
4
5
|
SELECTTO_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"FROMhr.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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECTDISTINCT(m.manager_id) "Manager",(-- INICIO SUBQUERYSELECTTO_CHAR(ROUND(e.salary,2),'$99,999.00') FROMhr.employees e WHEREe.manager_id=m.manager_id ORDERBYe.salary ASCFETCHNEXT1 ROWSONLY-- FIN SUBQUERY) AS"Salario Mínimo de Empleado"FROMhr.employees mINNERJOINhr.jobs j ONm.job_id=j.job_idWHEREm.manager_id>0 ANDj.min_salary>=6000ORDERBY2 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.
|
1
2
3
4
5
6
|
SELECTemployee_id "Número Empleado",last_name "Apellidos Empleado",TO_CHAR(ROUND(salary,2),'$99,999.00') "Salario Empleado"FROMhr.employeesWHEREsalary>(SELECTAVG(salary) fromhr.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.
|
1
2
3
4
5
6
7
8
9
|
SELECT(first_name || ' '|| last_name) "Nombres Empleado",d.department_name "Departamento",j.job_title "Puesto"FROMhr.employees eINNERJOINhr.departments d ONe.department_id=d.department_idINNERJOINhr.jobs j ONe.job_id=j.job_idWHEREd.location_id=1700ORDERBYe.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.
|
1
2
3
4
5
6
7
8
9
|
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"FROMhr.employees eINNERJOINhr.jobs j ONe.job_id=j.job_idWHEREe.manager_id=100ORDERBYe.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.
|
1
2
3
4
5
6
7
8
9
|
SELECTd.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"FROMhr.employees eINNERJOINhr.departments d ONe.department_id=d.department_idGROUPBYd.department_nameORDERBY2 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.
|
1
2
3
4
5
6
7
8
9
10
11
|
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",(SELECTCOUNT(employee_id) FROMhr.employees WHEREmanager_id = m.employee_id) "Total Empleados"FROMhr.employees mINNERJOINhr.jobs j ONm.job_id=j.job_idINNERJOINhr.departments d ONm.department_id=d.department_idWHEREm.employee_id IN(SELECTDISTINCT(manager_id) FROMhr.employees)ORDERBY5 DESC |
Ejercicio 11
Encuentre a los empleados que tienen más de una rotación laboral.
|
1
2
3
4
5
6
|
SELECT(e.first_name || ' '|| e.last_name) "Empleado",COUNT(jh.employee_id) "Rotaciones"FROMhr.job_history jhINNERJOINhr.employees e ONjh.employee_id=e.employee_idGROUPBY(e.first_name || ' '|| e.last_name)HAVINGCOUNT(jh.employee_id)>1 |
Ejercicio 12
¿Cuál fue el año donde ingresaron más trabajadores?
|
1
2
3
4
5
6
|
SELECTTO_CHAR(hire_date, 'YYYY') "Año Contratación",COUNT(employee_id) "Empleados"FROMhr.employeesGROUPBYTO_CHAR(hire_date, 'YYYY')ORDERBY2 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.
|
1
2
3
4
5
6
7
8
9
10
|
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"FROMhr.employees eINNERJOINhr.jobs j one.job_id=j.job_idWHEREe.salary<=j.min_salary ORe.salary BETWEENj.min_salary AND(j.min_salary+j.min_salary |
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.
|
1
2
3
4
5
6
7
8
|
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"FROMhr.employeesWHEREextract(yearfromhire_date) BETWEEN2003 AND2004ORDERBY3 DESC |
Ejercicio 15
Obtener el salario promedio de los empleados que tengan el cargo de «Stock Manager».
|
1
2
3
4
|
SELECTTO_CHAR(ROUND(AVG(e.salary),2),'$99,999.00') "Salario Promedio Stock Manager"FROMhr.employees eINNERJOINhr.jobs j ONe.job_id=j.job_idWHEREUPPER(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.
|
1
2
3
4
5
6
7
8
9
|
SELECTEXTRACT(YEARFROMhire_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"FROMhr.employeesGROUPBYEXTRACT(YEARFROMhire_date)ORDERBY1 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% .
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECTe.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_idWHERE (CURRENT_DATE - e.hire_date)>5000 ANDe.salary-c.min_salary<500 |
Te esperamos en los siguientes artículos en donde hablaremos mas acerca de estos temas, los cuales hoy en día son de vital importancia en el mundo de la tecnología.
