examen R.A.1.3 AMAIRANI

EXAMEN 1.3

mysql> create database materia_prima;

Query OK, 1 row affected (0.49 sec)

mysql> use materia_prima;

Database changed

mysql> create table productos(codigo varchar(5) primary key,nombre varchar (15),

precio integer,fecha_de_compra integer);

Query OK, 0 rows affected (0.14 sec)

mysql> insert into productos values('a01','vinos',250,2009/11/03);

Query OK, 1 row affected (0.02 sec)

mysql> insert into productos values('a02','silla modelo.xax',400,2009/11/03);

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into productos values('a03','silla modelo.xax',200,2009/11/03);

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into productos values('a04','silla modelo.zaz',200,2007/11/02);

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into productos values('a05','afilador',25,2007/11/02);

Query OK, 1 row affected (0.00 sec)

mysql> select*from productos;

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

| codigo | nombre          | precio | fecha_de_compra |

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

| a01    | vinos           |    250 |              61 |

| a02    | silla modelo.xa |    400 |              61 |

| a03    | silla modelo.xa |    200 |              61 |

| a04    | silla modelo.za |    200 |              91 |

| a05    | afilador        |     25 |              91 |

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

5 rows in set (0.00 sec)

 

mysql> select*from productos where nombre='vinos';

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

| codigo | nombre | precio | fecha_de_compra |

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

| a01    | vinos  |    250 |              61 |

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

1 row in set (0.00 sec)

mysql> select*from productos where nombre like 's%';

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

| codigo | nombre          | precio | fecha_de_compra |

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

| a02    | silla modelo.xa |    400 |              61 |

| a03    | silla modelo.xa |    200 |              61 |

| a04    | silla modelo.za |    200 |              91 |

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

3 rows in set (0.00 sec)

mysql> select nombre, precio from productos where precio <433;

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

| nombre          | precio |

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

| vinos           |    250 |

| silla modelo.xa |    400 |

| silla modelo.xa |    200 |

| silla modelo.za |    200 |

| afilador        |     25 |

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

5 rows in set (0.00 sec)

mysql> select avg(precio) from productos where left (nombre,15) ='silla';

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

| avg(precio) |

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

|        NULL |

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

1 row in set (0.00 sec)

mysql> alter table productos add categoria varchar(10);

Query OK, 5 rows affected (0.04 sec)

Records: 5  Duplicates: 0  Warnings: 0

 

mysql> update productos set categoria='utensilio' where left(nombre,5)='vinos';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update productos set categoria='mueble' where left(nombre,5)='silla';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> select*from productos;

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

| codigo | nombre          | precio | fecha_de_compra | categoria |

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

| a01    | vinos           |    250 |              61 | utensilio |

| a02    | silla modelo.xa |    400 |              61 | mueble    |

| a03    | silla modelo.xa |    200 |              61 | mueble    |

| a04    | silla modelo.za |    200 |              91 | mueble    |

| a05    | afilador        |     25 |              91 | NULL      |

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

5 rows in set (0.00 sec)

mysql> update productos set categoria='mueble' where left(nombre,5)='afilador';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 0

mysql> select*from productos;

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

| codigo | nombre          | precio | fecha_de_compra | categoria |

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

| a01    | vinos           |    250 |              61 | utensilio |

| a02    | silla modelo.xa |    400 |              61 | mueble    |

| a03    | silla modelo.xa |    200 |              61 | mueble    |

| a04    | silla modelo.za |    200 |              91 | mueble    |

| a05    | afilador        |     25 |              91 | NULL      |

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

5 rows in set (0.00 sec)

mysql> update productos set categoria='mueble' where left(nombre,8)='afilador';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select*from productos;

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

| codigo | nombre          | precio | fecha_de_compra | categoria |

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

| a01    | vinos           |    250 |              61 | utensilio |

| a02    | silla modelo.xa |    400 |              61 | mueble    |

| a03    | silla modelo.xa |    200 |              61 | mueble    |

| a04    | silla modelo.za |    200 |              91 | mueble    |

| a05    | afilador        |     25 |              91 | mueble    |

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

5 rows in set (0.00 sec)

ejercicio numero 3

Enter password: ***

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database productus1;

Query OK, 1 row affected (0.00 sec)

mysql> use productus1;

Database changed

mysql> create table productos(codigo varchar(3),nombre varchar(30),precio decima

l(6,2),fechaalta date,primary key(codigo));

Query OK, 0 rows affected (0.07 sec)

mysql> insert into productos values('a01','afilador',2.50,'2007/11/02');

Query OK, 1 row affected (0.00 sec)

mysql> insert into productos values('s01','silla mod.zaz',20,'2007/11/03');

Query OK, 1 row affected (0.00 sec)

mysql> insert into productos values('s02','silla mod.xax',25,'2007/11/03');

Query OK, 1 row affected (0.00 sec)

mysql> select*from productos;

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

| codigo | nombre        | precio | fechaalta  |

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

| a01    | afilador      |   2.50 | 2007-11-02 |

| s01    | silla mod.zaz |  20.00 | 2007-11-03 |

| s02    | silla mod.xax |  25.00 | 2007-11-03 |

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

3 rows in set (0.00 sec)

mysql> select*from productos where nombre='afilador';

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

| codigo | nombre   | precio | fechaalta  |

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

| a01    | afilador |   2.50 | 2007-11-02 |

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

1 row in set (0.00 sec)

mysql> select*from productos where nombre like 's%';

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

| codigo | nombre        | precio | fechaalta  |

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

| s01    | silla mod.zaz |  20.00 | 2007-11-03 |

| s02    | silla mod.xax |  25.00 | 2007-11-03 |

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

2 rows in set (0.00 sec)

mysql> select nombre,precio from productos where precio >22;

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

| nombre        | precio |

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

| silla mod.xax |  25.00 |

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

1 row in set (0.00 sec)

mysql> alter table productos add categoria varchar(10);

Query OK, 3 rows affected (0.10 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> update productos set categoria='utensilio';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> update productos set categoria='silla' where left(nombre,5)='silla';;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

mysql> use productus1;

Database changed

mysql> update productos set categoria="silla" where left(nombre,5)='silla';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 2  Changed: 0  Warnings: 0

mysql> select distinct categoria from productos;

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

| categoria |

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

| utensilio |

| silla     |

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

2 rows in set (0.00 sec)

mysql> select categoria, count(*) from productos group by categoria;

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

| categoria | count(*) |

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

| silla     |        2 |

| utensilio |        1 |

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

2 rows in set (0.00 sec)