Esquema de una base de datos

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

  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)
  1. 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)

También te puede interesar leer esto...

Previous Post Next Post