PRCATICA 8

mysql> create database dbclientes;

Query OK, 1 row affected (0.14 sec)

mysql> use dbclientes;

Database changed

mysql> create table tbclientes(codigo_cli int primary key,nombre_cli varchar(250),trabajo_cli varchar(250));

Query OK, 0 rows affected (0.13 sec)

mysql> describe tbclientes;

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

| Field       | Type         | Null | Key | Default | Extra |

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

| codigo_cli  | int(11)      | NO   | PRI | NULL    |       |

| nombre_cli  | varchar(250) | YES  |     | NULL    |       |

| trabajo_cli | varchar(250) | YES  |     | NULL    |       |

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

3 rows in set (0.11 sec)

mysql> insert into tbclientes(codigo_cli,nombre_cli,trabajo_cli) values(1,'Edith Martinez Hernandez','Informatica'),(2,'Juan Carmona Hernandez','Control de la Contaminacion') ,(3,'Fernanda Lopez Perez','Productividad');

Query OK, 3 rows affected (0.06 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbclientes;

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

| codigo_cli | nombre_cli               | trabajo_cli                 |

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

|          1 | Edith Martinez Hernandez | Informatica                 |

|          2 | Juan Carmona Hernandez   | Control de la Contaminacion |

|          3 | Fernanda Lopez Perez     | Productividad               |

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

3 rows in set (0.00 sec)

mysql> CREATE TABLE tbcopiaclientes(codigo_cop int primary key,nombre_cop varcha

r(50),trabajo_cop varchar(50),usuario_cop varchar(50),modificado_cop datetime);

Query OK, 0 rows affected (0.05 sec)

mysql> describe tbclientes;

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

| Field       | Type         | Null | Key | Default | Extra |

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

| codigo_cli  | int(11)      | NO   | PRI | NULL    |       |

| nombre_cli  | varchar(250) | YES  |     | NULL    |       |

| trabajo_cli | varchar(250) | YES  |     | NULL    |       |

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

3 rows in set (0.01 sec)

mysql> delimiter //

mysql> create trigger nuevocliente after insert on tbclientes

    -> for each row

    -> begin

    -> insert into tbcopiaclientes(codigo_cop,nombre_cop,trabajo_cop,usuario_cop

,modificado_cop)values(new.codigo_cli,new.nombre_cli,new.trabajo_cli,CURRENT_USE

R(),now());

    -> end;

    -> //

Query OK, 0 rows affected (0.12 sec)

    -> show triggers //

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

--------------------------------------------------------------------------------

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

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

| Trigger      | Event  | Table      | Statement

 

                                                      | Timing | Created | sql_m

ode                                   | Definer        |

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

--------------------------------------------------------------------------------

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

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

| nuevocliente | INSERT | tbclientes | begin

insert into tbcopiaclientes(codigo_cop,nombre_cop,trabajo_cop,usuario_cop,modifi

cado_cop)values(new.codigo_cli,new.nombre_cli,new.trabajo_cli,CURRENT_USER(),now());

end | AFTER  | NULL    | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@local

host |

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

--------------------------------------------------------------------------------

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

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

1 row in set (0.03 sec)

Query OK, 1 row affected (0.16 sec)

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

| codigo_cli | nombre_cli               | trabajo_cli                 |

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

|          1 | Edith Martinez Hernandez | Informatica                 |

|          2 | Juan Carmona Hernandez   | Control de la Contaminacion |

|          3 | Fernanda Lopez Perez     | Productividad               |

|          4 | Crlos manuel zavala      | informatica                 |

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

4 rows in set (0.16 sec)

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

| codigo_cop | nombre_cop          | trabajo_cop | usuario_cop    | modificado_c

op      |

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

|          4 | Crlos manuel zavala | informatica | root@localhost | 2014-05-16 0

9:27:39 |

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

1 row in set (0.18 sec)

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

--------------------------------------------------------------------------------

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

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

| Trigger      | Event  | Table      | Statement

 

                                                      | Timing | Created | sql_m

ode                                   | Definer        |

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

--------------------------------------------------------------------------------

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

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

| nuevocliente | INSERT | tbclientes | begin

insert into tbcopiaclientes(codigo_cop,nombre_cop,trabajo_cop,usuario_cop,modifi

cado_cop)values(new.codigo_cli,new.nombre_cli,new.trabajo_cli,CURRENT_USER(),now

());

end | AFTER  | NULL    | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@local

host |

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

--------------------------------------------------------------------------------

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

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

1 row in set (0.22 sec)

mysql> insert into tbclientes() values(4,'Carlos manuel zavala','Informatica')//

Query OK, 1 row affected (0.00 sec)

mysql> select * from tbclientes//

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

| codigo_cli | nombre_cli               | trabajo_cli                 |

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

|          1 | Edith Martinez Hernandez | Informatica                 |

|          2 | Juan Carmona Hernandez   | Control de la Contaminacion |

|          3 | Fernanda Lopez Perez     | Productividad               |

|          4 | Crlos manuel zavala      | informatica                 |

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

5 rows in set (0.00 sec)

mysql> select * from  tbcopiaclientes//

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

| codigo_cop | nombre_cop           | trabajo_cop | usuario_cop    | modificado_cop      |

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

|          4 | Crlos manuel zavala  | informatica | root@localhost | 2014-05-1609:27:39 |

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

2 rows in set (0.00 sec)

 

PRCATICA 7

 

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)