Ejercicio de Repaso N° 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.
LIBRO
+---------+---------------------+-----------+--------+-----------+
| idlibro | titulo | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructura de datos | 180 | 85000 | M03 |
| L08 | Diagramacion | 85 | 45000 | M08 |
| L05 | Admon en una pagina | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L04 | Ingles | 280 | 105000 | M04 |
+---------+---------------------+-----------+--------+-----------+
Cambiar el campo título por descripcion
MATERIA
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de datos |
| M04 | Ingl |
| M08 | Diagramacion |
| M06 | Contabilidad |
| M07 | Redes |
| M05 | Sistemas de Inf. |
| M09 | Base de datos |
+-----------+---------------------+
AUTOR
+----------+----------------------+
| codautor | nombre |
+----------+----------------------+
| A01 | Luis Joyanes |
| A02 | Jorge Vasquez Posada |
| A03 | Jhon Soars |
| A04 | Riaz Khadem |
| A05 | Robert Lorber |
| A06 | Mario Dream |
+----------+----------------------+
EDITORIAL
++---------+--------------+
| codedit | nombre |
+---------+--------------+
| E01 | Oveja Negra |
| E02 | Norma |
| E03 | Mc Graw Hill |
+---------+--------------+
LIAUTEDI
+---------+----------+---------+
| idlibro | codautor | codedit |
+---------+----------+---------+
| L02 | A01 | E01 |
| L02 | A05 | E03 |
| L06 | A02 | E02 |
| L07 | A05 | E03 |
| L04 | A04 | E01 |
| L04 | A04 | E02 |
| L04 | A04 | E03 |
+---------+----------+---------+
ACONTINUACION
a. Listar lo libros
b. listar los autores.
c. Listar las editoriales.
d. listar las materias
e. Listar la descripcion de los libros y los precios.
f. Cambiar el nombre de la tabla materia por asignatura
g. Realizar consultas con el comando like y con condiciones
h: Utilizar la funciones para realizar calculos en las tablas donde crea que puede hacerlo.
Desarrollo del Ejercicio de Repaso N° 1
mysql> create database libreria;
Query OK, 1 row affected (0.02 sec)
mysql> create table libro
-> (idlibro char(10) not null primary key,
-> titulo char(20) not null,
-> nropagina int(3) not null,
-> precio int(10) not null,
-> codigomat char(3) not null);
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| libreria |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| webauth |
+--------------------+
8 rows in set (0.07 sec)
mysql> use libreria;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table libro
-> (idlibro char(10) not null primary key,
-> titulo char(20) not null,
-> nropagina int(3) not null,
-> precio int(10) not null,
-> codigomat char(3) not null,
->foreign key(codigomat)references materia(codigomat)on delete cascade on update cascade)engine=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| libro |
+--------------------+
1 row in set (0.00 sec)
mysql> alter table libro change titulo descripcion char not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(1) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> create table materia
-> (codigomat char(3) not null primary key,
-> nombre char(20) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> create table autor
-> (codautor char(25) not null primary key,
-> nombre char(25) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> create table editorial
-> (codedit char(3) not null primary key,
-> nombre char(15) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| libreria |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| webauth |
+--------------------+
8 rows in set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| autor |
| editorial |
| libro |
| materia |
+--------------------+
4 rows in set (0.00 sec)
mysql> create table liautedi
-> (idlibro char(10) not null,
-> codautor char(3) not null,
-> codedit char(3)not null,
-> foreign key(idlibro)references libro(idlibro)on delete cascade on update cascade,
-> foreign key(codautor)references autor(codautor)on delete cascade on update cascade,
-> foreign key(codedit)references editorial(codedit)on delete cascade on update cascade)engine=innodb;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| autor |
| editorial |
| liautedi |
| libro |
| materia |
+--------------------+
5 rows in set (0.00 sec)
mysql> use libreria;
Database changed
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(1) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> describe liautedi;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | MUL | NULL | |
| codautor | char(3) | NO | MUL | NULL | |
| codedit | char(3) | NO | MUL | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| libreria |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| webauth |
+--------------------+
8 rows in set (0.12 sec)
mysql> use libreria;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| autor |
| editorial |
| liautedi |
| libro |
| materia |
+--------------------+
5 rows in set (0.00 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(1) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.12 sec)
mysql> describe materia;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.04 sec)
mysql> describe autor;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| codautor | char(25) | NO | PRI | NULL | |
| nombre | char(25) | NO | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.04 sec)
mysql> describe editorial;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| nombre | char(15) | NO | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> describe liautedi;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | MUL | NULL | |
| codautor | char(3) | NO | MUL | NULL | |
| codedit | char(3) | NO | MUL | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> use libreria;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| autor |
| editorial |
| liautedi |
| libro |
| materia |
+--------------------+
5 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| libreria |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| webauth |
+--------------------+
8 rows in set (0.00 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(1) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> select * from libro;
Empty set (0.02 sec)
mysql> select idlibro,descripcion,nropagina,precio,codigomat from libro;
Empty set (0.00 sec)
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(1) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> alter table libro modify descripcion char(20) not null;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(20) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L01','Calculo II',120,55000,'M01');
Query OK, 1 row affected (0.01 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L02','BD II',150,65000,'M09');
Query OK, 1 row affected (0.00 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L03','Estructara de Datos',180,85000,'M03');
Query OK, 1 row affected (0.00 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L08','Diagramacion',85,45000,'M08');
Query OK, 1 row affected (0.00 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L05','Admon en una Pagina',70,7500,'M05');
Query OK, 1 row affected (0.00 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L06','Contabilidad I',170,27500,'M06');
Query OK, 1 row affected (0.00 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L07','Redes',370,32500,'M07');
Query OK, 1 row affected (0.00 sec)
mysql> insert into libro(idlibro,descripcion,nropagina,precio,codigomat)values('L04','Ingles',280,105000,'M04');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M01','Calculo');
Query OK, 1 row affected (0.02 sec)
mysql> insert into materia(codigomat,nombre)values('M02','Matematicas');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M03','Estructura de Datos');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M04','Ingles');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M08','Diagramacion');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M06','Contabilidad');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M07','Redes');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M05','Sistemas de Inf.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into materia(codigomat,nombre)values('M09','Bases de Datos');
Query OK, 1 row affected (0.00 sec)
mysql> insert into autor(codautor,nombre)values('A01','Luis Joyanes');
Query OK, 1 row affected (0.03 sec)
mysql> insert into autor(codautor,nombre)values('A02','Jorge Vasquez Posada');
Query OK, 1 row affected (0.00 sec)
mysql> insert into autor(codautor,nombre)values('A03','Jhon Soars');
Query OK, 1 row affected (0.00 sec)
mysql> insert into autor(codautor,nombre)values('A04','Riaz Khadem');
Query OK, 1 row affected (0.00 sec)
mysql> insert into autor(codautor,nombre)values('A05','Robert Lorber');
Query OK, 1 row affected (0.00 sec)
mysql> insert into autor(codautor,nombre)values('A06','Mario Dream');
Query OK, 1 row affected (0.01 sec)
mysql> insert into editorial(codedit,nombre)values('E01','Oveja Negra');
Query OK, 1 row affected (0.03 sec)
mysql> insert into editorial(codedit,nombre)values('E02','Norma');
Query OK, 1 row affected (0.00 sec)
mysql> insert into editorial(codedit,nombre)values('E03','Mc Graw Hill');
Query OK, 1 row affected (0.00 sec)
mysql> insert into liautedi(idlibro,codautor,codedit)values('L02','A01','E01');
Query OK, 1 row affected (0.04 sec)
mysql> insert into liautedi(idlibro,codautor,codedit)values('L02','A05','E03');
Query OK, 1 row affected (0.00 sec)
mysql> insert into liautedi(idlibro,codautor,codedit)values('L06','A02','E02');
Query OK, 1 row affected (0.00 sec)
mysql> insert into liautedi(idlibro,codautor,codedit)values('L07','A05','E03');
Query OK, 1 row affected (0.00 sec)
mysql> insert into liautedi(idlibro,codautor,codedit)values('L04','A04','E01');
Query OK, 1 row affected (0.01 sec)
mysql> insert into liautedi(idlibro,codautor,codedit)values('L04','A04','E02');
Query OK, 1 row affected (0.00 sec)
mysql> insert into liautedi(idlibro,codautor,codedit)values('L04','A04','E03');
Query OK, 1 row affected (0.00 sec)
mysql> select idlibro,descripcion,nropagina,precio,codigomat from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
mysql> select codigomat,nombre from materia;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> select codautor,nombre from autor;
+----------+----------------------+
| codautor | nombre |
+----------+----------------------+
| A01 | Luis Joyanes |
| A02 | Jorge Vasquez Posada |
| A03 | Jhon Soars |
| A04 | Riaz Khadem |
| A05 | Robert Lorber |
| A06 | Mario Dream |
+----------+----------------------+
6 rows in set (0.00 sec)
mysql> select codedit,nombre from editorial;
+---------+--------------+
| codedit | nombre |
+---------+--------------+
| E01 | Oveja Negra |
| E02 | Norma |
| E03 | Mc Graw Hill |
+---------+--------------+
3 rows in set (0.00 sec)
mysql> select descripcion,precio from libro;
+---------------------+--------+
| descripcion | precio |
+---------------------+--------+
| Calculo II | 55000 |
| BD II | 65000 |
| Estructara de Datos | 85000 |
| Ingles | 105000 |
| Admon en una Pagina | 7500 |
| Contabilidad I | 27500 |
| Redes | 32500 |
| Diagramacion | 45000 |
+---------------------+--------+
8 rows in set (0.00 sec)
mysql> alter table materia rename to asignatura;
Query OK, 0 rows affected (0.01 sec)
mysql> select codigomat,nombre from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
mysql>exit