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

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

1. Seleccionar de la tabla employees el número de empleado, el salario, la comisión, el número de departamento y la fecha de contratación:

SELECT number, salary, commission, number, hire_date
FROM employees;

+--------+--------+------------+--------+---------------------+
| number | salary | commission | number | hire_date           |
+--------+--------+------------+--------+---------------------+
|      1 |   4000 |       NULL |      1 | 2004-11-10 00:00:00 |
|     10 |   3500 |        300 |     10 | 2004-11-10 00:00:00 |
|     11 |   3600 |       NULL |     11 | 2004-11-25 00:00:00 |
|     12 |   2500 |        500 |     12 | 2004-11-19 00:00:00 |
|     13 |   2300 |        700 |     13 | 2004-11-10 00:00:00 |
|     88 |   1200 |       NULL |     88 | 2004-12-11 00:00:00 |
|     89 |   1300 |       NULL |     89 | 2004-12-11 00:00:00 |
|     90 |   1200 |       NULL |     90 | 2004-12-11 00:00:00 |
|     91 |   1300 |       NULL |     91 | 2004-12-11 00:00:00 |
|     92 |   2100 |       NULL |     92 | 2004-12-11 00:00:00 |
|     93 |   1800 |       NULL |     93 | 2004-12-28 00:00:00 |
|     94 |   1800 |       NULL |     94 | 2004-12-11 00:00:00 |
|     95 |   2100 |       NULL |     95 | 2004-12-11 00:00:00 |
|     96 |   2100 |        500 |     96 | 2004-12-30 00:00:00 |
|     97 |   1800 |        600 |     97 | 2004-12-27 00:00:00 |
|    100 |   1800 |        500 |    100 | 2004-12-30 00:00:00 |
|    102 |   2100 |        550 |    102 | 2004-12-12 00:00:00 |
|    105 |   1800 |       NULL |    105 | 2004-12-11 00:00:00 |
|    109 |   1700 |       NULL |    109 | 2005-01-01 00:00:00 |
|    110 |   2000 |        200 |    110 | 2005-01-01 00:00:00 |
|    112 |   1700 |       NULL |    112 | 2005-01-01 00:00:00 |
+--------+--------+------------+--------+---------------------+
21 rows in set (0.00 sec)

2. Seleccionar todas las columnas de la tabla department:

SELECT *
FROM departments;

+----+--------+------------+-----------+
| id | number | name       | location  |
+----+--------+------------+-----------+
|  1 |    101 | Cuentas    | Madrid    |
|  2 |     76 | I+D        | Sevilla   |
|  3 |     25 | Ventas     | Barcelona |
|  4 |     88 | E-commerce | Barcelona |
+----+--------+------------+-----------+
4 rows in set (0.00 sec)

3. Obtener los empleados que sean comerciales:

SELECT *
FROM employees
WHERE job = 'Comercial';

+----+--------+--------+-----------+---------------------+--------+------------+-------------+---------------+
| id | number | name   | job       | hire_date           | salary | commission | id_employee | id_department |
+----+--------+--------+-----------+---------------------+--------+------------+-------------+---------------+
| 14 |     96 | Laura  | Comercial | 2004-12-30 00:00:00 |   2100 |        500 |           4 |             3 |
| 15 |     97 | Montse | Comercial | 2004-12-27 00:00:00 |   1800 |        600 |           4 |             3 |
| 16 |    100 | Jorge  | Comercial | 2004-12-30 00:00:00 |   1800 |        500 |           4 |             3 |
| 17 |    102 | Arturo | Comercial | 2004-12-12 00:00:00 |   2100 |        550 |           4 |             3 |
+----+--------+--------+-----------+---------------------+--------+------------+-------------+---------------+
4 rows in set (0.00 sec)

4. Seleccionar el nombre de los empleados que ganen más de 3000 €:

SELECT name
FROM employees
WHERE salary > 3000;

+---------+
| name    |
+---------+
| Julián  |
| Pepa    |
| Juan    |
+---------+
3 rows in set (0.00 sec)

5. Obtener el nombre y el puesto de los empleados que han sido contratados antes del 2004-11-25:

SELECT name, job
FROM employees
WHERE hire_date < '2004-11-25';

+---------+---------------------+
| name    | job                 |
+---------+---------------------+
| Julián  | Director general    |
| Pepa    | Jefe de cuentas     |
| Marta   | Jefa de ventas      |
| Ignacio | Director e-commerce |
+---------+---------------------+
4 rows in set (0.00 sec)

6. Seleccionar el nombre de los comerciales que ganen más de 1800 €:

SELECT name
FROM employees
WHERE job = 'Comercial' AND salary > 1800;

+--------+
| name   |
+--------+
| Laura  |
| Arturo |
+--------+
2 rows in set (0.00 sec)

7. Seleccionar los empleados que se llamen Marta, Pepe o Juan:

SELECT *
FROM employees
WHERE name = 'Marta' OR name = 'Pepe' OR name = 'Juan';

+----+--------+-------+-------------------+---------------------+--------+------------+-------------+---------------+
| id | number | name  | job               | hire_date           | salary | commission | id_employee | id_department |
+----+--------+-------+-------------------+---------------------+--------+------------+-------------+---------------+
|  3 |     11 | Juan  | Director técnico  | 2004-11-25 00:00:00 |   3600 |       NULL |           1 |             2 |
|  4 |     12 | Marta | Jefa de ventas    | 2004-11-19 00:00:00 |   2500 |        500 |           1 |             3 |
| 18 |    105 | Juan  | Programador       | 2004-12-11 00:00:00 |   1800 |       NULL |           5 |             4 |
| 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 |
+----+--------+-------+-------------------+---------------------+--------+------------+-------------+---------------+
6 rows in set (0.00 sec)

8. Seleccionar los empleados que no se llamen Marta, Pepe o Juan:

SELECT *
FROM employees
WHERE name <> 'Marta' AND name <> 'Pepe' AND name <> 'Juan';

+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
| id | number | name     | job                 | hire_date           | salary | commission | id_employee | id_department |
+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
|  1 |      1 | Julián   | Director general    | 2004-11-10 00:00:00 |   4000 |       NULL |        NULL |             1 |
|  2 |     10 | Pepa     | Jefe de cuentas     | 2004-11-10 00:00:00 |   3500 |        300 |           1 |             1 |
|  5 |     13 | Ignacio  | Director e-commerce | 2004-11-10 00:00:00 |   2300 |        700 |           1 |             4 |
|  6 |     88 | Carlos   | Administrativo      | 2004-12-11 00:00:00 |   1200 |       NULL |           2 |             1 |
|  7 |     89 | Silvia   | Contable            | 2004-12-11 00:00:00 |   1300 |       NULL |           2 |             1 |
|  8 |     90 | María    | Administrativa      | 2004-12-11 00:00:00 |   1200 |       NULL |           2 |             1 |
|  9 |     91 | Pedro    | Contable            | 2004-12-11 00:00:00 |   1300 |       NULL |           2 |             1 |
| 10 |     92 | Teresa   | Ingeniera           | 2004-12-11 00:00:00 |   2100 |       NULL |           3 |             2 |
| 11 |     93 | Isabel   | Ingeniera Técnica   | 2004-12-28 00:00:00 |   1800 |       NULL |           3 |             2 |
| 12 |     94 | Javier   | Ingeniero Técnico   | 2004-12-11 00:00:00 |   1800 |       NULL |           3 |             2 |
| 13 |     95 | Fernando | Ingeniero           | 2004-12-11 00:00:00 |   2100 |       NULL |           3 |             2 |
| 14 |     96 | Laura    | Comercial           | 2004-12-30 00:00:00 |   2100 |        500 |           4 |             3 |
| 15 |     97 | Montse   | Comercial           | 2004-12-27 00:00:00 |   1800 |        600 |           4 |             3 |
| 16 |    100 | Jorge    | Comercial           | 2004-12-30 00:00:00 |   1800 |        500 |           4 |             3 |
| 17 |    102 | Arturo   | Comercial           | 2004-12-12 00:00:00 |   2100 |        550 |           4 |             3 |
+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
15 rows in set (0.00 sec)

9. Seleccionar los empleados cuyo salario esté entre 2000 € y 3000 €:

SELECT *
FROM employees
WHERE salary BETWEEN 2000 AND 3000;

+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
| id | number | name     | job                 | hire_date           | salary | commission | id_employee | id_department |
+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
|  4 |     12 | Marta    | Jefa de ventas      | 2004-11-19 00:00:00 |   2500 |        500 |           1 |             3 |
|  5 |     13 | Ignacio  | Director e-commerce | 2004-11-10 00:00:00 |   2300 |        700 |           1 |             4 |
| 10 |     92 | Teresa   | Ingeniera           | 2004-12-11 00:00:00 |   2100 |       NULL |           3 |             2 |
| 13 |     95 | Fernando | Ingeniero           | 2004-12-11 00:00:00 |   2100 |       NULL |           3 |             2 |
| 14 |     96 | Laura    | Comercial           | 2004-12-30 00:00:00 |   2100 |        500 |           4 |             3 |
| 17 |    102 | Arturo   | Comercial           | 2004-12-12 00:00:00 |   2100 |        550 |           4 |             3 |
| 20 |    110 | Juan     | SEO                 | 2005-01-01 00:00:00 |   2000 |        200 |           5 |             4 |
+----+--------+----------+---------------------+---------------------+--------+------------+-------------+---------------+
7 rows in set (0.00 sec)

10. Seleccionar el nombre de los empleados que trabajan en Barcelona:

SELECT employees.name
FROM employees JOIN departments ON employees.id_department = departments.id
WHERE departments.location = 'Barcelona';

+---------+
| name    |
+---------+
| Marta   |
| Laura   |
| Montse  |
| Jorge   |
| Arturo  |
| Ignacio |
| Juan    |
| Pepe    |
| Juan    |
| Pepe    |
+---------+
10 rows in set (0.00 sec)

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