SQL · STUDENTS y EMPLOYEE

Esta serie mantiene la mente fresca resolviendo, uno a uno, los ejercicios de HackerRank. Cada entrada toma un tema concreto y lo agota; todas las consultas están en MySQL salvo donde se indique.

Te invito a intentar cada ejercicio antes de leer la solución.

Dos tablas nuevas y ejercicios con más cuerpo: ordenar por varios criterios, clasificar con CASE y corregir cifras mal registradas.

Student

Higher Than 75 Marks

Query the Name of any student in STUDENTS who scored higher than 75 Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Input Format

The STUDENTS table is described as follows:

(a-z) letters.

Sample Input

Ashley
Julia
Belvet

Sample Output

Only Ashley, Julia, and Belvet have Marks > 75. If you look at the last three characters of each of their names, there are no duplicates and 'ley' < 'lia' < 'vet'.

Solución

Pasamos a una nueva tabla. Refrescando lo que vimos anteriormente, ordenamos por los últimos 3 caracteres del nombre de forma ascendente y, en caso de empate, por ID también ascendente. Además, filtramos los registros donde la columna MARKS sea mayor a 75.

SELECT NAME FROM STUDENTS
WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3), ID;

The Report

Here is the complete text of the exercise in English, including the table structures that were missing from the images.

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Students

Grades contains the following data:

Grades

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Sample Input

Students

Grades

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88
Scarlet 8 78
NULL 7 63
NULL 7 68

Note

Print "NULL" as the name if the grade is less than 8.

Explanation

Consider the following table with the grades assigned to the students:

Students

So, the following students got 8, 9 or 10 grades:

• Maria (grade 10)
• Jane (grade 9)
• Julia (grade 9)
• Scarlet (grade 8)

Solución

Para unir las tablas Students y Grades. Como no hay una columna en común directa, la unión se hace con un INNER JOIN y la condición students.marks BETWEEN grades.min_mark AND grades.max_mark. Así, a cada estudiante se le asigna el grado que corresponde según sus marcas.

En el SELECT, debemos mostrar tres columnas: el nombre, el grado y las marcas. Pero como el enunciado pide que si el grado es menor a 8 el nombre sea 'NULL', usamos un CASE WHEN grades.grade < 8 THEN 'NULL' ELSE students.name END. Es importante notar que NULLIF no sirve aquí, porque compara un texto con un booleano y nunca los iguala, por lo que siempre devolvería el nombre original.

Ahora en el orden de usar ORDER BY seria primero grades.grade DESC (de mayor a menor). Dentro de cada grado, si es 8, 9 o 10, ordenamos por students.name ASC (alfabéticamente); si es 1 a 7, ordenamos por students.marks ASC. Para lograr esa condicional, usamos dos CASE dentro del ORDER BY: uno para los nombres cuando el grado es >= 8, y otro para las marcas cuando el grado es < 8. Así, el resultado sale exactamente como se pide.

SELECT 
    CASE WHEN grades.grade < 8 THEN 'NULL' ELSE students.name END AS Name,
    grades.grade,
    students.marks
FROM students 
INNER JOIN grades 
    ON students.marks BETWEEN grades.min_mark AND grades.max_mark
ORDER BY 
    grades.grade DESC,
    CASE WHEN grades.grade >= 8 THEN students.name END ASC,
    CASE WHEN grades.grade < 8 THEN students.marks END ASC;

Employe 2

The Employee table containing employee data for a company is described as follows:

Employee Names

Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is their monthly salary.

Sample Input

Sample Output

Angela
Bonnie
Frank
Joe
Kimberly
Lisa
Michael
Patrick
Rose
Todd

Solución

Ordenamos de manera ASC la columna name para que esté ordenada alfabéticamente.

SELECT NAME
FROM EMPLOYEE
ORDER BY NAME;

... (resultado truncado, hay más filas)

Employee Salaries

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than $2000 per month who have been employees for less than 10 months. Sort your result by ascending employee_id.

Explanation

Angela has been an employee for 1 month and earns $3443 per month.

Michael has been an employee for 6 months and earns $2017 per month.

Todd has been an employee for 5 months and earns $3396 per month.

Joe has been an employee for 9 months and earns $3573 per month.

We order our output by ascending employee_id.

Solución

Para buscar el salario mayor a 2000 de los empleados usamos el operador mayor (>), y para los que tengan menos de 10 meses usamos el operador menor (<). Finalmente, ordenamos de forma ascendente por el ID de cada empleado.

SELECT Name FROM Employee
WHERE salary > 2000 AND months < 10
ORDER BY employee_id;

... (resultado truncado, hay más filas)

The Blunder

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's 0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.: actual - miscalculated average monthly salaries), and round it up to the next integer.

Explanation

The table below shows the salaries without zeros as they were entered by Samantha:

Samantha computes an average salary of 98.00. The actual average salary is 2159.00.

The resulting error between the two calculations is 2159.00 - 98.00 = 2061.00. Since it is equal to the integer 2061, it does not get rounded up.

Un salario real de 1420 Samantha lo escribió como 142, y 2006 como 26. El objetivo es encontrar la diferencia entre el promedio real y el promedio erróneo que ella obtuvo. Es decir, primero calculamos el promedio correcto usando los salarios originales, luego calculamos el promedio equivocado usando los salarios sin ceros, y finalmente restamos: promedio_real - promedio_erróneo. El resultado de esa resta debemos redondearlo hacia arriba al siguiente entero.

Solución

Usamos AVG(SALARY) para obtener el promedio real, y AVG(REPLACE(SALARY, '0', '')) para obtener el promedio con los ceros eliminados. Después restamos ambos promedios y aplicamos CEIL() para redondear hacia arriba.

SELECT CEIL(
  AVG(SALARY) - AVG(REPLACE(SALARY, '0', ''))
)
FROM EMPLOYEES;

Top Earners

We define an employee's total earnings to be their monthly salary × months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

Explanation

The table and earnings data is depicted in the following diagram:

The maximum earnings value is 69952. The only employee with earnings = 69952 is Kimberly, so we print the maximum earnings value (69952) and a count of the number of employees who have earned $69952 (which is 1) as two space-separated values.

Solución

Para resolver este problema hay varias formas realmente, el enfoque principal son las subconsultas o CTE (Common Table Expression), para saber mas de CTE podemos ver mirar esta excelente guía . Desmembrando el problema, sabemos que debemos buscar las ganancias totales máximas de cualquier empleado en la tabla Employee. Para ello realizamos la operación salary * months y usamos la función de agregación MAX para obtener el valor máximo. Luego necesitamos contar cuántos empleados tienen ese valor máximo.

Si te detienes en este punto, el conflicto es cómo obtener en una sola fila tanto el valor máximo como el conteo de empleados que lo alcanzan. Hay varias formas; nos centraremos en dos: CTE y subconsulta.

Usando CTE:

WITH max_earnings AS (
    SELECT MAX(salary * months) AS max_val
    FROM Employee
)
SELECT MAX(max_val), COUNT(employee_id)
FROM Employee, max_earnings
WHERE (salary * months) = max_val;

Primero calculamos el salario máximo de todos los empleados y lo guardamos en una CTE, que actúa como una tabla temporal. Luego, en la consulta principal, reutilizamos ese valor para filtrar a los empleados cuyas ganancias coinciden con el máximo. Si te preguntas por qué volvemos a usar MAX en max_val, es porque al usar COUNT la consulta se convierte en una agregación. Todas las columnas del SELECT deben estar agregadas o en un GROUP BY. Como max_val no está agregada, debemos envolverla en una función de agregación (como MAX) o incluirla en un GROUP BY; de lo contrario, obtendremos un error SQL (puedes probar quitando el MAX).

Usando subconsulta:

SELECT MAX(salary * months), COUNT(*)
FROM Employee
WHERE (salary * months) = (
    SELECT MAX(salary * months)
    FROM Employee
);

Esta forma hace lo mismo, pero puede resultar un poco confusa al ordenar las ideas. La subconsulta calcula el máximo global y la consulta principal filtra y cuenta.