Taller 1

Ejercicio de repaso 1

Primero debe crear el model entidad relacion y el diagrama relacional en excel.

Cree en Mysql la base de datos llamada libreria.
Cree las siguientes tablas con sus respectivos registros.
Nota: el precio y nro de p�gina deben ser ingresados como enteros. Los dem�s campos tipo char.
Debe quedar constancia en el bloc de notas de la realizaci�n de cada consulta y adem�s debe exportar 
los resultados a excel.
+--------------------+
| Tables_in_libreria |
+--------------------+
| asignatura         |
| autor              |
| editorial          |
| liautedi           |
| libro              |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> select * from libro;
+---------+---------------------+------------+--------+-----------+
| idlibro | titulo              | nropaginas | precio | codigomat |
+---------+---------------------+------------+--------+-----------+
| L01     | Calculo II          |        120 |  55000 | M01       |
| L02     | Bases de datos II   |        150 |  65000 | M09       |
| L03     | Diagramacion        |        180 |  85000 | M03       |
| L04     | Ingles              |        280 |  10500 | M04       |
| L05     | Admon en una pagina |         70 |   7500 | M05       |
| L06     | Contabilidad I      |        170 |  27500 | M06       |
| L07     | Redes               |        370 |  32500 | M07       |
| L08     | Estructura de datos |         85 |  45000 | M08       |
+---------+---------------------+------------+--------+-----------+
8 rows in set (0.00 sec)
 
mysql> tee d:/taller_1.txt
Logging to file 'd:/taller_1.txt'
mysql> select * from autor;
+----------+----------------------+
| codautor | nombre               |
+----------+----------------------+
| A01      | Luis Joyanes         |
| A02      | Jorge Vasquez Posada |
| A03      | Jhon Soars           |
| A04      | Riaz Khadem          |
| A05      | Robert Lolbert       |
| A06      | Mario Dream          |
+----------+----------------------+
6 rows in set (0.00 sec)
 
mysql> select * from editorial;
+---------+--------------+
| codedit | nombre       |
+---------+--------------+
| E01     | Oveja negra  |
| E02     | Norma        |
| E03     | Mc graw hill |
+---------+--------------+
3 rows in set (0.00 sec)
 
mysql> select * from liautedi;
+---------+--------+---------+----------+
| idlibro | codaut | codedit | codautor |
+---------+--------+---------+----------+
| L02     |        | E01     | A01      |
| L02     |        | E03     | A05      |
| L06     |        | E02     | A02      |
| L07     |        | E03     | A05      |
| L04     |        | E01     | A04      |
| L04     |        | E02     | A04      |
| L04     |        | E03     | A04      |
+---------+--------+---------+----------+
7 rows in set (0.00 sec)
 
mysql> select codautor from autor where nombre like 'R%';
+----------+
| codautor |
+----------+
| A04      |
| A05      |
+----------+
2 rows in set (0.00 sec)
 
mysql> alter table LIAUTEDI add foreign key (codautor) references AUTOR(codautor) on update cascade on delete cascade;
ERROR 1072 (42000): Key column 'codautor' doesn't exist in table
mysql> alter table LIAUTEDI add codautor char(20) not null;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table LIAUTEDI add foreign key (codautor) references AUTOR(codautor) on update cascade on delete cascade;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table LIAUTEDI add codedit char(20) not null;
ERROR 1060 (42S21): Duplicate column name 'codedit'
mysql> describe LIAUTEDI;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| idlibro  | char(20) | NO   | MUL | NULL    |       |
| codaut   | char(20) | NO   |     | NULL    |       |
| codedit  | char(20) | NO   |     | NULL    |       |
| codautor | char(20) | NO   | MUL | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.03 sec)
 
mysql> alter table LIAUTEDI add foreign key (codedit) references EDITORIAL(codedit) on update cascade on delete cascade;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> describe libro;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| idlibro    | char(10) | NO   | PRI | NULL    |       |
| titulo     | char(60) | NO   |     | NULL    |       |
| nropaginas | int(7)   | NO   |     | NULL    |       |
| precio     | int(7)   | NO   |     | NULL    |       |
| codigomat  | char(20) | NO   | MUL | NULL    |       |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.02 sec)
 
mysql> describe materia;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(20) | NO   | PRI | NULL    |       |
| nombre    | char(60) | NO   |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M01',Calculo); 
ERROR 1054 (42S22): Unknown column 'Calculo' in 'field list'
mysql> insert into MATERIA(codigomat, nombre) values ('M01','Calculo'); 
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M02','Matematicas'); 
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M03','Estructura de datos'); 
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M04','Ingles');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M08','Diagramacion');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M06','Contabilidad');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M07','Redes');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M05','Sistemas de informacion');
Query OK, 1 row affected (0.04 sec)
 
mysql> insert into MATERIA(codigomat, nombre) values ('M09','Base de datos');
Query OK, 1 row affected (0.02 sec)
 
mysql> inset into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L01', 'Calculo II', 120, 55000, 'M01');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inset into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L01', ' at line 1
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L01', 'Calculo II', 120, 55000, 'M01');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L02', 'Bases de datos II', 150, 65000, 'M09');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L03', 'Diagramacion', 180, 85000, 'M03');
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L08', 'Estructura de datos', 85, 45000, 'M08');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L05', 'Admon en una pagina', 70, 7500, 'M05');
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L06', 'Contabilidad I', 170, 27500, 'M06');
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L07', 'Redes', 370, 32500, 'M07');
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into LIBRO(idlibro, titulo, nropaginas, precio, codigomat) values ('L04', 'Ingles', 280, 10500, 'M04');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into AUTOR(codautor, nombre) values ('A01', 'Luis Joyanes');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into AUTOR(codautor, nombre) values ('A02', 'Jorge Vasquez Posada');
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into AUTOR(codautor, nombre) values ('A03', 'Jhon Soars');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into AUTOR(codautor, nombre) values ('A04', 'Riaz Khadem');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into AUTOR(codautor, nombre) values ('A05', 'Robert Lolbert');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into AUTOR(codautor, nombre) values ('A06', 'Mario Dream');
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into EDITORIAL(codedit, nombre) values ('E01', 'Oveja negra');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into EDITORIAL(codedit, nombre) values ('E02', 'Norma');
Query OK, 1 row affected (0.04 sec)
 
mysql> insert into EDITORIAL(codedit, nombre) values ('E03', 'Mc graw hill');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into LIAUTEDI(idlibro, codautor, codedit) values ('L02', 'A01', 'E01');
Query OK, 1 row affected, 1 warning (0.02 sec)
 
mysql> insert into LIAUTEDI(idlibro, codautor, codedit) values ('L02', 'A05', 'E03');
Query OK, 1 row affected, 1 warning (0.02 sec)
 
mysql> insert into LIAUTEDI(idlibro, codautor, codedit) values ('L06', 'A02', 'E02');
Query OK, 1 row affected, 1 warning (0.02 sec)
 
mysql> insert into LIAUTEDI(idlibro, codautor, codedit) values ('L07', 'A05', 'E03');
Query OK, 1 row affected, 1 warning (0.02 sec)
 
mysql> insert into LIAUTEDI(idlibro, codautor, codedit) values ('L04', 'A04', 'E01');
Query OK, 1 row affected, 1 warning (0.03 sec)
 
mysql> insert into LIAUTEDI(idlibro, codautor, codedit) values ('L04', 'A04', 'E02');
Query OK, 1 row affected, 1 warning (0.01 sec)
 
mysql> insert into LIAUTEDI(idlibro, codautor, codedit) values ('L04', 'A04', 'E03');
Query OK, 1 row affected, 1 warning (0.03 sec)
 
mysql> select * from LIBRO;
+---------+---------------------+------------+--------+-----------+
| idlibro | titulo              | nropaginas | precio | codigomat |
+---------+---------------------+------------+--------+-----------+
| L01     | Calculo II          |        120 |  55000 | M01       |
| L02     | Bases de datos II   |        150 |  65000 | M09       |
| L03     | Diagramacion        |        180 |  85000 | M03       |
| L04     | Ingles              |        280 |  10500 | M04       |
| L05     | Admon en una pagina |         70 |   7500 | M05       |
| L06     | Contabilidad I      |        170 |  27500 | M06       |
| L07     | Redes               |        370 |  32500 | M07       |
| L08     | Estructura de datos |         85 |  45000 | M08       |
+---------+---------------------+------------+--------+-----------+
8 rows in set (0.00 sec)
 
mysql> select * from AUTOR;
+----------+----------------------+
| codautor | nombre               |
+----------+----------------------+
| A01      | Luis Joyanes         |
| A02      | Jorge Vasquez Posada |
| A03      | Jhon Soars           |
| A04      | Riaz Khadem          |
| A05      | Robert Lolbert       |
| A06      | Mario Dream          |
+----------+----------------------+
6 rows in set (0.00 sec)
 
mysql> select * from EDITORIAL;
+---------+--------------+
| codedit | nombre       |
+---------+--------------+
| E01     | Oveja negra  |
| E02     | Norma        |
| E03     | Mc graw hill |
+---------+--------------+
3 rows in set (0.00 sec)
 
mysql> select * from MATERIA;
+-----------+-------------------------+
| codigomat | nombre                  |
+-----------+-------------------------+
| M01       | Calculo                 |
| M02       | Matematicas             |
| M03       | Estructura de datos     |
| M04       | Ingles                  |
| M05       | Sistemas de informacion |
| M06       | Contabilidad            |
| M07       | Redes                   |
| M08       | Diagramacion            |
| M09       | Base de datos           |
+-----------+-------------------------+
9 rows in set (0.00 sec)
 
mysql> select titulo, precio from libro;
+---------------------+--------+
| titulo              | precio |
+---------------------+--------+
| Calculo II          |  55000 |
| Bases de datos II   |  65000 |
| Diagramacion        |  85000 |
| Ingles              |  10500 |
| Admon en una pagina |   7500 |
| Contabilidad I      |  27500 |
| Redes               |  32500 |
| Estructura de datos |  45000 |
+---------------------+--------+
8 rows in set (0.00 sec)
 
mysql> alter table MATERIA rename to ASIGNATURA;
Query OK, 0 rows affected (0.02 sec)
 
mysql> select from LIBRO where titulo like '%C' and '%a%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from LIBRO where titulo like '%C' and '%a%'' at line 1
mysql> select from LIBRO where titulo like '%C' && '%a%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from LIBRO where titulo like '%C' && '%a%'' at line 1
mysql> select titulo from LIBRO where titulo like '%C' and '%a%';
Empty set, 1 warning (0.00 sec)
 
mysql> select titulo from LIBRO where titulo like '%C' and '%a';
Empty set, 1 warning (0.00 sec)
 
mysql> select titulo from LIBRO where titulo like '%C';
Empty set (0.00 sec)
 
mysql> select titulo from LIBRO where titulo like 'C%' and '%a';
Empty set, 1 warning (0.00 sec)
 
mysql> select titulo from LIBRO where titulo like 'C%' and '%a%';
Empty set, 1 warning (0.00 sec)
 
mysql> select titulo from LIBRO where titulo like 'C%';
+----------------+
| titulo         |
+----------------+
| Calculo II     |
| Contabilidad I |
+----------------+
2 rows in set (0.00 sec)
 
mysql> select precio from LIBRO where precio like '5%';
+--------+
| precio |
+--------+
|  55000 |
+--------+
1 row in set (0.00 sec)
 
mysql> select titulo from LIBRO where precio like '5%';
+------------+
| titulo     |
+------------+
| Calculo II |
+------------+
1 row in set (0.00 sec)