TETETETETE

3.

mysql> select nombre, year(contrato) from Empleados;
+-------------------+----------------+
| nombre            | year(contrato) |
+-------------------+----------------+
| Antonio Gutierrez |           1989 |
| Paloma Blanco     |           1992 |
| Antonio Pazos     |           1986 |
| Ana Garcia        |           1995 |
| Amparo Beltran    |           1998 |
| Enrique Gomez     |           2000 |
| Nieves Soler      |           1996 |
| Juan Jose Velasco |           1997 |
| Isidro Perez      |           2000 |
| Ignacio Lopez     |           1990 |
| Vicente Salvador  |           1995 |
| Carmen Hernandez  |           1990 |
| Juan Pons         |           1994 |
| Pedro Fernandez   |           1999 |
| Silvia Blasco     |           1992 |
| Jose Alegre       |           1997 |
| Cristina Prats    |           1984 |
| Carlos Gimenez    |           1995 |
| Maria Gonzalez    |           1996 |
| Manuel Torres     |           1998 |
| Jose Perez        |           1996 |
| Alejandro Martos  |           1994 |
| Veronica Muelas   |           1997 |
| Elena Lopez       |           1994 |
| Isabel Fernandez  |           2000 |
| Jose Mujica       |           1987 |
| Pedro Bledos      |           1998 |
| Pablo Costas      |           1995 |
| Ester Castro      |           1996 |
| Gregorio Mas      |           1997 |
| Jose Medina       |           1995 |
| Maria Utrillas    |           1997 |
| Marina Gilabert   |           1998 |
+-------------------+----------------+
33 rows in set (0.04 sec)

4.

mysql> select edad from Empleados;
+------+
| edad |
+------+
|   45 |
|   56 |
|   52 |
|   27 |
|   28 |
|   36 |
|   25 |
|   34 |
|   22 |
|   38 |
|   29 |
|   44 |
|   50 |
|   23 |
|   33 |
|   26 |
|   46 |
|   35 |
|   37 |
|   24 |
|   28 |
|   34 |
|   25 |
|   29 |
|   22 |
|   49 |
|   26 |
|   35 |
|   27 |
|   33 |
|   34 |
|   27 |
|   24 |
+------+
33 rows in set (0.00 sec)

5.

mysql> select edad, count(edad) from Empleados group by edad;
+------+-------------+
| edad | count(edad) |
+------+-------------+
|   22 |           2 |
|   23 |           1 |
|   24 |           2 |
|   25 |           2 |
|   26 |           2 |
|   27 |           3 |
|   28 |           2 |
|   29 |           2 |
|   33 |           2 |
|   34 |           3 |
|   35 |           2 |
|   36 |           1 |
|   37 |           1 |
|   38 |           1 |
|   44 |           1 |
|   45 |           1 |
|   46 |           1 |
|   49 |           1 |
|   50 |           1 |
|   52 |           1 |
|   56 |           1 |
+------+-------------+
21 rows in set (0.01 sec)

6.

mysql> select departamento, avg(edad) from Empleados group by departamento;
+--------------+-----------+
| departamento | avg(edad) |
+--------------+-----------+
|          100 |   51.0000 |
|          101 |   30.3333 |
|          102 |   37.5000 |
|          103 |   37.5000 |
|          104 |   28.3333 |
|          105 |   29.5000 |
|          106 |   35.6667 |
|          107 |   35.0000 |
|          108 |   30.5000 |
|          109 |   29.6667 |
|          110 |   29.0000 |
|          111 |   30.0000 |
|          112 |   25.5000 |
+--------------+-----------+
13 rows in set (0.00 sec)

7.

mysql> select categoria from Categorias where salario > 35000;
+-----------+
| categoria |
+-----------+
|         1 |
|         2 |
+-----------+
2 rows in set (0.00 sec)

8.

mysql> select * from Empleados where num=1014;
+------+---------------+------+--------------+-----------+---------------------+
| NUM  | NOMBRE        | EDAD | DEPARTAMENTO | CATEGORIA | CONTRATO            |
+------+---------------+------+--------------+-----------+---------------------+
| 1014 | Silvia Blasco |   33 |          102 |         4 | 1992-02-23 00:00:00 |
+------+---------------+------+--------------+-----------+---------------------+
1 row in set (0.00 sec)

9.

mysql> select * from Empleados where departamento = 106;
+------+----------------+------+--------------+-----------+---------------------+
| NUM  | NOMBRE         | EDAD | DEPARTAMENTO | CATEGORIA | CONTRATO            |
+------+----------------+------+--------------+-----------+---------------------+
| 1015 | Jose Alegre    |   26 |          106 |         2 | 1997-08-26 00:00:00 |
| 1016 | Cristina Prats |   46 |          106 |         4 | 1984-11-18 00:00:00 |
| 1017 | Carlos Gimenez |   35 |          106 |         4 | 1995-05-15 00:00:00 |
+------+----------------+------+--------------+-----------+---------------------+
3 rows in set (0.00 sec)

10.

mysql> select nombre from Empleados where year(contrato) = 2000;
+------------------+
| nombre           |
+------------------+
| Enrique Gomez    |
| Isidro Perez     |
| Isabel Fernandez |
+------------------+
3 rows in set (0.00 sec)

11.

mysql> select * from Empleados where categoria <> 4;
+------+-------------------+------+--------------+-----------+---------------------+
| NUM  | NOMBRE            | EDAD | DEPARTAMENTO | CATEGORIA | CONTRATO            |
+------+-------------------+------+--------------+-----------+---------------------+
| 1000 | Antonio Gutierrez |   45 |          100 |         1 | 1989-01-12 00:00:00 |
| 1001 | Paloma Blanco     |   56 |          100 |         2 | 1992-03-17 00:00:00 |
| 1002 | Antonio Pazos     |   52 |          100 |         3 | 1986-03-14 00:00:00 |
| 1003 | Ana Garcia        |   27 |          101 |         2 | 1995-10-23 00:00:00 |
| 1004 | Amparo Beltran    |   28 |          101 |         3 | 1998-02-04 00:00:00 |
| 1005 | Enrique Gomez     |   36 |          101 |         3 | 2000-07-05 00:00:00 |
| 1006 | Nieves Soler      |   25 |          105 |         2 | 1996-03-13 00:00:00 |
| 1007 | Juan Jose Velasco |   34 |          105 |         3 | 1997-02-14 00:00:00 |
| 1008 | Isidro Perez      |   22 |          109 |         2 | 2000-05-06 00:00:00 |
| 1009 | Ignacio Lopez     |   38 |          109 |         3 | 1990-04-07 00:00:00 |
| 1010 | Vicente Salvador  |   29 |          109 |         3 | 1995-07-08 00:00:00 |
| 1011 | Carmen Hernandez  |   44 |          102 |         2 | 1990-07-16 00:00:00 |
| 1015 | Jose Alegre       |   26 |          106 |         2 | 1997-08-26 00:00:00 |
| 1020 | Jose Perez        |   28 |          110 |         2 | 1996-03-22 00:00:00 |
| 1025 | Jose Mujica       |   49 |          103 |         2 | 1987-09-04 00:00:00 |
| 1026 | Pedro Bledos      |   26 |          103 |         5 | 1998-02-06 00:00:00 |
| 1027 | Pablo Costas      |   35 |          107 |         5 | 1995-07-03 00:00:00 |
| 1028 | Ester Castro      |   27 |          111 |         2 | 1996-07-18 00:00:00 |
| 1029 | Gregorio Mas      |   33 |          111 |         5 | 1997-03-14 00:00:00 |
| 1030 | Jose Medina       |   34 |          104 |         2 | 1995-06-14 00:00:00 |
| 1031 | Maria Utrillas    |   27 |          104 |         3 | 1997-08-19 00:00:00 |
| 1032 | Marina Gilabert   |   24 |          104 |         3 | 1998-12-01 00:00:00 |
+------+-------------------+------+--------------+-----------+---------------------+
22 rows in set (0.00 sec)

12.

mysql> select nombre from Empleados where year(contrato) between 1990 and 1994;
+------------------+
| nombre           |
+------------------+
| Paloma Blanco    |
| Ignacio Lopez    |
| Carmen Hernandez |
| Juan Pons        |
| Silvia Blasco    |
| Alejandro Martos |
| Elena Lopez      |
+------------------+
7 rows in set (0.00 sec)

13.

mysql> select categoria from Categorias where salario < '35000' or salario > '40000';
+-----------+
| categoria |
+-----------+
|         1 |
|         5 |
+-----------+
2 rows in set (0.00 sec)

14.

mysql> select nombre from Empleados where categoria IN (1,2);
# También es válido
# mysql> select nombre from Empleados where categoria = 1 OR categoria = 2;
+-------------------+
| nombre            |
+-------------------+
| Antonio Gutierrez |
| Paloma Blanco     |
| Ana Garcia        |
| Nieves Soler      |
| Isidro Perez      |
| Carmen Hernandez  |
| Jose Alegre       |
| Jose Perez        |
| Jose Mujica       |
| Ester Castro      |
| Jose Medina       |
+-------------------+
11 rows in set (0.00 sec)

15.

mysql> select nombre from Empleados where nombre like 'Jose %';
+-------------+
| nombre      |
+-------------+
| Jose Alegre |
| Jose Perez  |
| Jose Mujica |
| Jose Medina |
+-------------+
4 rows in set (0.00 sec)

16.

mysql> select nombre from Empleados where categoria = '3' and edad > '35';
+---------------+
| nombre        |
+---------------+
| Antonio Pazos |
| Enrique Gomez |
| Ignacio Lopez |
+---------------+
3 rows in set (0.00 sec)

17.

mysql> select nombre from Empleados where not departamento = 110;
+-------------------+
| nombre            |
+-------------------+
| Antonio Gutierrez |
| Paloma Blanco     |
| Antonio Pazos     |
| Ana Garcia        |
| Amparo Beltran    |
| Enrique Gomez     |
| Nieves Soler      |
| Juan Jose Velasco |
| Isidro Perez      |
| Ignacio Lopez     |
| Vicente Salvador  |
| Carmen Hernandez  |
| Juan Pons         |
| Pedro Fernandez   |
| Silvia Blasco     |
| Jose Alegre       |
| Cristina Prats    |
| Carlos Gimenez    |
| Maria Gonzalez    |
| Manuel Torres     |
| Elena Lopez       |
| Isabel Fernandez  |
| Jose Mujica       |
| Pedro Bledos      |
| Pablo Costas      |
| Ester Castro      |
| Gregorio Mas      |
| Jose Medina       |
| Maria Utrillas    |
| Marina Gilabert   |
+-------------------+
30 rows in set (0.00 sec)

18.

mysql> select nombre, edad from Empleados order by edad;
+-------------------+------+
| nombre            | edad |
+-------------------+------+
| Isabel Fernandez  |   22 |
| Isidro Perez      |   22 |
| Pedro Fernandez   |   23 |
| Manuel Torres     |   24 |
| Marina Gilabert   |   24 |
| Nieves Soler      |   25 |
| Veronica Muelas   |   25 |
| Pedro Bledos      |   26 |
| Jose Alegre       |   26 |
| Maria Utrillas    |   27 |
| Ester Castro      |   27 |
| Ana Garcia        |   27 |
| Amparo Beltran    |   28 |
| Jose Perez        |   28 |
| Vicente Salvador  |   29 |
| Elena Lopez       |   29 |
| Gregorio Mas      |   33 |
| Silvia Blasco     |   33 |
| Juan Jose Velasco |   34 |
| Alejandro Martos  |   34 |
| Jose Medina       |   34 |
| Carlos Gimenez    |   35 |
| Pablo Costas      |   35 |
| Enrique Gomez     |   36 |
| Maria Gonzalez    |   37 |
| Ignacio Lopez     |   38 |
| Carmen Hernandez  |   44 |
| Antonio Gutierrez |   45 |
| Cristina Prats    |   46 |
| Jose Mujica       |   49 |
| Juan Pons         |   50 |
| Antonio Pazos     |   52 |
| Paloma Blanco     |   56 |
+-------------------+------+
33 rows in set (0.00 sec)

19.

mysql> select nombre, edad from Empleados order by nombre desc;
+-------------------+------+
| nombre            | edad |
+-------------------+------+
| Vicente Salvador  |   29 |
| Veronica Muelas   |   25 |
| Silvia Blasco     |   33 |
| Pedro Fernandez   |   23 |
| Pedro Bledos      |   26 |
| Paloma Blanco     |   56 |
| Pablo Costas      |   35 |
| Nieves Soler      |   25 |
| Marina Gilabert   |   24 |
| Maria Utrillas    |   27 |
| Maria Gonzalez    |   37 |
| Manuel Torres     |   24 |
| Juan Pons         |   50 |
| Juan Jose Velasco |   34 |
| Jose Perez        |   28 |
| Jose Mujica       |   49 |
| Jose Medina       |   34 |
| Jose Alegre       |   26 |
| Isidro Perez      |   22 |
| Isabel Fernandez  |   22 |
| Ignacio Lopez     |   38 |
| Gregorio Mas      |   33 |
| Ester Castro      |   27 |
| Enrique Gomez     |   36 |
| Elena Lopez       |   29 |
| Cristina Prats    |   46 |
| Carmen Hernandez  |   44 |
| Carlos Gimenez    |   35 |
| Antonio Pazos     |   52 |
| Antonio Gutierrez |   45 |
| Ana Garcia        |   27 |
| Amparo Beltran    |   28 |
| Alejandro Martos  |   34 |
+-------------------+------+
33 rows in set (0.00 sec)

20.

[...]

21.

mysql> select codigo, telefono from Dptoficinas, Oficinas where Dptoficinas.oficina = Oficinas.oficina and region = 'Centro';
+--------+-----------+
| codigo | telefono  |
+--------+-----------+
|    109 | 913641000 |
|    110 | 913641100 |
|    111 | 913641200 |
|    112 | 925871000 |
+--------+-----------+
4 rows in set (0.02 sec)

22.

mysql> select nombre, ciudad from Empleados, Oficinas, Dptoficinas where Empleados.departamento = codigo and Dptoficinas.oficina = Oficinas.oficina;
+-------------------+-----------+
| nombre            | ciudad    |
+-------------------+-----------+
| Antonio Gutierrez | Valencia  |
| Paloma Blanco     | Valencia  |
| Antonio Pazos     | Valencia  |
| Ana Garcia        | Valencia  |
| Amparo Beltran    | Valencia  |
| Enrique Gomez     | Valencia  |
| Carmen Hernandez  | Valencia  |
| Juan Pons         | Valencia  |
| Pedro Fernandez   | Valencia  |
| Silvia Blasco     | Valencia  |
| Jose Mujica       | Valencia  |
| Pedro Bledos      | Valencia  |
| Jose Medina       | Valencia  |
| Maria Utrillas    | Valencia  |
| Marina Gilabert   | Valencia  |
| Nieves Soler      | Barcelona |
| Juan Jose Velasco | Barcelona |
| Jose Alegre       | Barcelona |
| Cristina Prats    | Barcelona |
| Carlos Gimenez    | Barcelona |
| Pablo Costas      | Barcelona |
| Maria Gonzalez    | Alicante  |
| Manuel Torres     | Alicante  |
| Isidro Perez      | Madrid    |
| Ignacio Lopez     | Madrid    |
| Vicente Salvador  | Madrid    |
| Jose Perez        | Madrid    |
| Alejandro Martos  | Madrid    |
| Veronica Muelas   | Madrid    |
| Ester Castro      | Madrid    |
| Gregorio Mas      | Madrid    |
| Elena Lopez       | Toledo    |
| Isabel Fernandez  | Toledo    |
+-------------------+-----------+
33 rows in set (0.00 sec)

23.

mysql> select sum(salario) from Categorias, Empleados where Empleados.categoria = Categorias.categoria;
+--------------+
| sum(salario) |
+--------------+
|      1190000 |
+--------------+
1 row in set (0.00 sec)

24.

mysql> select avg(salario) from Empleados, Categorias, Dptoficinas, Oficinas where Empleados.categoria = Categorias.categoria and Empleados.departamento = codigo and Dptoficinas.oficina = Oficinas.oficina and ciudad = 'Barcelona';
+--------------+
| avg(salario) |
+--------------+
|   35000.0000 |
+--------------+
1 row in set (0.00 sec)

25.

mysql> select max(salario), min(salario) from Empleados, Categorias where Empleados.categoria = Categorias.categoria;
+--------------+--------------+
| max(salario) | min(salario) |
+--------------+--------------+
|        50000 |        25000 |
+--------------+--------------+
1 row in set (0.00 sec)

26.

mysql> select count(*) from Empleados where edad > 40;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

27.

mysql> select count(distinct edad) from Empleados;
+----------------------+
| count(distinct edad) |
+----------------------+
|                   21 |
+----------------------+
1 row in set (0.00 sec)

28.

mysql> select ciudad, sum(salario) from Empleados, Categorias, Dptoficinas, Oficinas where Empleados.categoria = Categorias.categoria and Empleados.departamento = codigo and Dptoficinas.oficina = Oficinas.oficina group by Oficinas.oficina, ciudad;
+-----------+--------------+
| ciudad    | sum(salario) |
+-----------+--------------+
| Valencia  |       555000 |
| Barcelona |       210000 |
| Alicante  |        70000 |
| Madrid    |       285000 |
| Toledo    |        70000 |
+-----------+--------------+
5 rows in set (0.03 sec)

29.

[...]

30.

[...]