PRACTICA 5

 

mysql> create database hos_emple;

Query OK, 1 row affected (0.18 sec)

mysql> use hos_emple;

Database changed

mysql> create table personas(cod_hosp integer,dni integer,apellidos varchar(50),funcion varchar(10),salario integer,localidad varchar(9));

Query OK, 0 rows affected (0.14 sec)

mysql> insert into personas values(1,12345,'Garcia Hernandez Elodio','conserje',1200,'Lorca');

Query OK, 1 row affected (0.03 sec)

mysql> insert into personas values(1,123456,'Fuentes Bermeso Carlos','Director',2000,'Murcia');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(2,666444,'Gonzalez Marin Maria','Conserje',1200,'Murcia');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(1,666666,'Castillo Montes Pedro','Medico',1700,'Murcia');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(2,222333,'Beltran Garcia Ana','Medico',1000,'Murcia');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(3,555444,'Ruiz Hernandez Carlos','Medico',1900,'Lorca');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(3,999833,'Soriano Diaz Alejandro','Director',2400,'Cartagena');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(4,332221,'Mendoza Del Castillo Ivan','Medico',2200,'Lorca');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(2,222333,'Martinez Molma Andres','Medico',1000,'Cartagena');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(4,555444,'Jimenez Jimenez Dolores','Conserje',1200,'Murcia');

Query OK, 1 row affected (0.00 sec)

mysql> insert into personas values(4,222331,'Martinez Molma Gloria','Medico',1600,'Murcia');

Query OK, 1 row affected (0.00 sec)

 

 

1

mysql> select*from personas;

+----------+--------+---------------------------+----------+---------+-----------+

| cod_hosp | dni    | apellidos                 | funcion  | salario | localidad |

+----------+--------+---------------------------+----------+---------+-----------+

|        1 |  12345 | Garcia Hernandez Elodio   | conserje |    1200 | Lorca |

|        1 | 123456 | Fuentes Bermeso Carlos    | Director |    2000 | Murcia |

|        2 | 666444 | Gonzalez Marin Maria      | Conserje |    1200 | Murcia |

|        1 | 666666 | Castillo Montes Pedro     | Medico   |    1700 | Murcia |

|        2 | 222333 | Beltran Garcia Ana        | Medico   |    1000 | Murcia |

|        3 | 555444 | Ruiz Hernandez Carlos     | Medico   |    1900 | Lorca |

|        3 | 999833 | Soriano Diaz Alejandro    | Director |    2400 | Cartagena |

|        4 | 332221 | Mendoza Del Castillo Ivan | Medico   |    2200 | Lorca |

|        2 | 222333 | Martinez Molma Andres     | Medico   |    1000 | Cartagena |

|        4 | 555444 | Jimenez Jimenez Dolores   | Conserje |    1200 | Murcia |

|        4 | 222331 | Martinez Molma Gloria     | Medico   |    1600 | Murcia |

+----------+--------+---------------------------+----------+---------+-----------+

11 rows in set (0.10 sec)

 

 

2

mysql> select dni,apellidos,funcion from personas;

+--------+---------------------------+----------+

| dni    | apellidos                 | funcion  |

+--------+---------------------------+----------+

|  12345 | Garcia Hernandez Elodio   | conserje |

| 123456 | Fuentes Bermeso Carlos    | Director |

| 666444 | Gonzalez Marin Maria      | Conserje |

| 666666 | Castillo Montes Pedro     | Medico   |

| 222333 | Beltran Garcia Ana        | Medico   |

| 555444 | Ruiz Hernandez Carlos     | Medico   |

| 999833 | Soriano Diaz Alejandro    | Director |

| 332221 | Mendoza Del Castillo Ivan | Medico   |

| 222333 | Martinez Molma Andres     | Medico   |

| 555444 | Jimenez Jimenez Dolores   | Conserje |

| 222331 | Martinez Molma Gloria     | Medico   |

+--------+---------------------------+----------+

11 rows in set (0.03 sec)

 

 

3

mysql> select apellidos from personas where localidad='Lorca';

+---------------------------+

| apellidos                 |

+---------------------------+

| Garcia Hernandez Elodio   |

| Ruiz Hernandez Carlos     |

| Mendoza Del Castillo Ivan |

+---------------------------+

3 rows in set (0.05 sec)

 

 

4

mysql> select apellidos from personas where localidad='Murcia' or  localidad='Lo

rca';

+---------------------------+

| apellidos                 |

+---------------------------+

| Garcia Hernandez Elodio   |

| Fuentes Bermeso Carlos    |

| Gonzalez Marin Maria      |

| Castillo Montes Pedro     |

| Beltran Garcia Ana        |

| Ruiz Hernandez Carlos     |

| Mendoza Del Castillo Ivan |

| Jimenez Jimenez Dolores   |

| Martinez Molma Gloria     |

+---------------------------+

9 rows in set (0.00 sec)

 

 

5

mysql>  select apellidos from personas where salario>1500 and localidad='Lorca';

 

+---------------------------+

| apellidos                 |

+---------------------------+

| Ruiz Hernandez Carlos     |

| Mendoza Del Castillo Ivan |

+---------------------------+

2 rows in set (0.00 sec)

 

 

6

mysql> select*from personas where salario>1500 and localidad='murcia' and funcio

n='Director';

+----------+--------+------------------------+----------+---------+-----------+

| cod_hosp | dni    | apellidos              | funcion  | salario | localidad |

+----------+--------+------------------------+----------+---------+-----------+

|        1 | 123456 | Fuentes Bermeso Carlos | Director |    2000 | Murcia    |

+----------+--------+------------------------+----------+---------+-----------+

1 row in set (0.00 sec)

 

 

7

mysql> select*from personas where funcion='Medico' order by apellidos desc;

+----------+--------+---------------------------+---------+---------+-----------+

| cod_hosp | dni    | apellidos                 | funcion | salario | localidad|

+----------+--------+---------------------------+---------+---------+-----------+

|        3 | 555444 | Ruiz Hernandez Carlos     | Medico  |    1900 | Lorca|

|        4 | 332221 | Mendoza Del Castillo Ivan | Medico  |    2200 | Lorca|

|        4 | 222331 | Martinez Molma Gloria     | Medico  |    1600 | Murcia|

|        2 | 222333 | Martinez Molma Andres     | Medico  |    1000 | Cartagena|

|        1 | 666666 | Castillo Montes Pedro     | Medico  |    1700 | Murcia|

|        2 | 222333 | Beltran Garcia Ana        | Medico  |    1000 | Murcia|

+----------+--------+---------------------------+---------+---------+-----------+

6 rows in set (0.04 sec)

 

 

8

mysql> select distinct localidad from personas;

+-----------+

| localidad |

+-----------+

| Lorca     |

| Murcia    |

| Cartagena |

+-----------+

3 rows in set (0.04 sec)

 

 

9

mysql> select*from personas where funcion='Medico'and salario>1500 order by salario desc;

+----------+--------+---------------------------+---------+---------+-----------+

| cod_hosp | dni    | apellidos                 | funcion | salario | localidad|

+----------+--------+---------------------------+---------+---------+-----------+

|        4 | 332221 | Mendoza Del Castillo Ivan | Medico  |    2200 | Lorca|

|        3 | 555444 | Ruiz Hernandez Carlos     | Medico  |    1900 | Lorca|

|        1 | 666666 | Castillo Montes Pedro     | Medico  |    1700 | Murcia|

|        4 | 222331 | Martinez Molma Gloria     | Medico  |    1600 | Murcia|

+----------+--------+---------------------------+---------+---------+-----------+

4 rows in set (0.00 sec)

10

mysql> select*from personas where apellidos like 'M*';

Empty set (0.00 sec)

 

 

11

mysql> select *from personas where funcion in('regexp' '^M','conserje');

+--------------+--------+--------------------------+----------+---------+-----------+

| cod_hospital | dni    | apellidos                | funcion  | salario | localidad |

+--------------+--------+--------------------------+----------+---------+-----------+

| 1            | 12345  | Garcia Hernandez Claudio | conserje | 1200    | lorca     |

| 2            | 666444 | Gonzalez Marin Maria     | conserje | 1200    | murcia    |

| 4            | 555444 | Jimenez Jimenez Dolores  | conserje | 1200    | murcia    |

+--------------+--------+--------------------------+----------+---------+-----------+

3 rows in set (0.00 sec)

 

 

12

mysql> SELECT * FROM personas WHERE salario between 1200 and 2000;

+--------------+--------+--------------------------+----------+---------+-----------+

| cod_hospital | dni    | apellidos                | funcion  | salario | localidad |

+--------------+--------+--------------------------+----------+---------+-----------+

| 1            | 12345  | Garcia Hernandez Claudio | conserje | 1200    | lorca     |

| 1            | 123456 | Fuentes Bermejo Carlos   | director | 2000    | murcia    |

| 2            | 666444 | Gonzalez Marin Maria     | conserje | 1200    | murcia    |

| 1            | 666666 | CastilloMontes Pedro     | medico   | 1700    | murcia    |

| 3            | 555441 | Ruiz Hernandez Carlos    | medico   | 1900    | lorca     |

| 2            | 222333 | Martinez Molina Andres   | medico   | 1600    | cartajena |

| 4            | 555444 | Jimenez Jimenez Dolores  | conserje | 1200    | murcia    |

| 4            | 222331 | Martinez Molina Gloria   | medico   | 1600    | murcia    |

+--------------+--------+--------------------------+----------+---------+-----------+

8 rows in set (0.00 sec)

 

 

13

mysql> select *from personas where funcion in ('medico','director');

+--------------+--------+------------------------+----------+---------+-----------+

| cod_hospital | dni    | apellidos              | funcion  | salario | localidad |

+--------------+--------+------------------------+----------+---------+-----------+

| 1            | 123456 | Fuentes Bermejo Carlos | director | 2000    | murcia    |

| 1            | 666666 | CastilloMontes Pedro   | medico   | 1700    | murcia    |

| 2            | 222333 | Beltran Garcia Ana     | medico   | 1000    | murcia    |

| 3            | 555441 | Ruiz Hernandez Carlos  | medico   | 1900    | lorca     |

| 3            | 999833 | Soriana Diaz Alejandro | director | 2400    | cartajena |

| 2            | 222333 | Martinez Molina Andres | medico   | 1600    | cartajena |

| 4            | 222331 | Martinez Molina Gloria | medico   | 1600    | murcia    |

+--------------+--------+------------------------+----------+---------+-----------+

7 rows in set (0.00 sec)

 

 

14

mysql> select *from personas where funcion in('medico','director') and salario > '1500';

+--------------+--------+------------------------+----------+---------+-----------+

| cod_hospital | dni    | apellidos              | funcion  | salario | localidad |

+--------------+--------+------------------------+----------+---------+-----------+

| 1            | 123456 | Fuentes Bermejo Carlos | director | 2000    | murcia    |

| 1            | 666666 | CastilloMontes Pedro   | medico   | 1700    | murcia    |

| 3            | 555441 | Ruiz Hernandez Carlos  | medico   | 1900    | lorca     |

| 3            | 999833 | Soriana Diaz Alejandro | director | 2400    | cartajena |

| 2            | 222333 | Martinez Molina Andres | medico   | 1600    | cartajena |

| 4            | 222331 | Martinez Molina Gloria | medico   | 1600    | murcia    |

+--------------+--------+------------------------+----------+---------+-----------+

6 rows in set (0.00 sec)

 

 

15

mysql> select *from personas where localidad in('murcia','cartagena') and cod_hospital='1';

+--------------+--------+------------------------+----------+---------+-----------+

| cod_hospital | dni    | apellidos              | funcion  | salario | localidad |

+--------------+--------+------------------------+----------+---------+-----------+

| 1            | 123456 | Fuentes Bermejo Carlos | director | 2000    | murcia    |

| 1            | 666666 | CastilloMontes Pedro   | medico   | 1700    | murcia    |

+--------------+--------+------------------------+----------+---------+-----------+

2 rows in set (0.00 sec)

 

 

16

mysql> select upper(apellidos);

mysql> select apellidos from personas where cod_hospital in('1');

+--------------------------+

| apellidos                |

+--------------------------+

| Garcia Hernandez Claudio |

| Fuentes Bermejo Carlos   |

| CastilloMontes Pedro     |

 

17

 

 

18

mysql> select apellidos,localidad from personas where cod_hospital in('1');

+--------------------------+-----------+

| apellidos                | localidad |

+--------------------------+-----------+

| Garcia Hernandez Claudio | lorca     |

| Fuentes Bermejo Carlos   | murcia    |

| CastilloMontes Pedro     | murcia    |

+--------------------------+-----------+

3 rows in set (0.00 sec)

 

 

19

mysql> select *from personas where cod_hospital in('1','2') and salario > 1200;

+--------------+--------+------------------------+----------+---------+-----------+

| cod_hospital | dni    | apellidos              | funcion  | salario | localidad |

+--------------+--------+------------------------+----------+---------+-----------+

| 1            | 123456 | Fuentes Bermejo Carlos | director | 2000    | murcia    |

| 1            | 666666 | CastilloMontes Pedro   | medico   | 1700    | murcia    |

| 2            | 222333 | Martinez Molina Andres | medico   | 1600    | cartajena |

+--------------+--------+------------------------+----------+---------+-----------+

3 rows in set (0.00 sec)

 

 

20

mysql> select *from personas where cod_hospital in('1','3','4') and localidad = 'murcia';

+--------------+--------+-------------------------+----------+---------+-----------+

| cod_hospital | dni    | apellidos               | funcion  | salario | localidad |

+--------------+--------+-------------------------+----------+---------+-----------+

| 1            | 123456 | Fuentes Bermejo Carlos  | director | 2000    | murcia    |

| 1            | 666666 | CastilloMontes Pedro    | medico   | 1700    | murcia    |

| 4            | 555444 | Jimenez Jimenez Dolores | conserje | 1200    | murcia    |

| 4            | 222331 | Martinez Molina Gloria  | medico   | 1600    | murcia    |

+--------------+--------+-------------------------+----------+---------+-----------+

4 rows in set (0.00 sec)