examen R.A. 1.3 AMANDA

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 materia_prima;

Query OK, 1 row affected (0.00 sec)

mysql> use materia_prima;

Database changed

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

precio integer,fecha_de_compra integer);

Query OK, 0 rows affected (0.10 sec)

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

Query OK, 1 row affected (0.06 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_zaz',200,2009/11/03);

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

mysql> insert into productos values('a04','tequila',300,2009/11/03);

Query OK, 1 row affected (0.00 sec)

mysql> insert into productos values('a05','wiski',500,2009/11/03);

Query OK, 1 row affected (0.00 sec)

mysql> select*from productos;

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

| codigo | nombre     | precio | fecha_de_compra |

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

| a01    | vino       |    250 |              61 |

| a02    | silla_mode |    400 |              61 |

| a03    | silla_mode |    200 |              61 |

| a04    | tequila    |    300 |              61 |

| a05    | wiski      |    500 |              61 |

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

5 rows in set (0.01 sec)

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

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

| codigo | nombre | precio | fecha_de_compra |

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

| a01    | vino   |    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_mode |    400 |              61 |

| a03    | silla_mode |    200 |              61 |

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

2 rows in set (0.00 sec)

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

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

| nombre | precio |

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

| wiski  |    500 |

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

1 row in set (0.00 sec)

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

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

| avg(precio) |

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

|    300.0000 |

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

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="silla" where left(nombre,5) = 'silla';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

mysql> select distinct categoria from productos;

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

| categoria |

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

| NULL      |

| silla     |

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

2 rows in set (0.00 sec)

 

 

EJERCICIO 3

Enter password: ***

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

Your MySQL connection id is 5

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 productos1;

Query OK, 1 row affected (0.00 sec)

mysql> use productos1;

Database changed

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

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

Query OK, 0 rows affected (0.07 sec)

mysql> insert into productos values ('a01','afilador', 5.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 | fechalata  |

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

| a01    | afilador       |   5.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 | fechalata  |

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

| a01    | afilador |   5.50 | 2007-11-02 |

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

1 row in set (0.00 sec)

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

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

| codigo | nombre         | precio | fechalata  |

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

| 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> select avg(precio) from productos where left(nombre,5) ='Silla';

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

| avg(precio) |

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

|   22.500000 |

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

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> 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.04 sec)

mysql>