¡Practiquemos las consultas SQL con MySQL! (III de IV)

Esta es la continuación de ¡Practiquemos las consultas SQL con MySQL! (II de IV).

11. Nombre de todos los empleados, empleo, número de departamento y localidad:

SELECT e.name, e.job, d.number, d.location
FROM employees as e JOIN departments as d ON e.id_department = d.id;

+----------+---------------------+--------+-----------+
| name     | job                 | number | location  |
+----------+---------------------+--------+-----------+
| Julián   | Director general    |    101 | Madrid    |
| Pepa     | Jefe de cuentas     |    101 | Madrid    |
| Carlos   | Administrativo      |    101 | Madrid    |
| Silvia   | Contable            |    101 | Madrid    |
| María    | Administrativa      |    101 | Madrid    |
| Pedro    | Contable            |    101 | Madrid    |
| Juan     | Director técnico    |     76 | Sevilla   |
| Teresa   | Ingeniera           |     76 | Sevilla   |
| Isabel   | Ingeniera Técnica   |     76 | Sevilla   |
| Javier   | Ingeniero Técnico   |     76 | Sevilla   |
| Fernando | Ingeniero           |     76 | Sevilla   |
| Marta    | Jefa de ventas      |     25 | Barcelona |
| Laura    | Comercial           |     25 | Barcelona |
| Montse   | Comercial           |     25 | Barcelona |
| Jorge    | Comercial           |     25 | Barcelona |
| Arturo   | Comercial           |     25 | Barcelona |
| Ignacio  | Director e-commerce |     88 | Barcelona |
| Juan     | Programador         |     88 | Barcelona |
| Pepe     | Programador         |     88 | Barcelona |
| Juan     | SEO                 |     88 | Barcelona |
| Pepe     | Analista            |     88 | Barcelona |
+----------+---------------------+--------+-----------+
21 rows in set (0.00 sec)

12. Nombre de los empleados que trabajan en el departamento número 101 o 25:

SELECT e.name
FROM employees as e JOIN departments as d ON e.id_department = d.id
WHERE d.number = 101 OR d.number = 25;

+---------+
| name    |
+---------+
| Julián  |
| Pepa    |
| Carlos  |
| Silvia  |
| María   |
| Pedro   |
| Marta   |
| Laura   |
| Montse  |
| Jorge   |
| Arturo  |
+---------+
11 rows in set (0.00 sec)

13. Seleccionar los distintos empleos que hay en cada departamento:

SELECT DISTINCT d.number, e.job
FROM employees as e JOIN departments as d ON e.id_department = d.id;

+--------+---------------------+
| number | job                 |
+--------+---------------------+
|    101 | Director general    |
|    101 | Jefe de cuentas     |
|    101 | Administrativo      |
|    101 | Contable            |
|    101 | Administrativa      |
|     76 | Director técnico    |
|     76 | Ingeniera           |
|     76 | Ingeniera Técnica   |
|     76 | Ingeniero Técnico   |
|     76 | Ingeniero           |
|     25 | Jefa de ventas      |
|     25 | Comercial           |
|     88 | Director e-commerce |
|     88 | Programador         |
|     88 | SEO                 |
|     88 | Analista            |
+--------+---------------------+
16 rows in set (0.00 sec)

14. Empleados contratados en 2005:

SELECT *
FROM employees
WHERE hire_date BETWEEN '2005-01-01' AND '2005-12-31';

+----+--------+------+-------------+---------------------+--------+------------+-------------+---------------+
| id | number | name | job         | hire_date           | salary | commission | id_employee | id_department |
+----+--------+------+-------------+---------------------+--------+------------+-------------+---------------+
| 19 |    109 | Pepe | Programador | 2005-01-01 00:00:00 |   1700 |       NULL |           5 |             4 |
| 20 |    110 | Juan | SEO         | 2005-01-01 00:00:00 |   2000 |        200 |           5 |             4 |
| 21 |    112 | Pepe | Analista    | 2005-01-01 00:00:00 |   1700 |       NULL |           5 |             4 |
+----+--------+------+-------------+---------------------+--------+------------+-------------+---------------+
3 rows in set (0.00 sec)

15. Nombre y empleo de los empleados que tienen comisión:

SELECT name, job
FROM employees
WHERE commission IS NOT NULL;

+---------+---------------------+
| name    | job                 |
+---------+---------------------+
| Pepa    | Jefe de cuentas     |
| Marta   | Jefa de ventas      |
| Ignacio | Director e-commerce |
| Laura   | Comercial           |
| Montse  | Comercial           |
| Jorge   | Comercial           |
| Arturo  | Comercial           |
| Juan    | SEO                 |
+---------+---------------------+
8 rows in set (0.00 sec)

16. Empleados cuyo nombre empieza por ‘J’:

SELECT DISTINCT name
FROM employees
WHERE name LIKE 'J%';

+---------+
| name    |
+---------+
| Julián  |
| Juan    |
| Javier  |
| Jorge   |
+---------+
4 rows in set (0.00 sec)

17. Empleados cuyo nombre tiene como segunda letra la ‘a’:

SELECT DISTINCT name
FROM employees
WHERE name LIKE '_a%';

+--------+
| name   |
+--------+
| Marta  |
| Carlos |
| María  |
| Javier |
| Laura  |
+--------+
5 rows in set (0.00 sec)

18. Empleados que ganan más de 2300 € ordenados por nombre:

SELECT *
FROM employees
WHERE salary >= 2300
ORDER BY name;

+----+--------+---------+---------------------+---------------------+--------+------------+-------------+---------------+
| id | number | name    | job                 | hire_date           | salary | commission | id_employee | id_department |
+----+--------+---------+---------------------+---------------------+--------+------------+-------------+---------------+
|  5 |     13 | Ignacio | Director e-commerce | 2004-11-10 00:00:00 |   2300 |        700 |           1 |             4 |
|  3 |     11 | Juan    | Director técnico    | 2004-11-25 00:00:00 |   3600 |       NULL |           1 |             2 |
|  1 |      1 | Julián  | Director general    | 2004-11-10 00:00:00 |   4000 |       NULL |        NULL |             1 |
|  4 |     12 | Marta   | Jefa de ventas      | 2004-11-19 00:00:00 |   2500 |        500 |           1 |             3 |
|  2 |     10 | Pepa    | Jefe de cuentas     | 2004-11-10 00:00:00 |   3500 |        300 |           1 |             1 |
+----+--------+---------+---------------------+---------------------+--------+------------+-------------+---------------+
5 rows in set (0.00 sec)

19. Calcular el salario anual a percibir por cada empleado:

SELECT name, salary * 12 as annual_salary
FROM employees;

+----------+---------------+
| name     | annual_salary |
+----------+---------------+
| Julián   |         48000 |
| Pepa     |         42000 |
| Juan     |         43200 |
| Marta    |         30000 |
| Ignacio  |         27600 |
| Carlos   |         14400 |
| Silvia   |         15600 |
| María    |         14400 |
| Pedro    |         15600 |
| Teresa   |         25200 |
| Isabel   |         21600 |
| Javier   |         21600 |
| Fernando |         25200 |
| Laura    |         25200 |
| Montse   |         21600 |
| Jorge    |         21600 |
| Arturo   |         25200 |
| Juan     |         21600 |
| Pepe     |         20400 |
| Juan     |         24000 |
| Pepe     |         20400 |
+----------+---------------+
21 rows in set (0.00 sec)

20. Calcular lo que paga la empresa cada mes en concepto de salarios:

SELECT SUM(salary) as month_salaries
FROM employees;

+----------------+
| month_salaries |
+----------------+
|          43700 |
+----------------+
1 row in set (0.00 sec)

21. Calcular el número de empleados que tienen comisión y la media de la comisión:

SELECT COUNT(*) as agents, AVG(commission) as average_comission
FROM employees
WHERE commission IS NOT NULL;

+--------+-------------------+
| agents | average_comission |
+--------+-------------------+
|      8 |          481.2500 |
+--------+-------------------+
1 row in set (0.00 sec)

22. Calcular el salario mínimo y el salario máximo de los empleados agrupados por empleo:

SELECT job, MIN(salary), MAX(salary)
FROM employees
GROUP BY job;

+---------------------+-------------+-------------+
| job                 | MIN(salary) | MAX(salary) |
+---------------------+-------------+-------------+
| Administrativa      |        1200 |        1200 |
| Administrativo      |        1200 |        1200 |
| Analista            |        1700 |        1700 |
| Comercial           |        1800 |        2100 |
| Contable            |        1300 |        1300 |
| Director e-commerce |        2300 |        2300 |
| Director general    |        4000 |        4000 |
| Director técnico    |        3600 |        3600 |
| Ingeniera           |        2100 |        2100 |
| Ingeniera Técnica   |        1800 |        1800 |
| Ingeniero           |        2100 |        2100 |
| Ingeniero Técnico   |        1800 |        1800 |
| Jefa de ventas      |        2500 |        2500 |
| Jefe de cuentas     |        3500 |        3500 |
| Programador         |        1700 |        1800 |
| SEO                 |        2000 |        2000 |
+---------------------+-------------+-------------+
16 rows in set (0.00 sec)

23. Calcular el número de empleados que tienen comisión y la media de la comisión en cada departamento:

SELECT id_department, COUNT(*) as agents, ROUND(AVG(commission),2) as average_comission
FROM employees
WHERE commission IS NOT NULL
GROUP BY id_department;

+---------------+--------+-------------------+
| id_department | agents | average_comission |
+---------------+--------+-------------------+
|             1 |      1 |            300.00 |
|             3 |      5 |            530.00 |
|             4 |      2 |            450.00 |
+---------------+--------+-------------------+
3 rows in set (0.00 sec)

24. Calcular el salario mínimo, máximo y medio de los empleados agrupados por empleo:

SELECT job, MIN(salary), MAX(salary), ROUND(AVG(salary),2)
FROM employees
GROUP BY job;

+---------------------+-------------+-------------+----------------------+
| job                 | MIN(salary) | MAX(salary) | ROUND(AVG(salary),2) |
+---------------------+-------------+-------------+----------------------+
| Administrativa      |        1200 |        1200 |              1200.00 |
| Administrativo      |        1200 |        1200 |              1200.00 |
| Analista            |        1700 |        1700 |              1700.00 |
| Comercial           |        1800 |        2100 |              1950.00 |
| Contable            |        1300 |        1300 |              1300.00 |
| Director e-commerce |        2300 |        2300 |              2300.00 |
| Director general    |        4000 |        4000 |              4000.00 |
| Director técnico    |        3600 |        3600 |              3600.00 |
| Ingeniera           |        2100 |        2100 |              2100.00 |
| Ingeniera Técnica   |        1800 |        1800 |              1800.00 |
| Ingeniero           |        2100 |        2100 |              2100.00 |
| Ingeniero Técnico   |        1800 |        1800 |              1800.00 |
| Jefa de ventas      |        2500 |        2500 |              2500.00 |
| Jefe de cuentas     |        3500 |        3500 |              3500.00 |
| Programador         |        1700 |        1800 |              1750.00 |
| SEO                 |        2000 |        2000 |              2000.00 |
+---------------------+-------------+-------------+----------------------+
16 rows in set (0.00 sec)

25. Calcular el salario mínimo, máximo y medio de los empleados agrupados por empleo, pero sólo de aquellos cuya media sea superior a 3000:

SELECT job, MIN(salary), MAX(salary), ROUND(AVG(salary),2)
FROM employees
GROUP BY job
HAVING AVG(salary) > 3000;

+-------------------+-------------+-------------+----------------------+
| job               | MIN(salary) | MAX(salary) | ROUND(AVG(salary),2) |
+-------------------+-------------+-------------+----------------------+
| Director general  |        4000 |        4000 |              4000.00 |
| Director técnico  |        3600 |        3600 |              3600.00 |
| Jefe de cuentas   |        3500 |        3500 |              3500.00 |
+-------------------+-------------+-------------+----------------------+
3 rows in set (0.00 sec)